Quick Reference · read legacy Excel .xls files from Python

xlrd 2.0.1

xlrd is a read-only decoder for one dead file format. It opens the OLE2 compound document that is a .xls, pulls out the Workbook stream, parses its BIFF records, and hands you Book → Sheet → Cell. It cannot write. It cannot read .xlsx. It is not being developed. Used for what it is — rescuing data out of pre-2007 spreadsheets — it is precise and dependable.

⚠ FROZEN · last release 11 Dec 2020 — version 2.0.0 deliberately removed all non-.xls support. If your file is .xlsx, you want openpyxl; scroll to the appendix.
install & open the Book the Sheet cells & values types, dates & unicode formatting_info & extras gotcha / removed ★ most common

Distilled & cross-checked against: xlrd.readthedocs.io (API Reference · Dates · Handling of Unicode · Formatting information · Loading worksheets on demand · Changes) · github.com/python-excel/xlrd CHANGELOG · PyPI · pandas 3.0 IO documentation · python-calamine

What xlrd is — and the gate every user hits first
A · ON DISK PARSED, EAGERLY, INTO MEMORY legacy.xls OLE2 compound document D0 CF 11 E0 A1 B1 1A E1 streams: Workbook, SummaryInformation … compdoc pull out the "Workbook" stream BIFF parser binary records → Python objects Book nsheets · datemode sheet_by_index() Sheet nrows · ncols row_values() Cell .ctype · .value .xf_index ✗ there is no write path — ever to write .xls use xlwt; to edit one, xlutils.copy B · THE FORMAT GATE — SINCE 2.0.0, ONLY ONE ANSWER PASSES open_workbook(path) inspect_format() sniffs it first .xls BIFF 2.0 – 8.0 ✓ xlrd reads it the only format it still understands .xlsx .xlsb .ods .zip XLRDError: Excel xlsx file; not supported the single most-searched xlrd message go here instead .xlsx → openpyxl · .xlsb → pyxlsb .ods → odfpy · any of them → calamine never pin xlrd==1.2 to dodge this
quickstart — read a legacy .xls, handling dates correctly
# pip install xlrd            # 2.0.1 — reads .xls ONLY, and only reads
import xlrd
from xlrd import open_workbook, xldate_as_datetime

book = open_workbook("legacy.xls")          # parses the WHOLE file up front
print(book.nsheets, book.sheet_names())

sheet = book.sheet_by_index(0)              # or .sheet_by_name("Data")
print(sheet.name, sheet.nrows, sheet.ncols)  # NOT len(sheet)

header = sheet.row_values(0)                # row 0 — indexes start at ZERO

for rx in range(1, sheet.nrows):
    cell = sheet.cell(rx, 2)                # (row, col), both 0-based
    if cell.ctype == xlrd.XL_CELL_DATE:     # ctype 3 → .value is a FLOAT
        value = xldate_as_datetime(cell.value, book.datemode)
    else:
        value = cell.value
    print(sheet.cell_value(rx, 0), value)

book.release_resources()                    # free the mmap + parsed data
01Install & the 2.0 Breakread this first
02open_workbook()the only entry point
03open_workbook() flagsall nine, in one place
04The Book Objectworkbook-level facts
05Getting a Sheetfour ways
06Sheet Dimensionsno len(), ever
07Cell Access(row, col), both 0-based
08Rowsthe usual workhorse
09Columnsmirror of the row API
10Cell Typesseven codes
11Datesthe one that costs hours
12Date Exceptionsxlrd.xldate
13Errors & Booleansctype 4 and 5
14Unicode & Encodingold files, old codepages
15formatting_info=Truewhat it unlocks
16Fonts & Coloursrendering an .xls
17Merged & Raggedshape surprises
18Names & Rangesdefined names
19Notes, Links & Panesthe long tail
20on_demand & Memorybig legacy files
21Debugging a Bad Filewhen it won't parse
22pandas Interophow most people meet xlrd
23Writing .xlsnot with xlrd
24Migrating Off xlrdthe honest card

Four pictures that prevent most xlrd bugs

The index base, the seven cell types, how a date becomes a date, and what formatting_info actually changes.

1 · xlrd counts from 0

The single biggest source of off-by-one bugs, especially when porting to or from openpyxl — which counts from 1. Same cell, two different tuples.

xlrd — 0-based openpyxl — 1-based A B C 0,0 0,1 0,2 1,0 1,1 1,2 A B C 1,1 1,2 1,3 2,1 2,2 2,3 cell A1 is   sheet.cell(0, 0)  vs  ws.cell(1, 1) last row is   sheet.nrows - 1  vs  ws.max_row loop with   range(sheet.nrows)  vs  range(1, ws.max_row + 1) ⚠ both accept (1, 1) without complaint one gives you B2, the other A1 — no error, just quietly wrong data.

