How can I ensure that my Excel cell will be treated as a string?
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开发者_JS百科 to be displayed literally, as text, rather than converted into a number?
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.
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>
This answer to another thread worked for me:
Export to Excel not displaying numbers correctly
加载中,请稍侯......
精彩评论