Export to Excel from a List with Epplus

export to Excel from a list with EPPLUS

What version of EPPlus are you using? I ask because I am surprised it does not throw an error as it does with 4.1.0 which is currently the latest. Maybe an older version is more forgiving.

But to answer you question, if you look at the signature of the final overload of LoadFromCollection that is eventually called you will see this:

public ExcelRangeBase LoadFromCollection<T>(IEnumerable<T> Collection, bool PrintHeaders, TableStyles TableStyle, BindingFlags memberFlags, MemberInfo[] Members)

Notice that Epplus is only looking at MemberInfos and not a Fields which is what you object is using. If you change Stock object to this:

class Stock
{
    public string Nif { get; set; }
    public string Proveedor { get; set; }
    public string Coodigo { get; set; }
    public string descripcion { get; set; }
    public string Catalogo { get; set; }
    public string Estadistico { get; set; }
    public decimal StockOn { get; set; }
}

You should see results.

How do i export child objects with EPPlus as Excel

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:

EPPlus Excel Export - All data values going into Column A for each record?

Answer Founrd Here

Added a ToString() override on each of my desired Models to ensure the value came through instead of just the object type:

public class INV_Models
{

    public override string ToString()
    {
        return this.model_description;
    }
}

public class INV_Manufacturers
{
    public override string ToString()
    {
        return this.manufacturer_description;
    }
}

public class INV_Locations
{
    public override string ToString()
    {
        return this.location_dept + "|" + this.location_room;
    }
}

public class INV_Vendors
{
    public override string ToString()
    {
        return this.vendor_name;
    }
}

public class INV_Types
{
    public override string ToString()
    {
        return this.type_description;
    }
}

public class INV_Statuses
{
    public override string ToString()
    {
        return this.status_description;
    }
}

ExportController:

    [HttpPost]
    public ActionResult ExportUsingEPPlus(ExportAssetsViewModel model)
    {
        ExcelPackage package = new ExcelPackage();
        var ws = package.Workbook.Worksheets.Add("TestExport");

        var exportFields = new List<string>();
        foreach (var selectedField in model.SelectedFields)
        {
            // Adds selected fields to [exportFields] List<string>
            exportFields.Add(model.ListOfExportFields.First(s => s.Key == selectedField).Value);
        }

        // Loops to insert column headings into Row 1 of Excel
        for (int i = 0; i < exportFields.Count(); i++)
        {
            ws.Cells[1, i + 1].Value = exportFields[i].ToString();
        }

        var membersToShow = typeof(INV_Assets).GetMembers().Where(p => exportFields.Contains(p.Name)).ToArray();

        ws.Cells["A2"].LoadFromCollection(_db.INV_Assets.ToList(), false, TableStyles.None, BindingFlags.Default, membersToShow);

        var membersToShow = typeof(INV_Assets).GetMembers().Where(p => exportFields.Contains(p.Name)).ToArray();

        var memoryStream = new MemoryStream();
        package.SaveAs(memoryStream);

        string fileName = "Exported-InventoryAssets-" + DateTime.Now + ".xlsx";
        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        memoryStream.Position = 0;
        return File(memoryStream, contentType, fileName);
    }

And the output Excel file:

Export DataTable to Excel with EPPlus

using (ExcelPackage pck = new ExcelPackage(newFile))
{
  ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts");
  ws.Cells["A1"].LoadFromDataTable(dataTable, true);
  pck.Save();
}

That should do the trick for you. If your fields are defined as int EPPlus will properly cast the columns into a number or float.

Export data to excel with EPPlus and WebApi

As I didn't know EPPlus, I googled it, and its Github page states it produces Open XML excel files (.xlsx). You produce the file with an extension and mimetype of the old binary excel filetype. Change the contenttype to application/vnd.openxmlformats-officedocument.spreadsheetml.sheet and the filename extension to xlsx.

EPPlus: CSV to Excel - Result all in one line

The method StringBuilder.AppendLine in CreateCsv terminates each line

with the default line terminator of the OS which is on:

• Windows \r\n
• Unix \n

EPPlus doesn't respect the default line terminator of the OS and uses \r\n as a default value.

That's why it works on Windows.

So you can set the ExcelTextFormat.EOL property to Environment.NewLine to use the default line terminator of the OS.

var format = new ExcelTextFormat
{
    DataTypes = new[]
    {
        eDataTypes.String, eDataTypes.String
    },
    Delimiter = Delimiter.First(),
    Encoding = new UTF8Encoding(),
    EOL = Environment.NewLine
};

---

Related Topics