sheetkit_core/formula/functions/
mod.rs

1//! Built-in Excel function dispatch.
2//!
3//! Provides [`lookup_function`] to resolve a function name to its implementation,
4//! and helper utilities used by individual function implementations.
5
6pub mod date_time;
7pub mod information;
8pub mod logical;
9pub mod lookup;
10pub mod math;
11pub mod statistical;
12pub mod text;
13
14use crate::cell::CellValue;
15use crate::error::{Error, Result};
16use crate::formula::ast::Expr;
17use crate::formula::eval::{coerce_to_number, coerce_to_string, Evaluator};
18
19/// Signature for a built-in function implementation.
20///
21/// Functions receive unevaluated argument expressions and a mutable evaluator,
22/// allowing short-circuit evaluation (e.g., IF) and range expansion (e.g., SUM).
23pub type FunctionFn = fn(&[Expr], &mut Evaluator) -> Result<CellValue>;
24
25/// Resolve a function name (case-insensitive) to its implementation.
26pub fn lookup_function(name: &str) -> Option<FunctionFn> {
27    match name.to_ascii_uppercase().as_str() {
28        "SUM" => Some(fn_sum),
29        "AVERAGE" => Some(fn_average),
30        "COUNT" => Some(fn_count),
31        "COUNTA" => Some(fn_counta),
32        "MIN" => Some(fn_min),
33        "MAX" => Some(fn_max),
34        "IF" => Some(fn_if),
35        "ABS" => Some(fn_abs),
36        "INT" => Some(fn_int),
37        "ROUND" => Some(fn_round),
38        "MOD" => Some(fn_mod),
39        "POWER" => Some(fn_power),
40        "SQRT" => Some(fn_sqrt),
41        "LEN" => Some(fn_len),
42        "LOWER" => Some(fn_lower),
43        "UPPER" => Some(fn_upper),
44        "TRIM" => Some(fn_trim),
45        "LEFT" => Some(fn_left),
46        "RIGHT" => Some(fn_right),
47        "MID" => Some(fn_mid),
48        "CONCATENATE" => Some(fn_concatenate),
49        "AND" => Some(fn_and),
50        "OR" => Some(fn_or),
51        "NOT" => Some(fn_not),
52        "ISNUMBER" => Some(fn_isnumber),
53        "ISTEXT" => Some(fn_istext),
54        "ISBLANK" => Some(fn_isblank),
55        "ISERROR" => Some(fn_iserror),
56        "VALUE" => Some(fn_value),
57        "TEXT" => Some(fn_text),
58        "SUMIF" => Some(math::fn_sumif),
59        "SUMIFS" => Some(math::fn_sumifs),
60        "ROUNDUP" => Some(math::fn_roundup),
61        "ROUNDDOWN" => Some(math::fn_rounddown),
62        "CEILING" => Some(math::fn_ceiling),
63        "FLOOR" => Some(math::fn_floor),
64        "SIGN" => Some(math::fn_sign),
65        "RAND" => Some(math::fn_rand),
66        "RANDBETWEEN" => Some(math::fn_randbetween),
67        "PI" => Some(math::fn_pi),
68        "LOG" => Some(math::fn_log),
69        "LOG10" => Some(math::fn_log10),
70        "LN" => Some(math::fn_ln),
71        "EXP" => Some(math::fn_exp),
72        "PRODUCT" => Some(math::fn_product),
73        "QUOTIENT" => Some(math::fn_quotient),
74        "FACT" => Some(math::fn_fact),
75        "AVERAGEIF" => Some(statistical::fn_averageif),
76        "AVERAGEIFS" => Some(statistical::fn_averageifs),
77        "COUNTBLANK" => Some(statistical::fn_countblank),
78        "COUNTIF" => Some(statistical::fn_countif),
79        "COUNTIFS" => Some(statistical::fn_countifs),
80        "MEDIAN" => Some(statistical::fn_median),
81        "MODE" => Some(statistical::fn_mode),
82        "LARGE" => Some(statistical::fn_large),
83        "SMALL" => Some(statistical::fn_small),
84        "RANK" => Some(statistical::fn_rank),
85        "ISERR" => Some(information::fn_iserr),
86        "ISNA" => Some(information::fn_isna),
87        "ISLOGICAL" => Some(information::fn_islogical),
88        "ISEVEN" => Some(information::fn_iseven),
89        "ISODD" => Some(information::fn_isodd),
90        "TYPE" => Some(information::fn_type),
91        "N" => Some(information::fn_n),
92        "NA" => Some(information::fn_na),
93        "ERROR.TYPE" => Some(information::fn_error_type),
94        "CONCAT" => Some(text::fn_concat),
95        "FIND" => Some(text::fn_find),
96        "SEARCH" => Some(text::fn_search),
97        "SUBSTITUTE" => Some(text::fn_substitute),
98        "REPLACE" => Some(text::fn_replace),
99        "REPT" => Some(text::fn_rept),
100        "EXACT" => Some(text::fn_exact),
101        "T" => Some(text::fn_t),
102        "PROPER" => Some(text::fn_proper),
103        "TRUE" => Some(logical::fn_true),
104        "FALSE" => Some(logical::fn_false),
105        "IFERROR" => Some(logical::fn_iferror),
106        "IFNA" => Some(logical::fn_ifna),
107        "IFS" => Some(logical::fn_ifs),
108        "SWITCH" => Some(logical::fn_switch),
109        "XOR" => Some(logical::fn_xor),
110        "DATE" => Some(date_time::fn_date),
111        "TODAY" => Some(date_time::fn_today),
112        "NOW" => Some(date_time::fn_now),
113        "YEAR" => Some(date_time::fn_year),
114        "MONTH" => Some(date_time::fn_month),
115        "DAY" => Some(date_time::fn_day),
116        "HOUR" => Some(date_time::fn_hour),
117        "MINUTE" => Some(date_time::fn_minute),
118        "SECOND" => Some(date_time::fn_second),
119        "DATEDIF" => Some(date_time::fn_datedif),
120        "EDATE" => Some(date_time::fn_edate),
121        "EOMONTH" => Some(date_time::fn_eomonth),
122        "DATEVALUE" => Some(date_time::fn_datevalue),
123        "WEEKDAY" => Some(date_time::fn_weekday),
124        "WEEKNUM" => Some(date_time::fn_weeknum),
125        "NETWORKDAYS" => Some(date_time::fn_networkdays),
126        "WORKDAY" => Some(date_time::fn_workday),
127        "VLOOKUP" => Some(lookup::fn_vlookup),
128        "HLOOKUP" => Some(lookup::fn_hlookup),
129        "INDEX" => Some(lookup::fn_index),
130        "MATCH" => Some(lookup::fn_match),
131        "LOOKUP" => Some(lookup::fn_lookup),
132        "ROW" => Some(lookup::fn_row),
133        "COLUMN" => Some(lookup::fn_column),
134        "ROWS" => Some(lookup::fn_rows),
135        "COLUMNS" => Some(lookup::fn_columns),
136        "CHOOSE" => Some(lookup::fn_choose),
137        "ADDRESS" => Some(lookup::fn_address),
138        _ => None,
139    }
140}
141
142/// Verify that `args` has between `min` and `max` entries (inclusive).
143pub fn check_arg_count(name: &str, args: &[Expr], min: usize, max: usize) -> Result<()> {
144    if args.len() < min || args.len() > max {
145        let expected = if min == max {
146            format!("{min}")
147        } else {
148            format!("{min}..{max}")
149        };
150        return Err(Error::WrongArgCount {
151            name: name.to_string(),
152            expected,
153            got: args.len(),
154        });
155    }
156    Ok(())
157}
158
159/// Check whether a cell value matches a criteria string.
160///
161/// Criteria format: ">5", "<=10", "<>text", "=exact", plain "text".
162/// Numeric comparisons are used when both sides parse as numbers;
163/// otherwise strings are compared case-insensitively. Wildcard `*` and
164/// `?` at the start/end of plain text are supported in a simplified form.
165pub fn matches_criteria(cell_value: &CellValue, criteria: &str) -> bool {
166    if criteria.is_empty() {
167        return matches!(cell_value, CellValue::Empty);
168    }
169
170    let (op, val_str) = if let Some(rest) = criteria.strip_prefix("<=") {
171        ("<=", rest)
172    } else if let Some(rest) = criteria.strip_prefix(">=") {
173        (">=", rest)
174    } else if let Some(rest) = criteria.strip_prefix("<>") {
175        ("<>", rest)
176    } else if let Some(rest) = criteria.strip_prefix('<') {
177        ("<", rest)
178    } else if let Some(rest) = criteria.strip_prefix('>') {
179        (">", rest)
180    } else if let Some(rest) = criteria.strip_prefix('=') {
181        ("=", rest)
182    } else {
183        ("=", criteria)
184    };
185
186    let cell_num = coerce_to_number(cell_value).ok();
187    let crit_num: Option<f64> = val_str.parse().ok();
188
189    if let (Some(cn), Some(crn)) = (cell_num, crit_num) {
190        return match op {
191            "<=" => cn <= crn,
192            ">=" => cn >= crn,
193            "<>" => (cn - crn).abs() > f64::EPSILON,
194            "<" => cn < crn,
195            ">" => cn > crn,
196            "=" => (cn - crn).abs() < f64::EPSILON,
197            _ => false,
198        };
199    }
200
201    let cell_str = coerce_to_string(cell_value).to_ascii_lowercase();
202    let crit_lower = val_str.to_ascii_lowercase();
203
204    match op {
205        "=" => {
206            if crit_lower.contains('*') || crit_lower.contains('?') {
207                wildcard_match(&cell_str, &crit_lower)
208            } else {
209                cell_str == crit_lower
210            }
211        }
212        "<>" => {
213            if crit_lower.contains('*') || crit_lower.contains('?') {
214                !wildcard_match(&cell_str, &crit_lower)
215            } else {
216                cell_str != crit_lower
217            }
218        }
219        "<" => cell_str < crit_lower,
220        ">" => cell_str > crit_lower,
221        "<=" => cell_str <= crit_lower,
222        ">=" => cell_str >= crit_lower,
223        _ => false,
224    }
225}
226
227fn wildcard_match(text: &str, pattern: &str) -> bool {
228    let t: Vec<char> = text.chars().collect();
229    let p: Vec<char> = pattern.chars().collect();
230    let (tlen, plen) = (t.len(), p.len());
231    let mut dp = vec![vec![false; plen + 1]; tlen + 1];
232    dp[0][0] = true;
233    for j in 1..=plen {
234        if p[j - 1] == '*' {
235            dp[0][j] = dp[0][j - 1];
236        }
237    }
238    for i in 1..=tlen {
239        for j in 1..=plen {
240            if p[j - 1] == '*' {
241                dp[i][j] = dp[i][j - 1] || dp[i - 1][j];
242            } else if p[j - 1] == '?' || p[j - 1] == t[i - 1] {
243                dp[i][j] = dp[i - 1][j - 1];
244            }
245        }
246    }
247    dp[tlen][plen]
248}
249
250/// Expand a single argument expression into a flat list of CellValues.
251pub fn collect_criteria_range_values(arg: &Expr, ctx: &mut Evaluator) -> Result<Vec<CellValue>> {
252    match arg {
253        Expr::Range { start, end } => ctx.expand_range(start, end),
254        _ => {
255            let v = ctx.eval_expr(arg)?;
256            Ok(vec![v])
257        }
258    }
259}
260
261// -- Aggregate functions --
262
263fn fn_sum(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
264    check_arg_count("SUM", args, 1, 255)?;
265    let nums = ctx.collect_numbers(args)?;
266    Ok(CellValue::Number(nums.iter().sum()))
267}
268
269fn fn_average(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
270    check_arg_count("AVERAGE", args, 1, 255)?;
271    let nums = ctx.collect_numbers(args)?;
272    if nums.is_empty() {
273        return Ok(CellValue::Error("#DIV/0!".to_string()));
274    }
275    let sum: f64 = nums.iter().sum();
276    Ok(CellValue::Number(sum / nums.len() as f64))
277}
278
279fn fn_count(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
280    check_arg_count("COUNT", args, 1, 255)?;
281    let values = ctx.flatten_args_to_values(args)?;
282    let count = values
283        .iter()
284        .filter(|v| matches!(v, CellValue::Number(_) | CellValue::Date(_)))
285        .count();
286    Ok(CellValue::Number(count as f64))
287}
288
289fn fn_counta(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
290    check_arg_count("COUNTA", args, 1, 255)?;
291    let values = ctx.flatten_args_to_values(args)?;
292    let count = values
293        .iter()
294        .filter(|v| !matches!(v, CellValue::Empty))
295        .count();
296    Ok(CellValue::Number(count as f64))
297}
298
299fn fn_min(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
300    check_arg_count("MIN", args, 1, 255)?;
301    let nums = ctx.collect_numbers(args)?;
302    if nums.is_empty() {
303        return Ok(CellValue::Number(0.0));
304    }
305    let min = nums.iter().copied().fold(f64::INFINITY, f64::min);
306    Ok(CellValue::Number(min))
307}
308
309fn fn_max(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
310    check_arg_count("MAX", args, 1, 255)?;
311    let nums = ctx.collect_numbers(args)?;
312    if nums.is_empty() {
313        return Ok(CellValue::Number(0.0));
314    }
315    let max = nums.iter().copied().fold(f64::NEG_INFINITY, f64::max);
316    Ok(CellValue::Number(max))
317}
318
319// -- Logical functions --
320
321fn fn_if(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
322    check_arg_count("IF", args, 1, 3)?;
323    let cond = ctx.eval_expr(&args[0])?;
324    let truth = crate::formula::eval::coerce_to_bool(&cond)?;
325    if truth {
326        if args.len() > 1 {
327            ctx.eval_expr(&args[1])
328        } else {
329            Ok(CellValue::Bool(true))
330        }
331    } else if args.len() > 2 {
332        ctx.eval_expr(&args[2])
333    } else {
334        Ok(CellValue::Bool(false))
335    }
336}
337
338fn fn_and(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
339    check_arg_count("AND", args, 1, 255)?;
340    let values = ctx.flatten_args_to_values(args)?;
341    for v in &values {
342        if matches!(v, CellValue::Empty) {
343            continue;
344        }
345        if !crate::formula::eval::coerce_to_bool(v)? {
346            return Ok(CellValue::Bool(false));
347        }
348    }
349    Ok(CellValue::Bool(true))
350}
351
352fn fn_or(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
353    check_arg_count("OR", args, 1, 255)?;
354    let values = ctx.flatten_args_to_values(args)?;
355    for v in &values {
356        if matches!(v, CellValue::Empty) {
357            continue;
358        }
359        if crate::formula::eval::coerce_to_bool(v)? {
360            return Ok(CellValue::Bool(true));
361        }
362    }
363    Ok(CellValue::Bool(false))
364}
365
366fn fn_not(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
367    check_arg_count("NOT", args, 1, 1)?;
368    let v = ctx.eval_expr(&args[0])?;
369    let b = crate::formula::eval::coerce_to_bool(&v)?;
370    Ok(CellValue::Bool(!b))
371}
372
373// -- Math functions --
374
375fn fn_abs(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
376    check_arg_count("ABS", args, 1, 1)?;
377    let v = ctx.eval_expr(&args[0])?;
378    let n = crate::formula::eval::coerce_to_number(&v)?;
379    Ok(CellValue::Number(n.abs()))
380}
381
382fn fn_int(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
383    check_arg_count("INT", args, 1, 1)?;
384    let v = ctx.eval_expr(&args[0])?;
385    let n = crate::formula::eval::coerce_to_number(&v)?;
386    Ok(CellValue::Number(n.floor()))
387}
388
389fn fn_round(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
390    check_arg_count("ROUND", args, 2, 2)?;
391    let v = ctx.eval_expr(&args[0])?;
392    let d = ctx.eval_expr(&args[1])?;
393    let n = crate::formula::eval::coerce_to_number(&v)?;
394    let digits = crate::formula::eval::coerce_to_number(&d)? as i32;
395    let factor = 10f64.powi(digits);
396    Ok(CellValue::Number((n * factor).round() / factor))
397}
398
399fn fn_mod(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
400    check_arg_count("MOD", args, 2, 2)?;
401    let a = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[0])?)?;
402    let b = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[1])?)?;
403    if b == 0.0 {
404        return Ok(CellValue::Error("#DIV/0!".to_string()));
405    }
406    // Excel MOD: result has the sign of the divisor.
407    let result = a - (a / b).floor() * b;
408    Ok(CellValue::Number(result))
409}
410
411fn fn_power(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
412    check_arg_count("POWER", args, 2, 2)?;
413    let base = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[0])?)?;
414    let exp = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[1])?)?;
415    Ok(CellValue::Number(base.powf(exp)))
416}
417
418fn fn_sqrt(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
419    check_arg_count("SQRT", args, 1, 1)?;
420    let n = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[0])?)?;
421    if n < 0.0 {
422        return Ok(CellValue::Error("#NUM!".to_string()));
423    }
424    Ok(CellValue::Number(n.sqrt()))
425}
426
427// -- Text functions --
428
429fn fn_len(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
430    check_arg_count("LEN", args, 1, 1)?;
431    let v = ctx.eval_expr(&args[0])?;
432    let s = crate::formula::eval::coerce_to_string(&v);
433    Ok(CellValue::Number(s.len() as f64))
434}
435
436fn fn_lower(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
437    check_arg_count("LOWER", args, 1, 1)?;
438    let v = ctx.eval_expr(&args[0])?;
439    let s = crate::formula::eval::coerce_to_string(&v);
440    Ok(CellValue::String(s.to_lowercase()))
441}
442
443fn fn_upper(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
444    check_arg_count("UPPER", args, 1, 1)?;
445    let v = ctx.eval_expr(&args[0])?;
446    let s = crate::formula::eval::coerce_to_string(&v);
447    Ok(CellValue::String(s.to_uppercase()))
448}
449
450fn fn_trim(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
451    check_arg_count("TRIM", args, 1, 1)?;
452    let v = ctx.eval_expr(&args[0])?;
453    let s = crate::formula::eval::coerce_to_string(&v);
454    // Excel TRIM removes leading/trailing spaces and collapses internal runs.
455    let trimmed: String = s.split_whitespace().collect::<Vec<_>>().join(" ");
456    Ok(CellValue::String(trimmed))
457}
458
459fn fn_left(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
460    check_arg_count("LEFT", args, 1, 2)?;
461    let v = ctx.eval_expr(&args[0])?;
462    let s = crate::formula::eval::coerce_to_string(&v);
463    let n = if args.len() > 1 {
464        crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[1])?)? as usize
465    } else {
466        1
467    };
468    let result: String = s.chars().take(n).collect();
469    Ok(CellValue::String(result))
470}
471
472fn fn_right(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
473    check_arg_count("RIGHT", args, 1, 2)?;
474    let v = ctx.eval_expr(&args[0])?;
475    let s = crate::formula::eval::coerce_to_string(&v);
476    let n = if args.len() > 1 {
477        crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[1])?)? as usize
478    } else {
479        1
480    };
481    let chars: Vec<char> = s.chars().collect();
482    let start = chars.len().saturating_sub(n);
483    let result: String = chars[start..].iter().collect();
484    Ok(CellValue::String(result))
485}
486
487fn fn_mid(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
488    check_arg_count("MID", args, 3, 3)?;
489    let v = ctx.eval_expr(&args[0])?;
490    let s = crate::formula::eval::coerce_to_string(&v);
491    let start = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[1])?)? as usize;
492    let count = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[2])?)? as usize;
493    if start < 1 {
494        return Ok(CellValue::Error("#VALUE!".to_string()));
495    }
496    let result: String = s.chars().skip(start - 1).take(count).collect();
497    Ok(CellValue::String(result))
498}
499
500fn fn_concatenate(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
501    check_arg_count("CONCATENATE", args, 1, 255)?;
502    let mut result = String::new();
503    for arg in args {
504        let v = ctx.eval_expr(arg)?;
505        result.push_str(&crate::formula::eval::coerce_to_string(&v));
506    }
507    Ok(CellValue::String(result))
508}
509
510// -- Information functions --
511
512fn fn_isnumber(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
513    check_arg_count("ISNUMBER", args, 1, 1)?;
514    let v = ctx.eval_expr(&args[0])?;
515    Ok(CellValue::Bool(matches!(
516        v,
517        CellValue::Number(_) | CellValue::Date(_)
518    )))
519}
520
521fn fn_istext(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
522    check_arg_count("ISTEXT", args, 1, 1)?;
523    let v = ctx.eval_expr(&args[0])?;
524    Ok(CellValue::Bool(matches!(v, CellValue::String(_))))
525}
526
527fn fn_isblank(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
528    check_arg_count("ISBLANK", args, 1, 1)?;
529    let v = ctx.eval_expr(&args[0])?;
530    Ok(CellValue::Bool(matches!(v, CellValue::Empty)))
531}
532
533fn fn_iserror(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
534    check_arg_count("ISERROR", args, 1, 1)?;
535    let v = ctx.eval_expr(&args[0])?;
536    Ok(CellValue::Bool(matches!(v, CellValue::Error(_))))
537}
538
539// -- Conversion functions --
540
541fn fn_value(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
542    check_arg_count("VALUE", args, 1, 1)?;
543    let v = ctx.eval_expr(&args[0])?;
544    match crate::formula::eval::coerce_to_number(&v) {
545        Ok(n) => Ok(CellValue::Number(n)),
546        Err(_) => Ok(CellValue::Error("#VALUE!".to_string())),
547    }
548}
549
550fn fn_text(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
551    check_arg_count("TEXT", args, 2, 2)?;
552    let v = ctx.eval_expr(&args[0])?;
553    let _fmt = ctx.eval_expr(&args[1])?;
554    // Simplified: just convert to string representation (full format codes not implemented).
555    Ok(CellValue::String(crate::formula::eval::coerce_to_string(
556        &v,
557    )))
558}
sheetkit_core/formula/functions/mod.rs

sheetkit_core/formula/functions/
mod.rs
