Change language

# Python | Arithmetic operations in an excel file using openpyxl

| | |

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:

## Shop

Learn programming in R: courses

\$

Best Python online courses for 2022

\$

Best laptop for Fortnite

\$

Best laptop for Excel

\$

Best laptop for Solidworks

\$

Best laptop for Roblox

\$

Best computer for crypto mining

\$

Best laptop for Sims 4

\$

Latest questions

NUMPYNUMPY

Common xlabel/ylabel for matplotlib subplots

NUMPYNUMPY

How to specify multiple return types using type-hints

NUMPYNUMPY

Why do I get "Pickle - EOFError: Ran out of input" reading an empty file?

NUMPYNUMPY

Flake8: Ignore specific warning for entire file

NUMPYNUMPY

glob exclude pattern

NUMPYNUMPY

How to avoid HTTP error 429 (Too Many Requests) python

NUMPYNUMPY

Python CSV error: line contains NULL byte

NUMPYNUMPY

csv.Error: iterator should return strings, not bytes

## Wiki

Python | How to copy data from one Excel sheet to another

Common xlabel/ylabel for matplotlib subplots

Check if one list is a subset of another in Python

How to specify multiple return types using type-hints

Printing words vertically in Python

Python Extract words from a given string

Cyclic redundancy check in Python

Finding mean, median, mode in Python without libraries