Title:
Bug 87056:(Watson Migration Closure)Summary: spreadsheetSetCellValue() and other cell-value setting functions should be data-type awareThe datatype of the data being put in the spreadsheet should be respected
| View in TrackerStatus/Resolution/Reason: Closed/Fixed/
Reporter/Name(from Bugbase): Adam Cameron / Adam Cameron (Adam Cameron)
Created: 08/21/2011
Components: Document Management, Office Integration
Versions: 9.0.1
Failure Type: Unspecified
Found In Build/Fixed In Build: 0000 /
Priority/Frequency: Minor / Unknown
Locale/System: English / Platforms All
Vote Count: 2
Problem:
Summary: spreadsheetSetCellValue() and other cell-value setting functions should be data-type awareThe 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.-- Adam
Method:
<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">
Result:
----------------------------- Additional Watson Details -----------------------------
Watson Bug ID: 3043981
External Customer Info:
External Company:
External Customer Name: Adam Cameron
External Customer Email: 17EB1A7649DA54C7992015A9
External Test Config: 08/21/2011Attachments:
Comments: