Export Selected GridView Rows To Excel

Export Selected GridView Rows To Excel in asp.net 2.0,3.5,4.0 using C# and VB.NET.

In this post i am going to explain how to export selected gridview rows to ms excel in asp.net.

I have used northwind database and customers table to populate gridview.



read how to install northwind database for northwind database installation in sql server 2008.




First of all 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.

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

Now 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# CODE

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 CODE

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    }

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

for this to implement we need to write code in RowDataBound event of gridview.

C# CODE

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 CODE

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

Now to export these selected rows to excel write below mentioned code in Click event of export button.

C# CODE

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 CODE

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.