Exporting Hierachical Grids

[Peter Parsons]

Hi,

I have a  grid with a master - detail hierachical structure using AutoGenerateHierarchy="true".

The grids display correctly, however, when trying to export to Excel I get the following error...

Column 'OriginalDataItem' does not belong to table Leads.

I have searched the forums but cannot find any reference to this error. 'OriginalDataItem' is not a field in either the master or detail DataTables and I cannot find it as a property of the grid.

-a-

[Daniel]

Hello Anthony,

Can you please post your markup in this forum thread?

Regards,
Daniel
the Telerik team

[Peter Parsons]

<h1>
    Lead Listing</h1>
<telerik:RadComboBox ID="rcbQuestionSets" runat="server" DataValueField="fieldsetId"
    DataTextField="fieldsetName" AutoPostBack="true" OnSelectedIndexChanged="rcbQuestionSets_SelectedIndexChanged">
</telerik:RadComboBox>
<div class="columnBreaker"> </div>
<telerik:RadGrid ID="rgLeadListing" runat="server" Visible="true" AllowPaging="true"
    AllowFilteringByColumn="true" PageSize="10" OnNeedDataSource="rgUserListing_NeedDataSource"
    OnDetailTableDataBind="rgUserListing_DetailTableDataBind" AutoGenerateHierarchy="true"
    OnItemCommand="rgLeadListing_ItemCommand" OnExcelMLExportRowCreated="rgLeadListing_ExcelMLExportRowCreated">
    <ClientSettings EnableRowHoverStyle="True" EnablePostBackOnRowClick="false" Selecting-AllowRowSelect="true">
    </ClientSettings>
    <ExportSettings Excel-Format="ExcelML" ExportOnlyData="true" IgnorePaging="true"
        FileName="LeadList" Excel-FileExtension="xls" HideStructureColumns="true" />
    <GroupingSettings CaseSensitive="false" />
    <MasterTableView AutoGenerateColumns="false" BorderWidth="0px" ClientDataKeyNames="UserId"
        DataKeyNames="UserId" GridLines="None" ShowHeader="True" CommandItemDisplay="Top"
        AllowAutomaticUpdates="False" AllowAutomaticInserts="False" AllowAutomaticDeletes="False">
        <CommandItemSettings ShowAddNewRecordButton="false" ShowExportToExcelButton="true" />
        <DetailTables>
            <telerik:GridTableView AllowFilteringByColumn="false" AutoGenerateColumns="false">
                <Columns>
                    <telerik:GridBoundColumn DataField="uaId" HeaderText="uaId" ReadOnly="True" SortExpression="uaId"
                        UniqueName="uaId" Visible="false">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="userId" HeaderText="userId" ReadOnly="True" SortExpression="userId"
                        UniqueName="userId" Visible="false">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="qText" HeaderText="Question" SortExpression="qText"
                        UniqueName="qText">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="aText" HeaderText="Answer" SortExpression="aText"
                        UniqueName="aText">
                    </telerik:GridBoundColumn>
                </Columns>
            </telerik:GridTableView>
        </DetailTables>
        <Columns>
            <telerik:GridBoundColumn DataField="userId" HeaderText="userId" ReadOnly="True" SortExpression="userId"
                UniqueName="userId" Visible="false">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="userName" HeaderText="Name" SortExpression="userName"
                UniqueName="userName">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="DOB" HeaderText="DOB" SortExpression="DOB" UniqueName="DOB">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="HouseNumber" HeaderText="House Number" SortExpression="HouseNumber"
                UniqueName="HouseNumber" Visible="false">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Postcode" HeaderText="Postcode" SortExpression="Postcode"
                UniqueName="Postcode">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="PhoneNumber" HeaderText="Phone Number" SortExpression="PhoneNumber"
                UniqueName="PhoneNumber" Visible="false">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="MobileNumber" HeaderText="Mobile Number" SortExpression="MobileNumber"
                UniqueName="MobileNumber" Visible="false">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Children" HeaderText="Children" SortExpression="Children"
                UniqueName="Children" Visible="false">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="MaritalStatus" HeaderText="Marital Status" SortExpression="MaritalStatus"
                UniqueName="MaritalStatus" Visible="false">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="HouseholdIncome" HeaderText="Household Income"
                SortExpression="HouseholdIncome" UniqueName="HouseholdIncome" Visible="false">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="ResidentialStatus" HeaderText="Residential Status"
                SortExpression="ResidentialStatus" UniqueName="ResidentialStatus" Visible="false">
            </telerik:GridBoundColumn>
            <telerik:GridCheckBoxColumn DataField="CreditReport" DataType="System.Boolean" HeaderText="Credit Report"
                SortExpression="CreditReport" UniqueName="CreditReport">
            </telerik:GridCheckBoxColumn>
            <telerik:GridBoundColumn DataField="PromoCode" HeaderText="PromoCode" SortExpression="PromoCode"
                UniqueName="PromoCode">
            </telerik:GridBoundColumn>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

protected void rgUserListing_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
{
    loadGrid();
}

protected void rgUserListing_DetailTableDataBind(object source, GridDetailTableDataBindEventArgs e)
{
    e.DetailTableView.Width = Unit.Percentage(100);
}

