def test_lookup_ws(fixture_xls_copy): INDIRECT_FORMULA_ADDRESS = AddressCell('Offset!B53') compiler = ExcelCompiler(fixture_xls_copy('lookup.xlsx')) # do an INDIRECT() before other cells are loaded to verify it can load what it needs result = compiler.validate_calcs([INDIRECT_FORMULA_ADDRESS]) assert result == {} # now load and check everything result = compiler.validate_serialized() assert result == {} # use indirect to an existing range loaded = ExcelCompiler.from_file(compiler.filename) loaded.set_value(INDIRECT_FORMULA_ADDRESS.address_at_offset(1, 0), 'B2:F6') indirect = loaded.evaluate(INDIRECT_FORMULA_ADDRESS) assert indirect == loaded.evaluate('Offset!B2') # use indirect to a non-pre-existing and empty range loaded.set_value(INDIRECT_FORMULA_ADDRESS.address_at_offset(1, 0), 'H1:H2') indirect = loaded.evaluate(INDIRECT_FORMULA_ADDRESS) assert indirect is None # use indirect to a non-pre-existing range to existing cells loaded.set_value(INDIRECT_FORMULA_ADDRESS.address_at_offset(1, 0), 'D3:E3') indirect = loaded.evaluate(INDIRECT_FORMULA_ADDRESS) assert indirect == 8
def offset(reference, row_inc, col_inc, height=None, width=None): # Excel reference: https://support.microsoft.com/en-us/office/ # offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66 """ Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. """ base_addr = AddressRange.create(reference) if height is None: height = base_addr.size.height if width is None: width = base_addr.size.width new_row = base_addr.row + row_inc end_row = new_row + height - 1 new_col = base_addr.col_idx + col_inc end_col = new_col + width - 1 if new_row <= 0 or end_row > MAX_ROW or new_col <= 0 or end_col > MAX_COL: return REF_ERROR top_left = AddressCell((new_col, new_row, new_col, new_row), sheet=base_addr.sheet) if height == width == 1: return top_left else: bottom_right = AddressCell((end_col, end_row, end_col, end_row), sheet=base_addr.sheet) return AddressRange(f'{top_left.coordinate}:{bottom_right.coordinate}', sheet=top_left.sheet)
def test_address_pickle(tmpdir): addrs = [ AddressRange('B1'), AddressRange('B1:C1'), AddressRange('B1:B2'), AddressRange('B1:C2'), AddressRange('sh!B1'), AddressRange('sh!B1:C1'), AddressRange('sh!B1:B2'), AddressRange('sh!B1:C2'), AddressRange('B:C'), AddressRange('2:4'), AddressCell('sh!XFC1048575'), AddressCell('sh!XFD1048576'), AddressCell('sh!A1'), AddressCell('sh!E5'), AddressCell('sh!F6'), ] filename = os.path.join(str(tmpdir), 'test_addrs.pkl') with open(filename, 'wb') as f: pickle.dump(addrs, f) with open(filename, 'rb') as f: new_addrs = pickle.load(f) assert addrs == new_addrs
def test_address_range(): a = AddressRange('a1:b2') b = AddressRange('A1:B2') c = AddressRange(a) assert a == b assert b == c assert b == AddressRange(b) assert b == AddressRange.create(b) assert AddressRange('sh!a1:b2') == AddressRange(a, sheet='sh') assert AddressCell('C13') == AddressCell('R13C3') with pytest.raises(ValueError): AddressRange(AddressRange('sh!a1:b2'), sheet='sheet') a = AddressRange('A:A') assert 'A' == a.start.column assert 'A' == a.end.column assert 0 == a.start.row assert 0 == a.end.row b = AddressRange('1:1') assert '' == b.start.column assert '' == b.end.column assert 1 == b.start.row assert 1 == b.end.row
def test_evaluate_conditional_formatting(cond_format_ws): cells_addrs = [ AddressCell('B2'), AddressCell('Sheet1!B3'), AddressRange('Sheet1!B4:B6'), ] formats = cond_format_ws.eval_conditional_formats(cells_addrs) formats2 = cond_format_ws.eval_conditional_formats( (a for a in cells_addrs)) assert formats == list(formats2) # should match cells_addrs's type assert formats2 == tuple( formats2) # tuple since cells_addrs is a generator assert isinstance(formats[0], tuple) assert len(formats) == 3 assert len(formats[2]) == 3 # read the spreadsheet from yaml cond_format_ws.to_file(file_types=('yml', )) cond_format_ws_yaml = ExcelCompiler.from_file(cond_format_ws.filename + '.yml') cells_addrs[0] = AddressCell('Sheet1!B2') formats3 = cond_format_ws_yaml.eval_conditional_formats(tuple(cells_addrs)) assert formats2 == formats3 # read the spreadsheet from pickle cond_format_ws.to_file(file_types=('pkl', )) cond_format_ws_pkl = ExcelCompiler.from_file(cond_format_ws.filename + '.pkl') cells_addrs[0] = AddressCell('Sheet1!B2') formats4 = cond_format_ws_pkl.eval_conditional_formats(tuple(cells_addrs)) assert formats2 == formats4 formats.append(formats[2][0][0]) formats.append(formats[2][1][0]) formats.append(formats[2][2][0]) del formats[2] color_key = { ('FF006100', 'FFC6EFCE'): 'grn', ('FF9C5700', 'FFFFEB9C'): 'yel', ('FF9C0006', 'FFFFC7CE'): 'red', (None, 'FFFFC7CE'): 'nofont', } color_map = {} for idx, dxf in cond_format_ws.conditional_formats.items(): color_map[idx] = color_key[dxf.font and dxf.font.color.value, dxf.fill.bgColor.value] expected = [ ['red'], ['grn', 'yel', 'red'], ['yel', 'red'], ['nofont'], ['yel', 'red'], ] results = [[color_map[x] for x in y] for y in formats] assert results == expected
def test_address_cell_addr_offset(): cell_addr = AddressCell('sh!C2') assert AddressCell('sh!XFC1048575') == cell_addr.address_at_offset(-3, -4) assert AddressCell('sh!XFD1048576') == cell_addr.address_at_offset(-2, -3) assert AddressCell('sh!A1') == cell_addr.address_at_offset(-1, -2) assert AddressCell('sh!E5') == cell_addr.address_at_offset(3, 2) assert AddressCell('sh!F6') == cell_addr.address_at_offset(4, 3)
def test_address_cell_addr_inc(): cell_addr = AddressCell('sh!C2') assert MAX_COL - 1 == cell_addr.inc_col(-4) assert MAX_COL == cell_addr.inc_col(-3) assert 1 == cell_addr.inc_col(-2) assert 5 == cell_addr.inc_col(2) assert 6 == cell_addr.inc_col(3) assert MAX_ROW - 1 == cell_addr.inc_row(-3) assert MAX_ROW == cell_addr.inc_row(-2) assert 1 == cell_addr.inc_row(-1) assert 5 == cell_addr.inc_row(3) assert 6 == cell_addr.inc_row(4)
def __init__(self, col, row, sheet='', excel=None): self.row = row self.col = col self.col_idx = column_index_from_string(col) self.sheet = sheet self.excel = excel self.address = AddressCell('{}{}'.format(col, row), sheet=sheet)
def conditional_format(self, address): """ Return the conditional formats applicable for this cell """ address = AddressCell(address) all_formats = self.workbook[address.sheet].conditional_formatting formats = (cf for cf in all_formats if address.coordinate in cf) rules = [] for cf in formats: origin = AddressRange(cf.cells.ranges[0].coord).start row_offset = address.row - origin.row col_offset = address.col_idx - origin.col_idx for rule in cf.rules: if rule.formula: trans = Translator('={}'.format(rule.formula[0]), origin.coordinate) formula = trans.translate_formula(row_delta=row_offset, col_delta=col_offset) rules.append( self.CfRule( formula=formula, priority=rule.priority, dxf_id=rule.dxfId, dxf=rule.dxf, stop_if_true=rule.stopIfTrue, )) return sorted(rules, key=lambda x: x.priority)
def __init__(self, col, row, sheet='', excel=None, value=None): self.row = row self.col = col self.col_idx = column_index_from_string(col) self.sheet = sheet self.excel = excel self.address = AddressCell(f'{col}{row}', sheet=sheet) self.value = value
def test_evaluate_empty_intersection(fixture_dir): excel_compiler = ExcelCompiler.from_file( os.path.join(fixture_dir, 'fixture.xlsx.yml')) address = AddressCell('s!A1') excel_compiler.cell_map[str(address)] = _Cell( address, None, '=_R_(str(_REF_("s!A1:A2") & _REF_("s!B1:B2")))', excel_compiler.excel) assert excel_compiler.evaluate(address) == NULL_ERROR
def test_evaluate_exceptions(fixture_dir): excel_compiler = ExcelCompiler.from_file( os.path.join(fixture_dir, 'fixture.xlsx.yml')) address = AddressCell('s!A1') excel_compiler.cell_map[str(address)] = _Cell(address, None, '=__REF__("s!A2")', excel_compiler.excel) address = AddressCell('s!A2') excel_compiler.cell_map[str(address)] = _Cell(address, None, '=$', excel_compiler.excel) with pytest.raises(FormulaParserError): excel_compiler.evaluate(address) result = excel_compiler.validate_calcs(address) assert 'exceptions' in result assert len(result['exceptions']) == 1
def test_offset(crwh, refer, rows, cols, height, width): expected = crwh if isinstance(crwh, tuple): start = AddressCell((crwh[0], crwh[1], crwh[0], crwh[1])) end = AddressCell((crwh[0] + crwh[2] - 1, crwh[1] + crwh[3] - 1, crwh[0] + crwh[2] - 1, crwh[1] + crwh[3] - 1)) expected = AddressRange.create(f'{start.coordinate}:{end.coordinate}') result = offset(refer, rows, cols, height, width) assert result == expected refer_addr = AddressRange.create(refer) if height == refer_addr.size.height: height = None if width == refer_addr.size.width: width = None assert offset(refer_addr, rows, cols, height, width) == expected
def table_name_containing(self, address): """ Return the table name containing the address given """ address = AddressCell(address) if address not in self._table_refs: for t in self.workbook[address.sheet]._tables: if address in AddressRange(t.ref): self._table_refs[address] = t.name.lower() break return self._table_refs.get(address)
def test_has_sheet(): assert AddressRange('Sheet1!a1').has_sheet assert not AddressRange('a1').has_sheet assert AddressRange('Sheet1!a1:b2').has_sheet assert not AddressRange('a1:b2').has_sheet assert AddressCell('sh!A2') == AddressRange(AddressRange('A2'), sheet='sh') with pytest.raises(ValueError, match='Mismatched sheets'): AddressRange(AddressRange('shx!a1'), sheet='sh')
def _formula_cells(self): """Iterate all cells and find cells with formulas""" if self._formula_cells_list is None: self._formula_cells_list = [ AddressCell.create(cell.coordinate, ws.title) for ws in self.excel.workbook for row in ws.iter_rows() for cell in row if isinstance(getattr(cell, 'value', None), str) and cell.value.startswith('=') ] return self._formula_cells_list
def test_address_cell_enum(): assert ('B1', '', 2, 1, 'B1') == AddressCell('B1') assert ('sheet!B1', 'sheet', 2, 1, 'B1') == AddressCell('sheet!B1') assert ('A1', '', 1, 1, 'A1') == AddressCell('R1C1') assert ('sheet!A1', 'sheet', 1, 1, 'A1') == AddressCell('sheet!R1C1') cell = ATestCell('A', 1) assert ('B2', '', 2, 2, 'B2') == AddressCell.create( 'R[1]C[1]', cell=cell) assert ('sheet!B2', 'sheet', 2, 2, 'B2') == AddressCell.create( 'sheet!R[1]C[1]', cell=cell) with pytest.raises(ValueError): AddressCell('B1:C2') with pytest.raises(ValueError): AddressCell('sheet!B1:C2') with pytest.raises(ValueError): AddressCell('xyzzy')
def test_unknown_functions(fixture_dir, msg, formula): excel_compiler = ExcelCompiler.from_file( os.path.join(fixture_dir, 'fixture.xlsx.yml')) address = AddressCell('s!A1') excel_compiler.cell_map[str(address)] = _Cell(address, None, formula, excel_compiler.excel) with pytest.raises(UnknownFunction, match=msg): excel_compiler.evaluate(address) result = excel_compiler.validate_calcs([address]) assert 'not-implemented' in result assert len(result['not-implemented']) == 1
def test_address_cell_enum(ATestCell): assert ('B1', '', 2, 1, 'B1') == AddressCell('B1') assert ('sheet!B1', 'sheet', 2, 1, 'B1') == AddressCell('sheet!B1') assert ('A1', '', 1, 1, 'A1') == AddressCell('R1C1') assert ('sheet!A1', 'sheet', 1, 1, 'A1') == AddressCell('sheet!R1C1') cell = ATestCell('A', 1) assert ('B2', '', 2, 2, 'B2') == AddressCell.create('R[1]C[1]', cell=cell) assert ('sheet!B2', 'sheet', 2, 2, 'B2') == AddressCell.create('sheet!R[1]C[1]', cell=cell) with pytest.raises(ValueError): AddressCell('B1:C2') with pytest.raises(ValueError): AddressCell('sheet!B1:C2') with pytest.raises(ValueError): AddressCell('xyzzy')
def test_needed_addresses(): formula = '=(3600/1000)*E40*(E8/E39)*(E15/E19)*LN(E54/(E54-E48))' needed = sorted(('E40', 'E8', 'E39', 'E15', 'E19', 'E54', 'E48')) excel_formula = ExcelFormula(formula) assert needed == sorted(x.address for x in excel_formula.needed_addresses) assert needed == sorted(x.address for x in excel_formula.needed_addresses) assert () == ExcelFormula('').needed_addresses excel_formula = ExcelFormula('_REF_(_R_("S!A1"))', formula_is_python_code=True) assert excel_formula.needed_addresses == (AddressCell('S!A1'), )
def test_save_restore_numpy_float(basic_ws, tmpdir): addr = AddressCell('Sheet1!A1') cell_value = basic_ws.evaluate(addr) assert not isinstance(cell_value, np.float64) basic_ws.set_value(addr, np.float64(8.0)) cell_value = basic_ws.evaluate(addr) assert isinstance(cell_value, np.float64) assert cell_value == 8.0 tmp_name = os.path.join(tmpdir, 'numpy_test') basic_ws.to_file(tmp_name) excel_compiler = ExcelCompiler.from_file(tmp_name) cell_value = excel_compiler.evaluate(addr) assert not isinstance(cell_value, np.float64) assert cell_value == 8.0
def set_value(self, address, value, set_as_range=False): """ Set the value of one or more cells or ranges :param address: `str`, `AddressRange`, `AddressCell` or a tuple, list or an iterable of these three :param value: value to set. This can be a value or a tuple/list which matches the shapes needed for the given address/addresses :param set_as_range: With a single range address and a list like value, set to true to set the entire rnage to the inserted list. """ if list_like(value) and not set_as_range: value = tuple(flatten(value)) if list_like(address): address = (AddressCell(addr) for addr in flatten(address)) else: address = flatten(AddressRange(address).resolve_range) address = tuple(address) assert len(address) == len(value) for addr, val in zip(address, value): self.set_value(addr, val) return elif address not in self.cell_map: address = AddressRange.create(address).address assert address in self.cell_map, ( f'Address "{address}" not found in the cell map. Evaluate the ' 'address, or an address that references it, to place it in the cell map.' ) if set_as_range and list_like(value) and not (value and list_like(value[0])): value = (value, ) cell_or_range = self.cell_map[address] if cell_or_range.value != value: # pragma: no branch # need to be able to 'set' an empty cell, set to not None cell_or_range.value = value # reset the node + its dependencies if not self.cycles: self._reset(cell_or_range) # set the value cell_or_range.value = value
def test_validate_calcs_all_cells(basic_ws): formula_cells = basic_ws.formula_cells('Sheet1') expected = { AddressCell('Sheet1!B2'), AddressCell('Sheet1!C2'), AddressCell('Sheet1!B3'), AddressCell('Sheet1!C3'), AddressCell('Sheet1!B4'), AddressCell('Sheet1!C4') } assert expected == set(formula_cells) assert {} == basic_ws.validate_calcs()
def formula_cells(self, sheet=None): """Iterate all cells and find cells with formulas""" if sheet is None: return list( it.chain.from_iterable( self.formula_cells(sheet.title) for sheet in self.excel.workbook)) if sheet not in self._formula_cells_dict: if sheet in self.excel.workbook: self._formula_cells_dict[sheet] = tuple( AddressCell.create(cell.coordinate, sheet) for row in self.excel.workbook[sheet].iter_rows() for cell in row if isinstance(getattr(cell, 'value', None), str) and cell.value.startswith('=')) else: self._formula_cells_dict[sheet] = tuple() return self._formula_cells_dict[sheet]
def formula_cells(self, sheet=None): """Iterate all cells and find cells with formulas""" if sheet is None: return list(it.chain.from_iterable( self.formula_cells(sheet.title) for sheet in self.excel.workbook)) if sheet not in self._formula_cells_dict: if sheet in self.excel.workbook: self._formula_cells_dict[sheet] = tuple( AddressCell.create(cell.coordinate, sheet) for row in self.excel.workbook[sheet].iter_rows() for cell in row if isinstance(getattr(cell, 'value', None), str) and cell.value.startswith('=') ) else: self._formula_cells_dict[sheet] = tuple() return self._formula_cells_dict[sheet]
def _to_text(self, filename=None, is_json=False): """Serialize to a json/yaml file""" extra_data = {} if self.extra_data is None else self.extra_data def cell_value(a_cell): if a_cell.formula and a_cell.formula.python_code: return '=' + a_cell.formula.python_code else: return a_cell.value extra_data.update( dict( excel_hash=self._excel_file_md5_digest, cell_map=dict( sorted(((addr, cell_value(cell)) for addr, cell in self.cell_map.items() if ':' not in addr), key=lambda x: AddressCell(x[0]).sort_key)), )) if not filename: filename = self.filename + ('.json' if is_json else '.yml') # hash the current file to see if this function makes any changes existing_hash = (self._compute_file_md5_digest(filename) if os.path.exists(filename) else None) if not is_json: with open(filename, 'w') as f: ymlo = YAML() ymlo.width = 120 ymlo.dump(extra_data, f) else: with open(filename, 'w') as f: json.dump(extra_data, f, indent=4) del extra_data['cell_map'] # hash the newfile, return True if it changed, this is only reliable # on pythons which have ordered dict (CPython 3.6 & python 3.7+) return (existing_hash is None or existing_hash != self._compute_file_md5_digest(filename))
def set_value(self, address, value): """ Set the value of one or more cells or ranges :param address: `str`, `AddressRange`, `AddressCell` or a tuple, list or an iterable of these three :param value: value to set. This can be a value or a tuple/list which matches the shapes needed for the given address/addresses """ if list_like(value): value = tuple(flatten(value)) if list_like(address): address = (AddressCell(addr) for addr in flatten(address)) else: address = flatten(AddressRange(address).resolve_range) address = tuple(address) assert len(address) == len(value) for addr, val in zip(address, value): self.set_value(addr, val) return elif address not in self.cell_map: address = AddressRange.create(address).address assert address in self.cell_map cell_or_range = self.cell_map[address] if cell_or_range.value != value: # pragma: no branch # need to be able to 'set' an empty cell if cell_or_range.value is None: cell_or_range.value = value # reset the node + its dependencies self._reset(cell_or_range) # set the value cell_or_range.value = value
def __init__(self, excel): self.excel = excel self.address = AddressCell('E5')
assert ((0, 1), (0, 2)) == find_corresponding_index((list('ABB'), ), 'B') assert ((0, 1), (0, 2)) == find_corresponding_index((list('ABB'), ), '<>A') assert () == find_corresponding_index((list('ABB'), ), 'D') with pytest.raises(TypeError): find_corresponding_index('ABB', '<B') with pytest.raises(ValueError): find_corresponding_index((list('ABB'), ), None) @pytest.mark.parametrize( 'value, expected', ( ('xyzzy', False), (AddressRange('A1:B2'), False), (AddressCell('A1'), False), ([1, 2], True), ((1, 2), True), ({1: 2, 3: 4}, True), ((a for a in range(2)), True), ) ) def test_list_like(value, expected): assert list_like(value) == expected if expected: assert_list_like(value) else: with pytest.raises(TypeError, match='Must be a list like: '): assert_list_like(value)
def test_quoted_address(sheet_name): addr = AddressCell('A2', sheet=sheet_name) assert addr.quoted_address == '{}!A2'.format(quote_sheetname(sheet_name))
def __init__(self, table, address): self.excel = Excel(table) self.address = AddressCell(address)
def test_address_range_contains(a_range, address, expected): a_range = AddressRange(a_range) assert expected == (address in a_range) address = AddressCell(address) assert expected == (address in a_range)