Delete Single/Multiple Excel Rows by openpyxl

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:

# 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:

# 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:

# 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: