使用EPPlus C#扩展表格

Question

我该如何在C#中使用EPPlus扩展现有的表格？我的程序先创建一个只有两行的表格，之后不断地添加更多行。但是在 ExcelTable 中似乎找不到任何改变表格大小的方式。是否有专门的方法来实现此功能，还是需要使用其他替代方法？

编辑：好的，我想澄清一下。我的问题是：如何将现有的ExcelTable扩展（添加更多行）？

- Jordan

---

EPPlus是用于导出到Excel的，您实际上想要什么？ - Anil Panwar

如果我理解你的问题正确，下面的答案将读取一个Excel文件到内存中并添加一行，然后覆盖该文件。 - dub stylee

Answer 1

很遗憾，没有直接的方法。不太确定为什么，但EPPlus的开发人员选择将ExcelTable.Address属性设置为只读。所以最明显的选择是创建一个新表，复制所有属性（假设你知道所有需要的属性），然后删除旧表。这并不是非常理想，因为你可能会漏掉一些内容。

但我看到另外两种方法 - 也不是特别好看但比复制/删除要好。假设有这样一张表：

选项1：你可以fork EPPlus源代码，并使所需属性变为可读/写。你可以在文件ExcelTable.cs中进行更改:

    public ExcelAddressBase Address
    {
        get
        {
            return _address;
        }
        internal set  //REMOVE internal KEYWORD
        {
            _address = value;
            SetXmlNodeString("@ref",value.Address);
            WriteAutoFilter(ShowTotal);
        }
    }

通过删除internal关键词。但是当然，您需要小心，这样做不会在途中破坏其他东西。有了这个，您可以做如下操作：

var fileInfo = new FileInfo(@"c:\temp\Expand_Table.xlsx");

using (var pck = new ExcelPackage(fileInfo))
{
    var workbook = pck.Workbook;
    var worksheet = workbook.Worksheets.First();

    //Added 11th data row assuming the table is from A1 to C11 (Header row + 10 data rows)
    worksheet.Cells["A12"].Value = 10;
    worksheet.Cells["B12"].Value = 100;
    worksheet.Cells["C12"].Value = Path.GetRandomFileName();

    var tbl = worksheet.Tables["TestTable1"];
    var oldaddy = tbl.Address;
    var newaddy = new ExcelAddressBase(oldaddy.Start.Row, oldaddy.Start.Column, oldaddy.End.Row + 1, oldaddy.End.Column);

    tbl.Address = newaddy;

    pck.Save();
}

选项2是更安全的选择，但使用字符串替换XML会更加"脏". 我们不能假设只有一个地址的引用，因为例如AutoFilter可能已经打开。这是在Excel中创建表格时默认XML的样子（请注意对地址的两个引用）：

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" id="3" name="TestTable1" displayName="TestTable1" ref="A1:C11" totalsRowShown="0">
    <autoFilter ref="A1:C11" />
    <tableColumns count="3">
        <tableColumn id="1" name="Col1" />
        <tableColumn id="2" name="Col2" />
        <tableColumn id="3" name="Col3" />
    </tableColumns>
    <tableStyleInfo name="TableStyleMedium2" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0" />
</table>

因此，我们可以像这样进行字符串替换：

var fileInfo = new FileInfo(@"c:\temp\Expand_Table.xlsx");

using (var pck = new ExcelPackage(fileInfo))
{
    var workbook = pck.Workbook;
    var worksheet = workbook.Worksheets.First();

    //Added 11th data row assuming the table is from A1 to C11 (Header row + 10 data rows)
    worksheet.Cells["A12"].Value = 10;
    worksheet.Cells["B12"].Value = 100;
    worksheet.Cells["C12"].Value = Path.GetRandomFileName();

    var tbl = worksheet.Tables["TestTable1"];
    var oldaddy = tbl.Address;
    var newaddy = new ExcelAddressBase(oldaddy.Start.Row, oldaddy.Start.Column, oldaddy.End.Row + 1, oldaddy.End.Column);

    //Edit the raw XML by searching for all references to the old address
    tbl.TableXml.InnerXml = tbl.TableXml.InnerXml.Replace(oldaddy.ToString(), newaddy.ToString());

    pck.Save();
}

- Ernie S

---

@dubstylee “Worksheet”和“ExcelTable”是两个完全不同的对象。这就是他在编辑中所说的“如何使现有的ExcelTable变大（添加更多行）”。 - Ernie S

啊，我明白了。如果我的回答能帮助到其他遇到这个问题的人，我会留下来的。这取决于 OP 的目的，可能根本不需要使用 ExcelTable。 - dub stylee

谢谢回复，我认为我最喜欢第一种方法。我有机会的时候会尝试一下。 - Jordan

我尝试了第二个答案——用新的地址范围替换xml中的旧地址范围，它完美地工作了——打开文档时没有出现任何错误。谢谢！ - kilkfoe

@Jordan 我建议你将所选答案改为另一个答案，那才是正确处理这个问题的方法。 - daniloquio

Answer 2

这个问题已经有一年了，但希望仍有人需要答案，有一个直接的解决方案，尽管由于EEPlus中的一个错误（我正在使用v4.1）而不完整。

ExcelWorksheet有一个InsertRow(Int32, Int32, Int32)方法。如果您在表格的一部分中插入行，则表格也会扩展。只需使用它的Address属性获取边界即可。只要确保在表格的第一行和最后一行之间插入。

我知道与单元格验证相关的一个缺陷：即使您使用可以从现有行复制样式的重载，验证也不会被复制。更糟糕的是，验证留在原地，而且没有随着单元格的移动而移动。我没有测试过，但我相信如果验证有一个范围而不是一个单元格作为目标，那么当您向该范围插入行时，该范围也会扩展。

如果您希望在工作表中扩展表格上的数据，则无法帮助您-但您仍然可以移动行。当您有许多并排的表格时，这也不适用（这是一场噩梦）。

- ZorgoZ

---

谢谢你的回答。然而，如果图表系列绑定到整个列，则会忽略插入的行。即使手动插入，图表也会更新。也许你知道这种情况的解决方法？ - Brains

Answer 3

我遇到了同样的问题，我发现了一种非常快速的方法，可以将SQL DataTable转储到Excel表格中。 仅用04.2秒处理了18004行数据 我的模板包含公式，当扩展表格时，这些公式会继承到新行。

Template.xlsx

Report_with_Epplus.xlsx

public void xlsEpplus()
    {
        try
        {
            TimeSpan stop;
            TimeSpan start = new TimeSpan(DateTime.Now.Ticks);

            Database DB = new Database();
            DB.msConect();

            string sql = "SELECT * FROM table_test";
            DataTable dt_tab = DB.xSqlMs(sql);

            string Template = "D:\\Plantillas\\Template.xlsx";
            string filePath = "D:\\Plantillas\\Report_with_Epplus.xlsx";
            File.Copy(Template, filePath, true);


            ExcelPackage pck = new ExcelPackage();

            pck.Load(new FileStream(Template, FileMode.OpenOrCreate));

            ExcelWorksheet ws = pck.Workbook.Worksheets.First();

            var a = ws.Cells["A1"].Value;


            int i = 2;
            ws.InsertRow(i, dt_tab.Rows.Count);
            ws.Cells["A2"].LoadFromDataTable(dt_tab, true);

            pck.SaveAs(new FileInfo(filePath));

            stop = new TimeSpan(DateTime.Now.Ticks);
            Console.WriteLine("xlsEpplus_While:" + stop.Subtract(start));
        }
        catch (Exception ex)
        {
            //
            Console.WriteLine("ERROR: " + ex.Message);
        }
    }

- Jean Paul Beard