Export Selected GridView Rows To Excel

2:33:00 am 0 Comments

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.

Export Selected GridView Rows to excel
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.

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.



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.


Download Sample Code

Unknown

0 comments:

Subscribe to: Post Comments (Atom)