How do I fix SpreadSheetAddRows function crashing when adding a large query?

Question

EDIT3: Thanks to the help of @Leigh I've narrowed down the problem to the date columns in the query. Using the original code set and POI, the page crashes when SpreadSheetAddRows() attempts to add a very large query that contains date-like cells. I've made a bug report here: https://bugbase.adobe.com/index.cfm?event=bug&id=3432184.

---

I have a query that I am adding to a spreadhseet object that seems to error when the query has an unweildly amount of rows (18583 in this example). The exact error is as follows:

java.lang.ArrayIndexOutOfBoundsException: -32735
at java.util.ArrayList.get(ArrayList.java:324)
at org.apache.poi.hssf.model.WorkbookRecordList.get(WorkbookRecordList.j ava:50)
at org.apache.poi.hssf.model.Workbook.getExFormatAt(Workbook.java:787)
at org.apache.poi.hssf.usermodel.HSSFCell.getCellStyle(HSSFCell.java:901 )
at org.apache.poi.hssf.usermodel.HSSFSheet.autoSizeColumn(HSSFSheet.java :1727)
at coldfusion.excel.Excel.autoResize(Excel.java:1246)
at coldfusion.excel.Excel.autoResize(Excel.java:1240)
at coldfusion.excel.Excel.addRows(Excel.java:1214)
at coldfusion.runtime.CFPage.SpreadSheetAddRows(CFPage.java:7089) at coldfusion.runtime.CFPage.SpreadSheetAddRows(CFPage.java:7076)

Here's the relevant code:

<cfset xls = spreadsheetNew()>
<cfset spreadsheetAddRow(xls, arrayToList( qryTest.getMeta().getColumnLabels() ))>
<cfset SpreadsheetFormatRow(xls, {bold=true,fgcolor="brown",color="white"}, 1)>
<cfset SpreadsheetAddRows(xls, qryTest)>
<cfheader name="Content-Disposition" value="attachment; filename=#filename#">
<cfcontent variable="#spreadsheetReadBinary(xls)#" reset="yes" type="application/vnd.ms-excel">

---

EDIT: I did use cfspreadsheet previously with success, but it does not produce a spreadsheet with headers (and it also has the downside of needing to create a temporary file to serve.)

---

EDIT2: Following @Leigh suggestion I updated the POI in my CF9/lib folder. The errors have changed now to the following:

<cfset SpreadsheetFormatRow(xls, {bold=true,fgcolor="brown",color="white"}, 1)> Gives the following message: org.apache.poi.hssf.util.HSSFColor.getIndexHash()Ljava/util/Hashtable;

Error code:

java.lang.NoSuchMethodError:
org.apache.poi.hssf.util.HSSFColor.getIndexHash()Ljava/util/Hashtable;
at coldfusion.excel.Excel.getHSSFColor(Excel.java:2094)
at coldfusion.excel.Excel.findFont(Excel.java:2237)
at coldfusion.excel.Excel.getCellStyle(Excel.java:2318)
at coldfusion.excel.Excel.formatRow(Excel.java:2948)
at coldfusion.excel.Excel.formatRow(Excel.java:2963)
at coldfusion.excel.Excel.formatRow(Excel.java:2981)
at coldfusion.runtime.CFPage.SpreadSheetFormatRow(CFPage.java:7268)

Commenting that line out, it now crashes again on: <cfset SpreadsheetAddRows(xls, qryTest)>

Error Code:

java.lang.IllegalStateException: The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook 
at org.apache.poi.hssf.usermodel.HSSFWorkbook.createCellStyle(HSSFWorkbook.java:1120) 
at org.apache.poi.hssf.usermodel.HSSFWorkbook.createCellStyle(HSSFWorkbook.java:73) 
at coldfusion.excel.Excel.addRow(Excel.java:1323)
at coldfusion.excel.Excel.addRows(Excel.java:1203) 
at coldfusion.runtime.CFPage.SpreadSheetAddRows(CFPage.java:7089) 
at coldfusion.runtime.CFPage.SpreadSheetAddRows(CFPage.java:7076) 

