QuestionAsk Questions?
prashant
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
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
Related Pages:
If you are facing any programming issue, such as compilation errors or not able to find the code you are looking for.
Ask your questions, our development team will try to give answers to your questions.
Tutorial Topics
