def test_CONCAT_MS_cases(self): self.assertEqual( text.CONCAT( "The", " ", "sun", " ", "will", " ", "come", " ", "up", " ", "tomorrow." ), "The sun will come up tomorrow.") range0 = func_xltypes.Array( ["A's", 'a1', 'a2', 'a4', 'a5', 'a6', 'a7']) range1 = func_xltypes.Array( ["B's", 'b1', 'b2', 'b4', 'b5', 'b6', 'b7']) self.assertEqual(text.CONCAT(range0, range1), "A'sa1a2a4a5a6a7B'sb1b2b4b5b6b7")
def test_SUM_with_nonnumbers_in_range(self): self.assertEqual(math.SUM(func_xltypes.Array([[1, 'bad'], [3, 4]])), 8) self.assertEqual(math.SUM( func_xltypes.Array([ [func_xltypes.Number(1), func_xltypes.Text('N/A')], [func_xltypes.Number(3), func_xltypes.Number(4)] ])), 8)
def test_SUMPRODUCT_ranges_with_errors(self): range1 = func_xltypes.Array( [[xlerrors.NumExcelError('err')], [10], [3]] ) range2 = func_xltypes.Array([[3], [3], [1]]) self.assertIsInstance( math.SUMPRODUCT(range1, range2), xlerrors.NaExcelError)
def test_COUNT(self): range0 = func_xltypes.Array([[1, 2], [3, 4]]) range1 = func_xltypes.Array([[1, 2], [3, 'SPAM']]) self.assertEqual(statistics.COUNT(range0), 4) self.assertEqual(statistics.COUNT(range1), 3) self.assertEqual(statistics.COUNT(range0, range1), 7) self.assertEqual(statistics.COUNT(range0, range1, 1), 8) self.assertEqual(statistics.COUNT(range0, range1, 1, 'SPAM'), 8)
def test_COUNTIFS(self): countRange = func_xltypes.Array([1, 2, 3, 4]) countRange2 = func_xltypes.Array(["a", "B", "A", "A"]) condition = "<3" condition2 = "A" self.assertEqual(statistics.COUNTIFS( countRange, condition, countRange2, condition2 ), 1)
def test_XNPV_rate_lt_minus_one(self): range_00 = func_xltypes.Array( [[-10000, 2750, 4250, 3250, 2750]]) range_01 = func_xltypes.Array([[ date.DATE(2008, 1, 1), date.DATE(2008, 3, 1), date.DATE(2008, 10, 30), date.DATE(2009, 2, 15), date.DATE(2009, 4, 1) ]]) self.assertEqual( financial.XNPV(-1.1, range_00, range_01), float('inf'))
def test_XNPV(self): range_00 = func_xltypes.Array( [[-10000, 2750, 4250, 3250, 2750]]) range_01 = func_xltypes.Array([[ date.DATE(2008, 1, 1), date.DATE(2008, 3, 1), date.DATE(2008, 10, 30), date.DATE(2009, 2, 15), date.DATE(2009, 4, 1) ]]) self.assertAlmostEqual( financial.XNPV(0.09, range_00, range_01), 2086.65, 2)
def test_XIRR(self): range_00 = func_xltypes.Array( [[-10000, 2750, 4250, 3250, 2750]]) range_01 = func_xltypes.Array([[ date.DATE(2008, 1, 1), date.DATE(2008, 3, 1), date.DATE(2008, 10, 30), date.DATE(2009, 2, 15), date.DATE(2009, 4, 1) ]]) self.assertAlmostEqual( financial.XIRR(range_00, range_01, 0.1), 0.373362535)
def test_VLOOOKUP_with_oversized_col_index_num(self): # Excel Doc example. range1 = func_xltypes.Array([ [101, 'Davis', 'Sara'], ]) self.assertIsInstance(lookup.VLOOKUP(102, range1, 4, False), xlerrors.ValueExcelError)
def test_AVERAGE_mixed(self): self.assertEqual( statistics.AVERAGE( func_xltypes.Array([[1, 2], [3, 4]]), 1, 2, 3, 4 ), 2.5 )
def eval(self, context): addr = self.full_address(context) if addr in context.ranges: empty_row = 0 empty_col = 0 range_cells = [] for range_row in context.ranges[addr].cells: row_cells = [] for col_addr in range_row: cell = context.eval_cell(col_addr) if cell.value == '' or cell.value is None: empty_col += 1 if empty_col > MAX_EMPTY: break else: empty_col = 0 row_cells.append(cell) if not row_cells: empty_row += 1 if empty_row > MAX_EMPTY: break else: empty_row = 0 range_cells.append(row_cells) context.ranges[addr].value = data = func_xltypes.Array(range_cells) return data value = context.eval_cell(addr) context.set_sheet() return value
def test_IRR(self): self.assertAlmostEqual( financial.IRR( func_xltypes.Array([[-100, 39, 59, 55, 20]]) ), 0.2809484 )
def test_cast_to_texts(self): dt = datetime.datetime(1900, 1, 5) array = func_xltypes.Array([[1, None, dt], ['4.0', True, 6]]) texts = array.cast_to_texts() self.assertIsInstance(texts[0][0], func_xltypes.Text) self.assertEqual(texts.flat, ['1', '', '1900-01-05 00:00:00', '4.0', 'True', '6'])
def eval(self, context): addr = self.full_address(context.ref) if addr in context.ranges: range_cells = [[context.eval_cell(addr) for addr in range_row] for range_row in context.ranges[addr].cells] context.ranges[addr].value = data = func_xltypes.Array(range_cells) return data return context.eval_cell(addr)
def test_VLOOOKUP(self): # Excel Doc example. range1 = func_xltypes.Array([ [101, 'Davis', 'Sara'], [102, 'Fortana', 'Olivier'], [103, 'Leal', 'Karina'], [104, 'Patten', 'Michael'], [105, 'Burke', 'Brian'], [106, 'Sousa', 'Luis'], ]) self.assertEqual(lookup.VLOOKUP(102, range1, 2, False), 'Fortana')
def test_CONCAT(self): self.assertEqual( text.CONCAT("SPAM", " ", "SPAM"), 'SPAM SPAM') self.assertEqual( text.CONCAT( "SPAM", " ", func_xltypes.Array([[1, 2]]), 4 ), 'SPAM 124' )
def test_NPV(self): self.assertAlmostEqual( financial.NPV(0.06, [[1, 2, 3]]), 5.2422470) self.assertAlmostEqual( financial.NPV(0.06, 1, 2, 3), 5.2422470) self.assertAlmostEqual( financial.NPV(0.06, 1), 0.9433962) self.assertAlmostEqual( financial.NPV(0.1, -10000, 3000, 4200, 6800), 1188.44, 2) range1 = func_xltypes.Array([[8000, 9200, 10000, 12000, 14500]]) self.assertAlmostEqual( financial.NPV(0.08, range1) + -40000, 1922.06, 2) self.assertAlmostEqual( financial.NPV(0.08, range1, -9000) + -40000, -3749.47, 2)
def test_eval(self): node = self.create_node('A1:B2') res = node.eval(context('Sh1!C1', self.model)) exp = func_xltypes.Array([[0, 2], [1, 3]]) self.assertTrue((res == exp).all().all())
def test_MIN(self): self.assertEqual( statistics.MIN(func_xltypes.Array([[1, 2], [3, 4]])), 1 )
def test_COUNTIF_text(self): countRange = func_xltypes.Array(["a", "b", "A"]) condition = "=A" self.assertEqual(statistics.COUNTIF(countRange, condition), 2)
def test_COUNTIF(self): countRange = func_xltypes.Array([1, 2, 3, 4]) condition = ">2" self.assertEqual(statistics.COUNTIF(countRange, condition), 2)
def test_COUNTA(self): range0 = func_xltypes.Array([[1, 2], [3, 4]]) range1 = func_xltypes.Array([[2, 1], [3, '']]) self.assertEqual(statistics.COUNTA(range0), 4) self.assertEqual(statistics.COUNTA(range1), 3)
def test_flat(self): self.assertEqual( func_xltypes.Array([[1, 2, 3], [4, 5, 6]]).flat, [1, 2, 3, 4, 5, 6])
def test_VLOOOKUP_with_unknown_lookup_value(self): range1 = func_xltypes.Array([ [101, 'Davis', 'Sara'], ]) self.assertIsInstance(lookup.VLOOKUP(102, range1, 2, False), xlerrors.NaExcelError)
def test_cast_to_numbers(self): dt = datetime.datetime(1900, 1, 5) array = func_xltypes.Array([[1, None, dt], ['4.0', True, 6]]) nums = array.cast_to_numbers() self.assertIsInstance(nums[0][0], func_xltypes.Number) self.assertEqual(nums.flat, [1, 0.0, 5.0, 4.0, 1, 6])
def test_VLOOOKUP_with_range_lookup(self): with self.assertRaises(NotImplementedError): lookup.VLOOKUP(1, func_xltypes.Array([[]]), 2, True)
def test_length(self): self.assertEqual(xl.length([1, [2, 3], [4]]), 4) df = func_xltypes.Array([[1, 2], [3, 4]]) self.assertEqual(xl.length(df), 4)
def test_flatten(self): self.assertEqual(xl.flatten([1, [2, 3], [4]]), [1, 2, 3, 4]) df = func_xltypes.Array([[1, 2], [3, 4]]) self.assertEqual(xl.flatten(df), [1, 2, 3, 4]) self.assertEqual(xl.flatten([df]), [1, 2, 3, 4])
def test_cast_to_numbers_with_errors(self): array = func_xltypes.Array([[1, object()]]) nums = array.cast_to_numbers() self.assertEqual(nums.flat, [1, 0.0])
def test_cast_to_booleans(self): dt = datetime.datetime(1900, 1, 5) array = func_xltypes.Array([[1, None, dt], ['4.0', True, 6]]) bools = array.cast_to_booleans() self.assertIsInstance(bools[0][0], func_xltypes.Boolean) self.assertEqual(bools.flat, [True, False, True, True, True, True])