How to Delete Rows in Excel using Openpyxl in Python

Managing data in Microsoft Excel often entails cleaning and refining datasets to extract valuable insights. When dealing with large spreadsheets, manual row deletions can be a tedious and error-prone task. Enter Python and the openpyxl library, a dynamic duo that can help automate the process, saving you time and minimizing the risk of mistakes.

In this article, we'll dive into the world of data manipulation and show you how to delete rows in Excel using openpyxl with Python. Whether you're a data analyst, financial professional, or simply someone seeking to optimize your Excel workflow, this guide will equip you with the skills to streamline your data cleaning processes efficiently.

We'll take you through the step-by-step process of leveraging openpyxl's capabilities to identify and remove rows that no longer serve your data analysis goals.

By the end of this article, you'll have the knowledge and tools needed to declutter your Excel spreadsheets and focus on the data that truly matters.

Let's begin our journey into the world of automated row deletion in Excel with Python and openpyxl.

Make sure you have the openpyxl library installed. If not, you can install it using pip.

pip install openpyxl

In your Python script or Jupyter Notebook, import the necessary libraries.

import openpyxl

This method removes empty rows but not continues empty rows, because when you delete the first empty row the next row gets its position.

# import openpyxl library
import openpyxl

# function to remove empty rows

def remove(sheet, row):
	# iterate the row object
	for cell in row:
		# check the value of each cell in
		# the row, if any of the value is not
		# None return without removing the row
		if cell.value != None:
			return
	# get the row number from the first cell
	# and remove the row
	sheet.delete_rows(row[0].row, 1)


if __name__ == '__main__':

	# enter your file path
	path = './delete_empty_rows.xlsx'

	# load excel file
	book = openpyxl.load_workbook(path)

	# select the sheet
	sheet = book['daily sales']

	print("Maximum rows before removing:", sheet.max_row)

	# iterate the sheet object
	for row in sheet:
	remove(sheet,row)
		
	print("Maximum rows after removing:",sheet.max_row)
	
	# save the file to the path
	path = './openpy.xlsx'
	book.save(path)

In this method, we delete the second row repeatedly until a single row is left.

import openpyxl


def delete(sheet):

	# continuously delete row 2 until there
	# is only a single row left over
	# that contains column names
	while(sheet.max_row > 1):
		# this method removes the row 2
		sheet.delete_rows(2)
	# return to main function
	return


if __name__ == '__main__':

		# enter your file path
	path = './delete_every_rows.xlsx'

	# load excel file
	book = openpyxl.load_workbook(path)

	# select the sheet
	sheet = book['sheet1']

	print("Maximum rows before removing:", sheet.max_row)

	delete(sheet)

	print("Maximum rows after removing:", sheet.max_row)

	# save the file to the path
	path = './openpy.xlsx'
	book.save(path)

Example:

In this method, we use openpyxl sheet method to delete entire rows with a single command.

import openpyxl

if __name__ == '__main__':

	# enter your file path
	path = './delete_every_rows.xlsx'

	# load excel file
	book = openpyxl.load_workbook(path)

	# select the sheet
	sheet = book['sheet1']

	print("Maximum rows before removing:", sheet.max_row)

	# sheet.max_row is the maximum number
	# of rows that the sheet have
	# delete_row() method removes rows, first parameter represents row
	# number and sencond parameter represents number of rows
	# to delete from the row number
	sheet.delete_rows(2, sheet.max_row-1)

	print("Maximum rows after removing:", sheet.max_row)

	# save the file to the path
	path = './openpy.xlsx'
	book.save(path)

You've now completed a step-by-step guide on how to delete rows in Excel using the openpyxl library in Python. With these skills, you can efficiently clean and refine your Excel datasets, making your data analysis tasks more manageable and accurate.

You might also like:

• Read Also: How To Add Export Button In Datatable
• Read Also: How To Export CSV File In Laravel 10 Example
• Read Also: Importing Excel File into Database Using Python
• Read Also: How to Write in Excel File using Openpyxl in Python