Read an `.xlsx` file in Python with no dependencies

Every answer to this question starts with pip install openpyxl. That is a fine answer, and sometimes it is not available to you — a locked-down build, a lambda you are keeping small, a script you want to paste into a colleague's machine and have work.

An .xlsx is a zip file full of XML. zipfile and xml.etree are both standard library. So:

>>> import zipfile
>>> zipfile.ZipFile("livro.xlsx").namelist()[:6]
['[Content_Types].xml',
 '_rels/.rels',
 'xl/workbook.xml',
 'xl/sharedStrings.xml',
 'xl/styles.xml',
 'xl/worksheets/sheet1.xml']

The cells are in sheet1.xml and they look like this:

<row r="2">
  <c r="A2" t="s"><v>10</v></c>
  <c r="C2" s="5"><v>45914</v></c>
  <c r="D2"><v>1618.5</v></c>
</row>

Four things in six lines of XML are already not obvious, and they are the whole difficulty:

t="s" means the value is not 10, it is index 10 of the shared string table.
There is no B2. Excel omits an empty cell rather than writing a blank one, so the row has a hole you must put back.
45914 is a date — 2025-09-14 — and nothing in the cell says so. The s="5" points into styles.xml, and only that file knows.
Nothing tells you the type of D2. No t attribute means number.

The three parts

1. Shared strings — and rich text

Excel stores every distinct string once and refers to it by index. The trap is styled text: if one word of a cell is bold, Excel splits that single value into several <r> runs, and reading only the first <t> returns "Ana" for a cell that says "Ana Silva".

import xml.etree.ElementTree as ET

NS = "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}"

def shared_strings(zf):
    try:
        root = ET.fromstring(zf.read("xl/sharedStrings.xml"))
    except (KeyError, ET.ParseError):
        return []
    # Every <t> under this <si>, including the ones inside <r> runs.
    return ["".join(t.text or "" for t in si.iter(f"{NS}t"))
            for si in root.iter(f"{NS}si")]

2. Which numbers are dates

A date in Excel is a number wearing a format. The cell's s attribute indexes cellXfs in styles.xml, which gives a numFmtId. Ids 14–22 and 45–47 are the built-in date and time formats; anything from 164 up is custom, and you have to read its formatCode.

That is where the trap is. You are looking for the letters y m d h s — but only outside quoted literals and backslash escapes. A perfectly ordinary format like "month" 0 has an m in it, and a naive check turns every number in that column into a date in 1900.

DATE_IDS = set(range(14, 23)) | {45, 46, 47}

def is_date_code(code):
    """Does this format code describe a date? Quoted text does not count."""
    code = code.replace("&quot;", '"').replace("&amp;", "&")
    out, i, n = [], 0, len(code)
    while i < n:
        ch = code[i]
        if ch == "\\":
            i += 2                      # escaped literal character
        elif ch == '"':
            i += 1
            while i < n and code[i] != '"':
                i += 1
            i += 1                      # quoted literal run
        elif ch == "[":
            while i < n and code[i] != "]":
                i += 1
            i += 1                      # [Red], [h], a condition
        else:
            out.append(ch)
            i += 1
    return any(c in "ymdhs" for c in "".join(out).lower())

def date_styles(zf):
    """Style indices whose number format means the value is a date."""
    try:
        root = ET.fromstring(zf.read("xl/styles.xml"))
    except (KeyError, ET.ParseError):
        return set()
    custom = {int(nf.get("numFmtId", -1)): nf.get("formatCode", "")
              for nf in root.iter(f"{NS}numFmt")}
    dates, cell_xfs = set(), root.find(f"{NS}cellXfs")
    # `if cell_xfs is not None`, never `cell_xfs or []` -- an Element with no
    # children is falsy today and that behaviour is deprecated.
    for i, xf in enumerate(cell_xfs if cell_xfs is not None else []):
        fmt = int(xf.get("numFmtId", 0))
        if fmt in DATE_IDS or (fmt in custom and is_date_code(custom[fmt])):
            dates.add(i)
    return dates

3. The 1900 leap year bug

This one is famous and still catches people. Excel believes 1900 was a leap year. It was not — 1900 is divisible by 100 and not by 400 — but Lotus 1-2-3 had the bug, and Excel copied it deliberately for compatibility. It has never been fixed and never will be.

So serial 60 is 29 February 1900, a day that did not happen, and every serial after it is one too many. Anchoring the epoch at 1899-12-30 for serials of 61 and up cancels the error exactly; below 60 the epoch is 1899-12-31. Serial 60 itself has no correct answer and should not be given one.

from datetime import datetime, timedelta

