HTML 到 Excel：如何告诉 Excel 将列视为数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/354476/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
HTML to Excel: How can tell Excel to treat columns as numbers?
提问by scunliffe
I need to achieve the following when opening an HTML in Excel (Response.contentType="application/vnd.ms-excel"):
在 Excel 中打开 HTML 时，我需要实现以下目标(Response.contentType="application/vnd.ms-excel")：
- force Excel to consider content of td cells as numbers
- make the above so that any subsequent user-entered formulas work on these cells (when the spreadsheet is opened)
- 强制 Excel 将 td 单元格的内容视为数字
- 进行上述操作，以便任何后续用户输入的公式都适用于这些单元格（打开电子表格时）
So far I was successful with adding style="vnd.ms-excel.numberformat:0.00"to the td cells in question. The contents of the cells are correctly shown as numbers when I right click on them in the Excel, however the formulas don't work.
到目前为止，我已成功添加style="vnd.ms-excel.numberformat:0.00"到有问题的 td 单元格中。当我在 Excel 中右键单击它们时，单元格的内容正确显示为数字，但是公式不起作用。
If successful, that technique would be quite useful because any web Excel report could be user enhanced with appropriate formulas according to custom requirements. Thanks in advance.
如果成功，该技术将非常有用，因为用户可以根据自定义要求使用适当的公式增强任何 Web Excel 报告。提前致谢。
回答by scunliffe
If you add a CSS Class to your page:
如果您向页面添加 CSS 类：
.num {
mso-number-format:General;
}
.date {
mso-number-format:"Short Date";
}
And slap those classes on your TD's, does it work?
把这些课放在你的 TD 上，有用吗？
<td class="num">34</td>
<td class="num">17.0</td>
<td class="date">12/17/2008</td> <!-- if you are playing with dates too -->
Update:Additional formatting optionsfrom @Aaron.
更新：来自@Aaron 的其他格式选项。
回答by Aaron Digulla
Here is a list of Excel formats: Styling Excel cells with mso-number-format
这是 Excel 格式的列表：使用 mso-number-format 设置 Excel 单元格样式
回答by BlackMael
The best trick is to mock up an Excel spreadsheet with all the colors/(conditional) formats/formulas you require.
最好的技巧是使用您需要的所有颜色/（条件）格式/公式来模拟 Excel 电子表格。
Keep it as simple as possible, just the bare minimum rows etc.
保持尽可能简单，只有最少的行等。
Then save the Excel spreadsheet "As Xml Spreadsheet"
然后将 Excel 电子表格保存为“作为 Xml 电子表格”
Then you have a template to build a spreadsheet via Xml.
然后你就有了一个通过 Xml 构建电子表格的模板。
I generally clean up styles in the original Xml saved from the Excel workbook. Removing duplicates and renaming them.
我通常清理从 Excel 工作簿保存的原始 Xml 中的样式。删除重复项并重命名它们。
Then it is just a matter of populating with whatever datasource you have.
那么这只是填充您拥有的任何数据源的问题。
I do not know what environment you are coding to, but in VB.NET with LINQ and Xml Literals, this is a very simple task.
我不知道您要编码到什么环境，但是在带有 LINQ 和 Xml 文字的 VB.NET 中，这是一项非常简单的任务。
回答by Mayank Pathak
It doesn't works sometimes if you add it using a css classin that case try it using in Styleto your TD. I did it for formatting a column to a text. like this
如果您css class在这种情况下使用 a 添加它，有时它不起作用，请尝试将其Style用于您的TD. 我这样做是为了将一列格式化为文本。像这样
style="mso-number-format:\@;"
For you it would be as
对你来说就像
mso-number-format:General
回答by Andy Castles
I couldn't get any of the answers on this page to work in Excel 2010. I ended up using this.
我无法在此页面上获得任何答案以在 Excel 2010 中工作。我最终使用了它。
<td x:num>123</td>回答by Andy Castles
UPDATE: You also need to remove non-breaking spaces (nbsp's) from td cells containing numeric or money information to make formulas work on these cells in Excel. So the problem is solved.
更新：您还需要从包含数字或货币信息的 td 单元格中删除不间断空格 (nbsp)，以使公式适用于 Excel 中的这些单元格。所以问题解决了。
