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))