Skip to main content

Parse and compile Excel formulas and workbooks in python code.

Project description

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: ExcelModel.

  • plot: enables to plot the formula ast and the Excel model.

To install formulas and all extras, do:

$ pip install formulas[all]

Development version

To help with the testing and the development of formulas, you can install the development version:

$ pip install https://github.com/vinci1it2000/formulas/archive/dev.zip

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:

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

[graph]

Finally to execute the formula and plot the workflow:

>>> 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({})})

[graph]

Excel workbook

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

>>> import formulas
>>> fpath, dir_output = 'excel.xlsx', 'output'
>>> xl_model = formulas.ExcelModel().loads(fpath).finish()
>>> xl_model.calculate()
Solution(...)
>>> xl_model.write(dirpath=dir_output)
{'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:

>>> dsp = xl_model.dsp
>>> dsp.plot(view=False)  # Set view=True to plot in the default browser.
SiteMap({ExcelModel: SiteMap(...)})

[graph]

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]'!INPUT_A": 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]'!INPUT_A": <Ranges>('[excel.xlsx]DATA'!A2)=[[3]],
           "'[excel.xlsx]DATA'!B3": <Ranges>('[excel.xlsx]DATA'!B3)=[[1]],
           "'[excel.xlsx]DATA'!A2": <Ranges>('[excel.xlsx]DATA'!A2)=[[3]],
           "'[excel.xlsx]DATA'!A3": <Ranges>('[excel.xlsx]DATA'!A3)=[[6]],
           "'[excel.xlsx]DATA'!A4": <Ranges>('[excel.xlsx]DATA'!A4)=[[5]],
           "'[excel.xlsx]DATA'!D2": <Ranges>('[excel.xlsx]DATA'!D2)=[[1]],
           "'[excel.xlsx]'!INPUT_B": <Ranges>('[excel.xlsx]DATA'!A3)=[[6]],
           "'[excel.xlsx]'!INPUT_C": <Ranges>('[excel.xlsx]DATA'!A4)=[[5]],
           "'[excel.xlsx]DATA'!A3:A4": <Ranges>('[excel.xlsx]DATA'!A3:A4)=[[6] [5]],
           "'[excel.xlsx]DATA'!B2": <Ranges>('[excel.xlsx]DATA'!B2)=[[9.0]],
           "'[excel.xlsx]DATA'!D3": <Ranges>('[excel.xlsx]DATA'!D3)=[[2.0]],
           "'[excel.xlsx]DATA'!C2": <Ranges>('[excel.xlsx]DATA'!C2)=[[10.0]],
           "'[excel.xlsx]DATA'!D4": <Ranges>('[excel.xlsx]DATA'!D4)=[[3.0]],
           "'[excel.xlsx]DATA'!C4": <Ranges>('[excel.xlsx]DATA'!C4)=[[4.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).

>>> func = xl_model.compile(
...     inputs=[
...         "'[excel.xlsx]'!INPUT_A",  # 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.
[10.0, 4.0]
>>> func.plot(view=False)  # Set view=True to plot in the default browser.
SiteMap({ExcelModel: SiteMap(...)})

[graph]

Alternatively, to load a partial excel model from the output cells, you can use the from_ranges method of the ExcelModel:

>>> xl = formulas.ExcelModel().from_ranges(
...     "'[%s]DATA'!C2:D2" % fpath,  # Output range.
...     "'[%s]DATA'!B4" % fpath,  # Output cell.
... )
>>> dsp = xl.dsp
>>> sorted(dsp.data_nodes)
["'[excel.xlsx]'!INPUT_A",
 "'[excel.xlsx]'!INPUT_B",
 "'[excel.xlsx]'!INPUT_C",
 "'[excel.xlsx]DATA'!A2",
 "'[excel.xlsx]DATA'!A3",
 "'[excel.xlsx]DATA'!A3:A4",
 "'[excel.xlsx]DATA'!A4",
 "'[excel.xlsx]DATA'!B2",
 "'[excel.xlsx]DATA'!B3",
 "'[excel.xlsx]DATA'!B4",
 "'[excel.xlsx]DATA'!C2",
 "'[excel.xlsx]DATA'!D2"]

[graph]

JSON export/import

The ExcelModel can be exported/imported to/from a readable JSON format. The reason of this functionality is to have format that can be easily maintained (e.g. using version control programs like git). Follows an example on how to export/import to/from JSON an ExcelModel:

>>> import json
>>> xl_dict = xl_model.to_dict()  # To JSON-able dict.
>>> xl_dict  # Exported format.
{
 "'[excel.xlsx]DATA'!A1": "inputs",
 "'[excel.xlsx]DATA'!B1": "Intermediate",
 "'[excel.xlsx]DATA'!C1": "outputs",
 "'[excel.xlsx]DATA'!D1": "defaults",
 "'[excel.xlsx]DATA'!A2": 2,
 "'[excel.xlsx]DATA'!D2": 1,
 "'[excel.xlsx]DATA'!A3": 6,
 "'[excel.xlsx]DATA'!A4": 5,
 "'[excel.xlsx]DATA'!B2": "=('[excel.xlsx]DATA'!A2 + '[excel.xlsx]DATA'!A3)",
 "'[excel.xlsx]DATA'!C2": "=(('[excel.xlsx]DATA'!B2 / '[excel.xlsx]DATA'!B3) + '[excel.xlsx]DATA'!D2)",
 "'[excel.xlsx]DATA'!B3": "=('[excel.xlsx]DATA'!B2 - '[excel.xlsx]DATA'!A3)",
 "'[excel.xlsx]DATA'!C3": "=(('[excel.xlsx]DATA'!C2 * '[excel.xlsx]DATA'!A2) + '[excel.xlsx]DATA'!D3)",
 "'[excel.xlsx]DATA'!D3": "=(1 + '[excel.xlsx]DATA'!D2)",
 "'[excel.xlsx]DATA'!B4": "=MAX('[excel.xlsx]DATA'!A3:A4, '[excel.xlsx]DATA'!B2)",
 "'[excel.xlsx]DATA'!C4": "=(('[excel.xlsx]DATA'!B3 ^ '[excel.xlsx]DATA'!C2) + '[excel.xlsx]DATA'!D4)",
 "'[excel.xlsx]DATA'!D4": "=(1 + '[excel.xlsx]DATA'!D3)"
}
>>> xl_json = json.dumps(xl_dict, indent=True)  # To JSON.
>>> xl_model = formulas.ExcelModel().from_dict(json.loads(xl_json))  # From JSON.

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

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

formulas-1.2.10.tar.gz (74.1 kB view details)

Uploaded Source

Built Distribution

formulas-1.2.10-py2.py3-none-any.whl (69.0 kB view details)

Uploaded Python 2Python 3

File details

Details for the file formulas-1.2.10.tar.gz.

File metadata

  • Download URL: formulas-1.2.10.tar.gz
  • Upload date:
  • Size: 74.1 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/6.0.1 CPython/3.11.0

File hashes

Hashes for formulas-1.2.10.tar.gz
Algorithm Hash digest
SHA256 c04bb4ca9bc90d716647ec4507473e38b34bb6d13be28ceee09d2ab0ec2b4dd4
MD5 56f2d8063cce858966fc6ba9bc3d3275
BLAKE2b-256 9d22ba360ad5f76877069a4b36cef48d2662144e6a0984f3ca5455f0ca2ab1aa

See more details on using hashes here.

File details

Details for the file formulas-1.2.10-py2.py3-none-any.whl.

File metadata

  • Download URL: formulas-1.2.10-py2.py3-none-any.whl
  • Upload date:
  • Size: 69.0 kB
  • Tags: Python 2, Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/6.0.1 CPython/3.11.0

File hashes

Hashes for formulas-1.2.10-py2.py3-none-any.whl
Algorithm Hash digest
SHA256 eb029109e9d4c1abad40bbb09c18ca1476bff49da2275b8da2ab50259d08770c
MD5 d5e9158d8b3c09e251c2e93a38799edd
BLAKE2b-256 3d148f0cfa85bc25a9f3ea968a8484a5649350e0c65e405b18ccd60cabe21792

See more details on using hashes here.

Supported by

AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Pingdom Monitoring Sentry Error logging StatusPage Status page