In this article we will explore export to excel feature of DataTable with formating of column in the excel.

Let's see how we can achieve this.
Step 1: Add a button in the aspx page.
< asp:Button ID="btnExport" Text="Export" runat="server" OnClick="ExportToExcel" />
Step 2: Add below code behind method which will be fired on click of Export button.
protected void ExportToExcel(object sender, EventArgs e) { DataTable dt = new DataTable(); dt.Columns.Add("Number"); dt.Columns.Add("Text"); dt.Columns.Add("Currency"); dt.Columns.Add("Date"); dt.Columns.Add("Percentage"); dt.Rows.Add("546", "This is Text", "546", "16/8/2011", "25"); dt.Rows.Add("5461", "This is again Text", "5462", "20/9/2011", "35");
//Double dimensional array to keep style name and style string[,] styles = { { "number", "0\\.00;" }, { "text", "\\@;" }, { "currency", "\\[\\$\\$\\-\\409\\]\\#\\,\\#\\#0\\.00;" }, { "date", "mm\\-dd\\-yy\\;\\@;" }, { "percent", "0\\.00\\%;" } };
//Dummy GridView to hold data to be exported in excel System.Web.UI.WebControls.GridView gvExport = new System.Web.UI.WebControls.GridView(); gvExport.AllowPaging = false; gvExport.DataSource = dt; gvExport.DataBind();
StringWriter sw = new StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(sw);
int cnt = styles.Length / 2;
for (int i = 0; i < gvExport.Rows.Count; i++) { for (int j = 0; j < cnt; j++) { //Apply style to each cell gvExport.Rows[i].Cells[j].Attributes.Add("class", styles[j, 0]); } }
gvExport.RenderControl(hw); StringBuilder style = new StringBuilder(); style.Append("<style>"); for (int j = 0; j < cnt; j++) { style.Append("." + styles[j, 0] + " { mso-number-format:" + styles[j, 1] + " }"); }
style.Append("</style>"); Response.Clear(); Response.Buffer = true; Response.AddHeader("content-disposition", "attachment;filename=Export.xls");Response.Charset = ""; Response.ContentType = "application/vnd.ms-excel"; Response.Write(style.ToString()); Response.Output.Write(sw.ToString()); Response.Flush(); Response.End(); }
Below line of code in above statements sets the style of excel. The styling of first column will be number, second will be text, third will currency, fourth will be date and fifth will be percent.
string[,] styles = { { "number", "0\\.00;" }, { "text", "\\@;" }, { "currency", "\\[\\$\\$\\-\\409\\]\\#\\,\\#\\#0\\.00;" }, { "date", "mm\\-dd\\-yy\\;\\@;" }, { "percent", "0\\.00\\%;" } };
Now let's see how we can get the styling code.
Step 1: Open Excel and type any number in any cell.
Step 2: Right click and select Format Cells
Step 3: Select Currency from number tab.
Step 4: Change the Symbol dropdownlist to $ English (U.S.)

Step 5: Now click on custom you will get the style of currency which is [$$-409]#,##0.00
To use the above style to any particular column of excel all the specail charcter need to be escaped like below
\\[\\$\\$\\-\\409\\]\\#\\,\\#\\#0\\.00
So any formatting style which needs to be applied on any column of the excel can be achieved by following Step 1 to Step 5. If you want to get the style of date, select Date instead of currency in Step 3.
Live Demo
This ends the article of formatting in export to excel.
|