导出到 Excel 无法正确显示数字

发布于 2024-09-12 17:45:48 字数 1195 浏览 7 评论 0原文

将数据从数据库导出到 Excel 电子表格时，我很难正确显示帐号。一家公司的帐号显示正确。然而，其他人则不然。用户必须双击单元格才能查看整个帐号。我用谷歌搜索了这个问题，但我尝试过的解决方案都不起作用。

<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>

原文

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>

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

本宫微胖 2024-09-19 17:45:48

如果要导出 html，您还可以使用 xml mso 架构属性将数字格式化为文本，而无需更改实际单元格值，即添加撇号。

<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>

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>

回复收藏 0 原文