How to edit an existing table using EPPlus

Question

I have an Excel worksheet with a table in an xlsx file. I am filling out an EPPlus worksheet. How to edit a table?

Answer 1

The problem with the answer above. It works if the number of columns and names / ordinals of these columns has not changed, but a crash occurs when changing the scheme. There are several things that need to be updated to ensure that the file meets the requirements of Excel.

• Set the attribute countin the element tableColumns:

• Synchronize column headings in a table with column elements inside an element tableColumns

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

Answer 2

, . - , xml Table.TableXml. xlsx , zip xml , XML-. Table.TableXml raw 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, , - .