Cfspreadsheet and leading zeros in cells

Dev Shed Forums Sponsor:

November 2nd, 2012, 07:48 AM

Abnaxus78

Registered User

Join Date: Nov 2011

Posts: 13

Time spent in forums: 20 h 43 m 54 sec
Reputation Power: 0

Cfspreadsheet and leading zeros in cells

Hi guys,

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, 10:25 AM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 5,100

Time spent in forums: 2 Weeks 5 Days 4 h 29 m 54 sec
Reputation Power: 966

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.

__________________
How to Ask Questions the Smart Way | Inversion of Control

November 2nd, 2012, 10:04 PM

jbird4k

Contributing User

Join Date: Nov 2001

Location: Maryland

Posts: 74

Time spent in forums: 15 h 46 m 58 sec
Reputation Power: 12

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

__________________
J. Birdsell,

November 5th, 2012, 01:32 AM

Abnaxus78

Registered User

Join Date: Nov 2011

Posts: 13

Time spent in forums: 20 h 43 m 54 sec
Reputation Power: 0

@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, 04:53 AM

Abnaxus78

Registered User

Join Date: Nov 2011

Posts: 13

Time spent in forums: 20 h 43 m 54 sec
Reputation Power: 0

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, 02:28 PM

cfSearching

Contributing User

Join Date: May 2008

Posts: 117

Time spent in forums: 17 h 16 m 2 sec
Reputation Power: 6

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:

Code:

<cfset qry = queryNew("")>
<cfset queryAddColumn(qry, "MyColumn", "varchar", listToArray("02222,01111"))>
<cfset sheet = spreadSheetNew()>
<cfset SpreadsheetFormatColumn(sheet,{dataFormat="@"},1)>
<cfset SpreadSheetAddRows(sheet, qry)>
<cfset SpreadSheetWrite(sheet, "c:/path/to/someFile.xls", true)>

Cfspreadsheet and leading zeros in cells

Developer Shed Advertisers and Affiliates