导出到excel时如何显示前导零? [英] How do i show the leading zeros when exporting to excel?

问题描述

我正在通过更改内容类型来创建Excel报告.

Response.ContentType = "application/vnd.ms-excel"

我的值包含前导零.问题是，导出到excel时，前导零丢失了.

例如

000123-> 123

我知道可以通过excel手动更改.问题是我该如何以编程方式完成此任务?

解决方案

将application/vnd.ms-excel导出为HTML表，它使您可以访问多种工作表格式设置选项:

使用:

Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment; filename=" + fileName);

并发送如下内容:

<table border="1">
<caption>This is a caption, it appears centered across all columns</caption>
<colgroup>
<col width="100">
<col width="200">
</colgroup>
<tr valign="bottom">
<td><b>Header 1</b></td>
<td filter="all"><b>Header 2 (filterable)</b></td>
</tr>
<tr valign="top">
<td style="vnd.ms-excel.numberformat:$#,##0.00_)[semicolon]($#,##0.00)">1</td>
<td style="vnd.ms-excel.numberformat:00000000">2</td>
</tr>
<tr valign="top">
<td style="vnd.ms-excel.numberformat:$#,##0_)[semicolon]($#,##0)">3</td>
<td style="vnd.ms-excel.numberformat:00000000">4</td>
</tr>
<tr valign="top">
<td style="vnd.ms-excel.numberformat:$#,##0.00_)[semicolon]($#,##0.00)">-5</td>
<td style="vnd.ms-excel.numberformat:00000000">2</td>
</tr>
<tr valign="top">
<td style="vnd.ms-excel.numberformat:$#,##0.00_)[semicolon][red]$#,##0.00">-7</td>
<td style="vnd.ms-excel.numberformat:00000000">8</td>
</tr>
<tr valign="bottom">
<td formula="=sum(a2:a5)" style="vnd.ms-excel.numberformat:$#,##0.00_)[semicolon][red]$#,##0.00"></td>
<td formula="=average(b2:b5)" style="vnd.ms-excel.numberformat:00000000"></td>
</tr>
</table>

请注意，通过使用HTML表语法，您可以添加列AutoFilter ，指定垂直行对齐，控制列的宽度，将列与<colspan>组合(未显示)，添加 vnd.ms-excel.numberformat .您甚至可以交叉表(数据透视表)(未显示). /p>

我有一个指向更全面文档的链接，有关支持哪些HTML标记和属性以及它们的作用，但我似乎放错了地方.如果其他人可以很好地链接到Microsoft文档，请随时编辑我的答案或发表评论.

看来您可以使用HTML可以做更多的事情/XML 来构建复杂的Excel工作表.我从没花这么长的时间，但是知道有什么可能很有趣.

I am creating an excel report by changing the content type.

Response.ContentType = "application/vnd.ms-excel"

I have values that contain leading zeros. The issue is when exporting to excel the leading zeros are missing.

e.g.

000123 -> 123

I know that this can be changed manually via excel. The question is how can i accomplish this programmatically?

解决方案

Export your application/vnd.ms-excel as an HTML table, it gives you access to a variety of worksheet formatting options:

Use:

Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment; filename=" + fileName);

And send something like the following:

<table border="1">
<caption>This is a caption, it appears centered across all columns</caption>
<colgroup>
<col width="100">
<col width="200">
</colgroup>
<tr valign="bottom">
<td><b>Header 1</b></td>
<td filter="all"><b>Header 2 (filterable)</b></td>
</tr>
<tr valign="top">
<td style="vnd.ms-excel.numberformat:$#,##0.00_)[semicolon]($#,##0.00)">1</td>
<td style="vnd.ms-excel.numberformat:00000000">2</td>
</tr>
<tr valign="top">
<td style="vnd.ms-excel.numberformat:$#,##0_)[semicolon]($#,##0)">3</td>
<td style="vnd.ms-excel.numberformat:00000000">4</td>
</tr>
<tr valign="top">
<td style="vnd.ms-excel.numberformat:$#,##0.00_)[semicolon]($#,##0.00)">-5</td>
<td style="vnd.ms-excel.numberformat:00000000">2</td>
</tr>
<tr valign="top">
<td style="vnd.ms-excel.numberformat:$#,##0.00_)[semicolon][red]$#,##0.00">-7</td>
<td style="vnd.ms-excel.numberformat:00000000">8</td>
</tr>
<tr valign="bottom">
<td formula="=sum(a2:a5)" style="vnd.ms-excel.numberformat:$#,##0.00_)[semicolon][red]$#,##0.00"></td>
<td formula="=average(b2:b5)" style="vnd.ms-excel.numberformat:00000000"></td>
</tr>
</table>

Note that by using the HTML table syntax you can add a column AutoFilter, specify the vertical row alignment, control the width of columns, combine columns with <colspan> (not shown), add formulas and specify the format of column data with vnd.ms-excel.numberformat. You can even do Crosstab (PivotTables) (not shown).

I had a link to more comprehensive documentation about what HTML tags and attributes are supported, and what they do, but I seem to have misplaced it. If anyone else has a good link to Microsoft documentation about this, feel free to edit my answer or include a comment.

EDIT: It seems that you can do a lot more with HTML/XML to build complicated Excel worksheets. I've never gone to these lengths, but it is interesting to know what is possible.

这篇关于导出到excel时如何显示前导零?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！