Export Selected GridView Rows To Excel

This example explains how to Export Selected GridView Rows To Ms Excel Using CheckBox In Asp.Net 2.0,3.5,4.0 C# And VB.NET. 
Place a gridview on aspx page, add checkbox control in it using TemplateField and ItemTemplate to select rows and populate gridview from database, add one button for exporting gridview rows to excel.

I have used Northwind Databaseand customers table to populate gridview.
you can follow link to know how to install it on sql server 2008.

in one of my previous posts Export Gridview To Excel, i described how to export gridview containing controls like linkbutton, checkbox,dropdown etc to excel. I'll be using this code further to export selected rows.

Set DataKeyNames property of gridview to CustomerID.

HTML MARKUP

---

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
              DataSourceID="sqlDataSourceGridView" 
              DataKeyNames="CustomerID" 
              AutoGenerateColumns="False"
              onpageindexchanging="GridView1_PageIndexChanging" 
              onrowdatabound="GridView1_RowDataBound" >
<Columns>
<asp:TemplateField>
     <ItemTemplate>
          <asp:CheckBox ID="chkSelect" runat="server" />
     </ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Customer ID">
     <ItemTemplate>
          <asp:LinkButton ID="lButton" runat="server" 
                          Text='<%#Eval("CustomerID") %>' 
                          PostBackUrl="~/Default.aspx" >
          </asp:LinkButton>
     </ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="CompanyName" HeaderText="Company">
</asp:BoundField>
<asp:BoundField DataField="ContactName" HeaderText="Name">
</asp:BoundField>
<asp:BoundField DataField="City" HeaderText="city">
</asp:BoundField>
<asp:BoundField DataField="Country" HeaderText="Country" 
&lt;/asp:BoundField>
</Columns>
</asp:GridView>
       
<asp:SqlDataSource ID="sqlDataSourceGridView" runat="server" 
ConnectionString="<%$ ConnectionStrings:northWindConnectionString %>" 
SelectCommand="SELECT [CustomerID], [CompanyName], [ContactName], 
               [City], [Country] FROM [Customers]">
</asp:SqlDataSource>

<asp:Button ID="btnExportToExcel" runat="server" 
            Text="Export To Excel" 
            onclick="btnExportToExcel_Click"/>

We need to write a method to find checked rows and maintane their state across postbacks or across gridview paging.

This method stores to customerID of selected row in viewstate using arraylist.

C#

---

01private void FindCheckedRows()

02    {

03        ArrayList checkedRowsList;

04        if (ViewState["checkedRowsList"] != null)

05        {

06            checkedRowsList = (ArrayList)ViewState["checkedRowsList"];

07        }

08        else

09        {

10            checkedRowsList = new ArrayList();

11        }

12 

13        foreach (GridViewRow gvRow in GridView1.Rows)

14        {

15            if (gvRow.RowType == DataControlRowType.DataRow)

16            {

17                string rowIndex =

18 

19Convert.ToString(GridView1.DataKeys[gvRow.RowIndex]["CustomerID"]);

20                //int rowIndex = Convert.ToInt32(gvRow.RowIndex) +

21 

22Convert.ToInt32(GridView1.PageIndex);

23                CheckBox chkSelect =

24 

25(CheckBox)gvRow.FindControl("chkSelect");

26                if ((chkSelect.Checked) &&

27 

28(!checkedRowsList.Contains(rowIndex)))

29                {

30                    checkedRowsList.Add(rowIndex);

31                }

32                else if ((!chkSelect.Checked) &&

33 

34(checkedRowsList.Contains(rowIndex)))

35                {

36                    checkedRowsList.Remove(rowIndex);

37                }

38            }

39 

40        }

41        ViewState["checkedRowsList"] = checkedRowsList;

42    }

VB.NET

---

01Private Sub FindCheckedRows()

02 Dim checkedRowsList As ArrayList

03 If ViewState("checkedRowsList") IsNot Nothing Then

04  checkedRowsList =

05 

06DirectCast(ViewState("checkedRowsList"), ArrayList)

07 Else

08  checkedRowsList = New ArrayList()

09 End If

10 

11 For Each gvRow As GridViewRow In GridView1.Rows

12  If gvRow.RowType = DataControlRowType.DataRow Then

13   Dim rowIndex As String =

14 

15Convert.ToString(GridView1.DataKeys(gvRow.RowIndex)("CustomerID"))

16   'int rowIndex = Convert.ToInt32(gvRow.RowIndex)

17 

18+ Convert.ToInt32(GridView1.PageIndex);

19   Dim chkSelect As CheckBox =

20 

21DirectCast(gvRow.FindControl("chkSelect"), CheckBox)

22   If (chkSelect.Checked) AndAlso (Not

23 

24checkedRowsList.Contains(rowIndex)) Then

25    checkedRowsList.Add(rowIndex)

26   ElseIf (Not chkSelect.Checked) AndAlso

27 

28(checkedRowsList.Contains(rowIndex)) Then

29    checkedRowsList.Remove(rowIndex)

30   End If

31 

32  End If

33 Next

34 ViewState("checkedRowsList") = checkedRowsList

35End Sub

Call this method whenever gridview pageindex changes.

1protected void GridView1_PageIndexChanging(object sender,

2 

3GridViewPageEventArgs e)

4    {

5        FindCheckedRows();

6    }

Find the checkbox state and implement it whenever gridview is refreshed while paging.

To implement this write code in RowDataBound event of gridview.

C#

---

01protected void GridView1_RowDataBound(object sender,

02 

03GridViewRowEventArgs e)

