• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Excel spreadsheet

New Here ,
Mar 30, 2009 Mar 30, 2009

Copy link to clipboard

Copied

Hello,

I use CF to pass data into a excel spreadsheet. But the values that have preceeding zeros to a number are truncated when I view them in a spreadsheet. I have to convert the value to string and pass. I tried find()
, refindnocase() and other string functions. It doesnot work.

Does anyone have a solution for this?
Also if anyone has idea about adding headings and other css functionality to the spreadsheet from CF will be helpful.

TOPICS
Advanced techniques

Views

1.5K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Mar 30, 2009 Mar 30, 2009

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Mar 30, 2009 Mar 30, 2009

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Mar 30, 2009 Mar 30, 2009

Copy link to clipboard

Copied

> 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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 02, 2009 Apr 02, 2009

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 02, 2009 Apr 02, 2009

Copy link to clipboard

Copied

Ian and Craig

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 02, 2009 Apr 02, 2009

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 02, 2009 Apr 02, 2009

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Apr 02, 2009 Apr 02, 2009

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Apr 02, 2009 Apr 02, 2009

Copy link to clipboard

Copied

arunaumuc wrote:
> Ian and Craig ..
> I tried the same, but was wondering if there is any other soultion other than POI utility.

Ian's suggestion, and mine, do not require using the POI utility.

cfwild wrote:
> is there a reason you want to keep the leading zeros?

I do not know what the OP is exporting, with some data leading zeroes can be meaningful. For example, with zip codes.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 06, 2009 Apr 06, 2009

Copy link to clipboard

Copied

LATEST

Try setting the style mso-number-format:"\@"

Example:

<table>

     <tr>

          <td style="mso-number-format:'\@';">01852</td>

          <td style="mso-number-format:'\@';">0000000000</td>

          <td style="font-weight:900;font-size:14.0pt;color:#ff0000;font-style:italic">Testing</td>

     </tr>

</table>

This is taking directly from saving the Excel file in HTML. Other formatting using CSS can be done the same way.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation