Calculate XLSX formulas
Project description
Calculate XLSX formulas
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. Use this library.
Summary
- Currently supports
- Supported Functions
- Adding/Registering Excel Functions
- Excel number precision
- Test
Installation
# pip
pip install xlsx-evaluate
# poetry
poetry add xlsx-evaluate
Example
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
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for xlsx_evaluate-0.5.0-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | a327f7ac653f2436e2eb4119aaf9fe8dc75a3820ffa458d67314715a0acb0b83 |
|
MD5 | 28fbf2381409d4e4321232ece758da05 |
|
BLAKE2b-256 | fea0b6f384a5e0cc3f43f0439c7bc46535d9a11c0ba09b6a051f4e4b6d40f3ad |