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

java arrays coldfusion apache-poi coldfusion-9
Alex picture Alex · Dec 17, 2012 · Viewed 7.5k times · Source

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

Leigh picture Leigh · Dec 18, 2012

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.

Related questions

How do I declare and initialize an array in Java?

How do I declare and initialize an array in Java?

Read More
What's the simplest way to print a Java array?

In Java, arrays don't override toString(), so if you try to print one directly, you get the className + '@' + the hex of the hashCode of the array, as defined by Object.toString(): int[] intArray = new int[] {1, 2, 3, 4, 5}; System.out.println(…

Read More
How do I determine whether an array contains a particular value in Java?

I have a String[] with values like so: public static final String[] VALUES = new String[] {"AB","BC","CD","AE"}; Given String s, is there a good way of testing whether VALUES contains s?

Read More