Export to Excel Using ExcelML Adds Empty Cell

[Dan]

Hi, I have a master table and a detail table.  When I export to Excel I get an extra cell inserted in the first column for the detail table view records only.  If you look at the attached image you will see cells A3 and A6 are empty and appear to be associated with the detail table.

Any insights as to what I might be overlooking and how to fix this would be greatly appreciated.

Radgrid defintion...    

<telerik:RadGrid ID="rgCourseInventoryList" runat="server" CssClass="RadGrid" GridLines="None" AllowPaging="True" PageSize="10"

    AllowSorting="True" AutoGenerateColumns="False" ShowStatusBar="true" OnDetailTableDataBind="rgCourseInventoryList_DetailTableDataBind"

    OnNeedDataSource="rgCourseInventoryList_NeedDataSource">

    <MasterTableView CommandItemDisplay="TopAndBottom" DataKeyNames="CourseId" AutoGenerateColumns="false" HierarchyLoadMode="ServerBind"

    HierarchyDefaultExpanded="true" ClientDataKeyNames="CourseId">

    <CommandItemTemplate>

      <asp:ImageButton runat="server" ImageUrl="../Images/Icons/addrecord.gif"  OnClientClick="return ShowInsertForm();"  ID="ibInsertSection" ToolTip="<%$ Resources:ui_labels, ibInsertSection %>"/>

      <asp:LinkButton runat="server" ID="lbAddCourse" OnClientClick="return ShowInsertForm();" Text="<%$ Resources:ui_messages, msgAddNewRecord %>"></asp:LinkButton>

      <asp:ImageButton runat="server" ImageUrl="../Images/Icons/excel.png" ID="ibExportToExcel" ToolTip="<%$ Resources:ui_labels, ibInsertSection %>"/>

      <asp:LinkButton runat="server" ID="lbExportToExcel" Text="<%$ Resources:ui_messages, msgExcel %>" CommandName="ExportToExcel"></asp:LinkButton>

    </CommandItemTemplate>

 

    <Columns>

        <telerik:GridEditCommandColumn UniqueName="gecEditCourse" EditImageUrl="../Images/Icons/edit.png"  ButtonType="ImageButton" ItemStyle-Width="10px" EditText="<%$ Resources:ui_labels, ibEditCourse %>">

        </telerik:GridEditCommandColumn>

        <telerik:GridBoundColumn UniqueName="CourseCode" HeaderText="<%$Resources:ui_labels, gbcCourseCode %>" DataField="SISCourseCode">

        </telerik:GridBoundColumn>

        <telerik:GridBoundColumn UniqueName="CourseType" HeaderText="<%$Resources:ui_labels, gbcCourseType %>" DataField="SISCourseType">

        </telerik:GridBoundColumn>

        <telerik:GridBoundColumn UniqueName="CourseTitle" DataField="SISCourseTitle" Visible="false">

        </telerik:GridBoundColumn>

        <telerik:GridTemplateColumn UniqueName="gtcTitle" HeaderText="<%$ Resources:ui_labels, gtcTitle %>" ItemStyle-VerticalAlign="Middle">

            <ItemTemplate>

                <asp:Image ID="imgParent" runat="server" Visible="false" />

                <asp:Label ID="lblParentTitle" runat="server"></asp:Label>

                <asp:Label ID="lblCourseTitle" runat="server"></asp:Label>

            </ItemTemplate>

        </telerik:GridTemplateColumn>

        <telerik:GridBoundColumn UniqueName="CourseNote" HeaderText="<%$Resources:ui_labels, gbcNote %>" DataField="SISCourseNotes">

        </telerik:GridBoundColumn>

        <telerik:GridBoundColumn UniqueName="CourseExpired" DataField="SISCourseExpired" Visible="false">

        </telerik:GridBoundColumn>

        <telerik:GridBoundColumn UniqueName="ParentId" DataField="ParentId" Visible="false">

        </telerik:GridBoundColumn>

        <telerik:GridBoundColumn UniqueName="CourseStructure" HeaderText="<%$Resources:ui_labels, gbcCourseStructure %>" DataField="SISCourseStructure">

        </telerik:GridBoundColumn>

        <telerik:GridTemplateColumn UniqueName="gtcShowDelete" ItemStyle-Width="10px">

            <ItemTemplate>

            <asp:ImageButton runat="server" ImageUrl="../config/Images/Icons/delete.png" ID="ibShowDeleteForm" ToolTip="<%$ Resources:ui_labels, ibShowDeleteForm %>"/>

            </ItemTemplate>

        </telerik:GridTemplateColumn>

    </Columns>

    <DetailTables>

        <telerik:GridTableView runat="server" DataKeyNames="ChildCourseId" Name="CrossListView" ClientDataKeyNames="ChildCourseId">

            <Columns>

                <telerik:GridBoundColumn UniqueName="SISCourseCode" DataField="SISCourseCode">

                </telerik:GridBoundColumn>

                <telerik:GridBoundColumn UniqueName="SISCourseTitle" DataField="SISCourseTitle">

                </telerik:GridBoundColumn>

                <telerik:GridBoundColumn UniqueName="SISCourseExpired" DataField="SISCourseExpired" Visible="false">

                </telerik:GridBoundColumn>

            </Columns>

        </telerik:GridTableView>

    </DetailTables>

    </MasterTableView>

    <ExportSettings ExportOnlyData="true" Excel-Format="ExcelML" IgnorePaging="true" OpenInNewWindow="true" HideStructureColumns="true">

    </ExportSettings>

    <ClientSettings>

        <Selecting AllowRowSelect="true"></Selecting>

        <ClientEvents OnRowDblClick="RowDblClickEdit"></ClientEvents>

    </ClientSettings>

