Inserting & Deleting Rows/Columns using openpyxl

This Python tutorial helps to insert and delete rows and columns into an Excel file using openpyxl. We’ll use openpyxl libs to manipulate the Excel file. The openpyxl is the default reader for Python Pandas.

You can read and write Excel xlsx/xlsm/xltm/xltx files without using the Excel software.

What’s openpyxl

It is an open-source excel libs and the most widely used library for Excel operation. The openpyxl is a Python Library developed by Eric Gazoni and Charlie Clark.

You can also checkout other Python Excel tutorials:

What’s an Excel File

Excel is a very powerful and popular software for spreadsheets. The python excel libs help in reading and modifying excel spreadsheet files through python programs.

I have already shared a tutorial How To Read & Update Excel File Using Python.

How to install openpyxl python

This module does not come built-in with Python 3. You can install this package into your python application by running the following command into the terminal.

pip3 install openpyxl

Install openpyxl on Mac in Python

You can also use the below command to install openpyxl on mac system using python 3.

pip3 install openpyxl

The pip3 command is used for Python 3, and it will download and install the openpyxl library along with any necessary dependencies into the mac system.

I am just extending the previous tutorial and adding functionality to insert and delete rows with columns.

How To Verify Installation

You can verify that openpyxl was successfully installed by creating a simple Python script(test.py) and attempting to import the library:

import openpyxl
print("openpyxl is installed.")

Let’s run it using Python 3:

python3 verify_openpyxl.py

Inserting and Deleting Rows and Columns

The openpyxl provides a set of methods to the sheet class, that help to add and delete rows/columns from the excel sheet. I’m going to load the workbook, and then grab that active sheet and perform add/delete operations on the selected sheet.

How To Insert a Row into an Excel File

You can insert rows using an excel file using the insert_rows() worksheet methods. The default is one row to insert into an excel file. The syntax is as follows:

insert_rows(idx, amount=1)

Whereas: The first parameter represents the row number and the second parameter represents the number of rows.

The sample Python code for Inserting a row into Excel file:

path = "C:\employee.xlsx"
wb_obj = openpyxl.load_workbook(path.strip())
sheet_obj = wb_obj.active
print("Maximum rows before inserting:", sheet_obj.max_row)
#insert 2 rows starting on the first row
sheet_obj.insert_rows(idx=3)

#insert multiple rows at once
#insert 3 rows starting on the six row
sheet_obj.insert_rows(6,3)

print("Maximum rows after inserting:", sheet_obj.max_row)

# save the file to the path
path = './employee.xlsx'
sheet_obj.save(path)

How To Insert a Column into Excel File

You can insert columns into the Excel file using the insert_cols() worksheet methods. The default is one column to insert into excel file. The syntax is as follows:

insert_cols(idx, amount=1)

Whereas : The first parameter represents column number and the second parameter represents the number of columns to add

Python code to Inserting Column into the Excel File:

path = "C:\employee.xlsx"
wb_obj = openpyxl.load_workbook(path.strip())
sheet_obj = wb_obj.active
print("Maximum column before inserting:", sheet_obj.max_column)
#insert a column before first column A
sheet_obj.insert_cols(idx=1)

print("Maximum column after inserting:", sheet_obj.max_column)

# save the file to the path
path = './employee.xlsx'
sheet_obj.save(path)

How To Delete a Row From an Excel File

You can delete a row from the Excel file using the delete_rows() worksheet methods. The default is one row to delete from the excel file. The syntax is as follows:

delete_rows(idx, amount=1)

Whereas: The first parameter represents row number and the second parameter represents the number of rows to delete

Python Code to Delete Row from Excel:

path = "C:\employee.xlsx"
wb_obj = openpyxl.load_workbook(path.strip())
//sheet = employee['sheet1']
sheet_obj = wb_obj.active
print("Maximum rows before removing:", sheet_obj.max_row)
# delete 2 rows starting on the third row
sheet_obj.delete_rows(idx=3, amount=2)

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

# save the file to the path
path = './employee.xlsx'
sheet_obj.save(path)

How To Delete a Column From Excel File

You can delete a column from an Excel file using the delete_cols() worksheet methods. The default is one column to delete from the excel file. The syntax is as follows:

delete_cols(idx, amount=1)

Sample Python Code to Delete Column from Excel:

path = "C:\employee.xlsx"
wb_obj = openpyxl.load_workbook(path.strip())
sheet_obj = wb_obj.active
print("Maximum rows before removing:", sheet_obj.max_column)
#Delete column A
sheet_obj.delete_cols(idx=1)

print("Maximum cols after removing:", sheet_obj.max_column)

# save the file to the path
path = './employee.xlsx'
sheet_obj.save(path)

How to Insert Image in Excel File Using openpyxl

You can insert an image into an Excel file using the openpyxl library in Python. Please make sure your image format must be PNG and JPEG, that you want to insert into the Excel file.

from openpyxl.drawing.image import Image
img = Image("employee_image.png")
sheet.add_image(img, 'A1')
wb_obj.save("employee.xlsx")

in the above code, we are doing the following process to insert an image in an Excel file.

Import the Image class from openpyxl.drawing.
Load your image file using openpyxl.drawing.image.Image
Adding the image to a specific cell in your Excel sheet.

Conclusion

We have learned about How to insert a row of data into the existing excel file, and insert a column into the existing excel file. Also, deleted a row and column from the excel file using openpyxl. There are a lot of Popular Python excel Library to perform operation on excel file.