Sum of two columns in openpyxl and result in a new column
Hello, Coders!! In this openpyxl tutorial, we will learn how to sum two columns and print the results in a new column using openpyxl in Python.
You can get more details about the use and installation of openpyxl.
Program to sum two columns and result in a new column using openpyxl in Python
Let’s take a sample Excel Workbook with some data for this program:
Here we have been given a sample Excel Workbook with some academic data. Our aim is to calculate the total mark of each student by adding the Math and English column and then print the result to Total column.
Step1: Import the openpyxl library using the
import to the program.
Step2: Initialize an object with the path to the Excel file stored in the system.
file_path = "//home//sanamsahoo0876//Documents//Book86.xlsx"
Step3: Now, connect/load the Excel workbook to the program by passing the path to the openpyxl’s load_workbook() function.
wb = openpyxl.load_workbook(file_path)
Step4: Get the title of the default active sheet using wb.active and initialize it to the sheet object.
sheet = wb.active
Step5: Define a function CellVal() that will take row and column as arguments and return each cell value of the columns.
def CellVal(r, c): return sheet.cell(row=r, column=c).value
Step6: Using a loop, add each row of the two-column and store the result to each row of another column.
for m in range(2, 11): sheet.cell(row=m, column=4).value = CellVal(m, 2) + CellVal(m, 3)
Step7: Lastly, save the changes to the Excel file.
Here is the complete python program:
import openpyxl def CellVal(r, c): return sheet.cell(row=r, column=c).value file_path = "//home//sanamsahoo0876//Documents//Book86.xlsx" wb = openpyxl.load_workbook(file_path) sheet = wb.active for m in range(2, 11): sheet.cell(row=m, column=4).value = CellVal(m, 2) + CellVal(m, 3) wb.save(file_path)
Hope you liked reading this article and learned how to sum two columns and print the result in another column using openpyxl in Python.
You can also read, Get values of all rows in a particular column in openpyxl in Python