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?

Solution 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>

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

Solution 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>

Solution 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!

Solution 5

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

View more solutions

Comments

[John]

Took way too long to google for this solution.. these html table to excel spreadsheet tips are impossible to find

[pojomx]

made my day too... almost a year later to find this... haha Thank you anyway :)

[javabeangrinder]

I couldn't make this work when opening in LibreOffice. M$ Excel works fine though.

[scunliffe]

@javabeangrinder yeah the format settings are specific to Microsoft Excel. I don't think OpenOffice, LibreOffice, etc. have any special settings.

[TheStoryCoder]

Leaves an invisible character, but it looks better!

[Gary]

This is the ONLY thing that helped me force a cell to text. Pre-appended this to the value and BLAMO! Text!

[WhiteOne]

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]

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>

[Slai]

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 <td style='mso-number-format:"#,##0"'>2345</td>, but that can make the file bigger.

[Lloyd]

thanks, this works for me. (mso-number-format:"\@";/*force text*/ is NOT)

[Urvish Joshi]

Ohhhhhhh bro you saved me!!🙏✌