How can I ensure that my Excel cell will be treated as a string?

Question

I am using Coldfusion to create a basic Excel spreadsheet. I build a table, and then convert it to Excel using the following code:

<cfset fileName = "myFile.xls">
<CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=#fileName#">
<cfcontent type="application/msexcel" reset="true">
<cfoutput>#myExcel#</cfoutput>

The "myExcel" variable holds an HTML table.

My problem is that some of the cells contain values such as: 281E47. Which Excel is then translating to 2.81E+49. How can I specify, in my HTML table, that I want these values to be displayed literally, as text, rather than converted into a number?

possible duplicate of [ColdFusion export to Excel](http://stackoverflow.com/questions/3392590/coldfusion-export-to-excel) — Lance Roberts, Jul 18 '11 at 18:45
@Lance - thanks, it does seem the same. Although the accepted answer doesn't seem to work for me. I guess I didn't search well enough before posting... — froadie, Jul 18 '11 at 18:51
You can run into the same issue using the built-in spreadsheet functions in CF9, for which there's a different workaround: http://stackoverflow.com/questions/3081202/how-to-format-spreadsheet-columns-using-coldfusion — CfSimplicity, Jul 19 '11 at 08:22

score 1 · Answer 1 · answered Jul 18 '11 at 18:32

1

I'm not up to speed with ColdFusion, however, one trick you can use with Excel cells is prefixing the data value with a tick (e.g. single quote). If you do that, the value will be treated as a string. So store '281E47 in your table.

answered Jul 18 '11 at 18:32

dcp

54,410
22
144
164

1

I actually just tried that... but it's displaying the single quote in the column :-/ ('281E47) – froadie Jul 18 '11 at 18:37
@froadie - After a bit of googling it seems somebody else had same problem, and this solution worked for them. See this link: http://stackoverflow.com/questions/3392590/coldfusion-export-to-excel So I am not sure why this doesn't work for you, and unfortunately, I don't have any other suggestions. – dcp Jul 18 '11 at 18:40
thanks for the link, seems identical to my question. wonder why the apostrophe isn't working for me :( but leigh's suggestion in that thread seems to work for me – froadie Jul 18 '11 at 18:50

score 0 · Answer 2 · answered Jul 19 '11 at 15:28

Though a lot of people don't like it (toproprietary for some people), look up mso-number-format. When you are build your table, you can set the format of the cell my setting the mso-number-format in your css.

Here is an example that will do the accounting format in excel:

<cfcontent type="application/vnd.ms-excel">

<style>
    td.accountnum
      {mso-number-format:"_-$* #,##0.00_-;$* (#,##0.00_-);_-$* \" - \"??_-;_-@_-"}
</style>

</head>

<body>
<table>
<tr>
<td class="accountnum">-1</td>
<td class="accountnum">0</td>
<td class="accountnum">1</td>
</tr>
</table>
</html>

Yeah, from the comments above that is what they ended up using ie just text: http://stackoverflow.com/questions/3392590/coldfusion-export-to-excel/3399625#3399625 — Leigh, Jul 19 '11 at 16:51

score 0 · Accepted Answer · edited May 23 '17 at 11:50

0

This answer to another thread worked for me:

Export to Excel not displaying numbers correctly

edited May 23 '17 at 11:50

Community

1
1

answered Nov 06 '11 at 09:46

froadie

79,995
75
166
235

How can I ensure that my Excel cell will be treated as a string?

3 Answers3

Linked