Geek Answers Handbook

ASPV CSV answer, Excel exponential format problem

In my ASP.NET application, I need to write a DataTable as a response to a CSV client. Everything works fine except for the column with numbers.

Example: 7002136138603600000

But when I open CSV in Excel, it displays in exponential format.

Something like this: 7E + 18

Could you tell me what needs to be done to show these values ​​as a test instead in exponential format?

Thanks Mahesh

+4
source share
5 answers

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

I have done export to Excel many times. Just write the Gridview output in String Builder using HtmlTextWriter, send the response.

You can find two examples of this:

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

So, I think the easiest way is to link to the GridView and write it as HTML, as in other examples, if you don't need CSV for another reason.

+1
source

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

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

x:str="'7002136138603600000"

Excel will display it as text. For a more complete example, create an Excel document as I mentioned, and save it as html.

You can also use XML if you know that your clients are using Excel 2003 or later.

+1
source

Open the file in a text editor and you will most likely find that the numbers are exported correctly. Excel is simply used by default for the exponential format for some long numbers.

0
source

Do I need to be CSV? If this is the case, then you almost completely adhere to the default behavior from Excel.

If it’s not necessarily CSV, look in Excel XML format, which will give you much finer control over the display formats (everything that can be done in Excel can be done in the line of the XML file). Of course, this is only supported in Office 2003 and later.

0
source

it is easy to add asc char 32, when numbers are all digits, use the function to find out if digits are all digits, and if true, then just add asi 32 to the number (convert to string if necessary). Tested csv file on microsoft excel 2007 and Windows 7, and it works fine, not sure about earlier versions.

-one
source

More articles:


All Articles