SIVANANDA REDDY GANGIREDDY: ASP.Net GridView Export only selected columns to Excel Sheet

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" >

<asp:TemplateField>

<asp:CheckBox ID="chkCol0" runat="server" Checked = "true" />

<asp:Label ID="lblCol0" runat="server" Text = "CustomerID"/>

</HeaderTemplate>

<asp:Label ID="lblCustomerID" runat="server"

Text='<%# Eval("CustomerID")%>' />

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<asp:CheckBox ID="chkCol1" runat="server" Checked = "true"/>

<asp:Label ID="lblCol1" runat="server" Text = "ContactName" />

</HeaderTemplate>

<asp:Label ID="lblContactName" runat="server"

Text='<%# Eval("ContactName")%>'/>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<asp:CheckBox ID="chkCol2" runat="server" Checked = "true" />

<asp:Label ID="lblCol2" runat="server" Text = "City"></asp:Label>

</HeaderTemplate>

<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

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

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

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

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

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