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_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 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_bit_funcs(formula, result): eval_ctx = ExcelFormula.build_eval_context(lambda x: None, None) assert eval_ctx(ExcelFormula(formula)) == result