如何解决小号preadSheetAddRows功能加入大量查询时崩溃？ [英] How do I fix SpreadSheetAddRows function crashing when adding a large query?
问题描述
EDIT3：由于@Leigh的帮助下我已经缩小的问题在查询日期列。使用原来的$ C $账套和POI，当s preadSheetAddRows（）尝试添加包含日期样细胞非常大的查询页面崩溃。我在这里做了一个错误报告： HTTPS：//bugbase.adobe。 COM / index.cfm事件=错误和放大器;？ID = 3432184
我有我加入，似乎当查询行的unweildly量（18583在这个例子中）误差A S preadhseet对象的查询。确切的错误是：
java.lang.ArrayIndexOutOfBoundsException：-32735
在java.util.ArrayList.get（ArrayList.java:324）
在org.apache.poi.hssf.model.WorkbookRecordList.get（WorkbookRecordList.j AVA：50）
在org.apache.poi.hssf.model.Workbook.getExFormatAt（Workbook.java:787）
在org.apache.poi.hssf.usermodel.HSSFCell.getCellStyle（HSSFCell.java:901）
在org.apache.poi.hssf.usermodel.HSSFSheet.autoSizeColumn（HSSFSheet.java：1727）
在coldfusion.excel.Excel.autoResize（Excel.java:1246）
在coldfusion.excel.Excel.autoResize（Excel.java:1240）
在coldfusion.excel.Excel.addRows（Excel.java:1214）
在coldfusion.runtime.CFPage.S preadSheetAddRows（CFPage.java:7089）在coldfusion.runtime.CFPage.S preadSheetAddRows（CFPage.java:7076）
下面是有关的code：
＆LT; CFSET XLS = S preadsheetNew（）＆GT;
＆LT; CFSET US preadsheetAddRow（XLS，arrayToList（qryTest.getMeta（）getColumnLabels（）））＆GT;
＆LT; CFSET US preadsheetFormatRow（XLS，{大胆= TRUE，fgcolor =棕色，颜色=白色}，1）＆GT;
＆LT; CFSET US preadsheetAddRows（XLS，打开qryTest）GT;
＆LT; cfheader名=内容处置VALUE =附件;文件名=＃＃文件名＆GT;
＆LT; cfcontent变量=＃小号preadsheetReadBinary（XLS）＃复位=是TYPE =应用程序/ vnd.ms - Excel的＆GT;
编辑：（它也有需要创建一个临时文件服务的缺点）我没有使用CFS preadsheet previously成功，但它不会产生与头preadsheet
EDIT2：继@Leigh建议我在CF9 / lib文件夹更新POI。现在错误已经更改为以下内容：
＆LT; CFSET US preadsheetFormatRow（XLS，{大胆= TRUE，fgcolor =棕色，颜色=白色}，1）＆GT; 给出以下消息：org.apache.poi.hssf.util.HSSFColor.getIndexHash（）Ljava / UTIL /哈希表;
错误code：
java.lang.NoSuchMethodError：
org.apache.poi.hssf.util.HSSFColor.getIndexHash（）Ljava / UTIL /哈希表;
在coldfusion.excel.Excel.getHSSFColor（Excel.java:2094）
在coldfusion.excel.Excel.findFont（Excel.java:2237）
在coldfusion.excel.Excel.getCellStyle（Excel.java:2318）
在coldfusion.excel.Excel.formatRow（Excel.java:2948）
在coldfusion.excel.Excel.formatRow（Excel.java:2963）
在coldfusion.excel.Excel.formatRow（Excel.java:2981）
在coldfusion.runtime.CFPage.S preadSheetFormatRow（CFPage.java:7268）
在谈到这一行了，现在再次崩溃于：＆LT; CFSET US preadsheetAddRows（XLS，打开qryTest）GT;
错误code：
java.lang.IllegalStateException：超出的单元格样式的最大数量。您可以在.xls的工作簿中定义多达4000个款式
在org.apache.poi.hssf.usermodel.HSSFWorkbook.createCellStyle（HSSFWorkbook.java:1120）
在org.apache.poi.hssf.usermodel.HSSFWorkbook.createCellStyle（HSSFWorkbook.java:73）
在coldfusion.excel.Excel.addRow（Excel.java:1323）
在coldfusion.excel.Excel.addRows（Excel.java:1203）
在coldfusion.runtime.CFPage.S preadSheetAddRows（CFPage.java:7089）
在coldfusion.runtime.CFPage.S preadSheetAddRows（CFPage.java:7076）
我怀疑它无关，与CF或JRE版本都没有。至少没有直接。这听起来像POI的错误。
如果你看一下例外它清楚地表明了问题发生在CF调用试图自动调整列（添加查询数据之后）POI方法。快速搜索打开了类似 ArrayIndexOutOfBoundsException异常错误的几份报告与 HSSFSheet.autoSizeColumn 像这样的（这恰好提的ColdFusion）：
如果您尝试使用org.apache.poi.hssf.usermodel.HSSFSheet和
设置超过32767后法autosizecolumn（INT）在列
细胞然后ArrayOutOfBoundsException异常。据bug报告在3.5版本中存在的问题，这是包括使用ColdFusion 9.其中一个POI开发商相同的（主要）版本说明这个问题被固定在以后的版本。所以，你可以尝试更新POI罐子。除此之外，如果你可以放在一起重现这一问题的测试用例，你可能要提交 bug报告。
我确实使用了CFS preadsheet previously成功，但它不
产生A S preadsheet用头（和它也有缺点
需要创建一个临时文件服务）。
CFS preadsheet可能不会尝试自动调整，比如取值preadsheetAddRows列呢，因此出现任何错误。所以很明显的解决方法（而不是一个伟大的）是避免试图调整列宽功能。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)
解决方案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
ArrayIndexOutOfBoundsExceptionerrors withHSSFSheet.autoSizeColumnlike 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.)
cfspreadsheetprobably does not attempt to automatically resize the columns likespreadsheetAddRowsdoes, hence no error occurs. So the obvious workaround (and not a great one) is to avoid functions that attempt to resize the column widths.这篇关于如何解决小号preadSheetAddRows功能加入大量查询时崩溃？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
