添加大型查询时，如何修复 SpreadSheetAddRows 函数崩溃? [英] How do I fix SpreadSheetAddRows function crashing when adding a large query?
问题描述
感谢@Leigh 的帮助，我已将问题缩小到查询中的日期列.使用原始代码集和 POI，当 SpreadSheetAddRows() 尝试添加包含类似日期的单元格的非常大的查询时，页面会崩溃.我在这里做了一个错误报告:https://bugbase.adobe.com/index.cfm?event=bug&id=3432184.
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.
我有一个查询，我要添加到一个扩展表对象中，当查询的行数过多(在本例中为 18583)时，该查询似乎会出错.具体错误如下:
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)
相关代码如下:
<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">
<小时>
我之前确实成功地使用了 cfspreadsheet，但它不会生成带有标题的电子表格(而且它也有需要创建一个临时文件来提供服务的缺点.)
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.)
按照@Leigh 的建议，我更新了 CF9/lib 文件夹中的 POI.错误现在已更改为以下内容:
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)> 给出以下消息:org.apache.poi.hssf.util.HSSFColor.getIndexHash()Ljava/util/Hashtable;
<cfset SpreadsheetFormatRow(xls, {bold=true,fgcolor="brown",color="white"}, 1)> Gives the following message: org.apache.poi.hssf.util.HSSFColor.getIndexHash()Ljava/util/Hashtable;
错误代码:
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)>
错误代码:
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)
推荐答案
我怀疑这与 CF 或 JRE 版本完全没有关系.至少不是直接的.这听起来像是 POI 中的一个错误.
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.
如果您查看异常，它清楚地表明问题发生在 CF 调用尝试自动调整列大小的 POI 方法时(在添加查询数据后).快速搜索发现了一些类似 ArrayIndexOutOfBoundsException 错误的报告，其中包含 HSSFSheet.autoSizeColumn 就像这个(恰好提到了 ColdFusion):
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):
如果您尝试使用 org.apache.poi.hssf.usermodel.HSSFSheet 和设置超过 32767 后的列上的方法 autosizecolumn(int)单元格然后抛出 ArrayOutOfBoundsException.
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.
根据错误报告，该问题存在于 3.5 版本中，该版本与 ColdFusion 9 中包含的(主要)版本相同.一位 POI 开发人员表示该问题已在更高版本中修复.所以你可以尝试更新 POI jar.除此之外，如果您可以组合一个重现问题的测试用例，您可能需要提交错误报告.
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.
我之前确实成功地使用了 cfspreadsheet，但它没有生成一个带有标题的电子表格(它也有需要创建一个临时文件来提供服务.)
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 可能不会像 spreadsheetAddRows 那样尝试自动调整列的大小，因此不会发生错误.因此，显而易见的解决方法(而不是一个很好的解决方法)是避免尝试调整列宽大小的函数.
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.
这篇关于添加大型查询时，如何修复 SpreadSheetAddRows 函数崩溃?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
