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_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 _evaluate(self, address):
"""Evaluate a single cell"""
cell = self.cell_map[address]
# calculate the cell value for formulas and ranges
if cell.value is None:
if isinstance(cell, _CellRange):
self._evaluate_range(cell.address.address)
elif cell.python_code:
self.log.debug("Evaluating: {}, {}".format(
cell.address, cell.python_code))
if self.eval is None:
self.eval = ExcelFormula.build_eval_context(
self._evaluate, self._evaluate_range, self.log)
value = self.eval(cell.formula)
self.log.info("Cell %s evaluated to '%s' (%s)" %
(cell.address, value, type(value).__name__))
cell.value = VALUE_ERROR if list_like(value) else value
if isinstance(cell.value, AddressRange):
# If the cell returns a reference, then dereference
return self._evaluate(str(cell.value))
return cell.value
def test_numerics_type_coercion():
eval_ctx = ExcelFormula.build_eval_context(lambda x: 3.0, None)
assert 7 == eval_ctx(ExcelFormula('=1+2+"4"'))
assert 7 == eval_ctx(ExcelFormula('=sum(1, 2, "4")'))
assert '3A' == eval_ctx(ExcelFormula('=3&"A"'))
assert '3A' == eval_ctx(ExcelFormula('=3.0&"A"'))
assert '3A' == eval_ctx(ExcelFormula('=A1&"A"'))
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 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_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_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_string_compare():
eval_ctx = ExcelFormula.build_eval_context(None, None)
assert not eval_ctx(ExcelFormula('=1="a"'))
assert not eval_ctx(ExcelFormula('=1=2'))
assert not eval_ctx(ExcelFormula('="a"="b"'))
assert eval_ctx(ExcelFormula('=1=1'))
assert eval_ctx(ExcelFormula('="A"="a"'))
assert eval_ctx(ExcelFormula('="a"="A"'))
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_string_concat():
eval_ctx = ExcelFormula.build_eval_context(None, None)
assert '6A' == eval_ctx(ExcelFormula('=2*3&"A"'))
assert '1a' == eval_ctx(ExcelFormula('=1&"a"'))
assert '12' == eval_ctx(ExcelFormula('="1"&2'))
assert 'ab' == eval_ctx(ExcelFormula('="a"&"b"'))
assert '11' == eval_ctx(ExcelFormula('=1&1'))
assert 'Aa' == eval_ctx(ExcelFormula('="A"&"a"'))
assert 'aA' == eval_ctx(ExcelFormula('="a"&"A"'))
def test_build_eval_context():
eval_context = ExcelFormula.build_eval_context(lambda x: 1, lambda x: 1)
assert 42 == eval_context(ExcelFormula('=2 * 21'))
assert 44 == eval_context(ExcelFormula('=2 * 21 + A1 + a1:a2'))
assert 1 == eval_context(ExcelFormula('=1 + sin(0)'))
assert pytest.approx(4.1415926) == eval_context(ExcelFormula('=1 + PI()'))
with pytest.raises(FormulaEvalError,
match="name 'unknown_function' is not defined"):
eval_context(ExcelFormula('=unknown_function(0)'))
def test_div_zero():
eval_ctx = ExcelFormula.build_eval_context(lambda x: DIV0,
lambda x: [[1, 1], [1, DIV0]],
logging.getLogger('pycel_x'))
assert DIV0 == eval_ctx(ExcelFormula('=sum(A1)'))
assert DIV0 == eval_ctx(ExcelFormula('=sum(A1:B2)'))
assert DIV0 == eval_ctx(ExcelFormula('=a1=1'))
assert DIV0 == eval_ctx(ExcelFormula('=a1+"l"'))
assert DIV0 == eval_ctx(ExcelFormula('=1 - (1 / 0)'))
def test_value_error():
eval_ctx = ExcelFormula.build_eval_context(
lambda x: VALUE_ERROR, lambda x: [[1, 1], [1, VALUE_ERROR]],
logging.getLogger('pycel_x'))
assert VALUE_ERROR == eval_ctx(ExcelFormula('=sum(A1)'))
assert VALUE_ERROR == eval_ctx(ExcelFormula('=sum(A1:B2)'))
assert VALUE_ERROR == eval_ctx(ExcelFormula('=a1=1'))
assert VALUE_ERROR == eval_ctx(ExcelFormula('=a1+"l"'))
assert 3 == eval_ctx(ExcelFormula('=iferror(1+"A",3)'))
assert VALUE_ERROR == eval_ctx(ExcelFormula('=1+"A"'))
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_unknown_function(msg, formula):
eval_ctx = ExcelFormula.build_eval_context(lambda x: None, lambda x: None,
logging.getLogger('pycel_x'))
compiled = ExcelFormula(formula)
with pytest.raises(UnknownFunction, match=msg):
eval_ctx(compiled)
# second time is needed to test cached msg
with pytest.raises(UnknownFunction, match=msg):
eval_ctx(compiled)
def test_row():
eval_ctx = ExcelFormula.build_eval_context(None, None)
assert 45 == eval_ctx(ExcelFormula('=ROW(L45)'))
assert 1 == eval_ctx(ExcelFormula('=ROW(B:E)'))
assert 4 == eval_ctx(ExcelFormula('=ROW(4:7)'))
assert 1 == eval_ctx(ExcelFormula('=ROW(D1:E1)'))
assert 1 == eval_ctx(ExcelFormula('=ROW(D1:D2)'))
assert 1 == eval_ctx(ExcelFormula('=ROW(D1:E2)'))
cell = ATestCell('B', 3)
assert 3 == eval_ctx(ExcelFormula('=ROW()', cell=cell))
assert 6 == eval_ctx(ExcelFormula('=ROW(B6:D7 C7:E7)'))
def test_column():
eval_ctx = ExcelFormula.build_eval_context(None, None)
assert 12 == eval_ctx(ExcelFormula('=COLUMN(L45)'))
assert 2 == eval_ctx(ExcelFormula('=COLUMN(B:E)'))
assert 1 == eval_ctx(ExcelFormula('=COLUMN(4:7)'))
assert 4 == eval_ctx(ExcelFormula('=COLUMN(D1:E1)'))
assert 4 == eval_ctx(ExcelFormula('=COLUMN(D1:D2)'))
assert 4 == eval_ctx(ExcelFormula('=COLUMN(D1:E2)'))
cell = ATestCell('B', 3)
assert 2 == eval_ctx(ExcelFormula('=COLUMN()', cell=cell))
assert 2 == eval_ctx(ExcelFormula('=COLUMN(B6:D7 C7:E7)'))
def test_lineno_on_error_reporting():
eval_ctx = ExcelFormula.build_eval_context(None, None)
excel_formula = ExcelFormula('')
excel_formula._python_code = 'X'
excel_formula.lineno = 6
excel_formula.filename = 'a_file'
with pytest.raises(FormulaEvalError, match='File "a_file", line 6'):
eval_ctx(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(FormulaEvalError, match=', line 60,'):
eval_ctx(excel_formula)
def _evaluate(self, address):
cell = self.cell_map[address]
# calculate the cell value for formulas and ranges
if cell.value is None:
if isinstance(cell, _CellRange):
self._evaluate_range(cell.address.address)
elif cell.python_code:
self.log.debug("Evaluating: {}, {}".format(
cell.address, cell.python_code))
if self.eval is None:
self.eval = ExcelFormula.build_eval_context(
self._evaluate, self._evaluate_range, self.log)
value = self.eval(cell.formula)
self.log.info("Cell %s evaluated to '%s' (%s)" %
(cell.address, value, type(value).__name__))
cell.value = value
return cell.value
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_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 eval(self):
if self._eval is None:
eval_ctx = ExcelFormula.build_eval_context(
self._evaluate, self._evaluate_range,
self.log, plugins=self._plugin_modules)
if self.cycles:
def _eval(cell, cse_array_address=None):
cell.start_calcs()
return eval_ctx(
cell.formula, cse_array_address=cse_array_address)
else:
def _eval(cell, cse_array_address=None):
return eval_ctx(
cell.formula, cse_array_address=cse_array_address)
self._eval = _eval
return self._eval
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_if_with_numpy(formula, result):
eval_ctx = ExcelFormula.build_eval_context(lambda x: np.int_(3.0), None)
assert eval_ctx(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_empty_cell_logic_op(formula, result):
eval_ctx = ExcelFormula.build_eval_context(lambda x: None, None)
assert eval_ctx(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 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'))
def test_bool_funcs(formula, result):
eval_ctx = ExcelFormula.build_eval_context(lambda x: None, None)
assert eval_ctx(ExcelFormula(formula)) == result
def empty_eval_context():
return ExcelFormula.build_eval_context(None, None,
logging.getLogger('pycel_x'))
def test_string_number_compare():
eval_ctx = ExcelFormula.build_eval_context(None, None)
assert 1 == eval_ctx(ExcelFormula('=(1=1.0)+("1"=1)+(1="1")'))
assert 1 == eval_ctx(ExcelFormula('=("1"="1") + ("x"=1)'))
assert 'b' == eval_ctx(ExcelFormula('=if("x"<>"x", "a", "b")'))
def test_empty_cell_logic_op():
eval_ctx = ExcelFormula.build_eval_context(lambda x: None, None)
assert 1 == eval_ctx(ExcelFormula('=(A1=0) + (A1=1)'))
assert 3 == eval_ctx(ExcelFormula('=(A1<0)+(A1<=0)+(A1=0)+(A1>=0)+(A1>0)'))
def test_unary_ops(expected, formula):
eval_ctx = ExcelFormula.build_eval_context(None, None)
assert expected == eval_ctx(ExcelFormula(formula))
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_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_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_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_bit_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_empty_cell_logic_op(formula, result):
eval_ctx = ExcelFormula.build_eval_context(lambda x: None, None)
assert eval_ctx(ExcelFormula(formula)) == result
