Insert or Delete Rows and Columns in Excel using C#

Insert or Delete Rows and Columns in Excel using C#

As a C# developer, you can easily insert or delete the rows and columns in the Excel worksheets programmatically. In this article, you will learn 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

For inserting or deleting the rows and columns in an Excel sheet, I will be using Aspose.Cells for .NET API. It is a well-known spreadsheet manipulation API that lets you create and process Excel files from within your .NET applications. The API allows you to insert single or multiple rows and columns in the Excel files. It also enables you to delete the rows and columns 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 the steps mentioned below:

  • Create an instance of the Workbook class with the input file path.
  • Create an instance of the Worksheet class.
  • Access the worksheet from Worksheets collection by its index.
  • Insert rows by calling the InsertRows() method and pass the row index to start from and total rows to insert.
  • 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#.

Insert Rows in Excel Worksheets using C#
Insert Multiple Rows in Excel Worksheets using C#.

Similarly, you can insert a single row in an Excel sheet using the following code example.

Insert a single Row in Excel Worksheets using C#
Insert a single Row in Excel Worksheets using C#

The Workbook class of the API represents an Excel workbook. You can get a collection of all the available worksheets within a workbook using the Worksheets property of this class. Any single worksheet of an Excel workbook can be accessed from the Worksheets’ collection by using its index. The Worksheet class represents a single worksheet. It exposes several properties and methods to perform various operations on the worksheet. The Cells property of this class represents a collection of cells available in the worksheet. The Cells class represents an individual cell within the worksheet.

The InsertRow() method of the Cells class allows inserting a single row at the specified index. The Cells class also provides the InsertRows() method to insert more than one row at the same time. It takes a row index from where to start inserting rows and the total number of new rows to insert as input parameters.

The Save() method of the Workbook class saves the workbook at the given file path specified as an input parameter.

Insert Rows with Formatting in Excel Worksheets using C#

You can insert rows with formatting in Excel sheets programmatically by following the steps mentioned below:

  • Create an instance of the Workbook class with the input file path.
  • Create an instance of the Worksheet class.
  • Access the worksheet from Worksheets collection by its index.
  • Create an instance of the InsertOptions class.
  • Set the CopyFormatType property
  • Call the InsertRows() method with the row index, total rows to insert and pass the InsertOptions.
  • 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 class of the API represents options while inserting the rows or columns. The CopyFormatType property of this class represents the type of copying format when inserting rows and supports the following types:

  • SameAsAbove — allows copying formats same as above row.
  • SameAsBelow — allows copying formats same as below row.
  • Clear — allows clearing formatting.

Delete Rows from Excel Worksheets using C#

You can delete rows from Excel sheets programmatically by following the steps mentioned below:

  • Create an instance of the Workbook class with the input file path.
  • Create an instance of the Worksheet class.
  • Access the worksheet from Worksheets collection by its index.
  • Delete the rows by calling the DeleteRows() method and pass the row index and total rows to delete.
  • 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() method of the Cells class allows deleting a single row at the specified index. Similarly, the DeleteRows() method allows deleting more than one row. It takes a row index from where to start deleting rows and the total number of rows to delete as input parameters.

Insert Columns in Excel Worksheets using C#

You can insert columns in Excel sheets programmatically by following the steps mentioned below:

  • Create an instance of the Workbook class with the input file path.
  • Create an instance of the Worksheet class.
  • Access the worksheet from Worksheets collection by its index.
  • Insert a column by calling the InsertColumn() method and pass the column index where to insert a new column.
  • 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#.

Insert a single Column in Excel Worksheets using C#
Insert a single Column in Excel Worksheets using C#.

Similarly, you can insert multiple columns in an Excel sheet using the code sample given below:

Insert Multiple Columns in Excel Worksheets using C#.
Insert Multiple Columns in Excel Worksheets using C#.

For inserting columns in Excel worksheets, the Cells class provides the InsertColumns() method to insert multiple columns in a worksheet. It takes a column index from where to start inserting columns and the total number of new columns to insert as input parameters. The Cells class also provides the InsertColumn() method to insert a single column at the specified index.

Delete Columns from Excel Worksheets using C#

You can delete columns from Excel sheets programmatically by following the steps mentioned below:

  • Create an instance of the Workbook class with the input file path.
  • Create an instance of the Worksheet class.
  • Access the worksheet from Worksheets collection by its index.
  • Delete a column by calling the DeleteColumn() method and pass the column index to delete.
  • 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#.

Similarly, you can delete multiple columns from an Excel sheet using the following code example.

The DeleteColumns() method allows deleting multiple columns at once. It takes three parameters, a column index from where to start deleting the columns, the total number of columns to delete as input parameters, and a true or false value to indicate whether to update the references in other worksheets. Similarly, the DeleteColumn() method of the Cells class allows deleting a single column at the specified index.

Get a Free License

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

Conclusion

In this article, you have learned how to insert rows or columns in Excel files using C#. You have also learned how to delete rows and columns from Excel files programmatically. Moreover, you have learned how to insert multiple rows or columns in an Excel sheet. Furthermore, you have learned how to delete multiple rows or columns from Excel files using C#. You can learn more about Aspose.Cells for .NET API using the documentation. In case of any ambiguity, please feel free to contact us on the forum.

See Also