2 · the seven cell types

Every value arrives with a ctype. Two of them are traps: a DATE is a float, and a BLANK only exists if you asked for formatting.

CTYPE SYMBOL PYTHON VALUE 0 XL_CELL_EMPTY '' — no record at all 1 XL_CELL_TEXT str 2 XL_CELL_NUMBER float — always, never int 3 XL_CELL_DATE float — NOT a datetime 4 XL_CELL_BOOLEAN int — 1 or 0, not True/False 5 XL_CELL_ERROR int → error_text_from_code 6 XL_CELL_BLANK '' — only with formatting_info Empty vs Blank: EMPTY never existed; BLANK has formatting but no data.

3 · how a date becomes a date

Excel stores a date as days since an epoch. Which epoch depends on the file, so the conversion always needs book.datemode.

ctype 3, value 43831.0 + which epoch? book.datemode xldate_as_datetime 2020-01-01 TWO EPOCHS, 1462 DAYS APART datemode 0 — 1900 Excel for Windows default datemode 1 — 1904 Excel for Macintosh default guess wrong and every date in the file shifts by 4 years and a day THE 1900 LEAP-YEAR BUG 1.0 – 61.0 61.0 and up — unambiguous, converts cleanly Excel thinks 1900 was a leap year. In that window xlrd raises XLDateAmbiguous rather than guess.

4 · what formatting_info changes

It is not just "extra style data". Turning it on changes which cells exist, and therefore nrows and ncols.

False (default) True cell_value / cell_type ✓ ✓ blank cells (ctype 6) invisible present nrows / ncols margins trimmed can be larger cell.xf_index None usable index merged_cells [] empty populated fonts, colours, borders ✗ ✓ memory & parse time lower noticeably higher xlutils.copy needs it too — without it, a copied workbook loses every style.

Appendix — reading a .xls in 2026

Three live routes. pandas still reaches for xlrd automatically, so even people who never import it depend on it.

xlrddirect, full control

✚ reach for it when

  • You need the cell type, not just the value — dates, errors, blanks
  • You need formatting: fonts, colours, merged ranges, XF records
  • You're feeding xlutils.copy to edit an .xls in place
  • You want defined names, notes, hyperlinks or page breaks
  • The file is odd and you need dump() to see the BIFF records

⚠ it can't

  • Read .xlsx, .xlsb or .ods — removed in 2.0.0
  • Write anything, ever
  • Receive fixes — no release since Dec 2020
pandasone line to a DataFrame

✚ reach for it when

  • You want the table, not the spreadsheet
  • pd.read_excel("f.xls") already picks xlrd for you
  • You need dtype, parse_dates, na_values, usecols on the way in
  • sheet_name=None gives you every sheet at once

⚠ watch out

  • It requires xlrd >= 2.0.1 — pinning 1.2.0 breaks pandas
  • Cell types, formatting and merges are all flattened away
  • There is an open proposal to drop the xlrd engine for calamine
calaminethe modern replacement

✚ reach for it when

  • You want one reader for .xls, .xlsx, .xlsm, .xlsb and .ods
  • Speed matters — it's Rust underneath, and markedly faster
  • You're standardising a mixed pile of legacy files
  • pip install python-calamine, then engine="calamine" in pandas

⚠ it can't

  • Write files — also read-only
  • Expose XF records, fonts or colours the way xlrd does
  • Replace xlutils.copy for in-place .xls editing

Worth memorizing

2.0+ reads .xls onlyand the fix is openpyxl, never pip install xlrd==1.2.0
frozen since Dec 20202.0.1 is the last release — stable, not maintained
read-only, alwaysxlwt writes .xls; xlutils.copy edits one
indexes start at 0A1 is (0, 0) — openpyxl calls it (1, 1)
nrows / ncolsthere is no len(sheet)
ncols = max index + 1trailing empty columns are dropped
ctype 3 = DATEbut .value is still a float
always pass book.datemode1900 vs 1904 is a 1,462-day error
XLDateAmbiguousserials 1–61 in the 1900 system: the leap-year bug
numbers are floatsExcel has no integer type
booleans are 1 / 0not True / False
blank ≠ emptyBLANK needs formatting_info=True to appear at all
merged_cells boundsupper limits are exclusive; only the top-left holds data
ragged_rows + row_len()use them together or you'll index past the end
open_workbook is eagerthe whole file is parsed up front unless on_demand=True
book.sheets()forces every sheet to load — defeats on_demand
release_resources()or just use with open_workbook(f) as book:
pandas needs xlrd>=2.0.1and still routes .xls to it in pandas 3.0