CODEWITHSUNDEEP
htmlexcelformattingcell
dmr
dmr

Reputation: 22333

Format HTML table cell so that Excel formats as text?

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?

Upvotes: 47

Views: 116198

Answers (7)

ANDiTKO
ANDiTKO

Reputation: 432

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.

Upvotes: -2

WhiteOne
WhiteOne

Reputation: 897

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

Upvotes: 15

Muhammad Yousaf
Muhammad Yousaf

Reputation: 21

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

Upvotes: 2

Stephen
Stephen

Reputation: 21

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 &#8203; 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.

Upvotes: 2

Thomas_WhoCan&#39;tThinkOfAWittyID
Thomas_WhoCan&#39;tThinkOfAWittyID

Reputation: 43

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 = '&#8203;' + someColumn 
FROM, etc.

and removing it in the SSRS field's expression:

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

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

Upvotes: 3

Raposo
Raposo

Reputation: 499

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 (&#8203;) in the begining of the field.

Upvotes: 15

scunliffe
scunliffe

Reputation: 63588

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>

Upvotes: 127

Related Questions