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