Data Analysis with Python (pandas, polars, DuckDB) Module 3 – Loading data: CSV, Excel, JSON, Parquet and databases

Excel workbooks: sheets, headers, dates, writing

Read Excel workbooks into pandas: pick sheets, header rows and columns, repair merged cells, convert serial dates, choose an engine and write reports.

Beginner
30 minutes
Examples run with Python 3.14.8, pandas 3.0.6, numpy 2.5.3, openpyxl 3.1.5, xlsxwriter 3.2.9 and python-calamine 0.8.2
By MySmartCoPilot

What you will learn

Read one or many sheets with read_excel and choose an engine
Handle multi-row headers, merged cells, note rows and Excel serial dates
Write a multi-sheet workbook from several DataFrames
Explain which Excel features do not survive a round trip through pandas

Before you start

Reading messy CSV files with pandas

What you’ll learn · Before you start

What you will learn

Read one or many sheets with read_excel and choose an engine
Handle multi-row headers, merged cells, note rows and Excel serial dates
Write a multi-sheet workbook from several DataFrames
Explain which Excel features do not survive a round trip through pandas

Before you start

Reading messy CSV files with pandas

On this page

pd.read_excel turns one worksheet of an Excel file into a DataFrame, and sheet_name=None turns every worksheet into a dictionary of DataFrames. The work is rarely in the call itself. Office workbooks are laid out for people: a title merged across the top, a note, a header on the fourth row, a totals row at the bottom, group labels in merged cells and dates that are sometimes plain day numbers. A clean read tells pandas where the table really is, with header, usecols, skiprows, nrows and skipfooter, and then repairs what a layout for people leaves behind.

Writing goes the other way: DataFrame.to_excel and pd.ExcelWriter write one or many sheets. This lesson builds its workbooks in code, reads them back, measures the two main reading engines, and shows what a trip through pandas keeps and what it drops, including a formula trap that matters for security.

A workbook made for people

Every example builds the same small workbook with XlsxWriter, a library that writes .xlsx files: two worksheets, North and South, with a merged title, a note, a header on row 4, a few sales and a totals row with a formula. The helper is shown here once:

build_workbook.py, imported by the examples Python · build_workbook.py

"""Builds branch_sales.xlsx the way many office reports look: a merged title, a note, a header on row 4 and a totals row."""
import datetime as dt

import xlsxwriter

SALES = {
    "North": [("2026-04-01", "Tea", 120, 18.5), ("2026-04-01", "Coffee", 80, 22.0), ("2026-04-02", "Tea", 95, 18.5)],
    "South": [("2026-04-01", "Tea", 60, 19.0), ("2026-04-02", "Juice", 40, 30.0)],
}


def build(path="branch_sales.xlsx"):
    book = xlsxwriter.Workbook(path)
    bold = book.add_format({"bold": True})
    title = book.add_format({"bold": True, "font_size": 14, "align": "center"})
    money = book.add_format({"num_format": "#,##0.00"})
    day = book.add_format({"num_format": "dd-mm-yyyy"})
    for branch, rows in SALES.items():
        sheet = book.add_worksheet(branch)
        sheet.merge_range("A1:D1", f"Branch sales: {branch}", title)
        sheet.write("A2", "Prepared by the accounts team. Figures exclude tax.")
        sheet.write_row("A4", ["Date", "Item", "Cups", "Price"], bold)
        for r, (when, item, cups, price) in enumerate(rows, start=4):
            sheet.write_datetime(r, 0, dt.datetime.fromisoformat(when), day)
            sheet.write(r, 1, item)
            sheet.write_number(r, 2, cups)
            sheet.write_number(r, 3, price, money)
        last = 4 + len(rows)
        sheet.write(last, 1, "Total", bold)
        sheet.write_formula(last, 2, f"=SUM(C5:C{last})")
    book.close()
    return path

Read without options, pandas takes the first worksheet and uses its first row as the header:

No options: the title becomes the header Python · naive_excel.py

import pandas as pd

from build_workbook import build

build("branch_sales.xlsx")

# No options: the first sheet only, with row 1 taken as the header.
sales = pd.read_excel("branch_sales.xlsx")
pd.set_option("display.width", 120)
pd.set_option("display.max_colwidth", 28)
print(sales)
print(sales.columns.tolist())

Output

           Branch sales: North Unnamed: 1 Unnamed: 2 Unnamed: 3
