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)

`java.lang.ArrayIndexOutOfBoundsException: -32735` This could actually be an overflow issue. — Paul Bellora, Dec 17 '12 at 21:01
Just guessing here but see if the problem goes away if you remove the row formatting. Remove the `SpreadsheetFormatRow()` line. Guessing because I found [this issue](https://issues.apache.org/bugzilla/show_bug.cgi?id=49188) regarding a limit on cell styles in Excel. — Miguel-F, Dec 17 '12 at 21:28
How many values is `qryTest.getMeta().getColumnLabels()` returning? — Miguel-F, Dec 17 '12 at 21:38
@Miguel-F I've commented out that line to no avail. The results are just not consistent enough to say that's the solution. Also, qryTest.getMeta().getColumnLabels() returns 13 values. — Alex, Dec 17 '12 at 22:07
Have you figured out which line is causing the error? From the dump I am assuming it is this code; `qryTest.getMeta().getColumnLabels()`. Have you tried using the ColdFusion equivalent `getMetaData(qryTest)`? See the [docs for it here](http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7dd9.html). Maybe even try the standard `cfquery` return object for the column names; `qryTest.columnList`. I realize these may return the columns in a different order and ALL CAPS but just trying to pin point the issue and a possible work around. — Miguel-F, Dec 18 '12 at 13:37
I apologize, I should've mentioned that CF gives the error line as . I did consider that Adding the rows in two different commands might've caused some type of overflow, but even when I commented out the header lines (addrow/formatrow) it still was giving an error when attempting to add the query to the spreadsheet object. Also, the columnList key for a query object returns the columns in alphabetical order and in all caps so it's not very useful in this context. — Alex, Dec 18 '12 at 15:10
As I stated, I realize the results will be different but I was just trying to see if it would get rid of the error. You have not shown us how `qryTest` is being defined/set either. Is it just the result of a `cfquery` statement? — Miguel-F, Dec 18 '12 at 16:35
Correct, it's a cfquery tag with a select statement (for future reference it returns 13 columns with column names being containing A-Z, [period] and [space].). — Alex, Dec 19 '12 at 16:07

Leigh · Accepted Answer · 2012-12-19T03:27:04.000

3

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.

edited Dec 19 '12 at 03:27

answered Dec 18 '12 at 20:44

Leigh

28,765
10
55
103

This was definitely a step in the right direction. I went and downloaded the latest [POI](http://poi.apache.org/download.html) and replaced the 6 poi files (poi, poi-excelant, poi-ooxml, poi-ooxml-schemas, poi-scratchpad; the archide didn't contain a poi-contrib but this [post](http://apache-poi.1045710.n5.nabble.com/where-is-poi-contrib-td4532546.html) mentions that poi-examples contains poi-contrib so I renamed poi-examples to poi-contrib in the CF9/lib folder). Now it errors: The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook – Alex Dec 19 '12 at 17:41
2

That is a known [Excel limitation](http://support.microsoft.com/kb/213904). In my experience you have to be careful how you create styles in POI so you do not exceed the Excel maximums. For example, say you have 3 columns, each using the same "format" for the entire column. If you create a new style for *each* cell, you can easily blow the maximum limit. The recommended way is to create `3` styles and reuse them for all cells in each of the columns. However, I am not sure if CF spreadsheet functions are doing that... From the error message, probably not. But I would have to check. – Leigh Dec 19 '12 at 17:51
*java.lang.NoSuchMethodError* If the error is to be believed, it is possible the newer version is not fully "backwards compatible". What version of POI did you install? Also do you have a small test case I can use to test on my end? – Leigh Dec 19 '12 at 18:03
POI 3.9. And the only test case I can imagine is making a query object that has 18000+ recordset with 13 columns. I did this with queryNew, queryAddrow and a cfloop qurySetCell and it worked without the FormatRow line (it gave the same error when I included that line). – Alex Dec 19 '12 at 18:18
@Alex - (Edit) I have not tested 3.9 yet, but tried the manual query under `9,0,1,274733`. It worked fine for me with 13 columns and 20,000 rows (containing "value `row_num`). I should have asked this sooner, but what is your full CF version number? – Leigh Dec 19 '12 at 18:56
Same version `9,0,1,274733` I'm wondering now if there's anything in the cfquery that adds extra metadata which causes a different logic path (as noted before, my own manual test worked as well with 18000+ rows). Could characters like , and . in the record set or column names have an affect? – Alex Dec 19 '12 at 19:01
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/21402/discussion-between-leigh-and-alex) – Leigh Dec 19 '12 at 19:02

score 1 · Answer 2 · edited May 23 '17 at 11:59

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".

@Alex - Can you explain how this solves your issue? The error points to a POI issue, not CF or the JVM. — Leigh, Dec 19 '12 at 16:16
@Leigh I apologize, I knew it was the underlying code that was the issue and I suppose I was a bit eager to solve this problem. I just finished upgrading to JRE 1.6.0_38 and you are correct, the issue remains. I'm going to see how updating the POI works out. — Alex, Dec 19 '12 at 17:08
@Alex - Okay, I just did not want future readers to see this as "the answer" and think that updating their jvm would magically solve the problem ;-) If it is a POI bug, as it appears to be, the fix is updating POI. Assuming they really *did* fix the issue in later versions ... — Leigh, Dec 19 '12 at 17:27
@Leigh and Alex - Just to be clear, I was not suggesting that the JRE was the issue. I was trying to suggest that updating the JRE may 'fix' the issue by potentially allowing the index to increase. Sorry for the confusion. Looks like Leigh has you headed down the right path. — Miguel-F, Dec 19 '12 at 17:53
@Miguel-F - I do not know what the maximum size of standard lists are in java, but I think it is something absurdly large like [`Integer.MAX_VALUE`](http://docs.oracle.com/javase/1.5.0/docs/api/java/lang/Integer.html#MAX_VALUE). More often than not `ArrayIndexOutOfBoundsException` errors are caused by simple programming errors. — Leigh, Dec 19 '12 at 17:58
@Leigh - While researching this issue I found [this post](http://stackoverflow.com/questions/3038392/do-java-arrays-have-a-maximum-size) stating it is `Integer.MAX_VALUE` minus some small number of bytes depending on Java version. ;) Anyway, I totally agree with you that it is most often caused by a programming issue. My concern with this one was that the OP is using a built-in ColdFusion function so he could not just modify his code. Great suggestion on updating the underlying libraries. I always forget about that option... — Miguel-F, Dec 19 '12 at 18:04
@Miguel-F - Agreed, the built in functions *should* work. Sadly they do not. Granted that is probably due to a POI issue, but still .. Unfortunately, updating the libraries does not always work ;-) But it is worth a shot, since you can always roll it back. — Leigh, Dec 19 '12 at 18:07

deldev · Answer 3 · 2014-12-04T19:23:23.033

(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)

score 0 · Answer 4 · answered Dec 17 '12 at 20:59

0

In my experience with arrays, when you get an OutOfBounds Exception, it is usually because I have "overrun" the array - that is, I looped one more than the total number of items in the index. This usually happens because I forget that when the array starts with 0, I must only loop to the array length - 1.

Make sure you are not looping past the length of your array.

answered Dec 17 '12 at 20:59

bgmCoder

6,205
8
58
105

Well, that would be more of a question for Adobe because all I'm doing is calling the SpreadsheetAddRows function. I have added maxrows attribute but the results have not been consistent. For a while it seemed that setting maxrows to 17750 fixed the problem, but I have created XLS with over 18000 rows. – Alex Dec 17 '12 at 22:05

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

4 Answers4

Linked