Gridview that exports selected rows to Excel


Information


I recently had a project where I needed to provide a Gridview of items that the customer could individually select and export to Excel.  The solution worked really well, so here it is for all to enjoy...

This seems like a messy workaround, but I ended up using two Gridviews - one to display my data and show my checkboxes, and the other which was invisible amd would eventually end up as my Excel spreadsheet.  Why two?  Well...
  • The export to Excel uses the Response.Write class which doesn't work under AJAX  (to my knowledge anyway).  Having the first Gridview in an UpdatePanel provides a better user experience when sorting and paging.  The second Gridview is outside the UpdatePanel and invisible.
  • The 2 Gridviews are formatted differently.  As an example, the Gridview that is exported to Excel doesn't need sorting or paging options, nor the checkboxes that are used to select rows.  Also, the colours used in your website may not be ideal for an Excel spreadsheet.
Demo | Code

Step 1: Create a Gridview with selectable rows
The first Gridview is the one that you will be displaying on your webpage (I use the same table and CSS structure as in my article on Creating a great looking Gridview with filtering):
I called this Gridview gvData.

 Gridview that exports selected rows to Excel

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AllowPaging="True"
AllowSorting="true" DataSourceID="dsGridview" EmptyDataText="No records to display."
PageSize="10" Width="540px" CssClass="Gridview" DataKeyNames="ID">
<RowStyle CssClass="data-row" />
<AlternatingRowStyle CssClass="alt-data-row" />
<HeaderStyle CssClass="header-row" />
<Columns>
    <asp:BoundField DataField="id" HeaderText="ID" SortExpression="id" ItemStyle-Width="40px"
        ItemStyle-HorizontalAlign="Center" />
    <asp:BoundField DataField="FirstName" HeaderText="First Name" SortExpression="FirstName"
        ItemStyle-Width="110px" />
    <asp:BoundField DataField="LastName" HeaderText="Last Name" SortExpression="LastName"
        ItemStyle-Width="110px" />
    <asp:BoundField DataField="Dept" HeaderText="Department" SortExpression="Department"
        ItemStyle-Width="120px" />
    <asp:BoundField DataField="Location" HeaderText="Location" SortExpression="Location"
        ItemStyle-Width="120px" />
    <asp:TemplateField HeaderText="">
        <ItemStyle Width="40px" HorizontalAlign="Center" />
        <ItemTemplate>
            <asp:CheckBox ID="chkSelect" runat="server" />
        </ItemTemplate>
    </asp:TemplateField>
</Columns>
</asp:GridView>
The main point of interest is the checkbox that is included as a field:
<asp:TemplateField HeaderText="">
    <ItemStyle Width="40px" HorizontalAlign="Center" />
    <ItemTemplate>
        <asp:CheckBox ID="chkSelect" runat="server" />
    </ItemTemplate>
</asp:TemplateField>

You also need to set the Datakeynames property to the ID of your primary database key.

Step 2: Create an invisible Gridview that will be exported to Excel
This Gridview is the one that will be exported to Excel.  The main things to note are the changes to the Gridview properties:
  • Disable paging
  • Disable sorting
  • Set visible = 'True'
Once again, set datakeynames to the ID of your primary database key. 
You also don't need the checkbox template field.
I called this gvExporttoExcel.

Gridview that exports selected rows to Excel

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AllowPaging="False"
DataSourceID="dsGridview" EmptyDataText="There are no records to display." Width="690px"
DataKeyNames="ID" Visible="false">
<Columns>
    <asp:BoundField DataField="id" HeaderText="Sort" SortExpression="id" ItemStyle-Width="40px"
        ItemStyle-HorizontalAlign="Center" />
    <asp:BoundField DataField="FirstName" HeaderText="Sort" SortExpression="FirstName"
        ItemStyle-Width="120px" />
    <asp:BoundField DataField="LastName" HeaderText="Sort" SortExpression="LastName"
        ItemStyle-Width="120px" />
    <asp:BoundField DataField="Department" HeaderText="Sort" SortExpression="Department"
        ItemStyle-Width="130px" />
    <asp:BoundField DataField="Location" HeaderText="Sort" SortExpression="Location"
        ItemStyle-Width="130px" />
</Columns>
<PagerSettings Mode="NumericFirstLast" PageButtonCount="10" />
</asp:GridView>

Step 3: Set EnableEventValidation="false"
In your page declarations, set EnableEventValidation='false', otherwise you will get the error:RegisterForEventValidation can only be called during Render();
<%@ Page Language="VB" CodeFile="GridviewwithSelectedExport.aspx.vb"
    Inherits="GridviewwithSelectedExport" EnableEventValidation="false" %>
You will also need to create an empty sub that overrides the Gridview's Render process:
Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
     ' Nothing needed here...
End Sub

