ravenrs_sheet/functions/
mod.rs

1//! GridCraft worksheet function library.
2//!
3//! Every function is described by a [`FnSpec`] (name, arity, category, which parameters take
4//! scalars, a signature and a one-line description) and implemented as a plain Rust function over
5//! already-evaluated arguments. The calculation engine looks functions up by name with
6//! [`lookup`] and invokes them through [`call`], which applies Excel's automatic array lifting:
7//! passing an array where a scalar is expected evaluates the function once per element.
8//!
9//! Special forms that need references, laziness or hidden-row information (ROW, OFFSET,
10//! INDIRECT, LET, LAMBDA, SUBTOTAL, AGGREGATE, TEXT…) are evaluated by the host; this crate
11//! exports [`aggregate_values`] for SUBTOTAL/AGGREGATE.
12#![deny(clippy::unwrap_used, clippy::expect_used, clippy::panic, clippy::unimplemented, clippy::todo, clippy::unreachable)]
13#![forbid(unsafe_code)]
14
15use std::collections::HashMap;
16use std::sync::OnceLock;
17
18use crate::base::{Array, CellError, DateSystem, Value};
19
20/// Builds a [`FnSpec`] whose implementation returns `Result<Value, CellError>`.
21///
22/// `f!(NAME, min, max, Category, scalar_args, "SIG", "description", path::to::impl)`; prefix the
23/// arguments with `volatile;` for volatile functions.
24macro_rules! f {
25    (volatile; $name:literal, $min:expr, $max:expr, $cat:ident, $scal:expr, $sig:literal, $desc:literal, $imp:expr) => {
26        $crate::functions::FnSpec {
27            name: $name,
28            min_args: $min,
29            max_args: $max,
30            category: $crate::functions::Category::$cat,
31            volatile: true,
32            scalar_args: $scal,
33            value_args: &[],
34            signature: $sig,
35            description: $desc,
36            imp: {
37                fn w(a: &[$crate::functions::Arg], c: &mut dyn $crate::functions::Ctx) -> crate::base::Value {
38                    $crate::functions::util::finish(($imp)(a, c))
39                }
40                w
41            },
42        }
43    };
44    ($name:literal, $min:expr, $max:expr, $cat:ident, $scal:expr, $sig:literal, $desc:literal, $imp:expr) => {
45        $crate::functions::FnSpec {
46            name: $name,
47            min_args: $min,
48            max_args: $max,
49            category: $crate::functions::Category::$cat,
50            volatile: false,
51            scalar_args: $scal,
52            value_args: &[],
53            signature: $sig,
54            description: $desc,
55            imp: {
56                fn w(a: &[$crate::functions::Arg], c: &mut dyn $crate::functions::Ctx) -> crate::base::Value {
57                    $crate::functions::util::finish(($imp)(a, c))
58                }
59                w
60            },
61        }
62    };
63}
64
65pub(crate) mod criteria;
66pub(crate) mod database;
67pub(crate) mod date;
68pub(crate) mod dynamic;
69pub(crate) mod engineering;
70pub(crate) mod ets;
71pub(crate) mod financial;
72pub(crate) mod info;
73pub(crate) mod lift;
74pub(crate) mod lookup;
75pub(crate) mod math;
76pub(crate) mod special;
77pub(crate) mod stat;
78pub(crate) mod text;
79pub(crate) mod util;
80
81pub use ets::Timeline;
82pub use stat::{aggregate_values, aggregate_values_k};
83
84/// An evaluated argument. Ranges arrive as `Value::Array` (blank cells = `Value::Empty`) with
85/// `from_ref = true`.
86#[derive(Clone, Debug)]
87pub struct Arg {
88    pub value: Value,
89    pub from_ref: bool,
90}
91
92impl Arg {
93    /// A literal (non-reference) argument.
94    pub fn val(value: impl Into<Value>) -> Arg {
95        Arg { value: value.into(), from_ref: false }
96    }
97    /// An argument that came from a cell or range reference.
98    pub fn reference(value: impl Into<Value>) -> Arg {
99        Arg { value: value.into(), from_ref: true }
100    }
101}
102
103/// Services a function may need from the host.
104pub trait Ctx {
105    fn date_system(&self) -> DateSystem;
106    /// Current local date-time serial.
107    fn now_serial(&self) -> f64;
108    /// Uniform in [0, 1).
109    fn random(&mut self) -> f64;
110    /// For the database functions: whether the formula in the criteria cell at (`row`, `col`) holds
111    /// for `record` (a row of `db` below its labels). `None` when that cell holds no formula or the
112    /// criteria didn't come from a reference.
113    fn computed_criterion(&mut self, _row: usize, _col: usize, _db: &Array, _record: usize) -> Option<bool> {
114        None
115    }
116    /// For GROUPBY/PIVOTBY: whether their function argument is a LAMBDA (its value is then not used).
117    fn has_lambda(&self) -> bool {
118        false
119    }
120    /// Calls that LAMBDA with `arg` (one group's values) and returns its single result; `None` when
121    /// there is no lambda.
122    fn call_lambda(&mut self, _arg: Value) -> Option<Value> {
123        None
124    }
125}
126
127pub type FnImpl = fn(&[Arg], &mut dyn Ctx) -> Value;
128
129#[derive(Clone, Copy, Debug, PartialEq, Eq)]
130pub enum Category {
131    Financial,
132    Logical,
133    Text,
134    DateTime,
135    Lookup,
136    MathTrig,
137    Statistical,
138    Engineering,
139    Information,
140    Database,
141    Compatibility,
142    Web,
143    Cube,
144    /// Excel's "Add-in and Automation" category (EUROCONVERT).
145    AddIn,
146}
147
148pub struct FnSpec {
149    /// Upper-case, without any `_xlfn.` prefix.
150    pub name: &'static str,
151    pub min_args: usize,
152    /// `usize::MAX` for variadic functions (Excel allows 255 arguments).
153    pub max_args: usize,
154    pub category: Category,
155    pub volatile: bool,
156    /// For automatic array lifting: true for each parameter position that expects a scalar. When
157    /// an argument at a scalar position is an array, the function is applied element-wise
158    /// (broadcast like Excel's dynamic arrays). The last entry repeats for variadic tails; an
159    /// empty slice means no lifting.
160    pub scalar_args: &'static [bool],
161    /// Parameter positions that take a single value and reject a reference to more than one
162    /// cell with `#VALUE!` (the Analysis ToolPak functions: EOMONTH, WORKDAY, COUPDAYS, BIN2DEC…),
163    /// where other scalar parameters lift the range. Arrays that aren't references still lift.
164    /// Same layout as `scalar_args`; filled at registration from [`lift::value_args`].
165    pub value_args: &'static [bool],
166    pub signature: &'static str,
167    pub description: &'static str,
168    pub imp: FnImpl,
169}
170
171impl std::fmt::Debug for FnSpec {
172    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
173        f.debug_struct("FnSpec").field("name", &self.name).field("min_args", &self.min_args).field("max_args", &self.max_args).finish()
174    }
175}
176
177impl FnSpec {
178    /// Whether parameter `i` expects a scalar (and is therefore lifted over arrays).
179    pub fn is_scalar_arg(&self, i: usize) -> bool {
180        match self.scalar_args.get(i) {
181            Some(b) => *b,
182            None => self.scalar_args.last().copied().unwrap_or(false),
183        }
184    }
185
186    /// Whether parameter `i` takes a single value, so a multi-cell reference there is `#VALUE!`.
187    pub fn is_value_arg(&self, i: usize) -> bool {
188        match self.value_args.get(i) {
189            Some(b) => *b,
190            None => self.value_args.last().copied().unwrap_or(false),
191        }
192    }
193
194    /// Whether a call with `n` arguments is valid (Excel allows at most 255).
195    pub fn accepts(&self, n: usize) -> bool {
196        (self.min_args..=self.max_args).contains(&n) && n <= 255
197    }
198}
199
200/// Variadic marker for `max_args`.
201pub(crate) const VAR: usize = usize::MAX;
202
203fn registry() -> &'static (Vec<FnSpec>, HashMap<&'static str, usize>) {
204    static REG: OnceLock<(Vec<FnSpec>, HashMap<&str, usize>)> = OnceLock::new();
205    REG.get_or_init(|| {
206        let mut v: Vec<FnSpec> = Vec::new();
207        for list in [
208            math::specs(),
209            stat::specs(),
210            ets::specs(),
211            text::specs(),
212            date::specs(),
213            lookup::specs(),
214            dynamic::specs(),
215            financial::specs(),
216            info::specs(),
217            engineering::specs(),
218            database::specs(),
219        ] {
220            v.extend(list);
221        }
222        for s in &mut v {
223            s.value_args = lift::value_args(s.name);
224        }
225        let mut map = HashMap::with_capacity(v.len());
226        for (i, s) in v.iter().enumerate() {
227            map.entry(s.name).or_insert(i);
228        }
229        (v, map)
230    })
231}
232
233/// Looks a function up by name (case-insensitive; `_xlfn.`- and `_xlws.`-free names). A
234/// `_xludf.` name is user-defined and never found.
235pub fn lookup(name: &str) -> Option<&'static FnSpec> {
236    let mut n = name.trim().to_ascii_uppercase();
237    loop {
238        if let Some(r) = n.strip_prefix("_XLFN.") {
239            n = r.to_string();
240        } else if let Some(r) = n.strip_prefix("_XLWS.") {
241            n = r.to_string();
242        } else {
243            break;
244        }
245    }
246    let (v, map) = registry();
247    map.get(n.as_str()).and_then(|&i| v.get(i))
248}
249
250/// Every registered function.
251pub fn all() -> &'static [FnSpec] {
252    &registry().0
253}
254
255/// Calls a function with array lifting applied. Wrong arity gives `#VALUE!`.
256pub fn call(spec: &FnSpec, args: &[Arg], ctx: &mut dyn Ctx) -> Value {
257    if !spec.accepts(args.len()) {
258        return Value::Error(CellError::Value);
259    }
260    lift::call_lifted(spec, args, ctx)
261}
262
263#[cfg(test)]
264mod tests {
265    use super::*;
266
267    #[test]
268    fn registry_is_consistent() {
269        let mut seen = std::collections::HashSet::new();
270        for s in all() {
271            assert!(seen.insert(s.name), "duplicate function {}", s.name);
272            assert_eq!(s.name, s.name.to_ascii_uppercase(), "{} not upper-case", s.name);
273            assert!(s.min_args <= s.max_args, "{} arity", s.name);
274            assert!(!s.description.is_empty() && !s.signature.is_empty(), "{} docs", s.name);
275        }
276        println!("registered functions: {}", all().len());
277        assert!(all().len() >= 400);
278        assert!(lookup("_xlfn._xlws.sort").is_some_and(|s| s.name == "SORT"));
279        assert!(lookup("nosuchfn").is_none());
280        for s in all() {
281            assert!(lookup(&format!("_xlfn.{}", s.name.to_ascii_lowercase())).is_some_and(|x| x.name == s.name));
282        }
283    }
284
285    /// #2163: a NaN column index is refused (it used to become 0 and underflow to `usize::MAX`).
286    #[test]
287    fn nan_index_is_a_value_error() {
288        use crate::base::CellError;
289        use crate::functions::util::testutil::*;
290        let table = rf(arr(vec![vec![tv("Field"), tv("Other")], vec![nv(1.0), nv(2.0)]]));
291        let nan = || av(Value::Number(f64::NAN));
292        assert_eq!(ev("VLOOKUP", vec![n(1.0), table.clone(), nan()]), Value::Error(CellError::Value));
293        assert_eq!(ev("HLOOKUP", vec![t("Field"), table.clone(), nan()]), Value::Error(CellError::Value));
294        assert_eq!(ev("DSUM", vec![table.clone(), nan(), table.clone()]), Value::Error(CellError::Value));
295        assert_eq!(ev("VLOOKUP", vec![n(1.0), table, n(2.0)]), Value::Number(2.0));
296    }
297
298    /// #2162: REGEXREPLACE gives up on a too-long result while building it, and otherwise replaces
299    /// as before (every match, `$n` groups).
300    #[test]
301    fn regexreplace_bounds_its_result() {
302        use crate::base::CellError;
303        use crate::functions::util::testutil::*;
304        let many = "a".repeat(32_767);
305        assert_eq!(ev("REGEXREPLACE", vec![t(&many), t("."), t(&"b".repeat(32_767))]), Value::Error(CellError::Value));
306        assert_eq!(ev("REGEXREPLACE", vec![t(&many), t("a"), t("b")]), Value::text("b".repeat(32_767)));
307        assert_eq!(ev("REGEXREPLACE", vec![t("a1b22c"), t("[0-9]+"), t("#")]), Value::text("a#b#c"));
308        assert_eq!(ev("REGEXREPLACE", vec![t("2026-10-22"), t("(\\d+)-(\\d+)-(\\d+)"), t("$3/$2/$1")]), Value::text("22/10/2026"));
309        assert_eq!(ev("REGEXREPLACE", vec![t("abc"), t("x*"), t("-")]), Value::text("-a-b-c-"));
310        assert_eq!(ev("REGEXREPLACE", vec![t("a1b2"), t("[0-9]"), t("#"), n(2.0)]), Value::text("a1b#"));
311        // One match whose replacement repeats it 10,000 times: refused before it is built.
312        assert_eq!(ev("REGEXREPLACE", vec![t(&many), t("^.*$"), t(&"$0".repeat(10_000))]), Value::Error(CellError::Value));
313        assert_eq!(ev("REGEXREPLACE", vec![t(&many), t("^.*$"), t(&"$0".repeat(10_000)), n(1.0)]), Value::Error(CellError::Value));
314    }
315
316    #[test]
317    fn hostile_inputs_never_panic() {
318        use crate::base::{Array, CellError};
319        use crate::functions::util::testutil::*;
320        let mixed = Value::from(
321            Array::new(2, 3, vec![Value::Number(1.0), Value::from("x"), Value::Empty, Value::Bool(true), Value::Number(-1e300), Value::Number(0.5)])
322                .unwrap(),
323        );
324        let patterns: Vec<Arg> = vec![
325            n(0.0),
326            n(-1.0),
327            n(1e308),
328            n(-1e308),
329            n(1e10),
330            n(2.5),
331            // Numbers no formula should make, but code can (#2163).
332            av(Value::Number(f64::NAN)),
333            av(Value::Number(f64::INFINITY)),
334            t(""),
335            t("abc"),
336            t("*?~"),
337            b(true),
338            e(CellError::NA),
339            empty(),
340            av(mixed.clone()),
341            rf(mixed),
342            rf(col(&[3.0, 1.0, 2.0])),
343        ];
344        for spec in all() {
345            let max = spec.max_args.min(spec.min_args + 3);
346            for count in spec.min_args..=max {
347                for p in &patterns {
348                    let args = vec![p.clone(); count];
349                    let _ = call(spec, &args, &mut TestCtx::default());
350                }
351                // Mixed: cycle through patterns.
352                let args: Vec<Arg> = (0..count).map(|i| patterns[(i * 7 + count) % patterns.len()].clone()).collect();
353                let _ = call(spec, &args, &mut TestCtx::default());
354            }
355        }
356    }
357}
ravenrs_sheet/functions/mod.rs

ravenrs_sheet/functions/
mod.rs
