Python Academy · Lesson

Reading Workbooks

Open and read Excel files.

Reading Workbooks is a free Python Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Python Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Excel from Python

openpyxl is the standard library for reading and writing modern Excel files (the .xlsx format). It lets Python automate spreadsheet tasks that people otherwise do by hand.

This lesson focuses on opening files and reading their contents.

The Workbook Model

Excel files have a clear hierarchy that openpyxl mirrors:

Workbook the whole file
Worksheet one tab inside it
Cell a single A1-style box with a value

You navigate from workbook to worksheet to cell.

Opening a File

load_workbook('data.xlsx') reads a file and returns a workbook. For large files that you only read, pass read_only=True to stream rows and save memory.

Use data_only=True to read the last computed result of formula cells instead of the formula text.

Selecting a Sheet

wb.active gives the currently selected sheet. wb['Sheet1'] selects by name, and wb.sheetnames lists all tabs. Choosing the right sheet is the first step of any read.

sheetnames = ['Summary', 'Q1', 'Q2', 'Q3']
wanted = 'Q2'
if wanted in sheetnames:
    print('Selecting sheet:', wanted)
else:
    print('Not found, defaulting to', sheetnames[0])

Reading a Single Cell

Access a cell by its Excel address: ws['A1'].value, or by numbers with ws.cell(row=1, column=1).value. The .value attribute holds the actual data, which may be text, a number, a date, or None.

Column Letters and Numbers

Excel columns are letters (A, B, ... Z, AA), but openpyxl also uses numbers starting at 1. Converting between them is occasionally needed. Here is the column number from a letter.

def col_to_num(letters):
    num = 0
    for ch in letters.upper():
        num = num * 26 + (ord(ch) - ord('A') + 1)
    return num

for c in ['A', 'B', 'Z', 'AA']:
    print(c, '->', col_to_num(c))

Iterating Over Rows

ws.iter_rows(values_only=True) yields each row as a tuple of values. This is the cleanest way to read a table. The first row is usually the header.

You loop over rows just like any iterable.

rows = [
    ('Name', 'Age', 'City'),
    ('Ada', 36, 'London'),
    ('Linus', 54, 'Helsinki'),
]
header = rows[0]
for record in rows[1:]:
    print(dict(zip(header, record)))

Finding the Used Range

ws.max_row and ws.max_column tell you how far the data extends. ws.dimensions returns the range string like 'A1:C10'. Use these to avoid reading empty cells.

Reading a Range

You can slice a worksheet: ws['A1:C3'] returns a tuple of row tuples of cell objects. This is handy when you only need part of a sheet rather than every row.

Handling Empty Cells

Empty cells return None. Real spreadsheets are messy, so guard against missing values when reading, for example by substituting a default. This prevents crashes downstream.

raw_row = ('Ada', None, 'London')
clean = [v if v is not None else 'N/A' for v in raw_row]
print(clean)

Reading Across Multiple Sheets

A workbook may split data across tabs, for example one per month. Loop over wb.sheetnames, select each sheet, and read it with the same logic. Tag each row with its sheet name to keep track of origin.

This pattern consolidates many tabs into one combined dataset.

Quick Check

Test your reading skills.

Recap

You can now read Excel files:

Hierarchy: workbook > worksheet > cell
load_workbook with read_only and data_only options
Select sheets via wb.active, wb['Name'], wb.sheetnames
Read with .value, iter_rows(values_only=True), and check max_row / max_column
Guard against None for empty cells

Frequently asked questions

Is the “Reading Workbooks” lesson free?

Yes — the full text of “Reading Workbooks” is free to read here on the web, and the Python Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Python Academy course, upgrade to CoddyKit PRO.

What will I learn in “Reading Workbooks”?

Open and read Excel files. You practise Python Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Python Academy?

No prior experience is required. Python Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Reading Workbooks” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Python Academy lesson?

Yes. Every Python Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to Python Academy