Python Excel

Openpyxl Tutorial

openpyxl reads and writes Excel .xlsx files from Python. Excel itself does not have to be installed. Use it to copy rows, rename sheets, add or delete worksheets, style cells, or build a file for someone else to open in Excel.

Install it once, from a terminal:

pip install openpyxl

Words this tutorial uses

  • A workbook is the Excel file. openpyxl saves it with a .xlsx extension.
  • A workbook holds one or more worksheets.
  • The active sheet is the worksheet that was selected the last time the file was saved.
  • Columns run from A upward. Rows start at 1.
  • A cell is the box where a row and column meet. It can hold a number, text, or a formula.

A sample sheet

Create testfile.xlsx in the same folder as your script and type this table into Sheet1. The later lessons use the same idea: a small file you can throw away.

DateRegionNameItemQuantityRateTotal
7/6/14 4:50 AMABConnorPencil151.9929.85
4/23/14 2:25 PMDGShaneBinder2019.99399.80
5/9/14 4:45 AMPQThatcherPencil254.99124.75
3/15/14 6:00 AMTXJamesPencil352.99104.65

Find the folder Python is using

openpyxl looks for a bare filename in the current working directory. Check that folder before you load a file:

import os
print(os.getcwd())
os.chdir(r"C:\data\myfiles")

Open the workbook and list its sheets

import openpyxl

wb = openpyxl.load_workbook("testfile.xlsx")
print(type(wb))
print(wb.sheetnames)

load_workbook returns a Workbook. sheetnames is the list of worksheet titles, for example ['Sheet1', 'Sheet2', 'Sheet3']. If you rename the tabs in Excel, save the file, and load it again, the list follows the new names.

Older openpyxl versions used get_sheet_names(). That method has been removed. Use wb.sheetnames.

Select one worksheet

sheet = wb["Sheet1"]
print(sheet.title)
print(type(sheet))

The result is a Worksheet. You can also take whatever tab was active when the file was saved:

sheet = wb.active

Older code called get_sheet_by_name("Sheet1") or get_active_sheet(). Both are gone. Index the workbook with the sheet title, or use wb.active.

Read a cell

print(sheet["B2"].value)
print(sheet.cell(row=2, column=2).value)

Both lines read the same cell. "B2" is the Excel address. cell(row, column) is easier inside a loop because row and column are numbers. Row 1, column 1 is cell A1.

for row in sheet.iter_rows(min_row=2, max_col=4, values_only=True):
    print(row)

Each row is a tuple of cell values. values_only=True skips the cell objects and returns the stored values.

Save your changes

Nothing is written to disk until you save. Pass a new filename when you want to keep the original:

wb.save("testfile-copy.xlsx")

Where to go next