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

Update title please - people will downvote the question otherwise. — xan
– xan, Commented Dec 7, 2008 at 14:42
I have posted the EXACT answer to your question, even the code sample refered in my solution matches yours! — Binoj Antony
– Binoj Antony, Commented Dec 29, 2009 at 7:19

Greg · Accepted Answer · 2008-12-07 14:42:19Z

4

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.

answered Dec 7, 2008 at 14:42

Greg

323k55 gold badges378 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Turnkey · Accepted Answer · 2008-12-07 14:41:10Z

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.

answered Dec 7, 2008 at 14:41

Turnkey

9,4463 gold badges30 silver badges36 bronze badges

Comments

Binoj Antony · Accepted Answer · 2009-12-29 07:25:25Z

1

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)

edited Dec 29, 2009 at 7:25

answered Dec 29, 2009 at 7:11

Binoj Antony

16.3k25 gold badges92 silver badges98 bronze badges

1 Comment

Binoj Antony Over a year ago

Yoda deletes my delusional comments and his answer! Hmmm, wondering why... you could have at least upvoted me and then deleted :)

Russ Cam · Accepted Answer · 2008-12-07 14:44:57Z

0

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

answered Dec 7, 2008 at 14:44

Russ Cam

126k34 gold badges211 silver badges275 bronze badges

Comments

mson · Accepted Answer · 2008-12-07 15:00:02Z

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.

Robert Tyson · Accepted Answer · 2011-05-18 04:36:26Z

0

If you can change the cell value and it does not a part of formula try to change digits to letters, for instance: 001200 --> OO12OO :)

answered May 18, 2011 at 4:36

Robert Tyson

712 bronze badges

Comments

Robert Tyson · Accepted Answer · 2011-05-18 04:45:33Z

0

I had similar problems few months ago, and I gave up from Excel... I would recommend you to use Excel Jet Cell .NET component.

it has a free version with some size limitations. Look at the Excel C# sample:

// Open template
string xlfile = @"d:\prj\HrePro.xlsx";
ExcelWorkbook Wbook = ExcelWorkbook.ReadXLSX(xlfile);
ExcelCellCollection Cells = Wbook.Worksheets.Add("Sheet1").Cells;

// Save values and excel file
Cells["b2"].Value = "001";
Cells["b2"].Style.StringFormat = DefinedFormats.Textual;

Wbook.WriteXLSX(@"d:\prj\HReport.xlsx");

answered May 18, 2011 at 4:45

Robert Tyson

712 bronze badges

Comments

jafar sathik · Accepted Answer · 2013-02-16 09:34:29Z

0

Add One More Line Before

Response.Write("
 td { mso-number-format:\@; } 
");

so simple

answered Feb 16, 2013 at 9:34

jafar sathik

1

Collectives™ on Stack Overflow

Excel cell formatting by ASP.NET

8 Answers 8

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related