Avoid default formatting when exporting data from datagrid to excel.

ambatisreedhar

Rate me:

4.67/5 (5 votes)

14 Oct 2010CPOL

43.8K

Export to Excel,default formatting of excel,excel number format,excel date format,excel currency format

Download project

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("&lt;html xmlns:x=\"urn:schemas-microsoft-com:office:excel\"&gt;");
Response.Write("\r\n");
Response.Write("&lt;style&gt;  .mystyle1 " + "\r\n" + "{mso-style-parent:style0;mso-number-format:\""+@"\@"+"\""+";} " + "\r\n" + "&lt;/style&gt;");
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)

Written By

ambatisreedhar

Web Developer

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

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

Avoid default formatting when exporting data from datagrid to excel.

License

Comments and Discussions