This was asked to me by many readers who read the articles of Exporting GridView to Excel. So finally here’s it. Basically I have used my following past three articles on GridView

Using JavaScript with ASP.Net GridView Control
Preserving state of Checkboxes while paging in ASP.Net GridView Control
Export GridView with Images to Word, Excel and PDF Formats in ASP.Net

to achieve this task

 

Concept

GridView has a checkbox Template field where user can select the records on one or multiple pages in the GridView control and when he clicks export button the only selected rows will be exported to the Excel document or Sheet.

 

Database

For this example I have used Northwind Sample SQL Server Database which can be downloaded using the link below.

Download Northwind Database

Once downloaded you’ll need to attach the database to you SQL Server Instance

 

GridView Markup

Below is the GridView Markup as you’ll notice below I have added a header check all checkbox and checkbox template field

<asp:GridView ID="GridView1" runat="server"

AutoGenerateColumns = "false" Font-Names = "Arial"

Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B" 

HeaderStyle-BackColor = "green" AllowPaging ="true"  

OnPageIndexChanging = "OnPaging" DataKeyNames = "CustomerID" >

<Columns>

<asp:TemplateField>

<HeaderTemplate>

  <asp:CheckBox ID="chkAll" runat="server" onclick = "checkAll(this)" />

</HeaderTemplate>

<ItemTemplate>

 <asp:CheckBox ID="CheckBox1" runat="server" onclick = "Check_Click(this)" />

</ItemTemplate>

</asp:TemplateField>

<asp:BoundField ItemStyle-Width = "150px" DataField = "CustomerID"

 HeaderText = "CustomerID" />

<asp:BoundField ItemStyle-Width = "150px" DataField = "City"

 HeaderText = "City"/>

<asp:BoundField ItemStyle-Width = "150px" DataField = "Country"

 HeaderText = "Country"/>

<asp:BoundField ItemStyle-Width = "150px" DataField = "PostalCode"

 HeaderText = "PostalCode"/>

</Columns>

</asp:GridView>


For the check all functionality I have used JavaScript. More information on the same you’ll find it on my article.

Using JavaScript with ASP.Net GridView Control


Binding the GridView

Below is the function that’s used to bind the data to the ASP.Net GridView control, which simply fires a select query on the customers table of the Northwind database.

C#

private void BindGrid()

