html中table和format,格式化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

92

You can apply formatting to the cells for numbers, text, dates, etc.

您可以將格式應用於單元格中的數字、文本、日期等。

請參閱我之前的回答: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上，有用嗎?

3417.0067

#2

10

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

2

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 = '​' + 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!

這是我發現的唯一有效的方法。謝謝!

#4

2

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

="067". This has the same problem as #2.

將TD = < TD >="067"

。這和第二條問題是一樣的。

Add the TD as

=text(067,"000"). 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

2

Superb solution! I did it like below

極好的解決方案!我是這樣做的

HttpContext.Current.Response.Write("");

HttpContext.Current.Response.Write("

​");

HttpContext.Current.Response.Write(Coltext);

HttpContext.Current.Response.Write("

");

and it works fine for me

對我來說還行

#6

0

You can solve the problem too by adding non-breaking space:   before the value of the

element.

Example:

 0:12:12.185

Instead of:

0:12:12.185

你也可以通過增加不間斷的空間來解決這個問題:在元素的值之前。例:& 0:12 .185，而不是0:12 .185