Insert a column to Excel using openpyxl in Python

This tutorial will see how to insert a column into the Excel sheet using the Openpyxl library in Python. Using this library we can do multiple operations on an excel file.

First, import the openpyxl library to use the functions from it.

import openpyxl

The excel file we will refer to is:

Insert a column to Excel using openpyxl in Python

Now using the load_workbook() function from the openpyxl library access the above excel file. Pass the excel file path to the function to extract the file.
After extracting the excel workbook select the sheet you want to work on. Select the active sheet from excel workbook using .active method. You can select any other sheet by specifying its name.

path = 'abc.xlsx'
wb = load_workbook(path)
sheet = wb.active

insert_cols( ): Function to insert columns

To insert the column in an excel sheet insert_cols(idx) function from the openpyxl library is used. It takes idx(Integer) as an argument that specifies the position at which you want to insert the column.
Now Insert the column at the 3rd position using the above function.
Print the column numbers before and after the insertion for reference using max_column method. This method returns the maximum number of columns present in the excel sheet.

After insertion save the workbook using the save() function. Pass the target file path to the function and it will save the changes.

print("Maximum number of columns before inserting : ",sheet.max_column)
sheet.insert_cols(idx=3)
wb.save(path)
print("Maximum number of columns after inserting : ",sheet.max_column)

Output:

Maximum number of columns before inserting :  6
Maximum number of columns after inserting :  7

After insertion, the excel file looks like  this:

sheet.insert_cols(idx=3)

Also, refer to Insert a row in specific position into Excel using openpyxl in Python

Leave a Reply

Your email address will not be published. Required fields are marked *