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 620 Views

Grid

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

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

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

answered on 19 May 2015, 12:22 PM

gridandexport.png

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.

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

answered on 21 May 2015, 01:49 PM

HierarchyIssue.zip

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