Format HTML table cell for Excel to format text?

Question

I am creating an HTML table that will open as a table in Excel. What HTML tag or CSS style can I use to tell Excel to display the contents of a cell as text?

Answer 1

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

See my previous answer to this question: HTML in Excel: how to tell Excel to treat columns as numbers?

(adjusted fragment)

If you add a CSS class to your page:

.num { mso-number-format:General; } .text{ mso-number-format:"\@";/*force text*/ } 

And slap these classes on your APs, does it work?

 <td class="num">34</td> <td class="num">17.0</td> <td class="text">067</td> 

Answer 2

There is one problem using this solution (css style with number format). Excel gives the error "Number Stored in text", which can be inconvenient in some cases. To avoid this problem, you can use the ZERO WIDTH SPACE character (& # 8203;) at the beginning of the field.

Answer 3

I do not have enough comments to comment or vote, but Raposo's answer worked very well for me. Our system imports SSRS reports and runs them locally. It stores a DataSet request in the database and pulls them at runtime. However, to export Excel, it simply runs the received query data into a DataGrid and writes it directly to the stream as HTML, setting the extension to .xls. Including Raposo Solution in a DataSet Report Request:

 SELECT someColumn = '​' + someColumn FROM, etc. 

and delete it in the SSRS field expression:

 =Replace(Fields!someColumn.Value, "​", "") 

- the only thing I found that works. Thanks!

Answer 4

Perfect 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 great for me

Answer 5

I found five solutions to this problem:

• Format the field as text, as described by scunliffe. This is due to the green triangle problem, according to Raposo.

• Use & # 8203; as described by Raposo. This is because the value is not a number. This can be a problem if the data is pulled into some system for processing.

• Add TD as <td> = "067" </td>. This has the same problem as # 2.

• Add TD as <td> = text (067, "000") </td>. This also has the same problem as # 2.

• Use the CSS.text3 class {mso-number-format: "000";} and add this class to TD. This is my preferred solution, but it has a problem with requiring multiple classes if you have numbers of different lengths. If you write your headline and then click on your data, you must add all the possible classes before you know which ones you will need. But it has advantages that the text is really a number and there is no green triangle.

Answer 6

You can also solve this problem by adding inextricable space :   to the value of the <td> element.
Example: <td>&nbsp;0:12:12.185</td>
Instead: <td>0:12:12.185</td>