Delete Single/Multiple Excel Rows by openpyxl
In this tutorial, we will explain how to remove one or more rows by using openpyxl library.
The openpyxl has following two methods to remove rows and columns from the specified Workbook sheet.
delete_rows
delete_cols
Delete rows in Excel by openpyxl
In this tutorial, we will explain how to remove one or more rows by using openpyxl library.
The openpyxl has the following two methods to remove rows and columns from the specified Workbook sheet.
- delete_rows
- delete_cols
By specifying parameters, you may remove one or more rows and columns by using these methods.
The examples below are:
- Removing one row
- Removing multiple rows by specifying row number and number of rows to be deleted
- Last example is how to delete the whole dataset except the header without specifying the row number or amount.
An example of removing a single row
For showing examples of removing rows, we have the following sample sheet. Look at its dummy data first and then we will show the code for removing one or more rows:
For removing single row we may specify the row idx as follows:
sheet.delete_rows(idx=1)
This will delete the first row from the specified Sheet.
See an example with the complete code below.
The code to delete row number 2 with Product ID = p-001 from our sample Workbook:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
# Deleting Data from Sheets # excel-python.py from openpyxl import load_workbook #Specify the Workbook wb_del = load_workbook("test_sheet.xlsx") sheet = wb_del.active #Deleting row # 2 sheet.delete_rows(idx=2) #Saving the operation wb_del.save('test_sheet.xlsx') |
Result:
You can see, row number 2 with Product ID = p-001 is removed from our dataset.
Removing multiple rows example
The delete_rows() method has another keyword argument, amount, that you may use to specify the number of rows to be deleted.
For example:
delete_rows(idx=2, amount=4)
Be careful to specify idx keyword value. It is the first row to be deleted from the dataset and the amount include that row.
In the above line, row number 2, 3, 4, and 5 will be deleted from the Excel Sheet.
An example with code and output
Our target is to remove four rows from our sample sheet. The rows number are from 6 to 9:
In that case,
idx = 6
amount = 4
See this in the Python code below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# Deleting Data from Sheets # excel-python.py from openpyxl import load_workbook #Specify the Workbook wb_del = load_workbook("test_sheet.xlsx") sheet = wb_del.active #Deleting from row number 6 to 9 sheet.delete_rows(idx=6, amount=4) #Saving the operation wb_del.save('test_sheet.xlsx') |
Sample sheet before deletion:
Sample sheet after removing records:
You can see, four rows are deleted from our dataset.
Deleting all rows except headers example
If you want to delete all rows without specifying the rows numbers and amount, you may do this by using max_row in the delete_rows function.
While you may delete all rows by using this with single line of code, you may omit rows as well.
In the example below, we will delete all rows from our sample sheet except the headers:
The code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
# Deleting Data from Sheets # excel-python.py from openpyxl import load_workbook #Specify the Workbook wb_del = load_workbook("test_sheet.xlsx") sheet = wb_del.active #Deleting all rows from sameple sheet except headers sheet.delete_rows(2, sheet.max_row-1) #Saving the Workbook wb_del.save('test_sheet.xlsx') |
Result:
