Use EPPlus to export dynamic query result to Excel

c# dynamic-linq epplus generics

Question

I have an application where users input a large number of data fields and then wish to produce Excel spreadsheets from custom queries on the data.

For single-table queries, I can return an IEnumerable<T> and use the LoadFromCollection() method in EPPlus to create the spreadsheet with only the requested columns, eg:

List<string> fieldNames = userFieldsParam;

var data = Entities.MainStatements
           .Where(ms => ms.FinancialYear.YearString == finYearParam);
var displayFields = typeof(MainStatements).GetMembers()
                    .Where(st => fieldNames.Contains(st.Name)).ToList();

worksheet.Cells["a2"].LoadFromCollection(data, false, OfficeOpenXm.Table.TableStyles.None, 
                      BindingFlags.Default, displayFields.ToArray());

However, as the users' queries span related tables, my query returns a Dynamic Class (not an IEnumerable<T>), which doesn't load.

var data = Entities.MainStatements
           .Include(RevenueStatements)
           .Include(ExpenditureStatements)
           .Where(ms => ms.FinancialYear.YearString == finYearParam)
           .Select(fieldNamesString);

I've tried using the (OP's) solution here, by returning an IEnumerable with all the columns (including .Included table columns), but cannot work out how to get MemberInfo data from related tables. First I tried including the table name in the requested column list, but GetMembers() didn't add them.

Next I tried to create a MemberInfo for each of the individual tables, but when I tried to add table name information to collate them in one list, I found that MemberInfo is readonly.

foreach (MemberInfo mi in typeof(RevenueStatements).GetMembers().Where(rs => fieldNames.Contains(rs.Name)))
{ 
  mi.Name = "RevenueStatements." + mi.Name;     <-- Property or indexer mi
  displayFields.Add(mi);
} 

How do I populate an Excel spreadsheet from a multi-table query result with custom columns using EPPlus (or other 3rd party solution)? Specifically, how do I tell EPPlus which columns to include from the related tables?

1
1
2/4/2019 9:28:54 AM

Popular Answer

Build a DataTable using your results and write to excel using EPPlus method LoadFromDataTable();

0
2/5/2019 8:51:47 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow