def test_get_parse_tree_dependencies_should_not_return_locations_for_deleted_references(
self):
self.assertEquals(
get_dependencies_from_parse_tree(parse("=#Deleted!")), [])
self.assertEquals(
get_dependencies_from_parse_tree(parse("=#Deleted! + A2")),
[(1, 2)])
def test_rewrite_should_raise_deleted_cell_reference_error_when_appropriate(self):
with self.assertRaises(FormulaError) as mgr:
rewrite(parse('=#Deleted!')).flatten()
self.assertEquals(str(mgr.exception), "#Deleted! cell reference in formula")
with self.assertRaises(FormulaError) as mgr:
rewrite(parse('=sum(#Deleted!:B1)'))
self.assertEquals(str(mgr.exception), "#Deleted! cell reference in formula")
def test_get_parse_tree_dependencies_should_not_return_locations_for_worksheet_references(
self):
for expression in ("=<Sheet1>", "=<Sheet1>.Bounds",
"=<Sheet1> + <Sheet2>"):
self.assertEquals(
get_dependencies_from_parse_tree(parse(expression)), [])
self.assertEquals(
get_dependencies_from_parse_tree(parse("=<Sheet1> + A2")),
[(1, 2)])
def test_get_parse_tree_dependencies_should_not_return_locations_for_invalid_references_in_cell_ranges(
self):
self.assertEquals(
get_dependencies_from_parse_tree(parse("=#Invalid!:A2")), [])
self.assertEquals(
get_dependencies_from_parse_tree(parse("=A2:#Invalid!")), [])
self.assertEquals(
get_dependencies_from_parse_tree(parse("=#Invalid!:#Invalid!")),
[])
def test_rewrite_should_raise_deleted_cell_reference_error_when_appropriate(
self):
with self.assertRaises(FormulaError) as mgr:
rewrite(parse('=#Deleted!')).flatten()
self.assertEquals(str(mgr.exception),
"#Deleted! cell reference in formula")
with self.assertRaises(FormulaError) as mgr:
rewrite(parse('=sum(#Deleted!:B1)'))
self.assertEquals(str(mgr.exception),
"#Deleted! cell reference in formula")
def test_rewrite_should_translate_dictionary_arrow_to_colon(self):
self.assertEquals(
rewrite(parse('={"key"->"value"}')).flatten(), '={"key":"value"}')
self.assertEquals(
rewrite(parse('={"key" ->"value"}')).flatten(),
'={"key" :"value"}')
self.assertEquals(
rewrite(parse('={"key"-> "value"}')).flatten(),
'={"key":"value"}')
def test_rewrite_should_translate_lambda_arrow_to_colon(self):
self.assertEquals(
rewrite(parse('=lambda x-> x')).flatten(), '=lambda x:x')
self.assertEquals(
rewrite(parse('=lambda x-> x')).flatten(), '=lambda x:x')
self.assertEquals(
rewrite(parse('=lambda x -> x')).flatten(), '=lambda x :x')
self.assertEquals(
rewrite(parse('=lambda -> x')).flatten(), '=lambda :x')
def test_get_parse_tree_dependencies_should_return_cellrange_deps(self):
self.assertItemsEqual(
get_dependencies_from_parse_tree(parse('=a2:b3')), [(1, 2), (1, 3),
(2, 2),
(2, 3)])
self.assertItemsEqual(
get_dependencies_from_parse_tree(parse('=b3:a2')), [(1, 2), (1, 3),
(2, 2),
(2, 3)])
self.assertItemsEqual(
get_dependencies_from_parse_tree(parse('=a3:b2')), [(1, 2), (1, 3),
(2, 2),
(2, 3)])
def test_get_parse_tree_dependencies_should_return_cellrange_deps(self):
self.assertItemsEqual(
get_dependencies_from_parse_tree(parse('=a2:b3')),
[(1, 2), (1, 3), (2, 2), (2, 3)]
)
self.assertItemsEqual(
get_dependencies_from_parse_tree(parse('=b3:a2')),
[(1, 2), (1, 3), (2, 2), (2, 3)]
)
self.assertItemsEqual(
get_dependencies_from_parse_tree(parse('=a3:b2')),
[(1, 2), (1, 3), (2, 2), (2, 3)]
)
def test_rewrite_should_translate_dictionary_arrow_to_colon(self):
self.assertEquals(
rewrite(parse('={"key"->"value"}')).flatten(),
'={"key":"value"}'
)
self.assertEquals(
rewrite(parse('={"key" ->"value"}')).flatten(),
'={"key" :"value"}'
)
self.assertEquals(
rewrite(parse('={"key"-> "value"}')).flatten(),
'={"key":"value"}'
)
def test_produces_correct_python(self):
self.assertEquals(
get_python_formula_from_parse_tree(
parse('=[x * A1 for x in range(5)]')),
'[x * worksheet[(1,1)].value for x in range(5)]')
self.assertEquals(
get_python_formula_from_parse_tree(
parse('=[x in A1:B3 for x in range(5)]')),
'[x in CellRange(worksheet,(1,1),(2,3)) for x in range(5)]')
self.assertEquals(
get_python_formula_from_parse_tree(parse('={1 -> B1}')),
'{1 :worksheet[(2,1)].value }')
self.assertEquals(
get_python_formula_from_parse_tree(
parse('=(lambda x -> C1 * x)(2)')),
'(lambda x :worksheet[(3,1)].value * x)(2)')
def rewrite_formula(
formula, column_offset, row_offset, is_cut, orig_bounds
):
if formula is None or not formula.startswith('='):
return formula
def cell_in_original_bounds(column, row):
return (
orig_bounds[0] <= column <= orig_bounds[2] and
orig_bounds[1] <= row <= orig_bounds[3]
)
def range_in_original_bounds(left, top, right, bottom):
return (
cell_in_original_bounds(left, top) and
cell_in_original_bounds(right, bottom)
)
def offset_cell_reference(node):
orig_column, orig_row = node.coords
if is_cut and not cell_in_original_bounds(orig_column, orig_row):
return node
node.offset(column_offset, row_offset, move_absolute=is_cut)
return node
def offset_cell_range(node):
corner1_col, corner1_row = node.first_cell_reference.coords
corner2_col, corner2_row = node.second_cell_reference.coords
if is_cut and not range_in_original_bounds(
corner1_col, corner1_row, corner2_col, corner2_row
):
return node
node.first_cell_reference.offset(column_offset, row_offset, move_absolute=is_cut)
node.second_cell_reference.offset(column_offset, row_offset, move_absolute=is_cut)
return node
def rewrite(node):
if isinstance(node, ParseNode):
if isinstance(node, FLCellRange):
return offset_cell_range(node)
if isinstance(node, FLCellReference):
return offset_cell_reference(node)
else:
node.children = [
rewrite(child)
for child in node.children
]
return node
try:
return rewrite(parse(formula)).flatten()
except FormulaError:
return formula
def test_rewrite_should_translate_lambda_arrow_to_colon(self):
self.assertEquals(
rewrite(parse('=lambda x-> x')).flatten(),
'=lambda x:x'
)
self.assertEquals(
rewrite(parse('=lambda x-> x')).flatten(),
'=lambda x:x'
)
self.assertEquals(
rewrite(parse('=lambda x -> x')).flatten(),
'=lambda x :x'
)
self.assertEquals(
rewrite(parse('=lambda -> x')).flatten(),
'=lambda :x'
)
def test_produces_correct_python(self):
self.assertEquals(
get_python_formula_from_parse_tree(parse(
'=[x * A1 for x in range(5)]'
)),
'[x * worksheet[(1,1)].value for x in range(5)]'
)
self.assertEquals(
get_python_formula_from_parse_tree(parse(
'=[x in A1:B3 for x in range(5)]'
)),
'[x in CellRange(worksheet,(1,1),(2,3)) for x in range(5)]'
)
self.assertEquals(
get_python_formula_from_parse_tree(parse('={1 -> B1}')),
'{1 :worksheet[(2,1)].value }'
)
self.assertEquals(
get_python_formula_from_parse_tree(parse('=(lambda x -> C1 * x)(2)')),
'(lambda x :worksheet[(3,1)].value * x)(2)'
)
def test_slicing_in_formulae(self):
self.assertEquals(
rewrite(parse('=p[1->]')).flatten(),
'=p[1:]'
)
self.assertEquals(
rewrite(parse('=p[->6]')).flatten(),
'=p[:6]'
)
self.assertEquals(
rewrite(parse('=p[1->6]')).flatten(),
'=p[1:6]'
)
self.assertEquals(
rewrite(parse('=p[1->6->3]')).flatten(),
'=p[1:6:3]'
)
self.assertEquals(
rewrite(parse('=p[1->->3]')).flatten(),
'=p[1::3]'
)
def rewrite_formula(formula, column_offset, row_offset, is_cut, orig_bounds):
if formula is None or not formula.startswith('='):
return formula
def cell_in_original_bounds(column, row):
return (orig_bounds[0] <= column <= orig_bounds[2]
and orig_bounds[1] <= row <= orig_bounds[3])
def range_in_original_bounds(left, top, right, bottom):
return (cell_in_original_bounds(left, top)
and cell_in_original_bounds(right, bottom))
def offset_cell_reference(node):
orig_column, orig_row = node.coords
if is_cut and not cell_in_original_bounds(orig_column, orig_row):
return node
node.offset(column_offset, row_offset, move_absolute=is_cut)
return node
def offset_cell_range(node):
corner1_col, corner1_row = node.first_cell_reference.coords
corner2_col, corner2_row = node.second_cell_reference.coords
if is_cut and not range_in_original_bounds(corner1_col, corner1_row,
corner2_col, corner2_row):
return node
node.first_cell_reference.offset(column_offset,
row_offset,
move_absolute=is_cut)
node.second_cell_reference.offset(column_offset,
row_offset,
move_absolute=is_cut)
return node
def rewrite(node):
if isinstance(node, ParseNode):
if isinstance(node, FLCellRange):
return offset_cell_range(node)
if isinstance(node, FLCellReference):
return offset_cell_reference(node)
else:
node.children = [rewrite(child) for child in node.children]
return node
try:
return rewrite(parse(formula)).flatten()
except FormulaError:
return formula
def _set_formula(self, value):
self._python_formula = None
if value is None:
self._formula = None
elif type(value) == str or type(value) == unicode:
self._formula = value
if value.startswith('='):
try:
parsed_formula = parser.parse(value)
self.dependencies = get_dependencies_from_parse_tree(parsed_formula)
self._python_formula = get_python_formula_from_parse_tree(parsed_formula)
except FormulaError, e:
self.dependencies = []
self._python_formula = '_raise(FormulaError("{}"))'.format(e)
def _set_formula(self, value):
self._python_formula = None
if value is None:
self._formula = None
elif type(value) == str or type(value) == unicode:
self._formula = value
if value.startswith('='):
try:
parsed_formula = parser.parse(value)
self.dependencies = get_dependencies_from_parse_tree(
parsed_formula)
self._python_formula = get_python_formula_from_parse_tree(
parsed_formula)
except FormulaError, e:
self.dependencies = []
self._python_formula = '_raise(FormulaError("{}"))'.format(
e)
def test_get_parse_tree_dependencies_should_not_return_locations_for_invalid_references(self):
self.assertEquals(get_dependencies_from_parse_tree(parse("=#Invalid!")), [])
self.assertEquals(get_dependencies_from_parse_tree(parse("=#Invalid! + A2")), [(1, 2)])
def test_get_parse_tree_dependencies_should_return_locations_for_simple_expression_with_case_mismatch(
self):
tree = parse("=a1+A2")
self.assertEquals(get_dependencies_from_parse_tree(tree), [(1, 1),
(1, 2)])
def test_get_parse_tree_dependencies_should_return_empty_list_when_no_cell_refs(
self):
tree = parse("=1+2")
self.assertEquals(get_dependencies_from_parse_tree(tree), [])
def test_converts_formula_starting_with_equals(self):
self.assertEquals(get_python_formula_from_parse_tree(parse('=1')), "1")
self.assertEquals(get_python_formula_from_parse_tree(parse('=1+2')), "1+2")
def test_converts_cell_references_and_adds_space(self):
self.assertEquals(
get_python_formula_from_parse_tree(parse('=A1')),
"worksheet[(1,1)].value "
)
def test_get_parse_tree_dependencies_should_not_return_locations_for_worksheet_references(self):
for expression in ("=<Sheet1>", "=<Sheet1>.Bounds", "=<Sheet1> + <Sheet2>"):
self.assertEquals(get_dependencies_from_parse_tree(parse(expression)), [])
self.assertEquals(get_dependencies_from_parse_tree(parse("=<Sheet1> + A2")), [(1, 2)])
def test_get_parse_tree_dependencies_should_return_locations_disregarding_worksheet_names(
self):
tree = parse("=Sheet1!A3")
self.assertEquals(get_dependencies_from_parse_tree(tree), [(1, 3)])
def test_slicing_in_formulae(self):
self.assertEquals(rewrite(parse('=p[1->]')).flatten(), '=p[1:]')
self.assertEquals(rewrite(parse('=p[->6]')).flatten(), '=p[:6]')
self.assertEquals(rewrite(parse('=p[1->6]')).flatten(), '=p[1:6]')
self.assertEquals(rewrite(parse('=p[1->6->3]')).flatten(), '=p[1:6:3]')
self.assertEquals(rewrite(parse('=p[1->->3]')).flatten(), '=p[1::3]')
def test_get_parse_tree_dependencies_should_not_return_locations_for_deleted_references_in_cell_ranges(self):
self.assertEquals(get_dependencies_from_parse_tree(parse("=#Deleted!:A2")), [])
self.assertEquals(get_dependencies_from_parse_tree(parse("=A2:#Deleted!")), [])
self.assertEquals(get_dependencies_from_parse_tree(parse("=#Deleted!:#Deleted!")), [])
def test_get_parse_tree_dependencies_should_return_locations_disregarding_worksheet_names(self):
tree = parse("=Sheet1!A3")
self.assertEquals(get_dependencies_from_parse_tree(tree), [(1, 3)])
def test_get_parse_tree_dependencies_should_return_locations_used_for_function_calls_and_arguments(self):
tree = parse("=A2(a3)")
self.assertEquals(get_dependencies_from_parse_tree(tree), [(1, 2), (1, 3)])
def test_get_parse_tree_dependencies_should_return_locations_for_simple_expression_with_case_mismatch(self):
tree = parse("=a1+A2")
self.assertEquals(get_dependencies_from_parse_tree(tree), [(1, 1), (1, 2)])
def test_get_parse_tree_dependencies_should_return_empty_list_when_no_cell_refs(self):
tree = parse("=1+2")
self.assertEquals(get_dependencies_from_parse_tree(tree), [])
def test_get_parse_tree_dependencies_should_return_locations_used_for_function_calls_and_arguments(
self):
tree = parse("=A2(a3)")
self.assertEquals(get_dependencies_from_parse_tree(tree), [(1, 2),
(1, 3)])
def test_converts_formula_starting_with_equals(self):
self.assertEquals(get_python_formula_from_parse_tree(parse('=1')), "1")
self.assertEquals(get_python_formula_from_parse_tree(parse('=1+2')),
"1+2")
def test_converts_cell_references_and_adds_space(self):
self.assertEquals(get_python_formula_from_parse_tree(parse('=A1')),
"worksheet[(1,1)].value ")
