如何使用EPPlus编辑现有表格

Question

我有一个带表格的Excel模板xlsx文件，我正在使用EPPlus填充工作表。如何编辑表格？

- Justin Dearing

Answer 1

这是对Codeplex上描述的解决方法的澄清。 唯一的方法是获取表格主体的范围，将数据附加到其中，并通过Table.TableXml属性手动编辑表格xml。在xlsx文件中，它只是一个由xml文件组成的压缩文件，每个表格都是单独的xml文档。Table.TableXml表示该原始xml作为XmlDocument。

假设您的模板中有一个空表格，带有标题但不带正文，则以下内容将起作用。

var table = ws.Tables["MyTable"];
var start = table.Address.Start;
var body = ws.Cells[start.Row + 1, start.Column];

var outRange = body.LoadFromDataTable(myDt, false);
// or however you wish to populate the table

var newRange =
    string.Format("{0}:{1}", start.Address, outRange.End.Address);

var tableElement = table.TableXml.DocumentElement;
tableElement.Attributes["ref"].Value = newRange;
tableElement["autoFilter"].Attributes["ref"].Value = newRange;

在我的有限尝试中，这样创建的xlsx文件可以被Excel正确地打开，没有任何警告或错误。

- Justin Dearing

---

很高兴知道这个方法，对我有用，所以+1。两个注意事项：（1）确保您的DataTable（或任何您正在使用的LoadFrom*源）加载的列数正好与现有表格相同；否则，Excel会看到损坏。您可以依赖于table.Address.End而不是outRange.End来避免这种情况。（2）ws.Dimension不反映此方法对表格XML的更新。（我也不知道有没有直接的API方式来“刷新”工作表或包以反映它。） - William

我已经使用了这段代码，它运行良好。但是，有人知道是否可以自动将公式应用于已在加载的Excel文件中设置的新范围吗？ - Sawd

Answer 2

上面的答案存在问题。如果列的数量和这些列的名称/序号没有改变，它可以工作，但如果模式发生变化，它将失败。有几件事情需要更新，以确保文件符合Excel要求。

1. Set the count attribute on the tableColumns element:

2. Syncronise the column headers in your table with the column elements inside the tableColumns element

   var table = sheet.Tables.First();
   var tableElement = table.TableXml.DocumentElement;
   tableElement.Attributes["ref"].Value = rng.Address;
   var columnNode = tableElement["tableColumns"];
   columnNode.Attributes["count"].Value = rng.End.Column.ToString();
   for (int i = 0; i < dataTable.Columns.Count; i++)
   {
       if(columnNode.ChildNodes.Count == i)
       {
           var clonedNode = columnNode.ChildNodes[i - 1].CloneNode(true);
           clonedNode.Attributes["id"].Value = (i + 1).ToString();
           clonedNode.Attributes["name"].Value = dataTable.Columns[i].ColumnName;
           columnNode.AppendChild(clonedNode);
       }
       else
       {
           columnNode.ChildNodes[i].Attributes["name"].Value = dataTable.Columns[i].ColumnName;
       }
       if(i == reportInstance.Data.Columns.Count - 1)
       {
           while(columnNode.ChildNodes.Count > reportInstance.Data.Columns.Count)
           {
                columnNode.RemoveChild(columnNode.ChildNodes[i + 1]);
           }
       }
   }
   
   if (tableElement["autoFilter"] != null)
   {
       tableElement["autoFilter"].Attributes["ref"].Value = rng.Address;
   }
   

- David Colwell

---

很高兴我没有自己接受我的答案。下次使用EPPlus时，我会再次审查并标记为正确。 - Justin Dearing

@JustinDearing，我添加了一些（未经测试的）代码来删除额外的列。我发布的代码已经通过将较小的表格调整大小以具有更多列进行了测试，但是没有通过将较大的表格调整大小以具有较少列进行测试。需要测试，但应该可以工作。我使用这种方法的原因是，如果您使用AddNode创建节点，则它们会获得XMLNS引用并失败Excel模式验证。 - David Colwell

报告实例是什么？ - maximusg