Insert or Delete Rows and Columns in Excel using C#

As a C# developer, you can quickly insert or delete rows and columns in Excel (XLSX, XLS) worksheets programmatically. This article shows how to insert or delete rows and columns in an Excel sheet using C#.

The following topics are discussed/covered in this article:

• C# API to Insert or Delete Rows and Columns
• Insert Rows in Excel Worksheets using C#
• Insert Rows with Formatting in Excel Worksheets using C#
• Delete Rows from Excel Worksheets using C#
• Insert Columns in Excel Worksheets using C#
• Delete Columns from Excel Worksheets using C#

C# API to Insert or Delete Rows and Columns

To insert or delete rows and columns in an Excel sheet, I use Aspose.Cells for .NET API. This popular spreadsheet library lets you create and manipulate Excel files in .NET applications. It supports inserting single or multiple rows and columns, as well as deleting them programmatically.

You can either download the DLL of the API or install it using NuGet.

Install-Package Aspose.Cells

Insert Rows in Excel Worksheets using C#

You can insert rows in Excel sheets programmatically by following these steps:

1. Create a Workbook instance with the input file path.
2. Create a Worksheet instance.
3. Access the worksheet from the Worksheets collection by its index.
4. Call the InsertRows() method, passing the start row index and the number of rows to insert.
5. Call the Save() method with the output file path.

The following code sample shows how to insert multiple rows in an Excel sheet using C#.

You can also insert a single row with this code example.

The Workbook class represents an Excel workbook. Use its Worksheets property to get all worksheets, then access a sheet by index. The Worksheet class represents a single sheet and provides many properties and methods. Its Cells property gives access to the cell collection, and the Cells class represents individual cells.

The InsertRow() method of the Cells class inserts a single row at a specified index. The InsertRows() method inserts multiple rows at once, taking the start index and the number of rows as parameters. The Save() method writes the workbook to the given file path.

Insert Rows with Formatting in Excel Worksheets using C#

To insert rows with formatting, follow these steps:

1. Create a Workbook instance with the input file path.
2. Create a Worksheet instance.
3. Access the worksheet from the Worksheets collection by its index.
4. Create an [InsertOptions][22] instance.
5. Set the [CopyFormatType][23] property.
6. Call the [InsertRows()][24] method with the start row index, number of rows, and the InsertOptions object.
7. Call the Save() method with the output file path.

The following code sample shows how to insert rows with formatting in an Excel sheet using C#.

The [InsertOptions][22] class defines options for inserting rows or columns. Its [CopyFormatType][23] property controls how formatting is copied and supports:

• SameAsAbove — copy the format from the row above.
• SameAsBelow — copy the format from the row below.
• Clear — remove formatting.

Delete Rows from Excel Worksheets using C#

To delete rows, follow these steps:

1. Create a Workbook instance with the input file path.
2. Create a Worksheet instance.
3. Access the worksheet from the Worksheets collection by its index.
4. Call the [DeleteRows()][25] method, passing the start row index and the number of rows to delete.
5. Call the Save() method with the output file path.

The following code sample shows how to delete rows from an Excel sheet using C#.

The [DeleteRow()][26] method of the Cells class deletes a single row at a given index. The [DeleteRows()][25] method deletes multiple rows, taking the start index and the number of rows as parameters.

Insert Columns in Excel Worksheets using C#

To insert columns, follow these steps:

1. Create a Workbook instance with the input file path.
2. Create a Worksheet instance.
3. Access the worksheet from the Worksheets collection by its index.
4. Call the [InsertColumn()][27] method, passing the column index where the new column should be inserted.
5. Call the Save() method with the output file path.

The following code sample shows how to insert a column in an Excel sheet using C#.

To insert multiple columns, use this code example:

The Cells class provides the [InsertColumns()][30] method to add several columns at once. It takes the start column index and the number of new columns as parameters. The [InsertColumn()][27] method inserts a single column at the specified index.

Delete Columns from Excel Worksheets using C#

To delete columns, follow these steps:

1. Create a Workbook instance with the input file path.
2. Create a Worksheet instance.
3. Access the worksheet from the Worksheets collection by its index.
4. Call the [DeleteColumn()][31] method, passing the column index to delete.
5. Call the Save() method with the output file path.

The following code sample shows how to delete a column from an Excel sheet using C#.

To delete multiple columns, use this example:

The [DeleteColumns()][32] method deletes several columns at once. It requires the start column index, the number of columns to delete, and a Boolean indicating whether to update references in other worksheets. The [DeleteColumn()][31] method removes a single column at the given index.

Get a Free License

You can try the API without evaluation limitations by requesting [a free temporary license][33].

Conclusion

In this article, you have learned how to insert rows or columns in Excel files using C#. You also learned how to delete rows and columns programmatically. Additionally, you saw how to insert multiple rows or columns and how to delete multiple rows or columns. Explore more about Aspose.Cells for .NET API in the [documentation][34]. For questions, visit the [forum][35].

See Also

• [Hide and Show Rows or Columns in Excel Worksheet using C#][36]
• [Delete Blank Rows and Columns in Excel using C#][37]