Search code examples
excelcoldfusionspreadsheetcoldfusion-11

Spreadsheet Functions converts dates into text

I am using spreadsheet functions for my Excel reports, but it converts dates into text when exported to Excel. So, I used datatype 'Date' in SpreadSheetSetCellValue, so the values in the first column can be converted into any date format the customer wants. Here is the code:

 <cfset SpreadsheetSetCellValue(objSheet, '#RecordDate#', iRow, 1, 'Date')>

This works fine, but when I later apply a background color to alternate rows: 

<cfset stFormat.AlternateRow = StructNew()>
<cfset stFormat.AlternateRow.fgcolor = 'yellow'>

<cfif (iRow mod 2) IS 0>
    <cfset SpreadsheetFormatRow(objSheet, stFormat.AlternateRow, iRow)>
</cfif>

It converts all of the columns in that row to 'date' format. Whereas I only want the 1st column, in each row, to be of 'date' datetype.

Solution

  • Sounds like a bug to me. I'd put together a repro case and submit a bug bugbase.adobe.com.

    Though not ideal, one possible workaround is to set the date column value after formatting the row. Unfortunately setting the cell value also wipes out the background color, so you need to reapply that as well. Tested with CF11, YMMV.

    NB: The workaround doesn't change the order of the columns at all, only when they're formatted. So the "date" still ends up in the 1st column.

    Runnable Example on TryCF.com

    <cfscript>
    objSheet = SpreadSheetNew("Sheet1", true);
    for(iRow = 1; iRow <= 100; iRow++) {

        // populate everything EXCEPT the date column
        SpreadsheetSetCellValue(objSheet, "B", iRow, 2);
        SpreadsheetSetCellValue(objSheet, "C", iRow, 3);
        SpreadsheetSetCellValue(objSheet, "D", iRow, 4);
        SpreadsheetSetCellValue(objSheet, "1234", iRow, 5);

        isAlternateRow = (iRow MOD 2) EQ 0;
        rowColor = isAlternateRow ? 'yellow' : 'white';

        // format whole row 
        if (isAlternateRow) {
            SpreadsheetFormatRow(objSheet, { fgcolor = rowColor }, iRow);
        }

        // finally apply date and reformat that cell
        SpreadsheetSetCellValue(objSheet, now(), iRow, 1, 'date');
        SpreadSheetFormatCell(objSheet, { dataformat = 'mm-dd-yyyy', fgcolor = rowColor}, iRow, 1);
    }
</cfscript>

    Results:

    Results