Coldfusion generated spreadsheet does not allow column formating

Question

When creating a spreadsheet from a query, the spreadsheet that is produced will not allow a column of a datetime datatype to be formatted.

The spreadsheet creation is:

    <cfset filenametouse = 'AllCaseData' />
    <!---Set directory and full file path--->
    <cfset theDir = GetDirectoryFromPath(GetCurrentTemplatePath()) /> 
    <!---Attach file extension to full file path and file name--->
    <cfset theFile = theDir & filenametouse & ".xls" /> 
    <cflock name="fileActionSentItems" type="exclusive" timeout="30" throwontimeout="true">
        <cfset SpreadsheetObj = spreadsheetNew()>
        <!---<cfset fcol = {}>
        <cfset fcol.dataformat = "@">--->
        <!---Create the column headings--->
        <cfset SpreadsheetAddRow(SpreadsheetObj, "Case_Number,Ship_Start,Projected_$,VA_$,Customer_ID,Customer,Project_Description,Stage,Sales,PM,J_Count,O_Count,S2_Count,S_Count,I_Count")>
        <!---add the column data to the spreadsheet--->
        <cfset SpreadsheetAddRows(SpreadsheetObj, caseData) />

        <cfset SpreadsheetFormatColumn(SpreadsheetObj, {dataformat="m/d/yyyy"}, 2)>


        <!---Generate the spreadsheet--->
        <cfspreadsheet action="write" filename="#theFile#" name="SpreadsheetObj" sheetname="Sheet1" overwrite="true" />

    </cflock>
    <cftry>
        <cflock name="fileActionSentItems" type="exclusive" timeout="30" throwontimeout="true"> 
            <cfmail to="#session.authUser.getEmail()#" from="noreply@pratt.com" subject="All Case Data" type="html" >
                <cfmailparam file="#theFile#" remove="true" />
                <p>The requested data is attached.</p>
            </cfmail>
        </cflock>
        <cfcatch type="any" >
            <cffile action="delete" file="#theFile#" /> 
        </cfcatch>
    </cftry>
    <cfoutput><h3 style="margin-top: 1em;">Email sent to <cfoutput>#session.authUser.getEmail()#</cfoutput></h3></cfoutput>

All the data is correct and in the correct columns, however the "Ship_Start" column comes formatted similar to 2012-11-27 00:00:00.000 and cannot be changed to any other format in Excel.

If I run the query manually in SQL Server Management Studio then copy the results with headers into Excel, the data can be formatted. I have also created an Excel template with the column headers formatted and populated the template with the data, but the column cannot be formatted even though it is formatted with "date."

What could I be missing?

