def test_COUNT(self):
range0 = xltypes.Array([[1, 2], [3, 4]])
range1 = 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_SUM_with_nonnumbers_in_range(self):
self.assertEqual(math.SUM(xltypes.Array([[1, 'bad'], [3, 4]])), 8)
self.assertEqual(
math.SUM(
xltypes.Array([[xltypes.Number(1),
xltypes.Text('N/A')],
[xltypes.Number(3),
xltypes.Number(4)]])), 8)
def test_XNPV(self):
range_00 = xltypes.Array(
[[-10000, 2750, 4250, 3250, 2750]])
range_01 = 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_cast_to_texts(self):
dt = datetime.datetime(1900, 1, 5)
array = xltypes.Array([[1, None, dt], ['4.0', True, 6]])
texts = array.cast_to_texts()
self.assertIsInstance(texts[0][0], xltypes.Text)
self.assertEqual(texts.flat,
['1', '', '1900-01-05 00:00:00', '4.0', 'True', '6'])
def test_VLOOOKUP_with_oversized_col_index_num(self):
# Excel Doc example.
range1 = xltypes.Array([
[101, 'Davis', 'Sara'],
])
self.assertIsInstance(lookup.VLOOKUP(102, range1, 4, False),
xlerrors.ValueExcelError)
def test_VLOOOKUP(self):
# Excel Doc example.
range1 = 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_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 = 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_cast_to_booleans(self):
dt = datetime.datetime(1900, 1, 5)
array = xltypes.Array([[1, None, dt], ['4.0', True, 6]])
bools = array.cast_to_booleans()
self.assertIsInstance(bools[0][0], xltypes.Boolean)
self.assertEqual(bools.flat, [True, False, True, True, True, True])
def test_cast_to_numbers_with_errors(self):
array = xltypes.Array([[1, object()]])
nums = array.cast_to_numbers()
self.assertEqual(nums.flat, [1, 0.0])
def test_cast_to_numbers(self):
dt = datetime.datetime(1900, 1, 5)
array = xltypes.Array([[1, None, dt], ['4.0', True, 6]])
nums = array.cast_to_numbers()
self.assertIsInstance(nums[0][0], xltypes.Number)
self.assertEqual(nums.flat, [1, 0.0, 5.0, 4.0, 1, 6])
def test_SUM(self):
self.assertEqual(math.SUM(xltypes.Array([[1, 2], [3, 4]])), 10)
self.assertEqual(math.SUM(1, 2, 3, 4.0), 10.0)
def test_SUMPRODUCT_with_empty_frist_range(self):
self.assertEqual(math.SUMPRODUCT(xltypes.Array([])), 0)
def test_VLOOOKUP_with_range_lookup(self):
with self.assertRaises(NotImplementedError):
lookup.VLOOKUP(1, xltypes.Array([[]]), 2, True)
def test_CONCAT(self):
self.assertEqual(
text.CONCAT("SPAM", " ", "SPAM"), 'SPAM SPAM')
self.assertEqual(
text.CONCAT("SPAM", " ", xltypes.Array([[1, 2]]), 4), 'SPAM 124')
def test_AVERAGE(self):
self.assertEqual(
statistics.AVERAGE(xltypes.Array([[1, 2], [3, 4]])), 2.5)
def test_MIN(self):
self.assertEqual(statistics.MIN(xltypes.Array([[1, 2], [3, 4]])), 1)
def test_COUNTA(self):
range0 = xltypes.Array([[1, 2], [3, 4]])
range1 = xltypes.Array([[2, 1], [3, '']])
self.assertEqual(statistics.COUNTA(range0), 4)
self.assertEqual(statistics.COUNTA(range1), 3)
def test_SUMPRODUCT(self):
range1 = xltypes.Array([[1], [10], [3]])
range2 = xltypes.Array([[3], [1], [2]])
self.assertEqual(math.SUMPRODUCT(range1, range2), 19)
def test_IRR_with_guess_non_null(self):
self.assertAlmostEqual(
financial.IRR(xltypes.Array([[-100, 39, 59, 55, 20]]), 1),
0.2809484)
def test_SUMPRODUCT_ranges_with_different_sizes(self):
range1 = xltypes.Array([[1], [10], [3]])
range2 = xltypes.Array([[3], [3], [1], [2]])
self.assertIsInstance(math.SUMPRODUCT(range1, range2),
xlerrors.ValueExcelError)
def test__init__withbad_data(self):
with self.assertRaises(xlerrors.ValueExcelError):
xltypes.Array('bad')
def test_SUMPRODUCT_ranges_with_errors(self):
range1 = xltypes.Array([[xlerrors.NumExcelError('err')], [10], [3]])
range2 = xltypes.Array([[3], [3], [1]])
self.assertIsInstance(math.SUMPRODUCT(range1, range2),
xlerrors.NaExcelError)
def test_flat(self):
self.assertEqual(
xltypes.Array([[1, 2, 3], [4, 5, 6]]).flat, [1, 2, 3, 4, 5, 6])
def test_VLOOOKUP_with_unknown_lookup_value(self):
range1 = xltypes.Array([
[101, 'Davis', 'Sara'],
])
self.assertIsInstance(lookup.VLOOKUP(102, range1, 2, False),
xlerrors.NaExcelError)
def test_IRR(self):
self.assertAlmostEqual(
financial.IRR(xltypes.Array([[-100, 39, 59, 55, 20]])), 0.2809484)
