Sum of two columns in openpyxl and result in a new column

Most of the developers especially developers who work in the field of data use Excel alongside other programming languages on a daily basis. Now Sometimes a situation might arise that they need to find the sum of two adjacent or non-adjacent columns and fill a separate column using the summation of each row. In this article, we will see how we can find the sum of two columns of an Excel workbook and store the result in a new column using Python.

The Sum of Columns using openpyxl in Python

The openpyxl module is specially made to manipulate Excel files in Python, just by using Python codes, without opening them in Excel. The operations we do use openpyxl will be reflected in the Excel file. It supports different extensions like xlsx/xlsm/xltx/xltm etc. Now there are different ways to achieve that. One can directly do that in Excel, but developers might use a different approach to solve this problem using Python.

Required Module

We will use only a single Python module called openpyxl. To install it write the below command in any terminal of the user’s choice.

pip install openpyxl

Step to get the Sum of Columns of Excel File using Openpyxl

Let us see the step-by-step process to add columns of an Excel File into another column using the Openpyxl module in Python. But before that, we will create and store an Excel file that consists of at least two columns consisting of numerical values. So that we can find the sum of them and store the result in a new column. For this article, we will take the following “data.xlsx” Excel file as an example.

data.xlsx

Step 1: Importing required modules

The first step is to install the openpyxl module.

import openpyxl

Step 2: Load the file

After importing the modules, we will use a variable to store the path of the file. If the file exists in the same folder as of the Python file then there is no need to provide an entire path. Only the name with the extension would do. The load_workbook() function of the openpyxl module is used to load the Excel file, which takes the path of the file as a parameter and returns the workbook.

path = "path_of_the_excel_file"
workbook = openpyxl.load_workbook(path)

Step 3: Initialize the Excel sheet

Next, we will use the active method to create and initialize a sheet object. Which we will later use to fetch the required row and column from that sheet.

sheet = workbook.active

Step 4: Adding the Columns

This is the penultimate step, here we will use a Python for loop to iterate over each row of both columns and add their values using the format() inbuilt function and Python List Comprehension. The for loop’s start and end positions has to be the first and last row numbers which store a numerical value.

Inside the total_sum variable, we will call the sheet by its Column Name and provide the row number using the loop iterator variable.

for i in range(2, 11):
    sheet['D{}'.format(i)] = sheet["B{}".format(i)].value + sheet["C{}".format(i)].value

Step 5: Saving the Excel file

Then finally we will come out of the loop and use the save() method to save the updated workbook by providing the path/name of it.

workbook.save(path)

Examples of Getting The Sum of Columns using Openpyxl

Let us see a few examples to get the sum of columns of an Excel file using Openpyxl in Python.

Example 1: Sum of adjacent Columns

In this example, we will be taking two adjacent columns of the Excel worksheet, i.e., Column B and Column C, and storing their summation in Column E of the Excel file.

Python3

# import module
import openpyxl

# load excel file
path = "files/data.xlsx"
workbook = openpyxl.load_workbook(path)

# activate worksheet
sheet = workbook.active

# calculate sum
for i in range(2, 11):
    sheet['E{}'.format(i)] = sheet["B{}".format(i)].value + sheet["C{}".format(i)].value

# save the workbook
workbook.save(path)

Output:

The sum of Adjacent Columns

Example 2: Sum of non-adjacent Columns

In this example, we will be taking two non-adjacent columns of the Excel worksheet, i.e., Column B and Column D, and storing their summation in Column F of the Excel file.

Python3

# import module
import openpyxl

# load file
path = "files/data.xlsx"
workbook = openpyxl.load_workbook(path)

# activate worksheet
sheet = workbook.active

# calculate sum
for i in range(2, 11):
    sheet['F{}'.format(i)] = sheet["B{}".format(i)].value + sheet["D{}".format(i)].value

# save file
workbook.save(path)

Output:

The sum of Non-Adjacent Columns