如何使用EPPlus填充ExcelTable

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

我正在修改现有的excel工作表。确切地说，我希望向工作表中存在的表(使用格式作为表创建)添加一些行。我试着

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

任何人都知道我如何向表中添加行!谢谢

1 个解决方案

#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.

在我们的公司，我们使用如下的东西。基本的想法是，对于您想要输出的每条记录，您都要创建一个数据对象数组以加载到excel中，然后调用loadfromarray。

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