Click here to Skip to main content
13,457,828 members
Click here to Skip to main content
Add your own
alternative version


5 bookmarked
Posted 14 Oct 2010

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


For formatting the currency use


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.ContentType = "application/";
Response.Charset = "";
this.EnableViewState = false;
Response.Write("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
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);


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


About the Author

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: and

You may also be interested in...


Comments and Discussions

QuestionMultiple sheets in Single File Pin
Member 1290735815-Dec-16 19:34
memberMember 1290735815-Dec-16 19:34 
QuestionSaved my day Pin
Suhani Mody17-Jul-15 1:22
memberSuhani Mody17-Jul-15 1:22 
Questionusefull stuff Pin
NK727-Nov-14 21:13
memberNK727-Nov-14 21:13 
Questioni need to save the file to alocation instead of downloading Pin
Member 1011261116-Sep-13 23:22
memberMember 1011261116-Sep-13 23:22 
GeneralMy vote of 5 Pin
kreplech20-May-13 5:01
memberkreplech20-May-13 5:01 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04-2016 | 2.8.180323.1 | Last Updated 14 Oct 2010
Article Copyright 2010 by ambatisreedhar
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid