Date Formatting Options do not work in Spreadsheet Functions

While I can appreciate the semantic argument on whether it is or is not a "bug", the old axiom "garbage in, garbage out" comes to mind. Pass Excel data it does not expect for its rules of date usage, you get undesired results. Period.

Since CF's built in toolset does not automatically convert this for you, we are (unfortunately) left doing the heavy lifting.

Would it be "nice" and "helpful" if Adobe's SpreadsheetFormatColumn (and related functions) automatically convert dates to numeric dates? Unquestionably. But, at the end of the day, there are two choices:

1) live without functional dates in spreadsheets you create

OR

2) convert your data and have the desired results

If you are uncomfortable with the SQL based fix, just add a zero time span to any CF date and you get an accurate real-number based date that SpreadsheetFormatColumn works PERFECTLY with to create function and visually desired date columns. An example of using a zero-value CreateTimeSpan is:

<cfset myDateObject = Now() />

<cfset myNumericDate = (myDateObject + CreateTimeSpan( 0, 0, 0, 0 )) />

<cfoutput>

  myDateObject :: #myDateObject#<br />

  myNumericDate :: #myNumericDate#<br />

  IsDate() :: #IsDate( myNumericDate )#<br />

  IsNumericDate() :: #IsNumericDate( myNumericDate )#<br />

</cfoutput>

Or you could just as easily make it component based:

utilities.cfc

-------------

<cfcomponent>

   <cffunction name="dateToExcel" access="public" returntype="numeric">

      <cfargument name="dateToConvert" type="date" required="yes">

      <cfreturn (Arguments.dateToConvert + CreateTimeSpan( 0, 0, 0, 0 ))>

   </cffunction>

</cfcomponent>

excelDate.cfm

-------------------

<cfobject component="test.utilities" name="utilities">

<cfset myDateObject = now()>

<cfset myNumericDate = utilities.dateToExcel(myDateObject)>

<cfoutput>

   myDateObject :: #myDateObject#<br />

   myNumericDate :: #myNumericDate#<br />

   IsDate() :: #IsDate( myNumericDate )#<br />

   IsNumericDate() :: #IsNumericDate( myNumericDate )#<br />

</cfoutput>

Yep, that description of the bug is far more accurate. My objection was to the statement that CF's is not the cause, which is incorrect.

it should be put in the spreadsheet as a date

Yes. As I mentioned earlier, Cell.setCellValue(java.util.Date) works with CF date objects and does exactly that. But it actually does two things. First, it converts the date to a value Excel understands. Second, it sets the cell type to numeric (what Excel uses for dates) so Excel applies formatting properly.

Now I agree with most of what you said. What I was disagreeing with (albeit poorly) is the notion that just passing in a date number alone is what magically makes the formatting work. Take your example. Passing in that number is only part of why it produces the desired the results. The other reason is that CF also instructs POI to make the cell "numeric".  Change it to "string" and the initial display is wrong again. Or at least not what you would expect.

<cfscript>
     ts = now();
     stFormat = {dataformat="yyy-mm-dd"};
   
     oSpreadsheet = spreadsheetNew();
     spreadsheetSetCellValue(oSpreadsheet, date2Excel(ts), 1, 1);
     spreadsheetSetCellValue(oSpreadsheet, date2Excel(ts), 1, 2);

    // set cell type to string
     jSheet = oSpreadsheet.getWorkbook().getSheetAt(0);
     jCell = jSheet.getRow(0).getCell( 1 );
     jCell.setCellType( jCell.CELL_TYPE_STRING );
   
     spreadsheetFormatCell(oSpreadsheet, stFormat, 1, 1);
     spreadsheetFormatCell(oSpreadsheet, stFormat, 1, 2);
   
     // show cell types
     for (col = 1; col <= 2; col++)     {
         jCell = jSheet.getRow(0).getCell(col-1);
         spreadsheetSetCellValue(oSpreadsheet, getCellTypeName(jCell), 2, col);
     }

    string function getCellTypeName(any cell) {
         var type = cell.getCellType();
         if (type == cell.CELL_TYPE_NUMERIC) {
             return "CELL_TYPE_NUMERIC";
         }
         else if (type == cell.CELL_TYPE_STRING) {
             return "CELL_TYPE_STRING";       
         }
         // ignore the rest for our test case
         return "**OTHER**";       
     }
   
     numeric function date2Excel(date d){
         var iDayPart = dateDiff("d", createDate(1899, 12, 30), arguments.d);
         var fTimePart = (hour(arguments.d) / 24) + (minute(arguments.d) / (24*60)) + (second(arguments.d) / (24*60*60));
         return iDayPart + fTimePart;
     }
</cfscript>

So yes values do play an important role. But if we are talking accuracy, strictly speaking the issue involves more than just the value. You can apply all the formatting you want. But if CF does not also set the correct cell type, it will be ignored.

-Leigh