Source code

Download source archive ↓

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)