ExportToExcel with Grid Column Groups and making columns Visible

[Rick]

NUTSHELL
I am exporting a grid that has Grid Column Groups to Excel and making changing the display of several fields from false to true.  The Grid Column Group is not expanding in Excel to handle the columns which are now displayed.

DETAIL
My grid has 16 viewable columns and 6 grid column groups.  When I go to Export to Excel, I change 10 additional columns so the display is true.  These columns are interspersed throughout the grid and not all at the end.  In Excel, the Grid Column Groups do not expand, so if they were 3columns before but now there are 6 columns under that group, it is still only 3 columns wide.  This of course throws everything off.

I am programming in MS Visual Studio 2012.
I am using Telerik UI for ASP.NET AJAX, v.2014.1.225.45

CODE

Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As EventArgs)

    Me.RadGrid1.MasterTableView.GetColumn("Location").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_NotIssued").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_NotIssuedReason").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_Qualification").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_EstimatedProjectArea").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_EstimatedConstructionCost").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_WinningProbabilityPct").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_DateGranted").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_ProjectID").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_Comments").Display = True

    For Each item As GridDataItem In Me.RadGrid1.Items
        If item.Selected = False Then
            item.Visible = False
        End If
    Next
    Me.RadGrid1.ExportSettings.ExportOnlyData = True
    Me.RadGrid1.ExportSettings.OpenInNewWindow = True
    Me.RadGrid1.ExportSettings.FileName = "Selected Proposals " & Format(Now, "yyyy-MM-dd-h-mm-ss")
    'Me.RadGrid1.ExportSettings.IgnorePaging = True
    Me.RadGrid1.ExportSettings.UseItemStyles = True
    Me.RadGrid1.MasterTableView.ExportToExcel()


End Sub

HTML
I won't bother to post the HTML until asked, but here is the code for the GridColumnGroup and one of the non-displayed columns.

<ColumnGroups>
    <telerik:GridColumnGroup HeaderText="Proposal" Name="Proposal" HeaderStyle-HorizontalAlign="left"  >
        <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
    </telerik:GridColumnGroup>
    <telerik:GridColumnGroup HeaderText="Fee" Name="Fee" HeaderStyle-HorizontalAlign="left" >
        <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
    </telerik:GridColumnGroup>
    <telerik:GridColumnGroup  HeaderText="Key Dates" Name="Dates" HeaderStyle-HorizontalAlign="left" >
        <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
    </telerik:GridColumnGroup>
    <telerik:GridColumnGroup HeaderText="Client" Name="Client" HeaderStyle-HorizontalAlign="left">
        <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
    </telerik:GridColumnGroup>
    <telerik:GridColumnGroup HeaderText="Owner" Name="Owner" HeaderStyle-HorizontalAlign="left" >
        <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
    </telerik:GridColumnGroup>
    <telerik:GridColumnGroup HeaderText="DLB" Name="DLB" HeaderStyle-HorizontalAlign="left">
        <HeaderStyle HorizontalAlign="Left"></HeaderStyle>
    </telerik:GridColumnGroup>
</ColumnGroups>

<telerik:GridBoundColumn Display="False" DataField="Proposal_Qualification" ColumnGroupName="Proposal"  HeaderStyle-Height="44px" HeaderStyle-HorizontalAlign="Left" HeaderStyle-VerticalAlign="Middle"
     FilterControlAltText="Filter Proposal_Qualification column" HeaderText="Qualification" SortExpression="Proposal_Qualification" UniqueName="Proposal_Qualification">
    <ColumnValidationSettings>
        <ModelErrorMessage Text="" />
    </ColumnValidationSettings>
    <HeaderStyle HorizontalAlign="Left" VerticalAlign="Middle" Height="44px"></HeaderStyle>

    <ItemStyle Width="105px"></ItemStyle>
</telerik:GridBoundColumn>

Thank you!

[Kostadin]

Hello Rick,

You have to either enable the IgnorePaging or Rebind the grid before exporting to Excel. I noticed that you have commented the IgnorePaging property, so you could rebind the grid in order the hidden columns to be placed under the correct group.

Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As EventArgs)

    Me.RadGrid1.MasterTableView.GetColumn("Location").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_NotIssued").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_NotIssuedReason").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_Qualification").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_EstimatedProjectArea").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_EstimatedConstructionCost").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_WinningProbabilityPct").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_DateGranted").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_ProjectID").Display = True
    Me.RadGrid1.MasterTableView.GetColumn("Proposal_Comments").Display = True

    For Each item As GridDataItem In Me.RadGrid1.Items
        If item.Selected = False Then
            item.Visible = False
        End If
    Next
    Me.RadGrid1.ExportSettings.ExportOnlyData = True
    Me.RadGrid1.ExportSettings.OpenInNewWindow = True
    Me.RadGrid1.ExportSettings.FileName = "Selected Proposals " & Format(Now, "yyyy-MM-dd-h-mm-ss")
    'Me.RadGrid1.ExportSettings.IgnorePaging = True
    Me.RadGrid1.Rebind()
    Me.RadGrid1.ExportSettings.UseItemStyles = True
    Me.RadGrid1.MasterTableView.ExportToExcel()


End Sub

Regards,
Kostadin
Telerik

 

 

[Rick]

Thank you, that worked perfectly.

[Rick]

Oops...  While the REBIND did fix the column heads, it causes the "selected row" code to be ignored.

For Each item As GridDataItem In Me.RadGrid1.Items
    If item.Selected = False Then
        item.Visible = False
    End If
Next

Instead of only showing the few rows I have selected, the rebind causes all of my rows to be printed.  

I tried moving the rebind before the selected row code, but then no rows are printed.

[Kostadin]

Hi Rick,

Actually selected items are not persisted through a Rebind and in order to export only them you have to persist the selected items manually. I would recommend you to examine the following help article which might help you to achieve your goal.
Persisting the Selected Rows Server-side

Regards,
Kostadin
Telerik

 

 

[Rick]

I didn't want to use that method because it eliminates the mouse drag option for selecting rows.  However, I wasn't able to find another way to do it, so I followed the method shown in the article you linked.  It fails on the Dim customerID line:

Protected Sub RadGrid1_ItemCommand(ByVal source As Object, ByVal e As GridCommandEventArgs) Handles RadGrid1.ItemCommand
    Dim selectedItems As ArrayList
    If Session("selectedItems") Is Nothing Then
        selectedItems = New ArrayList
    Else
        selectedItems = CType(Session("selectedItems"), ArrayList)
    End If
    If e.CommandName = RadGrid.SelectCommandName AndAlso TypeOf e.Item Is GridDataItem Then
        Dim dataItem As GridDataItem = CType(e.Item, GridDataItem)
        Dim customerID As String = dataItem.OwnerTableView.DataKeyValues(dataItem.ItemIndex)("Proposal_Number").ToString
        selectedItems.Add(customerID)
        Session("selectedItems") = selectedItems
    End If
    If e.CommandName = RadGrid.DeselectCommandName AndAlso TypeOf e.Item Is GridDataItem Then
        Dim dataItem As GridDataItem = CType(e.Item, GridDataItem)
        Dim customerID As String = dataItem.OwnerTableView.DataKeyValues(dataItem.ItemIndex)("Proposal_Number").ToString
        selectedItems.Remove(customerID)
        Session("selectedItems") = selectedItems
    End If
End Sub

The error message is:

ArgumentOutOfRangeException was unhandled by user code.
Index was out of range. Must be non-negative and less than the size of the collection.

I don't know what this means.  There is a value in that field...

COMPLETE APPLICABLE CODE

Protected Sub RadGrid1_ItemCommand(ByVal source As Object, ByVal e As GridCommandEventArgs) Handles RadGrid1.ItemCommand
    Dim selectedItems As ArrayList
    If Session("selectedItems") Is Nothing Then
        selectedItems = New ArrayList
    Else
        selectedItems = CType(Session("selectedItems"), ArrayList)
    End If
    If e.CommandName = RadGrid.SelectCommandName AndAlso TypeOf e.Item Is GridDataItem Then
        Dim dataItem As GridDataItem = CType(e.Item, GridDataItem)
        Dim customerID As String = dataItem.OwnerTableView.DataKeyValues(dataItem.ItemIndex)("Proposal_Number").ToString
        selectedItems.Add(customerID)
        Session("selectedItems") = selectedItems
    End If
    If e.CommandName = RadGrid.DeselectCommandName AndAlso TypeOf e.Item Is GridDataItem Then
        Dim dataItem As GridDataItem = CType(e.Item, GridDataItem)
        Dim customerID As String = dataItem.OwnerTableView.DataKeyValues(dataItem.ItemIndex)("Proposal_Number").ToString
        selectedItems.Remove(customerID)
        Session("selectedItems") = selectedItems
    End If
End Sub
Protected Sub RadGrid1_PreRender(ByVal sender As Object, ByVal e As EventArgs) Handles RadGrid1.PreRender
    If Not (Session("selectedItems") Is Nothing) Then
        Dim selectedItems As ArrayList = CType(Session("selectedItems"), ArrayList)
        Dim stackIndex As Int16
        For stackIndex = 0 To selectedItems.Count - 1
            Dim curItem As String = selectedItems(stackIndex).ToString
            For Each item As GridItem In RadGrid1.MasterTableView.Items
                If TypeOf item Is GridDataItem Then
                    Dim dataItem As GridDataItem = CType(item, GridDataItem)
                    If curItem.Equals(dataItem.OwnerTableView.DataKeyValues(dataItem.ItemIndex)("Proposal_Number").ToString()) Then
                        dataItem.Selected = True
                    End If
                End If
            Next
        Next
    End If
End Sub

[Rick]

Sorry, that error occurs when I press the Select command (in case that wasn't obvious)

[Kostadin]

Hi Rick,

Could you please verify that you have specify Proposal_Number field as a DataKeyName of the MasterTableView? Also could you please let me know where exactly this error is through. Generally this exception is received when you try to access an item form a collection by index which is negative or larger then the size of the collection.

Regards,
Kostadin
Telerik