Closed dberardo-com closed 10 months ago
You can use the following script to extract the online formula.
>>> import functools
>>> import schedula as sh
>>> xl_model = formulas.ExcelModel().from_dict({
... "A3": 1,
... "A2": "=SUM(A4,A13)",
... "A13": "=SUM(1,3)",
... "A1": "=A4",
... "A4": "=SUM(A3,1,1,1,1)"
... })
>>> @functools.lru_cache()
... def get_one_line_formula(xl_model, node):
... try:
... formula = next(iter(xl_model.dsp.dmap.pred[node]))
... except StopIteration:
... value = xl_model.dsp.default_values[node]['value']
... return str(value)
... for k in sorted(xl_model.dsp.dmap.pred[formula], key=lambda x: (len(x), x),
... reverse=True):
... if not isinstance(k, sh.Token):
... formula = formula.replace(k, get_one_line_formula(xl_model, k))
... return formula[1:]
>>> get_one_line_formula(xl_model, 'A1')
'SUM(1, 1, 1, 1, 1)'
>>> get_one_line_formula(xl_model, 'A2')
'SUM(SUM(1, 1, 1, 1, 1), SUM(1, 3))'
i guess the function above does not work with ranges, right ? like A1:C3
In #142 I posted some code that expands ranges, hope you can test it for your case.
thanks for the mutual cooperation, i am kind of curious to see what do you use this formula for, as i believe i have quite a different use case for it :D
will have a deeper look at your code over there to figure that out
i guess the function above does not work with ranges, right ? like A1:C3
Actually it seems that ranges are handled, or at least xl_model.dsp.dmap.pred
dictionary contains the expantions of ranges and also takes care of unused cells. For example in xl_model.dsp.dmap.pred
dictionary of my Excel document I just noted the item
{"='[test.xlsx]WS'!M10:N14": {"'[test.xlsx]WS'!M10": {}, "'[test.xlsx]WS'!M11": {}, "'[test.xlsx]WS'!M12": {}, self: {}}}
which is precisely the expantion of range M10:N14
to the only 3 cells of the entire range used in my document.
It would be useful to get a one liner string representing a DAG from the dispatcher.
so basically having one call like "dsp.get_one_liner(origin = 'A2')" which basically returns all subnodes of the graph starting from A2. Example: