tracker issue : CF-4058810

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

Leading zeros removed from spreadsheet object cell values

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/NotABug

Reporter/Name(from Bugbase): Neil Liggett / Neil Liggett (Neil Liggett)

Created: 09/17/2015

Components: Document Management, Office Integration

Versions: 11.0

Failure Type: Data Corruption

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Critical / All users will encounter

Locale/System: English / Win 2008 Server R2 64 bit

Vote Count: 0

Problem Description: Data inserted into a spreadsheet object, cell values with leading zeros, the leading zeros are completely removed even though the cells/columns/rows is set to be formatted as text. Tried formatting the data with functions: SpreadsheetFormatColumn(s), SpreadsheetFormatRow(s) before and after setting the values, but the leading zeros are still removed

Steps to Reproduce:

Below is a simple reproduction of the issue:

<cfset value = "00123456">
<cfset xlsx = SpreadsheetNew("Test",true)>
<cfset SpreadsheetFormatColumn(xlsx,{dataformat="@"},1)>
<cfset SpreadsheetSetCellValue(xlsx,value,1,1)>
<cfspreadsheet action="write" filename="#GetDirectoryFromPath(GetCurrentTemplatePath())#Test.xlsx" name="xlsx" overwrite="yes">

Actual Result:

After opening Test.xlsx, the column format is Text, but the zeros are removed.

Cell A1 = 123456

Expected Result:

The expected result in Test.xlsx Cell A1 = 00123456

Any Workarounds:

The only way I've been able to keep the leading zeros is by using the SpreadsheetFormatCell or SpreadsheetFormatCellRange functions prior to the value of the cell being set.

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

Watson Bug ID:	4058810

External Customer Info:
External Company:  
External Customer Name: Neil Liggett
External Customer Email:  
External Test Config: My Hardware and Environment details:

Attachments:

  1. September 18, 2015 00:00:00: 1_Excel_Results.PNG

Comments:

Neil, Can you try using the new datatype parameter introduced in CF11 (https://wikidocs.adobe.com/wiki/display/coldfusionen/SpreadsheetSetCellValue). Here's how you'll use it in the test code you shared: SpreadsheetSetCellValue(xlsx,value,1,1,"STRING")
Comment by Piyush K.
5838 | September 18, 2015 01:44:26 AM GMT
Hello Piyush, That works. I didn't realize there was a new parameter for datatype added in CF11. Thank you very much! I have another question because I usually work with Query objects that I insert into spreadsheet objects. I tried using the datatype parameter with SpreadsheetAddRows, but it appears that some of the rows and columns were formatted correctly, but not all of them. Does it look like I may be doing something wrong? Below is an example: <cfset EMPLOYEE = QueryNew("Row_ID,Name,Birth_Year,Employee_ID","integer,varchar,integer,varchar")> <cfset QueryAddRow(EMPLOYEE,[{Row_ID=1,Name="Neil",Birth_Year=1988,Employee_ID="0123456"},{Row_ID=2,Name="John",Birth_Year=1989,Employee_ID="0654321"},{Row_ID=3,Name="Amanda",Birth_Year=1985,Employee_ID="0568789"}])> <cfset xlsx = SpreadsheetNew("Test",true)> <cfset SpreadsheetAddRows(xlsx,EMPLOYEE,1,1,true,["NUMERIC","STRING","NUMERIC","STRING"])> <cfspreadsheet action="write" filename="#GetDirectoryFromPath(GetCurrentTemplatePath())#Test.xlsx" name="xlsx" overwrite="yes"> I attached an image of what the resulting spreadsheet looks like. Excel_Results.PNG Thanks again, -Neil
Comment by External U.
5839 | September 18, 2015 06:53:01 AM GMT
Hello Piyush, I see that the documentation has recently been updated with a new example showing the use of the datatype parameter for SpreadsheetAddRows. Now I see what I was doing wrong and was able to get my spreadsheet columns in the correct format. So in my example in my previous note, I changed <cfset SpreadsheetAddRows(xlsx,EMPLOYEE,1,1,true,["NUMERIC","STRING","NUMERIC","STRING"])> to <cfset SpreadsheetAddRows(xlsx,EMPLOYEE,1,1,true,["NUMERIC:1,3;STRING"])>
Comment by External U.
5840 | September 21, 2015 07:10:46 AM GMT
Thanks Neil for the confirmation. Just to add, you can pass a range as well as the comma delimited values for datatype. So ["NUMERIC:1,2,3;STRING"] can also be specified as ["NUMERIC:1-3;STRING"] closing this.
Comment by Piyush K.
5841 | September 21, 2015 10:20:24 PM GMT
Hi Piyush The SpreadsheetAddRows doc's description for the datatype parameter should probably clarify the ways in which the parameter can be used (including the range example you provided). That is helpful information that should go in the Parameters table (and not just in the code examples beneath it). Thanks!, -Aaron
Comment by External U.
5842 | September 23, 2015 12:54:29 AM GMT