Inserting rows and columns
You can insert rows or columns using the relevant worksheet methods:
openpyxl.worksheet.worksheet.Worksheet.insert_rows()openpyxl.worksheet.worksheet.Worksheet.insert_cols()openpyxl.worksheet.worksheet.Worksheet.delete_rows()openpyxl.worksheet.worksheet.Worksheet.delete_cols()
The default is one row or column. For example to insert a row at 7 (before the existing row 7):
>>> ws.insert_rows(7)
Deleting rows and columns
To delete the columns F:H:
>>> ws.delete_cols(6, 3)
Moving ranges of cells
You can also move ranges of cells within a worksheet:
>>> ws.move_range("D4:F10", rows=-1, cols=2)
This will move the cells in the range D4:F10 up one row, and right two columns. The cells will overwrite any existing cells.
If cells contain formulae you can let openpyxl translate these for you, but as this is not always what you want it is disabled by default. Also only the formulae in the cells themselves will be translated. References to the cells from other cells or defined names will not be updated; you can use the Parsing Formulas translator to do this:
>>> ws.move_range("G4:H10", rows=1, cols=1, translate=True)
This will move the relative references in formulae in the range by one row and one column.
Inserting 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.
Python Code
Copy def insert_row(self):
\# Instantiating a Workbook object by excel file path
workbook = self.Workbook(self.dataDir + "Book1.xls")
\# Accessing the first worksheet in the Excel file
worksheet = workbook.getWorksheets().get(0)
\# Inserting a row into the worksheet at 3rd position
worksheet.getCells().insertRows(2,1)
\# Saving the modified Excel file in default (that is Excel 2003) format
workbook.save(self.dataDir + "Insert Row.xls")
print "Insert Row Successfully."
Inserting 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, total number of rows that need to be inserted.
Python Code
Copy def insert_multiple_rows(self):
\# Instantiating a Workbook object by excel file path
workbook = self.Workbook(self.dataDir + 'Book1.xls')
\# Accessing the first worksheet in the Excel file
worksheet = workbook.getWorksheets().get(0)
\# Inserting a row into the worksheet at 3rd position
worksheet.getCells().insertRows(2,10)
\# Saving the modified Excel file in default (that is Excel 2003) format
workbook.save(self.dataDir + "Insert Multiple Rows.xls")
print "Insert Multiple Rows Successfully."
Deleting 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, total number of rows that need to be deleted.
Python Code
Copy def delete_row(self):
\# Instantiating a Workbook object by excel file path
workbook = self.Workbook(self.dataDir + 'Book1.xls')
\# Accessing the first worksheet in the Excel file
worksheet = workbook.getWorksheets().get(0)
\# Deleting 3rd row from the worksheet
worksheet.getCells().deleteRows(2,1,True)
\# Saving the modified Excel file in default (that is Excel 2003) format
workbook.save(self.dataDir + "Delete Row.xls")
print "Delete Row Successfully."
Deleting 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, total number of rows that need to be deleted.
Python Code
Copy def delete_multiple_rows(self):
\# Instantiating a Workbook object by excel file path
workbook = self.Workbook(self.dataDir + 'Book1.xls')
\# Accessing the first worksheet in the Excel file
worksheet = workbook.getWorksheets().get(0)
\# Deleting 10 rows from the worksheet starting from 3rd row
worksheet.getCells().deleteRows(2,10,True)
\# Saving the modified Excel file in default (that is Excel 2003) format
workbook.save(self.dataDir + "Delete Multiple Rows.xls")
print "Delete Multiple Rows Successfully."
Inserting a Column
Developers can also insert a column into the worksheet at any location by calling the insertColumns method of the Cells collection. insertColumns method takes two parameters:
- Column index, the index of the column from where the column will be inserted
- Number of columns, total number of columns that need to be inserted
Python Code
Copy def insert_column(self):
\# Instantiating a Workbook object by excel file path
workbook = self.Workbook(self.dataDir + 'Book1.xls')
\# Accessing the first worksheet in the Excel file
worksheet = workbook.getWorksheets().get(0)
\# Inserting a column into the worksheet at 2nd position
worksheet.getCells().insertColumns(1,1)
\# Saving the modified Excel file in default (that is Excel 2003) format
workbook.save(self.dataDir + "Insert Column.xls")
print "Insert Column Successfully."
Deleting 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, total number of columns that need to be deleted.
- Shift cells, Boolean parameter to indicate whether to shift the cells left after deletion.
Python Code
Copy def delete_column(self):
\# Instantiating a Workbook object by excel file path
workbook = self.Workbook(self.dataDir + 'Book1.xls')
\# Accessing the first worksheet in the Excel file
worksheet = workbook.getWorksheets().get(0)
\# Deleting a column from the worksheet at 2nd position
worksheet.getCells().deleteColumns(1,1,True)
\# Saving the modified Excel file in default (that is Excel 2003) format
workbook.save(self.dataDir + "Delete Column.xls")
print "Delete Column Successfully."
