Python “Formula” Package: How do I parse Excel formula with a range of cells?

Python “Formula” Package: How do I parse Excel formula with a range of cells? - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Python “Formula” Package: How do I parse Excel formula with a range of cells? (/thread-34259.html)

Python “Formula” Package: How do I parse Excel formula with a range of cells? - JaneTan - Jul-12-2021

Hi,

I have read the documentation of the "Formula" package at https://formulas.readthedocs.io/en/stable/doc.html#parsing-formula but it doesn't explain how to input the values for an Excel function that uses a range of cells.

Hope someone can help with the below queries. Thank you!

1) For Excel function with a range of values separated by :, how do I input into the parsed function?

EG

val='=SUM(A1:B3)'
func = formulas.Parser().ast(val)[1].compile()

print (func.inputs)
output:
OrderedDict([('A1:B3', <Ranges>(A1:B3))])

print (func(25,4))
output:
raise TypeError('too many positional arguments') from None

TypeError: too many positional arguments

2) Excel Function SUMPRODUCT doesn't seem to be supported, I get [[#VALUE!]] error

EG

val='=SUMPRODUCT(A1,B3)'
func = formulas.Parser().ast(val)[1].compile()

print (func.inputs)
output:
OrderedDict([('A1', <Ranges>(A1)), ('B3', <Ranges>(B3))])

print (func(25,4))
output:
[[#VALUE!]]

RE: Python “Formula” Package: How do I parse Excel formula with a range of cells? - jefsummers - Jul-12-2021

1. I would use Pandas. Check the documentation for the read_excel function HERE. You can specify the file, sheet, and columns to be imported.

2. Once in Python, do not expect to use Excel functions. Different program. You will likely find equivalents. Again, check out Pandas.