Excel formulas interpreter in Python.

License: EUPL-1.1

Language: Python

formulas: An Excel formulas interpreter in Python.

Latest Version in PyPI Travis build status Appveyor build status Code coverage Documentation status Dependencies up-to-date? Issues count Supported Python versions Project License Live Demo

release: 0.4.0
date: 2019-08-31 19:00:00
keywords: excel, formulas, interpreter, compiler, dispatch
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.


To install it use (with root privileges):

$ pip install formulas

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

$ python install

Install extras

Some additional functionality is enabled installing the following extras:

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'}

    >>> 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'}

    >>> func(1, 5)
    Array(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')
    >>> dir_output = osp.join(mydir, 'test/test_files/tmp')

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


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

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

To overwrite the default inputs that are defined by the excel file or to impose some value to a specific cell:

>>> xl_model.calculate(
...     inputs={
...         "'[EXCEL.XLSX]DATA'!A2": 3,  # To overwrite the default value.
...         "'[EXCEL.XLSX]DATA'!B3": 1  # To impose a value to B3 cell.
...     },
...     outputs=[
...        "'[EXCEL.XLSX]DATA'!C2", "'[EXCEL.XLSX]DATA'!C4"
...     ] # To define the outputs that you want to calculate.
... )
Solution([("'[EXCEL.XLSX]DATA'!A2", <Ranges>('[EXCEL.XLSX]DATA'!A2)=[[3]]),
          ("'[EXCEL.XLSX]DATA'!A3", <Ranges>('[EXCEL.XLSX]DATA'!A3)=[[6]]),
          ("'[EXCEL.XLSX]DATA'!B3", <Ranges>('[EXCEL.XLSX]DATA'!B3)=[[1]]),
          ("'[EXCEL.XLSX]DATA'!B2", <Ranges>('[EXCEL.XLSX]DATA'!B2)=[[9.0]]),
          ("'[EXCEL.XLSX]DATA'!C2", <Ranges>('[EXCEL.XLSX]DATA'!C2)=[[9.0]]),
          ("'[EXCEL.XLSX]DATA'!C4", <Ranges>('[EXCEL.XLSX]DATA'!C4)=[[1.0]])])

To build a single function out of an excel model with fixed inputs and outputs, you can use the compile method of the ExcelModel that returns a DispatchPipe. This is a function where the inputs and outputs are defined by the data node ids (i.e., cell references).

.. dispatcher:: func

    >>> func = xl_model.compile(
    ...     inputs=[
    ...         "'[EXCEL.XLSX]DATA'!A2",  # First argument of the function.
    ...         "'[EXCEL.XLSX]DATA'!B3"   # Second argument of the function.
    ...     ], # To define function inputs.
    ...     outputs=[
    ...         "'[EXCEL.XLSX]DATA'!C2", "'[EXCEL.XLSX]DATA'!C4"
    ...     ] # To define function outputs.
    ... )
    >>> func
    <schedula.utils.dsp.DispatchPipe object at ...>
    >>> [v.value[0, 0] for v in func(3, 1)]  # To retrieve the data.
    [9.0, 1.0]
    >>> func.plot(view=False)  # Set view=True to plot in the default browser.
    SiteMap([(ExcelModel, 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()

Next moves

Things yet to do: implement the missing Excel formulas.

Project Statistics

Sourcerank 7
Repository Size 1.97 MB
Stars 50
Forks 20
Watchers 5
Open issues 0
Dependencies 32
Contributors 5
Tags 18
Last updated
Last pushed

Top Contributors See all

vinci1it2000 Nicholas Bollweg Matthew Gerring Edward Atkins Brad-eki

Packages Referencing this Repo

Parse and compile Excel formulas and workbooks in python code.
Latest release 0.4.0 - Updated - 50 stars

Recent Tags See all

v0.4.0 August 31, 2019
v0.3.0 April 24, 2019
v0.2.0 December 11, 2018
v0.1.4 October 19, 2018
v0.1.3 October 09, 2018
v0.1.2 September 12, 2018
v0.1.1 September 11, 2018
v0.1.0 July 20, 2018
v0.0.10 June 05, 2018
v0.0.9 May 28, 2018
v0.0.8 May 23, 2018
v0.0.7 July 20, 2017
v0.0.6 May 31, 2017
v0.0.5 May 04, 2017
v0.0.4 February 10, 2017

Something wrong with this page? Make a suggestion

Last synced: 2019-09-03 06:21:34 UTC

Login to resync this repository