46

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?

dmr
  • 21,811
  • 37
  • 100
  • 138

7 Answers7

126

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>
Community
  • 1
  • 1
scunliffe
  • 62,582
  • 25
  • 126
  • 161
  • 15
    Took way too long to google for this solution.. these html table to excel spreadsheet tips are impossible to find – John Jan 19 '11 at 20:16
  • 2
    made my day too... almost a year later to find this... haha Thank you anyway :) – pojomx Aug 04 '12 at 23:33
  • I couldn't make this work when opening in LibreOffice. M$ Excel works fine though. – javabeangrinder Jan 09 '13 at 15:56
  • @javabeangrinder yeah the format settings are specific to Microsoft Excel. I don't think OpenOffice, LibreOffice, etc. have any special settings. – scunliffe Jan 09 '13 at 16:25
  • Hello, how can i apply this custom format: h:mm:ss.000 ? i tried: mso-number-format:"h:mm:ss.000"; but it doesn't work for me. – WhiteOne Nov 09 '16 at 13:02
  • 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 – WhiteOne Nov 11 '16 at 11:44
  • 2
    For anyone with special number formats: you can save Excel file in `.htm` format and search for the `mso-number-format` in the generated files (or advanced way: copy the cell and get the HTML from the Clipboard). The CSS can also be inlined `2345`, but that can make the file bigger. – Slai Nov 28 '16 at 13:36
  • This works when you have no other classes applied. If you have to have other classes use style attribute. – Pawel Jun 15 '23 at 18:39
15

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.

Raposo
  • 499
  • 5
  • 4
14

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>

WhiteOne
  • 887
  • 1
  • 10
  • 20
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:

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!

Thomas_WhoCan'tThinkOfAWittyID
  • 43
  • 8
2

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

Paweł Tomkiel
  • 1,974
  • 2
  • 21
  • 39
Muhammad Yousaf
  • 21
  • 2
2

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.

Stephen
  • 21
  • 1
-2

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.

ANDiTKO
  • 432
  • 4
  • 8