How to remove ListObjects in .NET Excel Library

16 Aug 20263 minutes to read

ListObjects represent Excel tables in a worksheet. You can use the ListObjects collection to access and manipulate tables in your worksheet. To remove all ListObjects, you need to iterate through the collection in reverse order and remove each one.

The following code example illustrates how to remove all ListObjects from a worksheet.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputTemplate.xlsx"));
    IWorksheet worksheet = workbook.Worksheets[0];

    // Remove all ListObjects from the sheet
    // Iterate in reverse order to avoid index shifting issues
    for(int i = worksheet.ListObjects.Count - 1; i >= 0; i--)
    {
        IListObject listObject = worksheet.ListObjects[i];
        worksheet.ListObjects.Remove(listObject);
    }

    workbook.SaveAs("Output.xlsx");
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];

    // Remove all ListObjects from the sheet
    // Iterate in reverse order to avoid index shifting issues
    for(int i = worksheet.ListObjects.Count - 1; i >= 0; i--)
    {
        IListObject listObject = worksheet.ListObjects[i];
        worksheet.ListObjects.Remove(listObject);
    }

    workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    ' Remove all ListObjects from the sheet
    ' Iterate in reverse order to avoid index shifting issues
    For i = worksheet.ListObjects.Count - 1 To 0 Step -1
        Dim listObject As IListObject = worksheet.ListObjects(i)
        worksheet.ListObjects.Remove(listObject)
    Next

    workbook.SaveAs("Output.xlsx")
End Using

A complete working example in C# is present on this GitHub page.