Unable to delete table using EPPlus

Question

I'm having an issue with a very basic thing. Deleting a table using EPPlus. The problem that I'm facing is that from the code point of view (I've debugged the code, and in the Worksheet.Tables collection, the tabled that I called Delete on is gone. I proceed to save the excel and when I try to open it, Excel tells me that "they" found a problem with it, and if I want them to recover as much as possible. If I select Yes (because No closes the file) the table that I just delete through code is back again.

If I set the ClearRange property in the Delete() method to true, the table is still in the spreadsheet, but this time the header columns are gone, and instead Excel recreates my table with some default names (Column1, Column2, etc, depending on the number of columns the original table had).

I've tried to manipulate the TableXml property of the table, and it's still not working. Other than that, I've just read the Internet for about 3 hours with no apparent solution to my problem.

So, if anyone is wondering, the code for this is:

using (var package = new ExcelPackage(fileToWriteTo, templateToStartWith))
    {
            var workbook = package.Workbook;
            var worksheet = workbook.Worksheets[WORKSHEET_NAME];
            worksheet.Tables.Delete("dynamicTable", true);
            //worksheet.Tables.Delete("dynamicTable");
            package.Save();
    }

As you can see, a very basic operation that kinda fails. If anyone knows of a solution to remove a table from a spreadsheet with EPPlus, please share. Would be much appreciated.

Answer 1

I have trouble with the Delete method as well. I can't even compile when I reference it half the time. Strange.

What if you delete the reference to the table via xml. If it is the only table in the ws or you know the exact order it is pretty easy to do:

[TestMethod]
public void Table_Delete_Test()
{
    //http://stackoverflow.com/questions/36359047/unable-to-delete-table-using-epplus
    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.AddRange(new[]
    {
        new DataColumn("Col1", typeof (int)), new DataColumn("Col2", typeof (int)), new DataColumn("Col3", typeof (object))
    });

    for (var i = 0; i < 10; i++)
    {
        var row = datatable.NewRow();
        row[0] = i; row[1] = i * 10; row[2] = Path.GetRandomFileName();
        datatable.Rows.Add(row);
    }

    var fi = new FileInfo(@"c:\temp\Table_Delete_Test.xlsx");
    if (fi.Exists)
        fi.Delete();

    //Create a worksheet with tables to later open and look for tables (note that EPPlus does not create the table objects until save)
    using (var pck = new ExcelPackage(fi))
    {
        var workbook = pck.Workbook;

        var worksheet = workbook.Worksheets.Add("source");
        worksheet.Cells["A1"].LoadFromDataTable(datatable, true);
        worksheet.Cells["A12"].LoadFromDataTable(datatable, true);

        var table1 = worksheet.Tables.Add(worksheet.Cells["A1:C11"], "TableTest1");
        var table2 = worksheet.Tables.Add(worksheet.Cells["A12:C22"], "TableTest2");

        pck.Save();
    }

    //Open the test workbook and delete the second table
    using (var pck = new ExcelPackage(fi))
    {
        var workbook = pck.Workbook;
        var worksheet = workbook.Worksheets.First();

        var wsXml = worksheet.WorksheetXml;
        var nsm = new XmlNamespaceManager(wsXml.NameTable);
        var nsuri = wsXml.DocumentElement.NamespaceURI;
        nsm.AddNamespace("m", nsuri);

        //Remove reference to the second table which should be last
        var tablePartsNode = wsXml.SelectSingleNode("m:worksheet/m:tableParts", nsm);
        tablePartsNode.RemoveChild(tablePartsNode.LastChild);

        pck.Save();
    }
}

So it goes from this:

to this:

But if you need to do it by Table.Name that is not so easy. You would have to get the RelationshipID which Epplus has marked as internal. The RID is based on the zip package so to get that you either fork and modify Epplus to expose it or you open the XLSX as a ZipArchive and get the RID from that - kind of a pain.