Inserting data in Excel File

Expert:Karthikeyan. K
Dear Sir,

How can we fill the excel with fetched data from the database like Oracle, DB2? Is it possible to create an excel with filled data? Kindly give me the solutions to questions.....

Thanks & Regards,
Karthikeyan. K

Answers

Hi friend,

Code to solve the problem :

<%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFRow"%>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell"%>
<%@ page import="java.io.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>

<%!
Connection conn = null;

%>
<br><br>
<%

java.util.Date date = new java.util.Date();

String filename = "c:\\excel\\f"+date.getTime() +".xls" ;

String searchText="";
if(request.getParameter("searchtxt")!=null)
{
searchText= request.getParameter("searchtxt").toString();
}

try{

Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection("jdbc:oracle:thin:@machine_name:1521:database_name","scott","tiger");

Statement stmt = conn.createStatement();
String strQuery = "select * from register where firstname like '%"+searchText+"%' or lastname like '%"+searchText+"%'";

ResultSet rs = stmt.executeQuery(strQuery);

HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("new sheet");

HSSFRow rowhead = sheet.createRow((short)2);
rowhead.createCell((short) 0).setCellValue("SNo");
rowhead.createCell((short) 1).setCellValue("First Name");
rowhead.createCell((short) 2).setCellValue("Last Name");
rowhead.createCell((short) 3).setCellValue("Username");
rowhead.createCell((short) 4).setCellValue("E-mail");
rowhead.createCell((short) 5).setCellValue("Country");

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

HSSFRow row = sheet.createRow((short)index);
row.createCell((short) 0).setCellValue(sno);
row.createCell((short) 1).setCellValue(rs.getString(4));
row.createCell((short) 2).setCellValue(rs.getString(5));
row.createCell((short) 3).setCellValue(rs.getString(2));
row.createCell((short) 4).setCellValue(rs.getString(6));
row.createCell((short) 5).setCellValue(rs.getString(9));
index++;
}
FileOutputStream fileOut = new FileOutputStream(filename);
hwb.write(fileOut);
fileOut.close();
out.println("<b>Your excel file has been generated</b>");

} catch ( Exception ex ) {

}
%>

Thanks

More Questions

Post Answers

Ask Question Facing Programming Problem?