</telerik:RadGrid>



Code behind...

Protected Sub rgCourseInventory_ItemCommand(ByVal source As Object, ByVal e As Telerik.Web.UI.GridCommandEventArgs) Handles rgCourseInventoryList.ItemCommand

    rgCourseInventoryList.MasterTableView.CommandItemSettings.ShowRefreshButton = False

    If (e.CommandName = RadGrid.ExportToExcelCommandName) Then
        _isExporting = True
        rgCourseInventoryList.ExportSettings.FileName = "Course list " + Date.Now.ToString("F")
        rgCourseInventoryList.ExportSettings.HideStructureColumns = True
        rgCourseInventoryList.MasterTableView.GetColumn("CourseTitle").Visible = True
        rgCourseInventoryList.MasterTableView.GetColumn("CourseTitle").HeaderText = Resources.ui_labels.gtcTitle.ToString
        rgCourseInventoryList.MasterTableView.ExportToExcel()

    ElseIf (e.CommandName = RadGrid.CancelCommandName) Then
        rgCourseInventoryList.MasterTableView.CommandItemSettings.ShowAddNewRecordButton = True
        rgCourseInventoryList.MasterTableView.CommandItemSettings.AddNewRecordText = Resources.ui_messages.msgAddNewRecord

    ElseIf (e.CommandName = RadGrid.InitInsertCommandName) Then
        rgCourseInventoryList.MasterTableView.CommandItemSettings.ShowAddNewRecordButton = False
        rgCourseInventoryList.MasterTableView.CommandItemSettings.AddNewRecordText = ""

    End If
End Sub

Protected Sub rgCourseInventoryList_ExcelMLExportRowCreated(ByVal sender As Object, ByVal e As Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowCreatedArgs) Handles rgCourseInventoryList.ExcelMLExportRowCreated

    If e.RowType = GridExportExcelMLRowType.DataRow Then
        If Not _isExcelConfigured Then
            'Set Worksheet name
            e.Worksheet.Name = rcbAcademicUnitId.SelectedItem.Text + " " + Date.Now.ToString("MMM dd, yyyy")

            'Set Column widths
            For Each column As ColumnElement In e.Worksheet.Table.Columns
                If e.Worksheet.Table.Columns.IndexOf(column) = 2 Or e.Worksheet.Table.Columns.IndexOf(column) = 3 Then
                    'set width 280 for Course Title and Notes column
                    column.Width = Unit.Point(280)
                Else
                    column.Width = Unit.Point(80)
                    'set width 80 to the rest of the columns
                End If
            Next

            'Set Page options
            Dim pageSetup As PageSetupElement = e.Worksheet.WorksheetOptions.PageSetup
            pageSetup.PageLayoutElement.IsCenteredVertical = True
            pageSetup.PageLayoutElement.IsCenteredHorizontal = True
            pageSetup.PageMarginsElement.Left = 0.5
            pageSetup.PageMarginsElement.Top = 0.5
            pageSetup.PageMarginsElement.Right = 0.5
            pageSetup.PageMarginsElement.Bottom = 0.5
            pageSetup.PageLayoutElement.PageOrientation = PageOrientationType.Landscape

            'Freeze panes
            e.Worksheet.WorksheetOptions.AllowFreezePanes = True
            e.Worksheet.WorksheetOptions.LeftColumnRightPaneNumber = 1
            e.Worksheet.WorksheetOptions.TopRowBottomPaneNumber = 1
            e.Worksheet.WorksheetOptions.SplitHorizontalOffset = 1
            e.Worksheet.WorksheetOptions.SplitVerticalOffest = 1

            e.Worksheet.WorksheetOptions.ActivePane = 2
            _isExcelConfigured = True
        End If

    ElseIf e.RowType = GridExcelBuilder.GridExportExcelMLRowType.HeaderRow Then
            If e.Row.Cells.Count < 4 Then
                e.Worksheet.Table.Rows.Remove(e.Row) ' These are the detail column header record rows which insert empty cells into the spreadsheet so get rid of the whole row
            End If
    End If

End Sub

Protected Sub rgCourseInventoryList_ExcelMLExportStylesCreated(ByVal source As Object, ByVal e As Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLStyleCreatedArgs) Handles rgCourseInventoryList.ExcelMLExportStylesCreated

    For Each style As StyleElement In e.Styles
        If style.Id = "headerStyle" Then
            style.InteriorStyle.Pattern = InteriorPatternType.Solid
            style.InteriorStyle.Color = System.Drawing.Color.Gray
        End If
        If style.Id = "alternatingItemStyle" Then
            style.InteriorStyle.Pattern = InteriorPatternType.Solid
            style.InteriorStyle.Color = System.Drawing.Color.LightGray
        End If
        If style.Id.Contains("itemStyle") Then
            style.InteriorStyle.Pattern = InteriorPatternType.Solid
            style.InteriorStyle.Color = System.Drawing.Color.White
        End If
    Next
End Sub

[Kostadin]

Hello Dan,

I was not able to reproduce the issue. I prepared a small sample based on your code and attached it to this forum post. Could you please give it a try and let me know how it differs from your real setup?

Regards,
Kostadin
the Telerik team

[Dan]

Hi Kostadin, thank you for responding to my issue.  Subsequent to submitting this issue and upon further research into using the excelML format I realized that excelML does  not support multi-column headers.  Since this does not meet my requirements I will be using the HTML format instead.

Thank you for your efforts all the same.
Dan