def test_str():
excel_formula = ExcelFormula('=E54-E48')
assert '=E54-E48' == str(excel_formula)
assert '_C_("E54") - _C_("E48")' == excel_formula.python_code
excel_formula.base_formula = None
assert '_C_("E54") - _C_("E48")' == str(excel_formula)
excel_formula._ast = None
excel_formula._rpn = None
excel_formula._python_code = None
assert '' == str(excel_formula)
def test_compiled_python_cache():
formula = ExcelFormula('=1 + 2')
# first call does the calc, the second uses cached
compiled_python = formula.compiled_python
assert compiled_python == formula.compiled_python
# rebuild from marshalled
formula._compiled_python = None
assert compiled_python == formula.compiled_python
# invalidate the marshalled code, rebuild from source
formula._compiled_python = None
formula._marshalled_python = 'junk'
assert compiled_python == formula.compiled_python
def test_eval_exception():
eval_ctx = ExcelFormula.build_eval_context(
lambda x: 1 + 'a', lambda x: [[1, 1], [1, DIV0]],
logging.getLogger('pycel'))
with pytest.raises(FormulaEvalError):
eval_ctx(ExcelFormula('=a1'))
def test_if_args_error():
eval_context = ExcelFormula.build_eval_context(lambda x: 1, lambda x: 1)
assert 0 == eval_context(ExcelFormula('=if(1,0)'))
assert VALUE_ERROR == eval_context(ExcelFormula('=if(#VALUE!,1)'))
assert VALUE_ERROR == eval_context(ExcelFormula('=if(#VALUE!,1,0)'))
assert VALUE_ERROR == eval_context(ExcelFormula('=if(1,#VALUE!,0)'))
assert VALUE_ERROR == eval_context(ExcelFormula('=if(0,1,#VALUE!)'))
def test_math_wrap():
eval_context = ExcelFormula.build_eval_context(
lambda x: None, lambda x: DIV0)
assert 1 == eval_context(ExcelFormula('=1 + sin(A1)'))
assert DIV0 == eval_context(ExcelFormula('=1 + sin(A1:B1)'))
assert 1 == eval_context(ExcelFormula('=1 + abs(A1)'))
assert DIV0 == eval_context(ExcelFormula('=1 + abs(A1:B1)'))
def test_plugins():
with mock.patch('pycel.excelformula.ExcelFormula.default_modules', ()):
eval_ctx = ExcelFormula.build_eval_context(None, None)
with pytest.raises(UnknownFunction):
eval_ctx(ExcelFormula('=sum({1,2,3})'))
with mock.patch('pycel.excelformula.ExcelFormula.default_modules', ()):
eval_ctx = ExcelFormula.build_eval_context(
None, None, plugins=('pycel.excellib', ))
assert eval_ctx(ExcelFormula('=sum({1,2,3})')) == 6
with mock.patch('pycel.excelformula.ExcelFormula.default_modules', ()):
eval_ctx = ExcelFormula.build_eval_context(
None, None, plugins='pycel.excellib')
assert eval_ctx(ExcelFormula('=sum({1,2,3})')) == 6
with mock.patch('pycel.excelformula.ExcelFormula.default_modules',
('pycel.excellib', )):
eval_ctx = ExcelFormula.build_eval_context(None, None)
assert eval_ctx(ExcelFormula('=sum({1,2,3})')) == 6
def test_lineno_on_error_reporting(empty_eval_context):
excel_formula = ExcelFormula('')
excel_formula._python_code = 'X'
excel_formula.lineno = 6
excel_formula.filename = 'a_file'
msg = 'File "a_file", line 6,'
with pytest.raises(UnknownFunction, match=msg):
empty_eval_context(excel_formula)
excel_formula._python_code = '(x)'
excel_formula._compiled_python = None
excel_formula._marshalled_python = None
excel_formula.compiled_lambda = None
excel_formula.lineno = 60
with pytest.raises(UnknownFunction, match='File "a_file", line 60,'):
empty_eval_context(excel_formula)
def test_error_logging(caplog):
eval_ctx = ExcelFormula.build_eval_context(
lambda x: DIV0, lambda x: [[1, 1], [1, DIV0]],
logging.getLogger('pycel_x'))
caplog.set_level(logging.INFO)
assert 3 == eval_ctx(ExcelFormula('=iferror(1/0,3)'))
assert 1 == len(caplog.records)
assert "INFO" == caplog.records[0].levelname
assert "1 Div 0" in caplog.records[0].message
assert DIV0 == eval_ctx(ExcelFormula('=1/0'))
assert 2 == len(caplog.records)
assert "WARNING" == caplog.records[1].levelname
message = """return PYTHON_AST_OPERATORS[op](left_op, right_op)
ZeroDivisionError: division by zero
Eval: 1 / 0
Values: 1 Div 0"""
assert message in caplog.records[1].message
def test_bool_funcs(formula, result):
eval_ctx = ExcelFormula.build_eval_context(lambda x: None, None)
assert eval_ctx(ExcelFormula(formula)) == result
def test_empty_cell_logic_op(formula, result):
eval_ctx = ExcelFormula.build_eval_context(lambda x: None, None)
assert eval_ctx(ExcelFormula(formula)) == result
def test_parser_error(formula):
with pytest.raises(FormulaParserError):
ExcelFormula(formula).ast
def test_compiled_python_error():
formula = ExcelFormula('=1 + 2')
formula._python_code = 'this will be a syntax error'
with pytest.raises(FormulaParserError,
match='Failed to compile expression'):
formula.compiled_python
def test_numerics_type_coercion(formula, result):
eval_ctx = ExcelFormula.build_eval_context(lambda x: 3.0, None)
assert eval_ctx(ExcelFormula(formula)) == result
def test_bool_funcs(formula, result):
eval_ctx = ExcelFormula.build_eval_context(lambda x: None, None)
assert eval_ctx(ExcelFormula(formula)) == result
def test_value_error(formula, result):
eval_ctx = ExcelFormula.build_eval_context(
lambda x: VALUE_ERROR, lambda x: [[1, 1], [1, VALUE_ERROR]],
logging.getLogger('pycel_x'))
assert eval_ctx(ExcelFormula(formula)) == result
def test_div_zero(formula):
eval_ctx = ExcelFormula.build_eval_context(
lambda x: DIV0, lambda x: [[1, 1], [1, DIV0]],
logging.getLogger('pycel_x'))
assert eval_ctx(ExcelFormula(formula)) == DIV0
def test_subtotal_errors(formula):
with pytest.raises(ValueError):
ExcelFormula(formula).ast.emit
def test_empty_cell_logic_op(formula, result):
eval_ctx = ExcelFormula.build_eval_context(lambda x: None, None)
assert eval_ctx(ExcelFormula(formula)) == result
def test_subtotal(formula, result):
assert ExcelFormula(formula).ast.emit == result
assert ExcelFormula(formula.replace('tal(', 'tal(1')).ast.emit == result
def test_row(formula, result, cell, empty_eval_context):
assert empty_eval_context(ExcelFormula(formula, cell=cell)) == result
def test_string_concat(formula, result, empty_eval_context):
assert empty_eval_context(ExcelFormula(formula)) == result
def test_numerics_type_coercion(formula, result):
eval_ctx = ExcelFormula.build_eval_context(lambda x: 3.0, None)
assert eval_ctx(ExcelFormula(formula)) == result
def test_compiled_python_error():
formula = ExcelFormula('=1 + 2')
formula._python_code = 'this will be a syntax error'
with pytest.raises(FormulaParserError,
match='Failed to compile expression'):
formula.compiled_python
def test_string_number_compare(formula, result, empty_eval_context):
assert empty_eval_context(ExcelFormula(formula)) == result
def test_build_eval_context(result, formula):
eval_context = ExcelFormula.build_eval_context(lambda x: 1, lambda x: 1)
assert eval_context(ExcelFormula(formula)) == pytest.approx(result)
def test_tokenizer(test_number, formula, rpn, python_code):
assert rpn == stringify_rpn(ExcelFormula(formula).rpn)
def test_unary_ops(expected, formula, empty_eval_context):
assert expected == empty_eval_context(ExcelFormula(formula))
def empty_eval_context():
return ExcelFormula.build_eval_context(
None, None, logging.getLogger('pycel_x'))
def test_build_eval_context(result, formula):
eval_context = ExcelFormula.build_eval_context(lambda x: 1, lambda x: 1)
assert eval_context(ExcelFormula(formula)) == pytest.approx(result)
def eval(self):
if self._eval is None:
self._eval = ExcelFormula.build_eval_context(
self._evaluate, self._evaluate_range,
self.log, plugins=self._plugin_modules)
return self._eval
def empty_eval_context():
return ExcelFormula.build_eval_context(None, None,
logging.getLogger('pycel_x'))
