Openpyxl
— is a Python library with which you can perform several operations on Excel files, such as reading, writing, arithmetic, and graphing. Let’s see how to perform various arithmetic operations using openpyxl.
- = SUM (cell1: cell2): adds all numbers in a range of cells.
# import openpyxl module
import
openpyxl
# Calling the Workbook () function from openpyxl
# create a new blank Workbook object
wb
=
openpyxl.Workbook ()
# Get a working sheet of the active sheet
# from the active attribute.
sheet
=
wb.active
# write to worksheet cell Excel
sheet [
’A1’
]
=
200
sheet [
’A2’
]
=
300
sheet [
’A3’
]
=
400
sheet [
’A4’
]
=
500
sheet [
’A5’
]
=
600
# The value in cell A7 is set to the formula
# which sums the values in A1, A2, A3, A4, A5 .
sheet [
’A7’
]
=
’= SUM (A1: A5)’
# save file
wb.save (
"sum.xlsx"
)
Exit:
- = PRODUCT (cell 1: cell 2): Multiplies all numbers in a range of cells.
import
openpyxl
wb
=
openpyxl.Workbook ()
sheet
=
wb.active
sheet [
’A1’
]
=
2
sheet [
’A2’
]
=
3
sheet [
’A3’
]
=
4
sheet [
’A4’
]
=
5
sheet [
’ A5’
]
=
6
# The value in cell A7 is set to the formula
# which multiplies the values in A1, A2, A3, A4, A5.
sheet [
’A7’
]
=
’ = PRODUCT (A1: A5) ’
wb.save (
"product.xlsx"
)
Output:
- = MEDIUM (cell 1: cell 2): it gives the average (arithmetic mean) of all numbers that are present in the given range of cells.
import
openpyxl
wb
=
openpyxl.Workbook ()
sheet
=
wb.active
sheet [
’A1’
]
=
200
sheet [
’A2 ’
]
=
300
sheet [
’A3’
]
=
400
sheet [
’A4’
]
=
500
sheet [
’A5’
]
=
600
# The value in cell A7 is set to the formula
# which return average in A1, A2, A3, A4, A5.
sheet [
’A7’
]
=
’= AVERAGE (A1: A5)’
wb.save (
"average.xlsx"
)
Output:
- = QUOTIENT (num1, num2): returns the integer part of the division.
import
openpyxl
wb
=
openpyxl.Workbook ()
sheet
=
wb.active
# The value in the cell is set by the formula
# which gives the factor value.
sheet [
’A1’
]
=
’= QUOTIENT (64, 8)’
sheet [
’A2 ’
]
=
’ = QUOTIENT (25, 4) ’
wb.save (
" quotient.xlsx "
)
Output:
- = MOD (num1, num2): returns the remainder after dividing the number by the divisor.
import
openpyxl
wb
=
openpyxl.Workbook ()
sheet
=
wb.active
# The value in the cell is set by the formula
# which gives the remainder or value module.
sheet [
’A1’
]
=
’= MOD (64, 8)’
sheet [
’A2’
]
=
’= MOD (25, 4)’
wb.save (
"modulus.xlsx"
)
Output:
- = COUNT (cell a1: cell2): counts the number of cells in a range that contain a number.
import
openpyxl
wb
=
openpyxl.Workbook ()
sheet
=
wb.active
sheet [
’A1’
]
=
200
sheet [
’A2’
]
=
300
sheet [
’A3’
]
=
400
sheet [
’A4’
]
=
500
sheet [
’A5’
]
=
600
# The value in cell A7 is set to the formula
# which gives a count of the number present in the cells.
sheet [
’A7’
]
=
’= COUNT (A1: A6)’
wb.save (
"count.xlsx"
)
Output: