HTML to Excel: How can tell Excel to treat columns as numbers?

Question

I need to achieve the following when opening an HTML in Excel (Response.contentType="application/vnd.ms-excel") :

• force Excel to consider content of td cells as numbers
• make the above so that any subsequent user-entered formulas work on these cells (when the spreadsheet is opened)

So far I was successful with adding style="vnd.ms-excel.numberformat:0.00" to the td cells in question. The contents of the cells are correctly shown as numbers when I right click on them in the Excel, however the formulas don't work.

If successful, that technique would be quite useful because any web Excel report could be user enhanced with appropriate formulas according to custom requirements. Thanks in advance.

Answer 1

The best trick is to mock up an Excel spreadsheet with all the colors/(conditional) formats/formulas you require.

Keep it as simple as possible, just the bare minimum rows etc.

Then save the Excel spreadsheet "As Xml Spreadsheet"

Then you have a template to build a spreadsheet via Xml.

I generally clean up styles in the original Xml saved from the Excel workbook. Removing duplicates and renaming them.

Then it is just a matter of populating with whatever datasource you have.

I do not know what environment you are coding to, but in VB.NET with LINQ and Xml Literals, this is a very simple task.

Answer 2

If you add a CSS Class to your page:

.num {
  mso-number-format:General;
}
.date {
  mso-number-format:"Short Date";
}

And slap those classes on your TD's, does it work?

<td class="num">34</td>
<td class="num">17.0</td>
<td class="date">12/17/2008</td> <!-- if you are playing with dates too -->

Update: Additional formatting options from @Aaron.

Answer 3

UPDATE: You also need to remove non-breaking spaces (nbsp's) from td cells containing numeric or money information to make formulas work on these cells in Excel. So the problem is solved.

Answer 4

Here is a list of Excel formats: Styling Excel cells with mso-number-format