Jason Stedwell
136 lines
4.5 KiB
Python
136 lines
4.5 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Minimal .xlsx reader — standard library only (no openpyxl/pandas).
|
|
|
|
An .xlsx file is a zip of XML parts. This reads worksheet cell values, resolving
|
|
shared strings and inline strings, and returns plain Python rows. Enough for
|
|
tabular imports; it does NOT evaluate formulas (it returns the cached value if
|
|
present) and ignores styling/number formats.
|
|
|
|
API:
|
|
from xlsx_read import read_sheet, sheet_names
|
|
names = sheet_names("file.xlsx")
|
|
rows = read_sheet("file.xlsx", "To-Do List") # -> list[list[str|None]]
|
|
table = read_table("file.xlsx", "To-Do List") # -> (headers, list[dict])
|
|
"""
|
|
|
|
from __future__ import annotations
|
|
|
|
import re
|
|
import zipfile
|
|
from xml.etree import ElementTree as ET
|
|
|
|
_NS = "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}"
|
|
_RID = "{http://schemas.openxmlformats.org/officeDocument/2006/relationships}id"
|
|
_RNS = "{http://schemas.openxmlformats.org/package/2006/relationships}"
|
|
|
|
|
|
def _col_to_num(ref):
|
|
"""'B7' -> 2 (1-based column index)."""
|
|
letters = re.match(r"[A-Z]+", ref).group()
|
|
n = 0
|
|
for ch in letters:
|
|
n = n * 26 + (ord(ch) - 64)
|
|
return n
|
|
|
|
|
|
def _shared_strings(zf):
|
|
if "xl/sharedStrings.xml" not in zf.namelist():
|
|
return []
|
|
root = ET.fromstring(zf.read("xl/sharedStrings.xml"))
|
|
out = []
|
|
for si in root.findall(f"{_NS}si"):
|
|
out.append("".join(t.text or "" for t in si.iter(f"{_NS}t")))
|
|
return out
|
|
|
|
|
|
def _sheet_map(zf):
|
|
"""Return ordered list of (sheet_name, worksheet_part_path)."""
|
|
wb = ET.fromstring(zf.read("xl/workbook.xml"))
|
|
rels = ET.fromstring(zf.read("xl/_rels/workbook.xml.rels"))
|
|
rid2target = {r.get("Id"): r.get("Target")
|
|
for r in rels.iter(f"{_RNS}Relationship")}
|
|
sheets = []
|
|
for s in wb.iter(f"{_NS}sheet"):
|
|
target = rid2target.get(s.get(_RID))
|
|
if not target:
|
|
continue
|
|
if not target.startswith("xl/"):
|
|
target = "xl/" + target.lstrip("/")
|
|
sheets.append((s.get("name"), target))
|
|
return sheets
|
|
|
|
|
|
def sheet_names(path):
|
|
with zipfile.ZipFile(path) as zf:
|
|
return [name for name, _ in _sheet_map(zf)]
|
|
|
|
|
|
def read_sheet(path, sheet_name):
|
|
"""Return rows as lists of cell values (str or None), padded to max width."""
|
|
with zipfile.ZipFile(path) as zf:
|
|
shared = _shared_strings(zf)
|
|
target = None
|
|
for name, part in _sheet_map(zf):
|
|
if name == sheet_name:
|
|
target = part
|
|
break
|
|
if target is None:
|
|
raise KeyError(f"Sheet '{sheet_name}' not found. "
|
|
f"Available: {[n for n, _ in _sheet_map(zf)]}")
|
|
root = ET.fromstring(zf.read(target))
|
|
rows = []
|
|
max_col = 0
|
|
for row in root.iter(f"{_NS}row"):
|
|
cells = {}
|
|
for c in row.findall(f"{_NS}c"):
|
|
ref = c.get("r")
|
|
if not ref:
|
|
continue
|
|
ctype = c.get("t")
|
|
v = c.find(f"{_NS}v")
|
|
inline = c.find(f"{_NS}is")
|
|
val = None
|
|
if ctype == "s" and v is not None:
|
|
val = shared[int(v.text)]
|
|
elif ctype == "inlineStr" and inline is not None:
|
|
val = "".join(t.text or "" for t in inline.iter(f"{_NS}t"))
|
|
elif v is not None:
|
|
val = v.text
|
|
idx = _col_to_num(ref)
|
|
cells[idx] = val
|
|
max_col = max(max_col, idx)
|
|
rows.append(cells)
|
|
return [[r.get(i) for i in range(1, max_col + 1)] for r in rows]
|
|
|
|
|
|
def read_table(path, sheet_name, header_row=0):
|
|
"""Return (headers, list[dict]) using the given 0-based header row index.
|
|
|
|
Rows above the header are skipped; the first row at/after header_row whose
|
|
first cell is non-empty is treated as the header.
|
|
"""
|
|
rows = read_sheet(path, sheet_name)
|
|
if not rows:
|
|
return [], []
|
|
headers = [(h or "").strip() for h in rows[header_row]]
|
|
records = []
|
|
for r in rows[header_row + 1:]:
|
|
rec = {}
|
|
for i, h in enumerate(headers):
|
|
if not h:
|
|
continue
|
|
rec[h] = r[i] if i < len(r) else None
|
|
records.append(rec)
|
|
return headers, records
|
|
|
|
|
|
if __name__ == "__main__":
|
|
import sys
|
|
p = sys.argv[1]
|
|
print("Sheets:", sheet_names(p))
|
|
if len(sys.argv) > 2:
|
|
hdrs, recs = read_table(p, sys.argv[2])
|
|
print("Headers:", hdrs)
|
|
print(f"{len(recs)} data rows")
|