Arithmetic Operation in excel file using openpyxl in Python
Openpyxl is a library in Python with which one can perform the different mathematic operations in an excel sheet. In this tutorial, we are going to learn the implementation of mathematical functions available in openpyxl library. Mathematical operations like sum, product, average, quotient, count, etc. are simple, straight forward, and easy to implement in Python using openpyxl library.
Arithmetic Operations in excel file with Python.
We can perform different operations like reading, writing, plotting graphs, etc. too with openpyxl.
Let’s see how we can implement these methods.
The first step is – importing openpyxl library
The second step is to call a workbook() function and create a workbook object.
wb = openpyxl.Workbook()
Next, get the worksheet using an active attribute.
sheet = wb.active
After creating the empty worksheet, fill the empty cells with some random values to perform mathematical operation later.
sheet['A1'] = 200 sheet['A2'] = 300 sheet['A3'] = 400 sheet['A4'] = 500 sheet['A5'] = 600
Firstly, we will see SUM() method – ‘=SUM(cell1:cell2)’
sheet['A7'] = '= SUM(A1:A5)'
Here, the formula is set to the cell ‘A7’ that sums the values of ‘A1’, ‘A2’, ‘A3’, ‘A4’ and ‘A5’ and print the results in ‘A7’
To save the file
Second is the Product() method – ‘=PRODUCT(cell1:cell2)’ returns the product of the numbers in the cells.
wb2 = openpyxl.Workbook() sheet2 = wb2.active sheet2['A1'] = 2 sheet2['A2'] = 3 sheet2['A3'] = 4 sheet2['A4'] = 5 sheet2['A5'] = 6 sheet2['A7'] = '= PRODUCT(A1:A5)' wb2.save("product.xlsx")
It sets the formula to the cell ‘A7’ and returns the multiplication of the numbers in all the cells. At the end of the code save the file.
Third is Average() method – ‘=AVERAGE(cell1:cell2)’ returns the average value of the given cells.
wb3 = openpyxl.Workbook() sheet3 = wb3.active sheet3['A1'] = 200 sheet3['A2'] = 300 sheet3['A3'] = 400 sheet3['A4'] = 500 sheet3['A5'] = 600 sheet3['A7'] = '= AVERAGE(A1:A5)' wb3.save("average.xlsx")
Works similar to the above, it calculates the arithmetic mean of all the numbers in a given cell range.
Fourth is quotient() method – ‘=QUOTIENT(num1, num2)’
wb4 = openpyxl.Workbook() sheet4 = wb4.active sheet4['A1'] = '= QUOTIENT(64, 8)' sheet4['A2'] = '= QUOTIENT(25, 4)' wb4.save("quotient.xlsx")
It returns the quotient from the division operation where num1 is the dividend and num2 is the divisor.
Fifth is the MOD() method – ‘=MOD(num1, num2)’
wb5 = openpyxl.Workbook() sheet5 = wb5.active sheet5['A1'] = '= MOD(64, 8)' sheet5['A2'] = '= MOD(25, 4)' wb5.save("modulus.xlsx")
It returns the remainder of the division operation where num1 is dividend and num2 is the divisor.
and the last one is count() method – ‘=COUNT(cell1:cell2)’
wb6 = openpyxl.Workbook() sheet6 = wb6.active sheet6['A1'] = 200 sheet6['A2'] = 300 sheet6['A3'] = 400 sheet6['A4'] = 500 sheet6['A5'] = 600 sheet6['A7'] = '= COUNT(A1:A6)' wb6.save("count.xlsx")
It counts the number of cells that contain the value.