Previously I had Post for Export Datagrid Data to Excel
It was perfectly alright but following Article has added Advantage over the Previous Post.
- You can able to Hide Few Fields and Display desired fields in Exported Excel Sheet, bydefault all the fields which are displayed in datagrid was displayed in Excel, but let say we want to hide "Edit" and "Delete" field while exporting datagrid data to excel. (Note: You can also use datagrid1.Columns[4].Visible = false;)
- Format Datagrid Data. Full control over formatting of data as compare to previous method.
- Replace Control before exporting to excel. eg: Removing Hyperlink, dropdownlist, checkbox and other controls before exporting data to excel.
- Can Export All data @ once, bydefault you can only export page full of data.
Lets understand by example, consider following datagrid, which contain "Employee Name" and "Email" as Hyperlink, but while exporting data i want Hyperlink from email should be removed.
Utility Code: It is self explanatory
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
DataTable dtOriginal = new DataTable();
dtOriginal = ReturnTable(); //Return Table consisting data
//Create Tempory Table
DataTable dtTemp = new DataTable();
//Creating Header Row
dtTemp.Columns.Add("<b>Employee Name</b>");
dtTemp.Columns.Add("<b>Email</b>");
dtTemp.Columns.Add("<b>Join Date</b>");
dtTemp.Columns.Add("<b>Salary</b>");
double dSalary;
DateTime dtDate;
DataRow drAddItem;
for (int i = 0; i < dtOriginal.Rows.Count; i++)
{
drAddItem = dtTemp.NewRow();
drAddItem[0] = dtOriginal.Rows[i][0].ToString();//Name
drAddItem[1] = dtOriginal.Rows[i][1].ToString();//Email
//Join Date
dtDate = Convert.ToDateTime(dtOriginal.Rows[i][2].ToString());
drAddItem[2] = dtDate.ToShortDateString();
//Salary
dSalary = Convert.ToDouble(dtOriginal.Rows[i][3].ToString());
drAddItem[3] = dSalary.ToString("C");
dtTemp.Rows.Add(drAddItem);
}
//Temp Grid
DataGrid dg = new DataGrid();
dg.DataSource = dtTemp;
dg.DataBind();
ExportToExcel("BudgeReport.xls", dg);
dg = null;
dg.Dispose();
}
private void ExportToExcel(string strFileName, DataGrid dg)
{
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
Response.ContentType = "application/excel";
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
dg.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
