def compare_result(expected, result):
expected = coerce_to_number(expected)
result = coerce_to_number(result)
if isinstance(expected, (int, float)) and isinstance(result, (int, float)):
return pytest.approx(expected) == result
else:
return expected == result
def compare_result(expected, result):
expected = coerce_to_number(expected)
result = coerce_to_number(result)
if isinstance(expected, (int, float)) and isinstance(result, (int, float)):
return pytest.approx(expected) == result
else:
return expected == result
def months_inc(start_date, months, eomonth=False):
if isinstance(start_date, bool) or isinstance(months, bool):
return VALUE_ERROR
start_date = coerce_to_number(start_date, convert_all=True)
months = coerce_to_number(months, convert_all=True)
if isinstance(start_date, str) or isinstance(months, str):
return VALUE_ERROR
if start_date < 0:
return NUM_ERROR
y, m, d = date_from_int(start_date)
if eomonth:
return date(y, m + months + 1, 1) - 1
else:
return date(y, m + months, d)
def roundup_unwrapped(number, num_digits):
# Excel reference: https://support.office.com/en-us/article/
# ROUNDUP-function-F8BC9B23-E795-47DB-8703-DB171D0C42A7
number, num_digits = coerce_to_number(number), coerce_to_number(num_digits)
if not is_number(number) or not is_number(num_digits):
return VALUE_ERROR
if isinstance(number, bool):
number = int(number)
quant = Decimal('1E{}{}'.format('+-'[num_digits >= 0], abs(num_digits)))
return float(Decimal(repr(number)).quantize(quant, rounding=ROUND_UP))
def test_coerce_to_number():
assert 1 == coerce_to_number(1)
assert 1.0 == coerce_to_number(1.0)
assert coerce_to_number(None) is None
assert 1 == coerce_to_number('1')
assert isinstance(coerce_to_number('1'), int)
assert 1 == coerce_to_number('1.')
assert isinstance(coerce_to_number('1.'), float)
assert 'xyzzy' == coerce_to_number('xyzzy')
with pytest.raises(ZeroDivisionError):
coerce_to_number(DIV0)
def mod(number, divisor):
# Excel reference: https://support.office.com/en-us/article/
# MOD-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3
if number in ERROR_CODES:
return number
if divisor in ERROR_CODES:
return divisor
number, divisor = coerce_to_number(number), coerce_to_number(divisor)
if divisor in (0, None):
return DIV0
if not is_number(number) or not is_number(divisor):
return VALUE_ERROR
return number % divisor
def isodd(value):
# Excel reference: https://support.office.com/en-us/article/
# is-functions-0f2d7971-6019-40a0-a171-f2d869135665
if isinstance(value, bool):
return VALUE_ERROR
value = coerce_to_number(value)
if isinstance(value, str):
return VALUE_ERROR
return bool(math.floor(abs(value)) % 2)
def _numerics(*args, no_bools=False):
# ignore non numeric cells
args = tuple(flatten(args, lambda x: coerce_to_number(x, raise_div0=False)))
error = next((x for x in args if x in ERROR_CODES), None)
if error is not None:
# return the first error in the list
return error
else:
if no_bools:
args = (a for a in args if not isinstance(a, bool))
return tuple(x for x in args if isinstance(x, (int, float)))
def factdouble(value):
# Excel reference: https://support.office.com/en-us/article/
# fact-function-ca8588c2-15f2-41c0-8e8c-c11bd471a4f3
if isinstance(value, bool):
return VALUE_ERROR
value = coerce_to_number(value, convert_all=True)
if isinstance(value, str):
return VALUE_ERROR
if value < 0:
return NUM_ERROR
return np.sum(np.prod(range(int(value), 0, -2), axis=0))
def mid(text, start_num, num_chars):
# Excel reference: https://support.office.com/en-us/article/
# MID-MIDB-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028
if text in ERROR_CODES:
return text
if start_num in ERROR_CODES:
return start_num
if num_chars in ERROR_CODES:
return num_chars
start_num = coerce_to_number(start_num)
num_chars = coerce_to_number(num_chars)
if not is_number(start_num) or not is_number(num_chars):
return VALUE_ERROR
if start_num < 1 or num_chars < 0:
return VALUE_ERROR
start_num = int(start_num) - 1
return str(text)[start_num:start_num + int(num_chars)]
def xround(number, num_digits=0):
# Excel reference: https://support.office.com/en-us/article/
# ROUND-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c
if number in ERROR_CODES:
return number
if num_digits in ERROR_CODES:
return num_digits
number, num_digits = coerce_to_number(number), coerce_to_number(num_digits)
if not is_number(number) or not is_number(num_digits):
return VALUE_ERROR
num_digits = int(num_digits)
if num_digits >= 0: # round to the right side of the point
return float(
Decimal(repr(number)).quantize(Decimal(repr(pow(10, -num_digits))),
rounding=ROUND_HALF_UP))
# see https://docs.python.org/2/library/functions.html#round
# and https://gist.github.com/ejamesc/cedc886c5f36e2d075c5
else:
return round(number, num_digits)
def small(array, k):
# Excel reference: https://support.office.com/en-us/article/
# small-function-17da8222-7c82-42b2-961b-14c45384df07
data = _numerics(array, to_number=coerce_to_number)
if isinstance(data, str):
return data
k = coerce_to_number(k)
if isinstance(k, str):
return VALUE_ERROR
if not data or k is None or k < 1 or k > len(data):
return NUM_ERROR
k = math.ceil(k)
return nsmallest(k, data)[-1]
def large(array, k):
# Excel reference: https://support.office.com/en-us/article/
# large-function-3af0af19-1190-42bb-bb8b-01672ec00a64
data = _numerics(array, to_number=coerce_to_number)
if isinstance(data, str):
return data
k = coerce_to_number(k)
if isinstance(k, str):
return VALUE_ERROR
if not data or k is None or k < 1 or k > len(data):
return NUM_ERROR
k = math.ceil(k)
return nlargest(k, data)[-1]
def func_subtotal(self):
# Excel reference: https://support.microsoft.com/en-us/office/
# SUBTOTAL-function-7B027003-F060-4ADE-9040-E478765B9939
# Note: This does not implement skipping hidden rows.
func_num = coerce_to_number(self.children[0].emit)
if func_num not in self.SUBTOTAL_FUNCS:
if func_num - 100 in self.SUBTOTAL_FUNCS:
func_num -= 100
else:
raise ValueError(f"Unknown SUBTOTAL function number: {func_num}")
func = self.SUBTOTAL_FUNCS[func_num]
to_emit = self.comma_join_emit(fmt_str="{}", to_emit=self.children[1:])
return f'{func}({to_emit})'
def test_excel_operator_operand_fixup(left_op, op, right_op, expected):
error_messages = []
def capture_error_state(is_exception, msg):
error_messages.append((is_exception, msg))
assert expected == build_operator_operand_fixup(capture_error_state)(
left_op, op, right_op)
if expected == VALUE_ERROR:
if expected == VALUE_ERROR and VALUE_ERROR not in (left_op, right_op):
assert [(True, 'Values: {} {} {}'.format(
coerce_to_number(left_op, convert_all=True), op,
right_op))] == error_messages
elif expected == DIV0 and DIV0 not in (left_op, right_op):
assert [(True, f'Values: {left_op} {op} {right_op}')] == error_messages
def test_excel_operator_operand_fixup(left_op, op, right_op, expected):
error_messages = []
def capture_error_state(is_exception, msg):
error_messages.append((is_exception, msg))
assert expected == build_operator_operand_fixup(
capture_error_state)(left_op, op, right_op)
if expected == VALUE_ERROR:
if expected == VALUE_ERROR and VALUE_ERROR not in (left_op, right_op):
assert [(True, 'Values: {} {} {}'.format(
coerce_to_number(left_op, convert_all=True), op, right_op))
] == error_messages
elif expected == DIV0 and DIV0 not in (left_op, right_op):
assert [(True, 'Values: {} {} {}'.format(left_op, op, right_op))
] == error_messages
def right(text, num_chars=1):
# Excel reference: https://support.office.com/en-us/article/
# RIGHT-RIGHTB-functions-240267EE-9AFA-4639-A02B-F19E1786CF2F
if text in ERROR_CODES:
return text
if num_chars in ERROR_CODES:
return num_chars
num_chars = coerce_to_number(num_chars)
if not is_number(num_chars) or num_chars < 0:
return VALUE_ERROR
if num_chars == 0:
return ''
else:
return str(text)[-int(num_chars):]
def func_subtotal(self):
# Excel reference: https://support.office.com/en-us/article/
# SUBTOTAL-function-7B027003-F060-4ADE-9040-E478765B9939
# Note: This does not implement skipping hidden rows.
func_num = coerce_to_number(self.children[0].emit)
if func_num not in self.SUBTOTAL_FUNCS:
if func_num - 100 in self.SUBTOTAL_FUNCS:
func_num -= 100
else:
raise ValueError(
"Unknown SUBTOTAL function number: {}".format(func_num))
func = self.SUBTOTAL_FUNCS[func_num]
return "{}({})".format(
func, self.comma_join_emit(fmt_str="{}", to_emit=self.children[1:]))
def wrapper(*args):
new_args = tuple(coerce_to_number(a, convert_all=True)
if i in param_indices else a
for i, a in enumerate(args))
error = next((a for i, a in enumerate(new_args)
if i in param_indices and a in ERROR_CODES), None)
if error:
return error
if any(i in param_indices and not is_number(a)
for i, a in enumerate(new_args)):
return VALUE_ERROR
try:
return f(*new_args)
except ValueError as exc:
if "math domain error" in str(exc):
return NUM_ERROR
raise # pragma: no cover
def wrapper(*args):
new_args = tuple(coerce_to_number(a, convert_all=True)
if i in param_indices else a
for i, a in enumerate(args))
error = next((a for i, a in enumerate(new_args)
if i in param_indices and a in ERROR_CODES), None)
if error:
return error
if any(i in param_indices and not is_number(a)
for i, a in enumerate(new_args)):
return VALUE_ERROR
try:
return f(*new_args)
except ValueError as exc:
if "math domain error" in str(exc):
return NUM_ERROR
raise # pragma: no cover
def test_coerce_to_number(value, expected, expected_type, convert_all):
result = coerce_to_number(value, convert_all=convert_all)
assert result == expected
assert isinstance(result, expected_type)
def format_value(self, data) -> str:
tokenized_formats = self.tokenized_formats
if isinstance(tokenized_formats, str):
return tokenized_formats
# check for only one string replace field, and in the last field if present
string_replace_token_count = sum(int(self.TokenType.REPLACE in tokens.types)
for tokens in tokenized_formats)
if string_replace_token_count and (
string_replace_token_count > 1 or
self.TokenType.REPLACE not in tokenized_formats[-1].types):
return VALUE_ERROR
# (attempt to) convert the data into a date (serial number) or number
convertor = DateTimeFormatter.new(data)
if convertor is not None:
# The data was a convertable date
data = convertor.serial_number
elif data is None:
data = 0
else:
data = coerce_to_number(data)
# Process strings first
if isinstance(data, str):
# '@' is not required in the fourth field to use the field
if string_replace_token_count or len(tokenized_formats) == 4:
tokens, token_types = tokenized_formats[-1][:2]
return ''.join(data if t.type == self.TokenType.REPLACE else t.token
for t in tokens)
else:
# if no specific string formatter, then pass through
return data
if not tokenized_formats:
return '-' if data < 0 else ''
if self.TokenType.REPLACE in tokenized_formats[-1].types:
# remove the string formatter on the end if present
tokenized_formats = tokenized_formats[:-1]
if data == 0 and len(tokenized_formats) > 2:
tokenized_format = tokenized_formats[2]
elif data < 0 and len(tokenized_formats) > 1:
tokenized_format = tokenized_formats[1]
else:
tokenized_format = tokenized_formats[0]
if data < 0 and self.TokenType.DATETIME not in tokenized_format.types:
data = -data
if len(tokenized_formats) < 2:
amended_tokens = (
self.Token('-', self.TokenType.STRING, -1), *tokenized_format.tokens)
tokenized_format = self.Tokenized(
tokens=amended_tokens,
types=tokenized_format.types,
decimal=tokenized_format.decimal,
thousands=tokenized_format.thousands,
percents=tokenized_format.percents,
)
format_tokens, format_types = tokenized_format[:2]
if self.TokenType.DATETIME in format_types:
if convertor is None:
convertor = DateTimeFormatter(data)
tokens = tuple(token.token if token.type == self.TokenType.STRING
else convertor.format(token.token)
for token in format_tokens)
if any(t in ERROR_CODES for t in tokens):
return VALUE_ERROR
else:
return ''.join(tokens)
elif self.TokenType.NUMBER in format_types:
return self._number_converter(data, tokenized_format)
else:
# return the format directly
return ''.join(t.token for t in tokenized_format.tokens)
def test_coerce_to_number(value, expected, expected_type, convert_all):
result = coerce_to_number(value, convert_all=convert_all)
assert result == expected
assert isinstance(result, expected_type)
def excel_value(formula, value):
"""A openpyxl sheet does not have values for formula cells"""
return None if formula or value is None else coerce_to_number(
value, convert_all=True)