Date Formatting Options do not work in Spreadsheet Functions

[Dave_Miller]

The date format specified in the ColdFusion SpreadSheetFormatCell does not work. I need to format the dates as mm/dd/yy. None of the options work.

[-__cfSearching__-]

It is a bug (see the comments here and bug report #86948).  The mm/dd/yyy format is being stored. But CF is setting the cell's type to string/text instead of a date/number. So excel thinks the value is text and ignores the formatting. At least initially. If you click inside the cell and update it, the format is applied.

[Adam Cameron.]

Sorry, I disagree that the issue as described is a bug. The formatting functions work fine, provided the data in the cell is appropriate to the formatting (as mentioned elsewhere on this thread).

The problem is with how CF passed the data to Excel: if the CF data is a date (ie: a date object or a query column of type date), then when the value is put in the spreadsheet, it should be put in the spreadsheet as a date (what Excel understands to be a date).  What it should not to is just cast the date to a string (ending up with something like {ts '2011-08-21 12:41:43'}).

So there's a shortfall in CF's spreadsheet functionality, sure.  But that issue in the bug tracker is not correct.

I have raised another bug - http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html?#bugId=87056 - which states:

{quote}

Summary: spreadsheetSetCellValue() and other cell-value setting functions should be data-type aware

The datatype of the data being put in the spreadsheet should be respected.  If it's a date I am putting in, then it should be put in as a date.  Date data is currently just being cast to a string and inserted as a string (eg: {ts '2011-08-21 12:41:43'}, which is not a date as far as Excel is concerned).

Just casting everything as a string has a knock-on effect, as detailed in bug 86948.  I think the expectations described in that issue are valid, if not how it's been reported.

Steps to reproduce:

<cfscript>
    ts = now();
   
    stFormat = {dataformat="yyy-mm-dd hh:mm:ss"};
   
    oSpreadsheet = spreadsheetNew();
    spreadsheetSetCellValue(oSpreadsheet, ts, 1, 1);
    spreadsheetSetCellValue(oSpreadsheet, date2Excel(ts), 1, 2);
   
    spreadsheetFormatCell(oSpreadsheet, stFormat, 1, 1);
    spreadsheetFormatCell(oSpreadsheet, stFormat, 1, 2);
   
    spreadsheetSetColumnWidth(oSpreadsheet, 2, 20);
   
    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>
<cfheader name="Content-Disposition" value="attachment; filename=dateTest_#timeFormat(now(), "HHMMSS")#.xls">
<cfcontent type="application/vnd-ms.excel" variable="#spreadsheetReadBinary(oSpreadsheet)#" reset="true">

{quote}

Pls go vote for it so we can encourage Adobe to sort it out.

--
Adam

[-__cfSearching__-]

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

[tooMuchTrouble]

numeric function date2Excel(date d){

var iDayPart=int(d); // excel epoch offset in integer days

var fTimePart=iDayPart-d; // remainder in decimal days

return iDayPart + fTimePart;

}

iDayPart=40777

fTimePart=0.362037037034

returns 40777.362037

{ts '2011-08-22 08:41:20'}

[tooMuchTrouble]

curious, i just cracked open coldfusion.runtime.OleDateTime and if nothing else

it proves the cf team has a sense of humor. that class definitely has changed

since the last time i looked (at the beginning of my battles w/timezone hell).

in any case you can short circuit that whole function to just

return d.toDouble();

[Adam Cameron.]

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.

Well I suppose.  But what you're saying is if CF did it differently to how it in fact does do it... if it did it wrong instead of right... then it'd be wrong.  Which is a bit of a truism, innit?

But it doesn't do it wrong, so I don't see how that's relevant.

If I pass in a "date number" (for lack of a better term!) into Excel, and then set the format to be some date mask, it works.  I do not have to mung with data types or anything like that.

I suppose yes, if I passed a number in, the explicitly set the type of the cell to be wrong, then maybe the formatting wouldn't work.  But if I go out of my way to mess stuff up, should I not expect the end results to be messed up?

--

Adam

[-__cfSearching__-]

If I pass in a "date number" (for lack of a better term!)

into Excel, and then set the format to be some date mask, it

works.  I do not have to mung with data types or anything

like that.

Um, it is still happening in the background. You are talking about Excel after all. It does a lot of things for you automatically. Check your cell types. You will see the cell type was automatically adjusted for you to indicate the cell contains some sort of date.

But it doesn't do it wrong, so I don't see how that's relevant.

It is only relevant to understanding the issue and how date formatting actually works in POI.

Message was edited by: -==cfSearching==-

[Adam Cameron.]

I have raised another bug - http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html?# bugId=87056 - which states:

{quote}

Summary: spreadsheetSetCellValue() and other cell-value setting functions should be data-type aware

The datatype of the data being put in the spreadsheet should be respected.  If it's a date I am putting in, then it should be put in as a date.  Date data is currently just being cast to a string and inserted as a string (eg: {ts '2011-08-21 12:41:43'}, which is not a date as far as Excel is concerned).

Just casting everything as a string has a knock-on effect, as detailed in bug 86948.  I think the expectations described in that issue are valid, if not how it's been reported.

Steps to reproduce:

<cfscript>
    ts = now();
   
    stFormat = {dataformat="yyy-mm-dd hh:mm:ss"};
   
    oSpreadsheet = spreadsheetNew();
    spreadsheetSetCellValue(oSpreadsheet, ts, 1, 1);
    spreadsheetSetCellValue(oSpreadsheet, date2Excel(ts), 1, 2);
   
    spreadsheetFormatCell(oSpreadsheet, stFormat, 1, 1);
    spreadsheetFormatCell(oSpreadsheet, stFormat, 1, 2);
   
    spreadsheetSetColumnWidth(oSpreadsheet, 2, 20);
   
    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>
<cfheader name="Content-Disposition" value="attachment; filename=dateTest_#timeFormat(now(), "HHMMSS")#.xls">
<cfcontent type="application/vnd-ms.excel" variable="#spreadsheetReadBinary(oSpreadsheet)#" reset="true">

{quote}

Pls go vote for it so we can encourage Adobe to sort it out.

Just FYI: I just got an email from Adobe saying this is flagged to be fixed for CF10 beta 1.

--

Adam

[Guest]

Although there is an open bug report, this is in actuality not a "bug" per se.

Technically, it is NOT ColdFusion’s “fault” (but it is a problem).

Excel considers dates as a floating-point value, measuring days from midnight, 30 December 1899. I don’t believe that is technically a “Julian date” as that measures from 01 January 4713 BC. But, the concept is the same.

Hence, SpreadsheetFormatColumn actually works PERFECTLY *if* you present it with the correctly formatted data – a floating-point value as specified above – NOT a dateTime value from a DB.

ALSO, this cures the problem of how Excel sorts the dates you place via cfspreadsheet. (if you attempt simply casting the dateTime as a VarChar, Excel will DISPLAY it correctly, but it will not recognize it as a date and only perform alpha sorts, which are not chronological).

SO, here is the solution for properly converting SQL returned data to a date integer that Excel will understand (and format correctly with the various SpreadsheetFormat functions):

In your SQL select, you must cast the dateTime as a “float” as follows:

select

  floor( cast(myDateColumn AS float ) )  as myDateColumn

from

  myTable

THEN, when you apply the SpreadsheetFormatColumn as follows, you get the perfect result:

<cfset SpreadsheetFormatColumn(spreadsheetObject, {alignment="left", dataformat="mm/dd/yyyy"}, 1)>

A perfect Excel date, that appears perfectly and sorts perfectly.

[-__cfSearching__-]

Although there is an open bug report, this is in actuality not a "bug" per se.
Technically, it is NOT ColdFusion’s “fault” (but it is a problem).

No, this is a bug imo and the fault does lie with CF 😉 At least when dealing with known date/time objects anyway.

Excel considers dates as a floating-point value, measuring days from midnight, 30 December 1899 ...

POI already includes utility classes for converting java dates to Excel dates. So this should be a non-issue.

Hence, SpreadsheetFormatColumn actually works PERFECTLY
*if* you present it with the correctly formatted data – a
floating-point value as specified above – NOT a dateTime
value from a DB.

Not quite. It may result in the wrong date

   orig      ms sql datetime         : 2011-08-17 09:56:10.857
   float => int                       : 40770
   excel mm/dd/yyyy =>       : 08/15/2011

ALSO, this cures the problem of how Excel sorts the dates
you place via cfspreadsheet. (if you attempt simply casting
the dateTime as a VarChar, Excel will DISPLAY it correctly,
but it will not recognize it as a date and only perform
alpha sorts, which are not chronological).

The cell values were not really dates. They were strings. I think you may be misunderstanding how cells work. Cells are similar to database columns in that they also have a data type (well .. of sorts). The cell "type", not just the value, determines how ordering is performed. The same set of values may sort very differently when treated as strings rather than numbers.  So even if the date cell values really were correct (they are not), they still might not sort correctly.  Not as long as the cell type is string instead of date (or numeric really).

In your SQL select, you must cast the dateTime as a “float” as follows:

None of this hoop jumping should be necesary IMO. Date/time query columns contain java.util.Date objects. POI provides a very simple method for storing those Date values: setCellValue(java.util.Date). I am not sure why CF does not use it, or does not appear to anyway ... When supplied with a known date object, CF should use this method automatically. Then dates would be handled correctly without requiring work-arounds or hacks.

Message was edited by: -==cfSearching==-

[Guest]

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>

You could easily loop through a query resultset and apply the dateToExcel function to populate a column with Excel-usuable real-number dates or apply it to ad hoc dates for the ad hoc formatting.

[-__cfSearching__-]

While I can appreciate the semantic argument on whether it

is or is not a "bug"

It is not semantics. CF's handling of dates goes against common expectations. This is a bug by most accepted definitions. Period.

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.

That does not apply as Excel does not even know they are dates. As mentioned before CF is creating "text" cells. Certainly a valid operation with spreadsheets. It is just not what most people are expecting here ...

If you are uncomfortable with the SQL based fix

No discomfort. Your earlier solution just did not work for me.

zero time span to any CF date and you get an accurate
real-number based date that SpreadsheetFormatColumn works

I have not tested the new one to see if it works any better.  But POI already has a perfectly good method that can handle CF date objects "as is". Personally I would rather use that instead.  But like your suggestion above it requires setting values row by row. That kind of defeats the purpose of having functions like SpreadsheetAddRows() in the first place ...

Since CF's built in toolset does not automatically convert

this for you, we are (unfortunately) left doing the heavy lifting.

That was never in question. The whole point of filing a bug, and calling it a bug, is that hopefully the behavior will be changed in the next update. So we do not have to continue resorting to less efficient work-arounds/hacks  😉  Should we implement a valid work-around in the mean time? Obviously yes. But let us also call a spade a spade here. IMO it is a bug. Call it that. Vote for it to be fixed. The End.

-Leigh

Message was edited by: -==cfSearching==-