File size: 4,050 Bytes
ecbb458 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 | #!/usr/bin/env python3
"""Read .xlsx with the pure standard library (no openpyxl / pandas dependency).
Implements only the minimal capabilities needed for batch annotation:
- list sheet names
- read a sheet by name into a list of [dict(col_letter -> value), ...] rows
- auto-parse sharedStrings and numeric cells
Purpose: iHARP batch scripts read input xlsx and calibration tables (cluster/curve).
"""
from __future__ import annotations
import zipfile
from xml.etree import ElementTree as ET
from typing import Any
_NS = "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}"
def _col_letters(ref: str) -> str:
return "".join(ch for ch in ref if ch.isalpha())
def _read_shared_strings(z: zipfile.ZipFile) -> list[str]:
if "xl/sharedStrings.xml" not in z.namelist():
return []
root = ET.fromstring(z.read("xl/sharedStrings.xml").decode("utf-8", errors="replace"))
strings: list[str] = []
for si in root.iter(f"{_NS}si"):
txt = "".join(t.text or "" for t in si.iter(f"{_NS}t"))
strings.append(txt)
return strings
def _sheet_targets(z: zipfile.ZipFile) -> dict[str, str]:
"""Return {sheet_name: worksheet.xml path}."""
wb_root = ET.fromstring(z.read("xl/workbook.xml").decode("utf-8", errors="replace"))
rels_root = ET.fromstring(z.read("xl/_rels/workbook.xml.rels").decode("utf-8", errors="replace"))
rid_to_target = {}
for rel in rels_root:
rid_to_target[rel.get("Id")] = rel.get("Target")
name_to_target: dict[str, str] = {}
for sheet in wb_root.iter(f"{_NS}sheet"):
name = sheet.get("name")
rid = sheet.get(
"{http://schemas.openxmlformats.org/officeDocument/2006/relationships}id"
)
target = rid_to_target.get(rid)
if name and target:
# target looks like worksheets/sheet1.xml, may be a relative path
if not target.startswith("xl/"):
target = "xl/" + target.lstrip("/")
name_to_target[name] = target
return name_to_target
def _parse_sheet(z: zipfile.ZipFile, target: str, strings: list[str]) -> list[dict[str, Any]]:
root = ET.fromstring(z.read(target).decode("utf-8", errors="replace"))
rows: list[dict[str, Any]] = []
for row in root.iter(f"{_NS}row"):
cells: dict[str, Any] = {}
for c in row.iter(f"{_NS}c"):
ref = c.get("r") or ""
col = _col_letters(ref)
t = c.get("t")
v = c.find(f"{_NS}v")
val: Any = None
if t == "s" and v is not None:
val = strings[int(v.text)]
elif v is not None:
val = v.text
elif t == "inlineStr":
is_el = c.find(f"{_NS}is")
if is_el is not None:
val = "".join(tt.text or "" for tt in is_el.iter(f"{_NS}t"))
cells[col] = val
if cells:
rows.append(cells)
return rows
def sheet_names(path: str) -> list[str]:
with zipfile.ZipFile(path) as z:
return list(_sheet_targets(z).keys())
def read_sheet(path: str, name: str | None = None) -> list[dict[str, Any]]:
"""Read the specified sheet; when name is None read the first sheet. Return a list of rows."""
with zipfile.ZipFile(path) as z:
strings = _read_shared_strings(z)
targets = _sheet_targets(z)
if name is None:
name = next(iter(targets))
if name not in targets:
raise KeyError(f"sheet does not exist: {name}")
return _parse_sheet(z, targets[name], strings)
def read_sheet_as_rows(path: str, name: str | None = None) -> list[list[Any]]:
"""Read as a [column-value list] (in column-letter A,B,C... order), suitable for header/data separation."""
dicts = read_sheet(path, name)
if not dicts:
return []
# determine column order
cols = sorted({c for row in dicts for c in row})
result: list[list[Any]] = []
for row in dicts:
result.append([row.get(c) for c in cols])
return result
|