Inserting and Deleting Rows and Columns

Introduction

Whether creating a new worksheet from scratch or working on an existing worksheet, we may need to add extra rows or columns to accommodate more data. Inversely, we may also need to delete rows or columns from specified positions in the worksheet.

To fulfill these requirements, Aspose.Cells provides a very simplest set of classes and methods, discussed below.

How to Manage Rows/Columns

Aspose.Cells provides a Workbook class that represents a Microsoft Excel file. The Workbook class contains a WorksheetCollection that allows access to each worksheet in an Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a Cells collection that represents all cells in the worksheet.

The Cells collection provides several methods for managing rows and columns in a worksheet. Some of these are discussed below.

When rows or columns are added, the content in the worksheet is shifted down or to the right, but if rows or columns are removed, the content is shifted up or the left.

How to Insert a Row

Insert a row into at any location by calling the insertRows method of the Cells collection. The insertRows  method takes the index of the row where the new row will be inserted as the first argument, and the number of rows to be inserted as the second argument.

How to Insert Multiple Rows

To insert multiple rows into the worksheet, call the insertRows method of the Cells collection. The insertRows method takes two parameters:

• Row index: the index of the row from where the new rows will be inserted.
• Number of rows: the total number of rows that need to be inserted.

How to Insert a Row with Formatting

To insert a row with formatting options, use the insertRows overload that takes InsertOptions as a parameter. Set the CopyFormatType property of InsertOptions class with CopyFormatType Enumeration. The CopyFormatType Enumeration has three members as listed below.

• SAME_AS_ABOVE: Formats the row same as the above row.
• SAME_AS_BELOW:  Formats the row same as below row.
• CLEAR: Clears the formatting.

How to Delete a Row

To delete a row at any location, call the deleteRows method of the Cells collection. The deleteRows method takes two parameters:

• Row index: the index of the row from where the rows will be deleted.
• Number of rows: the total number of rows that need to be deleted.

How to Delete Multiple Rows

To delete multiple rows from a worksheet, call the deleteRows method of the Cells collection. The deleteRows method takes two parameters:

• Row index: the index of the row from where the rows will be deleted.
• Number of rows: the total number of rows that need to be deleted.

How to Insert one or Multiple Columns

Developers can also insert a column into the worksheet at any location by calling the insertColumns method of the Cells collection. The insertColumns method takes two parameters:

• Column index, the index of the column from where the column will be inserted
• Number of columns, the total number of columns that need to be inserted

How to Delete a Column

To delete a column from the worksheet at any location, call the deleteColumns method of the Cells collection. The deleteColumns method takes the following parameters:

• Column index: the index of the column from where the column will be deleted.
• Number of columns: the total number of columns that need to be deleted.
• Update Reference: Boolean parameter to indicate whether to update references in other worksheets.