How do i export child objects with EPPlus as Excel

Question

I am using EPPlus to help me export data as excel. I am still learning to export data properly but somehow am stuck at a point where i am not able to export an object with child objects all flatted out.

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

ChildObject
    public string C;
    public string D;

so i want my exported excel to look like

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

This is how my current implementation looks like

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();
        }
    }

I tried using Microsoft generics using expando object but EPPlus wont work with generics, is there a way where in i can export objects with child objects ?

also: is there any other library that i could use ?

Answer 1

There is no native function that could do that. Hard to come up with something generic as it would require a great deal of assumption. What property type should be automatically exported vs what should be treated a child node and have ITS properties exported or expanded. But if you come up with that it is a basic tree traversal from there.

Below is something I adapted from a similar task. Here, I assume that anything that is a either a string or a data type without properties is considered an value type for exporting (int, double, etc.). But it is very easy to tweak as needed. I threw this together so it may not be fully optimized:

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();
    }
}

So say you have these as a structure:

#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

And this as a test:

[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"
        );
}

Will give you this: