tracker issue : CF-4204270

Chad, The excel file you've shared has a hidden sheet called "page". Col "I" in that sheet has formulae that follow pattern similar to the following: =INDEX(C:C,MATCH(G2,A:A)) Here the address space C:C and A:A is quite exhaustive. CF internally uses POI library for spreadsheet manipulation, and it's running into memory outage when reading cell with such formulae. You can avoid the issue by using a more restricted search space such as A1:A1500. Let us know if this works to solve the issue.

Comment by Piyush K.

No, it doesn’t. There is no documented way to restrict search like that with cfspreadsheet. Suggestions: Add that capability to cfspreadsheet tag OR Create a new method that pulls high level information about a spreadsheet. Items like Total sheets, Total hidden sheets, total records per sheet. That information would allow any developer to quickly scan documents prior to a full read.

Comment by Chad S.

Piyush, first let me thank you for looking at this issue. I've had some more thoughts but let me state my goals here as your team might find alternative solutions to meet those goals When using cfspreadsheet: 1) Make sure files like these can't crash ColdFusion servers (or seriously load them down) 2) Add the capability to identify a host of format/template/etc issues prior to reading the entire document to memory. After looking at POI documentation the following should be possible and would be AMAZING to developers. It would make a newsworthy impact for ColdFusion developers with relatively little effort. Here's the link I used: https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Workbook.html Item #1 Create New Method: ReadTopLevel (or whatever you want to call it) This creates a Struct/Array with the following information: (Top Level) - Total Sheets/Tabs [ getNumberOfSheets() ] - Total Hidden Sheets/Tabs [ isSheetHidden() AND isSheetVeryHidden() ] - Total Hidden Columns in Sheets/Tabs (Top Level Sheet Data) Sheet/Tab - Name [ getSheetName() ] - Visibility [ getSheetVisibility() ] - Total Rows [ getPhysicalNumberOfRows() ] - Total Columns [ read first row only, getRow(0).getPhysicalNumberOfCells() ] - Column Names [ cell.getSheet().getRow(0).getCell(currentcellIndex) .getRichStringCellValue().toString() ] Item #2 Add functionality to cfspreadsheet would allow us to: - Read from only visible Sheets/Tabs - Read from only the first Sheet/Tab - Read from only Sheet/Tab with name "X" - Read only the top X rows (developers understand java starts with 0). Thank you so much for working with this issue.

Comment by Chad S.

Chad, First of all, let me apologize for the delay in getting back to you on this. Now, most of the functionality that you've requested already is in existence. For example. spreadsheetInfo function returns information such as no. of sheets and their names. You can find the exhaustive list of spreadsheet related CF functions at https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-s/spreadsheetinfo.html You can also selectively read data for a specific sheet by specifying the sheetname or sheet (number) attribute. You may refer the docs on spreadSheetRead for details. For any information related to the data in the spreadSheet, you can read the data into a query first and do the required processing for the information you need. For example, for "getPhysicalNumberOfRows", you may read the data into a query and get the recordCount Now in cases where a large amount of data in a file that can lead to memory overruns, one cannot pull information such as no. of records in a sheet without loading the data first. That there is a catch 22 kind of a situation. The only functionality that I see missing is to identify hidden sheets. SpreadsheetInfo returns amongst other things a list of all sheets whether they're hidden or not.

Comment by Piyush K.

Changing this to an enhancement. Looks like we can use some new function to set the visibility of sheets, and also return the visibility status of sheets. That info can be appended to the struct returned by spreadsheetInfo as well.

Comment by Piyush K.