0  Prepared by the accounts...        NaN        NaN        NaN
1                          NaN        NaN        NaN        NaN
2                         Date       Item       Cups      Price
3          2026-04-01 00:00:00        Tea        120       18.5
4          2026-04-01 00:00:00     Coffee         80         22
5          2026-04-02 00:00:00        Tea         95       18.5
6                          NaN      Total          0        NaN
['Branch sales: North', 'Unnamed: 1', 'Unnamed: 2', 'Unnamed: 3']

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 naive_excel.py

The title became the name of the first column, the other columns are Unnamed: 1 to Unnamed: 3, and the real header sits in the data as an ordinary row. The totals row has 0 where the sum of cups should be. That zero is not a bug in pandas: XlsxWriter does not calculate formulas, and it stores 0 as each formula’s result until a spreadsheet program opens the file and recalculates it. pandas reads the stored result, never the formula.

Which rows of an office worksheet read_excel keeps, and the options that decide it

Text description of the diagram

The diagram shows the eight rows of the worksheet North of branch_sales.xlsx and what read_excel does with each.

Row 1 holds a merged title, row 2 a note for people and row 3 is empty. They sit above the header, so header=3 skips them (pandas counts rows from 0, so the fourth row is row 3).
Row 4 holds the column names Date, Item, Cups and Price: header=3 makes them the DataFrame's columns.
Rows 5 to 7 hold one sale each and become the DataFrame's rows. usecols="A:D" keeps columns A to D only.
Row 8 holds the word Total and a SUM formula. skipfooter=1 drops it. Read without that option, its formula would arrive as 0, because the program that wrote the file never calculated it.

Find the table: header, usecols and skipfooter

header is the row that holds the column names, counted from 0, so the fourth row is header=3; everything above it is skipped. usecols="A:D" keeps columns A to D, in Excel’s own letters. skipfooter=1 drops the last row of each sheet, here the totals. With sheet_name=None, the same settings apply to every worksheet:

Every sheet, the right header, no totals row Python · careful_excel.py

import pandas as pd

from build_workbook import build

build("branch_sales.xlsx")

sheets = pd.read_excel(
    "branch_sales.xlsx",
    sheet_name=None,  # every sheet, as a dict of DataFrames keyed by sheet name
    header=3,  # the header is on row 4 (counting from 0, it is row 3)
    usecols="A:D",
    skipfooter=1,  # drop the totals row at the bottom of each sheet
)
print({name: frame.shape for name, frame in sheets.items()})

sales = pd.concat(sheets, names=["branch", "row"]).reset_index(level="branch").reset_index(drop=True)
print(sales)
print(sales.dtypes)
print(f"\nCups sold: {sales['Cups'].sum()}")

Output

{'North': (3, 4), 'South': (2, 4)}
  branch       Date    Item  Cups  Price
0  North 2026-04-01     Tea   120   18.5
1  North 2026-04-01  Coffee    80   22.0
2  North 2026-04-02     Tea    95   18.5
3  South 2026-04-01     Tea    60   19.0
4  South 2026-04-02   Juice    40   30.0
branch               str
Date      datetime64[us]
Item                 str
Cups               int64
Price            float64
dtype: object

Cups sold: 395

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 careful_excel.py

sheet_name=None returned a dictionary keyed by worksheet name. pd.concat with a dictionary uses its keys as an extra index level, which reset_index turns into the branch column, so each row still says which sheet it came from. The dates arrived as datetime64[us], because they were real Excel dates. The totals were dropped deliberately; recompute them from the data, where you can check them.

Other ways to cut the table: skiprows skips a number of rows, or a list of row numbers, nrows stops after that many data rows, and sheet_name also accepts a position (0 is the first worksheet) or a list of names and positions.

Two header rows and merged cells

Reports often put a group heading above the column names, such as a term above each subject. header=[0, 1] reads both rows into a two-level column index (a MultiIndex), and joining the two levels gives flat names that are easy to type. Merged cells are the other common layout: a region label merged down the rows of its branches. Only the top cell of a merged range holds the value, so pandas reads NaN for the other rows, and ffill() (forward fill) copies the label down:

A two-row header, and a label merged down three rows Python · headers_and_merges.py

import pandas as pd
import xlsxwriter

book = xlsxwriter.Workbook("results.xlsx")
centre = book.add_format({"bold": True, "align": "center"})