04    {

05        if (ViewState["checkedRowsList"] != null)

06        {

07            ArrayList checkedRowsList =

08 

09(ArrayList)ViewState["checkedRowsList"];

10            GridViewRow gvRow = e.Row;

11            if (gvRow.RowType == DataControlRowType.DataRow)

12            {

13                CheckBox chkSelect =

14 

15(CheckBox)gvRow.FindControl("chkSelect");

16                string rowIndex =

17 

18Convert.ToString(GridView1.DataKeys[gvRow.RowIndex]["CustomerID"]);

19                //int rowIndex = Convert.ToInt32(gvRow.RowIndex) +

20 

21Convert.ToInt32(GridView1.PageIndex);

22                if(checkedRowsList.Contains(rowIndex))

23                {

24                    chkSelect.Checked = true;

25                }

26 

27 

28            }

29        }

30 

31 

32    }

VB.NET

---

01Protected Sub GridView1_RowDataBound(sender As Object, e As

02 

03GridViewRowEventArgs)

04 If ViewState("checkedRowsList") IsNot Nothing Then

05  Dim checkedRowsList As ArrayList =

06 

07DirectCast(ViewState("checkedRowsList"), ArrayList)

08  Dim gvRow As GridViewRow = e.Row

09  If gvRow.RowType = DataControlRowType.DataRow Then

10   Dim chkSelect As CheckBox =

11 

12DirectCast(gvRow.FindControl("chkSelect"), CheckBox)

13   Dim rowIndex As String =

14 

15Convert.ToString(GridView1.DataKeys(gvRow.RowIndex)("CustomerID"))

16   'int rowIndex = Convert.ToInt32(gvRow.RowIndex)

17 

18+ Convert.ToInt32(GridView1.PageIndex);

19   If checkedRowsList.Contains(rowIndex) Then

20    chkSelect.Checked = True

21 

22 

23   End If

24  End If

25 End If

26 

27 

28End Sub

To export these selected rows to excel write following code in Click event of export button.

C#

---

01protected void btnExportToExcel_Click(object sender, EventArgs e)

02    {

03        FindCheckedRows();

04        GridView1.ShowHeader = true;

05        GridView1.GridLines = GridLines.Both;

06        GridView1.AllowPaging = false;

07        GridView1.DataBind();

08        GridView1.HeaderRow.Cells.RemoveAt(0);

09        if (ViewState["checkedRowsList"] != null)

10        {

11            ArrayList checkedRowsList =

12 

13(ArrayList)ViewState["checkedRowsList"];

14            foreach (GridViewRow gvRow in GridView1.Rows)

15            {

16                gvRow.Visible = false;

17                if (gvRow.RowType == DataControlRowType.DataRow)

18                {

19                    string rowIndex =

20 

21Convert.ToString(GridView1.DataKeys[gvRow.RowIndex]["CustomerID"]);

22                    if(checkedRowsList.Contains(rowIndex))

23                    {

24                        gvRow.Visible = true;

25                        gvRow.Cells[0].Visible = false;

26 

27                    }

28                }

29            }

30        }

31 

32        ChangeControlsToValue(GridView1);

33        Response.ClearContent();

34 

35        Response.AddHeader("content-disposition", "attachment;

36 

37filename=GridViewToExcel.xls");

38 

39        Response.ContentType = "application/excel";

40 

41        StringWriter sWriter = new StringWriter();

42 

43        HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);

44 

45        HtmlForm hForm = new HtmlForm();

46 

47        GridView1.Parent.Controls.Add(hForm);

48 

49        hForm.Attributes["runat"] = "server";

50 

51        hForm.Controls.Add(GridView1);

52 

53        hForm.RenderControl(hTextWriter);

54 

55        Response.Write(sWriter.ToString());

56 

57        Response.End();

58    }

VB.NET

---

01Protected Sub btnExportToExcel_Click(sender As Object, e As EventArgs)

02 FindCheckedRows()

03 GridView1.ShowHeader = True

04 GridView1.GridLines = GridLines.Both

05 GridView1.AllowPaging = False

06 GridView1.DataBind()

07 GridView1.HeaderRow.Cells.RemoveAt(0)

08 If ViewState("checkedRowsList") IsNot Nothing Then

09  Dim checkedRowsList As ArrayList =

10 

11DirectCast(ViewState("checkedRowsList"), ArrayList)

12  For Each gvRow As GridViewRow In GridView1.Rows

13   gvRow.Visible = False

14   If gvRow.RowType = DataControlRowType.DataRow

15 

16Then

17    Dim rowIndex As String =

18 

19Convert.ToString(GridView1.DataKeys(gvRow.RowIndex)("CustomerID"))

20    If checkedRowsList.Contains(rowIndex)

21 

22Then

23     gvRow.Visible = True

24 

25     gvRow.Cells(0).Visible = False

26    End If

27   End If

28  Next

29 End If

30 

31 ChangeControlsToValue(GridView1)

32 Response.ClearContent()

33 

34 Response.AddHeader("content-disposition", "attachment;

35 

36filename=GridViewToExcel.xls")

37 

38 Response.ContentType = "application/excel"

39 

40 Dim sWriter As New StringWriter()

41 

42 Dim hTextWriter As New HtmlTextWriter(sWriter)

43 

44 Dim hForm As New HtmlForm()

45 

46 GridView1.Parent.Controls.Add(hForm)

47 

48 hForm.Attributes("runat") = "server"

49 

50 hForm.Controls.Add(GridView1)

51 

52 hForm.RenderControl(hTextWriter)

53 

54 Response.Write(sWriter.ToString())

55 

56 Response.[End]()

57End Sub

This is how exported rows will look like in excel.