Click here to Skip to main content
Click here to Skip to main content
Go to top

Avoid default formatting when exporting data from datagrid to excel.

, 14 Oct 2010
Rate this:
Please Sign up or sign in to vote.
Export to Excel,default formatting of excel,excel number format,excel date format,excel currency format

RenderControl method of Datagrid : Helps in exporting the grid data to excel. But for dates,currencies, numbers it will automatically formats that data. The user has little or no control on that through code. Say for eg: 123343423434 number converts into exponential number. If the user doesnt deserves the formatting either one has to preface the data with an apostrophe. or select the column Format cells->General->Select the required one (Number ,Currency,Date) as per the requirement.
One cannot "set" Excel to stop thinking those are dates.

The work around is

For formatting the text use

mso-number-format:"\@";

For formatting the currency use

mso-number-format:"\0022$\0022\#\,\#\#0\.00";

In the attached application I kept a datagrid and 5 buttons on the form. Each button click helps in formatting different kinds of data say : String , Number ,Currency, Date. On button click I am building dataset through programmatically and binding the dataset to grid and using Rendercontrol method of datagrid I am exporting the data to excel.

Response.Cache.SetExpires(DateTime.Now.AddSeconds(1));
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
Response.Write("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
Response.Write("\r\n");
Response.Write("<style>  .mystyle1 " + "\r\n" + "{mso-style-parent:style0;mso-number-format:\""+@"\@"+"\""+";} " + "\r\n" + "</style>");
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
DataGrid1.RenderControl(hw);
Response.AppendHeader("content-disposition","attachment;filename=x.xls");
Response.Write(tw.ToString());
Response.End();

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

ambatisreedhar
Web Developer
India India
I am Sreedhar Ambati working as a developer. I am having 6+ yrs of experience in Microsoft technologies like ASP,VB,ASP.Net, Vb.Net, C#.Net, Microsoft Dynamics CRM and Sharepoint Server 2007.
I used to blog at: http://ambatisreedharworks.wordpress.com and http://ambatisreedhar.spaces.live.com

Comments and Discussions

 
Questioni need to save the file to alocation instead of downloading PinmemberMember 1011261116-Sep-13 23:22 
GeneralMy vote of 5 Pinmemberkreplech20-May-13 5:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 14 Oct 2010
Article Copyright 2010 by ambatisreedhar
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid