Printing data into Excel sheet

View Answers

April 9, 2009 at 12:33 PM

Hi prashant,

Follow these steps:

step1) Create a simple 'excelFile.jsp' page:

<html>
<form method="post" action="file.jsp">
<input type = "submit" value="Submit">
</form>
</html>

step2) Create 'file.jsp' page that calls the Java Bean:

<jsp:useBean id="sample" class="form.ExcelBean" >
<jsp:setProperty name="sample" property="*"/>
</jsp:useBean>
<%
sample.insertExcelData();
out.println("Your excel file has been generated");
%>

step3) Create a Java Bean that will retrieve the data from the database and insert it into excel file:

package form;

import java.io.*;
import java.util.* ;
import java.sql.* ;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;

public class CreateExcelFile{
public CreateExcelFile(){
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/";;
String dbName = "register";
String driver = "com.mysql.jdbc.Driver";
String username = "root";
String userPassword = "root";
String filename = "c:\\excel\\file.xls" ;
try{
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url+dbName,username,userPassword);
Statement stmt = conn.createStatement();
String strQuery = "select * from registertable ";
ResultSet rs = stmt.executeQuery(strQuery);

HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("new sheet");
HSSFRow rowhead = sheet.createRow((short)0);
rowhead.createCell((short) 0).setCellValue("User ID");
rowhead.createCell((short) 1).setCellValue("First Name");
rowhead.createCell((short) 2).setCellValue("Last Name");
rowhead.createCell((short) 3).setCellValue("Address");
rowhead.createCell((short) 4).setCellValue("E-mail");

int index=1;
int id=0;
String name="";
while(rs.next())
{
id++;

HSSFRow row = sheet.createRow((short)index);
row.createCell((short) 0).setCellValue(id);
row.createCell((short) 1).setCellValue(rs.getString(2));
row.createCell((short) 2).setCellValue(rs.getString(3));
row.createCell((short) 3).setCellValue(rs.getString(4));
row.createCell((short) 4).setCellValue(rs.getString(5));
index++;
}
FileOutputStream fileOut = new FileOutputStream(filename);
hwb.write(fileOut);
fileOut.close();
System.out.println("Your excel file has been generated");
}
catch(Exception ex) {}
}
}
Thanks