How do I edit an existing table with EPPlus

Question

I have an Excel worksheet with a table in a template xlsx file. I am populating the worksheet with EPPlus. How do I edit the table?

Answer 1

This is a clarification of a workaround methodology described on codeplex. The only way to do this is to get the range of the table body, append data to it and edit the table xml manually via the Table.TableXml property. In the xlsx file, which is just a zip file of xml files, each table is a separate xml document. Table.TableXml represents that raw xml as an XmlDocument.

Assuming you have an empty table in your template, with a header and no body, the following will work.

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;

In my limited attempts to do this, this created an xlsx file that excel opened correctly without any warnings or errors.

Answer 2

There is a problem with the answer above. It works if the number of columns and names/ordinals of those columns have not changed, but will fail if there is a change to schema. There are a few things that need to be updated to ensure that the file is Excel compliant.

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