格式化 HTML 表格单元格，以便 Excel 格式化为文本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4619909/
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
Format HTML table cell so that Excel formats as text?
提问by dmr
I am creating an HTML table that will be opened as a spreadsheet in Excel. What HTML tag or CSS style can I use to "tell" Excel to display the cell's contents as text?
我正在创建一个 HTML 表格，它将在 Excel 中作为电子表格打开。我可以使用什么 HTML 标记或 CSS 样式来“告诉”Excel 将单元格的内容显示为文本？
回答by scunliffe
You can apply formatting to the cells for numbers, text, dates, etc.
您可以将格式应用于数字、文本、日期等单元格。
See my previous answer on this: HTML to Excel: How can tell Excel to treat columns as numbers?
请参阅我之前的回答：HTML to Excel：如何告诉 Excel 将列视为数字？
(adjusted snippet)
（调整后的片段）
If you add a CSS Class to your page:
如果您向页面添加 CSS 类：
.num {
mso-number-format:General;
}
.text{
mso-number-format:"\@";/*force text*/
}
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="text">067</td>
回答by Raposo
There is one problem using that solution (css style with number-format). The Excel gives the error "Number Stored as text" which can be inconvenient in some cases. To avoid this problem it's possible to use the ZERO WIDTH SPACE character (​) in the begining of the field.
使用该解决方案存在一个问题（带有数字格式的 css 样式）。Excel 给出错误“数字存储为文本”，这在某些情况下可能会带来不便。为了避免这个问题，可以在字段的开头使用零宽度空格字符 ()。
回答by WhiteOne
You can solve the problem too by adding non-breaking space: before the value of the <td>element.
Example:
<td> 0:12:12.185</td>
Instead of:
<td>0:12:12.185</td>
您可以通过添加过解决问题的非换空间： 将前值<td>的元素。
示例：
<td> 0:12:12.185</td>
而不是：
<td>0:12:12.185</td>
回答by Thomas_WhoCan'tThinkOfAWittyID
I don't have enough rep to comment or up-vote, but Raposo's answer worked very well for me. Our system imports SSRS reports and runs them in local mode. It stores the DataSet query(ies) in the database and pulls them at runtime. However for Excel exports it just runs the query's resulting data into a DataGrid object and writes that directly to the stream as HTML, setting the extension to .xls. Putting Raposo's solution in the report's DataSet query:
我没有足够的代表来评论或投票，但 Raposo 的回答对我来说非常有效。我们的系统导入 SSRS 报告并在本地模式下运行它们。它将 DataSet 查询存储在数据库中并在运行时提取它们。但是，对于 Excel 导出，它只是将查询的结果数据运行到 DataGrid 对象中，并将其作为 HTML 直接写入流，将扩展名设置为 .xls。将 Raposo 的解决方案放入报表的 DataSet 查询中：
SELECT someColumn = '​' + someColumn
FROM, etc.
and removing it in the SSRS field's expression:
并在 SSRS 字段的表达式中删除它：
=Replace(Fields!someColumn.Value, "​", "")
is the only thing I've found that works. Thanks!
是我发现唯一有效的方法。谢谢！
回答by Muhammad Yousaf
Superb solution! I did it like below
绝妙的解决方案！我像下面那样做
HttpContext.Current.Response.Write("<style> .txt " + "\r\n" + " {mso-style-parent:style0;mso-number-format:\"" + @"\@" + "\"" + ";} " + "\r\n" + "</style>");
HttpContext.Current.Response.Write("<Td class='txt'>​");
HttpContext.Current.Response.Write(Coltext);
HttpContext.Current.Response.Write("</Td>");
and it works fine for me
它对我来说很好用
回答by Stephen
I have found five solutions for this issue:
针对这个问题，我找到了五种解决方案：
Format the field as text as described by scunliffe. This has the problem of the green triangle as stated by Raposo.
Use ​ as described by Raposo. This has the problem that the value is not really a number. This could be an issue if the data is pulled into some system for processing.
Add the TD as <td>="067"</td>. This has the same problem as #2.
Add the TD as <td>=text(067,"000")</td>. This also has the same problem as #2.
Use a CSS class of .text3 {mso-number-format:"000";} and add this class to the TD. This is my preferred solution but it has the problem of requiring multiple classes if you have numbers of different lengths. If you write your header and then iterate through your data, you have to add all possible classes before knowing which of them you will need. But this has the advantages that the text is really a number and there is no green triangle.
按照 scunliffe 的描述将字段格式化为文本。这有 Raposo 所说的绿色三角形的问题。
使用 正如 Raposo 所描述的那样。这有一个问题，即该值实际上不是一个数字。如果数据被拉入某个系统进行处理，这可能是一个问题。
将 TD 添加为 <td>="067"</td>。这与#2有同样的问题。
将 TD 添加为 <td>=text(067,"000")</td>。这也有与#2 相同的问题。
使用 .text3 {mso-number-format:"000";} 的 CSS 类并将此类添加到 TD。这是我的首选解决方案，但如果您有不同长度的数字，则存在需要多个类的问题。如果您编写标头然后遍历数据，则必须先添加所有可能的类，然后才能知道需要哪些类。但是这样做的好处是文本真的是一个数字，没有绿色三角形。
回答by ANDiTKO
Try adding a single quote before your value:
尝试在您的值前添加单引号：
'
So your cell would become
所以你的细胞会变成
<td>'007</td>
This was the only solution that worked for me. Non of the abode worked for "Open Office" and "Libre Office", where my solution work. And the best part is that if you copy the value it won't copy the single quote.
这是唯一对我有用的解决方案。非住所为“开放式办公室”和“自由办公室”工作，我的解决方案在那里工作。最好的部分是，如果您复制值，它不会复制单引号。
