tracker issue : CF-3640428

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

Using SpreadSheetFormatCellRange and other SpreadSheetFormat functions may create corrupted Excel spreadsheets

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): John Dobbins / John Dobbins (John Dobbins)

Created: 09/26/2013

Components: Document Management, Office Integration

Versions: 10.0

Failure Type: Data Corruption

Found In Build/Fixed In Build: Final /

Priority/Frequency: Critical / All users will encounter

Locale/System: English / Windows 7 64-bit

Vote Count: 1

Problem Description:

When creating a spreadsheet using the cfscript SpreadSheet functions, any time a format is applied to a cell, a separate Excel style is created and applied to that cell, even if the same format is used on other cells.  Therefore, if you format 100 cells, CF generates 100 style definitions in the "styles.xml" file, with unique references to each style in the XML file containing the sheet.  Since I'm formatting over 65,000 cells, CF is generating that many style definitions and is apparently exceeding the Excel maximum for the number of styles.

Opening the generated spreadsheet results in the error "Excel found unreadable content in 'bigSheetTest.xlsx'.  Do you want to recover the contents of this workbook?".

Steps to Reproduce:

This error may be reproduced using the uploaded cfm file (note that the file writes the geneated spreadsheet to the root of the C: drive; change this as appropriate).  With the "maxRows" variable set to 725, opening the generated sheet will result in an Excel error.  However, setting the value to 724 will produce a valid spreadsheet.

Actual Result:

By viewing the Excel "styles.xml" and "sheet1.xml" files, it is clear that every formatted cell has a separate style definition, even those cells formatted using the SpreadSheetFormatCellRange function.

Expected Result:

Only unique style definitions should be created.  In the sample code, I'm only using four different styles, and yet over 65,000 style definitions are being created.

Any Workarounds:

None that I am aware of.

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

Watson Bug ID:	3640428

External Customer Info:
External Company:  
External Customer Name: jbdobs
External Customer Email:  
External Test Config: My Hardware and Environment details:

Windows 7 Enterprise (64 bit) with 8 Gig of RAM, Intel Core i7-3520M CPU @ 2.90GHz

Microsoft Office Professional Plus 2010

Microsoft Excel Version 14.0.7106.5001 (32-bit)


System Information

Server Details

Server Product	ColdFusion

Version	ColdFusion 10,285437

Edition	Developer  

Operating System	Windows 7  

OS Version	6.1  

Update Level	/C:/ColdFusion10/cfusion/lib/updates/chf10000011.jar  

Adobe Driver Version	4.1 (Build 0001)  




JVM Details

Java Version	1.7.0_15  

Java Vendor	Oracle Corporation  

Java Vendor URL  

Java Home	C:\ColdFusion10\jre  

Java File Encoding	Cp1252  

Java Default Locale	en_US  

File Separator	\  

Path Separator	;  

Line Separator	Chr(13)  

User Name	F1610-7X7HKX1-L$  

User Home	C:\  

User Dir	C:\ColdFusion10\cfusion\bin  

Java VM Specification Version	1.7  

Java VM Specification Vendor	Oracle Corporation  

Java VM Specification Name	Java Virtual Machine Specification  

Java VM Version	23.7-b01  

Java VM Vendor	Oracle Corporation  

Java VM Name	Java HotSpot(TM) 64-Bit Server VM  

Java Specification Version	1.7  

Java Specification Vendor	Oracle Corporation  

Java Specification Name	Java Platform API Specification  

Java Class Version	51.0


  1. September 27, 2013 00:00:00: 1_bigSheetTest.cfm


More information on this problem (including the generated Excel XML for one row of the output spreadsheet) may be found in the following forum discussion:
Comment by External U.
14434 | September 26, 2013 03:04:24 PM GMT
Thank you Anuj, I have a few questions. 1) Would you please email me the spreadsheet so I can examine it? 2) Is there a "hot fix" we can apply to our existing CF 10 installation to fix this problem? 3) Is there a release date for next version? 4) We just purchased CF 10 a few months ago. Will we be able to upgrade to next without having to purchase another license? Thank you.
Comment by External U.
14435 | September 30, 2013 03:06:45 PM GMT
This is a critical issue impacting an application I'm about to place into production for my customer. Could I please get a response to the questions in my post of 30 September? I'm in desperate need of a hot fix or other work around. Thank you
Comment by External U.
14436 | October 14, 2013 09:52:51 AM GMT
@jbdobs, If you need any urgent fix for this, I would recommend you to get in touch with the ColdFusion support as that is the easiest and quickest way to get it.
Comment by Rupesh K.
14437 | October 16, 2013 05:57:30 AM GMT
Hello there, alltough this bug is rather old, I want to ask something about it. Currently I have the same probelm, that my Excel sheet gets "corrupted" if a certain number of rows is reached, actually it's about 2900 rows in my case. I used spreadsheetformatColumn to format certain columns to be currency, number and so on. Before I use spreadSheetFormatRow to set the Background Color of the whole row to "dark Grey". I found the bug and changed my code to use SpreadSheetFormatCellRange instead of spreadsheetformatColumn and spreadsheetformatRow, hopeing, my issue would be fixed with this. but for a pitty I'm still stuck :-( My System is as follows: Server Product ColdFusion Version ColdFusion 10,283922 Edition Developer Operating System Windows 8 OS Version 6.2 Is the bugfix for the BUG #CF-3640428 already contanied in my Version? If not, how can I get the bugfix? Is there any other way to get out of my Situation? Thanks a lot and Kind regards Marcus
Comment by External U.
14438 | July 29, 2015 08:21:53 AM GMT
Sould be fixed in CF 10, because we still use Cf 10 as production Version.
Vote by External U.
14442 | July 29, 2015 09:15:38 AM GMT
I see this bug is now closed with a status of "fixed", but I don't see anything in the notes that provides information about the fix. What was the fix, and in what release was this bug fixed? Thanks, John
Comment by External U.
14439 | August 04, 2015 09:52:13 AM GMT
Isn't there a bugfix for that, is it? Getting very upset with the spreadsheet function if the ofrmatting breaks with about 2000 lines and mir than 10 columns to be formatted.
Comment by External U.
14440 | March 07, 2016 08:14:53 AM GMT
What version of Coldfusion was this fixed in?
Comment by External U.
14441 | October 27, 2016 02:30:58 PM GMT