CODEWITHSUNDEEP
excelcoldfusionexport-to-excelcoldfusion-8
user281867
user281867

Reputation: 587

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">
   <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>#totalpastd‌​ue#</td>
              <td>#datefor‌​mat(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>

Upvotes: 2

Views: 9074

Answers (3)

Leigh
Leigh

Reputation: 28873

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>

Upvotes: 7

buckbova
buckbova

Reputation: 1223

You can insert an apostrophe ' preceding the account number. This will force excel to treat numeric (any) data as text.

Upvotes: 5

Dave Ferguson
Dave Ferguson

Reputation: 773

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.

Upvotes: 1

Related Questions