src/xstream_piflow_excel_write.py
1"""XLSX workbook as an output byte stream, without destination paths or append mode."""
2import datetime
3import io
4import re
5from openpyxl import Workbook
6from openpyxl.cell import WriteOnlyCell
7from openpyxl.utils.cell import coordinate_to_tuple
8from dacp_piflow_support import params, MAX_BYTES
9
10
11def process(ctx):
12 p = params(ctx, optional=['header', 'dataAddress'])
13 header, address = p.get('header', True), p.get('dataAddress', 'Sheet1!A1')
14 if type(header) is not bool or not isinstance(address, str):
15 raise ValueError('header must be boolean; dataAddress must be a string')
16 match = re.fullmatch(r"(?:(?:'((?:[^']|'')+)'|([^!]+))!)?([A-Za-z]+[1-9][0-9]*)", address)
17 if not match:
18 raise ValueError('dataAddress must be [Sheet!]A1 start cell; bounded write ranges are not supported')
19 sheet = (match[1] or match[2] or 'Sheet1').replace("''", "'")
20 row_start, col_start = coordinate_to_tuple(match[3])
21 if row_start>100000 or col_start>16384:
22 raise ValueError('start cell outside supported bounds')
23 source = ctx.input('source')
24 names = source.schema.names
25 if col_start+len(names)>16385:
26 raise ValueError('too many columns for XLSX')
27 wb = Workbook(write_only=True)
28 ws = wb.create_sheet(sheet)
29 size, count = 0, row_start-1
30 for _ in range(row_start-1):
31 ws.append([])
32 def append(values):
33 nonlocal count
34 count += 1
35 if count>1000000:
36 raise ValueError('XLSX output exceeds one million rows')
37 cells = [None]*(col_start-1)
38 for value in values:
39 if isinstance(value, (dict,list,bytes)):
40 raise ValueError('XLSX requires scalar nonbinary values')
41 if isinstance(value, (datetime.date,datetime.time,datetime.datetime)):
42 value=value.isoformat()
43 cell=WriteOnlyCell(ws,value=value)
44 if isinstance(value,str):
45 cell.data_type='s' # preserve text, including leading =; never create formulas
46 cells.append(cell)
47 ws.append(cells)
48 try:
49 if header:
50 append(names)
51 for batch in source.records():
52 size+=batch.nbytes
53 if size>MAX_BYTES:
54 raise ValueError('XLSX input exceeds 128 MiB')
55 for row in batch.to_pylist():
56 append([row[n] for n in names])
57 buffer=io.BytesIO()
58 wb.save(buffer)
59 out=ctx.bytes_output('result','xlsx')
60 buffer.seek(0)
61 while chunk:=buffer.read(65536):
62 out.write(chunk)
63 finally:
64 wb.close()
65
66
67def infer_outputs(inputs, params):
68 import pyarrow as pa
69 from xstream_operator import descriptor, result, records_schema
70 expected = {'source'}
71 if set(inputs) != expected:
72 raise ValueError('unexpected input ports')
73 outputs = {'result': {'representations': ['bytes'], 'formats': ['xlsx'], 'certainty': 'known'}}
74 return result(outputs)
75
76
77def preview_outputs(inputs, params, limits):
78 from xstream_operator import preview_process
79 return preview_process(process, inputs, params, limits)
80
81
82if __name__ == '__main__':
83 from xstream_operator import run_operator
84 run_operator(process, infer_outputs, preview_outputs=preview_outputs)