使用LoadFromCollection和匿名类型的epplus [英] epplus using LoadFromCollection with anonymous types

问题描述

我有一个 IEnumerable< object> 数据源，其中包含一个匿名类型集合.匿名类型的实际结构在设计时无法得知，因此我试图找到一种可以处理任何匿名类型的通用解决方案.

I have a IEnumerable<object> dataSource which contains a collection anonymous types. The actual structure of the anonymous type won't be known at design time, so I'm trying to find a generic solution that can handle any anonymous type.

如何将它们加载到epplus中以创建电子表格?我有一个名为ws的工作表，并且尝试过:

How can I load them into epplus to create a spreadsheet? I have a worksheet called ws and I tried:

ws.Cells["A1"].LoadFromCollection(dataSource, true);

但是，当运行时，它将所有匿名类型的属性输出到单个单元格中:

However when that runs it outputs all of the anonymous type's properties into a single cell:

{ Id = 10000, Title = This is a test }

我尝试使用以下方式传递MemberInfo:

I've tried passing in MemberInfo using:

var members = dataSource.First().GetType().GetMembers();
ws.Cells["A1"].LoadFromCollection(this._dataSource, true,
    TableStyles.Medium1, BindingFlags.Public, members);

但这会引发异常:

参数Properties中提供的属性必须与T具有相同的类型

Supplied properties in parameter Properties must be of the same type as T

关于如何在c#中使用匿名类型创建电子表格的任何建议?

Any suggestions on how I can create a spreadsheet using anonymous types in c#?

推荐答案

您可以对匿名类型进行分组，以使其更易于使用dataTables导出. 错误 参数属性"中提供的属性必须是与T相同的类型"仍然存在，并且一种解决方法是使用DataTables.

You could group the anonymous types to make it easier for exporting with dataTables. The bug "Supplied properties in parameter Properties must be of the same type as T" is still there and a workaround is using DataTables.

// Imagine list is your main datasource
IEnumerable<object> list = Enumerable.Empty<object>(); // Data Source of <object>

// Added anon types at runtime added to the object list
var anonTypesOne = new object[] 
{ 
    new { GuidID = Guid.NewGuid(), StringProperty = "the string property" },
    new { IntegerID = 1, IntegerProperty = 99 }
};

var anonTypesTwo = new object[]
{
    new { StringID = "1", BooleanProperty = true, NumberProperty = 3, StringProperty = "Four" },
    new { GuidID = Guid.NewGuid(), NumberThree = 3 },
    new { GuidID = Guid.NewGuid(), NumberThree = 3 },
    new { GuidID = Guid.NewGuid(), NumberThree = 3 }
};

list = list.Concat(anonTypesOne).Concat(anonTypesTwo);

// Grouping works on anon types so we can group the export into their own tables
var groupings = list.GroupBy(i => i.GetType());

using(var package = new ExcelPackage(new FileInfo("C:\\Temp\\Anon.xlsx")))
{
    var ws = package.Workbook.Worksheets.Add("Anonymous Types");

    // add each "anon type matched grouping"
    foreach(var grouping in groupings)
    {
        var isNew = ws.Dimension == null; // the sheet is empty if Dimension is null.
        var row = 0;

        if(isNew)
        {
            row = 1; // start from the first row
        }
        else 
        {       
            // otherwise there are tables already, start from the bottom
            row = ws.Dimension.End.Row; 
        }       

        // because of EPP inheritance bug of T, we can just use dataTable
        DataTable dt = new DataTable(grouping.Key.Name);
        var properties = grouping.Key.GetProperties(); // Get anon type Properties

        foreach(var property in properties)
        {
            dt.Columns.Add(property.Name);
        }

        foreach(var item in grouping.ToList())
        {
            var dataRow = dt.NewRow();

            foreach(var p in properties) // populate a single row
            {
                dataRow[p.Name] = p.GetValue(item); // item is anon object instance
            }

            dt.Rows.Add(dataRow);
        }

        if(isNew) // load into the top most left cell of the worksheet
            ws.Cells[1, 1].LoadFromDataTable(dt, PrintHeaders: true);
        else // load from the dimension of current items + 1 row for spacing
            ws.Cells[ws.Dimension.End.Row + 1, 1].LoadFromDataTable(dt, PrintHeaders: true);

        ws.InsertRow(ws.Dimension.End.Row + 2, 5); // Insert some padding between each group

    }

    package.Save();
}

这篇关于使用LoadFromCollection和匿名类型的epplus的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！