src/xstream_piflow_excel_read.py
1"""Read a selected XLSX worksheet/range from an input byte stream as string records."""
2import datetime
3import io
4import re
5import zipfile
6import pyarrow as pa
7from openpyxl import load_workbook
8from openpyxl.utils.cell import range_boundaries, coordinate_to_tuple
9from dacp_piflow_support import params, MAX_BYTES
10
11
12def process(ctx):
13 p = params(ctx, optional=['header','dataAddress'])
14 header, address=p.get('header',True),p.get('dataAddress','A1')
15 if type(header) is not bool or not isinstance(address,str):
16 raise ValueError('header must be boolean; dataAddress must be string')
17 match=re.fullmatch(r"(?:(?:'((?:[^']|'')+)'|([^!]+))!)?([A-Za-z]+[1-9][0-9]*(?::[A-Za-z]+[1-9][0-9]*)?)",address)
18 if not match:
19 raise ValueError('dataAddress must be [Sheet!]A1[:F35]')
20 buffer=io.BytesIO()
21 for chunk in ctx.input('source').bytes():
22 if buffer.tell()+len(chunk)>MAX_BYTES:
23 raise ValueError('XLSX input exceeds 128 MiB')
24 buffer.write(chunk)
25 buffer.seek(0)
26 with zipfile.ZipFile(buffer) as archive:
27 if sum(x.file_size for x in archive.infolist())>MAX_BYTES:
28 raise ValueError('expanded XLSX exceeds 128 MiB')
29 buffer.seek(0)
30 wb=load_workbook(buffer,read_only=True,data_only=True,keep_links=False)
31 try:
32 sheet=(match[1] or match[2] or '').replace("''", "'")
33 ws=wb[sheet] if sheet else wb.worksheets[0]
34 if ':' in match[3]:
35 c1,r1,c2,r2=range_boundaries(match[3])
36 else:
37 r1,c1=coordinate_to_tuple(match[3]);r2,c2=ws.max_row,ws.max_column
38 if not r2 or not c2 or r2<r1 or c2<c1 or (r2-r1+1)*(c2-c1+1)>1000000:
39 raise ValueError('selected XLSX range must contain at most one million cells')
40 rows=iter(ws.iter_rows(min_row=r1,max_row=r2,min_col=c1,max_col=c2,values_only=True))
41 first=next(rows,None)
42 if header:
43 if first is None or any(v is None for v in first):
44 raise ValueError('nonempty XLSX header required')
45 names=[str(v) for v in first]
46 else:
47 import itertools
48 names=['_'+str(i) for i in range(c2-c1+1)]
49 rows=itertools.chain([] if first is None else [first],rows)
50 if any(not n for n in names) or len(set(names))!=len(names):
51 raise ValueError('unique nonempty XLSX column names required')
52 schema=pa.schema([(n,pa.string()) for n in names])
53 out=ctx.records_output('result',schema)
54 batch=[]
55 for row in rows:
56 def text(v):
57 if isinstance(v,(datetime.date,datetime.datetime,datetime.time)):
58 return v.isoformat()
59 if isinstance(v,bool):
60 return 'true' if v else 'false'
61 return None if v is None else str(v)
62 batch.append(dict(zip(names,map(text,row))))
63 if len(batch)==128:
64 out.write(pa.RecordBatch.from_pylist(batch,schema=schema));batch.clear()
65 if batch:
66 out.write(pa.RecordBatch.from_pylist(batch,schema=schema))
67 finally:
68 wb.close()
69
70
71def infer_outputs(inputs, params):
72 import pyarrow as pa
73 from xstream_operator import descriptor, result, records_schema
74 expected = {'source'}
75 if set(inputs) != expected:
76 raise ValueError('unexpected input ports')
77 outputs = {'result': {'representations': ['records'], 'formats': ['arrow'], 'certainty': 'unknown'}}
78 return result(outputs)
79
80
81def preview_outputs(inputs, params, limits):
82 from xstream_operator import preview_process
83 return preview_process(process, inputs, params, limits)
84
85
86if __name__ == '__main__':
87 from xstream_operator import run_operator
88 run_operator(process, infer_outputs, preview_outputs=preview_outputs)