def serial_to_text(serial):
    """Excel's day number as an ISO date, or None if it is not one."""
    try:
        serial = float(serial)
    except (TypeError, ValueError):
        return None
    if serial < 0 or serial > 2958465:          # 9999-12-31
        return None
    days, frac = int(serial), serial - int(serial)
    if days == 60:
        return None                              # the day that never was
    base = datetime(1899, 12, 30) if days >= 61 else datetime(1899, 12, 31)
    when = base + timedelta(days=days, seconds=round(frac * 86400))
    if days == 0 and frac:
        return when.strftime("%H:%M:%S")         # a time with no date
    return when.strftime("%Y-%m-%d" if not frac else "%Y-%m-%d %H:%M:%S")

>>> serial_to_text(59), serial_to_text(60), serial_to_text(61)
('1900-02-28', None, '1900-03-01')
>>> serial_to_text(45914)
'2025-09-14'

Putting it together

Column letters are base-26 with no zero, which is the part that is easy to get subtly wrong: Z is 25 and AA is 26, not 27.

def col_index(ref):
    """'AB12' -> 27."""
    n = 0
    for ch in ref:
        if not ch.isalpha():
            break
        n = n * 26 + (ord(ch.upper()) - 64)
    return n - 1

def read_xlsx(path, sheet=0):
    """(header, rows) from an .xlsx. Every value comes back as a string."""
    import zipfile
    with zipfile.ZipFile(path) as zf:
        names = sorted(n for n in zf.namelist()
                       if n.startswith("xl/worksheets/sheet") and n.endswith(".xml"))
        strings, dates = shared_strings(zf), date_styles(zf)
        root = ET.fromstring(zf.read(names[sheet]))

    rows, width = [], 0
    for row in root.iter(f"{NS}row"):
        cells = {}
        for c in row.iter(f"{NS}c"):
            ref, ctype, style = c.get("r", ""), c.get("t", "n"), c.get("s")
            v = c.find(f"{NS}v")
            if ctype == "s":
                text = strings[int(v.text)] if v is not None else ""
            elif ctype == "inlineStr":
                el = c.find(f"{NS}is")
                text = "".join(t.text or "" for t in el.iter(f"{NS}t")) if el is not None else ""
            elif ctype == "b":
                text = "TRUE" if (v is not None and v.text == "1") else "FALSE"
            else:
                text = (v.text or "") if v is not None else ""
                if text and style is not None and int(style) in dates:
                    text = serial_to_text(text) or text
            cells[col_index(ref) if ref else len(cells)] = text
        if cells:
            width = max(width, max(cells) + 1)
        # The gaps are real: a row written as A, C, D has no B in the file.
        rows.append([cells.get(i, "") for i in range(max(cells) + 1)] if cells else [])

    rows = [r + [""] * (width - len(r)) for r in rows]
    while rows and not any(c.strip() for c in rows[0]):
        rows.pop(0)                                  # blank rows before the header
    return (rows[0], rows[1:]) if rows else ([], [])

About 120 lines all in. Point it at a real workbook:

>>> header, rows = read_xlsx("financial-sample.xlsx")
>>> len(rows), len(header)
(700, 16)
>>> header[:4]
['Segment', 'Country', 'Product', 'Discount Band']
>>> rows[0][:5]
['Government', 'Canada', 'Carretera', 'None', '1618.5']
>>> rows[0][12]
'2014-01-01'

What this does not do

Formulas. You get the cached result Excel last calculated, which is what the file says the answer was. Evaluating formulas yourself is a different and much larger project.
Formatting, merged cells, charts, macros, pivot tables. Values only.
Writing. Reading a zip of XML is easy; producing one that Excel opens without complaining is not, and that is where a library genuinely earns its place.
Huge files. This parses the whole sheet into memory. Past a few hundred thousand rows you want ET.iterparse — still standard library, just more code.
.xls — the old binary format, which is a completely different thing and not a zip at all.

Is this better than `openpyxl`?

No. If you can install it, install it — it writes files, it handles the long tail, and it has been hit by more strange spreadsheets than this ever will.

This is for when you cannot, or when a dependency costs more than it is worth: a script that has to run on a machine you do not control, a container you are keeping to a few megabytes, a one-file tool you want a colleague to be able to paste and run. In those cases "just install a library" is not a smaller answer than 120 lines — it is a much larger one.

The finished version

All of the above is in csvclean.py, which reads .xlsx and CSV alike and then cleans what it finds: duplicates, whitespace, ISO dates, headers, and European versus US decimal marks (also harder than it looks). One file, no dependencies, public domain, tested on Python 3.9 to 3.13.

Free, and it stays free. If your file is the awkward one, that is what the Data Cleanup package is for, at a fixed 49€.

← More writing · HookForge: fixed-price automation scripts, delivered in 48h

Read an .xlsx file in Python with no dependencies