Excel cell formatting by ASP.NET

Question

Hi, I have a problem. One of the datatable colums value is a string value '001200' for example. When the Excel document creats the value became '1200'. How can I keep a data format as is? I'm working with ASP.NET 1.1.

The part of the code is:

private void lnkExport_Click( object sender, System.EventArgs e ) 
{

Response.Clear();

Response.Buffer= true;

Response.ContentType = "application/vnd.ms-excel";

Response.AddHeader( "Content-Disposition", "attachment; filename=" + "CartsList.xls" );

Response.Charset = "iso-8859-8";

Response.Cache.SetCacheability( HttpCacheability.Public );

Response.ContentEncoding = System.Text.Encoding.UTF7;

this.EnableViewState = false;

System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter( oStringWriter );

this.ClearControls( dtgCarts );

dtgCarts.RenderControl( oHtmlTextWriter );

Response.Write( oStringWriter.ToString() );

Response.End();

}

Thank you

Answer 1

Have you tried simply reformatting the cell in Excel? It's probably just that the Excel is choosing a different format (integer rather than string).

Here is an article on Excel formatting from .Net.

Answer 2

Add an apostrophe (single quote) to the start - that should fix it. It forces Excel to treat the cell as text instead of a number.

Edit: just to be clear - the apostrophe thing is a built-in feature of an Excel, not just a random character I picked. Can't seem to find a link for it though.

Answer 3

Can you precede any that need to be kept as numbers with a single quote when exported to excel e.g.

'001200

This should keep it as text

Answer 4

I ran into something similar to your problem, but in the other direction.

This is the extended properties setting for a Excel 2007 file.
"Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'";

You may also want to write dummy records with the format of the data you want to use. Declaring something as Text in Excel doesn't necessarily convert the data the way you want it to.

There is something behind the scenes that automatically sets the data type used during transfer. The system uses the first 8 records to determine this. If you want string values, buffer the column with any text, if you need something longer I think you need to buffer it with text of 512+ characters.

BTW - I tried most of the above suggestions above before finding the system secretly overrides what you programmatically set.

Answer 5

thanks a lot for the information, it's really a useful one

Answer 6

The above single quote method retains my leading zeroes but problem with above method is on excel sheet the single quote is still visible.only after pressing enter on the cell the formatting occurs and single quote is not visible.

Answer 7

This link has the EXACT answer to your question. The article uses the exact same technique you are using to render html to excel.

The trick is to use the excel css properties

<style> .text { mso-number-format:\@; } </style> 

[Edit]
One more link with a similar issue. (Its in cold fusion but the CSS apply part can be used)