jsp-servlet

vieworderCD.jsp

<%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCellStyle"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFFont"%>
<%@ page import="org.apache.poi.hssf.util.HSSFColor"%>
<%@ page contentType="application/vnd.ms-excel" %>
<%@ page import="java.io.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>

<%


HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("View Order");
HSSFRow row = sheet.createRow((short)0);
HSSFCell cell = row.createCell((short)4);
HSSFRow row1 =sheet.createRow((short)3);
HSSFCell cell1 = row1.createCell((short)0);
HSSFCell cell2 = row1.createCell((short)1);
HSSFCell cell3 = row1.createCell((short)2);
HSSFCell cell4= row1.createCell((short)3);
HSSFCell cell5 = row1.createCell((short)4);
HSSFCell cell6 = row1.createCell((short)5);
HSSFCell cell7 = row1.createCell((short)6);
HSSFCell cell8 = row1.createCell((short)7);
HSSFCell cell9 = row1.createCell((short)8);
HSSFCell cell10 = row1.createCell((short)9);
HSSFCell cell11 = row1.createCell((short)10);
HSSFCell cell12 = row1.createCell((short)11);
HSSFCell cell13 = row1.createCell((short)12);
HSSFCell cell14 = row1.createCell((short)13);

HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short)14);
font.setFontName("Arial Black");
font.setItalic(true);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);

HSSFFont font1 = wb.createFont();
font1.setFontHeightInPoints((short)12);
font1.setFontName("Arial Black");
font1.setUnderline((byte)3);
HSSFCellStyle cellStyle1 = wb.createCellStyle();
cellStyle1.setFont(font1);
cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell1.setCellStyle(cellStyle1);

HSSFFont font2 = wb.createFont();
font2.setFontHeightInPoints((short)12);
font2.setFontName("Arial Black");
HSSFCellStyle cellStyle2 = wb.createCellStyle();
cellStyle2.setFont(font2);
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell2.setCellStyle(cellStyle2);

HSSFFont font3 = wb.createFont();
font3.setFontHeightInPoints((short)12);
font3.setFontName("Arial Black");
HSSFCellStyle cellStyle3 = wb.createCellStyle();
cellStyle3.setFont(font3);
cellStyle3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell3.setCellStyle(cellStyle3);

HSSFFont font4 = wb.createFont();
font4.setFontHeightInPoints((short)12);
font4.setFontName("Arial Black");
HSSFCellStyle cellStyle4 = wb.createCellStyle();
cellStyle4.setFont(font4);
cellStyle4.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell4.setCellStyle(cellStyle4);

HSSFFont font5 = wb.createFont();
font5.setFontHeightInPoints((short)12);
font5.setFontName("Arial Black");
HSSFCellStyle cellStyle5 = wb.createCellStyle();
cellStyle5.setFont(font5);
cellStyle5.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell5.setCellStyle(cellStyle5);

HSSFFont font6 = wb.createFont();
font6.setFontHeightInPoints((short)12);
font6.setFontName("Arial Black");
HSSFCellStyle cellStyle6 = wb.createCellStyle();
cellStyle6.setFont(font6);
cellStyle6.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell6.setCellStyle(cellStyle6);

HSSFFont font7 = wb.createFont();
font7.setFontHeightInPoints((short)12);
font7.setFontName("Arial Black");
HSSFCellStyle cellStyle7 = wb.createCellStyle();
cellStyle7.setFont(font7);
cellStyle7.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell7.setCellStyle(cellStyle7);

HSSFFont font8 = wb.createFont();
font8.setFontHeightInPoints((short)12);
font8.setFontName("Arial Black");
HSSFCellStyle cellStyle8 = wb.createCellStyle();
cellStyle8.setFont(font8);
cellStyle8.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell8.setCellStyle(cellStyle8);

HSSFFont font9 = wb.createFont();
font9.setFontHeightInPoints((short)12);
font9.setFontName("Arial Black");
HSSFCellStyle cellStyle9 = wb.createCellStyle();
cellStyle9.setFont(font9);
cellStyle9.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell9.setCellStyle(cellStyle9);

HSSFFont font10 = wb.createFont();
font10.setFontHeightInPoints((short)12);
font10.setFontName("Arial Black");
HSSFCellStyle cellStyle10 = wb.createCellStyle();
cellStyle10.setFont(font10);
cellStyle10.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell10.setCellStyle(cellStyle10);

HSSFFont font11 = wb.createFont();
font11.setFontHeightInPoints((short)12);
font11.setFontName("Arial Black");
HSSFCellStyle cellStyle11 = wb.createCellStyle();
cellStyle11.setFont(font11);
cellStyle11.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell11.setCellStyle(cellStyle11);

HSSFFont font12 = wb.createFont();
font12.setFontHeightInPoints((short)12);
font12.setFontName("Arial Black");
HSSFCellStyle cellStyle12 = wb.createCellStyle();
cellStyle12.setFont(font12);
cellStyle12.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell12.setCellStyle(cellStyle12);

