Chapter 3: Looping Through Data in Rows and Columns

The true power of automation is unlocked when you can handle large amounts of data without writing code for every single cell. In this chapter, you'll learn one of the most fundamental concepts in programming: loops. We'll use loops to read and process entire datasets in your Excel files, row by row.

Getting Started: Our inventory_updated.xlsx File

We will use the inventory_updated.xlsx file from the last chapter. Our goal is to read the data for each product, calculate the total value (Quantity × Price) for each, and print it.

Finding the Size of Your Worksheet

Before we can loop through the rows, we need to know how many rows of data we have. openpyxl makes this easy with the worksheet.max_row attribute.

Let's start a new script. Create a file named process_inventory.py:

from openpyxl import load_workbook

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

# Get the total number of rows with data
max_rows = ws.max_row
print(f"The worksheet has {max_rows} rows.")

Run this. It should correctly report the number of rows in your sheet (which should be 3 if you've been following along).

Looping Through Rows

Now we can use a for loop to iterate through each row, from the second row (to skip the header) to the last row. Inside the loop, we'll read the product name, quantity, and price, then perform a calculation.

Update your process_inventory.py script:

from openpyxl import load_workbook

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

# Loop through all rows starting from the second row
# The range function's second argument is exclusive, so we add 1
for row_num in range(2, ws.max_row + 1):
    # Read the data from each cell in the current row
    product_name = ws.cell(row=row_num, column=1).value
    quantity = ws.cell(row=row_num, column=2).value
    price = ws.cell(row=row_num, column=3).value

    # Calculate the total value
    total_value = quantity * price

    print(f"Product: {product_name}, Total Value: {total_value}")

Run this script. It will automatically process every product in your sheet and print the calculated total value for each one. Imagine if your sheet had thousands of rows—this script would handle it in seconds!

Writing Data Back to Excel

Printing to the console is great for checking our work, but the real goal is to update the spreadsheet. Let's add a new column called "Total Value" to our sheet and write our calculated totals into it.

Here's the final version of process_inventory.py:

from openpyxl import load_workbook

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

# Add a header for our new column
ws['D1'] = "Total Value"

# Loop through all rows starting from the second row
for row_num in range(2, ws.max_row + 1):
    quantity_cell = ws.cell(row=row_num, column=2)
    price_cell = ws.cell(row=row_num, column=3)

    # Calculate the total value
    total_value = quantity_cell.value * price_cell.value

    # Get the cell for the new column and write the total value to it
    total_value_cell = ws.cell(row=row_num, column=4)
    total_value_cell.value = total_value

# Save the result to a new file
wb.save("inventory_with_totals.xlsx")

print("Successfully calculated totals and saved to 'inventory_with_totals.xlsx'")

Run the script. Open the new inventory_with_totals.xlsx file. You will see a new "Total Value" column with all the correct calculations filled in.

What You've Accomplished

You have learned the single most important technique for spreadsheet automation: looping. You can now process any number of rows in a worksheet, perform calculations on the data, and write the results back to the spreadsheet. This skill allows you to scale your automation from a few cells to massive datasets.

In the next chapter, we'll learn how to make our reports look professional by applying formatting and styles.