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_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 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 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)
