Save tree-like data to Excel

[Paul Meems]

I have a data model, which simplified looks like this:

Table1 has a 1:1 relation with Table2

Table1 has a 1:n relation with Table3

Table1 has a 1:n relation with Table4

I want to export the data of Table1 and its relations to Excel using XlsIO.

Currently, I just use  worksheet.ImportData(). This works fine.

But when Table1 has multiple entries in Table3, say 2 entries and it has 2 entries in Table4 I end up with 4 rows:

Row-Table1     Row-Table2     Row1-Table3     Row1-Table4     

Row-Table1     Row-Table2     Row1-Table3     Row2-Table4     

Row-Table1     Row-Table2     Row2-Table3     Row1-Table4     

Row-Table1     Row-Table2     Row2-Table3     Row2-Table4     

Getting 4 rows is OK, but can I easily skip the duplicate data for Table1 and Table2? Because this export will be used in some additional manual computing and if you now select a column of Table1-data the SUM and AVG are wrong.

I would like to see something like this:

Row-Table1     Row-Table2     Row1-Table3     Row1-Table4     

                                              Row2-Table4     

                              Row2-Table3     Row1-Table4     

                                              Row2-Table4    

Is this possible?

[Shamini Kiruba Sobers]

Hi Paul, 

Greetings from Syncfusion. 

You can use ImportData() method with ExcelImportDataOptions by setting the ExcelNestedDataLayoutOptions enumeration to Merge. 

Kindly look into the following UG and demo links that may help to achieve your requirement. 

Help links: 

https://help.syncfusion.com/file-formats/xlsio/working-with-data?cs-save-lang=1&cs-lang=csharp#import-data-from-nested-collection-objects 

https://ej2.syncfusion.com/aspnetmvc/XlsIO/ImportNestedCollection#/material 

Kindly let us know if the suggestion helps. 

Regards, 

Shamini 

[Paul Meems]

Thanks that is exactly what I was looking for.

I first need to change my model and will then try it.

I now use a view in SQL server to get all data at once. I need to rewrite it to use .Include for the 1:n tables.

[Mohan Chandran]

Hi Paul, 

Thank you for the update. We will wait to hear from you. 

Regards. 

Mohan. 

[Paul Meems]

Not sure if this is within your scope, but I'm using a view to get my dto.

This is rather a complicated view using `ROW_NUMBER() OVER (PARTITION BY ` so I can't create the query using LINQ.

It works fine. I have a dedicated DTO to map the results.

And this result I want to export to Excel.

I removed the 1:n table from my query and instead of the properties in my view-dto I'm using 

`public ICollection<Sector> Sectors { get; set; }` now.

This Sector class had 3 properties and 1 navigation property to one of the classes which is also used in the view-dto.

When I now execute my view: `var rows = context.LatestEmployeeData` where `LatestEmployeeData` is my view-dto I need to include the Sector table.

I tried adding `.Include(x=>x.Sectors)` but that results in an error: 'Lambda expression used inside Include is not valid.'

Most likely this is because EFCore cannot create the JOIN part.

Anybody here knows how to solve this?

[Paul Meems]

I think I've fixed it. Not sure if it is the recommended workflow, but at least I get the data I expect.

I first call my view without any of the 1:n tables:

var rows = context.LatestEmployeeData

Next I get the tables separately:

var sectors = context.Sector.OrderBy(x => x.FooId).ToList();

-- two other tables.

Next I loop my view data and fill the collections:

foreach (var row in rows)

{

  row.SectorCollection = sectors.Where(x => x.FooId== latestEmployeeData.FooId).ToList();

  -- the same for 2 other tables

}

return rows;

               

[Shamini Kiruba Sobers]

Hi Paul, 

Thanks for the updates. We are glad that you have found a solution and getting the expected result. 

If you are still facing any issue, kindly share us the issue reproducing sample so that we can analyze and provide prompt solution at the earliest. 

Regards, 

Shamini