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