How to delete one or more rows in excel using Openpyxl?
Last Updated :
03 Jun, 2022
Openpyxl is a Python library to manipulate xlsx/xlsm/xltx/xltm files. With Openpyxl you can create a new Excel file or a sheet and can also be used on an existing Excel file or sheet.
Installation
This module does not come built-in with Python. To install this type the below command in the terminal.
pip3 install openpyxl
In this article, we will discuss how to delete rows in an Excel sheet with openpyxl. You can find the Excel file used for this article here.
Deleting Empty rows (one or more)
Method 1:
This method removes empty rows but not continues empty rows, because when you delete the first empty row the next row gets its position. So it is not validated. Hence, this problem can be solved by recursive function calls.
Approach:
- Import openpyxl library.
- Load Excel file with openpyxl.
- Then load the sheet from the file.
- Iterate the rows from the sheet that is loaded.
- Pass the row to remove the function.
- Then check for each cell if it is empty if any of the cells non-empty return the function, so only empty rows will exit the for loop without returning.
- Only if all rows are empty, remove statement is executed.
- Finally, save the file to the path.
Python3
import openpyxl
def remove(sheet, row):
for cell in row:
if cell.value ! = None :
return
sheet.delete_rows(row[ 0 ].row, 1 )
if __name__ = = '__main__' :
path = './delete_empty_rows.xlsx'
book = openpyxl.load_workbook(path)
sheet = book[ 'daily sales' ]
print ( "Maximum rows before removing:" , sheet.max_row)
for row in sheet:
remove(sheet,row)
print ( "Maximum rows after removing:" ,sheet.max_row)
path = './openpy.xlsx'
book.save(path)
|
Output:
Maximum rows before removing: 15
Maximum rows after removing: 14
File after deletion:
The first method deleted only the first empty row and the second continuous empty row is not deleted.
Method 2:
This method removes empty rows, including continuous empty rows by using the recursive approach. The key point is to pass the modified sheet object as an argument to the recursive function. If there is no empty row function is returned immediately.
Approach:
- Import openpyxl library.
- Load Excel file with openpyxl.
- Then load the sheet from the file.
- Pass the sheet that is loaded to the remove function.
- Iterate the rows with iter_rows().
- If any of the cells in a row is non-empty, any() return false, so it is returned immediately.
- If all cells in a row are empty, then remove the row with delete_rows().
- Then pass the modified sheet object to the remove function, this repeats until the end of the sheet is reached.
- Finally, save the file to the path.
Python3
import openpyxl
def remove(sheet):
for row in sheet.iter_rows():
if not all (cell.value for cell in row):
sheet.delete_rows(row[ 0 ].row, 1 )
remove(sheet)
return
if __name__ = = '__main__' :
path = './delete_empty_rows.xlsx'
book = openpyxl.load_workbook(path)
sheet = book[ 'daily sales' ]
print ( "Maximum rows before removing:" , sheet.max_row)
for row in sheet:
remove(sheet)
print ( "Maximum rows after removing:" ,sheet.max_row)
path = './openpy.xlsx'
book.save(path)
|
Output:
Maximum rows before removing: 15
Maximum rows after removing: 13
File after deletion:
This method deleted both continuous empty rows as expected.
Deleting All rows
Method 1:
In this method, we delete the second row repeatedly until a single row is left(column names).
Approach:
- Import openpyxl library.
- Load the Excel file and the sheet to work with.
- Pass the sheet object to delete function.
- Delete the second row, until there is a single row left.
- Finally, return the function.
Python3
import openpyxl
def delete(sheet):
while (sheet.max_row > 1 ):
sheet.delete_rows( 2 )
return
if __name__ = = '__main__' :
path = './delete_every_rows.xlsx'
book = openpyxl.load_workbook(path)
sheet = book[ 'sheet1' ]
print ( "Maximum rows before removing:" , sheet.max_row)
delete(sheet)
print ( "Maximum rows after removing:" , sheet.max_row)
path = './openpy.xlsx'
book.save(path)
|
Output:
Maximum rows before removing: 15
Maximum rows after removing: 1
File after deletion:
Method 2:
In this method, we use openpyxl sheet method to delete entire rows with a single command.
Approach:
- Import openpyxl library.
- Load the Excel file and the sheet to work with.
- Use delete_rows function to delete all rows except the column names.
- So a single empty row is left over.
Python3
import openpyxl
if __name__ = = '__main__' :
path = './delete_every_rows.xlsx'
book = openpyxl.load_workbook(path)
sheet = book[ 'sheet1' ]
print ( "Maximum rows before removing:" , sheet.max_row)
sheet.delete_rows( 2 , sheet.max_row - 1 )
print ( "Maximum rows after removing:" , sheet.max_row)
path = './openpy.xlsx'
book.save(path)
|
Output:
Maximum rows before removing: 15
Maximum rows after removing: 1
File after deletion:
Share your thoughts in the comments
Please Login to comment...