use crate::cell::cell::CellValue;
use std::collections::BTreeMap;
pub const SUPPORTED_FUNCTIONS: &[&str] = &[
"ABS",
"AND",
"AVERAGE",
"CHOOSE",
"CONCAT",
"CONCATENATE",
"COUNT",
"COUNTA",
"COUNTIF",
"COUNTIFS",
"FALSE",
"IF",
"IFERROR",
"INDEX",
"INT",
"ISBLANK",
"ISERROR",
"ISNUMBER",
"ISTEXT",
"LEFT",
"LEN",
"LOWER",
"MATCH",
"MAX",
"MIN",
"MOD",
"NOT",
"OR",
"POWER",
"PRODUCT",
"RIGHT",
"ROUND",
"ROUNDDOWN",
"ROUNDUP",
"SIGN",
"SQRT",
"SUM",
"SUMIF",
"SUMIFS",
"TRIM",
"TRUE",
"UPPER",
"VLOOKUP",
"XLOOKUP",
];
pub fn supports(name: &str) -> bool {
let wanted = name.trim().to_uppercase();
SUPPORTED_FUNCTIONS.contains(&wanted.as_str())
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct Unresolved {
pub sheet: String,
pub coordinate: String,
pub formula: String,
pub reason: String,
}
#[derive(Debug, Clone, Default)]
pub struct Recalculation {
pub computed: BTreeMap<String, BTreeMap<String, CellValue>>,
pub unresolved: Vec<Unresolved>,
}
impl Recalculation {
pub fn computed_count(&self) -> usize {
self.computed.values().map(BTreeMap::len).sum()
}
pub fn is_complete(&self) -> bool {
self.unresolved.is_empty()
}
pub fn reasons(&self) -> Vec<(String, usize)> {
let mut counts: BTreeMap<&str, usize> = BTreeMap::new();
for entry in &self.unresolved {
*counts.entry(entry.reason.as_str()).or_default() += 1;
}
counts
.into_iter()
.map(|(reason, count)| (reason.to_string(), count))
.collect()
}
}
enum Resolved {
Single(CellValue),
Range {
values: Vec<CellValue>,
cols: usize,
rows: usize,
},
}
pub trait ValueSource {
fn cell(&self, sheet: &str, coordinate: &str) -> CellValue;
fn max_row(&self, _sheet: &str) -> Option<u32> {
None
}
}
pub fn evaluate(
formula: &str,
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<CellValue, String> {
let expression = formula.trim().trim_start_matches('=');
let tokens = tokenize(expression).map_err(|reason| format!("cannot parse: {reason}"))?;
let mut parser = Parser {
tokens,
position: 0,
};
let node = parser
.parse_expression()
.ok_or_else(|| "cannot parse: unexpected end of formula".to_string())?;
if !parser.at_end() {
return Err("cannot parse: trailing text after the formula".to_string());
}
eval(&node, sheet, source)
}
#[derive(Debug, Clone, PartialEq)]
enum Token {
Number(f64),
Text(String),
Word(String),
Name(String),
Operator(String),
OpenParen,
CloseParen,
Comma,
Colon,
Bang,
}
fn tokenize(input: &str) -> std::result::Result<Vec<Token>, String> {
let chars: Vec<char> = input.chars().collect();
let mut tokens = Vec::new();
let mut at = 0usize;
while at < chars.len() {
let ch = chars[at];
match ch {
' ' | '\t' | '\n' | '\r' => at += 1,
'(' => {
tokens.push(Token::OpenParen);
at += 1;
}
')' => {
tokens.push(Token::CloseParen);
at += 1;
}
',' | ';' => {
tokens.push(Token::Comma);
at += 1;
}
':' => {
tokens.push(Token::Colon);
at += 1;
}
'!' => {
tokens.push(Token::Bang);
at += 1;
}
'+' | '-' | '*' | '/' | '^' | '&' | '=' | '<' | '>' | '%' => {
let start = at;
at += 1;
let is_pair = match ch {
'<' | '>' => matches!(chars.get(at), Some('>') | Some('=')),
_ => false,
};
if is_pair {
at += 1;
}
tokens.push(Token::Operator(chars[start..at].iter().collect()));
}
'"' => {
let mut text = String::new();
at += 1;
loop {
match chars.get(at) {
None => return Err("unterminated text".to_string()),
Some('"') => {
if chars.get(at + 1) == Some(&'"') {
text.push('"');
at += 2;
} else {
at += 1;
break;
}
}
Some(other) => {
text.push(*other);
at += 1;
}
}
}
tokens.push(Token::Text(text));
}
'\'' => {
let mut sheet_name = String::new();
at += 1;
loop {
match chars.get(at) {
None => return Err("unterminated sheet name in formula".to_string()),
Some('\'') => {
if chars.get(at + 1) == Some(&'\'') {
sheet_name.push('\'');
at += 2;
} else {
at += 1;
break;
}
}
Some(other) => {
sheet_name.push(*other);
at += 1;
}
}
}
tokens.push(Token::Name(sheet_name));
}
'#' => {
let start = at;
while at < chars.len() && (chars[at].is_ascii_alphanumeric() || chars[at] == '!') {
at += 1;
}
let word: String = chars[start..at].iter().collect();
if ERROR_CODES.contains(&word.to_uppercase().as_str()) {
tokens.push(Token::Word(word));
} else {
return Err(format!("unknown error literal {word:?}"));
}
}
_ if ch.is_ascii_digit()
|| (ch == '.' && chars.get(at + 1).is_some_and(|c| c.is_ascii_digit())) =>
{
let start = at;
while at < chars.len() && (chars[at].is_ascii_digit() || chars[at] == '.') {
at += 1;
}
if matches!(chars.get(at), Some('E') | Some('e')) {
let mut ahead = at + 1;
if matches!(chars.get(ahead), Some('+') | Some('-')) {
ahead += 1;
}
if chars.get(ahead).is_some_and(char::is_ascii_digit) {
at = ahead;
while at < chars.len() && chars[at].is_ascii_digit() {
at += 1;
}
}
}
let text: String = chars[start..at].iter().collect();
let value = text
.parse::<f64>()
.map_err(|_| format!("cannot read the number {text:?}"))?;
tokens.push(Token::Number(value));
}
_ if is_name_start(ch) => {
let start = at;
while at < chars.len() && is_name_part(chars[at]) {
at += 1;
}
let word: String = chars[start..at].iter().collect();
while at < chars.len() && chars[at] == '$' {
at += 1;
}
let upper = word.to_uppercase();
if matches!(upper.as_str(), "TRUE" | "FALSE") {
tokens.push(Token::Word(upper));
} else if crate::styles::named_style::builtin(&word).is_some()
&& !looks_like_reference(&word)
{
tokens.push(Token::Name(word));
} else {
tokens.push(Token::Name(word));
}
}
_ => return Err(format!("unexpected character {ch:?}")),
}
}
Ok(tokens)
}
const ERROR_CODES: [&str; 7] = [
"#NULL!", "#DIV/0!", "#VALUE!", "#REF!", "#NAME?", "#NUM!", "#N/A",
];
fn is_name_start(ch: char) -> bool {
ch.is_alphabetic() || ch == '_' || ch == '\\' || ch == '$'
}
fn is_name_part(ch: char) -> bool {
ch.is_alphanumeric() || ch == '_' || ch == '.' || ch == '\\' || ch == '$'
}
fn looks_like_column_reference(word: &str) -> bool {
let letters: String = word.chars().filter(|c| *c != '$').collect();
if letters.is_empty() || letters.len() > 3 {
return false;
}
letters.chars().all(|c| c.is_ascii_alphabetic())
}
fn looks_like_reference(word: &str) -> bool {
let letters: String = word.chars().filter(|c| *c != '$').collect();
let mut chars = letters.chars();
let Some(first) = chars.next() else {
return false;
};
if !first.is_ascii_alphabetic() {
return false;
}
if chars.clone().all(|c| c.is_ascii_alphabetic()) {
return false;
}
chars.all(|c| c.is_ascii_alphanumeric())
}
#[derive(Debug, Clone, PartialEq)]
enum Node {
Literal(CellValue),
Reference(Option<String>, String),
Function(String, Vec<Node>),
Unary(String, Box<Node>),
Binary(String, Box<Node>, Box<Node>),
}
struct Parser {
tokens: Vec<Token>,
position: usize,
}
impl Parser {
fn at_end(&self) -> bool {
self.position >= self.tokens.len()
}
fn peek(&self) -> Option<&Token> {
self.tokens.get(self.position)
}
fn take(&mut self) -> Option<Token> {
let token = self.tokens.get(self.position).cloned();
if token.is_some() {
self.position += 1;
}
token
}
fn eat_operator(&mut self, wanted: &str) -> bool {
if matches!(self.peek(), Some(Token::Operator(o)) if o == wanted) {
self.position += 1;
true
} else {
false
}
}
fn parse_expression(&mut self) -> Option<Node> {
let mut left = self.parse_concat()?;
loop {
let operator = match self.peek() {
Some(Token::Operator(o))
if matches!(o.as_str(), "=" | "<>" | "<" | "<=" | ">" | ">=") =>
{
o.clone()
}
_ => return Some(left),
};
self.position += 1;
let right = self.parse_concat()?;
left = Node::Binary(operator, Box::new(left), Box::new(right));
}
}
fn parse_concat(&mut self) -> Option<Node> {
let mut left = self.parse_additive()?;
while self.eat_operator("&") {
let right = self.parse_additive()?;
left = Node::Binary("&".to_string(), Box::new(left), Box::new(right));
}
Some(left)
}
fn parse_additive(&mut self) -> Option<Node> {
let mut left = self.parse_multiplicative()?;
loop {
let operator = match self.peek() {
Some(Token::Operator(o)) if o == "+" || o == "-" => o.clone(),
_ => return Some(left),
};
self.position += 1;
let right = self.parse_multiplicative()?;
left = Node::Binary(operator, Box::new(left), Box::new(right));
}
}
fn parse_multiplicative(&mut self) -> Option<Node> {
let mut left = self.parse_exponent()?;
loop {
let operator = match self.peek() {
Some(Token::Operator(o)) if o == "*" || o == "/" => o.clone(),
_ => return Some(left),
};
self.position += 1;
let right = self.parse_exponent()?;
left = Node::Binary(operator, Box::new(left), Box::new(right));
}
}
fn parse_exponent(&mut self) -> Option<Node> {
let mut left = self.parse_unary()?;
while self.eat_operator("^") {
let right = self.parse_unary()?;
left = Node::Binary("^".to_string(), Box::new(left), Box::new(right));
}
Some(left)
}
fn parse_unary(&mut self) -> Option<Node> {
if self.eat_operator("-") {
let inner = self.parse_unary()?;
return Some(Node::Unary("-".to_string(), Box::new(inner)));
}
if self.eat_operator("+") {
return self.parse_unary();
}
self.parse_postfix()
}
fn parse_postfix(&mut self) -> Option<Node> {
let mut node = self.parse_primary()?;
while self.eat_operator("%") {
node = Node::Unary("%".to_string(), Box::new(node));
}
Some(node)
}
fn parse_primary(&mut self) -> Option<Node> {
match self.peek().cloned() {
Some(Token::Number(value)) => {
self.position += 1;
Some(Node::Literal(CellValue::Number(value)))
}
Some(Token::Text(text)) => {
self.position += 1;
Some(Node::Literal(CellValue::Text(text)))
}
Some(Token::Word(word)) => {
self.position += 1;
match word.as_str() {
"TRUE" => Some(Node::Literal(CellValue::Bool(true))),
"FALSE" => Some(Node::Literal(CellValue::Bool(false))),
other => Some(Node::Literal(CellValue::Error(other.to_string()))),
}
}
Some(Token::OpenParen) => {
self.position += 1;
let inner = self.parse_expression()?;
if !matches!(self.take(), Some(Token::CloseParen)) {
return None;
}
Some(inner)
}
Some(Token::Name(name)) => {
self.position += 1;
self.parse_name(name)
}
_ => None,
}
}
fn parse_name(&mut self, name: String) -> Option<Node> {
if matches!(self.peek(), Some(Token::OpenParen)) {
self.position += 1;
let mut arguments = Vec::new();
if !matches!(self.peek(), Some(Token::CloseParen)) {
loop {
arguments.push(self.parse_expression()?);
match self.peek() {
Some(Token::Comma) => {
self.position += 1;
}
_ => break,
}
}
}
if !matches!(self.take(), Some(Token::CloseParen)) {
return None;
}
return Some(Node::Function(name.to_uppercase(), arguments));
}
let mut sheet = None;
let mut body = name;
while matches!(self.peek(), Some(Token::Bang)) {
self.position += 1;
let next = match self.take() {
Some(Token::Name(second)) => second,
Some(Token::Word(second)) => second,
_ => return None,
};
sheet = Some(match sheet {
Some(previous) => format!("{previous}!{body}"),
None => body,
});
body = next;
}
if matches!(self.peek(), Some(Token::Colon)) {
self.position += 1;
let second = match self.take() {
Some(Token::Name(second)) => second,
_ => return None,
};
let mut range = format!("{body}:{second}");
if range.contains(':')
&& range
.split(':')
.any(|side| !looks_like_reference(side) && !looks_like_column_reference(side))
{
range.push(' ');
}
return Some(Node::Reference(sheet, range));
}
Some(Node::Reference(sheet, body))
}
}
type Reason = String;
fn error_value(code: &str) -> CellValue {
CellValue::Error(code.to_string())
}
fn readable(value: CellValue) -> CellValue {
match value {
CellValue::Formula(_) => CellValue::None,
other => other,
}
}
fn is_error(value: &CellValue) -> bool {
matches!(value, CellValue::Error(_))
}
fn eval(
node: &Node,
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<CellValue, Reason> {
match node {
Node::Literal(value) => Ok(value.clone()),
Node::Reference(reference_sheet, body) => {
let resolved = resolve(reference_sheet.as_deref().unwrap_or(sheet), body, source)?;
match resolved {
Resolved::Single(value) => Ok(readable(value)),
Resolved::Range { mut values, .. } => Ok(if values.is_empty() {
CellValue::None
} else {
values.remove(0)
}),
}
}
Node::Unary(operator, inner) => {
let value = eval(inner, sheet, source)?;
unary(operator, value)
}
Node::Binary(operator, left, right) => {
let a = eval(left, sheet, source)?;
let b = eval(right, sheet, source)?;
binary(operator, a, b)
}
Node::Function(name, arguments) => {
if !supports(name) {
return Err(format!("unsupported function {name}()"));
}
call_function(name, arguments, sheet, source)
}
}
}
fn resolve(
sheet: &str,
body: &str,
source: &dyn ValueSource,
) -> std::result::Result<Resolved, Reason> {
let body = body.trim();
let (start, end) = match body.split_once(':') {
Some((start, end)) => (start, Some(end)),
None => (body, None),
};
match end {
None => {
let start = normalise_reference(start)?;
Ok(Resolved::Single(readable(source.cell(sheet, &start))))
}
Some(end) => {
let start_clean: String = start.chars().filter(|c| *c != '$').collect();
let end_clean: String = end.chars().filter(|c| *c != '$').collect();
let (start_coord, end_coord) = if looks_like_column_reference(&start_clean)
&& looks_like_column_reference(&end_clean)
{
let max = source.max_row(sheet).unwrap_or(100).max(1);
(
format!("{}1", start_clean.to_uppercase()),
format!("{}{max}", end_clean.to_uppercase()),
)
} else {
(normalise_reference(start)?, normalise_reference(end)?)
};
let range = crate::worksheet::cell_range::CellRange::parse(&format!(
"{start_coord}:{end_coord}"
))
.map_err(|error| format!("cannot read the range {body:?}: {error}"))?;
let (min_col, min_row, max_col, max_row) = range.bounds();
let cols = (max_col - min_col + 1) as usize;
let rows = (max_row - min_row + 1) as usize;
let mut values = Vec::new();
for coordinate in range.cells().map_err(|error| error.to_string())? {
values.push(readable(source.cell(sheet, &coordinate)));
}
Ok(Resolved::Range { values, cols, rows })
}
}
}
fn normalise_reference(text: &str) -> std::result::Result<String, Reason> {
let cleaned: String = text.chars().filter(|c| *c != '$').collect();
if !looks_like_reference(&cleaned) {
return Err(format!("{text:?} is not a cell reference"));
}
Ok(cleaned.to_uppercase())
}
fn unary(operator: &str, value: CellValue) -> std::result::Result<CellValue, Reason> {
if is_error(&value) {
return Ok(value);
}
match operator {
"%" => to_number(&value).map(|n| CellValue::Number(n / 100.0)),
"-" => to_number(&value).map(|n| CellValue::Number(-n)),
other => Err(format!("unknown operator {other}")),
}
}
fn binary(
operator: &str,
left: CellValue,
right: CellValue,
) -> std::result::Result<CellValue, Reason> {
if let CellValue::Error(code) = &left {
return Ok(error_value(code));
}
if let CellValue::Error(code) = &right {
return Ok(error_value(code));
}
match operator {
"&" => {
Ok(CellValue::Text(format!(
"{}{}",
as_display(&left),
as_display(&right)
)))
}
"=" | "<>" | "<" | "<=" | ">" | ">=" => {
let ordering = compare(&left, &right);
let holds = match operator {
"=" => ordering == std::cmp::Ordering::Equal,
"<>" => ordering != std::cmp::Ordering::Equal,
"<" => ordering == std::cmp::Ordering::Less,
"<=" => ordering != std::cmp::Ordering::Greater,
">" => ordering == std::cmp::Ordering::Greater,
_ => ordering != std::cmp::Ordering::Less,
};
Ok(CellValue::Bool(holds))
}
_ => {
let a = to_number(&left)?;
let b = to_number(&right)?;
let value = match operator {
"+" => a + b,
"-" => a - b,
"*" => a * b,
"/" => {
if b == 0.0 {
return Ok(error_value("#DIV/0!"));
}
a / b
}
"^" => {
let result = a.powf(b);
if result.is_nan() {
return Ok(error_value("#NUM!"));
}
result
}
other => return Err(format!("unknown operator {other}")),
};
if value.is_infinite() {
return Ok(error_value("#NUM!"));
}
Ok(CellValue::Number(value))
}
}
}
fn rank(value: &CellValue) -> u8 {
match value {
CellValue::None => 0,
CellValue::Number(_) => 1,
CellValue::Text(_) => 2,
CellValue::Bool(_) => 3,
_ => 4,
}
}
fn compare(left: &CellValue, right: &CellValue) -> std::cmp::Ordering {
let left = coerce_for_comparison(left);
let right = coerce_for_comparison(right);
match (&left, &right) {
(CellValue::Number(a), CellValue::Number(b)) => {
a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal)
}
(CellValue::Text(a), CellValue::Text(b)) => {
a.to_lowercase().cmp(&b.to_lowercase())
}
(CellValue::Bool(a), CellValue::Bool(b)) => a.cmp(b),
_ => rank(&left).cmp(&rank(&right)),
}
}
fn coerce_for_comparison(value: &CellValue) -> CellValue {
match value {
CellValue::None => CellValue::Number(0.0),
other => other.clone(),
}
}
fn to_number(value: &CellValue) -> std::result::Result<f64, Reason> {
match value {
CellValue::Number(v) => Ok(*v),
CellValue::Bool(v) => Ok(if *v { 1.0 } else { 0.0 }),
CellValue::None => Ok(0.0),
CellValue::Text(text) => {
let trimmed = text.trim();
if trimmed.is_empty() {
return Ok(0.0);
}
let cleaned = trimmed.replace(['$', ','], "");
cleaned
.parse::<f64>()
.map_err(|_| format!("#VALUE! cannot read {text:?} as a number"))
}
CellValue::DateTime(moment) => Ok(crate::date_time::to_excel(
*moment,
crate::date_time::BaseDate::Windows1900,
)),
CellValue::Date(day) => Ok(crate::date_time::date_to_excel(
*day,
crate::date_time::BaseDate::Windows1900,
)),
CellValue::Time(clock) => Ok(crate::date_time::time_to_days(*clock)),
CellValue::Duration(span) => Ok(crate::date_time::timedelta_to_days(*span)),
CellValue::Error(code) => Err(code.clone()),
CellValue::Formula(_) => Err("#VALUE! a formula used as a value".to_string()),
}
}
fn to_number_value(value: &CellValue) -> CellValue {
match to_number(value) {
Ok(number) => CellValue::Number(number),
Err(code) => error_value(code.trim_start_matches("#VALUE! ")),
}
}
fn as_display(value: &CellValue) -> String {
match value {
CellValue::Text(text) => text.clone(),
CellValue::Bool(v) => if *v { "TRUE" } else { "FALSE" }.to_string(),
CellValue::None => String::new(),
CellValue::Number(v) => format_number(*v),
CellValue::DateTime(moment) => moment.format("%Y-%m-%d %H:%M:%S").to_string(),
other => format!("{other:?}"),
}
}
fn format_number(value: f64) -> String {
if value == value.trunc() && value.abs() < 1e15 {
return format!("{}", value as i64);
}
let mut text = format!("{value}");
if text.contains('e') {
text = format!("{value:.10}");
}
text
}
fn argument_values(
arguments: &[Node],
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<Vec<CellValue>, Reason> {
let mut values = Vec::new();
for argument in arguments {
match argument {
Node::Reference(reference_sheet, body) => {
match resolve(reference_sheet.as_deref().unwrap_or(sheet), body, source)? {
Resolved::Single(value) => values.push(value),
Resolved::Range { values: range, .. } => values.extend(range),
}
}
other => values.push(eval(other, sheet, source)?),
}
}
Ok(values)
}
fn aggregate_numbers(
arguments: &[Node],
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<Vec<f64>, Reason> {
let mut numbers = Vec::new();
for argument in arguments {
match argument {
Node::Reference(reference_sheet, body) => {
let sheet = reference_sheet.as_deref().unwrap_or(sheet);
match resolve(sheet, body, source)? {
Resolved::Single(value) => {
if let CellValue::Number(number) = value {
numbers.push(number);
}
}
Resolved::Range { values, .. } => {
for value in values {
if let CellValue::Number(number) = value {
numbers.push(number);
}
}
}
}
}
other => {
let value = eval(other, sheet, source)?;
numbers.push(to_number(&value)?);
}
}
}
Ok(numbers)
}
fn call_function(
name: &str,
arguments: &[Node],
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<CellValue, Reason> {
match name {
"IF" => return call_if(arguments, sheet, source),
"IFERROR" => return call_iferror(arguments, sheet, source),
"AND" | "OR" => return call_and_or(name, arguments, sheet, source),
"CHOOSE" => return call_choose(arguments, sheet, source),
"INDEX" => return call_index(arguments, sheet, source),
"MATCH" => return call_match(arguments, sheet, source),
"VLOOKUP" => return call_vlookup(arguments, sheet, source),
"XLOOKUP" => return call_xlookup(arguments, sheet, source),
"SUMIF" => return call_sumif(arguments, sheet, source),
"SUMIFS" => return call_sumifs(arguments, sheet, source),
"COUNTIF" => return call_countif(arguments, sheet, source),
"COUNTIFS" => return call_countifs(arguments, sheet, source),
_ => {}
}
match name {
"SUM" => {
let numbers = aggregate_numbers(arguments, sheet, source)?;
Ok(CellValue::Number(numbers.iter().sum()))
}
"PRODUCT" => {
let numbers = aggregate_numbers(arguments, sheet, source)?;
Ok(CellValue::Number(numbers.iter().product()))
}
"AVERAGE" => {
let numbers = aggregate_numbers(arguments, sheet, source)?;
if numbers.is_empty() {
return Ok(error_value("#DIV/0!"));
}
Ok(CellValue::Number(
numbers.iter().sum::<f64>() / numbers.len() as f64,
))
}
"MIN" | "MAX" => {
let numbers = aggregate_numbers(arguments, sheet, source)?;
if numbers.is_empty() {
return Ok(CellValue::Number(0.0));
}
let picked = if name == "MIN" {
numbers.iter().copied().fold(f64::INFINITY, f64::min)
} else {
numbers.iter().copied().fold(f64::NEG_INFINITY, f64::max)
};
Ok(CellValue::Number(picked))
}
"COUNT" => {
let values = argument_values(arguments, sheet, source)?;
Ok(CellValue::Number(
values
.iter()
.filter(|value| {
matches!(
value,
CellValue::Number(_) | CellValue::Bool(_) | CellValue::DateTime(_)
)
})
.count() as f64,
))
}
"COUNTA" => {
let values = argument_values(arguments, sheet, source)?;
Ok(CellValue::Number(
values.iter().filter(|value| !value.is_empty()).count() as f64,
))
}
"ABS" | "INT" | "SIGN" | "SQRT" | "ROUND" | "ROUNDUP" | "ROUNDDOWN" | "MOD" | "POWER" => {
let values = argument_values(arguments, sheet, source)?;
if values.is_empty() {
return Err(format!("{name}() needs an argument"));
}
let first = to_number_value(&values[0]);
if is_error(&first) {
return Ok(first);
}
let number = match first {
CellValue::Number(n) => n,
_ => 0.0,
};
match name {
"ABS" => Ok(CellValue::Number(number.abs())),
"INT" => Ok(CellValue::Number(number.floor())),
"SIGN" => Ok(CellValue::Number(number.signum())),
"SQRT" => {
if number < 0.0 {
return Ok(error_value("#NUM!"));
}
Ok(CellValue::Number(number.sqrt()))
}
"ROUND" | "ROUNDUP" | "ROUNDDOWN" => {
let places = match values.get(1) {
Some(value) => to_number(value)?,
None => 0.0,
};
if is_error(&to_number_value(values.get(1).unwrap_or(&CellValue::None))) {
return Ok(to_number_value(values.get(1).unwrap_or(&CellValue::None)));
}
Ok(CellValue::Number(round(number, places, name)))
}
"MOD" => {
let divisor = match values.get(1) {
Some(value) => to_number(value)?,
None => return Err("MOD() needs a divisor".to_string()),
};
if divisor == 0.0 {
return Ok(error_value("#DIV/0!"));
}
Ok(CellValue::Number(
number - divisor * (number / divisor).floor(),
))
}
"POWER" => {
let exponent = match values.get(1) {
Some(value) => to_number(value)?,
None => return Err("POWER() needs an exponent".to_string()),
};
let result = number.powf(exponent);
if result.is_nan() {
return Ok(error_value("#NUM!"));
}
Ok(CellValue::Number(result))
}
_ => unreachable!("the match above is exhaustive"),
}
}
"CONCAT" | "CONCATENATE" => {
let values = argument_values(arguments, sheet, source)?;
if let Some(bad) = values.iter().find(|value| is_error(value)) {
return Ok(bad.clone());
}
let mut text = String::new();
for value in values {
match value {
CellValue::None => {}
other => text.push_str(&as_display(&other)),
}
}
Ok(CellValue::Text(text))
}
"LEFT" | "RIGHT" => {
let values = argument_values(arguments, sheet, source)?;
let text = as_display(values.first().unwrap_or(&CellValue::None));
let count = match values.get(1) {
Some(value) => to_number(value)?.max(0.0) as usize,
None => 1,
};
let characters: Vec<char> = text.chars().collect();
let taken: String = if name == "LEFT" {
characters.iter().take(count).collect()
} else {
characters
.iter()
.skip(characters.len().saturating_sub(count))
.collect()
};
Ok(CellValue::Text(taken))
}
"LEN" => {
let values = argument_values(arguments, sheet, source)?;
let text = as_display(values.first().unwrap_or(&CellValue::None));
Ok(CellValue::Number(text.chars().count() as f64))
}
"TRIM" | "UPPER" | "LOWER" => {
let values = argument_values(arguments, sheet, source)?;
let text = as_display(values.first().unwrap_or(&CellValue::None));
Ok(CellValue::Text(match name {
"TRIM" => text.split_whitespace().collect::<Vec<_>>().join(" "),
"UPPER" => text.to_uppercase(),
_ => text.to_lowercase(),
}))
}
"NOT" => {
let values = argument_values(arguments, sheet, source)?;
match to_bool(values.first().unwrap_or(&CellValue::None))? {
Some(flag) => Ok(CellValue::Bool(!flag)),
None => Ok(CellValue::Number(0.0)),
}
}
"TRUE" => Ok(CellValue::Bool(true)),
"FALSE" => Ok(CellValue::Bool(false)),
"ISBLANK" => {
let values = argument_values(arguments, sheet, source)?;
let first = values.first().unwrap_or(&CellValue::None);
Ok(CellValue::Bool(first.is_empty()))
}
"ISNUMBER" => {
let values = argument_values(arguments, sheet, source)?;
let first = values.first().unwrap_or(&CellValue::None);
Ok(CellValue::Bool(matches!(first, CellValue::Number(_))))
}
"ISTEXT" => {
let values = argument_values(arguments, sheet, source)?;
let first = values.first().unwrap_or(&CellValue::None);
Ok(CellValue::Bool(matches!(first, CellValue::Text(_))))
}
"ISERROR" => {
let values = argument_values(arguments, sheet, source)?;
let first = values.first().unwrap_or(&CellValue::None);
Ok(CellValue::Bool(is_error(first)))
}
other => Err(format!("unsupported function {other}()")),
}
}
fn resolve_range(
node: &Node,
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<(Vec<CellValue>, usize, usize), Reason> {
match node {
Node::Reference(ref_sheet, body) => {
let target_sheet = ref_sheet.as_deref().unwrap_or(sheet);
match resolve(target_sheet, body, source)? {
Resolved::Single(val) => Ok((vec![val], 1, 1)),
Resolved::Range { values, cols, rows } => Ok((values, cols, rows)),
}
}
other => {
let val = eval(other, sheet, source)?;
Ok((vec![val], 1, 1))
}
}
}
fn values_equal(a: &CellValue, b: &CellValue) -> bool {
match (a, b) {
(CellValue::Number(x), CellValue::Number(y)) => (x - y).abs() < 1e-12,
(CellValue::Text(x), CellValue::Text(y)) => x.eq_ignore_ascii_case(y),
(CellValue::Bool(x), CellValue::Bool(y)) => x == y,
(CellValue::None, CellValue::None) => true,
(CellValue::Number(x), CellValue::Text(y)) | (CellValue::Text(y), CellValue::Number(x)) => {
if let Ok(yn) = y.trim().parse::<f64>() {
(x - yn).abs() < 1e-12
} else {
false
}
}
_ => false,
}
}
fn call_choose(
arguments: &[Node],
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<CellValue, Reason> {
if arguments.len() < 2 {
return Err("CHOOSE() needs an index and at least one choice".to_string());
}
let index_val = eval(&arguments[0], sheet, source)?;
let index = to_number(&index_val)? as i64;
if index < 1 || index as usize >= arguments.len() {
return Ok(error_value("#VALUE!"));
}
eval(&arguments[index as usize], sheet, source)
}
fn call_index(
arguments: &[Node],
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<CellValue, Reason> {
if arguments.len() < 2 {
return Err("INDEX() needs at least a range and a row number".to_string());
}
let (values, cols, rows) = resolve_range(&arguments[0], sheet, source)?;
let row_val = eval(&arguments[1], sheet, source)?;
let row_num = to_number(&row_val)? as i64;
let col_num = if arguments.len() >= 3 {
let col_val = eval(&arguments[2], sheet, source)?;
to_number(&col_val)? as i64
} else if rows == 1 && cols > 1 {
row_num
} else {
1
};
let actual_row = if rows == 1 && cols > 1 && arguments.len() < 3 {
1
} else {
row_num
};
let actual_col = col_num;
if actual_row < 1 || actual_row as usize > rows || actual_col < 1 || actual_col as usize > cols
{
return Ok(error_value("#REF!"));
}
let idx = (actual_row as usize - 1) * cols + (actual_col as usize - 1);
Ok(values.get(idx).cloned().unwrap_or(CellValue::None))
}
fn call_match(
arguments: &[Node],
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<CellValue, Reason> {
if arguments.len() < 2 {
return Err("MATCH() needs a lookup value and lookup array".to_string());
}
let lookup_val = eval(&arguments[0], sheet, source)?;
let (values, cols, rows) = resolve_range(&arguments[1], sheet, source)?;
if cols > 1 && rows > 1 {
return Ok(error_value("#N/A"));
}
let match_type = if arguments.len() >= 3 {
let m_val = eval(&arguments[2], sheet, source)?;
to_number(&m_val)? as i64
} else {
1
};
if match_type == 0 {
for (idx, val) in values.iter().enumerate() {
if values_equal(&lookup_val, val) {
return Ok(CellValue::Number((idx + 1) as f64));
}
}
Ok(error_value("#N/A"))
} else if match_type == 1 {
let mut last_match = None;
let target = to_number(&lookup_val)?;
for (idx, val) in values.iter().enumerate() {
if let Ok(num) = to_number(val) {
if num <= target {
last_match = Some(idx + 1);
} else {
break;
}
}
}
match last_match {
Some(idx) => Ok(CellValue::Number(idx as f64)),
None => Ok(error_value("#N/A")),
}
} else if match_type == -1 {
let mut last_match = None;
let target = to_number(&lookup_val)?;
for (idx, val) in values.iter().enumerate() {
if let Ok(num) = to_number(val) {
if num >= target {
last_match = Some(idx + 1);
} else {
break;
}
}
}
match last_match {
Some(idx) => Ok(CellValue::Number(idx as f64)),
None => Ok(error_value("#N/A")),
}
} else {
Ok(error_value("#N/A"))
}
}
fn call_vlookup(
arguments: &[Node],
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<CellValue, Reason> {
if arguments.len() < 3 {
return Err("VLOOKUP() needs lookup_value, table_array, and col_index".to_string());
}
let lookup_val = eval(&arguments[0], sheet, source)?;
let (values, cols, rows) = resolve_range(&arguments[1], sheet, source)?;
let col_val = eval(&arguments[2], sheet, source)?;
let col_index = to_number(&col_val)? as i64;
if col_index < 1 || col_index as usize > cols {
return Ok(error_value("#REF!"));
}
let approximate = if arguments.len() >= 4 {
let flag_val = eval(&arguments[3], sheet, source)?;
to_bool(&flag_val)?.unwrap_or(true)
} else {
true
};
let col_offset = col_index as usize - 1;
if !approximate {
for r in 0..rows {
let key = &values[r * cols];
if values_equal(&lookup_val, key) {
return Ok(values[r * cols + col_offset].clone());
}
}
Ok(error_value("#N/A"))
} else {
let mut best_row = None;
if let Ok(target) = to_number(&lookup_val) {
for r in 0..rows {
let key = &values[r * cols];
if let Ok(k_num) = to_number(key) {
if k_num <= target {
best_row = Some(r);
} else {
break;
}
}
}
} else {
let target_str = as_display(&lookup_val).to_lowercase();
for r in 0..rows {
let key_str = as_display(&values[r * cols]).to_lowercase();
if key_str.as_str() <= target_str.as_str() {
best_row = Some(r);
} else {
break;
}
}
}
match best_row {
Some(r) => Ok(values[r * cols + col_offset].clone()),
None => Ok(error_value("#N/A")),
}
}
}
fn call_xlookup(
arguments: &[Node],
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<CellValue, Reason> {
if arguments.len() < 3 {
return Err("XLOOKUP() needs lookup_value, lookup_array, and return_array".to_string());
}
let lookup_val = eval(&arguments[0], sheet, source)?;
let (lookup_values, _, _) = resolve_range(&arguments[1], sheet, source)?;
let (return_values, _, _) = resolve_range(&arguments[2], sheet, source)?;
if lookup_values.len() != return_values.len() {
return Ok(error_value("#VALUE!"));
}
let match_mode = if arguments.len() >= 5 {
let m_val = eval(&arguments[4], sheet, source)?;
to_number(&m_val)? as i64
} else {
0
};
let mut found_index = None;
if match_mode == 0 {
for (i, val) in lookup_values.iter().enumerate() {
if values_equal(&lookup_val, val) {
found_index = Some(i);
break;
}
}
} else if match_mode == -1 {
for (i, val) in lookup_values.iter().enumerate() {
if values_equal(&lookup_val, val) {
found_index = Some(i);
break;
}
}
if found_index.is_none() {
if let Ok(target) = to_number(&lookup_val) {
let mut best_diff = f64::INFINITY;
for (i, val) in lookup_values.iter().enumerate() {
if let Ok(n) = to_number(val) {
if n < target && (target - n) < best_diff {
best_diff = target - n;
found_index = Some(i);
}
}
}
}
}
} else if match_mode == 1 {
for (i, val) in lookup_values.iter().enumerate() {
if values_equal(&lookup_val, val) {
found_index = Some(i);
break;
}
}
if found_index.is_none() {
if let Ok(target) = to_number(&lookup_val) {
let mut best_diff = f64::INFINITY;
for (i, val) in lookup_values.iter().enumerate() {
if let Ok(n) = to_number(val) {
if n > target && (n - target) < best_diff {
best_diff = n - target;
found_index = Some(i);
}
}
}
}
}
}
match found_index {
Some(i) => Ok(return_values[i].clone()),
None => {
if arguments.len() >= 4 {
eval(&arguments[3], sheet, source)
} else {
Ok(error_value("#N/A"))
}
}
}
}
fn matches_criteria(val: &CellValue, crit: &CellValue) -> bool {
let crit_text = match crit {
CellValue::Text(t) => t.trim(),
CellValue::Number(n) => {
return match val {
CellValue::Number(vn) => (vn - n).abs() < 1e-12,
_ => false,
};
}
CellValue::Bool(b) => {
return match val {
CellValue::Bool(vb) => vb == b,
_ => false,
};
}
_ => return val.is_empty(),
};
if let Some(rest) = crit_text.strip_prefix(">=") {
if let Ok(target) = rest.trim().parse::<f64>() {
if let CellValue::Number(vn) = val {
return *vn >= target;
}
}
return false;
}
if let Some(rest) = crit_text.strip_prefix("<=") {
if let Ok(target) = rest.trim().parse::<f64>() {
if let CellValue::Number(vn) = val {
return *vn <= target;
}
}
return false;
}
if let Some(rest) = crit_text.strip_prefix("<>") {
let rest = rest.trim();
if let Ok(target) = rest.parse::<f64>() {
return match val {
CellValue::Number(vn) => (vn - target).abs() >= 1e-12,
_ => true,
};
} else {
return match val {
CellValue::Text(vt) => !vt.eq_ignore_ascii_case(rest),
_ => true,
};
}
}
if let Some(rest) = crit_text.strip_prefix('>') {
if let Ok(target) = rest.trim().parse::<f64>() {
if let CellValue::Number(vn) = val {
return *vn > target;
}
}
return false;
}
if let Some(rest) = crit_text.strip_prefix('<') {
if let Ok(target) = rest.trim().parse::<f64>() {
if let CellValue::Number(vn) = val {
return *vn < target;
}
}
return false;
}
if let Some(rest) = crit_text.strip_prefix('=') {
let rest = rest.trim();
if let Ok(target) = rest.parse::<f64>() {
return match val {
CellValue::Number(vn) => (vn - target).abs() < 1e-12,
_ => false,
};
} else {
return match val {
CellValue::Text(vt) => vt.eq_ignore_ascii_case(rest),
_ => false,
};
}
}
if let Ok(target) = crit_text.parse::<f64>() {
match val {
CellValue::Number(vn) => (vn - target).abs() < 1e-12,
_ => false,
}
} else {
match val {
CellValue::Text(vt) => vt.eq_ignore_ascii_case(crit_text),
_ => false,
}
}
}
fn call_sumif(
arguments: &[Node],
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<CellValue, Reason> {
if arguments.len() < 2 {
return Err("SUMIF() needs range and criteria".to_string());
}
let (range_values, _, _) = resolve_range(&arguments[0], sheet, source)?;
let crit_val = eval(&arguments[1], sheet, source)?;
let (sum_values, _, _) = if arguments.len() >= 3 {
resolve_range(&arguments[2], sheet, source)?
} else {
(range_values.clone(), 1, 1)
};
let mut sum = 0.0;
for (i, val) in range_values.iter().enumerate() {
if matches_criteria(val, &crit_val) {
if let Some(CellValue::Number(n)) = sum_values.get(i) {
sum += n;
}
}
}
Ok(CellValue::Number(sum))
}
fn call_countif(
arguments: &[Node],
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<CellValue, Reason> {
if arguments.len() < 2 {
return Err("COUNTIF() needs range and criteria".to_string());
}
let (range_values, _, _) = resolve_range(&arguments[0], sheet, source)?;
let crit_val = eval(&arguments[1], sheet, source)?;
let count = range_values
.iter()
.filter(|val| matches_criteria(val, &crit_val))
.count();
Ok(CellValue::Number(count as f64))
}
fn call_sumifs(
arguments: &[Node],
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<CellValue, Reason> {
if arguments.len() < 3 || !(arguments.len() - 1).is_multiple_of(2) {
return Err(
"SUMIFS() requires a sum range followed by pairs of criteria range and criteria"
.to_string(),
);
}
let (sum_values, _, _) = resolve_range(&arguments[0], sheet, source)?;
let mut criteria_pairs = Vec::new();
let mut idx = 1;
while idx < arguments.len() {
let (crit_range, _, _) = resolve_range(&arguments[idx], sheet, source)?;
let crit_val = eval(&arguments[idx + 1], sheet, source)?;
criteria_pairs.push((crit_range, crit_val));
idx += 2;
}
let mut sum = 0.0;
for (i, sum_val) in sum_values.iter().enumerate() {
let mut all_match = true;
for (crit_range, crit_val) in &criteria_pairs {
if let Some(val) = crit_range.get(i) {
if !matches_criteria(val, crit_val) {
all_match = false;
break;
}
} else {
all_match = false;
break;
}
}
if all_match {
if let CellValue::Number(n) = sum_val {
sum += n;
}
}
}
Ok(CellValue::Number(sum))
}
fn call_countifs(
arguments: &[Node],
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<CellValue, Reason> {
if arguments.len() < 2 || !arguments.len().is_multiple_of(2) {
return Err("COUNTIFS() requires pairs of criteria range and criteria".to_string());
}
let mut criteria_pairs = Vec::new();
let mut idx = 0;
while idx < arguments.len() {
let (crit_range, _, _) = resolve_range(&arguments[idx], sheet, source)?;
let crit_val = eval(&arguments[idx + 1], sheet, source)?;
criteria_pairs.push((crit_range, crit_val));
idx += 2;
}
let first_len = criteria_pairs[0].0.len();
let mut count = 0;
for i in 0..first_len {
let mut all_match = true;
for (crit_range, crit_val) in &criteria_pairs {
if let Some(val) = crit_range.get(i) {
if !matches_criteria(val, crit_val) {
all_match = false;
break;
}
} else {
all_match = false;
break;
}
}
if all_match {
count += 1;
}
}
Ok(CellValue::Number(count as f64))
}
pub fn prefix_future_functions(formula: &str) -> String {
const FUTURE_FUNCTIONS: &[&str] = &[
"XLOOKUP",
"XMATCH",
"SEQUENCE",
"UNIQUE",
"FILTER",
"SORT",
"SORTBY",
"RANDARRAY",
"SINGLE",
"CONCAT",
"TEXTJOIN",
"IFS",
"SWITCH",
"MINIFS",
"MAXIFS",
"LET",
"LAMBDA",
"MAP",
"REDUCE",
"SCAN",
"MAKEARRAY",
"BYROW",
"BYCOL",
"ISOMITTED",
"CHOOSEROWS",
"CHOOSECOLS",
"DROP",
"TAKE",
"EXPAND",
"TOCOL",
"TOROW",
"WRAPROWS",
"WRAPCOLS",
"TEXTSPLIT",
"TEXTBEFORE",
"TEXTAFTER",
"VSTACK",
"HSTACK",
];
let mut result = String::with_capacity(formula.len() + 16);
let mut chars = formula.chars().peekable();
let mut in_string = false;
while let Some(ch) = chars.next() {
if ch == '"' {
in_string = !in_string;
result.push(ch);
continue;
}
if !in_string && (ch.is_alphabetic() || ch == '_') {
let mut word = String::new();
word.push(ch);
while let Some(&next_ch) = chars.peek() {
if next_ch.is_alphanumeric() || next_ch == '.' || next_ch == '_' {
if let Some(nc) = chars.next() {
word.push(nc);
}
} else {
break;
}
}
if chars.peek() == Some(&'(') {
let upper = word.to_uppercase();
if !upper.starts_with("_XLFN.") && FUTURE_FUNCTIONS.contains(&upper.as_str()) {
result.push_str("_xlfn.");
}
}
result.push_str(&word);
} else {
result.push(ch);
}
}
result
}
fn call_if(
arguments: &[Node],
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<CellValue, Reason> {
let Some(condition) = arguments.first() else {
return Err("IF() needs a condition".to_string());
};
let value = eval(condition, sheet, source)?;
if is_error(&value) {
return Ok(value);
}
let flag = to_bool(&value)?;
match flag {
Some(true) => match arguments.get(1) {
Some(branch) => eval(branch, sheet, source),
None => Ok(CellValue::Text(String::new())),
},
Some(false) => match arguments.get(2) {
Some(branch) => eval(branch, sheet, source),
None => Ok(CellValue::Bool(false)),
},
None => Ok(CellValue::Number(0.0)),
}
}
fn call_iferror(
arguments: &[Node],
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<CellValue, Reason> {
let Some(value_node) = arguments.first() else {
return Err("IFERROR() needs a value".to_string());
};
match eval(value_node, sheet, source) {
Ok(value) if is_error(&value) => match arguments.get(1) {
Some(fallback) => eval(fallback, sheet, source),
None => Ok(CellValue::Text(String::new())),
},
other => other,
}
}
fn call_and_or(
name: &str,
arguments: &[Node],
sheet: &str,
source: &dyn ValueSource,
) -> std::result::Result<CellValue, Reason> {
if arguments.is_empty() {
return Err(format!("{name}() needs an argument"));
}
let mut any_true = false;
let mut all_true = true;
let mut seen = false;
for argument in arguments {
let values = match argument {
Node::Reference(reference_sheet, body) => {
match resolve(reference_sheet.as_deref().unwrap_or(sheet), body, source)? {
Resolved::Single(value) => vec![value],
Resolved::Range { values, .. } => values,
}
}
other => vec![eval(other, sheet, source)?],
};
for value in values {
if is_error(&value) {
return Ok(value);
}
if let CellValue::Text(text) = &value {
if matches!(argument, Node::Reference(_, _)) {
continue;
}
return Err(format!("#VALUE! cannot read {text:?} as a condition"));
}
if let Some(flag) = to_bool(&value)? {
seen = true;
any_true |= flag;
all_true &= flag;
}
}
}
if !seen {
return Ok(CellValue::Bool(name == "AND"));
}
Ok(CellValue::Bool(if name == "AND" {
all_true
} else {
any_true
}))
}
fn to_bool(value: &CellValue) -> std::result::Result<Option<bool>, Reason> {
match value {
CellValue::Bool(flag) => Ok(Some(*flag)),
CellValue::Number(number) => Ok(Some(*number != 0.0)),
CellValue::None => Ok(None),
CellValue::Text(text) => Err(format!("#VALUE! cannot read {text:?} as a condition")),
CellValue::Error(code) => Err(code.clone()),
other => Err(format!("#VALUE! cannot use {other:?} as a condition")),
}
}
fn round(value: f64, places: f64, mode: &str) -> f64 {
let factor = 10f64.powi(places as i32);
let scaled = value * factor;
let rounded = match mode {
"ROUNDUP" => {
if scaled >= 0.0 {
scaled.ceil()
} else {
scaled.floor()
}
}
"ROUNDDOWN" => {
if scaled >= 0.0 {
scaled.floor()
} else {
scaled.ceil()
}
}
_ => {
if scaled >= 0.0 {
(scaled + 0.5).floor()
} else {
(scaled - 0.5).ceil()
}
}
};
rounded / factor
}
#[cfg(test)]
mod tests {
use super::*;
use std::collections::BTreeMap;
struct Map(BTreeMap<String, CellValue>);
impl Map {
fn new() -> Self {
let mut cells = BTreeMap::new();
for (coordinate, value) in [
("A1", CellValue::Number(1.0)),
("A2", CellValue::Number(2.0)),
("A3", CellValue::Number(3.0)),
("B1", CellValue::Text("hello".to_string())),
("B2", CellValue::Text(" padded ".to_string())),
("C1", CellValue::Bool(true)),
("C2", CellValue::Bool(false)),
("A4", CellValue::Number(0.0)),
] {
cells.insert(coordinate.to_string(), value);
}
Map(cells)
}
fn set(&mut self, coordinate: &str, value: CellValue) {
self.0.insert(coordinate.to_string(), value);
}
}
impl ValueSource for Map {
fn cell(&self, _sheet: &str, coordinate: &str) -> CellValue {
self.0.get(coordinate).cloned().unwrap_or(CellValue::None)
}
fn max_row(&self, _sheet: &str) -> Option<u32> {
let mut max = 1u32;
for coord in self.0.keys() {
if let Ok((_col, row)) = crate::cell::utils::coordinate_from_string(coord) {
if row > max {
max = row;
}
}
}
Some(max)
}
}
fn value(formula: &str) -> CellValue {
let source = Map::new();
evaluate(formula, "Sheet1", &source).unwrap_or_else(|reason| panic!("{formula}: {reason}"))
}
fn reason(formula: &str) -> Option<String> {
let source = Map::new();
evaluate(formula, "Sheet1", &source).err()
}
fn number(formula: &str) -> f64 {
match value(formula) {
CellValue::Number(v) => v,
other => panic!("{formula} gave {other:?}, not a number"),
}
}
fn text(formula: &str) -> String {
match value(formula) {
CellValue::Text(v) => v,
other => panic!("{formula} gave {other:?}, not text"),
}
}
#[test]
fn the_supported_list_has_no_names_the_dispatcher_lacks() {
for name in SUPPORTED_FUNCTIONS {
let node = Node::Function(
(*name).to_string(),
vec![Node::Literal(CellValue::Number(1.0))],
);
let source = Map::new();
let result = eval(&node, "Sheet1", &source);
assert!(
!matches!(result, Err(reason) if reason.starts_with("unsupported function")),
"{name} is listed but the dispatcher refuses it"
);
}
assert_eq!(SUPPORTED_FUNCTIONS.len(), 44, "the list grew or shrank");
}
#[test]
fn arithmetic_follows_the_usual_precedence() {
assert_eq!(number("=1+2*3"), 7.0);
assert_eq!(number("=(1+2)*3"), 9.0);
assert_eq!(number("=2^3^2"), 64.0, "Excel's ^ is left-associative");
}
#[test]
fn unary_minus_binds_tighter_than_the_power() {
assert_eq!(number("=-2^2"), 4.0);
assert_eq!(number("=-(2^2)"), -4.0);
}
#[test]
fn percentages_and_blanks_behave_as_they_do_in_excel() {
assert_eq!(number("=50%"), 0.5);
assert_eq!(number("=E1+1"), 1.0, "a blank is zero in arithmetic");
assert_eq!(number("=SUM(E1:E3)"), 0.0);
}
#[test]
fn division_by_zero_is_an_error_rather_than_an_infinity() {
assert!(matches!(value("=1/0"), CellValue::Error(code) if code == "#DIV/0!"));
}
#[test]
fn an_error_anywhere_propagates() {
assert!(matches!(value("=1/0+SUM(A1:A3)"), CellValue::Error(code) if code == "#DIV/0!"));
assert!(matches!(value("=SUM(A1:A3)+1/0"), CellValue::Error(code) if code == "#DIV/0!"));
}
#[test]
fn ranges_aggregate_over_their_cells() {
assert_eq!(number("=SUM(A1:A3)"), 6.0);
assert_eq!(number("=AVERAGE(A1:A3)"), 2.0);
assert_eq!(number("=MIN(A1:A3)"), 1.0);
assert_eq!(number("=MAX(A1:A3)"), 3.0);
assert_eq!(number("=COUNT(A1:B3)"), 3.0, "text is not counted");
assert_eq!(
number("=COUNTA(A1:B3)"),
5.0,
"COUNTA counts the text but not the blank"
);
}
#[test]
fn aggregating_an_empty_range_follows_excel() {
assert!(matches!(value("=AVERAGE(E1:E3)"), CellValue::Error(code) if code == "#DIV/0!"));
assert_eq!(number("=SUM(E1:E3)"), 0.0);
assert_eq!(number("=MIN(E1:E3)"), 0.0);
}
#[test]
fn only_the_branch_if_takes_is_evaluated() {
assert_eq!(
number("=IF(A4=0,0,1/A4)"),
0.0,
"the true branch avoids the division"
);
assert!(
matches!(value("=IF(A4<>0,0,1/A4)"), CellValue::Error(code) if code == "#DIV/0!"),
"an error on the branch that is taken still propagates"
);
assert!(
!matches!(value("=IF(A4=0,0,1/A4)"), CellValue::Error(_)),
"the skipped branch must not raise #DIV/0!"
);
assert_eq!(
number("=IF(A1=0,0,1/A1)"),
1.0,
"the other branch is taken normally"
);
}
#[test]
fn if_returns_false_when_the_else_branch_is_missing() {
assert!(matches!(value("=IF(FALSE,1)"), CellValue::Bool(false)));
}
#[test]
fn comparison_orders_types_with_numbers_first() {
assert!(matches!(value("=1<\"a\""), CellValue::Bool(true)));
assert!(matches!(value("=\"a\"<TRUE"), CellValue::Bool(true)));
assert!(matches!(value("=\"ABC\"=\"abc\""), CellValue::Bool(true)));
assert!(matches!(value("=\"a\"=\"b\""), CellValue::Bool(false)));
}
#[test]
fn a_blank_compares_as_zero_against_a_number() {
assert!(matches!(value("=E1=0"), CellValue::Bool(true)));
}
#[test]
fn text_functions_behave() {
assert_eq!(text("=UPPER(B1)"), "HELLO");
assert_eq!(text("=LOWER(B1)"), "hello");
assert_eq!(text("=LEFT(B1,2)"), "he");
assert_eq!(text("=RIGHT(B1,3)"), "llo");
assert_eq!(text("=LEFT(B1)"), "h");
assert_eq!(number("=LEN(B1)"), 5.0);
assert_eq!(text("=TRIM(B2)"), "padded");
}
#[test]
fn concatenation_renders_numbers_the_way_a_cell_shows_them() {
assert_eq!(text("=1+1&\"\""), "2");
assert_eq!(text("=\"n=\"&1&\"\" "), "n=1");
assert_eq!(text("=A1&B1"), "1hello");
}
#[test]
fn rounding_goes_away_from_zero_on_a_half() {
assert_eq!(number("=ROUND(2.5)"), 3.0);
assert_eq!(number("=ROUND(-2.5)"), -3.0, "Rust's round would say -2");
assert_eq!(number("=ROUND(2.345,2)"), 2.35);
assert_eq!(number("=ROUNDUP(2.1)"), 3.0);
assert_eq!(number("=ROUNDDOWN(2.9)"), 2.0);
}
#[test]
fn mod_follows_the_divisors_sign() {
assert_eq!(number("=MOD(-3,2)"), 1.0);
assert_eq!(number("=MOD(3,-2)"), -1.0);
assert!(matches!(value("=MOD(1,0)"), CellValue::Error(code) if code == "#DIV/0!"));
}
#[test]
fn iferror_replaces_an_error_and_leaves_everything_else_alone() {
assert_eq!(number("=IFERROR(1/0,7)"), 7.0);
assert_eq!(number("=IFERROR(1/2,7)"), 0.5);
}
#[test]
fn and_or_and_not_read_conditions() {
assert!(matches!(value("=AND(TRUE,TRUE)"), CellValue::Bool(true)));
assert!(matches!(value("=AND(TRUE,FALSE)"), CellValue::Bool(false)));
assert!(matches!(value("=OR(FALSE,TRUE)"), CellValue::Bool(true)));
assert!(matches!(value("=NOT(TRUE)"), CellValue::Bool(false)));
assert!(matches!(value("=AND(C1:C2)"), CellValue::Bool(false)));
}
#[test]
fn an_unsupported_function_is_reported_rather_than_guessed_at() {
let source = Map::new();
let result = evaluate("=WEBSERVICE(A1)", "Sheet1", &source);
assert_eq!(
result,
Err("unsupported function WEBSERVICE()".to_string()),
"WEBSERVICE must not produce a value"
);
}
#[test]
fn vlookup_and_xlookup_and_index_match() {
let mut source = Map::new();
source.set("A1", CellValue::Number(10.0));
source.set("B1", CellValue::Number(100.0));
source.set("A2", CellValue::Number(20.0));
source.set("B2", CellValue::Number(200.0));
source.set("A3", CellValue::Number(30.0));
source.set("B3", CellValue::Number(300.0));
assert_eq!(
evaluate("=VLOOKUP(20, A1:B3, 2, FALSE)", "Sheet1", &source).unwrap(),
CellValue::Number(200.0)
);
assert_eq!(
evaluate("=XLOOKUP(30, A1:A3, B1:B3)", "Sheet1", &source).unwrap(),
CellValue::Number(300.0)
);
assert_eq!(
evaluate("=MATCH(20, A1:A3, 0)", "Sheet1", &source).unwrap(),
CellValue::Number(2.0)
);
assert_eq!(
evaluate("=INDEX(B1:B3, 2)", "Sheet1", &source).unwrap(),
CellValue::Number(200.0)
);
assert_eq!(
evaluate("=SUMIF(A1:A3, \">15\", B1:B3)", "Sheet1", &source).unwrap(),
CellValue::Number(500.0)
);
assert_eq!(
evaluate("=COUNTIF(A1:A3, \">15\")", "Sheet1", &source).unwrap(),
CellValue::Number(2.0)
);
assert_eq!(
evaluate("=CHOOSE(2, \"a\", \"b\", \"c\")", "Sheet1", &source).unwrap(),
CellValue::Text("b".to_string())
);
assert_eq!(
evaluate("=ISNUMBER(A1)", "Sheet1", &source).unwrap(),
CellValue::Bool(true)
);
assert_eq!(
evaluate("=ISTEXT(A1)", "Sheet1", &source).unwrap(),
CellValue::Bool(false)
);
}
#[test]
fn a_sheet_qualified_reference_is_read() {
let source = Map::new();
let value = evaluate("=Other!A1", "Other", &source).expect("A1 on that sheet");
assert_eq!(value, CellValue::Number(1.0));
}
#[test]
fn absolute_references_are_accepted() {
let source = Map::new();
let value = evaluate("=SUM($A$1:$A$3)", "Sheet1", &source).expect("the range");
assert_eq!(value, CellValue::Number(6.0));
}
#[test]
fn text_is_parsed_with_doubled_quotes_inside_it() {
assert_eq!(text("=\"say \"\"hi\"\"\""), "say \"hi\"");
}
#[test]
fn a_formula_that_cannot_be_parsed_is_an_error_not_a_value() {
let source = Map::new();
assert!(evaluate("=1+", "Sheet1", &source).is_err());
assert!(evaluate("=SUM(", "Sheet1", &source).is_err());
assert!(evaluate("=1 1", "Sheet1", &source).is_err());
}
#[test]
fn the_formula_may_be_written_with_or_without_its_leading_equals() {
let source = Map::new();
assert_eq!(
evaluate("1+1", "Sheet1", &source).expect("no leading equals"),
CellValue::Number(2.0)
);
assert_eq!(
evaluate("=1+1", "Sheet1", &source).expect("a leading equals"),
CellValue::Number(2.0)
);
}
#[test]
fn a_cell_reference_to_a_cell_holding_a_formula_is_not_followed() {
let mut source = Map::new();
source.set("D1", CellValue::Formula("=1+1".to_string()));
assert_eq!(
evaluate("=D1", "Sheet1", &source).expect("the reference itself is fine"),
CellValue::None,
"a cell holding a formula reads as blank, not as its unevaluated text"
);
}
#[test]
fn scientific_notation_is_read() {
assert_eq!(number("=1.5E2"), 150.0);
assert!(reason("=1+").is_some());
}
#[test]
fn functions_and_references_are_told_apart_by_the_bracket() {
let source = Map::new();
assert!(
evaluate("=SUM", "Sheet1", &source).is_err(),
"a bare name is not a value"
);
}
#[test]
fn whole_column_reference_clips_to_used_range() {
let mut source = Map::new();
source.set("A1", CellValue::Number(10.0));
source.set("A2", CellValue::Number(25.0));
source.set("A3", CellValue::Number(15.0));
let val = evaluate("=SUM(A:A)", "Sheet1", &source).unwrap();
assert_eq!(val, CellValue::Number(50.0));
}
#[test]
fn sumifs_and_countifs_evaluate_multi_criteria() {
let mut source = Map::new();
source.set("A1", CellValue::Text("Apple".into()));
source.set("B1", CellValue::Number(10.0));
source.set("C1", CellValue::Text("North".into()));
source.set("A2", CellValue::Text("Banana".into()));
source.set("B2", CellValue::Number(20.0));
source.set("C2", CellValue::Text("South".into()));
source.set("A3", CellValue::Text("Apple".into()));
source.set("B3", CellValue::Number(30.0));
source.set("C3", CellValue::Text("North".into()));
let sum_res = evaluate(
"=SUMIFS(B1:B3, A1:A3, \"Apple\", C1:C3, \"North\")",
"Sheet1",
&source,
)
.unwrap();
assert_eq!(sum_res, CellValue::Number(40.0));
let count_res = evaluate(
"=COUNTIFS(A1:A3, \"Apple\", C1:C3, \"North\")",
"Sheet1",
&source,
)
.unwrap();
assert_eq!(count_res, CellValue::Number(2.0));
}
#[test]
fn quoted_and_unicode_sheet_names_resolve() {
struct UnicodeSource;
impl ValueSource for UnicodeSource {
fn cell(&self, sheet: &str, coordinate: &str) -> CellValue {
if sheet == "Master Cash Book (रोकड़ बही)" && coordinate == "K473" {
CellValue::Number(21600.0)
} else {
CellValue::None
}
}
}
let source = UnicodeSource;
let res = evaluate(
"='Master Cash Book (रोकड़ बही)'!K473 + 100",
"Sheet1",
&source,
)
.unwrap();
assert_eq!(res, CellValue::Number(21700.0));
}
#[test]
fn recalculation_counts_and_reasons_are_reported() {
let mut sheet = BTreeMap::new();
sheet.insert("A1".to_string(), CellValue::Number(1.0));
let mut computed = BTreeMap::new();
computed.insert("Sheet1".to_string(), sheet);
let report = Recalculation {
computed,
unresolved: vec![
Unresolved {
sheet: "Sheet1".into(),
coordinate: "A2".into(),
formula: "=VLOOKUP(1,A:B,2)".into(),
reason: "unsupported function VLOOKUP()".into(),
},
Unresolved {
sheet: "Sheet1".into(),
coordinate: "A3".into(),
formula: "=XLOOKUP(1,A:B)".into(),
reason: "unsupported function VLOOKUP()".into(),
},
],
};
assert_eq!(report.computed_count(), 1);
assert!(!report.is_complete());
assert_eq!(
report.reasons(),
vec![("unsupported function VLOOKUP()".to_string(), 2)]
);
}
}