tracker issue : CF-3355173

select a category, or use search below
(searches all categories and all time range)
Title:

Spreadsheet operations seem to be limited to 256 columns

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/

Reporter/Name(from Bugbase): Adam Cameron / Adam Cameron (Adam Cameron)

Created: 10/29/2012

Components: Document Management, Office Integration

Versions: 10.0

Failure Type: Data Loss

Found In Build/Fixed In Build: Final /

Priority/Frequency: Critical / Some users will encounter

Locale/System: English / Windows 7 64-bit

Vote Count: 0

Problem Description:
When using <cfspreadsheet> to read in a spreadsheet, only the first 256 columns are returned.

I suspect this is because of the issue detailed here: http://stackoverflow.com/questions/2326668/how-to-get-more-than-255-columns-in-an-excel-sheet-using-apache-poi-3-6.  If using org.apache.poi.hssf.usermodel, the maximum columns is 256; using org.apache.poi.xssf.usermodel instead should resolves this (I have greatly abridged the detail at that link).

Steps to Reproduce:
Read in a spreadsheet with 257 columns

Actual Result:
Only the first 256 columns are returned

Expected Result:
All 257 columns

Any Workarounds:
Not relevant

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3355173

External Customer Info:
External Company:  
External Customer Name: Adam Cameron.
External Customer Email:  
External Test Config: My Hardware and Environment details:

Attachments:

Comments:

I am also encountering this error. I have to generate spreadsheets with more than 256 columns. Is there a way to update ColdFusion to use the org.apache.poi.xssf.usermodel and not the org.apache.poi.hssf.usermodel? Please contact me when/if this is resolved. Thanks!
Comment by External U.
17327 | February 27, 2013 03:25:48 PM GMT
I found, in the documentation, the ShreadsheetNew function has the xmlformat option. SpreadsheetNew([sheetName, xmlformat]) When set to true, the file will be saved as .xlsx and not .xls. The .xlsx format allows for many more columns than 256. Thanks.
Comment by External U.
17328 | February 28, 2013 12:36:01 PM GMT
an excel limitation. http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx expected behavior. closing this. This would work for xlsx, not xls: <cfset valarr = Arraynew(1)> <cfloop from="1" to="10" index="r"> <cfset rowlist = ""> <cfloop from="1" to="300" index="c"> <cfset rowlist = "#rowlist#"&"#r#"&"#c#,"> </cfloop> <cfset valarr[r] = "#rowlist#"> </cfloop> <cfset xlObj = spreadsheetNew("testsheet_xlsx", true)> <cfset spreadsheetAddRows(xlObj, valarr, 1, 1, true)> <cfset rObj = spreadsheetread("#Expandpath("./")#test_01.xlsx", "testsheet_xlsx")> <cfset spreadsheetwrite(rObj, "#Expandpath("./")#test_rw.xlsx", "", true)>
Comment by Piyush K.
17329 | June 11, 2013 02:58:16 AM GMT