formulas: An Excel formulas interpreter in Python.

release:	0.1.4
date:	2018-10-19 11:00:00
repository:	https://github.com/vinci1it2000/formulas
pypi-repo:	https://pypi.org/project/formulas/
docs:	http://formulas.readthedocs.io/
wiki:	https://github.com/vinci1it2000/formulas/wiki/
download:	http://github.com/vinci1it2000/formulas/releases/
donate:	https://donorbox.org/formulas
keywords:	excel, formulas, interpreter, compiler, dispatch
developers:
license:	EUPL 1.1+

What is formulas?

formulas implements an interpreter for Excel formulas, which parses and compile Excel formulas expressions.

Moreover, it compiles Excel workbooks to python and executes without using the Excel COM server. Hence, Excel is not needed.

Installation

To install it use (with root privileges):

$ pip install formulas

Or download the last git version and use (with root privileges):

$ python setup.py install

Install extras

Some additional functionality is enabled installing the following extras:

• excel: enables to compile Excel workbooks to python and execute using: :class:`~formulas.excel.ExcelModel`.
• plot: enables to plot the formula ast and the Excel model.

To install formulas and all extras, do:

$ pip install formulas[all]

Basic Examples

The following sections will show how to:

• parse a Excel formulas;
• load, compile, and execute a Excel workbook;
• extract a sub-model from a Excel workbook;
• add a custom function.

Parsing formula

An example how to parse and execute an Excel formula is the following:

>>> import formulas
>>> func = formulas.Parser().ast('=(1 + 1) + B3 / A2')[1].compile()

To visualize formula model and get the input order you can do the following:

.. dispatcher:: func
   :opt: graph_attr={'ratio': '1'}
   :code:

    >>> list(func.inputs)
    ['A2', 'B3']
    >>> func.plot(view=False)  # Set view=True to plot in the default browser.
    SiteMap([(=((1 + 1) + (B3 / A2)), SiteMap())])

Finally to execute the formula and plot the workflow:

.. dispatcher:: func
   :opt: workflow=True, graph_attr={'ratio': '1'}
   :code:

    >>> func(1, 5)
    OperatorArray(7.0, dtype=object)
    >>> func.plot(workflow=True, view=False)  # Set view=True to plot in the default browser.
    SiteMap([(=((1 + 1) + (B3 / A2)), SiteMap())])

Excel workbook

An example how to load, calculate, and write an Excel workbook is the following:

.. testsetup::

    >>> import os.path as osp
    >>> from setup import mydir
    >>> fpath = osp.join(mydir, 'test/test_files/excel.xlsx')

>>> import formulas
>>> fpath = 'file.xlsx'  # doctest: +SKIP
>>> xl_model = formulas.ExcelModel().loads(fpath).finish()
>>> xl_model.calculate()
Solution(...)
>>> xl_model.write()
{'EXCEL.XLSX': {Book: <openpyxl.workbook.workbook.Workbook ...>}}

Tip

If you have or could have circular references, add circular=True to finish method.

To plot the dependency graph that depict relationships between Excel cells:

.. dispatcher:: dsp
   :code:

    >>> dsp = xl_model.dsp
    >>> dsp.plot(view=False)  # Set view=True to plot in the default browser.
    SiteMap([(Dispatcher ..., SiteMap())])

To compile, execute, and plot a Excel sub-model you can do the following:

.. dispatcher:: func
   :code:

    >>> inputs = ["'[EXCEL.XLSX]DATA'!A2"]  # input cells
    >>> outputs = ["'[EXCEL.XLSX]DATA'!C2"]  # output cells
    >>> func = xl_model.compile(inputs, outputs)
    >>> func(2).value[0,0]
    4.0
    >>> func.plot(view=False)  # Set view=True to plot in the default browser.
    SiteMap([(Dispatcher ..., SiteMap())])

Custom functions

An example how to add a custom function to the formula parser is the following:

>>> import formulas
>>> FUNCTIONS = formulas.get_functions()
>>> FUNCTIONS['MYFUNC'] = lambda x, y: 1 + y + x
>>> func = formulas.Parser().ast('=MYFUNC(1, 2)')[1].compile()
>>> func()
4

Next moves

Things yet to do: implement the missing Excel formulas.