# Calculate XLSX formulas
[](https://github.com/devind-team/devind-django-dictionaries/actions)
[](https://coveralls.io/github/devind-team/devind-django-dictionaries?branch=main)
[](https://badge.fury.io/py/xlsx_evaluate)
[](https://opensource.org/licenses/MIT)
**xlsx_evaluate** - python library to convert excel functions in python code without the need for Excel itself within the scope of supported features.
This library is fork [xlcalculator](https://github.com/bradbase/xlcalculator). Use this library.
# Summary
- [Currently supports](docs/support.rst)
- [Supported Functions](docs/support_functions.rst)
- [Adding/Registering Excel Functions](docs/support_functions.rst)
- [Excel number precision](docs/number_precision.rst)
- [Test](docs/test.rst)
# Installation
```shell
# pip
pip install xlsx-evaluate
# poetry
poetry add xlsx-evaluate
```
# Example
```python
input_dict = {
'B4': 0.95,
'B2': 1000,
"B19": 0.001,
'B20': 4,
'B22': 1,
'B23': 2,
'B24': 3,
'B25': '=B2*B4',
'B26': 5,
'B27': 6,
'B28': '=B19 * B20 * B22',
'C22': '=SUM(B22:B28)',
"D1": "abc",
"D2": "bca",
"D3": "=CONCATENATE(D1, D2)",
}
from xlsx_evaluate import ModelCompiler
from xlsx_evaluate import Evaluator
compiler = ModelCompiler()
my_model = compiler.read_and_parse_dict(input_dict)
evaluator = Evaluator(my_model)
for formula in my_model.formulae:
print(f'Formula {formula} evaluates to {evaluator.evaluate(formula)}')
# cells need a sheet and Sheet1 is default.
evaluator.set_cell_value('Sheet1!B22', 100)
print('Formula B28 now evaluates to', evaluator.evaluate('Sheet1!B28'))
print('Formula C22 now evaluates to', evaluator.evaluate('Sheet1!C22'))
print('Formula D3 now evaluates to', evaluator.evaluate("Sheet1!D3"))
```
# TODO
- Do not treat ranges as a granular AST node it instead as an operation ":" of
two cell references to create the range. That will make implementing
features like ``A1:OFFSET(...)`` easy to implement.
- Support for alternative range evaluation: by ref (pointer), by expr (lazy
eval) and current eval mode.
* Pointers would allow easy implementations of functions like OFFSET().
* Lazy evals will allow efficient implementation of IF() since execution
of true and false expressions can be delayed until it is decided which
expression is needed.
- Implement array functions. It is really not that hard once a proper
RangeData class has been implemented on which one can easily act with scalar
functions.
- Improve testing
- Refactor model and evaluator to use pass-by-object-reference for values of
cells which then get "used"/referenced by ranges, defined names and formulas
- Handle multi-file addresses
- Improve integration with pyopenxl for reading and writing files [example of
problem space](https://stackoverflow.com/questions/40248564/pre-calculate-excel-formulas-when-exporting-data-with-python)
Raw data
{
"_id": null,
"home_page": "https://github.com/devind-team/xlsx_evaluate",
"name": "xlsx-evaluate",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.9",
"maintainer_email": "",
"keywords": "openpyxlsx,xlsx,formulas,evaluate",
"author": "Victor",
"author_email": "lyferov@yandex.ru",
"download_url": "https://files.pythonhosted.org/packages/7c/b0/194fe96f3b962c335ef183548cd00e68c4827fc87bf93d67432aec389d4e/xlsx_evaluate-0.5.0.tar.gz",
"platform": null,
"description": "# Calculate XLSX formulas\n\n[](https://github.com/devind-team/devind-django-dictionaries/actions)\n[](https://coveralls.io/github/devind-team/devind-django-dictionaries?branch=main)\n[](https://badge.fury.io/py/xlsx_evaluate)\n[](https://opensource.org/licenses/MIT)\n\n**xlsx_evaluate** - python library to convert excel functions in python code without the need for Excel itself within the scope of supported features.\n\nThis library is fork [xlcalculator](https://github.com/bradbase/xlcalculator). Use this library.\n\n# Summary\n\n- [Currently supports](docs/support.rst)\n- [Supported Functions](docs/support_functions.rst)\n- [Adding/Registering Excel Functions](docs/support_functions.rst)\n- [Excel number precision](docs/number_precision.rst)\n- [Test](docs/test.rst)\n\n# Installation\n\n```shell\n# pip\npip install xlsx-evaluate\n# poetry\npoetry add xlsx-evaluate\n```\n\n\n# Example\n\n```python\ninput_dict = {\n 'B4': 0.95,\n 'B2': 1000,\n \"B19\": 0.001,\n 'B20': 4,\n 'B22': 1,\n 'B23': 2,\n 'B24': 3,\n 'B25': '=B2*B4',\n 'B26': 5,\n 'B27': 6,\n 'B28': '=B19 * B20 * B22',\n 'C22': '=SUM(B22:B28)',\n \"D1\": \"abc\",\n \"D2\": \"bca\",\n \"D3\": \"=CONCATENATE(D1, D2)\",\n }\n\nfrom xlsx_evaluate import ModelCompiler\nfrom xlsx_evaluate import Evaluator\n\ncompiler = ModelCompiler()\nmy_model = compiler.read_and_parse_dict(input_dict)\nevaluator = Evaluator(my_model)\n\nfor formula in my_model.formulae:\n print(f'Formula {formula} evaluates to {evaluator.evaluate(formula)}')\n\n# cells need a sheet and Sheet1 is default.\nevaluator.set_cell_value('Sheet1!B22', 100)\nprint('Formula B28 now evaluates to', evaluator.evaluate('Sheet1!B28'))\nprint('Formula C22 now evaluates to', evaluator.evaluate('Sheet1!C22'))\nprint('Formula D3 now evaluates to', evaluator.evaluate(\"Sheet1!D3\"))\n```\n\n# TODO\n\n- Do not treat ranges as a granular AST node it instead as an operation \":\" of\n two cell references to create the range. That will make implementing\n features like ``A1:OFFSET(...)`` easy to implement.\n\n- Support for alternative range evaluation: by ref (pointer), by expr (lazy\n eval) and current eval mode.\n\n * Pointers would allow easy implementations of functions like OFFSET().\n\n * Lazy evals will allow efficient implementation of IF() since execution\n of true and false expressions can be delayed until it is decided which\n expression is needed.\n\n- Implement array functions. It is really not that hard once a proper\n RangeData class has been implemented on which one can easily act with scalar\n functions.\n\n- Improve testing\n\n- Refactor model and evaluator to use pass-by-object-reference for values of\n cells which then get \"used\"/referenced by ranges, defined names and formulas\n\n- Handle multi-file addresses\n\n- Improve integration with pyopenxl for reading and writing files [example of\n problem space](https://stackoverflow.com/questions/40248564/pre-calculate-excel-formulas-when-exporting-data-with-python)\n\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Calculate XLSX formulas",
"version": "0.5.0",
"split_keywords": [
"openpyxlsx",
"xlsx",
"formulas",
"evaluate"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "fea0b6f384a5e0cc3f43f0439c7bc46535d9a11c0ba09b6a051f4e4b6d40f3ad",
"md5": "28fbf2381409d4e4321232ece758da05",
"sha256": "a327f7ac653f2436e2eb4119aaf9fe8dc75a3820ffa458d67314715a0acb0b83"
},
"downloads": -1,
"filename": "xlsx_evaluate-0.5.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "28fbf2381409d4e4321232ece758da05",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.9",
"size": 48220,
"upload_time": "2023-04-12T11:15:36",
"upload_time_iso_8601": "2023-04-12T11:15:36.903039Z",
"url": "https://files.pythonhosted.org/packages/fe/a0/b6f384a5e0cc3f43f0439c7bc46535d9a11c0ba09b6a051f4e4b6d40f3ad/xlsx_evaluate-0.5.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "7cb0194fe96f3b962c335ef183548cd00e68c4827fc87bf93d67432aec389d4e",
"md5": "b446cb58630554b078c45a9d796be3c7",
"sha256": "29a7cbd6e5bea0014678d7ec2e29581715fdb6ef7ecb9e27a4b8523da7abfdf1"
},
"downloads": -1,
"filename": "xlsx_evaluate-0.5.0.tar.gz",
"has_sig": false,
"md5_digest": "b446cb58630554b078c45a9d796be3c7",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.9",
"size": 40957,
"upload_time": "2023-04-12T11:15:39",
"upload_time_iso_8601": "2023-04-12T11:15:39.476309Z",
"url": "https://files.pythonhosted.org/packages/7c/b0/194fe96f3b962c335ef183548cd00e68c4827fc87bf93d67432aec389d4e/xlsx_evaluate-0.5.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-04-12 11:15:39",
"github": true,
"gitlab": false,
"bitbucket": false,
"github_user": "devind-team",
"github_project": "xlsx_evaluate",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "xlsx-evaluate"
}
JSON
