Generate Excel in java using HSSF POI and oracle stored procedure

Below are the features of the same

• Implementation of oracle stored procedures in java
• Implementation of HSSF POI in java
• Ready to use code for generating excel using java

Note : You need to include the poi-3.5-beta5-20090219.jar file in your lib folder
Below are the files needed to be imported

<%@ page import="java.util.*" import="java.sql.*" import="java.io.IOException"

    import="java.io.PrintWriter" import="java.io.*" import="java.util.GregorianCalendar"%>

<%@ page import= "oracle.jdbc.*"%>

<%@ page import=" oracle.jdbc.pool.OracleDataSource"%>

<%@ page import=" oracle.jdbc.OracleDriver"%>

<%@ page import="org.apache.poi.xssf.usermodel.*"%>

<%@ page import="org.apache.poi.ss.usermodel.*"%>

<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%>

<%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%>

<%@ page import="org.apache.poi.hssf.usermodel.HSSFRow"%>

<%@ page import="org.apache.poi.hssf.usermodel.HSSFCell"%>

Below code shows the implementation of oracle stored procedure in java

<%

    Connection con = null;

    try{

        DBconnection con = new DBconnection();

        con = con.returnConnection();

        ResultSet rs= null;

 CallableStatement stmt=con.prepareCall("{ call Package.Procedure(?,?) }");

    stmt.setInt(1, id);

    stmt.registerOutParameter(2, OracleTypes.CURSOR);

    stmt.execute();

      rs = (ResultSet)stmt.getObject(2);

Below code shows the implementation of HSSF POI in java for generating dynamic excel

Workbook hwb = new HSSFWorkbook();

       CreationHelper createHelper = hwb.getCreationHelper();

       Sheet sheet = hwb.createSheet("FileName"+id);

       Row rowhead = sheet.createRow((short)0);

       CellStyle style = hwb.createCellStyle();

       style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());

       style.setFillPattern(CellStyle.SOLID_FOREGROUND);

       style.setBorderLeft(CellStyle.BORDER_THIN);

       style.setLeftBorderColor(IndexedColors.BLACK.getIndex());

       style.setBorderRight(CellStyle.BORDER_THIN);

       style.setRightBorderColor(IndexedColors.BLACK.getIndex());

       style.setBorderTop(CellStyle.BORDER_THIN);

       style.setTopBorderColor(IndexedColors.BLACK.getIndex());

       style.setBorderBottom(CellStyle.BORDER_THIN);

       style.setBottomBorderColor(IndexedColors.BLACK.getIndex());

             ResultSetMetaData rsMetaData = rsnew.getMetaData();

          int numberOfColumns =rsMetaData.getColumnCount();       

          Cell cell0= rowhead.createCell( 0);

             cell0.setCellValue(createHelper.createRichTextString("SNo"));

             cell0.setCellStyle(style);

       for (int i = 1; i < numberOfColumns + 1; i++) {

               Cell cell = rowhead.createCell(i);

               cell.setCellValue(createHelper.createRichTextString(rsMetaData.getColumnName(i)));

               cell.setCellStyle(style);

       }   

       int newline=1;

       int sno=0;

       String name="";

       int j=1;

       while (rsnew.next()) {

         Row row = sheet.createRow((short)newline);

         row.createCell( 0).setCellValue(j++);

        for(int i=1;i<=numberOfColumns;i++)

          {

            sno++;

            //Row row = sheet.createRow((short)index);

           row.createCell((short) i).setCellValue(rsnew.getString(i));

          }

          //index++;

        newline++; 

        }

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

       //FileOutputStream fileOut = new FileOutputStream("c:\\"+date.getTime() +".xls");

       //hwb.write(fileOut);

       //fileOut.close();

       response.setContentType("application/vnd.ms-excel");

       response.setHeader("Content-Disposition","attachment; filename=FloatReport.xls");

       OutputStream ot = response.getOutputStream();

       hwb.write(ot); 

       ot.close();

       //FileName = FilePath+"/outputfilename"+lotno+".xls";

       //FileOutputStream fileOut = new FileOutputStream(FileName);

   rs.close();

   out.clear();

   out=pageContext.pushBody();

   //out.println("<b>Your excel file has been generated</b>");

}//try

catch(Exception e)

{

// 

} 

finally

{

try{

con.close();

}

catch(Exception e){

System.out.println(e.getMessage());

}

}

%>