Arithmetic operations in excel file using openpyxl in Python

Python can help us work with Excel files directly from the Python environment using the openpyxl module. We can refer to individual cells or ranges of cells in Excel and apply arithmetic operators on them. The results of these operations can be stored in specific cells whose location we define in our Python program.

In the examples below, we perform various arithmetic operations using built-in Excel functions like SUM, AVERAGE, PRODUCT, and COUNT. We use the openpyxl module to create a workbook, store values in predefined cells, apply functions on those cells, and save the results to other cells ?

Installing openpyxl

First, install the openpyxl module if you haven't already ?

pip install openpyxl

Basic Arithmetic Operations Example

Let's create an Excel file with sample data and apply various arithmetic operations ?

import openpyxl

# Create a new workbook
excel = openpyxl.Workbook()
excel_file = excel.active

# Add sample data in different rows
excel_file['A1'] = 50
excel_file['B1'] = 100
excel_file['C1'] = 150
excel_file['D1'] = 200
excel_file['E1'] = 250

excel_file['A3'] = 20
excel_file['B3'] = 40
excel_file['C3'] = 60
excel_file['D3'] = 80
excel_file['E3'] = 100

excel_file['A5'] = 2
excel_file['B5'] = 4
excel_file['C5'] = 6
excel_file['D5'] = 9
excel_file['E5'] = 15

excel_file['A7'] = 4
excel_file['B7'] = 11
excel_file['C7'] = 27
excel_file['D7'] = 40
excel_file['E7'] = 75

# Add labels and formulas
excel_file['F1'] = 'Sum(A1 to E1)'
excel_file['G1'] = '= SUM(A1:E1)'

excel_file['F3'] = 'Average(A3 to E3)'
excel_file['G3'] = '= AVERAGE(A3:E3)'

excel_file['F5'] = 'Product(A5 to E5)'
excel_file['G5'] = '= PRODUCT(A5:E5)'

excel_file['F7'] = 'Count(A7 to E7)'
excel_file['G7'] = '= COUNT(A7:E7)'

# Save the workbook
excel.save("Arithmetic_operations.xlsx")
print("Excel file created successfully!")

Excel file created successfully!

Reading and Displaying Formula Results

You can also read the Excel file and display the calculated results ?

import openpyxl

# Load the existing workbook
workbook = openpyxl.load_workbook("Arithmetic_operations.xlsx")
sheet = workbook.active

# Read and display the data and formulas
print("Data and Results:")
print(f"Row 1 data: {[sheet[f'{col}1'].value for col in ['A','B','C','D','E']]}")
print(f"Sum formula: {sheet['G1'].value}")

print(f"\nRow 3 data: {[sheet[f'{col}3'].value for col in ['A','B','C','D','E']]}")
print(f"Average formula: {sheet['G3'].value}")

print(f"\nRow 5 data: {[sheet[f'{col}5'].value for col in ['A','B','C','D','E']]}")
print(f"Product formula: {sheet['G5'].value}")

print(f"\nRow 7 data: {[sheet[f'{col}7'].value for col in ['A','B','C','D','E']]}")
print(f"Count formula: {sheet['G7'].value}")

Data and Results:
Row 1 data: [50, 100, 150, 200, 250]
Sum formula: = SUM(A1:E1)

Row 3 data: [20, 40, 60, 80, 100]
Average formula: = AVERAGE(A3:E3)

Row 5 data: [2, 4, 6, 9, 15]
Product formula: = PRODUCT(A5:E5)

Row 7 data: [4, 11, 27, 40, 75]
Count formula: = COUNT(A7:E7)

Common Excel Functions

Output

Running the code creates an Excel file with the following structure ?

Conclusion

Using openpyxl, you can create Excel files with arithmetic formulas that automatically calculate results. This approach is useful for generating reports, performing data analysis, and creating dynamic spreadsheets programmatically. The Excel formulas will be evaluated when the file is opened in Excel applications.