marks = book.add_worksheet("Marks")  # two header rows: the term, then the subject
marks.merge_range("B1:C1", "Term 1", centre)
marks.merge_range("D1:E1", "Term 2", centre)
marks.write_row("A2", ["Student", "Maths", "Physics", "Maths", "Physics"], centre)
marks.write_row("A3", ["Asha", 78, 81, 84, 79])
marks.write_row("A4", ["Ben", 66, 72, 70, 75])

branches = book.add_worksheet("Branches")  # a region label merged down the rows it covers
branches.write_row("A1", ["Region", "Branch", "Sales"])
branches.merge_range("A2:A4", "North")
branches.merge_range("A5:A6", "South")
for row, (branch, sales) in enumerate([("Leeds", 120), ("York", 90), ("Hull", 75), ("Bath", 60), ("Kent", 85)], start=1):
    branches.write_row(row, 1, [branch, sales])
book.close()

two_rows = pd.read_excel("results.xlsx", sheet_name="Marks", header=[0, 1], index_col=0)
print(two_rows, end="\n\n")
flat = two_rows.copy()
flat.columns = [f"{term}_{subject}".lower().replace(" ", "") for term, subject in two_rows.columns]
print(flat, end="\n\n")

merged = pd.read_excel("results.xlsx", sheet_name="Branches")
print(merged, end="\n\n")
merged["Region"] = merged["Region"].ffill()  # the value sits only in the top cell of a merged range
print(merged.groupby("Region")["Sales"].sum())

Output

        Term 1         Term 2        
Student  Maths Physics  Maths Physics
Asha        78      81     84      79
Ben         66      72     70      75

      term1_maths  term1_physics  term2_maths  term2_physics
Asha           78             81           84             79
Ben            66             72           70             75

  Region Branch  Sales
0  North  Leeds    120
1    NaN   York     90
2    NaN   Hull     75
3  South   Bath     60
4    NaN   Kent     85

Region
North    285
South    145
Name: Sales, dtype: int64

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 headers_and_merges.py

Fill merged labels before you group or filter. Grouping the unfilled frame would count York, Hull and Kent under a missing region and the regional totals would be wrong.

Codes and mixed columns

Excel stores each cell as a number or as text, and one column can mix both. pandas then gives the column the generic object dtype with Python values of different types inside, which breaks sorting, joining and saving to Parquet later. Product codes, roll numbers and postal codes are the usual victims:

A code column with numbers and text Python · mixed_codes.py

import pandas as pd
import xlsxwriter

book = xlsxwriter.Workbook("stock.xlsx")
sheet = book.add_worksheet()
sheet.write_row(0, 0, ["code", "qty"])
for row, (code, qty) in enumerate([(1001, 5), ("A-17", 2), (1002, 1), ("007", 1)], start=1):
    sheet.write(row, 0, code)  # numbers are stored as numbers, the rest as text
    sheet.write(row, 1, qty)
book.close()

stock = pd.read_excel("stock.xlsx")
print(stock["code"].dtype, [type(v).__name__ for v in stock["code"]])

fixed = pd.read_excel("stock.xlsx", dtype={"code": "str"})
print(fixed["code"].dtype, fixed["code"].tolist())

Output

object ['int', 'str', 'int', 'str']
str ['1001', 'A-17', '1002', '007']

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 mixed_codes.py

dtype={"code": "str"} makes every value text, as in read_csv. It cannot bring back zeros that were never stored: when a system wrote 007 into the sheet as the number 7, the file holds 7. If such codes have a fixed width, .str.zfill(width) restores them; if they do not, ask for a better export.

Dates stored as day numbers

Excel stores a date as the number of days since a starting point, and shows that number with a date format. pandas converts real date cells for you, as above. Columns copied from other systems often hold the bare numbers, though, and then you convert them with pd.to_datetime(..., unit="D", origin="1899-12-30"):

Day numbers to dates in both date systems Python · serial_dates.py

import pandas as pd

# Exports from other systems often hold Excel dates as plain day numbers.
serials = pd.Series([45200, 46299, None], name="paid_on")

# The 1900 date system, Excel's default: count days from 1899-12-30.
dates = pd.to_datetime(serials, unit="D", origin="1899-12-30")
print(dates.dt.strftime("%Y-%m-%d").tolist())

# The 1904 date system of old Mac workbooks counts from 1904-01-01,
# so the same day has a serial number 1,462 lower.
print(pd.to_datetime(40729, unit="D", origin="1899-12-30").strftime("%Y-%m-%d"))
print(pd.to_datetime(39267, unit="D", origin="1904-01-01").strftime("%Y-%m-%d"))

Output

['2023-10-01', '2026-10-04', nan]
2011-07-05
2011-07-05

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 serial_dates.py

The origin is 1899-12-30, two days before the year Excel’s numbering starts in, because Excel counts a leap day that the year 1900 never had. Microsoft keeps that leap day for compatibility with Lotus 1-2-3, so the origin is right for every date after February of that year.
A workbook can use the 1904 date system instead, which Microsoft describes as the default of early Excel for Mac. Its numbers start in 1904 and are 1,462 lower for the same day, so a column converted with the wrong origin is off by about four years. Ask which system the source uses whenever dates look shifted.
None and empty cells become NaT, the missing value of dates.

Engines: calamine and openpyxl

pandas reads Excel files through an engine. For .xlsx it uses openpyxl unless you say otherwise. Calamine, a reader written in Rust and used through the python-calamine package, also reads .xls, .xlsb and .ods, and pandas uses it only when you pass engine="calamine". Both read the same workbook here:

The same 30,000-row workbook through both engines Python · engine_timing.py

import time

import numpy as np
import pandas as pd

rng = np.random.default_rng(3)
n = 30_000
orders = pd.DataFrame({
    "order_id": np.arange(1, n + 1),
    "branch": np.array(["Leeds", "Lima", "Pune", "Riga"])[rng.integers(0, 4, n)],
    "cups": rng.integers(1, 6, n),
    "price": rng.integers(150, 400, n) / 10,
})
orders.to_excel("orders.xlsx", index=False, engine="xlsxwriter")

for engine in ["calamine", "openpyxl"]:
    start = time.perf_counter()
    back = pd.read_excel("orders.xlsx", engine=engine)
    print(f"{engine:<9} {time.perf_counter() - start:.2f} s, {len(back):,} rows, same data: {back.equals(orders)}")

Output

calamine  0.68 s, 30,000 rows, same data: True
openpyxl  4.64 s, 30,000 rows, same data: True

This output changes from run to run: the times differ on every run and on every computer

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 engine_timing.py

Both engines returned exactly the data that was written, and calamine was several times faster. Use calamine for reading big workbooks or many files. openpyxl remains the engine for working with a workbook as a workbook, styles, comments and all, and it can write.

Excel engines in pandas
Criterion	calamine	openpyxl	XlsxWriter
Reads	xlsx, xls, xlsb, ods	xlsx, xlsm	No
Writes	No	Yes	Yes
Default	Never	Reading	Writing
Speed here	Fastest	Slower	Not timed
When to choose	Big reads	Editing a file	Formatted reports

Writing a workbook

DataFrame.to_excel writes one sheet. To write several, open a pd.ExcelWriter and give each sheet a unique name; the file is saved when the with block ends. The writer also exposes the engine’s own objects, so you can format the result without leaving pandas:

A summary sheet and a detail sheet Python · write_report.py

import pandas as pd
from openpyxl import load_workbook

sales = pd.DataFrame({
    "branch": ["North", "North", "South", "South"],
    "item": ["Tea", "Coffee", "Tea", "Juice"],
    "cups": [215, 80, 60, 40],
    "revenue": [3977.5, 1760.0, 1140.0, 1200.0],
})
summary = sales.groupby("branch", as_index=False)[["cups", "revenue"]].sum()

with pd.ExcelWriter("report.xlsx", engine="xlsxwriter") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False)
    sales.to_excel(writer, sheet_name="Detail", index=False, freeze_panes=(1, 0))
    money = writer.book.add_format({"num_format": "#,##0.00"})
    for name in ["Summary", "Detail"]:
        sheet = writer.sheets[name]
        sheet.autofit()
        column = "C:C" if name == "Summary" else "D:D"
        sheet.set_column(column, 12, money)

with pd.ExcelWriter("check.xlsx") as plain:  # no engine named: pandas picks XlsxWriter when it is installed
    print("default writer:", plain.engine)

book = load_workbook("report.xlsx")
print(book.sheetnames)
print("Detail frozen at", book["Detail"].freeze_panes)
print(pd.read_excel("report.xlsx", sheet_name="Summary"))

Output

default writer: xlsxwriter
['Summary', 'Detail']
Detail frozen at A2
  branch  cups  revenue
0  North   295   5737.5
1  South   100   2340.0

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 write_report.py

Without an engine, pandas picked XlsxWriter in this run because it is installed; the writer’s engine attribute says which one is in use.
index=False leaves out the row index, which is usually a meaningless 0, 1, 2 column in a report.
freeze_panes=(1, 0) keeps the header row in view while scrolling.
writer.book and writer.sheets are XlsxWriter’s workbook and worksheets: autofit() sizes the columns and set_column gives the money column a number format.
Opening an existing path with pd.ExcelWriter replaces the file. mode="a" with openpyxl appends sheets to an existing workbook instead.

A security trap: text that starts with =

Spreadsheets treat a cell that starts with = as a formula. Both writing engines do the same with text: a value such as =1+1 typed into a feedback form becomes a live formula in the file you send. OWASP calls the attack built on this “CSV injection”, and the formulas can do more than arithmetic:

User text written as formulas, and the fix Python · formulas_on_write.py

import pandas as pd
from openpyxl import load_workbook

# Text typed by users, such as feedback comments or product names.
comments = pd.DataFrame({"comment": ["Great tea", "=1+1", "+91 98765 43210", "@home"]})


def cell_types(path):
    sheet = load_workbook(path).active
    return [(cell.value, "formula" if cell.data_type == "f" else "text") for (cell,) in sheet.iter_rows(min_row=2)]


for engine in ["xlsxwriter", "openpyxl"]:
    comments.to_excel(f"{engine}.xlsx", index=False, engine=engine)
    print(f"{engine:<10}", cell_types(f"{engine}.xlsx"))
print("read back:", pd.read_excel("xlsxwriter.xlsx")["comment"].tolist())

with pd.ExcelWriter("safe.xlsx", engine="xlsxwriter", engine_kwargs={"options": {"strings_to_formulas": False}}) as writer:
    comments.to_excel(writer, index=False)
print("safe:      ", cell_types("safe.xlsx"))

Output

xlsxwriter [('Great tea', 'text'), ('=1+1', 'formula'), ('+91 98765 43210', 'text'), ('@home', 'text')]
openpyxl   [('Great tea', 'text'), ('=1+1', 'formula'), ('+91 98765 43210', 'text'), ('@home', 'text')]
read back: ['Great tea', 0, '+91 98765 43210', '@home']
safe:       [('Great tea', 'text'), ('=1+1', 'text'), ('+91 98765 43210', 'text'), ('@home', 'text')]

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 formulas_on_write.py

With XlsxWriter, the option strings_to_formulas: False, passed through engine_kwargs, writes every string as text. The XlsxWriter documentation lists it among the workbook options; its default is on. Values that start with + or @ stayed text in both engines here, but OWASP lists +, - and @ as dangerous first characters as well, because spreadsheet programs can read them as the start of a formula when people import or edit the data. Its page gives the escaping rules for files that people will open.

What a round trip through pandas loses

pandas reads values, not workbooks. A workbook with formulas, comments, a drop-down list, a bold header and a wide column, read into a DataFrame and written back, keeps only the values:

Read a budget workbook and write it back Python · round_trip.py

import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.comments import Comment
from openpyxl.styles import Font
from openpyxl.worksheet.datavalidation import DataValidation

# A small budget workbook with the features people add in Excel.
book = Workbook()
sheet = book.active
sheet.append(["item", "qty", "unit_price", "cost", "status"])
for cell in sheet[1]:
    cell.font = Font(bold=True)
sheet.append(["Chairs", 12, 1500, "=B2*C2", "approved"])
sheet.append(["Desks", 4, 5200, "=B3*C3", "pending"])
sheet["A2"].comment = Comment("Bulk order", "accounts")
choices = DataValidation(type="list", formula1='"approved,pending,rejected"')
sheet.add_data_validation(choices)
choices.add("E2:E3")
sheet.column_dimensions["A"].width = 30
book.save("budget.xlsx")

budget = pd.read_excel("budget.xlsx")
print(budget, end="\n\n")
budget.to_excel("budget_copy.xlsx", index=False)


def features(path):
    s = load_workbook(path).active
    cells = [c for row in s.iter_rows() for c in row]
    return {
        "formulas": sum(c.data_type == "f" for c in cells),
        "comments": sum(c.comment is not None for c in cells),
        "validations": len(s.data_validations.dataValidation),
        "bold header": bool(s["A1"].font.bold),
        "width of column A": s.column_dimensions["A"].width,
    }


print("original:", features("budget.xlsx"))
print("copy:    ", features("budget_copy.xlsx"))

Output

     item  qty  unit_price  cost    status
0  Chairs   12        1500   NaN  approved
1   Desks    4        5200   NaN   pending

original: {'formulas': 2, 'comments': 1, 'validations': 1, 'bold header': True, 'width of column A': 30.0}
copy:     {'formulas': 0, 'comments': 0, 'validations': 0, 'bold header': False, 'width of column A': 13.0}

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 round_trip.py

Formulas arrive as their stored results. This workbook was written by openpyxl, which does not calculate either, so cost is NaN; a workbook last saved by Excel would give the calculated numbers, but never the formulas.
Comments, data validation (drop-down lists), fonts and column widths are gone, and so are colours, conditional formats, charts, pivot tables and named ranges.
Merged cells come back unmerged, with the value in the top cell only.

When a workbook must keep its formatting, edit it with openpyxl directly and write only the cells you change, or keep the layout in a template and fill it in.

Your turn: three terms in one table

A college office sends a workbook with one sheet per term. Write load_terms(path) so that it returns one table with the columns term, roll_no, name, paid_on and amount. The three sheets came from two different systems, so they differ:

exercise/make_terms.py (builds the workbook) Python · exercise/make_terms.py

"""Builds fees_by_term.xlsx: three sheets from two different office systems."""
import datetime as dt

import xlsxwriter

ROWS = {
    "Term 1": [("0042", "Anil", "2026-04-06", 1200.0), ("0107", "Bea", "2026-04-07", 1200.0), ("0215", "Chidi", "2026-04-09", 950.5)],
    "Term 2": [("0042", "Anil", "2026-08-03", 1250.0), ("0107", "Bea", "2026-08-05", 1325.0), ("0215", "Chidi", "2026-08-11", 1250.0)],
    "Term 3": [(42, "Anil", 46357, 1250.0), (107, "Bea", 46358, 1250.0), (215, "Chidi", 46365, 1400.0)],
}


def build(path="fees_by_term.xlsx"):
    book = xlsxwriter.Workbook(path)
    day = book.add_format({"num_format": "dd-mm-yyyy"})
    for term, rows in ROWS.items():
        sheet = book.add_worksheet(term)
        sheet.write("A1", f"Fees received: {term}")
        first = 1
        if term == "Term 2":
            sheet.write("A2", "Note: late fees are included from this term.")
            first = 2
        sheet.write_row(first, 0, ["roll_no", "name", "paid_on", "amount"])
        for r, (roll, name, paid, amount) in enumerate(rows, start=first + 1):
            sheet.write(r, 0, roll)  # Term 3's system stores roll numbers as numbers
            sheet.write(r, 1, name)
            if isinstance(paid, str):
                sheet.write_datetime(r, 2, dt.datetime.fromisoformat(paid), day)
            else:
                sheet.write_number(r, 2, paid)  # and dates as Excel day numbers
            sheet.write_number(r, 3, amount)
    book.close()
    return path

exercise/load_terms.py (your starting point) Python · exercise/load_terms.py

import pandas as pd


def load_terms(path):
    """Return every term's payments as one table with a term column."""
    return pd.read_excel(path)  # only the first sheet, and its title row taken as the header

exercise/check_terms.py, run on the starting point Python · exercise/check_terms.py

import pandas as pd

from load_terms import load_terms
from make_terms import build

fees = load_terms(build("fees_by_term.xlsx"))


def check(name, test):
    try:
        ok = bool(test())
    except Exception:  # a missing column, for example
        ok = False
    print("PASS" if ok else "FAIL", name)
    return ok


results = [
    check("9 payments", lambda: len(fees) == 9),
    check("columns term, roll_no, name, paid_on, amount", lambda: list(fees.columns) == ["term", "roll_no", "name", "paid_on", "amount"]),
    check("3 payments in each term", lambda: fees["term"].value_counts().sort_index().tolist() == [3, 3, 3]),
    check("roll numbers are 4-character text", lambda: fees["roll_no"].map(lambda v: isinstance(v, str) and len(v) == 4).all()),
    check("paid_on holds dates", lambda: pd.api.types.is_datetime64_any_dtype(fees["paid_on"])),
    check("Term 3 dates are in its own term", lambda: fees.loc[fees["term"] == "Term 3", "paid_on"].min() == pd.Timestamp("2026-12-01")),
    check("amounts add up to 11,075.50", lambda: round(fees["amount"].sum(), 2) == 11075.50),
]
print(f"{sum(results)} of {len(results)} checks pass")

Output

FAIL 9 payments
FAIL columns term, roll_no, name, paid_on, amount
FAIL 3 payments in each term
FAIL roll numbers are 4-character text
FAIL paid_on holds dates
FAIL Term 3 dates are in its own term
FAIL amounts add up to 11,075.50
0 of 7 checks pass

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 check_terms.py

Save the three files side by side; load_terms.py is the only one you edit. The exercise is solved when python check_terms.py reports 7 of 7 checks pass. Some help: read each sheet with its own header row (Term 2 has a note above its header), read roll_no as text, convert Term 3’s day numbers with the 1900 origin, and pad Term 3’s roll numbers back to four characters with str.zfill.

Interview questions

Fresher · A colleague’s pandas script reads a report workbook and the totals row shows 0 instead of the sum. Why?

pandas never evaluates formulas. For each formula cell it reads the result that was stored in the file when the file was last saved. Excel calculates formulas and saves their results, but libraries that write workbooks, such as XlsxWriter and openpyxl, do not calculate anything: XlsxWriter stores 0 as every formula’s result, and openpyxl stores none, which pandas reads as NaN. A workbook produced by a script and never opened in Excel therefore shows 0 or a missing value. Opening and saving it in a spreadsheet program fills in the results. The better fix is to compute the total in pandas from the data rows, and to skip the totals row when reading with skipfooter or by filtering it out, so the figure can be checked.

Key takeaways

read_excel reads one sheet; sheet_name=None reads all of them into a dictionary keyed by sheet name.
Find the table with header (counted from 0), usecols="A:D", skiprows, nrows and skipfooter, then drop or recompute totals rows.
header=[0, 1] reads two header rows into a MultiIndex; merged labels arrive in the top cell only, so ffill() them before grouping.
Read codes with dtype={"code": "str"}; zeros that were stored as numbers are gone and need zfill.
Excel day numbers convert with unit="D", origin="1899-12-30"; the 1904 date system is 1,462 days apart.
calamine reads faster and reads more formats; openpyxl edits workbooks; XlsxWriter writes formatted reports.
Text that starts with = is written as a formula by both writing engines: turn off strings_to_formulas for user-entered text.
A round trip keeps values only: formulas, comments, validation, styles and merges are lost.

References

pandas.read_excel (pandas API reference) (pandas development team (NumFOCUS))
pandas.DataFrame.to_excel (pandas API reference) (pandas development team (NumFOCUS))
IO tools: Excel files (pandas user guide) (pandas development team (NumFOCUS))
openpyxl documentation (openpyxl project)
XlsxWriter: Working with Formulas (XlsxWriter project)
XlsxWriter: The Workbook Class (constructor options) (XlsxWriter project)
python-calamine (python-calamine project)
Date systems in Excel (Microsoft)
Excel incorrectly assumes that the year 1900 is a leap year (Microsoft)
CSV Injection (OWASP Foundation)

Report a problem with this lesson

In this module: Loading data: CSV, Excel, JSON, Parquet and databases

Reading messy CSV files with pandas Free: 35 minutes
Excel workbooks: sheets, headers, dates, writing Free: 30 minutes
JSON, APIs and nested records Free: 30 minutes
Parquet, Arrow and choosing a file format Free: 30 minutes
Reading from SQL databases safely Free: 25 minutes

Your country

Excel workbooks: sheets, headers, dates, writing

What you will learn

Before you start

What you will learn

Before you start

A workbook made for people

Find the table: header, usecols and skipfooter

Two header rows and merged cells

Codes and mixed columns

Dates stored as day numbers

Engines: calamine and openpyxl

Writing a workbook

A security trap: text that starts with =

What a round trip through pandas loses

Your turn: three terms in one table

Check yourself

Interview questions

Key takeaways

References

Related tools