{

    string strQuery = "select CustomerID,City,Country,PostalCode" +

        " 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,Country,PostalCode" & _

                " 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

 

Preserving the state of checkboxes

The concept of the preserving the state of checkboxes has already been explained in detailed in my article Preserving state of Checkboxes while paging in ASP.Net GridView Control . Here I am using some of its concepts to remember the list of checkboxes that are selected by the user. The idea is to allow the user to select any one or set of records on any page throughout the GridView using checkboxes

There are two functions help to achieve the functionality

C#

private void PopulateCheckBoxArray()

{

    ArrayList CheckBoxArray;

    if (ViewState["CheckBoxArray"] != null)

    {

        CheckBoxArray = (ArrayList)ViewState["CheckBoxArray"];

    }

    else

    {

        CheckBoxArray = new ArrayList();

    }

 

    int CheckBoxIndex;

    bool CheckAllWasChecked = false;

    CheckBox chkAll = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkAll");

    string checkAllIndex = "chkAll-" + GridView1.PageIndex;

    if (chkAll.Checked)

    {

        if (CheckBoxArray.IndexOf(checkAllIndex) == -1)

        {

            CheckBoxArray.Add(checkAllIndex);

        }

    }

    else

    {

        if (CheckBoxArray.IndexOf(checkAllIndex) != -1)

        {

            CheckBoxArray.Remove(checkAllIndex);

            CheckAllWasChecked = true;

        }

    }

    for (int i = 0; i < GridView1.Rows.Count; i++)

    {

        if (GridView1.Rows[i].RowType == DataControlRowType.DataRow)

        {

            CheckBox chk = (CheckBox)GridView1.Rows[i].Cells[0].FindControl("CheckBox1");

            CheckBoxIndex = GridView1.PageSize * GridView1.PageIndex + (i + 1);

            if (chk.Checked)

            {

                if (CheckBoxArray.IndexOf(CheckBoxIndex) == -1 && !CheckAllWasChecked)

                {

                    CheckBoxArray.Add(CheckBoxIndex);

                }

            }

            else

            {

                if (CheckBoxArray.IndexOf(CheckBoxIndex) != -1 || CheckAllWasChecked)

                {

                    CheckBoxArray.Remove(CheckBoxIndex);

                }

            }

        }

    }

    ViewState["CheckBoxArray"] = CheckBoxArray;

}

 

VB.Net

Private Sub PopulateCheckBoxArray()

        Dim CheckBoxArray As ArrayList

        If ViewState("CheckBoxArray") IsNot Nothing Then

            CheckBoxArray = DirectCast(ViewState("CheckBoxArray"), ArrayList)

        Else

            CheckBoxArray = New ArrayList()

        End If

 

        Dim CheckBoxIndex As Integer

        Dim CheckAllWasChecked As Boolean = False

        Dim chkAll As CheckBox = DirectCast(GridView1.HeaderRow.Cells(0).FindControl("chkAll"), CheckBox)

        Dim checkAllIndex As String = "chkAll-" & GridView1.PageIndex

        If chkAll.Checked Then

            If CheckBoxArray.IndexOf(checkAllIndex) = -1 Then

                CheckBoxArray.Add(checkAllIndex)

            End If

        Else

            If CheckBoxArray.IndexOf(checkAllIndex) <> -1 Then

                CheckBoxArray.Remove(checkAllIndex)

                CheckAllWasChecked = True

            End If

        End If

        For i As Integer = 0 To GridView1.Rows.Count - 1

            If GridView1.Rows(i).RowType = DataControlRowType.DataRow Then

                Dim chk As CheckBox = DirectCast(GridView1.Rows(i).Cells(0).FindControl("CheckBox1"), CheckBox)

                CheckBoxIndex = GridView1.PageSize * GridView1.PageIndex + (i + 1)

                If chk.Checked Then

                    If CheckBoxArray.IndexOf(CheckBoxIndex) = -1 AndAlso Not CheckAllWasChecked Then

                        CheckBoxArray.Add(CheckBoxIndex)

                    End If

                Else

                    If CheckBoxArray.IndexOf(CheckBoxIndex) <> -1 OrElse CheckAllWasChecked Then

                        CheckBoxArray.Remove(CheckBoxIndex)

                    End If

                End If

            End If

        Next

        ViewState("CheckBoxArray") = CheckBoxArray

End Sub


The above function basically maintains a list selected records list in ViewState so that while exporting we know what all records are selected by the user. This function is called up each time on page a PostBack occurs in the Page Load event of the page refer below

C#

protected void Page_Load(object sender, EventArgs e)

{

    Response.Cache.SetCacheability(HttpCacheability.NoCache);

    if (!IsPostBack)

    {

        PopulateCheckBoxArray();

    }

    BindGrid();

}

 

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

        Response.Cache.SetCacheability(HttpCacheability.NoCache)

        If Not IsPostBack Then

            PopulateCheckBoxArray()

        End If

        BindGrid()

End Sub

 

Now the next function is given below

 

C#

private void ResetCheckBoxes()

{

    if (ViewState["CheckBoxArray"] != null)

    {

        ArrayList CheckBoxArray = (ArrayList)ViewState["CheckBoxArray"];

        string checkAllIndex = "chkAll-" + GridView1.PageIndex;

 

        if (CheckBoxArray.IndexOf(checkAllIndex) != -1)

        {

            CheckBox chkAll = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkAll");

            chkAll.Checked = true;

        }

        for (int i = 0; i < GridView1.Rows.Count; i++)

        {

 

            if (GridView1.Rows[i].RowType == DataControlRowType.DataRow)

            {

                if (CheckBoxArray.IndexOf(checkAllIndex) != -1)

                {

                    CheckBox chk = (CheckBox)GridView1.Rows[i].Cells[0].FindControl("CheckBox1");

                    chk.Checked = true;

                }

                else

                {

                    int CheckBoxIndex = GridView1.PageSize * (GridView1.PageIndex) + (i + 1);

                    if (CheckBoxArray.IndexOf(CheckBoxIndex) != -1)

                    {

                        CheckBox chk = (CheckBox)GridView1.Rows[i].Cells[0].FindControl("CheckBox1");

                        chk.Checked = true;

                    }

                }

            }

        }

    }

}

 

VB.Net

Private Sub ResetCheckBoxes()

        If ViewState("CheckBoxArray") IsNot Nothing Then

            Dim CheckBoxArray As ArrayList = DirectCast(ViewState("CheckBoxArray"), ArrayList)

            Dim checkAllIndex As String = "chkAll-" & GridView1.PageIndex

 

            If CheckBoxArray.IndexOf(checkAllIndex) <> -1 Then

                Dim chkAll As CheckBox = DirectCast(GridView1.HeaderRow.Cells(0).FindControl("chkAll"), CheckBox)

                chkAll.Checked = True

            End If

            For i As Integer = 0 To GridView1.Rows.Count - 1

 

                If GridView1.Rows(i).RowType = DataControlRowType.DataRow Then

                    If CheckBoxArray.IndexOf(checkAllIndex) <> -1 Then

                        Dim chk As CheckBox = DirectCast(GridView1.Rows(i).Cells(0).FindControl("CheckBox1"), CheckBox)

                        chk.Checked = True

                    Else

                        Dim CheckBoxIndex As Integer = GridView1.PageSize * (GridView1.PageIndex) + (i + 1)

                        If CheckBoxArray.IndexOf(CheckBoxIndex) <> -1 Then

                            Dim chk As CheckBox = DirectCast(GridView1.Rows(i).Cells(0).FindControl("CheckBox1"), CheckBox)

                            chk.Checked = True

                        End If

                    End If

                End If

            Next

        End If

End Sub

 

The above function’s job is to reset the selected checkboxes for the current ASP.Net GridView Page. It simply checks whether the ViewState list of checkboxes has any checkbox that belongs to the current page. If it finds one it sets the checked property for that checkbox to true. This function is called up in the ASP.Net GridView Page index changing event given below

  

 

C#

protected void OnPaging(object sender, GridViewPageEventArgs  e)

{

    GridView1.PageIndex = e.NewPageIndex;

    GridView1.DataBind();

    ResetCheckBoxes();

}

 

VB.Net

Protected Sub OnPaging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)

        GridView1.PageIndex = e.NewPageIndex

        GridView1.DataBind()

        ResetCheckBoxes()

