| |
| """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: |
| |
| 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 [] |
| |
| 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 |
|
|
