Excel cell formatting by ASP.NET

Question

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

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 2

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.