End Sub

 

Exporting the selected records to Excel

Below is the Export Button click event handler that will process the exporting of selected records in the ASP.Net GridView to excel

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].Visible = false;

    GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");

    GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");

    GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");

    GridView1.HeaderRow.Cells[4].Style.Add("background-color", "green");

    if (ViewState["CheckBoxArray"] != null)

    {

        ArrayList CheckBoxArray = (ArrayList)ViewState["CheckBoxArray"];

        string checkAllIndex = "chkAll-" + GridView1.PageIndex;

        int rowIdx = 0;

        for (int i = 0; i < GridView1.Rows.Count; i++)

        {

            GridViewRow row = GridView1.Rows[i];

            row.Visible = false;

 

            if (row.RowType == DataControlRowType.DataRow)

            {

                if (CheckBoxArray.IndexOf(i + 1) != -1)

                {

                    row.Visible = true;

                    row.BackColor = System.Drawing.Color.White;

                    row.Cells[0].Visible = false;

                    row.Attributes.Add("class", "textmode");

                    if (rowIdx % 2 != 0)

                    {

                        

                       row.Cells[1].Style.Add("background-color", "#C2D69B");

                       row.Cells[2].Style.Add("background-color", "#C2D69B");

                       row.Cells[3].Style.Add("background-color", "#C2D69B");

                       row.Cells[4].Style.Add("background-color", "#C2D69B");

                    }

                    rowIdx++;

                }

            }

        }

    }

    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).Visible = False

        GridView1.HeaderRow.Cells(1).Style.Add("background-color", "green")

        GridView1.HeaderRow.Cells(2).Style.Add("background-color", "green")

        GridView1.HeaderRow.Cells(3).Style.Add("background-color", "green")

        GridView1.HeaderRow.Cells(4).Style.Add("background-color", "green")

        If ViewState("CheckBoxArray") IsNot Nothing Then

            Dim CheckBoxArray As ArrayList = DirectCast(ViewState("CheckBoxArray"), ArrayList)

            Dim checkAllIndex As String = "chkAll-" & GridView1.PageIndex

            Dim rowIdx As Integer = 0

            For i As Integer = 0 To GridView1.Rows.Count - 1

                Dim row As GridViewRow = GridView1.Rows(i)

                row.Visible = False

 

                If row.RowType = DataControlRowType.DataRow Then

                    If CheckBoxArray.IndexOf(i + 1) <> -1 Then

                      row.Visible = True

                      row.BackColor = System.Drawing.Color.White

                      row.Cells(0).Visible = False

                      row.Attributes.Add("class", "textmode")

                      If rowIdx Mod 2 <> 0 Then

 

                        row.Cells(1).Style.Add("background-color", "#C2D69B")

                        row.Cells(2).Style.Add("background-color", "#C2D69B")

                        row.Cells(3).Style.Add("background-color", "#C2D69B")

                        row.Cells(4).Style.Add("background-color", "#C2D69B")

                      End If

                      rowIdx += 1

                    End If

                End If

            Next

        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 everything is similar to the normal Excel export function except that By default all rows are set Visible = false and I am looping through GridView Rows and verifying the index with that of the index stored in the list of check items. If the item is found in the checked or selected list I simply make that particular row Visible = true.

 

The screenshots below describe the ASP.Net GridView with the selected records and the same records are exported to the Excel word document


User selecting records using checkboxes in ASP.Net GridView Control


The records selected or checked by the user has been exported to Excel sheet

As you’ll notice in the document I have added an extra button called Export All which basically is the normal function to export the ASP.Net GridView to Excel that can be found in my following article Export GridView with Images to Word, Excel and PDF Formats in ASP.Net. But don't forget to set the first cell visible = false in each row in order to hide the checkboxes.

Note: You might get the following errors or exceptions while trying to export. Please click on their respective links to know the resolutions


RegisterForEventValidation can only be called during Render()

Control ’GridView1’ of type ’GridView’ must be placed inside a form tag with runat=server.

To try the Live demo, click here.



That’s it. Hope you liked the article. You can download the source in VB.Net and C# using the link below

Download Code (7.22 kb)