格式化HTML表格单元格，使Excel格式为文本?

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以显示单元格的内容为文本?

6 个解决方案

#1

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

#2

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给出了“以文本形式存储的数字”的错误，这在某些情况下是不方便的。为了避免这个问题，可以在字段的开头使用零宽度空间字符()。

#3

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报告并以本地模式运行它们。它将数据集查询存储在数据库中，并在运行时提取它们。然而，对于Excel导出，它只是将查询的结果数据运行到一个DataGrid对象中，并将其作为HTML直接写入流中，并将扩展名设置为.xls。将Raposo的解决方案放到报表的数据集查询中:

SELECT someColumn = '&#8203;' + someColumn 
FROM, etc.

and removing it in the SSRS field's expression:

并在SSRS字段的表达式中删除:

=Replace(Fields!someColumn.Value, "&#8203;", "")

is the only thing I've found that works. Thanks!

这是我发现的唯一有效的方法。谢谢!

#4

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.

将字段格式化为scunliffe描述的文本。这就有了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.

使用& # 8203;像Raposo所描述的那样。它的问题是值不是一个真正的数字。如果数据被拉到某个系统中进行处理，这可能是一个问题。
Add the TD as <td>="067"</td>. This has the same problem as #2.

将TD = < TD >="067" 。这和第二条问题是一样的。
Add the TD as <td>=text(067,"000")</td>. This also has the same problem as #2.

将TD = < TD >=text(067，"000") 。这也与第二条问题相同。
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.

使用.text3 {mso-number格式:"000";}的CSS类，并将该类添加到TD中。这是我的首选解决方案，但如果您有不同长度的数字，那么它需要多个类。如果您编写头部，然后遍历数据，您必须在知道需要哪些类之前添加所有可能的类。但是这样做的好处是文本是一个数字，没有绿色三角形。

#5

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'>&#8203;");
HttpContext.Current.Response.Write(Coltext);
HttpContext.Current.Response.Write("</Td>");

and it works fine for me

对我来说还行

#6

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>

你也可以通过增加不间断的空间来解决这个问题:在元素的值之前。例:& 0:12 .185，而不是0:12 .185

#1