Wednesday, November 30, 2011

Export datagrid to Excel in asp.net (Revised)

How to export datagrid data into excel
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("Employee Name");
dtTemp.Columns.Add("Email");
dtTemp.Columns.Add("Join Date");
dtTemp.Columns.Add("Salary");
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();
}

No comments:

Post a Comment