Ray,

Check out Apache POI, it's much better at handling the formatting, and the resulting files are *much* smaller since they are binary, and not juset HTML/XML files with an .xls extension.

Yup, that's one of the reasons why I originally looked at POI... because people wanted specially formatted numbers.

Ray, if you need guidance on the subject, I wrote 2 entries about it:

intro: http://www.d-ross.org/index...

more: http://www.d-ross.org/index...

Hi Ray - This is pretty normal behaviour for Excel. By quoting the number you are telling Excel it is a Text value, rather than 'General' (which tries to identify the data type and would wrongly identify it as a number in this case). Using the single quote does include it in the data as you say, and in some situations Excel does hide it although it is always kept with the value. IMHO what you have done is better than the single quote.

rtrout: I -was- using quotes, ie "xxxxx", "xxxx", etc. But it still suppressed the leading zeros.

Seems you can use some Excel funtions, eh? I use =TEXT('#myfooval#') to handle leading zeroes or padding.

hey Ray, are you saying just output the Zip without an extension?

nravo - the zip was a simple 5 digit zip. I was trying to ensure that 01234 would come out as 01234, not 1234. Again though I did find a way to force it.

I had a hell of a time with a similar issue and I should repeat the popular frase "I hate microsoft"...
A warning to those downloading to excel when you have a ton of records and combine number columns with text columns with numbers padded as noted here (i.e. 001234) it turns out that at a certain point excel choked and begun importing numbers as text in those number only columns. (for some reason my magic row number was 2325)

For those not so savy of my users who would inocently sum up those columns, the totals would not be the same unless you would convert the text to numbers!... and of course there was no consistecy to this behaviour... so my solution was to output the number columns as follows:

<td style="vnd.ms-excel.numberformat:##,######,######,####0.00">#MyValue#</td>
(note the escaping # for the cfoutput tag)

and then for those padded number columns I ended up doing something similar to what Ray mentioned

MyValue = "=""#str#""";

anyway... you would hope one could use this trickery for more noble things in life...

Thanks Jose for providing the style I was looking for to provide trailing zeroes after decimal :)

I had the same issue with Excel treating a zip code as a number and thus removing the leading zeroes. I tried everything, until I asked a coworker who informed me about adding a CSS format to the zip code column.
Microsoft has some CSS directives that work with office produts such as excel.
For instance, in my case, I added style="mso-number-format:\@" to the <td> and that hinted Excel to treat the column as text, thereby preserving the leading zeroes.
The details are http://agoric.com/sources/s....

Well dang - that is a great tip. Thank you Neo!

Thank you so much for the tip(s)! They were extremely helpful.

Neo, thanks for that information, just what I was looking for and works perfectly.

Just wanted to add to Neo's comments that if there is a specific format you need you can do
mso-number-format:'00000'
I was trying to fix leading zeros in a Zip Code and did notice that using Neo's approach added a green triangle to the top left of the cell (MS info icon) whereas using '00000' did not.

Not to over simplify but I just put a space &nbsp; next to the variable ie. #var# &nbsp; and Excel sees as literal and puts the zero(s).