Format HTML table cell so that Excel formats as text?

Question

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?

Answer 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?

(adjusted snippet)

If you add a CSS Class to your page:

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

And slap those classes on your TD's, 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 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.

Answer 3

You can solve the problem too by adding non-breaking space:   before the value of the <td> element.
Example: <td>&nbsp;0:12:12.185</td>
Instead of: <td>0:12:12.185</td>

Answer 4

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:

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

and removing it in the SSRS field's expression:

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

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

Answer 5

I have found five solutions for this issue:

1. Format the field as text as described by scunliffe. This has the problem of the green triangle as stated by Raposo.

2. 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.

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

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

5. 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.

Answer 6

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

Answer 7

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.