Avoid default formatting when exporting data from datagrid to excel.
4.67/5 (5 votes)
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();