protected void rgLeadListing_ItemCommand(object source, GridCommandEventArgs e)
{
    if (e.CommandName == "ExportToExcel")
    {
        rgLeadListing.ExportSettings.FileName = "LeadList" + DateTime.Now.ToString("-dd-MM-yyyy");
        foreach (GridColumn col in rgLeadListing.MasterTableView.Columns)
            col.Visible = true;
    }
}

protected void rgLeadListing_ExcelMLExportRowCreated(object source, Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowCreatedArgs e)
{
    //if (e.RowType == Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLRowType.DataRow)
    //{
    //    if (e.Row.Cells[0] != null)
    //    {
    //        Guid userId = new Guid(e.Row.Cells[0].Data.DataItem.ToString());
    //        e.Row.Cells[e.Row.Cells.Count - 1].Data.DataItem = DateTime.Now.ToString("dd-MM-yyyy HH:mm");
    //        PrizeDraw.Data.User.Export(userId);
    //    }
    //}
}

protected void rcbQuestionSets_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
{
    if (rcbQuestionSets.SelectedItem != null)
    {
        loadGrid();
        rgLeadListing.Rebind();
    }
}

private void loadGrid()
{
    rgLeadListing.DataSource = PrizeDraw.Data.User.GetLeads(int.Parse(rcbQuestionSets.SelectedValue));
}

[Peter Parsons]

hi,

was there anything else you needed before you look into this?

[Daniel]

Hello Anthony,

I hoped that I will be able to reproduce the problem on my end but unfortunately to no avail. I attached a runnable demo to this ticket. Please test it on your end and let me know if I'm missing something.

Best regards,
Daniel
the Telerik team

[Peter Parsons]

Hi,

the issue is with the grid datasource, your example uses a single denomalized table, I am creating a dataset with two related tables.

There is a second issue with your example, in that neither aText nor qText are exported to the spreadsheet.

Here is the code I use to create the dataset...

public static DataSet GetLeads(int fieldsetId)
{
    DataSet dataSet = new DataSet();

    SqlConnection conn = null;
    conn = new SqlConnection(ConfigurationManager.ConnectionStrings["PrizeDraw"].
                                     ConnectionString);
    SqlCommand cmd = null;
    SqlDataAdapter da = new SqlDataAdapter();
    DataTable dataTable = new DataTable();

    try
    {
        conn.Open();
        cmd = new SqlCommand("udp_UsersList", conn);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter("@fieldsetId", fieldsetId));

        da.SelectCommand = cmd;
        da.Fill(dataTable);
    }
    catch (Exception e)
    {
        throw e;
    }
    finally
    {
        if (cmd != null) cmd.Dispose();
        da.Dispose();
        conn.Close();
    }

    DataColumn[] keys = new DataColumn[1];
    keys[0] = dataTable.Columns["userId"];
    dataTable.PrimaryKey = keys;
    dataTable.TableName = "Users";
    dataSet.Tables.Add(dataTable);

    da = new SqlDataAdapter();
    dataTable = new DataTable();

    try
    {
        conn.Open();
        cmd = new SqlCommand("udp_UsersGetLeads", conn);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter("@fieldsetId", fieldsetId));

        da.SelectCommand = cmd;
        da.Fill(dataTable);
    }
    catch (Exception e)
    {
        throw e;
    }
    finally
    {
        if (cmd != null) cmd.Dispose();
        da.Dispose();
        conn.Close();
    }

    keys = new DataColumn[1];
    keys[0] = dataTable.Columns["uaId"];
    dataTable.PrimaryKey = keys;
    dataTable.TableName = "Leads";
    dataSet.Tables.Add(dataTable);

    DataRelation UsersLeadsRelation = new DataRelation("UsersLeads", dataSet.Tables["Users"].Columns["userId"], dataSet.Tables["Leads"].Columns["userId"]);
    dataSet.Relations.Add(UsersLeadsRelation);

    return dataSet;
}

-a-

[Peter Parsons]

hmmm,

not sure where else to look with this, the grid is obviously happy with the datatables and relationships in the dataset, the grid works and displays fine. it's just the export that falls over.

-a-

[Daniel]

Hello Anthony,

the issue is with the grid datasource, your example uses a single denomalized table, I am creating a dataset with two related tables.
Thanks to the posted code I was able to reproduce the problem locally but I will need some time to investigate it thoroughly.

There is a second issue with your example, in that neither aText nor qText are exported to the spreadsheet.
You have to HierarchyDefaultExpanded as shown below:

protected void rgLeadListing_ItemCommand(object source, GridCommandEventArgs e)
{
    if (e.CommandName == "ExportToExcel")
    {
        rgLeadListing.MasterTableView.HierarchyDefaultExpanded = true;
        rgLeadListing.ExportSettings.FileName = "LeadList" + DateTime.Now.ToString("-dd-MM-yyyy");
        foreach (GridColumn col in rgLeadListing.MasterTableView.Columns)
            col.Visible = true;
    }
}

Best regards,
Daniel
the Telerik team

[Peter Parsons]

hi,

just a friendly bump to see how things are progressing...


-a-

[Daniel]

Hello Anthony,

We addressed the issue in the latest internal builds. You can download this build from the link below. Let me know whether the problem still persists.
Latest Internal Builds

We updated your Telerik points as a sign of gratitude for your cooperation.

Kind regards,
Daniel
the Telerik team