.. _start-intro:
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):
.. code:: console
$ pip install formulas
Or download the last git version and use (with root privileges):
.. code:: console
$ 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:
.. code:: console
$ pip install formulas[all]
Development version
===================
To help with the testing and the development of *formulas*, you can
install the development version:
.. code:: console
$ pip install https://github.com/vinci1it2000/formulas/archive/dev.zip
.. _end-quick:
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'!D2", <Ranges>('[excel.xlsx]DATA'!D2)=[[1]]),
("'[excel.xlsx]'!INPUT_B", <Ranges>('[excel.xlsx]DATA'!A3)=[[6]]),
("'[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
<https://schedula.readthedocs.io/en/master/_build/schedula/utils/dsp/schedula.utils.dsp.DispatchPipe.html#schedula.utils.dsp.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
Raw data
{
"_id": null,
"home_page": "https://github.com/vinci1it2000/formulas",
"name": "formulas",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "python,utility,library,excel,formulas,processing,calculation,dependencies,resolution,scientific,engineering,dispatch,compiling",
"author": "Vincenzo Arcidiacono",
"author_email": "vinci1it2000@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/68/e4/5487652f49684d2c66e9899c770709fca845b33cc40599fbdd7e81826eae/formulas-1.2.7.tar.gz",
"platform": null,
"description": ".. _start-intro:\n\n\nWhat is formulas?\n*****************\n\n**formulas** implements an interpreter for Excel formulas, which\nparses and compile Excel formulas expressions.\n\nMoreover, it compiles Excel workbooks to python and executes without\nusing the Excel COM server. Hence, **Excel is not needed**.\n\n\nInstallation\n************\n\nTo install it use (with root privileges):\n\n.. code:: console\n\n $ pip install formulas\n\nOr download the last git version and use (with root privileges):\n\n.. code:: console\n\n $ python setup.py install\n\n\nInstall extras\n==============\n\nSome additional functionality is enabled installing the following\nextras:\n\n* excel: enables to compile Excel workbooks to python and execute\n using: ``ExcelModel``.\n\n* plot: enables to plot the formula ast and the Excel model.\n\nTo install formulas and all extras, do:\n\n.. code:: console\n\n $ pip install formulas[all]\n\n\nDevelopment version\n===================\n\nTo help with the testing and the development of *formulas*, you can\ninstall the development version:\n\n.. code:: console\n\n $ pip install https://github.com/vinci1it2000/formulas/archive/dev.zip\n\n.. _end-quick:\n\n\nBasic Examples\n**************\n\nThe following sections will show how to:\n\n* parse a Excel formulas;\n\n* load, compile, and execute a Excel workbook;\n\n* extract a sub-model from a Excel workbook;\n\n* add a custom function.\n\n\nParsing formula\n===============\n\nAn example how to parse and execute an Excel formula is the following:\n\n>>> import formulas\n>>> func = formulas.Parser().ast('=(1 + 1) + B3 / A2')[1].compile()\n\nTo visualize formula model and get the input order you can do the\nfollowing:\n\n>>> list(func.inputs)\n['A2', 'B3']\n>>> func.plot(view=False) # Set view=True to plot in the default browser.\nSiteMap([(=((1 + 1) + (B3 / A2)), SiteMap())])\n\n[graph]\n\nFinally to execute the formula and plot the workflow:\n\n>>> func(1, 5)\nArray(7.0, dtype=object)\n>>> func.plot(workflow=True, view=False) # Set view=True to plot in the default browser.\nSiteMap([(=((1 + 1) + (B3 / A2)), SiteMap())])\n\n[graph]\n\n\nExcel workbook\n==============\n\nAn example how to load, calculate, and write an Excel workbook is the\nfollowing:\n\n::\n\n >>> import formulas\n >>> fpath, dir_output = 'excel.xlsx', 'output' \n >>> xl_model = formulas.ExcelModel().loads(fpath).finish()\n >>> xl_model.calculate()\n Solution(...)\n >>> xl_model.write(dirpath=dir_output)\n {'EXCEL.XLSX': {Book: <openpyxl.workbook.workbook.Workbook ...>}}\n\nTip: If you have or could have **circular references**, add\n *circular=True* to *finish* method.\n\nTo plot the dependency graph that depict relationships between Excel\ncells:\n\n>>> dsp = xl_model.dsp\n>>> dsp.plot(view=False) # Set view=True to plot in the default browser.\nSiteMap([(ExcelModel, SiteMap(...))])\n\n[graph]\n\nTo overwrite the default inputs that are defined by the excel file or\nto impose some value to a specific cell:\n\n>>> xl_model.calculate(\n... inputs={\n... \"'[excel.xlsx]'!INPUT_A\": 3, # To overwrite the default value.\n... \"'[excel.xlsx]DATA'!B3\": 1 # To impose a value to B3 cell.\n... },\n... outputs=[\n... \"'[excel.xlsx]DATA'!C2\", \"'[excel.xlsx]DATA'!C4\"\n... ] # To define the outputs that you want to calculate.\n... )\nSolution([(\"'[excel.xlsx]'!INPUT_A\", <Ranges>('[excel.xlsx]DATA'!A2)=[[3]]),\n (\"'[excel.xlsx]DATA'!B3\", <Ranges>('[excel.xlsx]DATA'!B3)=[[1]]),\n (\"'[excel.xlsx]DATA'!A2\", <Ranges>('[excel.xlsx]DATA'!A2)=[[3]]),\n (\"'[excel.xlsx]DATA'!A3\", <Ranges>('[excel.xlsx]DATA'!A3)=[[6]]),\n (\"'[excel.xlsx]DATA'!D2\", <Ranges>('[excel.xlsx]DATA'!D2)=[[1]]),\n (\"'[excel.xlsx]'!INPUT_B\", <Ranges>('[excel.xlsx]DATA'!A3)=[[6]]),\n (\"'[excel.xlsx]DATA'!B2\", <Ranges>('[excel.xlsx]DATA'!B2)=[[9.0]]),\n (\"'[excel.xlsx]DATA'!D3\", <Ranges>('[excel.xlsx]DATA'!D3)=[[2.0]]),\n (\"'[excel.xlsx]DATA'!C2\", <Ranges>('[excel.xlsx]DATA'!C2)=[[10.0]]),\n (\"'[excel.xlsx]DATA'!D4\", <Ranges>('[excel.xlsx]DATA'!D4)=[[3.0]]),\n (\"'[excel.xlsx]DATA'!C4\", <Ranges>('[excel.xlsx]DATA'!C4)=[[4.0]])])\n\nTo build a single function out of an excel model with fixed inputs and\noutputs, you can use the *compile* method of the *ExcelModel* that\nreturns a `DispatchPipe\n<https://schedula.readthedocs.io/en/master/_build/schedula/utils/dsp/schedula.utils.dsp.DispatchPipe.html#schedula.utils.dsp.DispatchPipe>`_.\nThis is a function where the inputs and outputs are defined by the\ndata node ids (i.e., cell references).\n\n>>> func = xl_model.compile(\n... inputs=[\n... \"'[excel.xlsx]'!INPUT_A\", # First argument of the function.\n... \"'[excel.xlsx]DATA'!B3\" # Second argument of the function.\n... ], # To define function inputs.\n... outputs=[\n... \"'[excel.xlsx]DATA'!C2\", \"'[excel.xlsx]DATA'!C4\"\n... ] # To define function outputs.\n... )\n>>> func\n<schedula.utils.dsp.DispatchPipe object at ...>\n>>> [v.value[0, 0] for v in func(3, 1)] # To retrieve the data.\n[10.0, 4.0]\n>>> func.plot(view=False) # Set view=True to plot in the default browser.\nSiteMap([(ExcelModel, SiteMap(...))])\n\n[graph]\n\nAlternatively, to load a partial excel model from the output cells,\nyou can use the *from_ranges* method of the *ExcelModel*:\n\n>>> xl = formulas.ExcelModel().from_ranges(\n... \"'[%s]DATA'!C2:D2\" % fpath, # Output range.\n... \"'[%s]DATA'!B4\" % fpath, # Output cell.\n... )\n>>> dsp = xl.dsp\n>>> sorted(dsp.data_nodes)\n[\"'[excel.xlsx]'!INPUT_A\",\n \"'[excel.xlsx]'!INPUT_B\",\n \"'[excel.xlsx]'!INPUT_C\",\n \"'[excel.xlsx]DATA'!A2\",\n \"'[excel.xlsx]DATA'!A3\",\n \"'[excel.xlsx]DATA'!A3:A4\",\n \"'[excel.xlsx]DATA'!A4\",\n \"'[excel.xlsx]DATA'!B2\",\n \"'[excel.xlsx]DATA'!B3\",\n \"'[excel.xlsx]DATA'!B4\",\n \"'[excel.xlsx]DATA'!C2\",\n \"'[excel.xlsx]DATA'!D2\"]\n\n[graph]\n\n\nJSON export/import\n------------------\n\nThe *ExcelModel* can be exported/imported to/from a readable JSON\nformat. The reason of this functionality is to have format that can be\neasily maintained (e.g. using version control programs like *git*).\nFollows an example on how to export/import to/from JSON an\n*ExcelModel*:\n\n::\n\n >>> import json\n >>> xl_dict = xl_model.to_dict() # To JSON-able dict.\n >>> xl_dict # Exported format. \n {\n \"'[excel.xlsx]DATA'!A1\": \"inputs\",\n \"'[excel.xlsx]DATA'!B1\": \"Intermediate\",\n \"'[excel.xlsx]DATA'!C1\": \"outputs\",\n \"'[excel.xlsx]DATA'!D1\": \"defaults\",\n \"'[excel.xlsx]DATA'!A2\": 2,\n \"'[excel.xlsx]DATA'!D2\": 1,\n \"'[excel.xlsx]DATA'!A3\": 6,\n \"'[excel.xlsx]DATA'!A4\": 5,\n \"'[excel.xlsx]DATA'!B2\": \"=('[excel.xlsx]DATA'!A2 + '[excel.xlsx]DATA'!A3)\",\n \"'[excel.xlsx]DATA'!C2\": \"=(('[excel.xlsx]DATA'!B2 / '[excel.xlsx]DATA'!B3) + '[excel.xlsx]DATA'!D2)\",\n \"'[excel.xlsx]DATA'!B3\": \"=('[excel.xlsx]DATA'!B2 - '[excel.xlsx]DATA'!A3)\",\n \"'[excel.xlsx]DATA'!C3\": \"=(('[excel.xlsx]DATA'!C2 * '[excel.xlsx]DATA'!A2) + '[excel.xlsx]DATA'!D3)\",\n \"'[excel.xlsx]DATA'!D3\": \"=(1 + '[excel.xlsx]DATA'!D2)\",\n \"'[excel.xlsx]DATA'!B4\": \"=MAX('[excel.xlsx]DATA'!A3:A4, '[excel.xlsx]DATA'!B2)\",\n \"'[excel.xlsx]DATA'!C4\": \"=(('[excel.xlsx]DATA'!B3 ^ '[excel.xlsx]DATA'!C2) + '[excel.xlsx]DATA'!D4)\",\n \"'[excel.xlsx]DATA'!D4\": \"=(1 + '[excel.xlsx]DATA'!D3)\"\n }\n >>> xl_json = json.dumps(xl_dict, indent=True) # To JSON.\n >>> xl_model = formulas.ExcelModel().from_dict(json.loads(xl_json)) # From JSON.\n\n\nCustom functions\n================\n\nAn example how to add a custom function to the formula parser is the\nfollowing:\n\n>>> import formulas\n>>> FUNCTIONS = formulas.get_functions()\n>>> FUNCTIONS['MYFUNC'] = lambda x, y: 1 + y + x\n>>> func = formulas.Parser().ast('=MYFUNC(1, 2)')[1].compile()\n>>> func()\n4\n",
"bugtrack_url": null,
"license": "EUPL 1.1+",
"summary": "Parse and compile Excel formulas and workbooks in python code.",
"version": "1.2.7",
"project_urls": {
"Documentation": "http://formulas.readthedocs.io",
"Donate": "https://donorbox.org/formulas",
"Download": "https://github.com/vinci1it2000/formulas/tarball/v1.2.7",
"Homepage": "https://github.com/vinci1it2000/formulas",
"Issue tracker": "https://github.com/vinci1it2000/formulas/issues"
},
"split_keywords": [
"python",
"utility",
"library",
"excel",
"formulas",
"processing",
"calculation",
"dependencies",
"resolution",
"scientific",
"engineering",
"dispatch",
"compiling"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "60b3c7b511d803aa8f55e756e1da0734965a7385ff34533dcc7ed5a5adb0c8dd",
"md5": "01c6f587143d2c609761d7b8264d46e6",
"sha256": "a2987a2f01e4a6ee82a856b61689c507e3dd9af1b2d8c7d36ccdc29580aacbe4"
},
"downloads": -1,
"filename": "formulas-1.2.7-py2.py3-none-any.whl",
"has_sig": false,
"md5_digest": "01c6f587143d2c609761d7b8264d46e6",
"packagetype": "bdist_wheel",
"python_version": "py2.py3",
"requires_python": null,
"size": 63451,
"upload_time": "2023-11-15T09:46:40",
"upload_time_iso_8601": "2023-11-15T09:46:40.556030Z",
"url": "https://files.pythonhosted.org/packages/60/b3/c7b511d803aa8f55e756e1da0734965a7385ff34533dcc7ed5a5adb0c8dd/formulas-1.2.7-py2.py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "68e45487652f49684d2c66e9899c770709fca845b33cc40599fbdd7e81826eae",
"md5": "2147f3c38e903b7c5d8412ea794cc0f1",
"sha256": "59f22b799f5bd519fb1a305ff571771901d3f3a0d32df959ac0126781337e515"
},
"downloads": -1,
"filename": "formulas-1.2.7.tar.gz",
"has_sig": false,
"md5_digest": "2147f3c38e903b7c5d8412ea794cc0f1",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 67233,
"upload_time": "2023-11-15T09:46:42",
"upload_time_iso_8601": "2023-11-15T09:46:42.414881Z",
"url": "https://files.pythonhosted.org/packages/68/e4/5487652f49684d2c66e9899c770709fca845b33cc40599fbdd7e81826eae/formulas-1.2.7.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-11-15 09:46:42",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "vinci1it2000",
"github_project": "formulas",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "formulas"
}
JSON
