The addition of the CFSPREADSHEET and all the SpreadSheet functions to ColdFusion 9 was an awesome thing. We have so many apps where clients want an Excel sheet and for whom a CVS is a foreign language. Before CF 9, we used POI to build our spreadsheets, and it worked well though it was slower and coding was clunkier. CF9 seemed to make it a breeze – building out a 3 -4 sheet download could be done in a relatively small amount of code. And there was a great number of functions to really let you “get happy” with building whatever insane spreadsheets you might need to.
On the whole, we’ve welcomed the change and it has been awesome. The XLSX files are smaller, easier to transfer, and it runs fast, except with large data sets and even then it isn’t hideous. However, we have stumbled on what appears to be a really annoying bug. Namely, while the various functions allowed you manipulate individual sheets, format cells and columns, etc. the formatting doesn’t always work well. What triggers this “breakage” though, we have yet to figure out.
We discovered it while building a multiple sheet spreadsheet for a client, which had some dollar figures on several sheets that needed formatting. So we coded it all up and it ran perfectly, except the formatting only “took” on the first sheet – kinda. The formatting of our header row worked perfectly, as did any other changes to the column formats – everything BUT the dataformat (which let’s you tap Excel’s “format cell”) option. So time to run some test scenarios…
Here is a two sheet spreadsheet. On the first sheet, I told it format the last column to a number with appropriate comma separation. On the second sheet, Fake Value should be formatted as a dollar figure.
For the purposes of this post, I am only including the parts related to the spreadsheet, not the queries themselves. And of course the file starts by turning off debugging.
<cfset writeDirectory = left(cgi.cf_template_path,(len(cgi.cf_template_path) - len(cgi.script_name))) />
<cfset myExcelFile = writeDirectory & "/testing_spreadsheets.xlsx" />
<cfset oSpreadsheet = SpreadsheetNew("Book Series", true) />
<cfset SpreadsheetAddRow(oSpreadsheet, "Series ID,Series Name,Number of Titles,Is On Going,Number Held,Total Num Pages") />
<cfset formatCell = StructNew() />
<cfset formatCell.bold = "true" />
<cfset formatCell.bottomborder = "thin" />
<cfset formatCell.bottombordercolor = "black" />
<cfset formatCell.fgcolor = "light_yellow" />
<cfset SpreadsheetFormatCellRange(oSpreadsheet, formatCell, 1, 1, 1, 6) />
<cfset SpreadsheetAddRows(oSpreadsheet, qSeries) />
<cfset formatCell = StructNew() />
<cfset formatCell.dataformat = "######,####0" />
<cfset SpreadsheetFormatCellRange(oSpreadsheet, formatCell, 2, 6, (qSeries.RecordCount+1), 6) />
<cfspreadsheet action="write" filename="#myExcelFile#" name="oSpreadsheet" sheetname="Series" overwrite="true" />
<!--- snip snip --->
<cfset SheetColumns = "Title ID, Title, ISBN, Date Acquired, Num Pages, Format, Category, Series, Fake Value" />
<cfset oSpreadsheet2 = SpreadsheetNew("Registrations", true) />
<cfset SpreadsheetAddRow(oSpreadsheet2, SheetColumns) />
<!--- FORMAT FOR COLUMN HEADERS --->
<cfset formatCell2 = StructNew() />
<cfset formatCell2.bold = "true" />
<cfset formatCell2.bottomborder = "thin" />
<cfset formatCell2.bottombordercolor = "black" />
<cfset formatCell2.fgcolor = "light_yellow" />
<cfset SpreadsheetFormatCellRange(oSpreadsheet2, formatCell2, 1, 1, 1, 9) />
<cfset SpreadsheetAddRows(oSpreadsheet2, qBooks) />
<!--- format dollar values --->
<cfset formatCell2 = StructNew() />
<cfset formatCell2.dataformat = "$######,####0.00" />
<cfset formatCell2.bold = "true" />
<cfset formatCell2.fgcolor = "pale_blue" />
<cfset SpreadsheetFormatCellRange(oSpreadsheet2, formatCell2, 2, 9, (qBooks.RecordCount+1), 9) />
<cfspreadsheet action="update" filename="#myExcelFile#" name="oSpreadsheet2" sheetname="Books" />
Nothing too extreme or anything. But notice the second spread has all other formatting except the dollar format. Now to prove that it was doing something to the column, I added an additional formatting of bold and threw on a blue background.
Yep, that worked. Only the dollar formatting is ignored. Now, for extra fun, if I remove the first sheet….
WTH? And if I switch the order of the sheets….
The Books sheet still has it’s dollar formatting, yay. However, now it is also incorrectly dollar formatting the 6th column of the Series sheet, instead of doing the number format it actually has in the code!
Now if we say okay, fine, do “old” Excel instead of XLSX…then it loses the number formatting all together on both sheets, so it isn’t an issue with that. We also tried doing FormatColumn instead of FormatCellRange, still the same. Then I wondered if it just didn’t like my “custom” format, which is supported by Excel but what the heck. So I grabbed ones from the Adobe Docs. Of course, it failed because they didn’t bother to escape the pounds in their code (yeah, I know – if you got here and are having that issue, double up each pound sign), so fixed that and….nope, no change. So I tried a number format without any pounds signs – still no go. Then I tried just formatting a single, individual cell, and again it doesn’t work.
So what to do? At this point, it seems like this is a bug in ColdFusion 9 – and one I can’t find any information about beyond a single ticket on the old ColdFusion bug tracker that is no where to be found at the newer one. If any of you have encountered this issue and know how to fix it, by all means let me know. For now, it seems our only options are formatting it using MySQL in the query, or looping the query results, formatting with ColdFusion and then reupdating the query. At least, for Excel files with more than one sheet.