Delete Excel table (ListObject) rows without affecting other columns using XlsIO

3 mins read

Syncfusion® Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. It also, converts Excel documents to PDF files. Using this library, you can perform row-column manipulations in Excel tables (ListObjects) using C# and VB.NET.

While working with ListObjects in Excel, you might need to delete one or more rows in a table but not the columns next to the table. This article explains how to delete rows of ListObjects in Excel file without affecting other columns in the same rows with an example.

Steps to delete ListObject rows in Excel, programmatically:

Step 1: Create a new C# console application project.

Create C# Console Application

Create a new C# console application

Step 2: Install the Syncfusion.XlsIO.WinForms NuGet package as a reference to your .NET Framework applications from the NuGet.org.

Install NuGet Package

Install NuGet package

Step 3: Include the following namespaces in the Program.cs file.

using Syncfusion.XlsIO;
using Syncfusion.Drawing;
using System.IO;
using System.Reflection;

VB.NET

Imports Syncfusion.XlsIO
Imports Syncfusion.Drawing
Imports System.IO
Imports System.Reflection

Step 4: Add the following code snippet to delete a row in ListObject using C# and VB.NET.

// Create an instance of ExcelEngine
using (ExcelEngine excelEngine = new ExcelEngine())
{
    // Initialize application
    IApplication application = excelEngine.Excel;
 
    // Open existing workbook with sample table
    Assembly assembly = typeof(Program).GetTypeInfo().Assembly;
    Stream fileStream = assembly.GetManifestResourceStream("XlsIOSample.table.xlsx");
    IWorkbook workbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic);
 
    // Access worksheet and a listobject in it
    IWorksheet worksheet = workbook.Worksheets[0];
    IListObject table = worksheet.ListObjects[0];
    table.Name = "SalesTable";
 
    // Delete a listobject row, with parameters as listobject and row number to be deleted
    Program className = new Program();
    className.DeleteListRow(table, 4);
 
    // Saves the modified workbook
    workbook.SaveAs("Output.xlsx");
}

VB.NET

'Create an instance of ExcelEngine
Using excelEngine As ExcelEngine = New ExcelEngine()
 
    'Initialize application
    Dim application As IApplication = excelEngine.Excel
 
    'Open existing workbook with data entered
    Dim assembly As Assembly = GetType(Module1).GetTypeInfo.Assembly
    Dim fileStream As Stream = assembly.GetManifestResourceStream("XlsIOSample_VB.table.xlsx")
    Dim workbook As IWorkbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic)
 
    'Access worksheet and a listobject in it
    Dim worksheet As IWorksheet = workbook.Worksheets(0)
    Dim table As IListObject = worksheet.ListObjects(0)
    table.Name = "SalesTable"
 
    'Delete a listobject row, with parameters as listobject and row number to be deleted
    DeleteListRow(table, 4)
 
    'Saves the modified workbook
    workbook.SaveAs("Output.xlsx")
 
End Using

The following DeleteListRow(IListObject, int) method can be used to delete a table row without affecting the columns outside the table range, by specifying the (worksheet) row number.

private void DeleteListRow(IListObject table, int rowNumber)
{
    IRange tableRange = table.Location;
    IWorksheet worksheet = table.Worksheet;
    int firstTableColumn = tableRange.Column;
    int lastTableColumn = tableRange.LastColumn;
    int firstTableRow = tableRange.Row;
    int lastTableRow = tableRange.LastRow;
 
    table.Worksheet[rowNumber, firstTableColumn, rowNumber, lastTableColumn].Clear(ExcelClearOptions.ClearContent);
    if (rowNumber < tableRange.LastRow)
    {
        for (int i = rowNumber + 1; i <= lastTableRow; i++)
        {
            worksheet.Range[i, firstTableColumn, i, lastTableColumn].MoveTo(worksheet.Range[i - 1, firstTableColumn, i - 1, lastTableColumn]);
        }
    }
    table.Location = table.Location[firstTableRow, firstTableColumn, lastTableRow - 1, lastTableColumn];
}

VB.NET

Private Sub DeleteListRow(ByVal table As IListObject, ByVal rowNumber As Integer)
    Dim tableRange As IRange = table.Location
    Dim worksheet As IWorksheet = table.Worksheet
    Dim firstTableColumn As Integer = tableRange.Column
    Dim lastTableColumn As Integer = tableRange.LastColumn
    Dim firstTableRow As Integer = tableRange.Row
    Dim lastTableRow As Integer = tableRange.LastRow
 
    table.Worksheet(rowNumber, firstTableColumn, rowNumber, lastTableColumn).Clear(ExcelClearOptions.ClearContent)
    If (rowNumber < tableRange.LastRow) Then
        For i As Integer = rowNumber + 1 To lastTableRow
            worksheet.Range(i, firstTableColumn, i, lastTableColumn).MoveTo(worksheet.Range(i - 1, firstTableColumn, i - 1, lastTableColumn))
        Next
        table.Location = table.Location(firstTableRow, firstTableColumn, lastTableRow - 1, lastTableColumn)
    End If
End Sub

A complete working example to delete ListObject rows in Excel along with input file containing a sample table can be downloaded from DeleteListObjectRows.zip.

By executing the program, you will get the output Excel file as shown below.

Output after deleting a ListObject (table) row without affecting other columns

Output Excel document

Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheet or workbook, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to worksheet using Template Markers, and most importantly PDF and Image conversions with code examples.

Refer here to explore the rich set of Syncfusion® Excel (XlsIO) library features.

An online sample link to generate Excel file.

Delete Excel table (ListObject) rows without affecting other columns using XlsIO

Steps to delete ListObject rows in Excel, programmatically:

See Also:

Access denied