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