def test_multiply(self):
self.assertEqual(RangeCore.multiply(2, 2), 4)
self.assertEqual(RangeCore.multiply(1, 0), 0)
self.assertIsInstance(RangeCore.multiply(ExcelError('#VALUE'), 1),
ExcelError)
self.assertIsInstance(RangeCore.multiply(1, ExcelError('#VALUE')),
ExcelError)
def test_divide(self):
self.assertEqual(RangeCore.divide(2, 2), 1)
self.assertIsInstance(RangeCore.divide(1, 0), ExcelError)
self.assertIsInstance(RangeCore.divide(ExcelError('#VALUE'), 1),
ExcelError)
self.assertIsInstance(RangeCore.divide(1, ExcelError('#VALUE')),
ExcelError)
def vlookup(lookup_value, table_array, col_index_num, range_lookup = True): # https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
if not isinstance(table_array, Range):
return ExcelError('#VALUE', 'table_array should be a Range')
if col_index_num > table_array.ncols:
return ExcelError('#VALUE', 'col_index_num is greater than the number of cols in table_array')
first_column = table_array.get(0, 1)
result_column = table_array.get(0, col_index_num)
if not range_lookup:
if lookup_value not in first_column.values:
return ExcelError('#N/A', 'lookup_value not in first column of table_array')
else:
i = first_column.values.index(lookup_value)
ref = first_column.order[i]
else:
i = None
for v in first_column.values:
if lookup_value >= v:
i = first_column.values.index(v)
ref = first_column.order[i]
else:
break
if i is None:
return ExcelError('#N/A', 'lookup_value smaller than all values of table_array')
return Range.find_associated_value(ref, result_column)
def vlookup(lookup_value, table_array, col_index_num, range_lookup = True): # https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
if not isinstance(table_array, Range):
return ExcelError('#VALUE', 'table_array should be a Range')
if col_index_num > table_array.ncols:
return ExcelError('#VALUE', 'col_index_num is greater than the number of cols in table_array')
first_column = table_array.get(0, 1)
result_column = table_array.get(0, col_index_num)
list = zip(first_column.order, first_column.values)
if not range_lookup:
if lookup_value not in first_column.values:
return ExcelError('#N/A', 'lookup_value not in first column of table_array')
else:
i = first_column.values.index(lookup_value)
ref = first_column.order[i]
else:
i = None
for v in first_column.values:
if lookup_value >= v:
i = first_column.values.index(v)
ref = first_column.order[i]
else:
break
if i is None:
return ExcelError('#N/A', 'lookup_value smaller than all values of table_array')
return Range.find_associated_value(ref, result_column)
def cell_from_dict(d):
cell_is_range = type(d["value"]) == dict
if cell_is_range:
range = d["value"]
if len(range["values"]) == 0:
range["values"] = [None] * len(range["cells"])
value = RangeCore(range["cells"], range["values"], nrows = range["nrows"], ncols = range["ncols"])
else:
value = d["value"]
new_cell = Cell(d["address"], None, value=value, formula=d["formula"], is_range = cell_is_range, is_named_range=d["is_named_range"], should_eval=d["should_eval"])
new_cell.python_expression = d["python_expression"]
new_cell.compile()
return {"id": new_cell}
def sumproduct(*ranges): # Excel reference: https://support.office.com/en-us/article/SUMPRODUCT-function-16753e75-9f68-4874-94ac-4d2145a2fd2e
range_list = list(ranges)
for r in range_list: # if a range has no values (i.e if it's empty)
if len(r.values) == 0:
return 0
for range in range_list:
for item in range.values:
# If there is an ExcelError inside a Range, sumproduct should output an ExcelError
if isinstance(item, ExcelError):
return ExcelError("#N/A", "ExcelErrors are present in the sumproduct items")
reduce(check_length, range_list) # check that all ranges have the same size
return reduce(lambda X, Y: X + Y, reduce(lambda x, y: Range.apply_all('multiply', x, y), range_list).values)
def sumproduct(*ranges): # Excel reference: https://support.office.com/en-us/article/SUMPRODUCT-function-16753e75-9f68-4874-94ac-4d2145a2fd2e
range_list = list(ranges)
for r in range_list: # if a range has no values (i.e if it's empty)
if len(r.values) == 0:
return 0
for range in range_list:
for item in range.values:
# If there is an ExcelError inside a Range, sumproduct should output an ExcelError
if isinstance(item, ExcelError):
return ExcelError("#N/A", "ExcelErrors are present in the sumproduct items")
reduce(check_length, range_list) # check that all ranges have the same size
return reduce(lambda X, Y: X + Y, reduce(lambda x, y: Range.apply_all('multiply', x, y), range_list).values)
def eval_ref(self, addr1, addr2=None, ref=None):
debug = False
if isinstance(addr1, ExcelError):
return addr1
elif isinstance(addr2, ExcelError):
return addr2
else:
if addr1 in self.cellmap:
cell1 = self.cellmap[addr1]
else:
if self.debug:
print 'WARNING in eval_ref: address %s not found in cellmap, returning #NULL' % addr1
return ExcelError('#NULL', 'Cell %s is empty' % addr1)
if addr2 == None:
if cell1.is_range:
if cell1.range.is_pointer:
self.build_pointer(cell1.range)
# print 'NEED UPDATE', cell1.need_update
associated_addr = RangeCore.find_associated_cell(
ref, cell1.range)
if associated_addr: # if range is associated to ref, no need to return/update all range
return self.evaluate(associated_addr)
else:
range_name = cell1.address()
if cell1.need_update:
self.update_range(cell1.range)
range_need_update = True
for c in self.G.successors_iter(
cell1
): # if a parent doesnt need update, then cell1 doesnt need update
if not c.need_update:
range_need_update = False
break
cell1.need_update = range_need_update
return cell1.range
else:
return cell1.range
elif addr1 in self.named_ranges or not is_range(addr1):
val = self.evaluate(addr1)
return val
else: # addr1 = Sheet1!A1:A2 or Sheet1!A1:Sheet1!A2
addr1, addr2 = addr1.split(':')
if '!' in addr1:
sheet = addr1.split('!')[0]
else:
sheet = None
if '!' in addr2:
addr2 = addr2.split('!')[1]
return self.Range('%s:%s' % (addr1, addr2))
else: # addr1 = Sheet1!A1, addr2 = Sheet1!A2
if '!' in addr1:
sheet = addr1.split('!')[0]
else:
sheet = None
if '!' in addr2:
addr2 = addr2.split('!')[1]
return self.Range('%s:%s' % (addr1, addr2))
def test_power(self):
self.assertEqual(RangeCore.power(2, 2), 4)
self.assertIsInstance(RangeCore.power(ExcelError('#VALUE'), 1),
ExcelError)
self.assertIsInstance(RangeCore.power(1, ExcelError('#VALUE')),
ExcelError)
def eval_ref(self, addr1, addr2 = None, ref = None):
debug = False
if isinstance(addr1, ExcelError):
return addr1
elif isinstance(addr2, ExcelError):
return addr2
else:
if addr1 in self.cellmap:
cell1 = self.cellmap[addr1]
else:
if self.debug:
print 'WARNING in eval_ref: address %s not found in cellmap, returning #NULL' % addr1
return ExcelError('#NULL', 'Cell %s is empty' % addr1)
if addr2 == None:
if cell1.is_range:
if cell1.range.is_volatile:
self.build_volatile(cell1.range)
associated_addr = RangeCore.find_associated_cell(ref, cell1.range)
if associated_addr: # if range is associated to ref, no need to return/update all range
return self.evaluate(associated_addr)
else:
range_name = cell1.address()
if cell1.need_update:
self.update_range(cell1.range)
range_need_update = True
for c in self.G.successors_iter(cell1): # if a parent doesnt need update, then cell1 doesnt need update
if not c.need_update:
range_need_update = False
break
cell1.need_update = range_need_update
return cell1.range
else:
return cell1.range
elif addr1 in self.named_ranges or not is_range(addr1):
val = self.evaluate(addr1)
return val
else: # addr1 = Sheet1!A1:A2 or Sheet1!A1:Sheet1!A2
addr1, addr2 = addr1.split(':')
if '!' in addr1:
sheet = addr1.split('!')[0]
else:
sheet = None
if '!' in addr2:
addr2 = addr2.split('!')[1]
return self.Range('%s:%s' % (addr1, addr2))
else: # addr1 = Sheet1!A1, addr2 = Sheet1!A2
if '!' in addr1:
sheet = addr1.split('!')[0]
else:
sheet = None
if '!' in addr2:
addr2 = addr2.split('!')[1]
return self.Range('%s:%s' % (addr1, addr2))