HTML to Excel: How can tell Excel to treat columns

问题:

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.

回答1:

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.

回答2:

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

回答3:

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.

回答4:

It doesn't works sometimes if you add it using a css class in that case try it using in Style to your TD. I did it for formatting a column to a text. like this

 style="mso-number-format:\@;"

For you it would be as

  mso-number-format:General

回答5:

I couldn't get any of the answers on this page to work in Excel 2010. I ended up using this.

<td x:num>123</td>

回答6:

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.