When working with spreadsheets, you may often need to insert or delete rows and columns from a worksheet. Therefore, this article shows you how to programmatically handle rows and columns in a worksheet. In particular, you’ll learn how to use Python to insert or delete single or multiple rows and columns in an Excel worksheet.

  • Use Python to insert rows into a worksheet
  • Insert columns into an Excel worksheet using Python
  • Delete rows in Excel worksheets using Python
  • Delete columns in Excel worksheets using Python

To insert or delete rows and columns in an Excel worksheet, we will use Aspose.Cells for Python via Java, a powerful spreadsheet processing API that provides a variety of features for Excel automation. You can download Aspose.Cells for Python via Java to use.

Insert rows into an Excel worksheet using Python

Here are the steps to insert rows in an Excel worksheet using Python.

  1. First, use the Workbook class to load Excel files.
  2. Use the workbook.getWorksheets ().get (index) method to access the required worksheet through the index.
  3. InsertRows using the worksheet.getcells ().insertrows (rowIndex, totalRows) method, where the first argument is the rowIndex and the second argument is the number of rows to insert.
  4. Finally, use the workbook.save (string) method to save the updated file.

The following code example demonstrates how to use Python to insert rows into an Excel worksheet.

# Instantiate a Workbook object by excel file path workbook = self.Workbook("Book1.xls") # Access the first worksheet in  the Excel file worksheet = workbook.getWorksheets().get(0) # Insert a row into the worksheet at 3rd position Worksheet.getcells ().insertrows (2,1) # Save the modified Excel file in default (that is Excel 2003) format workbook.save("Insert Row.xls") print "Insert Row Successfully."Copy the code

Insert columns into an Excel worksheet using Python

Here are the steps to insert columns into an Excel worksheet using Python.

  1. First, use the Workbook class to load Excel files.
  2. Use the workbook.getWorksheets ().get (index) method to access the required worksheet through the index.
  3. InsertColumns using the worksheet.getcells ().insertcolumns (columnIndex, totalColumns) method, where the first argument is the columnIndex and the second argument is the number of columns to insert.
  4. Finally, use the workbook.save (string) method to save the updated file.

The following code example demonstrates how to use Python to insert columns into an Excel worksheet.

# Instantiate a Workbook object by excel file path workbook = self.Workbook('Book1.xls') # Access the first worksheet in  the Excel file worksheet = workbook.getWorksheets().get(0) # Insert a column into the worksheet at 2nd position Worksheet.getcells ().insertcolumns (1,1) # Save the modified Excel file in default (that is Excel 2003) format workbook.save("Insert Column.xls") print "Insert Column Successfully."Copy the code

Delete rows in Excel using Python

Here are the steps to delete rows from an Excel worksheet using Python.

  1. First, use the Workbook class to load Excel files.
  2. Use the workbook.getWorksheets ().get (index) method to access the required worksheet through the index.
  3. Rows are deleted using the worksheet.getcells ().deleterows (rowIndex, totalRows) method, where the first argument is the rowIndex and the second argument is the number of rows to delete.
  4. Finally, use the workbook.save (string) method to save the updated file.

The following code example shows how to remove rows from an Excel worksheet in Python.

# Instantiate a Workbook object by excel file path workbook = self.Workbook("Book1.xls") # Access the first worksheet in  the Excel file worksheet = workbook.getWorksheets().get(0) # Delete 10 rows from the worksheet starting from 3rd row Worksheet.getcells ().deleterows (2,10,True) # Save the modified Excel file in default (that is Excel 2003) format workbook.save("Insert Row.xls") print "Insert Row Successfully."Copy the code

Delete columns in Excel using Python

Here are the steps to remove columns from an Excel worksheet using Python.

  1. First, use the Workbook class to load Excel files.
  2. Use the workbook.getWorksheets ().get (index) method to access the required worksheet through the index.
  3. Delete columns using worksheet.getCells ().insertColumns (columnIndex, totalColumns, updateReference). The first parameter is the column index, the second parameter is the number of columns to delete, and the third parameter indicates whether references need to be updated in other worksheets.
  4. Finally, use the workbook.save (string) method to save the updated file.

The following code example demonstrates how to use Python to remove columns from an Excel worksheet.

# Instantiate a Workbook object by excel file path workbook = self.Workbook('Book1.xls') # Access the first worksheet in  the Excel file worksheet = workbook.getWorksheets().get(0) # Delete a column from the worksheet at 2nd position Worksheet.getcells ().deletecolumns (1,1,True) # Save the modified Excel file in default (that is Excel 2003) format workbook.save("Insert Column.xls") print "Insert Column Successfully."Copy the code

If you have any questions or requirements, please feel free to join the Aspose Technology Exchange Group (761297826), we are happy to provide you with inquiries and consultation.