November 2nd, 2012, 08:48 AM
Cfspreadsheet and leading zeros in cells
i'm trying to export a query into an xls file. everything is working as it should and i get the xls file but there is a problem with a column that contains leading zeros ( i.e 022334).
I want to keep those zeros but it appears cfspreadsheet creates a new xls file with a cell format of "General" so that removes the leading zeros.
using SpreadsheetFormatColumns to change the dataformat to "text" doesn't help since the leading zeros have already dissapeared.
any thoughts on how to fix this problem.?
November 2nd, 2012, 11:25 AM
This one I'm afraid I can't help with, as I haven't used cfspreadsheet much. If no one else can chime in, I'd try Google or possibly the CF forums at Adobe.com.
November 2nd, 2012, 11:04 PM
I would convert the field to text in your query before sending the rs to cfspreadsheet. This works especially well with date fields, I have an oracle backend and formatting dates using cfspreadsheet function was a crap shoot as to the output. formatting the data in the rs cleared that up.
hope this helps
November 5th, 2012, 02:32 AM
@jbird4k what exactly do you mean by "convert the field to text in your query ".?
The field is already a text field .The issue appears when storing into the xls file for the reason i mentioned above ( General dataformat vs Text in Excel).
I have found this link http://cfsimplicity.com/16/forcing-values-to-be-inserted-into-spreadsheets-as-text but i have not tested it yet.
I'll let you know when i do.
November 5th, 2012, 05:53 AM
Update: Ok guys. everything worked using the above solution.
It's not the best solution since you
save your query in the excel file.
change the column in the excel that contains the leading zeros dataformat to text.
re-write everything using SpreadsheetSetCellValue in order to avoid reusing cfspreadsheet.
November 6th, 2012, 03:28 PM
Unfortunately that may be as good as it gets. CF9 has a several formatting related bugs, some of which were fixed in 9.0.1. FWIW, CF10 does preserve the zeroes:
<cfset qry = queryNew("")>
<cfset queryAddColumn(qry, "MyColumn", "varchar", listToArray("02222,01111"))>
<cfset sheet = spreadSheetNew()>
<cfset SpreadSheetAddRows(sheet, qry)>
<cfset SpreadSheetWrite(sheet, "c:/path/to/someFile.xls", true)>