What version of ColdFusion are you running? Can you format any of the columns in the spreadsheet? Have you tried formatting the column using [SpreadsheetFormatColumn](http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6806.html) ? — Miguel-F, Nov 19 '12 at 18:59
This app resides on a CF9 box. I just finished testing with SpreadsheetFormatColumn and it affects all columns except "Ship_Start." I've noticed in the Excel file, there looks like there's a space after the data in the column: "2012-11-27 00:00:00.0 " — aparker81, Nov 19 '12 at 19:34
My question has been updated with the SpreadsheetFormatColumn added. The full CF version is 9,0,1,274733. — aparker81, Nov 19 '12 at 19:47
Interesting...I am also running CF9 and just checked a spreadsheet that I had created with ColdFusion and that also contains a date column. I cannot change the format of that column either. My data is already in "m-d-yyyy" format and does not have any spaces before or after. I found that if I enter one of the date cells to edit it but don't make any changes, when I exit the field the format changes to "m/d/yyyy" and I can now format that cell. I will keep looking to see what I can find. Does your spreadsheet also exhibit this behavior? — Miguel-F, Nov 19 '12 at 20:21
@Miguel-F - I can replicate what you described. The behavior is the same. — aparker81, Nov 19 '12 at 20:28
Did you see this other similar question [How to format spreadsheet columns using ColdFusion?](http://stackoverflow.com/questions/3081202/how-to-format-spreadsheet-columns-using-coldfusion) Seems like a hack but might work for you. The theory is that the spreadsheet gets created first with dummy values, then you format the column how you want, then replace the dummy values with the actual values and they should be formatted. (Man I really don't like that.) — Miguel-F, Nov 19 '12 at 20:31

Miguel-F · Accepted Answer · 2012-11-19T21:03:27.660

This appears to be a bug. There is some pretty good discussion about this on Raymond Camden's blog - HERE. The discussion is in the user comments at the bottom. It looks like Ray actually entered the bug for this. I think I found it - HERE.

I found two of the comments that might help you. They are workarounds but maybe one will get you moving. I have BOLDED the parts of the comments that I think apply but you might want to read through all of those comments on Ray's blog anyway to get them in context.

*** BEGIN QUOTED BLOG ENTRIES ***

First comment posted by Les Konley Said On 06-20-2011 At 11:12 AM

Ray & James, I have additional information about the CFSPREADSHEET and Excel not recognizing resulting date fields. First of all, our code is both using a query object as well as setting explicit date formats [i.e, cfset temp = QuerySetCell(GetExcelReport, "FollowUpDate", "DateFormat(GetResult.FollowUpDate, "MM/DD/YYYY")) ]. When we open the spreadsheet in Excel (built using CFSPREADSHEET), the date column is formatted as regular text or general (left-justified). On this site (http://www.appnitro.com/forums/topic/excel-does-no...) a work-around is offered, so the business users can highlight the column (within Excel), then select Data > Text to Columns, then click FINISH on the Wizard (without needing to step through it)... and date fields are now recognized by Excel as a DATE. We may need to revert back to installing MS Office on the production servers and continue using the old CFX_Excel tag if the business users decide they don't want this added minor inconvenience, at least until that particular bug is corrected on a future release. Thanks for submitting a bug report to Adobe about this issue.

Second comment posted by Dan Murphy Said On 07-22-2011 At 10:44 AM

James, Ray, and Les - I know I'm a bit late to the game here, but I think I just found a workaround for the date formats in Excel that has been working out well for me. First, I'm creating my own query with querynew based on another query and using that to export to Excel. If I make the datatype in the querynew to cf_sql_varchar instead of date or cf_sql_timestamp and then output the query to the Excel spreadsheet, it will take whatever format you give it. If you stop there, Excel will still see the data as just text, not an actual date (although formatted correctly). Second, just set the dataformat using spreadsheetFormatColumns to match the format you specified in the query, and Excel now recognizes it all as a date. Ray, I'm using your DateTimeFormat function from CFLib, so here is how it all worked out for me. (this is just part of it all obviously, but you'll catch the idea).

Here's the query...

<cfset mys = QueryNew("OrderNumber,InScan,OutScan","cf_sql_varchar,cf_sql_varchar,cf_sql_varchar")>
<cfloop query="qryThroughputScans">
<cfset queryAddRow(mys)>
<cfset querySetCell(mys,"OrderNumber",NumberFormat(qryThroughputScans.OrderNumber,000000))> 
<cfset querySetCell(mys,"InScan",DateTimeFormat(qryThroughputScans.InScan))> 
<cfset querySetCell(mys,"OutScan",DateTimeFormat(qryThroughputScans.OutScan))>
</cfloop>

And then the column format that I use after creating the Excel spreadsheet...

<cfset spreadsheetFormatColumns(s,
{
alignment="center",
dataformat="m/d/yyyy h:mm AM/PM"
}, 
"2-3")>

I tested this out with all sorts of weird formats just to make sure it works, and I had great success with it. Maybe this is already well known but I couldn't find anything when I was looking for a fix. Hopefully this helps someone out when they go looking for an answer to the date issue.

Thanks, Dan

*** END QUOTED BLOG ENTRIES ***

Yep, it looks like you're right. It's been driving me crazy all day. I will recommend to the user to utilize the text to columns method as he needs to format the data himself. Thanks for the assistance! — aparker81, Nov 19 '12 at 21:50

score 1 · Answer 2 · answered Nov 19 '12 at 22:27

Unfortunately the formatting support is lacking. If you are running windows, you could simply do it yourself with a loop and SpreadSheetSetCellValue:

<cfset obj = spreadsheetNew()>
<cfset SpreadSheetAddRow(obj, "Case_Number,Ship_Start")>
<cfset SpreadSheetAddRows(obj, yourQuery)>
<cfset spreadsheetFormatColumns(obj, {dataformat="m/d/yyyy"}, 2)>
<cfloop query="yourQuery">
    <cfset theValue = yourQuery.Ship_Start[currentRow]>
    <cfif IsDate(theValue)>
         <!--- offset by 1 row for header --->
         <cfset SpreadSheetSetCellValue(obj, theValue.toDouble(), currentRow+1, 2)>
    </cfif>
</cfloop>

<cfset SpreadSheetWRite(obj, theFilePath, true)>

Coldfusion generated spreadsheet does not allow column formating

2 Answers2