112 lines
4.1 KiB
Python
112 lines
4.1 KiB
Python
 
|
"""Reads what the converter needs from the .xlsm: cell values, cell comments and embedded photos."""
|
||
|
|
|
||
|
|
from __future__ import annotations
|
||
|
|
|
||
|
|
import posixpath
|
||
|
|
import re
|
||
|
|
import zipfile
|
||
|
|
import xml.etree.ElementTree as ET
|
||
|
|
from dataclasses import dataclass, field
|
||
|
|
|
||
|
|
import openpyxl
|
||
|
|
|
||
|
|
NS = {
|
||
|
|
"main": "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
|
||
|
|
"rel": "http://schemas.openxmlformats.org/package/2006/relationships",
|
||
|
|
"r": "http://schemas.openxmlformats.org/officeDocument/2006/relationships",
|
||
|
|
"xdr": "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing",
|
||
|
|
"a": "http://schemas.openxmlformats.org/drawingml/2006/main",
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
@dataclass
|
||
|
|
class Sheet:
|
||
|
|
title: str
|
||
|
|
cells: dict[str, object] # "C3" -> raw value (cached formula results)
|
||
|
|
comments: dict[str, str] = field(default_factory=dict) # "C3" -> comment text without author
|
||
|
|
photos: list[bytes] = field(default_factory=list) # embedded pictures, top-left first
|
||
|
|
|
||
|
|
|
||
|
|
def read_sheets(path: str) -> list[Sheet]:
|
||
|
|
wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
|
||
|
|
sheets = []
|
||
|
|
for ws in wb.worksheets:
|
||
|
|
cells = {}
|
||
|
|
for row in ws.iter_rows():
|
||
|
|
for cell in row:
|
||
|
|
if cell.value is not None and str(cell.value).strip() != "":
|
||
|
|
cells[cell.coordinate] = cell.value
|
||
|
|
sheets.append(Sheet(ws.title, cells))
|
||
|
|
wb.close()
|
||
|
|
|
||
|
|
with zipfile.ZipFile(path) as z:
|
||
|
|
parts = _sheet_parts(z)
|
||
|
|
for sheet in sheets:
|
||
|
|
part = parts.get(sheet.title)
|
||
|
|
if part:
|
||
|
|
sheet.comments = _comments(z, part)
|
||
|
|
sheet.photos = _photos(z, part)
|
||
|
|
return sheets
|
||
|
|
|
||
|
|
|
||
|
|
def _rels(z: zipfile.ZipFile, part: str) -> dict[str, str]:
|
||
|
|
"""Relationship id -> absolute part path for a part's .rels file."""
|
||
|
|
rels_path = posixpath.join(posixpath.dirname(part), "_rels", posixpath.basename(part) + ".rels")
|
||
|
|
if rels_path not in z.namelist():
|
||
|
|
return {}
|
||
|
|
root = ET.fromstring(z.read(rels_path))
|
||
|
|
out = {}
|
||
|
|
for rel in root.findall("rel:Relationship", NS):
|
||
|
|
target = rel.get("Target")
|
||
|
|
if rel.get("TargetMode") == "External":
|
||
|
|
continue
|
||
|
|
out[rel.get("Id")] = (
|
||
|
|
target.lstrip("/") if target.startswith("/") else posixpath.normpath(posixpath.join(posixpath.dirname(part), target))
|
||
|
|
)
|
||
|
|
return out
|
||
|
|
|
||
|
|
|
||
|
|
def _sheet_parts(z: zipfile.ZipFile) -> dict[str, str]:
|
||
|
|
root = ET.fromstring(z.read("xl/workbook.xml"))
|
||
|
|
rels = _rels(z, "xl/workbook.xml")
|
||
|
|
return {
|
||
|
|
s.get("name"): rels[s.get(f"{{{NS['r']}}}id")]
|
||
|
|
for s in root.find("main:sheets", NS)
|
||
|
|
if s.get(f"{{{NS['r']}}}id") in rels
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
def _comments(z: zipfile.ZipFile, sheet_part: str) -> dict[str, str]:
|
||
|
|
out = {}
|
||
|
|
for target in _rels(z, sheet_part).values():
|
||
|
|
if not re.search(r"comments\d*\.xml$", target):
|
||
|
|
continue
|
||
|
|
root = ET.fromstring(z.read(target))
|
||
|
|
authors = [a.text or "" for a in root.find("main:authors", NS)]
|
||
|
|
for c in root.find("main:commentList", NS):
|
||
|
|
text = "".join(t.text or "" for t in c.iter(f"{{{NS['main']}}}t")).strip()
|
||
|
|
for author in authors:
|
||
|
|
if author and text.startswith(author + ":"):
|
||
|
|
text = text[len(author) + 1 :].strip()
|
||
|
|
if text:
|
||
|
|
out[c.get("ref")] = text
|
||
|
|
return out
|
||
|
|
|
||
|
|
|
||
|
|
def _photos(z: zipfile.ZipFile, sheet_part: str) -> list[bytes]:
|
||
|
|
found = []
|
||
|
|
for target in _rels(z, sheet_part).values():
|
||
|
|
if not re.search(r"drawings/drawing\d+\.xml$", target):
|
||
|
|
continue
|
||
|
|
media = _rels(z, target)
|
||
|
|
root = ET.fromstring(z.read(target))
|
||
|
|
for anchor in root:
|
||
|
|
frm = anchor.find("xdr:from", NS)
|
||
|
|
row = int(frm.find("xdr:row", NS).text) if frm is not None else 0
|
||
|
|
col = int(frm.find("xdr:col", NS).text) if frm is not None else 0
|
||
|
|
for blip in anchor.iter(f"{{{NS['a']}}}blip"):
|
||
|
|
embed = blip.get(f"{{{NS['r']}}}embed")
|
||
|
|
if embed in media:
|
||
|
|
found.append((row, col, z.read(media[embed])))
|
||
|
|
found.sort(key=lambda f: (f[0], f[1]))
|
||
|
|
return [data for _, _, data in found]
|