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, arithmatic operations and plotting graphs. Let’s see how to perform different arithmatic operations using openpyxl.
- =SUM(cell1:cell2) : Adds all the numbers in a range of cells.
# import openpyxl module
import
openpyxl
# Call a Workbook() function of openpyxl
# to create a new blank Workbook object
wb
=
openpyxl.Workbook()
# Get workbook active sheet
# from the active attribute.
sheet
=
wb.active
# writing to the cell of an excel sheet
sheet[
'A1'
]
=
200
sheet[
'A2'
]
=
300
sheet[
'A3'
]
=
400
sheet[
'A4'
]
=
500
sheet[
'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 file
wb.save(
"sum.xlsx"
)
Output:
- =PRODUCT(cell1:cell2) : Multiplies all the numbers in the 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 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.
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 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.
import
openpyxl
wb
=
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.
import
openpyxl
wb
=
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.
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 a formula
# that gives counting of number present in the cells.
sheet[
'A7'
]
=
'= COUNT(A1:A6)'
wb.save(
"count.xlsx"
)
Output:
This article is attributed to GeeksforGeeks.org
0 0You Might Also Like
Subscribe to Our Newsletter
- =AVERAGE(cell1:cell2) : It gives the average (arithmetical mean) of all the numbers which is present in the given cell range.
leave a comment
0 Comments