如何使用EPPlus将子对象导出为Excel

Question

我正在使用EPPlus帮助我将数据导出为Excel。虽然我还在学习如何正确地导出数据，但是现在遇到了一个问题：我无法将包含子对象的对象全部展开并导出。

ParentObject
    public string A;
    public string B;
    public ChildObject ChildObject;

ChildObject
    public string C;
    public string D;

所以我希望我的导出Excel看起来像这样：

A        B        C        D
aa1      bb1      cc1      dd1
aa2      bb2      cc2      dd2
aa3      bb3      cc3      dd3

这是我的当前实现情况：

public void CreateExcel(IEnumerable<T> dataCollection, string fullyQualifiedFileName, string worksheetName)
    {
        using (var package = new ExcelPackage(new FileInfo(fullyQualifiedFileName)))
        {
            var worksheet =
                package.Workbook.Worksheets.FirstOrDefault(excelWorksheet => excelWorksheet.Name == worksheetName) ??
                package.Workbook.Worksheets.Add(worksheetName);

            var membersToInclude = typeof(T)
                .GetMembers(BindingFlags.Instance | BindingFlags.Public)
                .Where(p => Attribute.IsDefined(p, typeof(ExcelUtilityIgnoreAttribute)) == false
                            || p.GetCustomAttribute<ExcelUtilityIgnoreAttribute>().IsIgnored == false)
                .ToArray();

            worksheet.Cells["A1"].LoadFromCollection(dataCollection, true, OfficeOpenXml.Table.TableStyles.None,
                BindingFlags.Public, membersToInclude);

            package.Save();
        }
    }

我尝试使用Microsoft泛型并使用扩展对象，但EPPlus不支持泛型，有没有一种方法可以导出具有子对象的对象？

另外，是否有其他库可供使用？

- Muds

Answer 1

没有本地函数可以做到这一点。很难想出一个通用的解决方案，因为它需要做出很多假设。哪些属性类型应该自动导出，哪些应该被视为子节点并具有其属性导出或展开。但是，如果你能想到这一点，那么从那里开始就是基本的树遍历。

下面是我从类似任务中改编的代码。在这里，我假设任何既是字符串又是没有属性的数据类型都被认为是导出的值类型（例如int、double等）。但是很容易根据需要进行调整。我匆忙拼凑了这个代码，可能还没有完全优化：

public static void ExportFlatExcel<T>(IEnumerable<T> dataCollection, FileInfo file, string worksheetName)
{
    using (var package = new ExcelPackage(file))
    {
        var worksheet =
            package.Workbook.Worksheets.FirstOrDefault(excelWorksheet => excelWorksheet.Name == worksheetName) ??
            package.Workbook.Worksheets.Add(worksheetName);

        const BindingFlags flags = BindingFlags.Instance | BindingFlags.Public;
        var props = typeof (T).GetProperties(flags);

        //Map the properties to types
        var rootTree = new Branch<PropertyInfo>(null);

        var stack = new Stack<KeyValuePair<PropertyInfo, IBranch<PropertyInfo>>>(
            props
                .Reverse()
                .Select(pi =>
                    new KeyValuePair<PropertyInfo, IBranch<PropertyInfo>>(
                        pi
                        , rootTree
                        )
                )
            );

        //Do a non-recursive traversal of the properties
        while (stack.Any())
        {
            var node = stack.Pop();
            var prop = node.Key;
            var branch = node.Value;

            //Print strings
            if (prop.PropertyType == typeof (string))
            {
                branch.AddNode(new Leaf<PropertyInfo>(prop));
                continue;
            }

            //Values type do not have properties
            var childProps = prop.PropertyType.GetProperties(flags);
            if (!childProps.Any())
            {
                branch.AddNode(new Leaf<PropertyInfo>(prop));
                continue;
            }

            //Add children to stack
            var child = new Branch<PropertyInfo>(prop);
            branch.AddNode(child);

            childProps
                .Reverse()
                .ToList()
                .ForEach(pi => stack
                    .Push(new KeyValuePair<PropertyInfo, IBranch<PropertyInfo>>(
                        pi
                        , child
                        )
                    )
                );
        }

        //Go through the data
        var rows = dataCollection.ToList();
        for (var r = 0; r < rows.Count; r++)
        {
            var currRow = rows[r];
            var col = 0;

            foreach (var child in rootTree.Children)
            {
                var nodestack = new Stack<Tuple<INode, object>>();
                nodestack.Push(new Tuple<INode, object>(child, currRow));

                while (nodestack.Any())
                {
                    var tuple = nodestack.Pop();
                    var node = tuple.Item1;
                    var currobj = tuple.Item2;

                    var branch = node as IBranch<PropertyInfo>;
                    if (branch != null)
                    {
                        currobj = branch.Data.GetValue(currobj, null);

                        branch
                            .Children
                            .Reverse()
                            .ToList()
                            .ForEach(cnode => nodestack.Push(
                                new Tuple<INode, object>(cnode, currobj)
                                ));

                        continue;
                    }

                    var leaf = node as ILeaf<PropertyInfo>;
                    if (leaf == null)
                        continue;

                    worksheet.Cells[r + 2, ++col].Value = leaf.Data.GetValue(currobj, null);

                    if (r == 0)
                        worksheet.Cells[r + 1, col].Value = leaf.Data.Name;
                }
            }
        }

        package.Save();
        package.Dispose();
    }
}

假设你有这样一个结构：

#region Classes

public class Parent
{
    public string A { get; set; }
    public Child1 Child1 { get; set; }
    public string D { get; set; }
    public int E { get; set; }
    public Child2 Child2 { get; set; }
}

public class Child1
{
    public string B { get; set; }
    public string C { get; set; }
}

public class Child2
{
    public Child1 Child1 { get; set; }
    public string F { get; set; }
    public string G { get; set; }
}

#endregion

#region Tree Nodes

public interface INode { }

public interface ILeaf<T> : INode
{
    T Data { get; set; }
}

public interface IBranch<T> : ILeaf<T>
{
    IList<INode> Children { get; }
    void AddNode(INode node);
}

public class Leaf<T> : ILeaf<T>
{
    public Leaf() { }

    public Leaf(T data) { Data = data; }

    public T Data { get; set; }
}

public class Branch<T> : IBranch<T>
{
    public Branch(T data) { Data = data; }

    public T Data { get; set; }

    public IList<INode> Children { get; } = new List<INode>();

    public void AddNode(INode node)
    {
        Children.Add(node);
    }
}

#endregion

我将作为一个测试来翻译这个内容：

[TestMethod]
public void ExportFlatTest()
{
    var list = new List<Parent>();

    for (var i = 0; i < 20; i++)
        list.Add(new Parent
        {
            A = $"A-{i}",
            D = $"D-{i}",
            E = i*10,
            Child1 = new Child1
            {
                B = $"Child1-B-{i}",
                C = $"Child1-C-{i}",
            },
            Child2 = new Child2
            {
                F = $"F-{i}",
                G = $"G-{i}",
                Child1 = new Child1
                {
                    B = $"Child2-Child1-B-{i}",
                    C = $"Child2-Child1-C-{i}",
                }
            }
        });

    var file = new FileInfo(@"c:\temp\flat.xlsx");
    if (file.Exists)
        file.Delete();

    TestExtensions.ExportFlatExcel(
        list
        , file
        , "Test1"
        );
}

我会给你这个：

将会给你这个：

- Ernie S

---

谢谢@Ernie，让我来实现这个，然后告诉你它的进展如何。 - Muds