Hi, I was using POI 2.5.1 to generate .xls files until the record count crossed the 65k+ limit set by Excel 2003. At this point, I switched to POI 3.6 and to XSSFWorkbook (from the previous HSSFWorkbook) to accomodate more number of records.
While I am successfuly in generating .xlsx documents, the performance seems to have taken a drastic hit. I tested this as follows. - First environment has the 3.6 version and the second environment has the existing 2.5.1. version. - I created a dummy report in both the environments which has plain SELECT query in the back-end to return a set no. of rows. - The SELECT query returns in both the cases in less than 2-3 seconds. The 2.5.1 version provides the document for download within 5 sec and the 3.6 version takes close to 70 seconds. I have attached the function which generates the Workbook object in the 3.6 version. Please let me know if you have any thoughts. Note: The code passes the ResultSet object to this method, which as we know is not a good coding standard. This is an existing code base which I am maintain. This didn't pose any performance problems in the 2.5.1 version. Do you think this can make a difference? Thank you in advance. Raghu
public XSSFWorkbook writeData(ResultSet rs, String repColumnHeading)
{
XSSFWorkbook wb = new XSSFWorkbook();
//XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet1 = null;
//wb.createDataFormat().getFormat("mm/dd/yyyy");
try
{
sheet1 = (XSSFSheet)wb.createSheet("Sheet1");
//sheet1.createFreezePane(0, 1);
Row row = sheet1.createRow(0);
Cell cell;
if (rs == null)
{
row = sheet1.createRow(0);
cell = row.createCell(0);
logger.info("writeData:: Report data ResultSet
obtained NULL. Providing message to user in Excel to contact Support");
/*cell.setCellValue("Exception occurred while
generating the Report. Please contact Application Support " +
"at
[email protected]");*/
cell.setCellValue(getMessageOnException());
return wb;
}
Font font = wb.createFont();
//font.setColor(HSSFColor.BLACK.index);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
// Heading Style
CellStyle headingCellStyle = wb.createCellStyle();
headingCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
headingCellStyle.setFont(font);
headingCellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
headingCellStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
headingCellStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
headingCellStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
headingCellStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
headingCellStyle.setLocked(true);
// Row1 Style with Left Align
CellStyle row1CellStyleLA = wb.createCellStyle();
row1CellStyleLA.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
row1CellStyleLA.setFillForegroundColor(HSSFColor.WHITE.index);
row1CellStyleLA.setAlignment(XSSFCellStyle.ALIGN_LEFT);
row1CellStyleLA.setBorderBottom(XSSFCellStyle.BORDER_THIN);
row1CellStyleLA.setBorderTop(XSSFCellStyle.BORDER_THIN);
row1CellStyleLA.setBorderLeft(XSSFCellStyle.BORDER_THIN);
row1CellStyleLA.setBorderRight(XSSFCellStyle.BORDER_THIN);
// Row1 Style with Right Align
CellStyle row1CellStyleRA = wb.createCellStyle();
row1CellStyleRA.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
row1CellStyleRA.setFillForegroundColor(HSSFColor.WHITE.index);
row1CellStyleRA.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
row1CellStyleRA.setBorderBottom(XSSFCellStyle.BORDER_THIN);
row1CellStyleRA.setBorderTop(XSSFCellStyle.BORDER_THIN);
row1CellStyleRA.setBorderLeft(XSSFCellStyle.BORDER_THIN);
row1CellStyleRA.setBorderRight(XSSFCellStyle.BORDER_THIN);
row1CellStyleRA.setDataFormat((short)0);
String[] strArrHeading = repColumnHeading.split(",");
for (int i=0; i < strArrHeading.length; i++)
{
cell = row.createCell(i);
cell.setCellValue(strArrHeading[i]);
cell.setCellStyle(headingCellStyle);
}
int iRowCount = 1;
Format dateFormatter = new
SimpleDateFormat("dd-MMM-yyyy");
while (rs.next())
{
row = sheet1.createRow(iRowCount++);
ResultSetMetaData rsMetaData = rs.getMetaData();
for (int i=1; i <= rsMetaData.getColumnCount();
i++)
{
String strCellValue = "";
cell = row.createCell(i-1);
/*if (iRowCount % 2 == 0)
{
cell.setCellStyle(row1CellStyleLA);
}
else
{
cell.setCellStyle(row2CellStyleLA);
}*/
//logger.debug("writeData:: Column
Name: " + rsMetaData.getColumnName(i) + " Column Type: " +
rsMetaData.getColumnType(i));
switch (rsMetaData.getColumnType(i))
{
case Types.DATE :
case Types.TIME :
case Types.TIMESTAMP:
//logger.debug("writeData:: Value T: " + (rs.getDate(i) == null ? "" :
dateFormatter.format(rs.getDate(i))));
cell.setCellValue((rs.getDate(i) == null ? "" :
dateFormatter.format(rs.getDate(i))));
cell.setCellStyle(row1CellStyleRA);
/*if (iRowCount % 2 == 0)
{
cell.setCellStyle(row1CellStyleRA);
}
else
{
cell.setCellStyle(row2CellStyleRA);
}*/
break;
case Types.NUMERIC :
case Types.BIGINT :
case Types.DECIMAL :
case Types.DOUBLE :
case Types.FLOAT :
case Types.INTEGER :
//logger.debug("Value N: " + rs.getDouble(i));
cell.setCellValue(rs.getDouble(i));
cell.setCellStyle(row1CellStyleRA);
/*if (iRowCount % 2 == 0)
{
cell.setCellStyle(row1CellStyleRA);
}
else
{
cell.setCellStyle(row2CellStyleRA);
}*/
break;
default
: //logger.debug("Value D: " + rs.getString(i));
cell.setCellValue(rs.getString(i));
cell.setCellStyle(row1CellStyleLA);
/*if (iRowCount % 2 == 0)
{
cell.setCellStyle(row1CellStyleLA);
}
else
{
cell.setCellStyle(row2CellStyleLA);
}*/
break;
}
}
}
/*sheet1.lockFormatCells();
sheet1.enableLocking();
wb.lockStructure();*/
}
catch (Exception e)
{
e.printStackTrace();
logger.error("writeData:: Exception occurred: " +
e.getMessage());
}
return wb;
}
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
