Inserting and Deleting Rows and Columns of Excel file

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 for Python via .NET provides a very simplest set of classes and methods, discussed below.

Manage Rows and Columns

Aspose.Cells for Python via .NET provides a class Workbook, that represents a Microsoft Excel file. The Workbook class contains a worksheets collection 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 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, and if rows or columns are removed, the content is shifted up or the left.

Insert Rows and Columns

How to Insert a Row

Insert a row into the worksheet at any location by calling the insert_row method of the cells collection. The insert_row method takes the index of the row where the new row will be inserted.

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Creating a file stream containing the Excel file to be opened
fstream = open(dataDir + "book1.xls", "rb")
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(fstream)
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Inserting a row into the worksheet at 3rd position
worksheet.cells.insert_row(2)
#  Saving the modified Excel file
workbook.save(dataDir + "output.out.xls")
#  Closing the file stream to free all resources
fstream.close()

How to Insert Multiple Rows

To insert multiple rows into a worksheet, call the insert_rows method of the cells collection. The insert_rows 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.

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Creating a file stream containing the Excel file to be opened
fstream = open(dataDir + "book1.xls", "rb")
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(fstream)
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Inserting 10 rows into the worksheet starting from 3rd row
worksheet.cells.insert_rows(2, 10)
#  Saving the modified Excel file
workbook.save(dataDir + "output.out.xls")
#  Closing the file stream to free all resources
fstream.close()

How to Insert a Row with Formatting

To insert a row with formatting options, use the insert_rows overload that takes InsertOptions as a parameter. Set the copy_format_type 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.

from aspose.cells import CopyFormatType, InsertOptions, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Creating a file stream containing the Excel file to be opened
fstream = open(dataDir + "book1.xls", "rb")
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(fstream)
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Setting Formatting options
insertOptions = InsertOptions()
insertOptions.copy_format_type = CopyFormatType.SAME_AS_ABOVE
#  Inserting a row into the worksheet at 3rd position
worksheet.cells.insert_rows(2, 1, insertOptions)
#  Saving the modified Excel file
workbook.save(dataDir + "InsertingARowWithFormatting.out.xls")
#  Closing the file stream to free all resources
fstream.close()

How to Insert a Column

Developers can also insert a column into the worksheet at any location by calling the insert_column method of the cells collection. The insert_column method takes the index of the column where the new column will be inserted.

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Creating a file stream containing the Excel file to be opened
fstream = open(dataDir + "book1.xls", "rb")
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(fstream)
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Inserting a column into the worksheet at 2nd position
worksheet.cells.insert_column(1)
#  Saving the modified Excel file
workbook.save(dataDir + "output.out.xls")
#  Closing the file stream to free all resources
fstream.close()

Delete Rows and Columns

How to Delete Multiple Rows

To delete multiple rows from a worksheet, call the delete_rows method of the cells collection. The delete_rows 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.

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Creating a file stream containing the Excel file to be opened
fstream = open(dataDir + "Book1.xlsx", "wb")
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(fstream)
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Deleting 10 rows from the worksheet starting from 3rd row
worksheet.cells.delete_rows(2, 10)
#  Saving the modified Excel file
workbook.save(dataDir + "output.xlsx")
#  Closing the file stream to free all resources
fstream.close()

How to Delete a Column

To delete a column from the worksheet at any location, call the delete_column method of the Cells collection. The delete_column method takes the index of the column to delete.

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Creating a file stream containing the Excel file to be opened
fstream = open(dataDir + "Book1.xlsx", "rb")
#  Instantiating a Workbook object
#  Opening the Excel file through the file stream
workbook = Workbook(fstream)
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Deleting a column from the worksheet at 5th position
worksheet.cells.delete_column(4)
#  Saving the modified Excel file
workbook.save(dataDir + "output.xlsx")
#  Closing the file stream to free all resources
fstream.close()