Step 4: Create a list variable that will store ID's of selected rows
In your code behind file, create a Private Property that will store the ID's of your selected rows:
Private ReadOnly Property IDs() As List(Of Integer)
    ' Create a list of ID's that are selected.  ID's is the primary
    ' Key for this table
    Get
        If Me.ViewState("IDs") Is Nothing Then
            Me.ViewState("IDs") = New List(Of Integer)()
        End If
        Return CType(Me.ViewState("IDs"), List(Of Integer))
    End Get
End Property

Step 5: Create a Sub that will store the selected row ID's into your list variable
Now we create a Sub that I called AddRowstoIDList which goes through all the currently visible rows and adds the ones that have a checkbox selected to the private list variable created in step 4.
Protected Sub AddRowstoIDList()
    ' Loop through all the currently displayed rows in the Gridview
    For Each gvr As GridViewRow In gvData.Rows
        ' Find the checkbox in each row
        Dim chkSelect As CheckBox = CType(gvr.FindControl("chkSelect"), CheckBox)
        ' If the checkbox is ticked then add the corresponding ID to our private list
        If (Not (chkSelect) Is Nothing) Then
            ' Get the ID from the datakeynames property
            Dim ID As Integer = Convert.ToInt32(gvData.DataKeys(gvr.RowIndex)("ID"))
            If (chkSelect.Checked AndAlso Not Me.IDs.Contains(ID)) Then
                ' Add the ID to our list
                Me.IDs.Add(ID)
            ElseIf (Not chkSelect.Checked AndAlso Me.IDs.Contains(ID)) Then
                ' Not checked - remove the ID from our list
                Me.IDs.Remove(ID)
            End If
        End If
    Next
End Sub

This sub is then called whenever the Gridview is paged or sorted - we want to get the current status of the rows and update our list before the Gridview refreshes:
Protected Sub Gridview1_PageIndexChanging(ByVal sender As Object, ByVal e & _
        As GridViewPageEventArgs) Handles gvData.PageIndexChanging
    AddRowstoIDlist()
End Sub

Protected Sub Gridview1_Sorting(ByVal sender As Object, ByVal e As & _
        System.Web.UI.WebControls.GridViewSortEventArgs) Handles gcData.Sorting
    AddRowstoIDlist()
End Sub

Step 6: Make sure the checkboxes are displayed correctly when the Gridview is refreshed
The next step is to set the status of each checkbox correctly when the Gridview is refreshed, by a sort of paging operation for example.  We do this by checking each row, finding the checkbox control, and checking it if the row ID exists in our list.
Protected Sub gvData_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) & _
        Handles gvData.RowDataBound
    ' Get each Gridview Row on DataBound
    Dim gvr As GridViewRow = e.Row
    If (gvr.RowType = DataControlRowType.DataRow) Then
        ' Find the checkbox in the current row
        Dim chkSelect As CheckBox = CType(gvr.FindControl("chkSelect"), CheckBox)
        ' Make sure we're referencing the correct control
        If (Not (chkSelect) Is Nothing) Then
            ' If the ID exists in our list then check the checkbox
            Dim ID As Integer = Convert.ToInt32(gvData.DataKeys(gvr.RowIndex)("ID"))
            chkSelect.Checked = Me.IDs.Contains(ID)
        End If
    End If
End Sub

Step 7: Create a download button that exports the selected rows to Excel
Finally, create a download button anywhere on your page:
<asp:LinkButton ID="btnDownload" runat="server" Text="Download selected rows to Excel" />
The next bit of code manipulates our hidden Gridview (gvExporttoExcel) by making visible any rows that are marked for export, then writing those to an Excel file.
Add the following code behind to export the data to Excel:
Protected Sub btnDownload_Click(ByVal sender As Object, ByVal e As System.EventArgs) & _
        Handles btnDownload.Click
    ' First, check the contents of the current Gridview page to make sure that
    ' all the checked items are in our list.  We need this as the user may have checked or 
    ' unchecked records since the last paging or sorting operation.
    AddRowstoIDList()
    ' Loop through our second Gridview, and make visible any row where the ID of the record
    ' is in our list of checked records
    For Each gvr As GridViewRow In gvExporttoExcel.Rows
        Dim ID As Integer = Convert.ToInt32(gvExporttoExcel.DataKeys(gvr.RowIndex)("ID"))
        If Not Me.IDs.Contains(ID) Then
            gvr.Visible = False
        Else
            gvr.Visible = True
        End If
    Next
    If Me.IDs.Count = 0 Then
        ' Display an error here if you like
    Else
        gvExporttoExcel.Visible = True
        ' Write the gridview to Excel
        Response.Clear()
        Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")
        Response.Charset = ""
        Response.Cache.SetCacheability(HttpCacheability.NoCache)
        Response.ContentType = "application/vnd.xls"
        Dim stringWrite As New System.IO.StringWriter()
        Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite)
        gvExporttoExcel.RenderControl(htmlWrite)
        Response.Write(stringWrite.ToString())
        Response.End()
        gvExporttoExcel.Visible = False
    End If
End Sub

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)