def hlookup(lookup_value, table_array, row_index_num, range_lookup=True): """ Horizontal Lookup :param lookup_value: value to match (value or cell reference) :param table_array: range of cells being searched. :param row_index_num: column number to return :param range_lookup: True, assumes sorted, finds nearest. False: find exact :return: #N/A if not found else value """ # Excel reference: https://support.office.com/en-us/article/ # hlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f if not list_like(table_array): return NA_ERROR if list_like(lookup_value) or list_like(row_index_num): raise NotImplementedError('Array Formulas not implemented') if row_index_num <= 0: return '#VALUE!' if row_index_num > len(table_array[0]): return '#REF!' result_idx = match(lookup_value, table_array[0], match_type=bool(range_lookup)) if isinstance(result_idx, int): return table_array[row_index_num - 1][result_idx - 1] else: # error string return result_idx
def vlookup(lookup_value, table_array, col_index_num, range_lookup=True): """ Vertical Lookup :param lookup_value: value to match (value or cell reference) :param table_array: range of cells being searched. :param col_index_num: column number to return :param range_lookup: True, assumes sorted, finds nearest. False: find exact :return: #N/A if not found else value """ # Excel reference: https://support.office.com/en-us/article/ # VLOOKUP-function-0BBC8083-26FE-4963-8AB8-93A18AD188A1 if not list_like(table_array): return NA_ERROR if list_like(lookup_value) or list_like(col_index_num): raise NotImplementedError('Array Formulas not implemented') if col_index_num <= 0: return '#VALUE!' if col_index_num > len(table_array[0]): return '#REF!' result_idx = match(lookup_value, [row[0] for row in table_array], match_type=bool(range_lookup)) if isinstance(result_idx, int): return table_array[result_idx - 1][col_index_num - 1] else: # error string return result_idx
def lookup(lookup_value, lookup_array, result_range=None): """ There are two ways to use LOOKUP: Vector form and Array form Vector form: lookup_array is list like (ie: n x 1) Array form: lookup_array is rectangular (ie: n x m) First row or column is the lookup vector. Last row or column is the result vector The longer dimension is the search dimension :param lookup_value: value to match (value or cell reference) :param lookup_array: range of cells being searched. :param result_range: (optional vector form) values are returned from here :return: #N/A if not found else value """ # Excel reference: https://support.microsoft.com/en-us/office/ # lookup-function-446d94af-663b-451d-8251-369d5e3864cb if not list_like(lookup_array): return NA_ERROR height = len(lookup_array) width = len(lookup_array[0]) # match across the largest dimension if width <= height: match_idx = _match(lookup_value, tuple(i[0] for i in lookup_array)) result = tuple(i[-1] for i in lookup_array) else: match_idx = _match(lookup_value, lookup_array[0]) result = lookup_array[-1] if result_range is not None: # if not a vector return NA if not list_like(result_range): return NA_ERROR rr_height = len(result_range) rr_width = len(result_range[0]) if rr_width < rr_height: if rr_width != 1: return NA_ERROR result = tuple(i[0] for i in result_range) else: if rr_height != 1: return NA_ERROR result = result_range[0] if isinstance(match_idx, int): return result[match_idx - 1] else: # error string return match_idx
def test_evaluate_entire_row_column(excel_compiler): value = excel_compiler.evaluate(AddressRange('Sheet1!A:A')) expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:A18')) assert value == expected assert len(value) == 18 assert not list_like(value[0]) value = excel_compiler.evaluate(AddressRange('Sheet1!1:1')) expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:D1')) assert value == expected assert len(value) == 4 assert not list_like(value[0]) value = excel_compiler.evaluate(AddressRange('Sheet1!A:B')) expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:B18')) assert value == expected assert len(value) == 18 assert len(value[0]) == 2 value = excel_compiler.evaluate(AddressRange('Sheet1!1:2')) expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:D2')) assert value == expected assert len(value) == 2 assert len(value[0]) == 4 # now from the text based file excel_compiler._to_text() text_excel_compiler = ExcelCompiler._from_text(excel_compiler.filename) value = text_excel_compiler.evaluate(AddressRange('Sheet1!A:A')) expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:A18')) assert value == expected assert len(value) == 18 assert not list_like(value[0]) value = text_excel_compiler.evaluate(AddressRange('Sheet1!1:1')) expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:D1')) assert value == expected assert len(value) == 4 assert not list_like(value[0]) value = text_excel_compiler.evaluate(AddressRange('Sheet1!A:B')) expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:B18')) assert len(value) == 18 assert len(value[0]) == 2 assert value == expected value = text_excel_compiler.evaluate(AddressRange('Sheet1!1:2')) expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:D2')) assert value == expected assert len(value) == 2 assert len(value[0]) == 4
def test_evaluate_entire_row_column(excel_compiler): value = excel_compiler.evaluate(AddressRange('Sheet1!A:A')) expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:A18')) assert value == expected assert len(value) == 18 assert not list_like(value[0]) value = excel_compiler.evaluate(AddressRange('Sheet1!1:1')) expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:D1')) assert value == expected assert len(value) == 4 assert not list_like(value[0]) value = excel_compiler.evaluate(AddressRange('Sheet1!A:B')) expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:B18')) assert value == expected assert len(value) == 18 assert len(value[0]) == 2 value = excel_compiler.evaluate(AddressRange('Sheet1!1:2')) expected = excel_compiler.evaluate(AddressRange('Sheet1!A1:D2')) assert value == expected assert len(value) == 2 assert len(value[0]) == 4 # now from the text based file excel_compiler._to_text() text_excel_compiler = ExcelCompiler._from_text(excel_compiler.filename) value = text_excel_compiler.evaluate(AddressRange('Sheet1!A:A')) expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:A18')) assert value == expected assert len(value) == 18 assert not list_like(value[0]) value = text_excel_compiler.evaluate(AddressRange('Sheet1!1:1')) expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:D1')) assert value == expected assert len(value) == 4 assert not list_like(value[0]) value = text_excel_compiler.evaluate(AddressRange('Sheet1!A:B')) expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:B18')) assert len(value) == 18 assert len(value[0]) == 2 assert value == expected value = text_excel_compiler.evaluate(AddressRange('Sheet1!1:2')) expected = text_excel_compiler.evaluate(AddressRange('Sheet1!A1:D2')) assert value == expected assert len(value) == 2 assert len(value[0]) == 4
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 vlookup(lookup_value, table_array, col_index_num, range_lookup=True): """ Vertical Lookup :param lookup_value: value to match (value or cell reference) :param table_array: range of cells being searched. :param col_index_num: column number to return :param range_lookup: True, assumes sorted, finds nearest. False: find exact :return: #N/A if not found else value """ # Excel reference: https://support.office.com/en-us/article/ # VLOOKUP-function-0BBC8083-26FE-4963-8AB8-93A18AD188A1 if not list_like(table_array): return NA_ERROR if col_index_num <= 0: return '#VALUE!' if col_index_num > len(table_array[0]): return REF_ERROR result_idx = _match( lookup_value, [row[0] for row in table_array], match_type=bool(range_lookup) ) if isinstance(result_idx, int): return table_array[result_idx - 1][col_index_num - 1] else: # error string return result_idx
def hlookup(lookup_value, table_array, row_index_num, range_lookup=True): """ Horizontal Lookup :param lookup_value: value to match (value or cell reference) :param table_array: range of cells being searched. :param row_index_num: column number to return :param range_lookup: True, assumes sorted, finds nearest. False: find exact :return: #N/A if not found else value """ # Excel reference: https://support.office.com/en-us/article/ # hlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f if not list_like(table_array): return NA_ERROR if row_index_num <= 0: return VALUE_ERROR if row_index_num > len(table_array[0]): return REF_ERROR result_idx = _match( lookup_value, table_array[0], match_type=bool(range_lookup)) if isinstance(result_idx, int): return table_array[row_index_num - 1][result_idx - 1] else: # error string return result_idx
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 _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: if (self._max_iterations is not None and cell.iterations > self._max_iterations): cell.value = 0 else: cell.iterations += 1 self.log.debug( "Evaluating: {}, {}".format(cell.address, cell.python_code)) 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 _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 evaluate(self, address): """ evaluate a cell or cells in the spreadsheet :param address: str, AddressRange, AddressCell or a tuple or list or iterable of these three :return: evaluated value/values """ if str(address) not in self.cell_map: if list_like(address): if not isinstance(address, (tuple, list)): address = tuple(address) # process a tuple or list of addresses return type(address)(self.evaluate(c) for c in address) address = AddressRange.create(address) # get the sheet if not specified if not address.has_sheet: address = AddressRange( address, sheet=self.excel.get_active_sheet_name()) if address.address not in self.cell_map: self._gen_graph(address.address) return self._evaluate(str(address))
def evaluate(self, address): """ evaluate a cell or cells in the spreadsheet :param address: str, AddressRange, AddressCell or a tuple or list or iterable of these three :return: evaluated value/values """ if str(address) not in self.cell_map: if list_like(address): if not isinstance(address, (tuple, list)): address = tuple(address) # process a tuple or list of addresses return type(address)(self.evaluate(c) for c in address) address = AddressRange.create(address) # get the sheet if not specified if not address.has_sheet: address = AddressRange( address, sheet=self.excel.get_active_sheet_name()) if address.address not in self.cell_map: self._gen_graph(address.address) result = self._evaluate(str(address)) if isinstance(result, tuple): # trim excess dimensions if len(result[0]) == 1: result = tuple(row[0] for row in result) if len(result) == 1: result = result[0] return result
def countif(rng, criteria): # Excel reference: https://support.office.com/en-us/article/ # COUNTIF-function-e0de10c6-f885-4e71-abb4-1f464816df34 if not list_like(rng): rng = ((rng, ), ) valid = find_corresponding_index(rng, criteria) return len(valid)
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 sumifs(sum_range, *args): # Excel reference: https://support.office.com/en-us/article/ # SUMIFS-function-C9E748F5-7EA7-455D-9406-611CEBCE642B if not list_like(sum_range): sum_range = ((sum_range, ), ) return sum( _numerics((sum_range[r][c] for r, c in handle_ifs(args, sum_range)), keep_bools=True))
def index(array, row_num, col_num=None): # Excel reference: https://support.office.com/en-us/article/ # 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 try: # rectangular array if row_num and col_num: return array[row_num - 1][col_num - 1] elif row_num: if len(array[0]) == 1: return array[row_num - 1][0] elif len(array) == 1: return array[0][row_num - 1] elif isinstance(array, np.ndarray): return array[row_num - 1, :] else: return (tuple(array[row_num - 1]), ) elif col_num: if len(array) == 1: return array[0][col_num - 1] elif len(array[0]) == 1: return array[col_num - 1][0] elif isinstance(array, np.ndarray): result = array[:, col_num - 1] result.shape = result.shape + (1, ) return result else: return tuple((r[col_num - 1], ) for r in array) except IndexError: pass return NA_ERROR
def lookup(lookup_value, lookup_array, result_range=None): """ There are two ways to use LOOKUP: Vector form and Array form Vector form: lookup_array is list like (ie: n x 1) Array form: lookup_array is rectangular (ie: n x m) First row or column is the lookup vector. Last row or column is the result vector The longer dimension is the search dimension :param lookup_value: value to match (value or cell reference) :param lookup_array: range of cells being searched. :param result_range: (optional vector form) values are returned from here :return: #N/A if not found else value """ if not list_like(lookup_array): return NA_ERROR height = len(lookup_array) if list_like(lookup_array[0]): # rectangular array assert result_range is None width = len(lookup_array[0]) # match across the largest dimension if width <= height: match_idx = match(lookup_value, tuple(i[0] for i in lookup_array)) result_range = tuple(i[-1] for i in lookup_array) else: match_idx = match(lookup_value, lookup_array[0]) result_range = lookup_array[-1] else: match_idx = match(lookup_value, lookup_array) result_range = result_range or lookup_array if isinstance(match_idx, int): return result_range[match_idx - 1] else: # error string return match_idx
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 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 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 _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 averageifs(average_range, *args): # Excel reference: https://support.office.com/en-us/article/ # AVERAGEIFS-function-48910C45-1FC0-4389-A028-F7C5C3001690 if not list_like(average_range): average_range = ((average_range, ), ) coords = handle_ifs(args, average_range) data = _numerics((average_range[r][c] for r, c in coords), keep_bools=True) if len(data) == 0: return DIV0 return sum(data) / len(data)
def eval_conditional_formats(self, address): """Evaluate the conditional format (formulas) for a cell or cells returns the conditional format id which is the key for the dict: ExcelCompiler.conditional_formats NOTE: conditional_formats are not saved in the persistent formats. If needed they can be hand serialized into "extra_data" :param address: str, AddressRange, AddressCell or a tuple or list or iterable of these three :return: evaluated objects ids """ if list_like(address): if not isinstance(address, (tuple, list)): address = tuple(address) # process a tuple or list of addresses return type(address)(self.eval_conditional_formats(c) for c in address) address = AddressRange.create(address) # get the sheet if not specified if not address.has_sheet: address = AddressRange(address, sheet=self.excel.get_active_sheet_name()) if address.is_range: return tuple( tuple(self.eval_conditional_formats(addr) for addr in row) for row in address.rows) cf_addr = str(address).replace('!', '.cf!') if cf_addr not in self.cell_map: phony_cell = _Cell(address) formats = self.excel.conditional_format(address) format_strs = [] for f in formats: excel_formula = ExcelFormula(f.formula, cell=phony_cell) python_code = excel_formula.python_code format_strs.append( f'({python_code}, {f.dxf_id}, {int(bool(f.stop_if_true))})' ) self.conditional_formats[f.dxf_id] = f.dxf python_code = f"=conditional_format_ids({', '.join(format_strs)})" a_cell = _Cell(address, formula=python_code) self.cell_map[cf_addr] = a_cell self._gen_graph(a_cell.formula.needed_addresses) return self.eval(self.cell_map[cf_addr])
def minifs(min_range, *args): # Excel reference: https://support.office.com/en-us/article/ # minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599 if not list_like(min_range): min_range = ((min_range, ), ) try: return min( _numerics( (min_range[r][c] for r, c in handle_ifs(args, min_range)), keep_bools=True)) except ValueError: return 0
def maxifs(max_range, *args): # Excel reference: https://support.office.com/en-us/article/ # maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883 if not list_like(max_range): max_range = ((max_range, ), ) try: return max( _numerics( (max_range[r][c] for r, c in handle_ifs(args, max_range)), keep_bools=True)) except ValueError: return 0
def index(array, row_num, col_num=None): # Excel reference: https://support.office.com/en-us/article/ # index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd if not list_like(array) or not list_like(array[0]): return VALUE_ERROR try: # rectangular array if row_num and col_num: return array[row_num - 1][col_num - 1] elif row_num: if len(array[0]) == 1: return array[row_num - 1][0] elif len(array) == 1: return array[0][row_num - 1] elif isinstance(array, np.ndarray): return array[row_num - 1, :] else: return (tuple(array[row_num - 1]),) elif col_num: if len(array) == 1: return array[0][col_num - 1] elif len(array[0]) == 1: return array[col_num - 1][0] elif isinstance(array, np.ndarray): result = array[:, col_num - 1] result.shape = result.shape + (1,) return result else: return tuple((r[col_num - 1], ) for r in array) except IndexError: pass return NA_ERROR
def sumifs(sum_range, *args): # Excel reference: https://support.microsoft.com/en-us/office/ # SUMIFS-function-C9E748F5-7EA7-455D-9406-611CEBCE642B if not list_like(sum_range): sum_range = ((sum_range, ), ) coords = handle_ifs(args, sum_range) # A returned string is an error code if isinstance(coords, str): return coords return sum(_numerics((sum_range[r][c] for r, c in coords), keep_bools=True))
def trend(Y, X=None, new_X=None, const=None): # Excel reference: https://support.microsoft.com/en-us/office/ # trend-function-e2f135f0-8827-4096-9873-9a7cf7b51ef1 kwargs = {} if const is not None: kwargs['const'] = const try: coefs, full_rank = linest_helper(Y, X, **kwargs) except AssertionError: return REF_ERROR except ValueError: return VALUE_ERROR if new_X is None: if X is not None: new_X = np.array(X) else: length = max(len(Y), len(Y[0])) width = len(coefs) - 1 new_X = np.resize(np.repeat(np.arange(1, length + 1), width), (length, width)) if list_like(new_X): new_X = np.array(new_X) if len(coefs) - 1 not in new_X.shape: return REF_ERROR if new_X.shape[1] != len(coefs) - 1: if full_rank: result = np.array( coefs[-2::-1]).transpose() @ new_X + coefs[-1] else: result = (coefs[-1], ) * new_X.shape[1] return result[0] if len(result) == 1 else (tuple(result), ) else: if full_rank: result = new_X @ np.array(coefs[-2::-1]) + coefs[-1] else: result = (coefs[-1], ) * new_X.shape[0] return result[0] if len(result) == 1 else tuple( (x, ) for x in result) elif len(coefs) != 2: # new_X is a scaler, this needs to be a single coef fit return REF_ERROR elif not full_rank: return coefs[-1] else: return coefs[0] * new_X + coefs[1]
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 index(array, row_num, col_num=None, rows=None, cols=None): # Excel reference: https://support.office.com/en-us/article/ # index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd # A returned string is an error code if isinstance(array, str): return array if row_num in ERROR_CODES: return row_num if col_num in ERROR_CODES: return col_num try: if list_like(array[0]): if rows or cols: # when we get array formulas out of the worksheet we expand # them into an index call. If we have rows and cols then # this is the size of the original range. Excel will expand # a vector to fill the rectangle. if 1 == len(array) and row_num <= rows: row_num = 1 if 1 == len(array[0]) and col_num <= cols: col_num = 1 # rectangular array if None not in (row_num, col_num): return array[row_num - 1][col_num - 1] elif row_num is not None: return array[row_num - 1] elif col_num is not None: if isinstance(array, np.ndarray): return array[:, col_num - 1] else: return type(array)(row[col_num - 1] for row in array) elif col_num in (1, None): return array[row_num - 1] elif row_num == 1: return array[col_num - 1] except IndexError: pass return NA_ERROR
def averageifs(average_range, *args): # Excel reference: https://support.microsoft.com/en-us/office/ # AVERAGEIFS-function-48910C45-1FC0-4389-A028-F7C5C3001690 if not list_like(average_range): average_range = ((average_range, ), ) coords = handle_ifs(args, average_range) # A returned string is an error code if isinstance(coords, str): return coords data = _numerics((average_range[r][c] for r, c in coords), keep_bools=True) if len(data) == 0: return DIV0 return sum(data) / len(data)
def lookup(lookup_value, lookup_array, result_range=None): """ There are two ways to use LOOKUP: Vector form and Array form Vector form: lookup_array is list like (ie: n x 1) Array form: lookup_array is rectangular (ie: n x m) First row or column is the lookup vector. Last row or column is the result vector The longer dimension is the search dimension :param lookup_value: value to match (value or cell reference) :param lookup_array: range of cells being searched. :param result_range: (optional vector form) values are returned from here :return: #N/A if not found else value """ if not list_like(lookup_array): return NA_ERROR height = len(lookup_array) width = len(lookup_array[0]) # match across the largest dimension if width <= height: match_idx = _match(lookup_value, tuple(i[0] for i in lookup_array)) result = tuple(i[-1] for i in lookup_array) else: match_idx = _match(lookup_value, lookup_array[0]) result = lookup_array[-1] if len(lookup_array) > 1 and len(lookup_array[0]) > 1: # rectangular array assert result_range is None elif result_range: if len(result_range) > len(result_range[0]): result = tuple(i[0] for i in result_range) else: result = result_range[0] if isinstance(match_idx, int): return result[match_idx - 1] else: # error string return match_idx
def minifs(min_range, *args): # Excel reference: https://support.microsoft.com/en-us/office/ # minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599 if not list_like(min_range): min_range = ((min_range, ), ) try: coords = handle_ifs(args, min_range) # A returned string is an error code if isinstance(coords, str): return coords return min( _numerics((min_range[r][c] for r, c in coords), keep_bools=True)) except ValueError: return 0
def maxifs(max_range, *args): # Excel reference: https://support.microsoft.com/en-us/office/ # maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883 if not list_like(max_range): max_range = ((max_range, ), ) try: coords = handle_ifs(args, max_range) # A returned string is an error code if isinstance(coords, str): return coords return max( _numerics((max_range[r][c] for r, c in coords), keep_bools=True)) except ValueError: return 0
def _evaluate(self, address): """Evaluate a single cell""" 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( "Evaluating: {}, {}".format(cell.address, cell.python_code)) value = self.eval(cell) 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 return cell.value
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)) 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 evaluate(self, address, _recursed=False): """ evaluate a cell or cells in the spreadsheet :param address: str, AddressRange, AddressCell or a tuple or list or iterable of these three :return: evaluated value/values """ if str(address) not in self.cell_map: if list_like(address): if not isinstance(address, (tuple, list)): address = tuple(address) # process a tuple or list of addresses return type(address)(self.evaluate(c, True) for c in address) address = AddressRange.create(address) # get the sheet if not specified if not address.has_sheet: address = AddressRange( address, sheet=self.excel.get_active_sheet_name()) if address.address not in self.cell_map: self._gen_graph(address.address) if not _recursed: for cell in self.cell_map.values(): if isinstance(cell, _CellRange) or cell.formula: cell.iterations = 0 result = self._evaluate(str(address)) if isinstance(result, tuple): # trim excess dimensions if len(result[0]) == 1: result = tuple(row[0] for row in result) if len(result) == 1: result = result[0] return result
def validate_calcs(self, output_addrs=None, sheet=None, verify_tree=True): """For each address, calc the value, and verify that it matches This is a debugging tool which will show which cells evaluate differently than they do for excel. :param output_addrs: The cells to evaluate from (defaults to all) :param sheet: The sheet to evaluate from (defaults to all) :param verify_tree: Follow the tree to any precedent nodes :return: dict of addresses with good/bad values that failed to verify """ def close_enough(val1, val2): import pytest if isinstance(val1, (int, float)) and \ isinstance(val2, (int, float)): return val2 == pytest.approx(val1) else: return val1 == val2 Mismatch = collections.namedtuple('Mismatch', 'original calced formula') if output_addrs is None: to_verify = list(self.formula_cells(sheet)) print('Found {} formulas to evaluate'.format(len(to_verify))) elif list_like(output_addrs): to_verify = [AddressCell(addr) for addr in flatten(output_addrs)] else: to_verify = [AddressCell(output_addrs)] verified = set() failed = {} while to_verify: addr = to_verify.pop() if len(to_verify) % 100 == 0: print("{} formulas left to process".format(len(to_verify))) try: self._gen_graph(addr) cell = self.cell_map[addr.address] if isinstance(cell, _Cell) and cell.python_code: original_value = cell.value if original_value == str(cell.formula): self.log.debug( "No Orig data?: {}: {}".format(addr, cell.value)) continue cell.value = None self._evaluate(addr.address) if not (original_value is None or close_enough(original_value, cell.value)): failed.setdefault('mismatch', {})[str(addr)] = Mismatch( original_value, cell.value, cell.formula.base_formula) print('{} mismatch {} -> {} {}'.format( addr, original_value, cell.value, cell.formula.base_formula)) # do it again to allow easy breakpointing cell.value = None self._evaluate(cell.address.address) verified.add(addr) if verify_tree: # pragma: no branch for addr in cell.needed_addresses: if addr not in verified: # pragma: no branch to_verify.append(addr) except Exception as exc: cell = self.cell_map.get(addr.address, None) formula = cell and cell.formula.base_formula exc_str = str(exc) exc_str_split = exc_str.split('\n') if 'is not implemented' in exc_str: exc_str_key = exc_str.split('is not implemented')[0] exc_str_key = exc_str_key.strip().rsplit(' ', 1)[1].upper() not_implemented = True else: if len(exc_str_split) == 1: exc_str_key = '{}: {}'.format( type(exc).__name__, exc_str) else: exc_str_key = exc_str_split[-2] # pragma: no cover not_implemented = exc_str_key.startswith( 'NotImplementedError: ') if not_implemented: failed.setdefault('not-implemented', {}).setdefault( exc_str_key, []).append((str(addr), formula, exc_str)) else: failed.setdefault('exceptions', {}).setdefault( exc_str_key, []).append((str(addr), formula, exc_str)) return failed
def validate_calcs(self, output_addrs=None): """For each address, calc the value, and verify that it matches This is a debugging tool which will show which cells evaluate differently than they do for excel. :param output_addrs: The cells to evaluate from (defaults to all) :return: dict of addresses with good/bad values that failed to verify """ def close_enough(val1, val2): import pytest if isinstance(val1, (int, float)) and \ isinstance(val2, (int, float)): return val2 == pytest.approx(val1) else: return val1 == val2 Mismatch = collections.namedtuple('Mismatch', 'original calced formula') if output_addrs is None: to_verify = self._formula_cells elif list_like(output_addrs): to_verify = [AddressCell(addr) for addr in flatten(output_addrs)] else: to_verify = [AddressCell(output_addrs)] verified = set() failed = {} while to_verify: addr = to_verify.pop() try: self._gen_graph(addr) cell = self.cell_map[addr.address] if isinstance(cell, _Cell) and cell.python_code: original_value = cell.value if original_value == str(cell.formula): self.log.debug("No Orig data?: {}: {}".format( addr, cell.value)) continue cell.value = None self._evaluate(addr.address) # pragma: no branch if not close_enough(original_value, cell.value): failed.setdefault('mismatch', {})[str(addr)] = Mismatch( original_value, cell.value, cell.formula.base_formula) print('{} mismatch {} -> {} {}'.format( addr, original_value, cell.value, cell.formula.base_formula)) # do it again to allow easy breakpointing cell.value = None self._evaluate(cell.address.address) verified.add(addr) for addr in cell.needed_addresses: if addr not in verified: # pragma: no branch to_verify.append(addr) except Exception as exc: cell = self.cell_map.get(addr.address, None) formula = cell and cell.formula.base_formula exc_str = str(exc) exc_str_split = exc_str.split('\n') if 'has not been implemented' in exc_str: exc_str_key = exc_str.split('has not been implemented')[0] exc_str_key = exc_str_key.strip().rsplit(' ', 1)[1].upper() not_implemented = True else: if len(exc_str_split) == 1: exc_str_key = '{}: {}'.format( type(exc).__name__, exc_str) else: exc_str_key = exc_str_split[-2] # pragma: no cover not_implemented = exc_str_key.startswith( 'NotImplementedError: ') if not_implemented: failed.setdefault('not-implemented', {}).setdefault(exc_str_key, []).append( (str(addr), formula, exc_str)) else: failed.setdefault('exceptions', {}).setdefault(exc_str_key, []).append( (str(addr), formula, exc_str)) return failed
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 validate_calcs(self, output_addrs=None, sheet=None, verify_tree=True, tolerance=None, raise_exceptions=False): """For each address, calc the value, and verify that it matches This is a debugging tool which will show which cells evaluate differently than they do for excel. :param output_addrs: The cells to evaluate from (defaults to all) :param sheet: The sheet to evaluate from (defaults to all) :param verify_tree: Follow the tree to any precedent nodes :return: dict of addresses with good/bad values that failed to verify """ if output_addrs is None: to_verify = list(self.formula_cells(sheet)) print(f'Found {len(to_verify)} formulas to evaluate') elif list_like(output_addrs): to_verify = [AddressCell(addr) for addr in flatten(output_addrs)] else: to_verify = [AddressCell(output_addrs)] verified = set() failed = {} if self.cycles: iterative_eval_tracker(**self.cycles) while to_verify: addr = to_verify.pop() if len(to_verify) % 100 == 0: print(f"{len(to_verify)} formulas left to process") try: self._gen_graph(addr) cell = self.cell_map[addr.address] if isinstance(cell, _Cell) and cell.python_code and ( not cell.address.is_unbounded_range): original_value = cell.value if original_value == str(cell.formula): self.log.debug(f"No Orig data?: {addr}: {cell.value}") continue cell.value = None self.evaluate(addr.address) if not (original_value is None or cell.close_enough( original_value, tol=tolerance)): failed.setdefault('mismatch', {})[str(addr)] = Mismatch( original_value, cell.value, cell.formula.base_formula) print('{} mismatch {} -> {} {}'.format( addr, original_value, cell.value, cell.formula.base_formula)) # do it again to allow easy break-pointing cell.value = None self.evaluate(cell.address.address) verified.add(addr) if verify_tree: # pragma: no branch for addr in cell.needed_addresses: if addr not in verified: # pragma: no branch to_verify.append(addr) except Exception as exc: if raise_exceptions: raise cell = self.cell_map.get(addr.address, None) formula = cell and cell.formula.base_formula exc_str = str(exc) exc_str_split = exc_str.split('\n') if 'is not implemented' in exc_str: exc_str_key = exc_str.split('is not implemented')[0] exc_str_key = exc_str_key.strip().rsplit(' ', 1)[1].upper() not_implemented = True else: if len(exc_str_split) == 1: exc_str_key = f'{type(exc).__name__}: {exc_str}' else: exc_str_key = exc_str_split[-2] # pragma: no cover not_implemented = exc_str_key.startswith( 'NotImplementedError: ') if not_implemented: failed.setdefault('not-implemented', {}).setdefault(exc_str_key, []).append( (str(addr), formula, exc_str)) else: failed.setdefault('exceptions', {}).setdefault(exc_str_key, []).append( (str(addr), formula, exc_str)) return failed