Sum of two Columns in OpenPyxl and Result in a new Column

In this era, where technology is getting advanced with the passage of every single day, the data and its uses are also growing at a very rapid rate. These days developers not only do programming but also they are engaging themselves in the field of data, where they use  Excel and also continue the other programming languages on a daily note. Somedays, these developers go through some situations where they are needed to find the addition of two adjacent or two non-adjacent columns and then fill a separate column using the summation on each row. Well, in this tutorial, we are going to say how we are able to do the summation of two columns on the Excel workbook and then how we are able to get the result or store the result in a new column using the Python programming language.

Sum of two Columns using OpenPyxl in Python.

As we know that Python is rich in terms of modules and methods. Here we will discuss another kind of Python module named openpyxl; this module is a specially made module that is used to manipulate Excel files in Python with the help of the Python codes, without even opening them in the Excel sheet. Whatever operation we will do with the help of this module will be completely reflected in the Excel file. Well, this module supports different kinds of extensions like xlsx/xlsm/xltx/xltm and etc. To use these extensions, there are different ways, a user can directly use them in Excel, but the developers sometimes use different kinds of approaches to find the solution for this problem using Python.

Required Module

To avail of this facility, we have only required a single Python module which is called openpyxl. To use this module, we have to install it by writing the below command in any terminal as per the choice.

pip install openpyxl

Steps to get the Sum of Columns of an Excel file using OpenPyxl.

Now let us see the step-by-step procedure to add the columns of an Excel file to the other column of the Excel file using the openpyxl module in Python. Before moving further, we will first create and store an Excel file that should have at least two columns, and the columns will consist of numerical values. By creating such an Excel file, we are able to find the addition of them and then able to store the result in the new column. We are going to take an Excel file named " as an example.

Step 1: Firstly, we will import the required module. For that module, we have to install the openpyxl module from a terminal window.

import openpyxl

Step 2: Thereafter, we have to load the file.

When the importing of the module is finished, we are required to use the variable which will store the path of the chosen file. We have to check that if the chosen file exists in the same folder where the Python file lies, then it isn't required to provide the entire path. For that purpose, only the name with the extension will help. Well, in this module, there is a function named load_workbook() function, and it is used to load the Excel file; this function takes the path of the file as a parameter, and then it returns the workbook.   Now let us look at the code below :

path = "path_of_the_excel_file"

workbook = openpyxl.load_workbook(path)

Step 3: Here, we will Initialize the Excel sheet

Thereafter we will be going to use the vigorous method to generate and initialize the sheet object and which we will use later to fetch the required row and column from the respective sheet.

Sheet = workbook.active

Step 4: Here, we will add the Columns

This is the deciding step, and here we will going to use Python for the loop to iterate over each and every row of both the columns and later add their values using the inbuilt format() function and the Python List Comprehension. Here loop starting and ending positions will be the first and the last row numbers, which can store the numerical value.

Here inside the total_sum variable, we can call the sheet by its Column Name and then provide the row number by 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: Here, we will Save the Excel file

Finally, we will come out of the loop, and then we will use the save() method to save the updated workbook by just providing the path and name of it.

workbook.save(path)

Examples of Getting the Sum of Columns by using the Openpyxl

Let us see one of the few examples of finding the sum of columns of the Excel file by using Openpyxl in Python.

Example 1: By doing the addition of adjacent Columns.

In the first example, we will go to take two adjacent columns of the Excel worksheet and which are Column B and Column C, and then we will store their addition in Column E of the Excel file.

Code:

# importing the module

import openpyxl

# loading the excel file

path = "files/data.xlsx"

workbook = openpyxl.load_workbook(path)

# activating the worksheet

sheet = workbook.active

# calculating the sum

for i in range(2, 11):

    sheet['E{}'.format(i)] = sheet["B{}".format(i)].value + sheet["C{}".format(i)].value

# saving the workbook

workbook.save(path)

Output:

Sum of Adjacent Columns

Example 2: Addition of two non-adjacent Columns

In the second example, we will go to take two non-adjacent columns of the Excel worksheet, and which are Column B and Column D, and then we will store their summation in Column F of our Excel file.

Code:

# importing the module

import openpyxl

# loading the file

path = "files/data.xlsx"

workbook = openpyxl.load_workbook(path)

# activating the worksheet

sheet = workbook.active

# calculating the sum

for i in range(2, 11):

    sheet['F{}'.format(i)] = sheet["B{}".format(i)].value + sheet["D{}".format(i)].value

# saving the file

workbook.save(path)

Output:

Sum of Non-Adjacent Columns

Conclusion

In this tutorial, we have discussed the addition of two columns in the openpyxl and getting the result in the new column. Firstly we discussed different types of extensions that can be used here, like xlsm, xltm and etc. We have discussed the module that will be required here and which is open.

Here we have followed different steps, which start from importing the required mod dual and then loading the file. Thereafter we have initialized the Excel sheet and then added the column. At last, we have seen some of the examples which deal with the sum of adjacent columns and the sum of non-adjacent columns.