I've recently been working on a ASP.NET application that requires a large set of data to be exported to Excel. Now the actual exportation seems to be find I use the following method:
where the actual html data is of course much larger, and generated through rendering a datagrid control out to an HtmlTextWriter, and then out to response.write through a StringWriter. Now the issue I am having is that in newer versions of Excel (2002 and
above), the numeric data is coming across as text (seems to be when values are less than 1, i.e 0.06117 etc.). This does not seem to be an issue in Excel 2000 as the values always show up as numeric. Are there any options in excel to automatically convert
text numbers into true numeric ones. Not a manual conversion, I already know I can specify how to do that to the end users, but I'd rather automate as much as possible.
Well I read through several posts / sites, and all pointed to using the mso-number-format parameter in a style tag (example). However while I was successful
in injecting the style and class assignments to each of the table data entries, it made no difference when the final *.xls file was opened in Excel 2002. Note that I was using mso-number-format:"0\.00000000",
to attempt to insure that the values ended up being numbers and not text. So I'm still at a huge loss here. Currently my customers using Excel 2002 need to select thousands of values and convert them all form text to number before being able to perform any
operations on them. Why is it Excel 2000 does not have this issue, but Excel 2002 does? Once again, note I can not use the ExcelML format as Excel 2000 does not support it.
Is mso-number-format the proper parameter to be using when you want to format HTML for use in Excel?
If not what exactly is the proper way to tell Excel, through HTML, that you want a value read a particular way?
Xorcist
Member
357 Points
183 Posts
Exporting HTML tables to Excel (Text to Number Issue)
Aug 25, 2006 02:13 PM|LINK
I've recently been working on a ASP.NET application that requires a large set of data to be exported to Excel. Now the actual exportation seems to be find I use the following method:
Response.Clear()
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Response.AddHeader("Content-Disposition", "attachment; filename=whatever.xls;")
Response.Write("<table border=0><tr><td>DATA<td></tr></table>")
Response.End()
where the actual html data is of course much larger, and generated through rendering a datagrid control out to an HtmlTextWriter, and then out to response.write through a StringWriter. Now the issue I am having is that in newer versions of Excel (2002 and above), the numeric data is coming across as text (seems to be when values are less than 1, i.e 0.06117 etc.). This does not seem to be an issue in Excel 2000 as the values always show up as numeric. Are there any options in excel to automatically convert text numbers into true numeric ones. Not a manual conversion, I already know I can specify how to do that to the end users, but I'd rather automate as much as possible.
Xorcist
Member
357 Points
183 Posts
Re: Exporting HTML tables to Excel (Text to Number Issue)
Sep 07, 2006 05:02 PM|LINK
Caddre
All-Star
26581 Points
5308 Posts
Re: Exporting HTML tables to Excel (Text to Number Issue)
Sep 07, 2006 05:09 PM|LINK
Try the threads below for several code samples some from Microsoft geared to XP/2003 play with it one will work. Hope this helps.
http://forums.asp.net/921410/ShowPost.aspx
http://forums.asp.net/thread/1340341.aspx
Gift Peddie
Xorcist
Member
357 Points
183 Posts
Re: Exporting HTML tables to Excel (Text to Number Issue)
Sep 20, 2006 03:02 PM|LINK
Well I read through several posts / sites, and all pointed to using the mso-number-format parameter in a style tag (example). However while I was successful in injecting the style and class assignments to each of the table data entries, it made no difference when the final *.xls file was opened in Excel 2002. Note that I was using mso-number-format:"0\.00000000", to attempt to insure that the values ended up being numbers and not text. So I'm still at a huge loss here. Currently my customers using Excel 2002 need to select thousands of values and convert them all form text to number before being able to perform any operations on them. Why is it Excel 2000 does not have this issue, but Excel 2002 does? Once again, note I can not use the ExcelML format as Excel 2000 does not support it.
Is mso-number-format the proper parameter to be using when you want to format HTML for use in Excel?
If not what exactly is the proper way to tell Excel, through HTML, that you want a value read a particular way?
Caddre
All-Star
26581 Points
5308 Posts
Re: Exporting HTML tables to Excel (Text to Number Issue)
Sep 20, 2006 04:16 PM|LINK
Execl 2000 saves as HTML but Excel 2002/2003 saves as XML also and XML is the default.
http://weblogs.asp.net/datagridgirl/archive/2003/04/14/5631.aspx
Gift Peddie