Python | Arithmetic operations in excel file using openpyxl (original) (raw)
Last Updated : 19 May, 2021
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
wb = openpyxl.Workbook()
sheet = wb.active
sheet[ 'A1' ] = 200
sheet[ 'A2' ] = 300
sheet[ 'A3' ] = 400
sheet[ 'A4' ] = 500
sheet[ 'A5' ] = 600
sheet[ 'A7' ] = '= SUM(A1:A5)'
wb.save( "sum.xlsx" )
- Output:
- =PRODUCT(cell1:cell2) : Multiplies all the numbers in the range of cells.
Python3
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet[ 'A1' ] = 2
sheet[ 'A2' ] = 3
sheet[ 'A3' ] = 4
sheet[ 'A4' ] = 5
sheet[ 'A5' ] = 6
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 openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet[ 'A1' ] = 200
sheet[ 'A2' ] = 300
sheet[ 'A3' ] = 400
sheet[ 'A4' ] = 500
sheet[ 'A5' ] = 600
sheet[ 'A7' ] = '= AVERAGE(A1:A5)'
wb.save( "average.xlsx" )
- Output:
- =QUOTIENT(num1, num2) : It returns the integer portion of a division.
Python3
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
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 openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
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 openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet[ 'A1' ] = 200
sheet[ 'A2' ] = 300
sheet[ 'A3' ] = 400
sheet[ 'A4' ] = 500
sheet[ 'A5' ] = 600
sheet[ 'A7' ] = '= COUNT(A1:A6)'
wb.save( "count.xlsx" )
- Output:
Similar Reads
- Python | Trigonometric operations in excel file using openpyxl Prerequisite : Adjusting rows and columns of an excel sheet using openpyxl. Openpyxl is a Python library using which one can perform multiple operations on excel files like reading, writing, mathematical operations and plotting graphs. Let’s see how to perform different Trigonometric operations usin 3 min read
- Excel Automation with Openpyxl in Python Excel is a powerful tool that allows you to store, manipulate and analyze data. It is widely used in various industries for data analysis and reporting. However, as the size of the data increases, Excel becomes slow and it takes a lot of time to perform complex operations. This is where Python comes 4 min read
- Python | Plotting charts in excel sheet using openpyxl module | Set - 1 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 plot different charts using realtime data. Charts are compo 6 min read
- Python | Plotting charts in excel sheet using openpyxl module | Set – 2 Prerequisite: Python | Plotting charts in excel sheet using openpyxl module | Set – 1 Openpyxl is a Python library using which one can perform multiple operations on excel files like reading, writing, arithmetic operations and plotting graphs. Charts are composed of at least one series of one or mor 6 min read
- Python | Plotting charts in excel sheet using openpyxl module | Set 3 Prerequisite : Plotting charts in excel sheet using openpyxl module Set - 1 | Set – 2Openpyxl is a Python library using which one can perform multiple operations on excel files like reading, writing, arithmetic operations and plotting graphs. Charts are composed of at least one series of one or more 7 min read
- Python | Writing to an excel file using openpyxl module Prerequisite : Reading an excel file using openpyxl Openpyxl is a Python library for reading and writing Excel (with extension xlsx/xlsm/xltx/xltm) files. The openpyxl module allows Python program to read and modify Excel files. For example, user might have to go through thousands of rows and pick o 3 min read
- Reading an excel file using Python openpyxl module Openpyxl is a Python library for reading and writing Excel (with extension xlsx/xlsm/xltx/xltm) files. The Openpyxl Module allows Python programs to read and modify Excel files. For example, users might have to go through thousands of rows and pick out a few handfuls of information to make small cha 3 min read
- How to delete one or more rows in excel using Openpyxl? Openpyxl is a Python library to manipulate xlsx/xlsm/xltx/xltm files. With Openpyxl you can create a new Excel file or a sheet and can also be used on an existing Excel file or sheet. Installation This module does not come built-in with Python. To install this type the below command in the terminal. 5 min read
- How to import an excel file into Python using Pandas? It is not always possible to get the dataset in CSV format. So, Pandas provides us the functions to convert datasets in other formats to the Data frame. An excel file has a '.xlsx' format. Before we get started, we need to install a few libraries. pip install pandas pip install xlrd For importing an 2 min read
- Formatting Cells using openpyxl in Python When it comes to managing Excel files programmatically, Python offers a powerful tool in the form of the openpyxl library. This library not only allows us to read and write Excel documents but also provides extensive support for cell formatting. From fonts and colors to alignment and borders, openpy 4 min read