tracker issue : CF-4139060

select a category, or use search below
(searches all categories and all time range)
Title:

SpreadsheetAddRow applies strange time format to time values added to a spreadsheet

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): Legorol San / Legorol San (Legorol San)

Created: 04/12/2016

Components: Document Management, Office Integration

Versions: 11.0

Failure Type:

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Normal / All users will encounter

Locale/System: English / Win All

Vote Count: 0

Listed in the version 2016.0.03.300466 Issues Fixed doc 
Problem Description: When using SpreadsheetAddRow to add a value that looks like a time, the resulting cell has strange and inconsistent time formatting applied to it.

Applies to CF 11 Update 7.

Steps to Reproduce:

<cfset st = SpreadSheetNew("Here")>
<cfset SpreadSheetAddRow(st, "01:02:03")>
<cfdump var=#SpreadsheetGetCellValue(st,1,1)#><br>
<cfspreadsheet action="write" filename="out.xls" name="st" overwrite="true">

Actual Result:

cfdump shows 02:03. Cell A1 in out.xls shows 02:03 and has a time format " mm:ss".

Expected Result:

cfdump should show 01:02:03 or a variant such as 1:02:03 or 1:02:03 AM.
Cell A1 in out.xls should have a proper time format applied to it, such as "hh:mm:ss".

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	4139060

External Customer Info:
External Company:  
External Customer Name: Legorol San
External Customer Email:  
External Test Config: My Hardware and Environment details: Windows 10 Pro

Attachments:

Comments:

Transferring it to Piyush as its his feature.
Comment by Kailash B.
3101 | April 17, 2016 11:59:39 PM GMT
Legorol, To prevent the spreadsheet function from trying to interpret the format of the passed data you can use the following approaches: Use "STRING" datatype so that the data is treated by spreadsheet as plain text. This parameter was introduced with CF11. for usage details refer : https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-s/spreadsheetaddrow.html Or you can use the formatting functions to apply desired formatting after inserting the cell data. Usage instructions at: https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-s/spreadsheetformatcell.html This should work with CF11 or older versions. Here a demonstration with example: <!--- approach 1 : use the DataType parameter ---> <cfset SpreadSheetAddRow(st, "01:02:23", 1, 1, false,"STRING")> <cfset SpreadSheetAddRow(st, "12:09:01, 01:45 PM", 2, 1, false, "STRING")> <!--- approach 2 : use the Spreadsheet formatting functions ---> <cfset SpreadSheetAddRow(st, "01:02:03", 1, 1)> <cfset SpreadSheetAddRow(st, "12:09:01, 01:45 PM", 2, 1)> <cfset SpreadsheetFormatCell(st, {"dataformat":"hh:mm:ss", "color":"blue"}, 1, 1)> <cfset SpreadsheetFormatRow(st, {"dataformat":"hh:mm:ss", "color":"red"}, 2)> Would this solve your purpose?
Comment by Piyush K.
3102 | April 19, 2016 03:45:32 AM GMT
Piyush, thank you very much for the detailed reply. You closed bug #CF-4139054 as a duplicate of this one, but that bug is different, applies to CF 10 and is more serious, as that bug causes data corruption. This bug is only about the formatting, as the underlying numeric date value is fine in CF11. So please reopen #CF-4139054 as a separate issue. As for your solution, I understand both of your proposals and they are sensible. Unfortunately, neither solutions would work in my specific case, as I am adding a variable length list of unpredictable number and type of values to the spreadsheet. I can't just set the column to STRING type, as some of the values are numbers and dates, and I would like them to appear as such in the Excel sheet. I also can't just set the formatting on the column, because I don't know if the column contains a number or a time. Generally, my expectation would be that if I add a value to a spreadsheet and don't specify any column type or formatting, it really should come out looking at least reasonably like the value I'm adding. I think this is a genuine bug.
Comment by External U.
3103 | April 19, 2016 02:57:49 PM GMT