Python Program for Arithmetic Operations in Excel File using openpyxl

Python Program for Arithmetic Operations in Excel File using openpyxl

by

Using openpyxl to read and write to an excel sheet

openpyxl Module:

openpyxl is a Python library that allows you to do various actions on Excel files such as reading, writing, mathematical operations, and graph plotting. Now we look at how to use openpyxl to execute various Arithmetic computations.

  • =SUM(cell1:cell2): Adds all the numbers in a cell range.
  • =PRODUCT(cell1:cell2): Multiplies all of the numbers in the cell range.
  • =AVERAGE(cell1:cell2): Returns the average (arithmetic mean) of all the numbers in the provided cell range.
  • =QUOTIENT(num_1,num_2): This function returns the integer part of a division.

Program for Arithmetic Operations in Excel File using openpyxl in Python

Example1

  • =SUM(cell1:cell2): Adds all the numbers in a cell range.

Approach:

  • Import openpyxl module using the import keyword.
  • Call the Workbook() function to create a workbook object and store it in a variable.
  • Choose the active sheet using the active attribute and store it in another variable.
  • Writing for the first 7 cells of the Excel by passing some random values to it.
  • Calculate the sum for the above 7 cells A1 to A7 using the ‘=SUM’ function.
  • Save the above Excel workbook using the save() function by passing the Excel file path to it.
  • The Exit of the Program.

Below is the implementation:

# Import openpyxl module using the import keyword
import openpyxl
   
# Call the Workbook() function to create a workbook object and store it in a variable.
work_book = openpyxl.Workbook()
  
# Choose the active sheet using the active attribute and store it in another variable.
actve_sheet = work_book.active

# Writing for the first 7 cells of the Excel by passing some random values to it
actve_sheet['A1'] = 10
actve_sheet['A2'] = 20
actve_sheet['A3'] = 30
actve_sheet['A4'] = 5
actve_sheet['A5'] = 40
actve_sheet['A6'] = 15
actve_sheet['A7'] = 120

# Calculate the sum for the above 7 cells A1 to A7 using the '=SUM' function
actve_sheet['A9'] = '= SUM(A1:A7)'

# Save the above Excel workbook using the save() function by passing the Excel file path to it.
work_book.save("demo_sum.xlsx")

Output:

Example2

  • =PRODUCT(cell1:cell2): Multiplies all of the numbers in the cell range.

Approach:

  • Import openpyxl module using the import keyword.
  • Call the Workbook() function to create a workbook object and store it in a variable.
  • Choose the active sheet using the active attribute and store it in another variable.
  • Writing for the first 7 cells of the Excel by passing some random values to it.
  • Calculate the product for the above 7 cells A1 to A7 using the ‘=PRODUCT’ function.
  • Save the above Excel workbook using the save() function by passing the Excel file path to it.
  • The Exit of the Program.

Below is the implementation:

# Import openpyxl module using the import keyword
import openpyxl
   
# Call the Workbook() function to create a workbook object and store it in a variable.
work_book = openpyxl.Workbook()
  
# Choose the active sheet using the active attribute and store it in another variable.
actve_sheet = work_book.active

# Writing for the first 7 cells of the Excel by passing some random values to it
actve_sheet['A1'] = 3
actve_sheet['A2'] = 5
actve_sheet['A3'] = 1
actve_sheet['A4'] = 2
actve_sheet['A5'] = 4
actve_sheet['A6'] = 3
actve_sheet['A7'] = 5

# Calculate the product for the above 7 cells A1 to A7 using the '=PRODUCT' function.
actve_sheet['A9'] = '= PRODUCT(A1:A7)'

# Save the above Excel workbook using the save() function by passing the Excel file path to it.
work_book.save("demo_product.xlsx")

Output:

Example3

  • =AVERAGE(cell1:cell2): Returns the average (arithmetic mean) of all the numbers in the provided cell range.

Approach:

  • Import openpyxl module using the import keyword.
  • Call the Workbook() function to create a workbook object and store it in a variable.
  • Choose the active sheet using the active attribute and store it in another variable.
  • Writing for the first 7 cells of the Excel by passing some random values to it.
  • Calculate the average(mean) for the above 7 cells A1 to A7 using the ‘=AVERAGE’ function.
  • Save the above Excel workbook using the save() function by passing the Excel file path to it.
  • The Exit of the Program.

Below is the implementation:

# Import openpyxl module using the import keyword
import openpyxl
   
# Call the Workbook() function to create a workbook object and store it in a variable.
work_book = openpyxl.Workbook()
  
# Choose the active sheet using the active attribute and store it in another variable.
actve_sheet = work_book.active

# Writing for the first 7 cells of the Excel by passing some random values to it
actve_sheet['A1'] = 100
actve_sheet['A2'] = 50
actve_sheet['A3'] = 250
actve_sheet['A4'] = 25
actve_sheet['A5'] = 75
actve_sheet['A6'] = 55
actve_sheet['A7'] = 350

# Calculate the average(mean) for the above 7 cells A1 to A7 using the '=AVERAGE' function.
actve_sheet['A9'] = '= AVERAGE(A1:A7)'

# Save the above Excel workbook using the save() function by passing the Excel file path to it.
work_book.save("demo_avg.xlsx")

Output:

Example4

  • =QUOTIENT(num_1,num_2): This function returns the integer part of a division.

Below is the implementation:

# Import openpyxl module using the import keyword
import openpyxl
   
# Call the Workbook() function to create a workbook object and store it in a variable.
work_book = openpyxl.Workbook()
  
# Choose the active sheet using the active attribute and store it in another variable.
actve_sheet = work_book.active

# Perform division operation for the random two numbers and get the quotient using the '=QUOTIENT' function.
# And store it in cell A1.
# Similarly do the same for the other cells
actve_sheet['A1'] = '= QUOTIENT(16, 4)'
actve_sheet['A2'] = '= QUOTIENT(100, 5)'
actve_sheet['A3'] = '= QUOTIENT(75, 15)'
actve_sheet['A4'] = '= QUOTIENT(24, 2)'

# Save the above Excel workbook using the save() function by passing the Excel file path to it.
work_book.save("demo_div.xlsx")

Output:

Also Read: Java Program to Convert String to Integer by Using Recursion

UBL Pivot Point Calculator

Leave a Comment