cfspreadsheet how to preserve leading zeroes

15 replies
5856 views
Translate

Is there a way to preserve leading zeroes in a field when displaying in excel other than adding a ' at the front of the field? I have an id field defined in sql server as varchar but when creating an xls document using cfspreadsheet excel is interpreting the field as numeric and dropping the leading zeroes. I cannot have the ' leading that field since it is imported into another system.

This topic is closed to new replies. Start a new post to keep the conversation going.

Correct answer

This has been resolved. I tested both suggested solutions. I tried formatting the columns with spreadsheetformatcolumn as suggested in http://stackoverflow.com/questions/3081202/how-to-format-spreadsheet-c olumns-using-coldfusion but that did not preserve the leading zeroes in the ID column. I then tried the solution with POI found here http://www.bennadel.com/projects/poi-utility.htm by Ben Nadel and that did preserve the leading zeroes. Thanks for everyone's assistance and thanks to Ben for sharing his POI utility.

- Julie

K

Khalid Saleem

Participating Frequently

This worked for me. Leading zero will display is the excel column. e.g. 01234

Translate

A

Anonymous

I did make the adjustment but I must have made an error somewhere else. I'll play with it when I have time as an alternate solution to the POI one that is working now. Thanks for your help.

Translate

-

-__cfSearching__-

Inspiring

It is probably just the column index. Though it is unfortunate SpreadSheetFormatColumn does not work properly, because that workaround should not be necessary. Anyway, glad you found something that works.

-Leigh

Translate

A

Anonymous

Leigh I did try the exact code and that worked on 9.0.1.

Translate

-

-__cfSearching__-

Inspiring

Okay. That suggests there may be a problem in your adapted query code. Perhaps that is why it did not work. Did you adjust the forceTextColumnNumber index to point to the correct column?

-Leigh

Translate

A

Anonymous

Leigh I am running CF9.0.1,

Translate

-

-__cfSearching__-

Inspiring

Hm... and you ran the example "as is" and did not get these results?

NUMBERSASTEXT TEXT

01050094071094340000 abc

743059E6 def

Translate

A

Anonymous

No I changed the code to run with my query. I can try running it as is if you are curious.

Translate

A

Anonymous

Correct answer

This has been resolved. I tested both suggested solutions. I tried formatting the columns with spreadsheetformatcolumn as suggested in http://stackoverflow.com/questions/3081202/how-to-format-spreadsheet-c olumns-using-coldfusion but that did not preserve the leading zeroes in the ID column. I then tried the solution with POI found here http://www.bennadel.com/projects/poi-utility.htm by Ben Nadel and that did preserve the leading zeroes. Thanks for everyone's assistance and thanks to Ben for sharing his POI utility.

- Julie

Translate