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