Openpyxl is a Python library to manipulate xlsx/xlsm/xltx/xltm files. With Openpyxl one 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.
pip install openpyxl
Below is the data used in this article. to download click here.
Deleting Empty rows (one or more)
1. Removing Non-Continuous Empty Rows (Single-Pass Deletion)
This approach removes empty rows using a single forward pass over the worksheet. However, when an empty row is deleted, the next row shifts upward, causing consecutive empty rows to be skipped. As a result, this method can remove only some empty rows, not continuous ones.
import openpyxl
def remove_empty_row(sheet, row):
for cell in row:
if cell.value is not 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.iter_rows():
remove_empty_row(sheet, row)
print("Maximum rows after removing:", sheet.max_row)
book.save('./openpy.xlsx')
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.
2. Removing Continuous Empty Rows (Recursive Safe Deletion)
This approach correctly removes all empty rows, including consecutive ones, by using a recursive strategy. After deleting a row, the function restarts scanning the sheet to ensure no empty rows are skipped.
import openpyxl
def remove_all_empty_rows(sheet):
for row in sheet.iter_rows():
if not any(cell.value for cell in row):
sheet.delete_rows(row[0].row, 1)
remove_all_empty_rows(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)
remove_all_empty_rows(sheet)
print("Maximum rows after removing:", sheet.max_row)
book.save('./openpy.xlsx')
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
1. Row-by-Row Deletion Using a Loop
This approach repeatedly deletes the second row until only the header row remains. Since row indices shift upward after each deletion, deleting row 2 ensures that all data rows are removed safely.
import openpyxl
def delete_all_rows(sheet):
while sheet.max_row > 1:
sheet.delete_rows(2)
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_all_rows(sheet)
print("Maximum rows after removing:", sheet.max_row)
book.save('./openpy.xlsx')
Output
Maximum rows before removing: 15
Maximum rows after removing: 1
File after deletion:
2. Bulk Deletion Using delete_rows()
This approach removes all data rows in a single command, making it faster and more efficient for large worksheets.
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)
book.save('./openpy.xlsx')
Output
Maximum rows before removing: 15
Maximum rows after removing: 1
File after deletion: