Coldfusion Spreadsheet cell formatting

1
votes

I need to generate excel spreadsheet with coldfusion 10 from a query. So far its working fine except the headers of the table. They are dynamically generated [month Year] December 2012. When I add a header column I get it in the date format like '01/12/2013'. There are other types of date formatting and other types of cell formatting. How do I force a cell to format as a string. Kind of when you add single quotation.

This is my code:

<cfset VARIABLES.vcFilename = "billtotals_" & DateFormat(Now(), "yyyymmdd") & "-" & TimeFormat(Now(), "HHmmss") & ".xls">
<cfset VARIABLES.sheet = SpreadSheetNew( "Summary" )>
<cfset VARIABLES.columns = arrayToList(GetBillPremTotals.getMeta().getColumnLabels())>
<cfset SpreadSheetAddRow( VARIABLES.sheet,VARIABLES.columns)>

<cfset format1 = StructNew()>
<cfset format1.bold = "true">
// tried this just for kicks doesn't work <cfset format1.dataformat = "'">
// tried this converts to a number <cfset format1.dataformat = "text">
<cfset SpreadsheetFormatRow(VARIABLES.sheet, format1, 1)> 

<cfset SpreadSheetAddRows(VARIABLES.sheet,GetBillPremTotals)>

<cfset SpreadSheetAddRows(VARIABLES.sheet,GetBillPremGrandTotals)>
<cfset VARIABLES.sheetAsBinary = SpreadSheetReadBinary(VARIABLES.sheet)>

<cfheader name="Content-Disposition" value="attachment; filename=#Chr(34)##VARIABLES.vcFilename##Chr(34)#">
<cfcontent type="application/msexcel" variable="#VARIABLES.sheetAsBinary#" reset="true">

This is a table with data from query analyzer, displays fine in the browser in html format

No          November 2012                           December 2012                           January 2013                            February 2013
----------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
25          60117.56000                             61515.17000                             60791.62000                             60745.29000
28          1564.69000                              1564.69000                              1564.69000                              1590.44000
30          110599.11000                            173954.08000                            178064.11000                            172892.65000

Thank you in advance, Gena

1
excelcoldfusionspreadsheet
You didn't show the code that you claim to generate strings such as December 2012. What is that code? What happens if you run it and output the result in a browser? - Dan Bracuk
I cannot test it right now, but IIRC, to format as text use either dataFormat="text" or dataFormat="@" , then apply the value. - Leigh
@Dan Bracuk I updated my question with the output from query analyzer - user1706426
@Leigh I tried both text and @. They both format headers as numbers. - user1706426
Try applying the format before you add the column header row. If its auto-converting the value to a date, applying the format after the fact will not help. - Leigh

1 Answers

1
votes

This did the trick. 

<cfset format1.dataformat = "mmmm yyyy">