This code example demonstrates the following:
Import files for implementing oracle stored procedures
Import files for implementing HSSF POI for generating excel
Below is the code which will demonstrate how to generate an auto excel file
- The process of executing oracle stored procedure through java programming
- The implementation of HSSF POI for generating excel data in java
- Below code is a ready to use code which can be cut & paste directly
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ page import="java.util.*" import="java.sql.*" import="java.io.IOException"
import="java.io.PrintWriter" import="java.io.*" import="java.util.GregorianCalendar"%>
Import files for implementing oracle stored procedures
<%@ page import= "oracle.jdbc.*"%>
<%@ page import=" oracle.jdbc.pool.OracleDataSource"%>
<%@ page import=" oracle.jdbc.OracleDriver"%>
Import files for implementing HSSF POI for generating excel
<%@ 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 is the code which will demonstrate how to generate an auto excel file
<%
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);
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());
}
}
%>
No comments:
Post a Comment