This is a migrated thread and some comments may be shown as answers.

Export to Excel Using ExcelML Adds Empty Cell

2 Answers 200 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Dan
Top achievements
Rank 1
Dan asked on 06 Feb 2013, 05:31 PM
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




2 Answers, 1 is accepted

Sort by
0
Kostadin
Telerik team
answered on 11 Feb 2013, 02:46 PM
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
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
0
Dan
Top achievements
Rank 1
answered on 11 Feb 2013, 05:58 PM
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
Tags
Grid
Asked by
Dan
Top achievements
Rank 1
Answers by
Kostadin
Telerik team
Dan
Top achievements
Rank 1
Share this question
or