Prerequisite: Reading & Writing to excel sheet using openpyxl
Openpyxl is a Python library using which one can perform multiple operations on excel files like reading, writing, arithmetic operations and plotting graphs. Let’s see how to perform different arithmetic operations using openpyxl.
- =SUM(cell1:cell2) : Adds all the numbers in a range of cells.
Python3
# import openpyxl moduleimport openpyxl# Call a Workbook() function of openpyxl # to create a new blank Workbook objectwb = openpyxl.Workbook()# Get workbook active sheet # from the active attribute.sheet = wb.active# writing to the cell of an excel sheetsheet['A1'] = 200sheet['A2'] = 300sheet['A3'] = 400sheet['A4'] = 500sheet['A5'] = 600# The value in cell A7 is set to a formula # that sums the values in A1, A2, A3, A4, A5 .sheet['A7'] = '= SUM(A1:A5)'# save the filewb.save("sum.xlsx") |
- Output:
- =PRODUCT(cell1:cell2) : Multiplies all the numbers in the range of cells.
Python3
import openpyxlwb = openpyxl.Workbook()sheet = wb.activesheet['A1'] = 2sheet['A2'] = 3sheet['A3'] = 4sheet['A4'] = 5sheet['A5'] = 6# The value in cell A7 is set to a formula # that multiplies the values in A1, A2, A3, A4, A5 .sheet['A7'] = '= PRODUCT(A1:A5)'wb.save("product.xlsx") |
- Output:
- =AVERAGE(cell1:cell2) : It gives the average (arithmetical mean) of all the numbers which is present in the given cell range.
Python3
import openpyxlwb = openpyxl.Workbook()sheet = wb.activesheet['A1'] = 200sheet['A2'] = 300sheet['A3'] = 400sheet['A4'] = 500sheet['A5'] = 600# The value in cell A7 is set to a formula # that return average of the values in A1, A2, A3, A4, A5 .sheet['A7'] = '= AVERAGE(A1:A5)'wb.save("average.xlsx") |
- Output:
- =QUOTIENT(num1, num2) : It returns the integer portion of a division.
Python3
import openpyxlwb = openpyxl.Workbook()sheet = wb.active# The value in cell is set to a formula # that gives quotient value .sheet['A1'] = '= QUOTIENT(64, 8)'sheet['A2'] = '= QUOTIENT(25, 4)'wb.save("quotient.xlsx") |
- Output:
- =MOD(num1, num2) : Returns the remainder after a number is divided by the divisor.
Python3
import openpyxlwb = openpyxl.Workbook()sheet = wb.active# The value in cell is set to a formula # that gives remainder or modulus value.sheet['A1'] = '= MOD(64, 8)'sheet['A2'] = '= MOD(25, 4)'wb.save("modulus.xlsx") |
- Output:
- =COUNT(cell1:cell2) : It counts the number of cells in a range that contain the number.
Python3
import openpyxlwb = openpyxl.Workbook()sheet = wb.activesheet['A1'] = 200sheet['A2'] = 300sheet['A3'] = 400sheet['A4'] = 500sheet['A5'] = 600# The value in cell A7 is set to a formula # that gives counting of number present in the cells.sheet['A7'] = '= COUNT(A1:A6)'wb.save("count.xlsx") |
- Output:
