Skip to content
Tutorials Center
My learning

Topics

Lesson 65 of 149 ยท Files and Errors

Excel Files with openpyxl

Read and write Excel .xlsx files with openpyxl. Create workbooks, fill cells, add formulas, style headers, set number formats, add charts and use pandas.

Sooner or later someone hands you an Excel file: monthly sales, a list of students, a budget. Business runs on spreadsheets, so being able to read them, fix them, and produce them from code is one of the most practical Python skills. You already know how to handle CSV files, which are plain text. Excel's .xlsx format is different: it is a zip archive of XML files that holds multiple sheets, formulas, colors, number formats and charts.

The most popular library for working with it is openpyxl. In this lesson you'll create a workbook, write and read cells, use formulas, style a report, add a chart, and combine openpyxl with pandas. Everything here runs live, because the examples build workbooks in memory.

Installing. openpyxl is not part of the standard library. On your computer, install it with python -m pip install openpyxl (ideally inside a virtual environment). The live examples install it first with micropip, the in-browser equivalent of pip. They keep workbooks in memory with io.BytesIO instead of saving to disk, because the browser has no real file system. On your computer you would write wb.save("report.xlsx") and load_workbook("report.xlsx") with a path.

Workbooks, sheets and cells

The model has three levels. A workbook is the whole file. It contains one or more worksheets (the tabs). A worksheet is a grid of cells, each addressed like "B3" (column B, row 3) or by numbers with ws.cell(row=3, column=2). Both rows and columns count from 1.

Python Create a workbook and read it back

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Key points from that example:

  • wb.active is the sheet that's open when the file is opened; wb["Sales"] picks one by name, and wb.sheetnames lists them.
  • ws.append(list) writes the next free row, which is the easiest way to write tabular data.
  • ws.iter_rows(min_row=2, values_only=True) yields each row as a tuple of plain values. Without values_only=True you get cell objects (with .value, .row, .column, .number_format and so on), which you need when you want styles.
  • Numbers come back as int or float, text as str, empty cells as None, dates as datetime. A cell holding 10 stays a number: Excel stores types, so you do not convert strings as you would with CSV.

load_workbook() and save() can destroy things. openpyxl keeps cell values and most formatting, but it drops what it doesn't understand: existing charts and shapes are not kept, images survive only if Pillow is installed, and macros are lost unless you pass keep_vba=True and use a .xlsm name. And wb.save("name.xlsx") silently overwrites any existing file of that name. Always work on a copy of an important workbook.

Formulas: written, not calculated

You can store a formula as text beginning with =. Excel (or LibreOffice) calculates it when the file is opened, but openpyxl never calculates anything. This surprises many people:

Python Formulas are stored as text

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

data_only=True only works for files that a spreadsheet program has saved, because Excel stores each formula's last result next to the formula. Two rules follow. First, if you need a number in Python, compute it in Python; write the formula too if the person opening the file should see how it's derived. Second, never load with data_only=True and then save: you would replace every formula with its cached value.

Styling a report

Plain data is fine for machines. For people, style matters: bold headers, readable widths, number formats and a frozen header row. Styles are objects you assign to a cell:

Python Fonts, fills, number formats and widths

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A number format changes only how a value looks. The cell still holds the number 0.104; '0.0%' displays it as 10.4%. That means you store real numbers and format them, rather than writing text such as "10.4%", which can't be summed. Other useful formats include '0.00', '$#,##0.00', and 'yyyy-mm-dd' for dates.

Colors are hex strings without a # ("305496"). Python datetime and date objects written to a cell are stored as real Excel dates, with a default date format applied.

Reading messy real-world sheets

Files made by people have surprises: title rows above the table, blank rows, merged cells, numbers stored as text. A few habits help:

  • Find the real header row by looking at the first few rows (ws.iter_rows(max_row=5, values_only=True)), then start reading below it.
  • Skip empty rows: if all(value is None for value in row): continue.
  • Strip and convert text: str(value).strip(), then int() or float() inside a try, as you learned for CSV.
  • Use read_only=True for huge files: load_workbook(path, read_only=True) streams the rows instead of loading everything into memory, at the price of not being able to edit.
  • Merged cells keep their value only in the top-left cell; the others read as None.
