Export to Excel not displaying numbers correctly
I'm having a hard time getting account numbers to display correctly when exporting data from database to an Excel spreadsheet. The account number for one company, shows up correctly. However, others don't. Users have to double click in the cell to see the whole account number. I have Googled this issue and none of the solutions that I've tried worked.
<cfsetting enablecfoutputonly="Yes">
开发者_JAVA百科 <cfsavecontent variable="sTest">
<cfoutput>
<table>
<tr><th align="center">ATTUID</th>
<th>Company Name</th>
<th align="center">Account Number </th>
<th align="center">Total Past Due Balance</th>
<th align="center">Date Sent</th>
</tr>
<cfloop query="returnQuery">
<tr><td>#attuid#</td>
<td>#companyname#</td>
<td>#AccountNum4#</td>
<td>#totalpastdue#</td>
<td>#dateformat(posted)# #timeformat(posted, "h:mm tt")#</td>
</tr>
</cfloop>
</table>
</cfoutput>
</cfsavecontent>
<cfcontent type="application/vnd.msexcel">
<cfheader name="Content-Disposition" value="inline; filename=export.xls">
<cfoutput>#sTest#</cfoutput>
If you are exporting html, you could also use the xml mso schema properties to format the number as text, without changing the actual cell value ie adding an apostrophe.
<cfheader name="Content-Disposition" value="inline; filename=someFile.xls">
<cfcontent type="application/vnd.ms-excel">
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<body>
<table>
<tr>
<td style='mso-number-format:"\@";'>510074123456989</td>
</tr>
</table>
</body>
</html>
You can insert an apostrophe ' preceding the account number. This will force excel to treat numeric (any) data as text.
This usually happens when the column is not wide enough to display the data. Try and make the column wider and see if that corrects the issue.
加载中,请稍侯......
精彩评论