How to edit an existing table using EPPlus

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

+4
source share
2 answers

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;
    }
    
+2
source

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

+8

Source: https://habr.com/ru/post/1538145/


All Articles