Excel spreadsheet

Hi,

I'm assuming you're just putting in <td>'#fieldName#</td> (notice the apostrophe) to convert to a string value which keeps the leading zeros.

I'm curious, is there a reason you want to keep the leading zeros?

cfwild

I have also discovered this -
<td style="vnd.ms-excel.text">#fieldName#</td>

It keeps the leading zeros but that adds the leading space as well.

try using this - <td>#Insert(chr(160), fieldName, 0)#</td>

It does however put a leading space in the cell. I haven't been able to
figure out how to remove that leading space it adds.

Ian and Craig

Thank you.
I tried the same, but was wondering if there is any other soultion other than POI utility.

Ian and craig Thank you.
I tried the same, but was wondering if there is any other soultion other than POI utility.

> Also if anyone has idea about adding headings and other css
> functionality to the spreadsheet from CF will be helpful.

If you are generating html, and prefer to stick with that for the moment, you can use styles on the table cells.
http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:55056#298084

Craig has the correct idea. It is Excel stripping the zero you need to
tell Excel to treat the value as a string and not a number. If you
don't want to dive into the POI utility just yet, you can try wrapping
the values in quotes ['] which usually tell excel that a value is a string.

Ben Nadel has an excellent utility (POIUtility) for creating spreadsheets from ColdFusion (including headers, style formatting, etc.). It's easy to use and quite effective.
http://www.bennadel.com/projects/poi-utility.htm

I *think* the problem you're seeing with the removal of leading zeros is Excel-based. If you cfdump your data in CF, do you see the preceding zeros? If so, it should be safe to assume the issue is in Excel (I seem to recall that Excel's cell type for numbers/integers does this truncation automatically (since, in it's logic or interpretation of numbers/integers 013 is the same as 13).

One of the benefits of the POIUtility is that you can set the cell type for Excel. Try setting the cell type as a string when you create your spreadsheet from the data and this *should* result in your preceding zeros not being truncated.