How to Export Data from Gridview to Excel in Asp.net Using C# and VB Programing

To export gridview data to excel we need to write the code like as shown below

C# Coding

using System;
using System.Data;
using System.IO;
using System.Web.UI;

protected void Page_Load(object sender, EventArgs e)
{
 if (!IsPostBack)
 {
  BindGridview();
 }
}
protected void BindGridview()
{
 DataTable dt = new DataTable();
 dt.Columns.Add("UserId", typeof(Int32));
 dt.Columns.Add("UserName", typeof(string));
 dt.Columns.Add("Education", typeof(string));
 dt.Columns.Add("Location", typeof(string));
 dt.Rows.Add(1, "SureshDasari", "B.Tech", "Chennai");
 dt.Rows.Add(2, "MadhavSai", "MBA", "Nagpur");
 dt.Rows.Add(3, "MaheshDasari", "B.Tech", "Nuzividu");
 dt.Rows.Add(4, "Rohini", "MSC", "Chennai");
 dt.Rows.Add(5, "Mahendra", "CA", "Guntur");
 dt.Rows.Add(6, "Honey", "B.Tech", "Nagpur");
 gvDetails.DataSource = dt;
 gvDetails.DataBind();
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
protected void btnExport_Click(object sender, EventArgs e)
{
 Response.ClearContent();
 Response.Buffer = true;
 Response.AddHeader("content-disposition", string.Format("attachment; filename={0}","Customers.xls"));
 Response.ContentType = "application/ms-excel";
 StringWriter sw = new StringWriter();
 HtmlTextWriter htw = new HtmlTextWriter(sw);
 gvDetails.AllowPaging = false;
 BindGridview();
 //Change the Header Row back to white color
 gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF");
 //Applying stlye to gridview header cells
 for (int i = 0; i < gvDetails.HeaderRow.Cells.Count; i++)
  {
   gvDetails.HeaderRow.Cells[i].Style.Add("background-color", "#df5015");
  }
  gvDetails.RenderControl(htw);
  Response.Write(sw.ToString());
  Response.End();
}

If you observe above code I added one function that is VerifyRenderingInServerForm this function is used to avoid the error like “control must be placed in inside of form tag”. If we setVerifyRenderingInServerForm function then compiler will think that controls rendered before exporting and our functionality will work perfectly.

VB.NET Coding

Imports System.Data
Imports System.IO
Imports System.Web.UI

Partial Class ExportGridviewDatainVB
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
 BindGridview()
End If
End Sub

Protected Sub BindGridview()
 Dim dt As New DataTable()
 dt.Columns.Add("UserId", GetType(Int32))
 dt.Columns.Add("UserName", GetType(String))
 dt.Columns.Add("Education", GetType(String))
 dt.Columns.Add("Location", GetType(String))
 dt.Rows.Add(1, "SureshDasari", "B.Tech", "Chennai")
 dt.Rows.Add(2, "MadhavSai", "MBA", "Nagpur")
 dt.Rows.Add(3, "MaheshDasari", "B.Tech", "Nuzividu")
 dt.Rows.Add(4, "Rohini", "MSC", "Chennai")
 dt.Rows.Add(5, "Mahendra", "CA", "Guntur")
 dt.Rows.Add(6, "Honey", "B.Tech", "Nagpur")
 gvDetails.DataSource = dt
 gvDetails.DataBind()
End Sub

Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
' Verifies that the control is rendered

End Sub

Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As EventArgs)
 Response.ClearContent()
 Response.Buffer = True
 Response.AddHeader("content-disposition", String.Format("attachment; filename={0}","Customers.xls"))
 Response.ContentType = "application/ms-excel"
 Dim sw As New StringWriter()
 Dim htw As New HtmlTextWriter(sw)
 gvDetails.AllowPaging = False
 BindGridview()
'Change the Header Row back to white color
 gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF")
'Applying stlye to gridview header cells
 For i As Integer = 0 To gvDetails.HeaderRow.Cells.Count - 1
 gvDetails.HeaderRow.Cells(i).Style.Add("background-color", "#df5015")
Next
 gvDetails.RenderControl(htw)
 Response.Write(sw.ToString())
 Response.[End]()
End Sub
End Class

================================================================================

Demo

Whenever we run application we will see the screen like as shown below

Once we click on Export to Excel button we will see data in excel file like as shown below