In this article I will explain with an example, how to export only selected columns of GridView to Excel Sheet in ASP.Net using C# and VB.Net.
The user will be allowed to select the Columns to be exported to Excel File with the help of CheckBoxes and thus only the fields which are selected will be exported to Excel Sheet in ASP.Net using C# and VB.Net.
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
HTML Markup
The following HTML Markup consists of an ASP.Net GridView with three TemplateField Columns. Each TemplateField column consists of a HeaderTemplate and an ASP.Net CheckBox is placed inside it.
Below the GridView, there is an ASP.Net Button which has been assigned an OnClick event handler.
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" Font-Names="Arial"
Font-Size="11pt" AlternatingRowStyle-BackColor="#C2D69B" HeaderStyle-BackColor="green"
AllowPaging="true" OnPageIndexChanging="OnPaging">
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID="chkCol0" runat="server" Checked="true" />
<asp:Label ID="lblCol0" runat="server" Text="CustomerID" />
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblCustomerID" runat="server" Text='<%# Eval("CustomerID")%>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID="chkCol1" runat="server" Checked="true" />
<asp:Label ID="lblCol1" runat="server" Text="ContactName" />
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblContactName" runat="server" Text='<%# Eval("ContactName")%>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID="chkCol2" runat="server" Checked="true" />
<asp:Label ID="lblCol2" runat="server" Text="City"></asp:Label>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblCity" runat="server" Text='<%# Eval("City")%>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
<br />
<asp:Button ID="btnExportExcel" runat="server" Text="ExportToExcel" OnClick="btnExportExcel_Click" />
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
using System.IO;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Binding the ASP.Net GridView control
Inside the Page Load event, the GridView is populated from SQL Server Database using the BindGrid method.
The GetCheckBoxStates is only called when PostBack occurs and its job is to maintain the states of the GridView header CheckBoxes in ViewState object.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
GetCheckBoxStates();
BindGrid();
}
private void BindGrid()
{
string strQuery = "select CustomerID,City,ContactName from customers";
DataTable dt = new DataTable();
String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand(strQuery);
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
try
{
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
sda.Dispose();
con.Dispose();
}
}
private void GetCheckBoxStates()
{
CheckBox chkCol0 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkCol0");
CheckBox chkCol1 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkCol1");
CheckBox chkCol2 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkCol2");
ArrayList arr;
if (ViewState["States"] == null)
{
arr = new ArrayList();
}
else
{
arr = (ArrayList)ViewState["States"];
}
arr.Add(chkCol0.Checked);
arr.Add(chkCol1.Checked);
arr.Add(chkCol2.Checked);
ViewState["States"] = arr;
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If IsPostBack Then
GetCheckBoxStates()
End If
BindGrid()
End Sub
Private Sub BindGrid()
Dim strQuery As String = "select CustomerID,City,ContactName from customers"
Dim dt As New DataTable()
Dim strConnString As [String] = System.Configuration.ConfigurationManager.ConnectionStrings("conString").ConnectionString
Dim con As New SqlConnection(strConnString)
Dim sda As New SqlDataAdapter()
Dim cmd As New SqlCommand(strQuery)
cmd.CommandType = CommandType.Text
cmd.Connection = con
Try
con.Open()
sda.SelectCommand = cmd
sda.Fill(dt)
GridView1.DataSource = dt
GridView1.DataBind()
Catch ex As Exception
Throw ex
Finally
con.Close()
sda.Dispose()
con.Dispose()
End Try
End Sub
Private Sub GetCheckBoxStates()
Dim chkCol0 As CheckBox = DirectCast(GridView1.HeaderRow.Cells(0).FindControl("chkCol0"), CheckBox)
Dim chkCol1 As CheckBox = DirectCast(GridView1.HeaderRow.Cells(0).FindControl("chkCol1"), CheckBox)
Dim chkCol2 As CheckBox = DirectCast(GridView1.HeaderRow.Cells(0).FindControl("chkCol2"), CheckBox)
Dim arr As ArrayList
If ViewState("States") Is Nothing Then
arr = New ArrayList()
Else
arr = DirectCast(ViewState("States"), ArrayList)
End If
arr.Add(chkCol0.Checked)
arr.Add(chkCol1.Checked)
arr.Add(chkCol2.Checked)
ViewState("States") = arr
End Sub
Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
' Verifies that the control is rendered
End Sub
Exporting the GridView with selected columns to Excel Sheet
When the Export Button is clicked all the checked Columns of the GridView control are made visible while all the unchecked GridView columns are hidden, and thus only the selected GridView columns are rendered as HTML and ultimately exported to Excel Sheet in ASP.Net using C# and VB.Net.
C#
protected void btnExportExcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView1.AllowPaging = false;
GridView1.DataBind();
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");
ArrayList arr = (ArrayList)ViewState["States"];
GridView1.HeaderRow.Cells[0].Visible = Convert.ToBoolean(arr[0]);
GridView1.HeaderRow.Cells[1].Visible = Convert.ToBoolean(arr[1]);
GridView1.HeaderRow.Cells[2].Visible = Convert.ToBoolean(arr[2]);
GridView1.HeaderRow.Cells[0].FindControl("chkCol0").Visible = false;
GridView1.HeaderRow.Cells[1].FindControl("chkCol1").Visible = false;
GridView1.HeaderRow.Cells[2].FindControl("chkCol2").Visible = false;
for (int i = 0; i < GridView1.Rows.Count; i++)
{
GridViewRow row = GridView1.Rows[i];
row.Cells[0].Visible = Convert.ToBoolean(arr[0]);
row.Cells[1].Visible = Convert.ToBoolean(arr[1]);
row.Cells[2].Visible = Convert.ToBoolean(arr[2]);
row.BackColor = System.Drawing.Color.White;
row.Attributes.Add("class", "textmode");
if (i % 2 != 0)
{
row.Cells[0].Style.Add("background-color", "#C2D69B");
row.Cells[1].Style.Add("background-color", "#C2D69B");
row.Cells[2].Style.Add("background-color", "#C2D69B");
}
}
GridView1.RenderControl(hw);
string style = @"<style> .textmode { } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.End();
}
VB.Net
Protected Sub btnExportExcel_Click(ByVal sender As Object, ByVal e As EventArgs)
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Dim sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)
GridView1.AllowPaging = False
GridView1.DataBind()
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF")
GridView1.HeaderRow.Cells(0).Style.Add("background-color", "green")
GridView1.HeaderRow.Cells(1).Style.Add("background-color", "green")
GridView1.HeaderRow.Cells(2).Style.Add("background-color", "green")
Dim arr As ArrayList = DirectCast(ViewState("States"), ArrayList)
GridView1.HeaderRow.Cells(0).Visible = Convert.ToBoolean(arr(0))
GridView1.HeaderRow.Cells(1).Visible = Convert.ToBoolean(arr(1))
GridView1.HeaderRow.Cells(2).Visible = Convert.ToBoolean(arr(2))
GridView1.HeaderRow.Cells(0).FindControl("chkCol0").Visible = False
GridView1.HeaderRow.Cells(1).FindControl("chkCol1").Visible = False
GridView1.HeaderRow.Cells(2).FindControl("chkCol2").Visible = False
For i As Integer = 0 To GridView1.Rows.Count - 1
Dim row As GridViewRow = GridView1.Rows(i)
row.Cells(0).Visible = Convert.ToBoolean(arr(0))
row.Cells(1).Visible = Convert.ToBoolean(arr(1))
row.Cells(2).Visible = Convert.ToBoolean(arr(2))
row.BackColor = System.Drawing.Color.White
row.Attributes.Add("class", "textmode")
If i Mod 2 <> 0 Then
row.Cells(0).Style.Add("background-color", "#C2D69B")
row.Cells(1).Style.Add("background-color", "#C2D69B")
row.Cells(2).Style.Add("background-color", "#C2D69B")
End If
Next
GridView1.RenderControl(hw)
Dim style As String = "<style> .textmode { } </style>"
Response.Write(style)
Response.Output.Write(sw.ToString())
Response.End()
End Sub
Implement Paging in GridView
The OnPageIndexChanging event handles the Pagination in the GridView.
Inside the OnPageIndexChanging event handler, the GridView’s PageIndex property is updated and the DataBind method of the GridView is called.
C#
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}
VB.Net
Protected Sub OnPaging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
GridView1.PageIndex = e.NewPageIndex
GridView1.DataBind()
End Sub
Screenshots
ASP.Net GridView with ability to select/unselect columns
![ASP.Net GridView Export to Excel - Hide Columns]()
Exported Excel sheet with selected columns
![ASP.Net GridView Export to Excel - Hide Columns]()
Demo
Downloads
