Access and Modify Excel Files using Python

The OpenPyXL package can be used to access and modify Excel files in Python.

In this short tutorial, you’ll see how to use this package to access and modify cells in Excel.

Steps to Access and Modify Excel Files using Python

(1) Installation

To install the OpenPyXL package, use the following command:

pip install openpyxl

(2) Load an Excel file

Load your existing Excel file.

For example, here we have an Excel file called “Products” (with a file extension of .xlsx) stored in “Test” folder:

Item	Price
Computer	700
Tablet	250
Printer	120
Laptop	1200

To load the Excel file (add “r” before the path to avoid unicode errors):

from openpyxl import load_workbook

# Path of Excel file
file_path = r"C:\Users\Ron\Desktop\Test\Products.xlsx"

# Load the Excel file
workbook = load_workbook(file_path)

(3) Access a Specific Sheet

To access a specific sheet, such as the default “Sheet1” sheet:

from openpyxl import load_workbook

# Path of Excel file
file_path = r"C:\Users\Ron\Desktop\Test\Products.xlsx"

# Load the Excel file
workbook = load_workbook(file_path) 

# Access a specific sheet
sheet = workbook["Sheet1"]

(4) Access Cell Values

To access the “A2” cell value:

from openpyxl import load_workbook

# Path of Excel file
file_path = r"C:\Users\Ron\Desktop\Test\Products.xlsx"

# Load the Excel file
workbook = load_workbook(file_path) 

# Access a specific sheet
sheet = workbook["Sheet1"]

# Access cell value
cell_value = sheet["A2"].value

# Print results
print(cell_value)

The result:

Computer

(5) Modify Cell Values

To modify the “A2” cell value from “Computer” to “Telescope” and then save the changes:

from openpyxl import load_workbook

# Path of Excel file
file_path = r"C:\Users\Ron\Desktop\Test\Products.xlsx"

# Load the Excel file
workbook = load_workbook(file_path) 

# Access a specific sheet
sheet = workbook["Sheet1"]

# Modify cell value
sheet["A2"] = "Telescope"

# Access the new cell value
cell_value = sheet["A2"].value
print(cell_value)

# Save results
workbook.save(file_path)

The new value for cell “A2” would be:

Telescope

Combining all the steps together:

from openpyxl import load_workbook

# Path of Excel file
file_path = r"C:\Users\Ron\Desktop\Test\Products.xlsx"

# Load the Excel file
workbook = load_workbook(file_path) 

# Access a specific sheet
sheet = workbook["Sheet1"]

# Access the old cell value
cell_value = sheet["A2"].value
print(f"Old Value: {cell_value}")

# Modify cell value
sheet["A2"] = "Telescope"

# Access the new cell value
cell_value = sheet["A2"].value
print(f"New Value: {cell_value}")

# Save results
workbook.save(file_path)

Visit the OpenPyXL Documentation to learn more about the usages of this package.