HSSFFont font13 = wb.createFont();
font13.setFontHeightInPoints((short)12);
font13.setFontName("Arial Black");
HSSFCellStyle cellStyle13 = wb.createCellStyle();
cellStyle13.setFont(font13);
cellStyle13.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell13.setCellStyle(cellStyle13);

HSSFFont font14 = wb.createFont();
font14.setFontHeightInPoints((short)12);
font14.setFontName("Arial Black");
HSSFCellStyle cellStyle14 = wb.createCellStyle();
cellStyle14.setFont(font14);
cellStyle14.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cell14.setCellStyle(cellStyle14);

Connection con=null;
Statement stmt=null;
ResultSet rs=null;
int i=Integer.parseInt(request.getParameter("Num"));


//catch ( Exception ex ){}
%>

<%
try {


Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:Advdsn");
stmt=con.createStatement();
rs=stmt.executeQuery("SELECT OrderTab.OrderNo, OrderTab.Day, OrderTab.Name, ClassifiedDisplay.State, ClassifiedDisplay.City, ClassifiedDisplay.MainCategory, ClassifiedDisplay.imageName, ClassifiedDisplay.SubCategory, ClassifiedDisplay.NewsPaper, ClassifiedDisplay.Size ,ClassifiedDisplay.Color ,ClassifiedDisplay.Page ,ClassifiedDisplay.Total ,ClassifiedDisplay.Day &'/' & ClassifiedDisplay.Month & '/' & ClassifiedDisplay.Year AS DateofAd FROM OrderTab INNER JOIN ClassifiedDisplay ON OrderTab.OrderNo=ClassifiedDisplay.OrderNo WHERE ((OrderTab.OrderNo)="+i+" And (OrderTab.OrderNo)=[ClassifiedDisplay].[OrderNo])");

//rs=stmt.executeQuery("SELECT OrderTab.OrderNo, OrderTab.Day, OrderTab.Name, ClassifiedText.State, ClassifiedText.City, ClassifiedText.MainCategory, ClassifiedText.SubCategory, ClassifiedText.NewsPaper, ClassifiedText.Size ,ClassifiedText.Page ,ClassifiedText.Total , ClassifiedText.Day &'/' & ClassifiedText.Month & '/' & ClassifiedText.Year AS DateofAd FROM OrderTab INNER JOIN ClassifiedText ON OrderTab.OrderNo=ClassifiedText.OrderNo WHERE (((OrderTab.OrderNo)="+i+" And (OrderTab.OrderNo)=[ClassifiedText].[OrderNo]))");
//rs=stmt.executeQuery("select OrderNo,Name,Day from OrderTab where OrderNo="+i+"");


cell.setCellValue("Order No wise Report for Classified Display Ads");
cell1.setCellValue("OrderNo");
cell2.setCellValue("Day");
cell3.setCellValue("Name");
cell4.setCellValue("AdvtDate");
cell5.setCellValue("State");
cell6.setCellValue("City");
cell7.setCellValue("MainCat");
cell8.setCellValue("SubCat");
cell9.setCellValue("Newspaper");
cell10.setCellValue("ImageName");
cell11.setCellValue("Size");
cell12.setCellValue("Color");
cell13.setCellValue("AdPages");
cell14.setCellValue("Total");

while(rs.next())

{
HSSFRow row2 =sheet.createRow((short)4);
row2.createCell((short)0).setCellValue(rs.getInt("OrderNo"));
row2.createCell((short)1).setCellValue(rs.getString("Day"));
row2.createCell((short)2).setCellValue(rs.getString("Name"));
row2.createCell((short)3).setCellValue(rs.getString("DateOfAd"));
row2.createCell((short)4).setCellValue(rs.getString("State"));
row2.createCell((short)5).setCellValue(rs.getString("City"));
row2.createCell((short)6).setCellValue(rs.getString("MainCategory"));
row2.createCell((short)7).setCellValue(rs.getString("SubCategory"));
row2.createCell((short)8).setCellValue(rs.getString("Newspaper"));
row2.createCell((short)9).setCellValue(rs.getString("imageName"));
row2.createCell((short)10).setCellValue(rs.getString("Size"));
row2.createCell((short)11).setCellValue(rs.getString("Color"));
row2.createCell((short)12).setCellValue(rs.getString("Page"));
row2.createCell((short)13).setCellValue(rs.getInt("Total"));

}

FileOutputStream fileOut = new FileOutputStream("C:\\ExcelCD\\ViewOrdCD"+i+".xls");
wb.write(fileOut);
fileOut.close();
//out.println("Your excel file has been generated");
}
catch(Exception e2)
{
out.println(e2.toString());
}
//}



%>

In this code though i have given that it should create file in C:/excel folder then too it asks me to save the file why is this coming i dont know so please help me sir?

and when i give font.setBold(true);
then its giving me error i.e variable not found.
please reply me sir as soon as possible??

Thank you sir.