def _gen_graph(self, seed, recursed=False): """Given a starting point (e.g., A6, or A3:B7) on a particular sheet, generate a Spreadsheet instance that captures the logic and control flow of the equations. """ if not is_address(seed): if isinstance(seed, str): seed = AddressRange(seed) elif isinstance(seed, collections.abc.Iterable): for s in seed: self._gen_graph(s, recursed=True) self._process_gen_graph() return else: raise ValueError(f'Unknown seed: {seed}') # get/set the current sheet if not seed.has_sheet: seed = AddressRange(seed, sheet=self.excel.get_active_sheet_name()) if '[' in seed.sheet: raise NotImplementedError('Linked SheetNames') if seed.address in self.cell_map: # already did this cell/range return # process the seed self._make_cells(seed) if not recursed: # if not entered to process one cell / cellrange process other work self._process_gen_graph()
def get_range(self, address): if not is_address(address): address = AddressRange(address) if address.has_sheet: sheet = self.workbook[address.sheet] sheet_dataonly = self.workbook_dataonly[address.sheet] else: sheet = self.workbook.active sheet_dataonly = self.workbook_dataonly.active with mock.patch('openpyxl.worksheet._reader.from_excel', self.from_excel): # work around type coercion to datetime that causes some issues if address.is_unbounded_range: # bound the address range to the data in the spreadsheet address = address & AddressRange( (1, 1, *self.max_col_row(sheet.title)), sheet=sheet.title) cells = sheet[address.coordinate] cells_dataonly = sheet_dataonly[address.coordinate] if isinstance(cells, (Cell, MergedCell)): return _OpxCell(cells, cells_dataonly, address) else: return _OpxRange(cells, cells_dataonly, address)
def test_indirect(address, expected): assert indirect(address) == expected if is_address(expected): with_sheet = expected.create(expected, sheet='S') assert indirect(address, None, 'S') == with_sheet address = f'S!{address}' assert indirect(address) == with_sheet assert indirect(address, None, 'S') == with_sheet
def get_formula_or_value(self, address): if not is_address(address): address = AddressRange(address) result = self.get_range(address) if isinstance(address, AddressCell): return result.formula or result.values else: return tuple( tuple(self.get_formula_or_value(a) for a in row) for row in result.resolve_range)
def get_formula_from_range(self, address): if not is_address(address): address = AddressRange(address) result = self.get_range(address) if isinstance(address, AddressCell): return result.formula if result.formula.startswith("=") else None else: return tuple( tuple(self.get_formula_from_range(a) for a in row) for row in result.resolve_range)
def _evaluate(self, address): """Evaluate a single cell""" if address not in self.cell_map: # INDIRECT() and OFFSET() can produce addresses we don't already have loaded self._gen_graph(address) cell = self.cell_map[address] # calculate the cell value for formulas and ranges if cell.needs_calc: if isinstance(cell, _CellRange) or cell.address.is_unbounded_range: self._evaluate_range(cell.address.address) elif cell.python_code: self.log.debug(f"Evaluating: {address}, {cell.python_code}") value = self.eval(cell) if is_address(value): # eval produced an address (aka: a reference) if value.is_range: # complain as we are not going to do any spilling self.log.warning( f"Cell {address} evaluated to '{value}'," f" truncating to '{value.start}'") value = value.start else: self.log.info( f"Cell {address} evaluated to address '{value}'") # fetch the value for this cell, if it exists ref_addr = value.address if ref_addr not in self.cell_map and getattr( self, 'excel', None): # INDIRECT() can produce addresses we don't already have loaded self._gen_graph(ref_addr) value = self.cell_map[ref_addr].value else: self.log.info( f"Cell {cell.address} evaluated to '{value}' ({type(value).__name__})" ) cell.value = (value[0][0] if list_like(value[0]) else value[0]) if list_like(value) else value return cell.value
def test_is_address(data, result): assert is_address(data) == result
def index(array, row_num, col_num=None): # Excel reference: https://support.microsoft.com/en-us/office/ # index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd if not list_like(array): if array in ERROR_CODES: return array else: return VALUE_ERROR if not list_like(array[0]): return VALUE_ERROR if is_address(array[0][0]): assert len({a for a in flatten(array)}) == 1 _C_ = index.excel_func_meta['name_space']['_C_'] ref_addr = array[0][0].address_at_offset else: ref_addr = None def array_data(row, col): if ref_addr: return _C_(ref_addr(row, col).address) else: return array[row][col] try: # rectangular array if row_num and col_num: if row_num < 0 or col_num < 0: return VALUE_ERROR else: return array_data(row_num - 1, col_num - 1) elif row_num: if row_num < 0: return VALUE_ERROR elif len(array[0]) == 1: return array_data(row_num - 1, 0) elif len(array) == 1: return array_data(0, row_num - 1) elif isinstance(array, np.ndarray): return array[row_num - 1, :] else: return (tuple( array_data(row_num - 1, col) for col in range(len(array[0]))), ) elif col_num: if col_num < 0: return VALUE_ERROR elif len(array) == 1: return array_data(0, col_num - 1) elif len(array[0]) == 1: return array_data(col_num - 1, 0) elif isinstance(array, np.ndarray): result = array[:, col_num - 1] result.shape = result.shape + (1, ) return result else: return tuple((array_data(row, col_num - 1), ) for row in range(len(array))) except IndexError: return REF_ERROR else: return array
def test_is_address(data, expected): assert is_address(data) == expected