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

Radgrid Export to Excel does not retrieve all detail column values

4 Answers 463 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Loren
Top achievements
Rank 2
Loren asked on 14 May 2015, 02:46 PM

Hi All,

I am using VS 2012 with the 2014 UI controls. We have a master page and this is in a content page. I have omitted sections with ---- to give main structure.

<telerik:RadGrid ID="RadGrid1" runat="server" AllowFilteringByColumn="True" AllowPaging="True"
    AllowSorting="True" AutoGenerateColumns="False" CellSpacing="-1" ShowFooter="True"
    OnItemCommand="RadGrid1_ItemCommand"
    OnNeedDataSource="RadGrid1_NeedDataSource"
    OnDetailTableDataBind="RadGrid1_DetailTableDataBind">
    <ExportSettings FileName="Safekeeping.xls" IgnorePaging="True" ExportOnlyData="true">
        <Excel Format="ExcelML" />
    </ExportSettings>
    <MasterTableView CommandItemDisplay="Top" Name="ShippingMaster" DataKeyNames="ScheduleId">
        <CommandItemSettings ShowAddNewRecordButton="False" ShowExportToExcelButton="True" />
        <DetailTables>
            <telerik:GridTableView runat="server" ShowFooter="False" AutoGenerateColumns="false" DataKeyNames="SheduleId" Name="ShippingDetail">
                <ParentTableRelation>
                    <telerik:GridRelationFields DetailKeyField="ScheduleId" MasterKeyField="ScheduleId" />
                </ParentTableRelation>
                <Columns>
                    <telerik:GridBoundColumn DataField="ScheduleId" UniqueName="ScheduleId" Display="false" AllowFiltering="false">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="AccountNumber" HeaderText="Account Number" UniqueName="AccountNumber" AllowFiltering="false">
                    </telerik:GridBoundColumn>
----
                </Columns>
            </telerik:GridTableView>
        </DetailTables>
        <Columns>
            <telerik:GridBoundColumn DataField="CenterId" DataType="System.Int64" UniqueName="CenterId" Display="false" AllowFiltering="false">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="ScheduleId" DataType="System.Int64" UniqueName="ScheduleId" Display="false" AllowFiltering="false">
            </telerik:GridBoundColumn>
----
        </Columns>
    </MasterTableView>
    <PagerStyle AlwaysVisible="True" />
</telerik:RadGrid>
 
The code is as follows. The Lists are just POCOs.

protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
{
    RadGrid1.DataSource = GetShipmentDetailBySearch();
}
 
private List<Shipment> GetShipmentDetailBySearch()
{
    if (ValidSearchCriteria())
    {
        lstShipment = shipUi.GetShipmentDetailBySearch(Int32.Parse(DropDrillYear.SelectedValue), TxtAccountNo.Text);
    }
    return lstShipment;
}
 
protected void RadGrid1_DetailTableDataBind(object sender, GridDetailTableDataBindEventArgs e)
{
    GridDataItem gdi = e.DetailTableView.ParentItem;
    if (e.DetailTableView.Name == "ShippingDetail")
    {
        ShipmentUiController shipUi = new ShipmentUiController();
        int drillYear = Int32.Parse(DropDrillYear.SelectedValue);
        int scheduleId = Int32.Parse(gdi.GetDataKeyValue("ScheduleId").ToString());
                 
        List<ShippingDetail> sd = new List<ShippingDetail>();
        sd = shipUi.GetShipmentBoxDetailByScheduleId(drillYear, scheduleId);
        e.DetailTableView.DataSource = sd;
    }
}
 
protected void RadGrid1_ItemCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
{
    if (e.CommandName == RadGrid.ExportToExcelCommandName)
    {
        RadGrid1.MasterTableView.HierarchyDefaultExpanded = true;
        RadGrid1.MasterTableView.DetailTables[0].HierarchyDefaultExpanded = true;               
        RadGrid1.MasterTableView.GetColumn("ShipmentDate").Visible = false;
....
        RadGrid1.MasterTableView.DetailTables[0].GetColumn("ContentsFound").Visible = false;
 
        RadGrid1.ExportSettings.IgnorePaging = true;
        RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;
        RadGrid1.Rebind();
        RadGrid1.ExportSettings.OpenInNewWindow = true;
        RadGrid1.MasterTableView.ExportToExcel();
    }
}

And the results of the export are like this. The 20335 below is the schedule id that belongs with the detail table. So it is firing.

Center         Cost Center  Company No  Associate         Associate
Cedar Ridge     424         99          Alex Lifeson      John Wetton
                 
           20335           

But it should be

