here is my code for a web application generating an excel document! My problem is that it only prints one line after a search. I need your help to improve it in a way that all the result of searches will be written in the excel document one after the other ie the excel document is filled with result of the search!
<%@ 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.HSSFCellStyle"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFFont"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFHeader"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFPrintSetup"%>
<%@ page import="org.apache.poi.hssf.util.HSSFColor"%>
<%@ page import="org.apache.poi.poifs.filesystem.POIFSFileSystem"%>
<%@ page import="java.io.*" %>
<%@ page import="java.sql.*" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%
Connection con= null;
Statement statement = null;
ResultSet rs = null;
%>
<%!
String msg="";
%>
<html>
<head><title>Search Page</title></head>
<body>
<form method=post action="">
Identifiant du client: <input type="text" name="id"> <input
type="submit" value="Generer">
</form>
<hr>
<%
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet1");
sheet.setColumnWidth((short)2,(short)(5000));
sheet.setColumnWidth((short)3,(short)(5000));
sheet.setColumnWidth((short)4,(short)(5000));
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short)12);
font.setFontName("Courier New");
font.setColor(HSSFColor.BLUE.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.ORANGE.index);
style.setFont(font);
HSSFRow row1 = sheet.createRow((short)1);
HSSFCell cell1 = row1.createCell((short)2);
cell1.setCellValue("Id");
cell1.setCellStyle(style);
cell1 = row1.createCell((short)3);
cell1.setCellValue("Name");
cell1.setCellStyle(style);
cell1 = row1.createCell((short)4);
cell1.setCellValue("Telephone");
cell1.setCellStyle(style);
%>
<%
if (request.getMethod().equalsIgnoreCase("post")) {
String id = request.getParameter("id");
if (id != null && !id.equals("")) {
id.trim();
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
String connectionURL = "jdbc
dbc:telephone";
con = DriverManager.getConnection(connectionURL);
PreparedStatement st = con.prepareStatement("select * from telephoneTable where id=?");
st.setInt(1,Integer.parseInt(id));
rs = st.executeQuery();
while(rs.next()){
String name = (rs.getString("Name"));
String Telephone = (rs.getString("Telephone"));
HSSFFont font1 = wb.createFont();
font1.setFontHeightInPoints((short)10);
font1.setFontName("Comic sans MS");
font1.setColor(HSSFColor.AQUA.index);
HSSFCellStyle style1 = wb.createCellStyle();
style1.setFont(font1);
HSSFRow row = sheet.createRow((short)2) ;
HSSFCell cell = row.createCell((short)2);
cell.setCellValue(id);
cell.setCellStyle(style1);
cell = row.createCell((short)3);
cell.setCellValue(name);
cell.setCellStyle(style1);
cell = row.createCell((short)4);
cell.setCellValue(Telephone);
cell.setCellStyle(style1);
}
}
catch (Exception e) {
msg = "Exception: " + e;
}
}
else{
msg = "Entrez obligatoirement le nom du client";
}
rs.close();
FileOutputStream fileOut = new FileOutputStream("C:\\Excelsample.xls");
wb.write(fileOut);
fileOut.close();
System.out.println("Data is saved in excel file.");
con.close();
}
%>
</table>
<hr>
<font color="red" size="3"><%=msg%></font>
</body>
</html>
<%@ 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.HSSFCellStyle"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFFont"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFHeader"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFPrintSetup"%>
<%@ page import="org.apache.poi.hssf.util.HSSFColor"%>
<%@ page import="org.apache.poi.poifs.filesystem.POIFSFileSystem"%>
<%@ page import="java.io.*" %>
<%@ page import="java.sql.*" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%
Connection con= null;
Statement statement = null;
ResultSet rs = null;
%>
<%!
String msg="";
%>
<html>
<head><title>Search Page</title></head>
<body>
<form method=post action="">
Identifiant du client: <input type="text" name="id"> <input
type="submit" value="Generer">
</form>
<hr>
<%
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet1");
sheet.setColumnWidth((short)2,(short)(5000));
sheet.setColumnWidth((short)3,(short)(5000));
sheet.setColumnWidth((short)4,(short)(5000));
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short)12);
font.setFontName("Courier New");
font.setColor(HSSFColor.BLUE.index);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.ORANGE.index);
style.setFont(font);
HSSFRow row1 = sheet.createRow((short)1);
HSSFCell cell1 = row1.createCell((short)2);
cell1.setCellValue("Id");
cell1.setCellStyle(style);
cell1 = row1.createCell((short)3);
cell1.setCellValue("Name");
cell1.setCellStyle(style);
cell1 = row1.createCell((short)4);
cell1.setCellValue("Telephone");
cell1.setCellStyle(style);
%>
<%
if (request.getMethod().equalsIgnoreCase("post")) {
String id = request.getParameter("id");
if (id != null && !id.equals("")) {
id.trim();
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
String connectionURL = "jdbc
dbc:telephone";con = DriverManager.getConnection(connectionURL);
PreparedStatement st = con.prepareStatement("select * from telephoneTable where id=?");
st.setInt(1,Integer.parseInt(id));
rs = st.executeQuery();
while(rs.next()){
String name = (rs.getString("Name"));
String Telephone = (rs.getString("Telephone"));
HSSFFont font1 = wb.createFont();
font1.setFontHeightInPoints((short)10);
font1.setFontName("Comic sans MS");
font1.setColor(HSSFColor.AQUA.index);
HSSFCellStyle style1 = wb.createCellStyle();
style1.setFont(font1);
HSSFRow row = sheet.createRow((short)2) ;
HSSFCell cell = row.createCell((short)2);
cell.setCellValue(id);
cell.setCellStyle(style1);
cell = row.createCell((short)3);
cell.setCellValue(name);
cell.setCellStyle(style1);
cell = row.createCell((short)4);
cell.setCellValue(Telephone);
cell.setCellStyle(style1);
}
}
catch (Exception e) {
msg = "Exception: " + e;
}
}
else{
msg = "Entrez obligatoirement le nom du client";
}
rs.close();
FileOutputStream fileOut = new FileOutputStream("C:\\Excelsample.xls");
wb.write(fileOut);
fileOut.close();
System.out.println("Data is saved in excel file.");
con.close();
}
%>
</table>
<hr>
<font color="red" size="3"><%=msg%></font>
</body>
</html>