In my previous article GridView - Export selected records to Excel Sheet in ASP.Net I explained how to export selected rows of ASP.Net GridView Control to ExcelSheet. In this article I’ll explain how to export only selected columns of GridView to Excel Sheet. One can provide the user choice to select or unselect the respective checkboxes of columns, thus exporting only the columns or Fields that he wants to Excel Sheet
Database
I am using Microsoft’s Northwind Sample Database for this article. You can download the same using the link below
Download Northwind Database
GridView HTML Markup
<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>
As you can see I have placed one checkbox in the HeaderTemplate for each column in the GridView. These checkboxes will be used to select or unselect the columns
Binding Data
Below function is used to bind the data from the SQL Server Database to the ASP.Net GridView Control
C#
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();
}
}
VB.Net
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
As you can see above the function simply executes the SQL query and binds the returned results to the ASP.Net GridView control. The above function is called up in the Page Load Event of the ASP.Net Web Page
C#
protected void Page_Load(object sender, EventArgs e)
{
if(IsPostBack)
GetCheckBoxStates();
BindGrid();
}
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
You’ll notice I am calling one more function GetCheckBoxStates() in the Page Load event of the Page. The job of this function is to maintain the states of the GridView header checkboxes in ViewState. The function is described below
C#
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;
}
VB.Net
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
Exporting the GridView with selected columns to Excel Sheet
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 { mso-number-format:\@; } </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 { mso-number-format:\@; } </style>"
Response.Write(style)
Response.Output.Write(sw.ToString())
Response.[End]()
End Sub
As you’ll notice above everything is same as the normal GridView to Excel export the only difference is that I am hiding the GridView cells based on the values of their respective checkboxes in the Header row. In other words hiding the columns Have a look at the screenshots below
ASP.Net GridView with ability to select/unselect columns
![ASP.Net GridView control with option to check uncheck columns]()
Exported Excel sheet with selected columns
![Excel sheet displaying the selected records columns from ASP.Net GridView control]()
With this the article comes to an end. Hope you liked it. Download the related sample source code in C# and VB.Net using the link below
Download Code (5.75 kb)