Center          Cost Center     Company No  Associate         Associate
Cedar Ridge     424             99          Alex Lifeson      John Wetton
      Customer Name        Received
      Robert Fripp         true
      Tom Waits            false

I have tried numerous solutions from different threads here, but nothing seems to work correctly. I am really at my wits end on this one. This eventually needs to be ajaxified, but I am just trying to get the base working first. Any help would be greatly appreciated!!!

4 Answers, 1 is accepted

Sort by
0
Kostadin
Telerik team
answered on 19 May 2015, 07:20 AM
Hello Loren,

I examined the provided sample and I noticed that you are using ExcelML export format. Note that when using this export format you have to set HierarchyLoadMode to SeverBind in order to fetch the detail table data.  Additionally I noticed that you are calling ExportToExcel method when ExportToExcelCommandName is fired. Note that this is not necessary since the ExportToExcel method fires this command.

Regards,
Kostadin
Telerik
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Loren
Top achievements
Rank 2
answered on 19 May 2015, 12:22 PM

Hi Kostadin, 

I made the changes and it didn't even produce an excel file, it just made the grid look odd. I have attached screenshot.
I have also tried using a DataSet with two DataTables to hold the master and detail data with no luck.
I am really at my wits end on this. I have other normal grids that export fine, but this, with detail level, is just not working for me :(
The actual grid will have bound controls throughout, but I am just trying to get the simple version to work. Granted, this is inside the application, but since other grids work, I thought it would work. I am going to work on a version today with dummy data to see if I can get that to work, then go from there.

0
Loren
Top achievements
Rank 2
answered on 19 May 2015, 01:54 PM

Hi Again,

I created a dummy page, and it is still not working :(
Here is truncated page:

<form id="form1" runat="server">
<telerik:RadScriptManager ID="RadScriptManager1" runat="server"></telerik:RadScriptManager>
<div>
 <telerik:RadGrid ID="RadTest" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CellSpacing="-1" ShowFooter="True"
    OnItemCommand="RadTest_ItemCommand"
    OnNeedDataSource="RadTest_NeedDataSource"
    OnDetailTableDataBind="RadTest_DetailTableDataBind">
<ExportSettings FileName="Safekeeping.xls" IgnorePaging="True" ExportOnlyData="true">
<Excel Format="ExcelML" />
</ExportSettings>                      
    <MasterTableView CommandItemDisplay="Top" Name="ShippingMaster" DataKeyNames="BankingCenterScheduleId" HierarchyLoadMode="ServerBind">
<CommandItemSettings ShowAddNewRecordButton="False" ShowExportToExcelButton="True" />
    <DetailTables>
        <telerik:GridTableView runat="server" ShowFooter="False" AutoGenerateColumns="false" DataKeyNames="BankingCenterScheduleId" Name="ShippingDetail">
            <ParentTableRelation>
                <telerik:GridRelationFields DetailKeyField="BankingCenterScheduleId" MasterKeyField="BankingCenterScheduleId" />
            </ParentTableRelation>
            <Columns>
                <telerik:GridBoundColumn DataField="TrmDate"  HeaderText="TRM Date" UniqueName="TrmDate" DataType="System.DateTime" DataFormatString="{0:MM/dd/yyyy}" AllowFiltering="false">
                </telerik:GridBoundColumn>
                <telerik:GridCheckBoxColumn DataField="Completed" HeaderText="Completed" UniqueName="Completed" AllowFiltering="false">
                </telerik:GridCheckBoxColumn>
                <telerik:GridBoundColumn DataField="BankingCenterScheduleId" UniqueName="BankingCenterScheduleId" Display="false" AllowFiltering="false">
                </telerik:GridBoundColumn>
            </Columns>
        </telerik:GridTableView>
    </DetailTables>
    <Columns>
        <telerik:GridBoundColumn DataField="BankingCenterId" DataType="System.Int64" UniqueName="BankingCenterId" Display="false" AllowFiltering="false">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="BankingCenterScheduleId" DataType="System.Int64" UniqueName="BankingCenterScheduleId" Display="false" AllowFiltering="false">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="CostCenter" DataType="System.Int32" HeaderText="Cost Center" SortExpression="CostCenter" UniqueName="CostCenter" AllowFiltering="false">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="CompanyNo" DataType="System.Int32" HeaderText="Company No" SortExpression="CompanyNo" UniqueName="CompanyNo" AllowFiltering="false">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="Comments" HeaderText="Comments" SortExpression="Comments" UniqueName="Comments" AllowFiltering="false">
        </telerik:GridBoundColumn>
    </Columns>
    </MasterTableView>
    <PagerStyle AlwaysVisible="True" />
</telerik:RadGrid>  
</div>
</form>

And here is the code with dummy entities

private List<ShipmentLoren> PopulateMasterTable()
{
    List<ShipmentLoren> sl = new List<ShipmentLoren>();
    ShipmentLoren s = new ShipmentLoren();
    s.BankingCenterId = 1;
    s.BankingCenterName = "Booga Booga";
    s.BankingCenterScheduleId = 1;
    s.Comments = "";
    s.CompanyNo = 1;
    s.ContentNumbersmatch = false;
    s.ContentsReceivedDate = DateTime.Parse("2011-01-01");
    s.CostCenter = 1;
    s.EscalationDate = DateTime.Parse("2011-01-01");
    s.NoOfBoxes = 1;
    s.NoOfIndividualPackages = 1;
    s.ShipmentDate = DateTime.Now;
    s.StartDateStartTime = DateTime.Now;
 
    sl.Add(s);
 
    ShipmentLoren s2 = new ShipmentLoren();
    s2.BankingCenterId = 2;
    s2.BankingCenterName = "Wooga Woga";
    s2.BankingCenterScheduleId = 2;
    s2.Comments = "";
    s2.CompanyNo = 2;
    s2.ContentNumbersmatch = false;
    s2.ContentsReceivedDate = DateTime.Parse("2011-01-01");
    s2.CostCenter = 2;
    s2.EscalationDate = DateTime.Parse("2011-01-01");
    s2.NoOfBoxes = 1;
    s2.NoOfIndividualPackages = 1;
    s2.ShipmentDate = DateTime.Now;
    s2.StartDateStartTime = DateTime.Now;
 
    sl.Add(s2);
 
    ShipmentLoren s3 = new ShipmentLoren();
    s3.BankingCenterId = 3;
    s3.BankingCenterName = "Shazam";
    s3.BankingCenterScheduleId = 3;
    s3.Comments = "";
    s3.CompanyNo = 3;
    s3.ContentNumbersmatch = false;
    s3.ContentsReceivedDate = DateTime.Parse("2011-01-01");
    s3.CostCenter = 3;
    s3.EscalationDate = DateTime.Parse("2011-01-01");
    s3.NoOfBoxes = 1;
    s3.NoOfIndividualPackages = 1;
    s3.ShipmentDate = DateTime.Now;
    s3.StartDateStartTime = DateTime.Now;
 
    sl.Add(s3);
 
    ShipmentLoren s4 = new ShipmentLoren();
    s4.BankingCenterId = 3;
    s4.BankingCenterName = "Shazam";
    s4.BankingCenterScheduleId = 3;
    s4.Comments = "";
    s4.CompanyNo = 3;
    s4.ContentNumbersmatch = false;
    s4.ContentsReceivedDate = DateTime.Parse("2011-01-01");
    s4.CostCenter = 3;
    s4.EscalationDate = DateTime.Parse("2011-01-01");
    s4.NoOfBoxes = 1;
    s4.NoOfIndividualPackages = 1;
    s4.ShipmentDate = DateTime.Now;
    s4.StartDateStartTime = DateTime.Now;
 
    sl.Add(s4);
 
    ShipmentLoren s5 = new ShipmentLoren();
    s5.BankingCenterId = 3;
    s5.BankingCenterName = "Shazam";
    s5.BankingCenterScheduleId = 3;
    s5.Comments = "";
    s5.CompanyNo = 3;
    s5.ContentNumbersmatch = false;
    s5.ContentsReceivedDate = DateTime.Parse("2011-01-01");
    s5.CostCenter = 3;
    s5.EscalationDate = DateTime.Parse("2011-01-01");
    s5.NoOfBoxes = 1;
    s5.NoOfIndividualPackages = 1;
    s5.ShipmentDate = DateTime.Now;
    s5.StartDateStartTime = DateTime.Now;
 
    sl.Add(s5);
    return sl;
}
 
 
protected void RadTest_ItemCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
{
    if (e.CommandName == RadGrid.ExportToExcelCommandName)
    {
 
        RadTest.MasterTableView.HierarchyDefaultExpanded = true;
        RadTest.MasterTableView.DetailTables[0].HierarchyDefaultExpanded = true; // for the second level 
 
        RadTest.MasterTableView.GetColumn("ShipmentDate").Visible = false;
        RadTest.MasterTableView.GetColumn("NoOfBoxes").Visible = false;
        RadTest.MasterTableView.GetColumn("NoOfIndividualPackages").Visible = false;
        RadTest.MasterTableView.GetColumn("startDatestartTime").Visible = false;
        RadTest.MasterTableView.GetColumn("ContentsReceivedDate").Visible = false;
        RadTest.MasterTableView.GetColumn("ContentNumbersmatch").Visible = false;
        RadTest.MasterTableView.GetColumn("EscalationDate").Visible = false;
        RadTest.MasterTableView.GetColumn("Comments").Visible = false;
 
        RadTest.ExportSettings.IgnorePaging = true;
        RadTest.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;
    }
}
 
protected void RadTest_NeedDataSource(object sender, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
{
    RadTest.DataSource = PopulateMasterTable();
}
 
protected void RadTest_DetailTableDataBind(object sender, Telerik.Web.UI.GridDetailTableDataBindEventArgs e)
{
    GridDataItem gdi = e.DetailTableView.ParentItem;
    List<ShippingDetail> lsd = new List<ShippingDetail>();
    ShippingDetail sd = new ShippingDetail();
    if (e.DetailTableView.Name == "ShippingDetail")
    {
         
        int scheduleId = Int32.Parse(gdi.GetDataKeyValue("BankingCenterScheduleId").ToString());
        if (scheduleId == 2)
        {
            sd.BankingCenterScheduleId = scheduleId;
            sd.BoxId = "1";
            sd.Completed = false;
            sd.ContentsFound = false;
            sd.DrillDate = "2014-12-31";
            sd.PrimaryRenterName = "Bob Saggett";
            sd.SafeboxAccountNumber = "12345";
            sd.ShippingTrackingNumber = "";
            sd.TrmConfirmationNumber = "";
            sd.TrmDate = null;
            sd.TrmRequested = false;
            lsd.Add(sd);
        }
        else
        {
            sd.BankingCenterScheduleId = scheduleId;
            sd.BoxId = "9";
            sd.Completed = false;
            sd.ContentsFound = false;
            sd.PrimaryRenterName = "Bill Bruford";
            sd.SafeboxAccountNumber = "90909";
            sd.ShippingTrackingNumber = "";
            sd.TrmConfirmationNumber = "";
            sd.TrmDate = null;
            sd.TrmRequested = false;
             
            lsd.Add(sd);
            ShippingDetail sd2 = new ShippingDetail();
            sd2.BankingCenterScheduleId = scheduleId;
            sd2.BoxId = "9";
            sd2.Completed = false;
            sd2.ContentsFound = false;
            sd2.DrillDate = "2015-5-15";
            sd2.PrimaryRenterName = "Bill Bruford";
            sd2.SafeboxAccountNumber = "90909";
            sd2.ShippingTrackingNumber = "";
            sd2.TrmConfirmationNumber = "";
            sd2.TrmDate = null;
            sd2.TrmRequested = false;
 
            lsd.Add(sd2);
 
        }
        e.DetailTableView.DataSource = lsd;
    }
 
 
}
 
#region entity
public class ShipmentLoren
{
    public long BankingCenterId { get; set; }
    public long BankingCenterScheduleId { get; set; }
    public string BankingCenterName { get; set; }
    public int CostCenter { get; set; }
    public int CompanyNo { get; set; }
    public int NoOfBoxes { get; set; }
    public int NoOfIndividualPackages { get; set; }
    public DateTime? ShipmentDate { get; set; }
    public DateTime? ContentsReceivedDate { get; set; }
    public bool ContentNumbersmatch { get; set; }
    public DateTime? StartDateStartTime { get; set; }
    public DateTime? EscalationDate { get; set; }
    public string Comments { get; set; }
}
 
public class ShipmentDetail
{
    public string BoxId { get; set; }
    public string SafeboxAccountNumber { get; set; }
    public string PrimaryRenterName { get; set; }
    public string ContentsFound { get; set; }
    public string TrmRequested { get; set; }
    public string TrmConfirmationNumber { get; set; }
    public DateTime? TrmDate { get; set; }
    public bool Completed { get; set; }
    public string IsEdited { get; set; }
    public string ShippingTrackingNumber { get; set; }
}
#endregion

I am sure that this is something blindingly simple, but I cannot see it

 

0
Kostadin
Telerik team
answered on 21 May 2015, 01:49 PM
Hi Loren,

I examine the provided code and I noticed that you are using a ArrayList to populate both MasterTableView and DetailTableView. Note that when exporting arrays, ArrayList, custom objects, LinqDataSource you should set UseAllDataFields="true" in MasterTableView and on each DetailTableView. Also in case you are binding the detail tables programmatically you do not need a ParentTableRelation.

I modified the provided sample and attached it to this thread. Please check it out and let me know about the result.

Regards,
Kostadin
Telerik
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Tags
Grid
Asked by
Loren
Top achievements
Rank 2
Answers by
Kostadin
Telerik team
Loren
Top achievements
Rank 2
Share this question
or