Chapter 2: Reading and Modifying Excel Files

Creating new files is a great start, but most of the time, you'll be working with existing spreadsheets. In this chapter, you'll learn how to open an Excel file, read data from its cells, and make modifications. This is the foundation for automating the updates of existing reports and datasets.

Getting Started: Your inventory.xlsx File

We will use the inventory.xlsx file that we created in the previous chapter. Make sure this file is in the same directory as your new Python script.

Loading an Existing Workbook

Instead of creating a new Workbook() object, we will use the load_workbook() function from openpyxl to open our existing file.

Create a new Python file named read_inventory.py and add the following code:

# 1. Import the load_workbook function
from openpyxl import load_workbook

# 2. Load the inventory.xlsx workbook
wb = load_workbook("inventory.xlsx")

# 3. Select the worksheet you want to work with
# You can select it by its name
ws = wb["Inventory"]

# 4. Read data from a specific cell
# Let's read the price of the laptop in cell C2
laptop_price = ws['C2'].value

print(f"The price of a laptop is: {laptop_price}")

Run this script. It will open the Excel file in the background, read the value from cell C2, and print it to your console.

Another Way to Access Cells

Besides using the "A1" notation, you can also access cells by their row and column number. This is incredibly useful when you need to work with data in loops, as we'll see in the next chapter. The worksheet.cell() method takes row and column numbers as arguments. Remember that rows and columns start from 1, not 0.

Let's modify our script to read the same cell using this method.

from openpyxl import load_workbook

wb = load_workbook("inventory.xlsx")
ws = wb["Inventory"]

# Access cell C2 using row=2, column=3
laptop_price_cell = ws.cell(row=2, column=3)
laptop_price = laptop_price_cell.value

print(f"Reading from row 2, column 3. The price is: {laptop_price}")

Modifying and Saving a Workbook

Reading data is useful, but automation often involves changing it. Let's say we had a price increase for laptops. We can read the old value, calculate the new one, and write it back into the cell.

Here's how to modify the laptop's price and save the changes to a new file to avoid overwriting our original data.

from openpyxl import load_workbook

wb = load_workbook("inventory.xlsx")
ws = wb["Inventory"]

# Get the cell for the laptop's price
laptop_price_cell = ws['C2']

# Let's say the price increased by 10%
new_price = laptop_price_cell.value * 1.10

# Assign the new value back to the cell
laptop_price_cell.value = new_price

# Save the changes to a new file
wb.save("inventory_updated.xlsx")

print("Successfully updated the price and saved to 'inventory_updated.xlsx'")

Run this script. A new file named inventory_updated.xlsx will appear. Open it, and you'll see the new, higher price for the laptop in cell C2.

What You've Accomplished

You now know how to open existing Excel files, read data from any cell using two different methods, and modify that data. You've also learned the important practice of saving your changes to a new file to preserve your original data.

These are essential skills for any automation task. In the next chapter, we'll learn how to work with many rows of data at once using loops.