How do I populate an ExcelTable using EPPlus

Question

I am trying to modify exisiting excel worksheet. Precisely I'd like to add a few rows to a table that exists in the worksheet (created using format as a table). I tried

var table = sheet.Tables["PositionsTable"];

but the 'table' thus created is only a meta-data of the actual table, and I can not add rows to it. If I try

sheet.Cells[table.Address.Address.ToString()].LoadFromCollection(positions);

Then I don't get the formatting of the table.

Anyone knows how I add rows to the table! Thanks

Answer 1

We use something like the following at our company. Basically the idea is that for each record you want to output you create an object array of the data to load into excel and then call LoadFromArrays.

        using (var excelPkg = new ExcelPackage())
        {
            var name = "Sheet1";
            //You will probably pass the columns to output into this function
            var headerArray = new string[] { "Column1", "Column2" };
            var data = positions
                .Select(i => headerArray.Select(h => GetValue(i, h)).ToArray());
            var ws = excelPkg.Workbook.Worksheets.Add(name);
            ws.Cells["A1"].LoadFromArrays(
                ((object[])headerArray).ToSingleItemEnumerable().Union(data));
            ws.Row(1).Style.Font.Bold = true; //set header to bold
            excelPkg.SaveAs(stream, "password");
        }

    private static object GetValue(Position item, string field)
    {
        //Your logic goes here
        return null;
    }

    public static IEnumerable<T> ToSingleItemEnumerable<T>(this T o)
    {
        yield return o;
    }