Thursday, December 17, 2009

Export To Excel in Asp.Net

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
DataTable dtOriginal = new DataTable();
dtOriginal = BindDataList(); //Return Table consisting data

//Create Tempory Table
DataTable dtTemp = new DataTable();

//Creating Header Row
dtTemp.Columns.Add("FirstName");
dtTemp.Columns.Add("EmpCode");
dtTemp.Columns.Add("Location");
//double dSalary;
//DateTime dtDate;
DataRow drAddItem;
for (int i = 0; i < dtOriginal.Rows.Count; i++)
{
drAddItem = dtTemp.NewRow();
drAddItem[0] = dtOriginal.Rows[i]["FirstName"].ToString();//FirstName
drAddItem[1] = dtOriginal.Rows[i]["EmpCode"].ToString();//EmpCode
drAddItem[2] = dtOriginal.Rows[i]["Location"].ToString();//Location

//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);
}

gvEmployee.DataSource = BindDataList();
gvEmployee.DataBind();
ExportToExcelSheet("EmployeeDetails.xls", gvEmployee);
}

private void ExportToExcelSheet(string strFileName, GridView 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();
}
private DataTable BindDataList()
{
string sql = "SELECT TOP 10 * FROM TBL_EMPLOYEES";
SqlDataAdapter da = new SqlDataAdapter(sql, dataObj.GetConnString());
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}

public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for the
// specified ASP.NET server control at run time.
// No code required here.
}

No comments:

Post a Comment