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 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_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 get_range(self, address):
if not isinstance(address, (AddressRange, AddressCell)):
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
cells = sheet[address.coordinate]
if isinstance(cells, Cell):
cell = cells
cell_dataonly = sheet_dataonly[address.coordinate]
return _OpxCell(cell, cell_dataonly, address)
else:
cells_dataonly = sheet_dataonly[address.coordinate]
addr_size = address.size
if 1 in addr_size:
if cells_dataonly \
and not isinstance(cells_dataonly[0], tuple):
# openpyxl returns a one dimensional structure for some
if addr_size.width == 1:
cells = tuple((c, ) for c in cells)
cells_dataonly = tuple(
(c, ) for c in cells_dataonly)
else:
cells = (cells, )
cells_dataonly = (cells_dataonly, )
elif addr_size.height == MAX_ROW:
# openpyxl does iter_cols, we need to transpose
cells = tuple(zip(*cells))
cells_dataonly = tuple(zip(*cells_dataonly))
if len(cells) != len(cells_dataonly):
# The read_only version of openpyxl worksheet has the
# somewhat annoying property of not giving empty rows at the
# end. Which is not the same behavior as the non-readonly
# version. So we need to align the data here by adding
# empty rows.
empty_row = (EMPTY_CELL, ) * len(cells[0])
empty_rows = (empty_row, ) * (len(cells) -
len(cells_dataonly))
cells_dataonly += empty_rows
# full range column or row addresses, trim the address
if len(cells) < addr_size.height or \
len(cells[0]) < addr_size.width:
start_col = address.start.column or 'A'
start_row = address.start.row or 1
start_addr = AddressCell(start_col + str(start_row),
sheet=address.sheet)
stop_addr = start_addr.address_at_offset(
len(cells) - 1,
len(cells[0]) - 1)
address = AddressRange((start_addr.col_idx, start_addr.row,
stop_addr.col_idx, stop_addr.row),
sheet=address.sheet)
return _OpxRange(cells, cells_dataonly, address)
