Source code

Download source archive ↓

src/piflow_workbooks.py

1"""First-sheet ingestion and sorted workbook export."""
2
3from contextlib import contextmanager
4import io
5from types import SimpleNamespace
6from zipfile import ZipFile
7
8import pyarrow as pa
9
10
11@contextmanager
12def excel_rows(data, extension):
13    if extension == ".xls":
14        from xlrd import open_workbook
15
16        book = open_workbook(file_contents=data, on_demand=True)
17        try:
18            sheet = book.sheet_by_index(0)
19            if sheet.nrows * sheet.ncols > 1_000_000:
20                raise ValueError("workbook exceeds one million cells")
21            yield iter(sheet.get_rows()), lambda cell: cell.value
22        finally:
23            book.release_resources()
24    else:
25        from openpyxl import load_workbook
26
27        with ZipFile(io.BytesIO(data)) as archive:
28            entries = archive.infolist()
29            if len(entries) > 3000 or sum(info.file_size for info in entries) > 32 * 1024 * 1024:
30                raise ValueError("expanded XLSX package exceeds supported limits")
31        book = load_workbook(io.BytesIO(data), read_only=True, data_only=True, keep_links=False)
32        try:
33            sheet = book.worksheets[0]
34            yield sheet.iter_rows(values_only=True), lambda cell: cell
35        finally:
36            book.close()
37
38
39def text_cell(value):
40    if value is None:
41        return ""
42    if isinstance(value, bool):
43        return str(value).lower()
44    if isinstance(value, float) and value.is_integer():
45        return str(int(value))
46    return str(value).strip()
47
48
49def first_sheet_collection(ctx, collect):
50    tables = []
51    size = 0
52    for item in ctx.input("source").items():
53        name = item["name"].lower()
54        if item["kind"] != "data" or not name.endswith((".xls", ".xlsx")):
55            continue
56        if len(tables) >= 32:
57            raise ValueError("at most 32 Excel workbooks can be combined")
58        with ctx.open_item(item, "bytes") as stream:
59            data = collect(stream.bytes())
60        size += len(data)
61        if size > 24 * 1024 * 1024:
62            raise ValueError("workbook inputs exceed 24 MiB")
63        extension = ".xlsx" if name.endswith(".xlsx") else ".xls"
64        with excel_rows(data, extension) as (rows, value):
65            header = [text_cell(value(cell)).lower().replace(" ", "_") for cell in next(rows, [])]
66            if not header or not all(header) or len(header) != len(set(header)):
67                raise ValueError("each workbook requires unique, nonempty first-sheet columns")
68            contents = []
69            schema = pa.schema([(key, pa.string()) for key in header])
70            width = len(header)
71            for i, row in enumerate(rows, start=2):
72                if i * width > 1_000_000:
73                    raise ValueError("workbook exceeds one million cells")
74                values = [text_cell(value(cell)) for cell in row[:width]]
75                if any(values):
76                    contents.append(dict(zip(header, values)))
77            tables.append(pa.Table.from_pylist(contents, schema=schema))
78    if not tables:
79        raise ValueError("no matching Excel Items")
80    return pa.concat_tables(tables, promote_options="default")
81
82
83def write_sorted(ctx, table):
84    from piflow_excel_writer import process
85
86    read = SimpleNamespace(schema=table.schema, records=table.to_batches)
87    params = {key: ctx.params[key] for key in ("header", "dataAddress")}
88    process(SimpleNamespace(params=params, input=lambda name: read, bytes_output=ctx.bytes_output))