ASP.NET CSV Response, Excel Exponential Format Issue

Question

Hi,

In my ASP.NET application, I need to write DataTable as CSV response to the customer. Everything is working fine except a column which has a numbers.

Ex: 7002136138603600000

But when I open the CSV in Excel, it is showing in exponential format.

Something like this: 7E+18

Could you please let me know what needs to be done in order to show these values as test instead in exponential format?

Thanks, Mahesh

Answer 1

Open up the file in a text editor and you will likely find that the numbers are exporting correctly. Excel simply defaults to exponential format for some long numbers.

Answer 2

Does it HAVE to be CSV? If so, then you're pretty much stuck with the default behavior from Excel.

If it doesn't have to be CSV, then look into the Excel XML format, which will give you much finer control over the display formats (anything that can be done within Excel can be done in the XML file string). Of course, this is only supported in Office 2003 and later.

Answer 3

You can't do it with CSV. Try it with Excel. Create a spreadsheet with the value "7002136138603600000" in a cell, preceded by a single quote (') to indicate that it's text. Excel will display the full number correctly. Save it as CSV. Reload. The number will be displayed using exponential format.

Alternatively you can use HTML. Add the namespace xmlns:x="urn:schemas-microsoft-com:office:excel" to your html root and at this attribute to your td element.

x:str="'7002136138603600000"

Excel will display it as text. For a more complete example create the Excel doc like I mentioned and save as html.

You can also use XML if you know your customers are using Excel 2003 or newer.

Answer 4

Regarding the CSV numbers format, check here: http://support.microsoft.com/kb/214233

---

I did export to Excel many times. Just write the output of the Gridview to a String Builder using an HtmlTextWriter send the response.

You can find two examples of doing this:

• By hand: http://geekswithblogs.net/azamsharp/archive/2005/12/21/63843.aspx
• Ready-made Utility Class http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html

More search results: http://www.google.com/search?q=export+gridview+to+excel

So, I think the easiest way is to bind to GridView and write it as HTML as in the other examples, unless you need CSV for another reason also.