Answer 1

I suspect it has nothing to do with CF or JRE version at all. At least not directly. It sounds like a bug in POI.

If you look at the exception it clearly shows the problem occurs when CF invokes a POI method that attempts to automatically resize the columns (after adding the query data). A quick search turned up several reports of similar ArrayIndexOutOfBoundsException errors with HSSFSheet.autoSizeColumn like this one (which just happens to mention ColdFusion):

If you attempt to use org.apache.poi.hssf.usermodel.HSSFSheet and method autosizecolumn(int) on a column after setting more than 32767 cells then ArrayOutOfBoundsException is thrown.

According to the bug report the issue existed in version 3.5, which is the same (major) version included with ColdFusion 9. One of the POI developers suggests the issue was fixed in later versions. So you might try updating the POI jar. Aside from that, if you can put together a test case that reproduces the issue, you may want to file a bug report.

I did use cfspreadsheet previously with success, but it does not produce a spreadsheet with headers (and it also has the downside of needing to create a temporary file to serve.)

cfspreadsheet probably does not attempt to automatically resize the columns like spreadsheetAddRows does, hence no error occurs. So the obvious workaround (and not a great one) is to avoid functions that attempt to resize the column widths.

Answer 2

If you step back from the code and just examine the error being thrown you get this from the java documentation and here is another reference

Thrown to indicate that an array has been accessed with an illegal index. The index is either negative or greater than or equal to the size of the array.

The error also shows you the illegal index value that was attempted: -32735

Now your problem is that you are not specifying the index values in your code per se because you are using a ColdFusion function. That function (SpreadsheetAddRows) is trying to convert your query result into an array and then append each of those values into rows of an Excel spreadsheet. It is utilizing the underlying Java runtime to perform these tasks and that is throwing an error. So I am afraid you are a bit stuck with this limitation you are encountering. You could attempt to upgrade the JRE version that your ColdFusion installation is running to see if the issue has been addressed in a newer release. I believe ColdFusion 9 ships with Java 1.6.0_14 see here. You should be running at least 1.6.0_24 anyway because of a DOS vulnerability patch. It looks like they are up to 1.6.0_38 now but you will have to check Adobe support for that.

If upgrading the JRE does not solve the issue then I believe you will need to change your ColdFusion code to avoid this issue. You stated that you had success using the CFSpreadSheet tag. Or perhaps you can play around with different ways to pass your query results to the SpreadsheetAddRows function. (Although I assume you have already exhausted that avenue.) Maybe looping over the query and building your own array or looping over the query and adding rows one at a time. I realize this may not be optimal but after trying a few different ways one will hopefully come out as the way to go.

I am also going to add the ColdFusion tag (without the version number) to your post to get some more eyes on it.

UPDATE

Just wanted to follow up on the supported Java version for ColdFusion 9. I found this blog entry by Charlie Arehart that discusses Adobe's stance on Java upgrading for ColdFusion servers. That links to the official Adobe post here which states that any minor version upgrade will be supported. So for ColdFusion 9 "All future JDK 1.6.0_x releases are supported".

Answer 3

(extracted from http://poi.apache.org/spreadsheet/quick-guide.html)

Note, the maximum number of unique fonts in a workbook is limited to 32767 ( the maximum positive short). You should re-use fonts in your apllications instead of creating a font for each cell. Examples:

Wrong:

for (int i = 0; i < 10000; i++) {
    Row row = sheet.createRow(i);
    Cell cell = row.createCell((short) 0);

    CellStyle style = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(font);
    cell.setCellStyle(style);
}

Correct:

CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
for (int i = 0; i < 10000; i++) {
    Row row = sheet.createRow(i);
    Cell cell = row.createCell((short) 0);
    cell.setCellStyle(style);
}

Anyway, you can try this too:

org.apache.poi.hssf.usermodel.HSSFOptimiser.optimiseCellStyles(HSSFWorkbook)