Python
from openpyxl import load_workbook

wb = load_workbook("customers.xlsx", read_only=True, data_only=True)
ws = wb["Customers"]
rows = ws.iter_rows(min_row=3, values_only=True)          # headers on row 2
headers = [str(h).strip().lower() for h in next(rows)]
customers = [dict(zip(headers, row)) for row in rows if any(row)]
wb.close()                                                # read-only mode keeps the file open

Charts

openpyxl can add native Excel charts that stay editable in Excel. A chart refers to cell ranges through Reference objects:

Python
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active
ws.append(["Month", "Revenue"])
for row in [("Jan", 12500), ("Feb", 13800), ("Mar", 16250)]:
    ws.append(row)

chart = BarChart()
chart.title = "Revenue by month"
chart.y_axis.title = "Euros"
values = Reference(ws, min_col=2, min_row=1, max_row=4)      # includes the header
categories = Reference(ws, min_col=1, min_row=2, max_row=4)
chart.add_data(values, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "D2")                                    # top-left corner of the chart
wb.save("revenue.xlsx")

You can see the result only by opening the file in Excel or LibreOffice, so this example is read-only here. LineChart, PieChart and ScatterChart work the same way.

openpyxl and pandas

When your goal is analysis, not formatting, let pandas handle the spreadsheet. It uses openpyxl behind the scenes:

Python DataFrame to Excel and back

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

pd.read_excel("file.xlsx", sheet_name="Raw", header=2, usecols="A:C") also lets you skip title rows and pick columns. Use pandas for "read the table, compute, write the table" jobs, and openpyxl directly when you need formatting, formulas, charts or to modify an existing template cell by cell. Write-only alternatives such as XlsxWriter are faster for very large new files, but cannot read or edit.

Common mistakes

  • Expecting formulas to calculate. openpyxl writes them; Excel calculates them. For numbers in Python, compute in Python.
  • Forgetting save(). Changes exist only in memory until you save, and saving to the same name overwrites the original.
  • Writing numbers as strings. "10.4%" is text. Write 0.104 with a percentage format.
  • Loading a .xls file. The old binary format isn't supported; convert it to .xlsx first.
  • Off-by-one rows and columns. Excel counts from 1, not 0, and ws[1] is a row while ws["A"] is a column.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. After ws["D2"] = "=B2*C2" and saving with openpyxl, what does load_workbook(path, data_only=True)["Sheet"]["D2"].value return?

    openpyxl does not evaluate formulas. data_only=True returns the cached result that a spreadsheet program stored, and a file written only by openpyxl has no cached results.
  2. 2. How do you write a growth rate of 10.4% so that it can still be summed?

    A number format only changes how the value is displayed. The cell keeps a real number that formulas and Python can calculate with.
  3. 3. Which call reads the rows below the header as plain tuples of values?

    iter_rows accepts min_row and max_row and, with values_only=True, yields tuples of values instead of cell objects.
  4. 4. You must read a 400 MB workbook with a few lines of memory. What should you try?

    Read-only mode streams the rows from the file instead of building the whole workbook in memory. You can't edit in this mode, and you should close the workbook afterwards.
  5. 5. What is a risk of opening a workbook with openpyxl and saving it under the same name?

    openpyxl rewrites the file from what it understood, so unsupported content disappears. Work on a copy of important files.

Summary

  • An .xlsx file is a workbook of worksheets of cells; openpyxl models them as Workbook, Worksheet and Cell, counting rows and columns from 1.
  • Write with ws["A1"] = value, ws.cell(...) or ws.append(row); read with ws.iter_rows(values_only=True); open and save with load_workbook() and wb.save().
  • Formulas are stored as text and calculated by the spreadsheet program, never by openpyxl; compute needed numbers in Python.
  • Style with Font, PatternFill, Alignment, number_format, column widths, freeze_panes and merged cells; store real numbers and format them.
  • Use read_only=True for large files, work on copies, and reach for pandas when the job is analysis.

Next: Browser Automation with Selenium and Playwright, for programs that drive a real web browser.