Export Data From GridView To Excel in Asp.net

A good way to display data is to show it in a GridView. However, it becomes difficult to manipulate and filter large amounts of data in this way. Exporting data to an Excel file is a great solution for handling large amounts of data because Excel has many features — such as sorting, searching and filtering.

Design Page—————————————————————————-

<asp:GridView ID=”GridView1″ runat=”server” EmptyDataText=”No Record.”
AutoGenerateColumns=”False” EnableModelValidation=”True”
AllowPaging=”True” PageSize=”7″ Width=”900px”>
<Columns>
<asp:TemplateField HeaderText=”ID”>
<ItemTemplate>
<asp:Label ID=”Label2″ Text='<%#Eval(“id”) %>’ runat=”server”></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”First Name”>
<ItemTemplate>
<asp:Label id=”lblname” Text='<%#Eval(“Name”) %>’ runat=”server”></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Last Name”>
<ItemTemplate>
<asp:Label ID=”Label1″ Text='<%#Eval(“LName”) %>’ runat=”server”></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Designation”>
<ItemTemplate>
<asp:Label ID=”lbldes” Text='<%#Eval(“Designation”) %>’ runat=”server”></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Age”>
<ItemTemplate>
<asp:Label ID=”lblAge” Text='<%#Eval(“Age”) %>’ runat=”server”></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”Phone No”>
<ItemTemplate>
<asp:Label ID=”lblPhone” Text='<%#Eval(“Phone”) %>’ runat=”server”></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>

</asp:GridView>

<asp:ImageButton ID=”ButtonNextDoPayment” runat=”server”  ImageUrl=”images/Excel.jpg”
OnClick=”ButtonNextDoPayment_Click”  style=”height:auto;width:155px” />

Server Side Page———————————————————————————–

protected void ButtonNextDoPayment_Click(object sender, EventArgs e)
{
DataTable dTable = getTable();
ExportDataSetToExcel(dTable, GridView1, “TEST”);

}
public void ExportDataSetToExcel(DataTable dTable, GridView grid, string filename)
{
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.Charset = “”;
response.ContentType = “application/vnd.ms-excel”;
response.AddHeader(“Content-Disposition”, “attachment;filename=\”” + filename + “.xls”);

int rowCount = GridView1.Rows.Count;
GridViewRow row = null;

for (int index = 0; index < rowCount; index++)
{
GridView1.AllowPaging = false;
row = GridView1.Rows[index];
DataRow request = dTable.NewRow();
Label lblProductName = (Label)row.FindControl(“Label2”);
request[“id”] = lblProductName.Text.ToString();
Label lblProductNumber = (Label)row.FindControl(“lblname”);
request[“Name”] = lblProductNumber.Text.ToString();
Label lblProductPrice = (Label)row.FindControl(“Label1”);
request[“LName”] = lblProductPrice.Text.ToString();
Label desi = (Label)row.FindControl(“lbldes”);
request[“Designation”] = desi.Text.ToString();
Label age = (Label)row.FindControl(“lblAge”);
request[“Age”] = age.Text.ToString();
Label phone = (Label)row.FindControl(“lblPhone”);
request[“Phone”] = phone.Text.ToString();
dTable.Rows.Add(request);
}
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
DataGrid dg = new DataGrid();
dg.HeaderStyle.Font.Bold = true;
dg.DataSource = dTable;
dg.DataBind();
dg.RenderControl(htw);
response.Write(sw.ToString());
response.End();
}
}
}
DataTable getTable()
{

DataTable dt2 = new DataTable(“ExcelReport”);

dt2.Columns.Add(“id”);
dt2.Columns.Add(“Name”);
dt2.Columns.Add(“LName”);
dt2.Columns.Add(“Designation”);
dt2.Columns.Add(“Age”);
dt2.Columns.Add(“Phone”);
return dt2;
}