formualizer_eval/builtins/lookup/
dynamic.rs

1//! Dynamic / modern lookup & array helpers: XLOOKUP, FILTER, UNIQUE (initial sprint subset)
2//!
3//! Notes / Simplifications (documented for future refinement):
4//! - XLOOKUP supports: lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]
5//!   * match_mode: 0 exact (default), -1 exact-or-next-smaller, 1 exact-or-next-larger, 2 wildcard (basic * ?)
6//!   * search_mode: 1 forward (default), -1 reverse; (2 / -2 binary not yet implemented -> treated as 1 / -1)
7//!   * Wildcard mode (2) currently case-insensitive ASCII only; TODO: full Excel semantics, escape handling (~)
8//! - FILTER supports: array, include, [if_empty]; Shapes must be broadcast-compatible by rows (include is 1-D).
9//!   * include may be vertical column vector OR same sized 2D; we reduce any non-zero truthy cell to include row.
10//!   * if_empty omitted -> #CALC! per Excel when no matches.
11//! - UNIQUE supports: array, [by_col], [exactly_once]
12//!   * by_col TRUE -> operate column-wise returning unique columns (NYI -> returns #N/IMPL! if TRUE)
13//!   * exactly_once TRUE returns only values with count == 1 (supported in row-wise primitive set)
14//! - All functions return Array literal values (spills) – engine handles spill placement later.
15//!
16//! TODO(backlog):
17//! - Binary search for XLOOKUP approximate modes; currently linear scan.
18//! - Better type coercion parity with Excel (booleans/text vs numbers nuances).
19//! - Match unsorted detection for approximate modes (#N/A) and wildcard escaping.
20//! - PERFORMANCE: streaming FILTER without full materialization; UNIQUE using smallvec for tiny sets.
21
22use super::lookup_utils::{
23    approximate_select_ascending, equals_maybe_wildcard, find_exact_index, guard_sorted_ascending,
24};
25use crate::args::{ArgSchema, CoercionPolicy, ShapeKind};
26use crate::function::Function; // FnCaps imported via macro
27use crate::traits::{ArgumentHandle, FunctionContext};
28use formualizer_common::{ArgKind, ExcelError, ExcelErrorKind, LiteralValue};
29use formualizer_macros::func_caps;
30use std::collections::HashMap;
31
32/* ───────────────────────── helpers ───────────────────────── */
33
34fn to_values_flat(
35    arg: &ArgumentHandle,
36    ctx: &dyn FunctionContext,
37) -> Result<Vec<LiteralValue>, ExcelError> {
38    if let Ok(r) = arg.as_reference_or_eval() {
39        let mut out = Vec::new();
40        let sheet = "Sheet1"; // TODO: propagate actual sheet if present in reference
41        let rv = ctx.resolve_range_view(&r, sheet)?;
42        rv.for_each_cell(&mut |v| {
43            out.push(v.clone());
44            Ok(())
45        })?;
46        Ok(out)
47    } else {
48        Ok(vec![arg.value()?.as_ref().clone()])
49    }
50}
51
52fn to_rows_2d(
53    arg: &ArgumentHandle,
54    ctx: &dyn FunctionContext,
55) -> Result<Vec<Vec<LiteralValue>>, ExcelError> {
56    if let Ok(r) = arg.as_reference_or_eval() {
57        let mut rows: Vec<Vec<LiteralValue>> = Vec::new();
58        let sheet = "Sheet1";
59        let rv = ctx.resolve_range_view(&r, sheet)?;
60        rv.for_each_row(&mut |row| {
61            rows.push(row.to_vec());
62            Ok(())
63        })?;
64        Ok(rows)
65    } else {
66        match arg.value()?.as_ref() {
67            LiteralValue::Array(a) => Ok(a.clone()),
68            v => Ok(vec![vec![v.clone()]]),
69        }
70    }
71}
72
73pub fn super_wildcard_match(pattern: &str, text: &str) -> bool {
74    // public for shared lookup utils
75    // Excel-style wildcards with escape (~): * any seq, ? single char, ~ escapes next (*, ?, ~)
76    // Implement non-recursive DP for performance & to support escapes.
77    #[derive(Clone, Copy, Debug)]
78    enum Token<'a> {
79        AnySeq,
80        AnyChar,
81        Lit(&'a str),
82    }
83    let mut tokens: Vec<Token> = Vec::new();
84    let mut i = 0;
85    let bytes = pattern.as_bytes();
86    let mut lit_start = 0;
87    while i < bytes.len() {
88        match bytes[i] {
89            b'~' => {
90                // escape next if present
91                if i + 1 < bytes.len() {
92                    // flush pending literal
93                    if lit_start < i {
94                        tokens.push(Token::Lit(&pattern[lit_start..i]));
95                    }
96                    tokens.push(Token::Lit(&pattern[i + 1..i + 2]));
97                    i += 2;
98                    lit_start = i;
99                } else {
100                    // trailing ~ treated literal
101                    i += 1;
102                }
103            }
104            b'*' => {
105                if lit_start < i {
106                    tokens.push(Token::Lit(&pattern[lit_start..i]));
107                }
108                tokens.push(Token::AnySeq);
109                i += 1;
110                lit_start = i;
111            }
112            b'?' => {
113                if lit_start < i {
114                    tokens.push(Token::Lit(&pattern[lit_start..i]));
115                }
116                tokens.push(Token::AnyChar);
117                i += 1;
118                lit_start = i;
119            }
120            _ => i += 1,
121        }
122    }
123    if lit_start < bytes.len() {
124        tokens.push(Token::Lit(&pattern[lit_start..]));
125    }
126    // Simplify consecutive AnySeq
127    let mut compact: Vec<Token> = Vec::new();
128    for t in tokens {
129        match t {
130            Token::AnySeq => {
131                if !matches!(compact.last(), Some(Token::AnySeq)) {
132                    compact.push(t);
133                }
134            }
135            _ => compact.push(t),
136        }
137    }
138    // Backtracking matcher
139    fn match_tokens<'a>(tokens: &[Token<'a>], text: &str) -> bool {
140        fn eq_icase(a: &str, b: &str) -> bool {
141            a.eq_ignore_ascii_case(b)
142        }
143        // Convert Lit tokens into lowercase for quick compare
144        let mut ti = 0;
145        let tb = tokens;
146        // Use manual stack for backtracking when encountering AnySeq
147        let mut backtrack: Vec<(usize, usize)> = Vec::new(); // (token_index, text_index after consuming 1 more char by *)
148        let text_bytes = text.as_bytes();
149        let mut si = 0; // text index
150        loop {
151            if ti == tb.len() {
152                // tokens consumed
153                if si == text_bytes.len() {
154                    return true;
155                }
156                // Maybe backtrack
157            } else {
158                match tb[ti] {
159                    Token::AnySeq => {
160                        // try to match zero chars first
161                        ti += 1;
162                        backtrack.push((ti - 1, si + 1));
163                        continue;
164                    }
165                    Token::AnyChar => {
166                        if si < text_bytes.len() {
167                            ti += 1;
168                            si += 1;
169                            continue;
170                        }
171                    }
172                    Token::Lit(l) => {
173                        let l_len = l.len();
174                        if si + l_len <= text_bytes.len() && eq_icase(&text[si..si + l_len], l) {
175                            ti += 1;
176                            si += l_len;
177                            continue;
178                        }
179                    }
180                }
181            }
182            // failed match; attempt backtrack
183            if let Some((tok_star, new_si)) = backtrack.pop() {
184                if new_si <= text_bytes.len() {
185                    ti = tok_star + 1;
186                    si = new_si;
187                    continue;
188                } else {
189                    continue;
190                }
191            }
192            return false;
193        }
194    }
195    match_tokens(&compact, text)
196}
197
198fn value_equal(a: &LiteralValue, b: &LiteralValue, wildcard: bool) -> bool {
199    match (a, b) {
200        (LiteralValue::Int(i1), LiteralValue::Int(i2)) => i1 == i2,
201        (LiteralValue::Number(n1), LiteralValue::Number(n2)) => (n1 - n2).abs() < 1e-12,
202        (LiteralValue::Int(i), LiteralValue::Number(n))
203        | (LiteralValue::Number(n), LiteralValue::Int(i)) => (*i as f64 - *n).abs() < 1e-12,
204        (LiteralValue::Text(s1), LiteralValue::Text(s2)) => {
205            if wildcard && (s1.contains('*') || s1.contains('?') || s1.contains('~')) {
206                super_wildcard_match(s1, s2)
207            } else {
208                s1.eq_ignore_ascii_case(s2)
209            }
210        }
211        (LiteralValue::Boolean(b1), LiteralValue::Boolean(b2)) => b1 == b2,
212        _ => false,
213    }
214}
215
216/* ───────────────────────── XLOOKUP() ───────────────────────── */
217
218#[derive(Debug)]
219pub struct XLookupFn;
220
221impl Function for XLookupFn {
222    func_caps!(PURE, LOOKUP);
223    fn name(&self) -> &'static str {
224        "XLOOKUP"
225    }
226    fn min_args(&self) -> usize {
227        3
228    }
229    fn variadic(&self) -> bool {
230        true
231    }
232    fn arg_schema(&self) -> &'static [ArgSchema] {
233        use once_cell::sync::Lazy;
234        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
235            vec![
236                // lookup_value
237                ArgSchema {
238                    kinds: smallvec::smallvec![ArgKind::Any],
239                    required: true,
240                    by_ref: false,
241                    shape: ShapeKind::Scalar,
242                    coercion: CoercionPolicy::None,
243                    max: None,
244                    repeating: None,
245                    default: None,
246                },
247                // lookup_array (range)
248                ArgSchema {
249                    kinds: smallvec::smallvec![ArgKind::Range],
250                    required: true,
251                    by_ref: true,
252                    shape: ShapeKind::Range,
253                    coercion: CoercionPolicy::None,
254                    max: None,
255                    repeating: None,
256                    default: None,
257                },
258                // return_array (range)
259                ArgSchema {
260                    kinds: smallvec::smallvec![ArgKind::Range],
261                    required: true,
262                    by_ref: true,
263                    shape: ShapeKind::Range,
264                    coercion: CoercionPolicy::None,
265                    max: None,
266                    repeating: None,
267                    default: None,
268                },
269                // if_not_found (any optional)
270                ArgSchema {
271                    kinds: smallvec::smallvec![ArgKind::Any],
272                    required: false,
273                    by_ref: false,
274                    shape: ShapeKind::Scalar,
275                    coercion: CoercionPolicy::None,
276                    max: None,
277                    repeating: None,
278                    default: None,
279                },
280                // match_mode (number) default 0
281                ArgSchema {
282                    kinds: smallvec::smallvec![ArgKind::Number],
283                    required: false,
284                    by_ref: false,
285                    shape: ShapeKind::Scalar,
286                    coercion: CoercionPolicy::NumberLenientText,
287                    max: None,
288                    repeating: None,
289                    default: Some(LiteralValue::Int(0)),
290                },
291                // search_mode (number) default 1
292                ArgSchema {
293                    kinds: smallvec::smallvec![ArgKind::Number],
294                    required: false,
295                    by_ref: false,
296                    shape: ShapeKind::Scalar,
297                    coercion: CoercionPolicy::NumberLenientText,
298                    max: None,
299                    repeating: None,
300                    default: Some(LiteralValue::Int(1)),
301                },
302            ]
303        });
304        &SCHEMA
305    }
306    fn eval_scalar<'a, 'b>(
307        &self,
308        args: &'a [ArgumentHandle<'a, 'b>],
309        ctx: &dyn FunctionContext,
310    ) -> Result<LiteralValue, ExcelError> {
311        if args.len() < 3 {
312            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
313        }
314        let lookup_value = args[0].value()?;
315        if let LiteralValue::Error(e) = lookup_value.as_ref() {
316            return Ok(LiteralValue::Error(e.clone()));
317        }
318        // Materialize lookup & return arrays with both flat & 2D for orientation logic
319        let lookup_rows = to_rows_2d(&args[1], ctx)?; // could be 1 x N or N x 1
320        let ret_rows = to_rows_2d(&args[2], ctx)?; // may be multi-column/row slice
321        // Derive flat list of candidates (respect row/col orientation). If lookup_rows has 1 row -> horizontal; else if 1 col -> vertical
322        let vertical = lookup_rows.len() > 1; // heuristic: multi-row => vertical orientation
323        let lookup_vals: Vec<LiteralValue> = if vertical {
324            // Each row should have at least 1 col; use first column
325            lookup_rows
326                .iter()
327                .map(|r| r.first().cloned().unwrap_or(LiteralValue::Empty))
328                .collect()
329        } else {
330            // horizontal
331            lookup_rows.first().cloned().unwrap_or_default()
332        };
333        if lookup_vals.is_empty() {
334            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
335        }
336        let match_mode = if args.len() >= 5 {
337            match args[4].value()?.as_ref() {
338                LiteralValue::Int(i) => *i,
339                LiteralValue::Number(n) => *n as i64,
340                _ => 0,
341            }
342        } else {
343            0
344        };
345        let search_mode = if args.len() >= 6 {
346            match args[5].value()?.as_ref() {
347                LiteralValue::Int(i) => *i,
348                LiteralValue::Number(n) => *n as i64,
349                _ => 1,
350            }
351        } else {
352            1
353        };
354        let wildcard = match_mode == 2;
355        let indices: Box<dyn Iterator<Item = usize>> = if search_mode == -1 {
356            Box::new((0..lookup_vals.len()).rev())
357        } else {
358            Box::new(0..lookup_vals.len())
359        };
360        let mut found: Option<usize> = None;
361        if match_mode == 0 || wildcard {
362            // respect search direction
363            if search_mode == -1 {
364                for i in (0..lookup_vals.len()).rev() {
365                    if equals_maybe_wildcard(lookup_value.as_ref(), &lookup_vals[i], wildcard) {
366                        found = Some(i);
367                        break;
368                    }
369                }
370            } else {
371                found = find_exact_index(&lookup_vals, lookup_value.as_ref(), wildcard);
372            }
373        } else if match_mode == -1 || match_mode == 1 {
374            if let Err(e) = guard_sorted_ascending(&lookup_vals) {
375                return Ok(LiteralValue::Error(e));
376            }
377            found = approximate_select_ascending(
378                &lookup_vals,
379                lookup_value.as_ref(),
380                match_mode as i32,
381            );
382        } else {
383            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
384        }
385        if let Some(idx) = found {
386            if ret_rows.is_empty() {
387                return Ok(LiteralValue::Empty);
388            }
389            if vertical {
390                if idx < ret_rows.len() {
391                    let row = &ret_rows[idx];
392                    if row.len() == 1 {
393                        return Ok(row[0].clone());
394                    }
395                    return Ok(LiteralValue::Array(vec![row.clone()]));
396                }
397            } else {
398                // horizontal orientation: treat idx as column; build column vector
399                let height = ret_rows.len();
400                if height == 1 {
401                    // single row -> scalar
402                    let row = &ret_rows[0];
403                    if idx < row.len() {
404                        return Ok(row[idx].clone());
405                    }
406                } else {
407                    let mut col: Vec<Vec<LiteralValue>> = Vec::new();
408                    for r in &ret_rows {
409                        col.push(vec![r.get(idx).cloned().unwrap_or(LiteralValue::Empty)]);
410                    }
411                    return Ok(LiteralValue::Array(col));
412                }
413            }
414            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
415        }
416        if args.len() >= 4 {
417            return args[3].value().map(|c| c.into_owned());
418        }
419        Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)))
420    }
421}
422
423/* ───────────────────────── FILTER() ───────────────────────── */
424
425#[derive(Debug)]
426pub struct FilterFn;
427impl Function for FilterFn {
428    func_caps!(PURE);
429    fn name(&self) -> &'static str {
430        "FILTER"
431    }
432    fn min_args(&self) -> usize {
433        2
434    }
435    fn variadic(&self) -> bool {
436        true
437    }
438    fn arg_schema(&self) -> &'static [ArgSchema] {
439        use once_cell::sync::Lazy;
440        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
441            vec![
442                // array
443                ArgSchema {
444                    kinds: smallvec::smallvec![ArgKind::Range],
445                    required: true,
446                    by_ref: true,
447                    shape: ShapeKind::Range,
448                    coercion: CoercionPolicy::None,
449                    max: None,
450                    repeating: None,
451                    default: None,
452                },
453                // include
454                ArgSchema {
455                    kinds: smallvec::smallvec![ArgKind::Range],
456                    required: true,
457                    by_ref: true,
458                    shape: ShapeKind::Range,
459                    coercion: CoercionPolicy::None,
460                    max: None,
461                    repeating: None,
462                    default: None,
463                },
464                // if_empty optional scalar
465                ArgSchema {
466                    kinds: smallvec::smallvec![ArgKind::Any],
467                    required: false,
468                    by_ref: false,
469                    shape: ShapeKind::Scalar,
470                    coercion: CoercionPolicy::None,
471                    max: None,
472                    repeating: None,
473                    default: None,
474                },
475            ]
476        });
477        &SCHEMA
478    }
479    fn eval_scalar<'a, 'b>(
480        &self,
481        args: &'a [ArgumentHandle<'a, 'b>],
482        ctx: &dyn FunctionContext,
483    ) -> Result<LiteralValue, ExcelError> {
484        if args.len() < 2 {
485            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
486        }
487        let rows = to_rows_2d(&args[0], ctx)?;
488        if rows.is_empty() {
489            return Ok(LiteralValue::Array(vec![]));
490        }
491        let include_rows = to_rows_2d(&args[1], ctx)?;
492        let mut result: Vec<Vec<LiteralValue>> = Vec::new();
493        let row_count = rows.len();
494        for (i, row) in rows.iter().enumerate() {
495            let include = if include_rows.len() == row_count {
496                include_rows[i].iter().any(|v| v.is_truthy())
497            } else if include_rows.len() == 1 {
498                include_rows[0].iter().any(|v| v.is_truthy())
499            } else {
500                return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
501            };
502            if include {
503                result.push(row.clone());
504            }
505        }
506        if result.is_empty() {
507            if args.len() >= 3 {
508                return args[2].value().map(|c| c.into_owned());
509            }
510            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Calc)));
511        }
512        if result.len() == 1 && result[0].len() == 1 {
513            return Ok(result[0][0].clone());
514        }
515        Ok(LiteralValue::Array(result))
516    }
517}
518
519/* ───────────────────────── UNIQUE() ───────────────────────── */
520
521#[derive(Debug)]
522pub struct UniqueFn;
523impl Function for UniqueFn {
524    func_caps!(PURE);
525    fn name(&self) -> &'static str {
526        "UNIQUE"
527    }
528    fn min_args(&self) -> usize {
529        1
530    }
531    fn variadic(&self) -> bool {
532        true
533    }
534    fn arg_schema(&self) -> &'static [ArgSchema] {
535        use once_cell::sync::Lazy;
536        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
537            vec![
538                ArgSchema {
539                    kinds: smallvec::smallvec![ArgKind::Range],
540                    required: true,
541                    by_ref: true,
542                    shape: ShapeKind::Range,
543                    coercion: CoercionPolicy::None,
544                    max: None,
545                    repeating: None,
546                    default: None,
547                },
548                ArgSchema {
549                    kinds: smallvec::smallvec![ArgKind::Logical],
550                    required: false,
551                    by_ref: false,
552                    shape: ShapeKind::Scalar,
553                    coercion: CoercionPolicy::Logical,
554                    max: None,
555                    repeating: None,
556                    default: Some(LiteralValue::Boolean(false)),
557                },
558                ArgSchema {
559                    kinds: smallvec::smallvec![ArgKind::Logical],
560                    required: false,
561                    by_ref: false,
562                    shape: ShapeKind::Scalar,
563                    coercion: CoercionPolicy::Logical,
564                    max: None,
565                    repeating: None,
566                    default: Some(LiteralValue::Boolean(false)),
567                },
568            ]
569        });
570        &SCHEMA
571    }
572    fn eval_scalar<'a, 'b>(
573        &self,
574        args: &'a [ArgumentHandle<'a, 'b>],
575        ctx: &dyn FunctionContext,
576    ) -> Result<LiteralValue, ExcelError> {
577        let rows = to_rows_2d(&args[0], ctx)?;
578        if rows.is_empty() {
579            return Ok(LiteralValue::Array(vec![]));
580        }
581        let by_col = if args.len() >= 2 {
582            matches!(args[1].value()?.as_ref(), LiteralValue::Boolean(true))
583        } else {
584            false
585        };
586        let exactly_once = if args.len() >= 3 {
587            matches!(args[2].value()?.as_ref(), LiteralValue::Boolean(true))
588        } else {
589            false
590        };
591        if by_col {
592            // Transpose columns to vectors and dedup columns
593            let width = rows[0].len();
594            // Ensure all rows same width
595            if rows.iter().any(|r| r.len() != width) {
596                return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
597            }
598            #[derive(Hash, Eq, PartialEq, Clone)]
599            struct ColKey(Vec<LiteralValue>);
600            let mut order: Vec<ColKey> = Vec::new();
601            let mut counts: HashMap<ColKey, usize> = HashMap::new();
602            for c in 0..width {
603                let key = ColKey(rows.iter().map(|r| r[c].clone()).collect());
604                if !counts.contains_key(&key) {
605                    order.push(key.clone());
606                }
607                *counts.entry(key).or_insert(0) += 1;
608            }
609            let mut out: Vec<Vec<LiteralValue>> = Vec::new();
610            for k in order {
611                if !exactly_once || counts.get(&k) == Some(&1) {
612                    out.push(k.0);
613                }
614            }
615            if out.len() == 1 && out[0].len() == 1 {
616                return Ok(out[0][0].clone());
617            }
618            return Ok(LiteralValue::Array(out));
619        }
620        // Row-wise uniqueness (entire rows)
621        #[derive(Hash, Eq, PartialEq, Clone)]
622        struct RowKey(Vec<LiteralValue>);
623        let mut order: Vec<RowKey> = Vec::new();
624        let mut counts: HashMap<RowKey, usize> = HashMap::new();
625        for r in &rows {
626            let key = RowKey(r.clone());
627            if !counts.contains_key(&key) {
628                order.push(key.clone());
629            }
630            *counts.entry(key).or_insert(0) += 1;
631        }
632        let mut out: Vec<Vec<LiteralValue>> = Vec::new();
633        for k in order {
634            if !exactly_once || counts.get(&k) == Some(&1) {
635                out.push(k.0);
636            }
637        }
638        if out.len() == 1 && out[0].len() == 1 {
639            return Ok(out[0][0].clone());
640        }
641        Ok(LiteralValue::Array(out))
642    }
643}
644
645pub fn register_builtins() {
646    use crate::function_registry::register_function;
647    use std::sync::Arc;
648    register_function(Arc::new(XLookupFn));
649    register_function(Arc::new(FilterFn));
650    register_function(Arc::new(UniqueFn));
651    register_function(Arc::new(SequenceFn));
652    register_function(Arc::new(TransposeFn));
653    register_function(Arc::new(TakeFn));
654    register_function(Arc::new(DropFn));
655}
656
657/* ───────────────────────── SEQUENCE() ───────────────────────── */
658
659#[derive(Debug)]
660pub struct SequenceFn;
661impl Function for SequenceFn {
662    func_caps!(PURE);
663    fn name(&self) -> &'static str {
664        "SEQUENCE"
665    }
666    fn min_args(&self) -> usize {
667        1
668    }
669    fn variadic(&self) -> bool {
670        true
671    }
672    fn arg_schema(&self) -> &'static [ArgSchema] {
673        use once_cell::sync::Lazy;
674        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
675            vec![
676                // rows
677                ArgSchema {
678                    kinds: smallvec::smallvec![ArgKind::Number],
679                    required: true,
680                    by_ref: false,
681                    shape: ShapeKind::Scalar,
682                    coercion: CoercionPolicy::NumberLenientText,
683                    max: None,
684                    repeating: None,
685                    default: None,
686                },
687                // columns (default 1)
688                ArgSchema {
689                    kinds: smallvec::smallvec![ArgKind::Number],
690                    required: false,
691                    by_ref: false,
692                    shape: ShapeKind::Scalar,
693                    coercion: CoercionPolicy::NumberLenientText,
694                    max: None,
695                    repeating: None,
696                    default: Some(LiteralValue::Int(1)),
697                },
698                // start (default 1)
699                ArgSchema {
700                    kinds: smallvec::smallvec![ArgKind::Number],
701                    required: false,
702                    by_ref: false,
703                    shape: ShapeKind::Scalar,
704                    coercion: CoercionPolicy::NumberLenientText,
705                    max: None,
706                    repeating: None,
707                    default: Some(LiteralValue::Int(1)),
708                },
709                // step (default 1)
710                ArgSchema {
711                    kinds: smallvec::smallvec![ArgKind::Number],
712                    required: false,
713                    by_ref: false,
714                    shape: ShapeKind::Scalar,
715                    coercion: CoercionPolicy::NumberLenientText,
716                    max: None,
717                    repeating: None,
718                    default: Some(LiteralValue::Int(1)),
719                },
720            ]
721        });
722        &SCHEMA
723    }
724    fn eval_scalar<'a, 'b>(
725        &self,
726        args: &'a [ArgumentHandle<'a, 'b>],
727        _ctx: &dyn FunctionContext,
728    ) -> Result<LiteralValue, ExcelError> {
729        // Extract numbers (allow float but coerce to i64 for dimensions)
730        let num = |a: &ArgumentHandle| -> Result<f64, ExcelError> {
731            Ok(match a.value()?.as_ref() {
732                LiteralValue::Int(i) => *i as f64,
733                LiteralValue::Number(n) => *n,
734                _other => {
735                    return Err(ExcelError::new(ExcelErrorKind::Value));
736                }
737            })
738        };
739        let rows_f = num(&args[0])?;
740        let rows = rows_f as i64;
741        let cols = if args.len() >= 2 {
742            num(&args[1])? as i64
743        } else {
744            1
745        };
746        let start = if args.len() >= 3 { num(&args[2])? } else { 1.0 };
747        let step = if args.len() >= 4 { num(&args[3])? } else { 1.0 };
748        if rows <= 0 || cols <= 0 {
749            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
750        }
751        let total = rows.saturating_mul(cols);
752        // TODO(perf): guard extremely large allocations (#NUM!).
753        let mut out: Vec<Vec<LiteralValue>> = Vec::with_capacity(rows as usize);
754        let mut current = start;
755        for _r in 0..rows {
756            let mut row_vec: Vec<LiteralValue> = Vec::with_capacity(cols as usize);
757            for _c in 0..cols {
758                // Use Int when value integral & within i64 range
759                if (current.fract().abs() < 1e-12) && current.abs() < (i64::MAX as f64) {
760                    row_vec.push(LiteralValue::Int(current as i64));
761                } else {
762                    row_vec.push(LiteralValue::Number(current));
763                }
764                current += step;
765            }
766            out.push(row_vec);
767        }
768        if out.len() == 1 && out[0].len() == 1 {
769            return Ok(out[0][0].clone());
770        }
771        Ok(LiteralValue::Array(out))
772    }
773}
774
775/* ───────────────────────── TRANSPOSE() ───────────────────────── */
776
777#[derive(Debug)]
778pub struct TransposeFn;
779impl Function for TransposeFn {
780    func_caps!(PURE);
781    fn name(&self) -> &'static str {
782        "TRANSPOSE"
783    }
784    fn min_args(&self) -> usize {
785        1
786    }
787    fn variadic(&self) -> bool {
788        false
789    }
790    fn arg_schema(&self) -> &'static [ArgSchema] {
791        use once_cell::sync::Lazy;
792        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
793            vec![ArgSchema {
794                kinds: smallvec::smallvec![ArgKind::Range],
795                required: true,
796                by_ref: true,
797                shape: ShapeKind::Range,
798                coercion: CoercionPolicy::None,
799                max: None,
800                repeating: None,
801                default: None,
802            }]
803        });
804        &SCHEMA
805    }
806    fn eval_scalar<'a, 'b>(
807        &self,
808        args: &'a [ArgumentHandle<'a, 'b>],
809        ctx: &dyn FunctionContext,
810    ) -> Result<LiteralValue, ExcelError> {
811        let rows = to_rows_2d(&args[0], ctx)?;
812        if rows.is_empty() {
813            return Ok(LiteralValue::Array(vec![]));
814        }
815        let width = rows[0].len();
816        // Validate rectangular
817        if rows.iter().any(|r| r.len() != width) {
818            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
819        }
820        if width == 0 {
821            return Ok(LiteralValue::Array(vec![]));
822        }
823        let mut out: Vec<Vec<LiteralValue>> = vec![Vec::with_capacity(rows.len()); width];
824        for row in &rows {
825            for (c, cell) in row.iter().enumerate() {
826                out[c].push(cell.clone());
827            }
828        }
829        if out.len() == 1 && out[0].len() == 1 {
830            return Ok(out[0][0].clone());
831        }
832        Ok(LiteralValue::Array(out))
833    }
834}
835
836/* ───────────────────────── TAKE() ───────────────────────── */
837
838#[derive(Debug)]
839pub struct TakeFn;
840impl Function for TakeFn {
841    func_caps!(PURE);
842    fn name(&self) -> &'static str {
843        "TAKE"
844    }
845    fn min_args(&self) -> usize {
846        2
847    }
848    fn variadic(&self) -> bool {
849        true
850    }
851    fn arg_schema(&self) -> &'static [ArgSchema] {
852        use once_cell::sync::Lazy;
853        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
854            vec![
855                ArgSchema {
856                    kinds: smallvec::smallvec![ArgKind::Range],
857                    required: true,
858                    by_ref: true,
859                    shape: ShapeKind::Range,
860                    coercion: CoercionPolicy::None,
861                    max: None,
862                    repeating: None,
863                    default: None,
864                },
865                ArgSchema {
866                    kinds: smallvec::smallvec![ArgKind::Number],
867                    required: true,
868                    by_ref: false,
869                    shape: ShapeKind::Scalar,
870                    coercion: CoercionPolicy::NumberLenientText,
871                    max: None,
872                    repeating: None,
873                    default: None,
874                },
875                ArgSchema {
876                    kinds: smallvec::smallvec![ArgKind::Number],
877                    required: false,
878                    by_ref: false,
879                    shape: ShapeKind::Scalar,
880                    coercion: CoercionPolicy::NumberLenientText,
881                    max: None,
882                    repeating: None,
883                    default: None,
884                },
885            ]
886        });
887        &SCHEMA
888    }
889    fn eval_scalar<'a, 'b>(
890        &self,
891        args: &'a [ArgumentHandle<'a, 'b>],
892        ctx: &dyn FunctionContext,
893    ) -> Result<LiteralValue, ExcelError> {
894        let rows = to_rows_2d(&args[0], ctx)?;
895        if rows.is_empty() {
896            return Ok(LiteralValue::Array(vec![]));
897        }
898        let height = rows.len() as i64;
899        let width = rows[0].len() as i64;
900        if rows.iter().any(|r| r.len() as i64 != width) {
901            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
902        }
903        let num = |a: &ArgumentHandle| -> Result<i64, ExcelError> {
904            Ok(match a.value()?.as_ref() {
905                LiteralValue::Int(i) => *i,
906                LiteralValue::Number(n) => *n as i64,
907                _ => 0,
908            })
909        };
910        let take_rows = num(&args[1])?;
911        let take_cols = if args.len() >= 3 {
912            Some(num(&args[2])?)
913        } else {
914            None
915        };
916        if take_rows.abs() > height {
917            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
918        }
919        let row_start;
920        let row_end; // inclusive-exclusive
921        if take_rows >= 0 {
922            row_start = 0;
923            row_end = take_rows as usize;
924        } else {
925            row_start = (height + take_rows) as usize;
926            row_end = height as usize;
927        }
928        let (col_start, col_end) = if let Some(tc) = take_cols {
929            if tc.abs() > width {
930                return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
931            }
932            if tc >= 0 {
933                (0, tc as usize)
934            } else {
935                ((width + tc) as usize, width as usize)
936            }
937        } else {
938            (0, width as usize)
939        };
940        let mut out: Vec<Vec<LiteralValue>> = Vec::with_capacity(row_end - row_start);
941        for row in rows.iter().skip(row_start).take(row_end - row_start) {
942            out.push(row[col_start..col_end].to_vec());
943        }
944        if out.is_empty() {
945            return Ok(LiteralValue::Array(vec![]));
946        }
947        if out.len() == 1 && out[0].len() == 1 {
948            return Ok(out[0][0].clone());
949        }
950        Ok(LiteralValue::Array(out))
951    }
952}
953
954/* ───────────────────────── DROP() ───────────────────────── */
955
956#[derive(Debug)]
957pub struct DropFn;
958impl Function for DropFn {
959    func_caps!(PURE);
960    fn name(&self) -> &'static str {
961        "DROP"
962    }
963    fn min_args(&self) -> usize {
964        2
965    }
966    fn variadic(&self) -> bool {
967        true
968    }
969    fn arg_schema(&self) -> &'static [ArgSchema] {
970        use once_cell::sync::Lazy;
971        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
972            vec![
973                ArgSchema {
974                    kinds: smallvec::smallvec![ArgKind::Range],
975                    required: true,
976                    by_ref: true,
977                    shape: ShapeKind::Range,
978                    coercion: CoercionPolicy::None,
979                    max: None,
980                    repeating: None,
981                    default: None,
982                },
983                ArgSchema {
984                    kinds: smallvec::smallvec![ArgKind::Number],
985                    required: true,
986                    by_ref: false,
987                    shape: ShapeKind::Scalar,
988                    coercion: CoercionPolicy::NumberLenientText,
989                    max: None,
990                    repeating: None,
991                    default: None,
992                },
993                ArgSchema {
994                    kinds: smallvec::smallvec![ArgKind::Number],
995                    required: false,
996                    by_ref: false,
997                    shape: ShapeKind::Scalar,
998                    coercion: CoercionPolicy::NumberLenientText,
999                    max: None,
1000                    repeating: None,
1001                    default: None,
1002                },
1003            ]
1004        });
1005        &SCHEMA
1006    }
1007    fn eval_scalar<'a, 'b>(
1008        &self,
1009        args: &'a [ArgumentHandle<'a, 'b>],
1010        ctx: &dyn FunctionContext,
1011    ) -> Result<LiteralValue, ExcelError> {
1012        let rows = to_rows_2d(&args[0], ctx)?;
1013        if rows.is_empty() {
1014            return Ok(LiteralValue::Array(vec![]));
1015        }
1016        let height = rows.len() as i64;
1017        let width = rows[0].len() as i64;
1018        if rows.iter().any(|r| r.len() as i64 != width) {
1019            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
1020        }
1021        let num = |a: &ArgumentHandle| -> Result<i64, ExcelError> {
1022            Ok(match a.value()?.as_ref() {
1023                LiteralValue::Int(i) => *i,
1024                LiteralValue::Number(n) => *n as i64,
1025                _ => 0,
1026            })
1027        };
1028        let drop_rows = num(&args[1])?;
1029        let drop_cols = if args.len() >= 3 {
1030            Some(num(&args[2])?)
1031        } else {
1032            None
1033        };
1034        let (row_start, row_end) = if drop_rows >= 0 {
1035            ((drop_rows as usize).min(height as usize), height as usize)
1036        } else {
1037            (0, ((height + drop_rows).max(0) as usize))
1038        };
1039        let (col_start, col_end) = if let Some(dc) = drop_cols {
1040            if dc >= 0 {
1041                (((dc as usize).min(width as usize)), width as usize)
1042            } else {
1043                (0, ((width + dc).max(0) as usize))
1044            }
1045        } else {
1046            (0, width as usize)
1047        };
1048        if row_start >= row_end || col_start >= col_end {
1049            return Ok(LiteralValue::Array(vec![]));
1050        }
1051        let mut out: Vec<Vec<LiteralValue>> = Vec::with_capacity(row_end - row_start);
1052        for row in rows.iter().skip(row_start).take(row_end - row_start) {
1053            out.push(row[col_start..col_end].to_vec());
1054        }
1055        if out.len() == 1 && out[0].len() == 1 {
1056            return Ok(out[0][0].clone());
1057        }
1058        Ok(LiteralValue::Array(out))
1059    }
1060}
1061
1062/* ───────────────────────── tests ───────────────────────── */
1063
1064#[cfg(test)]
1065mod tests {
1066    use super::*;
1067    use crate::test_workbook::TestWorkbook;
1068    use crate::traits::ArgumentHandle;
1069    use formualizer_parse::parser::{ASTNode, ASTNodeType, ReferenceType};
1070    use std::sync::Arc;
1071    fn lit(v: LiteralValue) -> ASTNode {
1072        ASTNode::new(ASTNodeType::Literal(v), None)
1073    }
1074
1075    #[test]
1076    fn xlookup_basic_exact_and_if_not_found() {
1077        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
1078        let wb = wb
1079            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("a".into()))
1080            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("b".into()))
1081            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(10))
1082            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(20));
1083        let ctx = wb.interpreter();
1084        let lookup_range = ASTNode::new(
1085            ASTNodeType::Reference {
1086                original: "A1:A2".into(),
1087                reference: ReferenceType::Range {
1088                    sheet: None,
1089                    start_row: Some(1),
1090                    start_col: Some(1),
1091                    end_row: Some(2),
1092                    end_col: Some(1),
1093                },
1094            },
1095            None,
1096        );
1097        let return_range = ASTNode::new(
1098            ASTNodeType::Reference {
1099                original: "B1:B2".into(),
1100                reference: ReferenceType::Range {
1101                    sheet: None,
1102                    start_row: Some(1),
1103                    start_col: Some(2),
1104                    end_row: Some(2),
1105                    end_col: Some(2),
1106                },
1107            },
1108            None,
1109        );
1110        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
1111        let key_b = lit(LiteralValue::Text("b".into()));
1112        let args = vec![
1113            ArgumentHandle::new(&key_b, &ctx),
1114            ArgumentHandle::new(&lookup_range, &ctx),
1115            ArgumentHandle::new(&return_range, &ctx),
1116        ];
1117        let v = f.dispatch(&args, &ctx.function_context(None)).unwrap();
1118        assert_eq!(v, LiteralValue::Int(20));
1119        let key_missing = lit(LiteralValue::Text("z".into()));
1120        let if_nf = lit(LiteralValue::Text("NF".into()));
1121        let args_nf = vec![
1122            ArgumentHandle::new(&key_missing, &ctx),
1123            ArgumentHandle::new(&lookup_range, &ctx),
1124            ArgumentHandle::new(&return_range, &ctx),
1125            ArgumentHandle::new(&if_nf, &ctx),
1126        ];
1127        let v_nf = f.dispatch(&args_nf, &ctx.function_context(None)).unwrap();
1128        assert_eq!(v_nf, LiteralValue::Text("NF".into()));
1129    }
1130
1131    #[test]
1132    fn xlookup_match_modes_next_smaller_larger() {
1133        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
1134        let wb = wb
1135            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
1136            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
1137            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
1138            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(1))
1139            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(2))
1140            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(3));
1141        let ctx = wb.interpreter();
1142        let lookup_range = ASTNode::new(
1143            ASTNodeType::Reference {
1144                original: "A1:A3".into(),
1145                reference: ReferenceType::Range {
1146                    sheet: None,
1147                    start_row: Some(1),
1148                    start_col: Some(1),
1149                    end_row: Some(3),
1150                    end_col: Some(1),
1151                },
1152            },
1153            None,
1154        );
1155        let return_range = ASTNode::new(
1156            ASTNodeType::Reference {
1157                original: "B1:B3".into(),
1158                reference: ReferenceType::Range {
1159                    sheet: None,
1160                    start_row: Some(1),
1161                    start_col: Some(2),
1162                    end_row: Some(3),
1163                    end_col: Some(2),
1164                },
1165            },
1166            None,
1167        );
1168        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
1169        let needle_25 = lit(LiteralValue::Int(25));
1170        let mm_next_smaller = lit(LiteralValue::Int(-1));
1171        let nf_text = lit(LiteralValue::Text("NF".into()));
1172        let args_smaller = vec![
1173            ArgumentHandle::new(&needle_25, &ctx),
1174            ArgumentHandle::new(&lookup_range, &ctx),
1175            ArgumentHandle::new(&return_range, &ctx),
1176            ArgumentHandle::new(&nf_text, &ctx),
1177            ArgumentHandle::new(&mm_next_smaller, &ctx),
1178        ];
1179        let v_smaller = f
1180            .dispatch(&args_smaller, &ctx.function_context(None))
1181            .unwrap();
1182        assert_eq!(v_smaller, LiteralValue::Int(2));
1183        let mm_next_larger = lit(LiteralValue::Int(1));
1184        let nf_text2 = lit(LiteralValue::Text("NF".into()));
1185        let args_larger = vec![
1186            ArgumentHandle::new(&needle_25, &ctx),
1187            ArgumentHandle::new(&lookup_range, &ctx),
1188            ArgumentHandle::new(&return_range, &ctx),
1189            ArgumentHandle::new(&nf_text2, &ctx),
1190            ArgumentHandle::new(&mm_next_larger, &ctx),
1191        ];
1192        let v_larger = f
1193            .dispatch(&args_larger, &ctx.function_context(None))
1194            .unwrap();
1195        assert_eq!(v_larger, LiteralValue::Int(3));
1196    }
1197
1198    #[test]
1199    fn xlookup_wildcard_and_not_found_default_na() {
1200        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
1201        let wb = wb
1202            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Alpha".into()))
1203            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("Beta".into()))
1204            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("Gamma".into()))
1205            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(100))
1206            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(200))
1207            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(300));
1208        let ctx = wb.interpreter();
1209        let lookup_range = ASTNode::new(
1210            ASTNodeType::Reference {
1211                original: "A1:A3".into(),
1212                reference: ReferenceType::Range {
1213                    sheet: None,
1214                    start_row: Some(1),
1215                    start_col: Some(1),
1216                    end_row: Some(3),
1217                    end_col: Some(1),
1218                },
1219            },
1220            None,
1221        );
1222        let return_range = ASTNode::new(
1223            ASTNodeType::Reference {
1224                original: "B1:B3".into(),
1225                reference: ReferenceType::Range {
1226                    sheet: None,
1227                    start_row: Some(1),
1228                    start_col: Some(2),
1229                    end_row: Some(3),
1230                    end_col: Some(2),
1231                },
1232            },
1233            None,
1234        );
1235        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
1236        // Wildcard should match Beta (*et*) with match_mode 2
1237        let pattern = lit(LiteralValue::Text("*et*".into()));
1238        let match_mode_wild = lit(LiteralValue::Int(2));
1239        let nf_binding = lit(LiteralValue::Text("NF".into()));
1240        let args_wild = vec![
1241            ArgumentHandle::new(&pattern, &ctx),
1242            ArgumentHandle::new(&lookup_range, &ctx),
1243            ArgumentHandle::new(&return_range, &ctx),
1244            ArgumentHandle::new(&nf_binding, &ctx),
1245            ArgumentHandle::new(&match_mode_wild, &ctx),
1246        ];
1247        let v_wild = f.dispatch(&args_wild, &ctx.function_context(None)).unwrap();
1248        assert_eq!(v_wild, LiteralValue::Int(200));
1249        // Escaped wildcard literal ~* should not match Beta
1250        let pattern_lit_star = lit(LiteralValue::Text("~*eta".into()));
1251        let args_lit = vec![
1252            ArgumentHandle::new(&pattern_lit_star, &ctx),
1253            ArgumentHandle::new(&lookup_range, &ctx),
1254            ArgumentHandle::new(&return_range, &ctx),
1255            ArgumentHandle::new(&nf_binding, &ctx),
1256            ArgumentHandle::new(&match_mode_wild, &ctx),
1257        ];
1258        let v_lit = f.dispatch(&args_lit, &ctx.function_context(None)).unwrap();
1259        match v_lit {
1260            LiteralValue::Text(s) => assert_eq!(s, "NF"),
1261            other => panic!("expected NF text got {other:?}"),
1262        }
1263        // Not found without if_not_found -> #N/A
1264        let missing = lit(LiteralValue::Text("Zeta".into()));
1265        let args_nf = vec![
1266            ArgumentHandle::new(&missing, &ctx),
1267            ArgumentHandle::new(&lookup_range, &ctx),
1268            ArgumentHandle::new(&return_range, &ctx),
1269        ];
1270        let v_nf = f.dispatch(&args_nf, &ctx.function_context(None)).unwrap();
1271        match v_nf {
1272            LiteralValue::Error(e) => assert_eq!(e.kind, ExcelErrorKind::Na),
1273            other => panic!("expected #N/A got {other:?}"),
1274        }
1275    }
1276
1277    #[test]
1278    fn xlookup_reverse_search_mode_picks_last() {
1279        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
1280        let wb = wb
1281            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
1282            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2))
1283            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(1))
1284            .with_cell_a1("Sheet1", "B1", LiteralValue::Text("First".into()))
1285            .with_cell_a1("Sheet1", "B2", LiteralValue::Text("Mid".into()))
1286            .with_cell_a1("Sheet1", "B3", LiteralValue::Text("Last".into()));
1287        let ctx = wb.interpreter();
1288        let lookup_range = ASTNode::new(
1289            ASTNodeType::Reference {
1290                original: "A1:A3".into(),
1291                reference: ReferenceType::Range {
1292                    sheet: None,
1293                    start_row: Some(1),
1294                    start_col: Some(1),
1295                    end_row: Some(3),
1296                    end_col: Some(1),
1297                },
1298            },
1299            None,
1300        );
1301        let return_range = ASTNode::new(
1302            ASTNodeType::Reference {
1303                original: "B1:B3".into(),
1304                reference: ReferenceType::Range {
1305                    sheet: None,
1306                    start_row: Some(1),
1307                    start_col: Some(2),
1308                    end_row: Some(3),
1309                    end_col: Some(2),
1310                },
1311            },
1312            None,
1313        );
1314        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
1315        let needle_one = lit(LiteralValue::Int(1));
1316        let search_rev = lit(LiteralValue::Int(-1));
1317        let nf_binding2 = lit(LiteralValue::Text("NF".into()));
1318        let match_mode_zero = lit(LiteralValue::Int(0));
1319        let args_rev = vec![
1320            ArgumentHandle::new(&needle_one, &ctx),
1321            ArgumentHandle::new(&lookup_range, &ctx),
1322            ArgumentHandle::new(&return_range, &ctx),
1323            ArgumentHandle::new(&nf_binding2, &ctx),
1324            /* match_mode default */ ArgumentHandle::new(&match_mode_zero, &ctx),
1325            ArgumentHandle::new(&search_rev, &ctx),
1326        ];
1327        let v_rev = f.dispatch(&args_rev, &ctx.function_context(None)).unwrap();
1328        assert_eq!(v_rev, LiteralValue::Text("Last".into()));
1329    }
1330
1331    #[test]
1332    fn filter_basic_and_if_empty() {
1333        let wb = TestWorkbook::new().with_function(Arc::new(FilterFn));
1334        let wb = wb
1335            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
1336            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2))
1337            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(10))
1338            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(20))
1339            .with_cell_a1("Sheet1", "C1", LiteralValue::Boolean(true))
1340            .with_cell_a1("Sheet1", "C2", LiteralValue::Boolean(false));
1341        let ctx = wb.interpreter();
1342        let array_range = ASTNode::new(
1343            ASTNodeType::Reference {
1344                original: "A1:B2".into(),
1345                reference: ReferenceType::Range {
1346                    sheet: None,
1347                    start_row: Some(1),
1348                    start_col: Some(1),
1349                    end_row: Some(2),
1350                    end_col: Some(2),
1351                },
1352            },
1353            None,
1354        );
1355        let include_range = ASTNode::new(
1356            ASTNodeType::Reference {
1357                original: "C1:C2".into(),
1358                reference: ReferenceType::Range {
1359                    sheet: None,
1360                    start_row: Some(1),
1361                    start_col: Some(3),
1362                    end_row: Some(2),
1363                    end_col: Some(3),
1364                },
1365            },
1366            None,
1367        );
1368        let f = ctx.context.get_function("", "FILTER").unwrap();
1369        let args = vec![
1370            ArgumentHandle::new(&array_range, &ctx),
1371            ArgumentHandle::new(&include_range, &ctx),
1372        ];
1373        let v = f.dispatch(&args, &ctx.function_context(None)).unwrap();
1374        match v {
1375            LiteralValue::Array(a) => {
1376                assert_eq!(a.len(), 1);
1377                assert_eq!(a[0], vec![LiteralValue::Int(1), LiteralValue::Int(10)]);
1378            }
1379            other => panic!("expected array got {other:?}"),
1380        }
1381        // Overwrite C1:C2 to both FALSE to produce empty result
1382        let wb2 = wb
1383            .with_cell_a1("Sheet1", "C1", LiteralValue::Boolean(false))
1384            .with_cell_a1("Sheet1", "C2", LiteralValue::Boolean(false));
1385        let ctx2 = wb2.interpreter();
1386        let include_range_false = ASTNode::new(
1387            ASTNodeType::Reference {
1388                original: "C1:C2".into(),
1389                reference: ReferenceType::Range {
1390                    sheet: None,
1391                    start_row: Some(1),
1392                    start_col: Some(3),
1393                    end_row: Some(2),
1394                    end_col: Some(3),
1395                },
1396            },
1397            None,
1398        );
1399        let f2 = ctx2.context.get_function("", "FILTER").unwrap();
1400        let empty_text = lit(LiteralValue::Text("EMPTY".into()));
1401        let args_empty = vec![
1402            ArgumentHandle::new(&array_range, &ctx2),
1403            ArgumentHandle::new(&include_range_false, &ctx2),
1404            ArgumentHandle::new(&empty_text, &ctx2),
1405        ];
1406        let v_empty = f2
1407            .dispatch(&args_empty, &ctx2.function_context(None))
1408            .unwrap();
1409        assert_eq!(v_empty, LiteralValue::Text("EMPTY".into()));
1410        // Now test #CALC! path (remove fallback)
1411        let args_calc = vec![
1412            ArgumentHandle::new(&array_range, &ctx2),
1413            ArgumentHandle::new(&include_range_false, &ctx2),
1414        ];
1415        let v_calc = f2
1416            .dispatch(&args_calc, &ctx2.function_context(None))
1417            .unwrap();
1418        match v_calc {
1419            LiteralValue::Error(e) => assert_eq!(e.kind, ExcelErrorKind::Calc),
1420            other => panic!("expected #CALC! got {other:?}"),
1421        }
1422    }
1423
1424    #[test]
1425    fn unique_basic_and_exactly_once() {
1426        let wb = TestWorkbook::new().with_function(Arc::new(UniqueFn));
1427        let wb = wb
1428            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
1429            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(1))
1430            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(2))
1431            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(3));
1432        let ctx = wb.interpreter();
1433        let range = ASTNode::new(
1434            ASTNodeType::Reference {
1435                original: "A1:A4".into(),
1436                reference: ReferenceType::Range {
1437                    sheet: None,
1438                    start_row: Some(1),
1439                    start_col: Some(1),
1440                    end_row: Some(4),
1441                    end_col: Some(1),
1442                },
1443            },
1444            None,
1445        );
1446        let f = ctx.context.get_function("", "UNIQUE").unwrap();
1447        let args = vec![ArgumentHandle::new(&range, &ctx)];
1448        let v = f.dispatch(&args, &ctx.function_context(None)).unwrap();
1449        match v {
1450            LiteralValue::Array(a) => {
1451                assert_eq!(a.len(), 3);
1452                assert_eq!(a[0][0], LiteralValue::Int(1));
1453                assert_eq!(a[1][0], LiteralValue::Int(2));
1454                assert_eq!(a[2][0], LiteralValue::Int(3));
1455            }
1456            _ => panic!("expected array"),
1457        }
1458        let true_lit = lit(LiteralValue::Boolean(true));
1459        let false_lit = lit(LiteralValue::Boolean(false));
1460        let args_once = vec![
1461            ArgumentHandle::new(&range, &ctx),
1462            ArgumentHandle::new(&false_lit, &ctx),
1463            ArgumentHandle::new(&true_lit, &ctx),
1464        ];
1465        let v_once = f.dispatch(&args_once, &ctx.function_context(None)).unwrap();
1466        match v_once {
1467            LiteralValue::Array(a) => {
1468                assert_eq!(a.len(), 2);
1469                assert_eq!(a[0][0], LiteralValue::Int(2));
1470                assert_eq!(a[1][0], LiteralValue::Int(3));
1471            }
1472            _ => panic!("expected array"),
1473        }
1474        // by_col = TRUE (single column -> same result)
1475        let true_lit2 = lit(LiteralValue::Boolean(true));
1476        let args_by_col = vec![
1477            ArgumentHandle::new(&range, &ctx),
1478            ArgumentHandle::new(&true_lit2, &ctx),
1479        ];
1480        let v_by_col = f
1481            .dispatch(&args_by_col, &ctx.function_context(None))
1482            .unwrap();
1483        match v_by_col {
1484            LiteralValue::Array(a) => {
1485                assert_eq!(a.len(), 1);
1486            }
1487            other => panic!("expected array got {other:?}"),
1488        }
1489
1490        // Collapse single cell test: shrink range to single cell
1491        let single = ASTNode::new(
1492            ASTNodeType::Reference {
1493                original: "A1:A1".into(),
1494                reference: ReferenceType::Range {
1495                    sheet: None,
1496                    start_row: Some(1),
1497                    start_col: Some(1),
1498                    end_row: Some(1),
1499                    end_col: Some(1),
1500                },
1501            },
1502            None,
1503        );
1504        let args_single = vec![ArgumentHandle::new(&single, &ctx)];
1505        let v_single = f
1506            .dispatch(&args_single, &ctx.function_context(None))
1507            .unwrap();
1508        assert_eq!(v_single, LiteralValue::Int(1));
1509    }
1510
1511    #[test]
1512    fn xlookup_unsorted_approx_returns_na() {
1513        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
1514        let wb = wb
1515            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
1516            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(30))
1517            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(20))
1518            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(1))
1519            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(2))
1520            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(3));
1521        let ctx = wb.interpreter();
1522        let lookup_range = ASTNode::new(
1523            ASTNodeType::Reference {
1524                original: "A1:A3".into(),
1525                reference: ReferenceType::Range {
1526                    sheet: None,
1527                    start_row: Some(1),
1528                    start_col: Some(1),
1529                    end_row: Some(3),
1530                    end_col: Some(1),
1531                },
1532            },
1533            None,
1534        );
1535        let return_range = ASTNode::new(
1536            ASTNodeType::Reference {
1537                original: "B1:B3".into(),
1538                reference: ReferenceType::Range {
1539                    sheet: None,
1540                    start_row: Some(1),
1541                    start_col: Some(2),
1542                    end_row: Some(3),
1543                    end_col: Some(2),
1544                },
1545            },
1546            None,
1547        );
1548        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
1549        let needle = lit(LiteralValue::Int(25));
1550        let mm_next_smaller = lit(LiteralValue::Int(-1));
1551        let nf_binding = lit(LiteralValue::Text("NF".into()));
1552        let args = vec![
1553            ArgumentHandle::new(&needle, &ctx),
1554            ArgumentHandle::new(&lookup_range, &ctx),
1555            ArgumentHandle::new(&return_range, &ctx),
1556            ArgumentHandle::new(&nf_binding, &ctx),
1557            ArgumentHandle::new(&mm_next_smaller, &ctx),
1558        ];
1559        let v = f.dispatch(&args, &ctx.function_context(None)).unwrap();
1560        match v {
1561            LiteralValue::Error(e) => assert_eq!(e.kind, ExcelErrorKind::Na),
1562            other => panic!("expected #N/A got {other:?}"),
1563        }
1564    }
1565
1566    #[test]
1567    fn unique_multi_column_row_and_col_modes() {
1568        let wb = TestWorkbook::new().with_function(Arc::new(UniqueFn));
1569        let wb = wb
1570            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
1571            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(1))
1572            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(2))
1573            .with_cell_a1("Sheet1", "B1", LiteralValue::Text("x".into()))
1574            .with_cell_a1("Sheet1", "B2", LiteralValue::Text("x".into()))
1575            .with_cell_a1("Sheet1", "B3", LiteralValue::Text("y".into()));
1576        let ctx = wb.interpreter();
1577        let range = ASTNode::new(
1578            ASTNodeType::Reference {
1579                original: "A1:B3".into(),
1580                reference: ReferenceType::Range {
1581                    sheet: None,
1582                    start_row: Some(1),
1583                    start_col: Some(1),
1584                    end_row: Some(3),
1585                    end_col: Some(2),
1586                },
1587            },
1588            None,
1589        );
1590        let f = ctx.context.get_function("", "UNIQUE").unwrap();
1591        // Row-wise unique => (1,x) & (2,y)
1592        let args_rows = vec![ArgumentHandle::new(&range, &ctx)];
1593        let vr = f.dispatch(&args_rows, &ctx.function_context(None)).unwrap();
1594        match vr {
1595            LiteralValue::Array(a) => {
1596                assert_eq!(a.len(), 2);
1597                assert_eq!(
1598                    a[0],
1599                    vec![LiteralValue::Int(1), LiteralValue::Text("x".into())]
1600                );
1601                assert_eq!(
1602                    a[1],
1603                    vec![LiteralValue::Int(2), LiteralValue::Text("y".into())]
1604                );
1605            }
1606            other => panic!("expected array got {other:?}"),
1607        }
1608        // Column-wise unique -> columns [1,1,2] and [x,x,y]
1609        let true_lit = lit(LiteralValue::Boolean(true));
1610        let args_cols = vec![
1611            ArgumentHandle::new(&range, &ctx),
1612            ArgumentHandle::new(&true_lit, &ctx),
1613        ];
1614        let vc = f.dispatch(&args_cols, &ctx.function_context(None)).unwrap();
1615        match vc {
1616            LiteralValue::Array(a) => {
1617                assert_eq!(a.len(), 2);
1618                assert_eq!(a[0].len(), 3);
1619                assert_eq!(a[1].len(), 3);
1620            }
1621            other => panic!("expected array got {other:?}"),
1622        }
1623    }
1624
1625    #[test]
1626    fn sequence_basic_rows_cols_step() {
1627        let wb = TestWorkbook::new().with_function(Arc::new(SequenceFn));
1628        let ctx = wb.interpreter();
1629        let f = ctx.context.get_function("", "SEQUENCE").unwrap();
1630        let rows = lit(LiteralValue::Int(2));
1631        let cols = lit(LiteralValue::Int(3));
1632        let start = lit(LiteralValue::Int(5));
1633        let step = lit(LiteralValue::Int(2));
1634        let args = vec![
1635            ArgumentHandle::new(&rows, &ctx),
1636            ArgumentHandle::new(&cols, &ctx),
1637            ArgumentHandle::new(&start, &ctx),
1638            ArgumentHandle::new(&step, &ctx),
1639        ];
1640        let v = f.dispatch(&args, &ctx.function_context(None)).unwrap();
1641        match v {
1642            LiteralValue::Array(a) => {
1643                assert_eq!(a.len(), 2);
1644                assert_eq!(
1645                    a[0],
1646                    vec![
1647                        LiteralValue::Int(5),
1648                        LiteralValue::Int(7),
1649                        LiteralValue::Int(9)
1650                    ]
1651                );
1652                assert_eq!(
1653                    a[1],
1654                    vec![
1655                        LiteralValue::Int(11),
1656                        LiteralValue::Int(13),
1657                        LiteralValue::Int(15)
1658                    ]
1659                );
1660            }
1661            other => panic!("expected array got {other:?}"),
1662        }
1663    }
1664
1665    #[test]
1666    fn transpose_rectangular_and_single_cell() {
1667        let wb = TestWorkbook::new().with_function(Arc::new(TransposeFn));
1668        let wb = wb
1669            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
1670            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2))
1671            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(10))
1672            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(20));
1673        let ctx = wb.interpreter();
1674        use formualizer_parse::parser::{ASTNodeType, ReferenceType};
1675        let range = ASTNode::new(
1676            ASTNodeType::Reference {
1677                original: "A1:B2".into(),
1678                reference: ReferenceType::Range {
1679                    sheet: None,
1680                    start_row: Some(1),
1681                    start_col: Some(1),
1682                    end_row: Some(2),
1683                    end_col: Some(2),
1684                },
1685            },
1686            None,
1687        );
1688        let f = ctx.context.get_function("", "TRANSPOSE").unwrap();
1689        let args = vec![ArgumentHandle::new(&range, &ctx)];
1690        let v = f.dispatch(&args, &ctx.function_context(None)).unwrap();
1691        match v {
1692            LiteralValue::Array(a) => {
1693                assert_eq!(a.len(), 2); // 2 columns -> rows
1694                assert_eq!(a[0], vec![LiteralValue::Int(1), LiteralValue::Int(2)]);
1695                assert_eq!(a[1], vec![LiteralValue::Int(10), LiteralValue::Int(20)]);
1696            }
1697            other => panic!("expected array got {other:?}"),
1698        }
1699    }
1700
1701    #[test]
1702    fn take_positive_and_negative() {
1703        let wb = TestWorkbook::new().with_function(Arc::new(TakeFn));
1704        let wb = wb
1705            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
1706            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2))
1707            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(3))
1708            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(10))
1709            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(20))
1710            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(30));
1711        let ctx = wb.interpreter();
1712        use formualizer_parse::parser::{ASTNodeType, ReferenceType};
1713        let range = ASTNode::new(
1714            ASTNodeType::Reference {
1715                original: "A1:B3".into(),
1716                reference: ReferenceType::Range {
1717                    sheet: None,
1718                    start_row: Some(1),
1719                    start_col: Some(1),
1720                    end_row: Some(3),
1721                    end_col: Some(2),
1722                },
1723            },
1724            None,
1725        );
1726        let f = ctx.context.get_function("", "TAKE").unwrap();
1727        // TAKE first 2 rows
1728        let n2 = lit(LiteralValue::Int(2));
1729        let args_first = vec![
1730            ArgumentHandle::new(&range, &ctx),
1731            ArgumentHandle::new(&n2, &ctx),
1732        ];
1733        let v_first = f
1734            .dispatch(&args_first, &ctx.function_context(None))
1735            .unwrap();
1736        match v_first {
1737            LiteralValue::Array(a) => assert_eq!(a.len(), 2),
1738            other => panic!("expected array got {other:?}"),
1739        }
1740        // TAKE last 1 row (negative)
1741        let n_neg1 = lit(LiteralValue::Int(-1));
1742        let args_last = vec![
1743            ArgumentHandle::new(&range, &ctx),
1744            ArgumentHandle::new(&n_neg1, &ctx),
1745        ];
1746        let v_last = f.dispatch(&args_last, &ctx.function_context(None)).unwrap();
1747        match v_last {
1748            LiteralValue::Array(a) => {
1749                assert_eq!(a.len(), 1);
1750                assert_eq!(a[0][0], LiteralValue::Int(3));
1751            }
1752            other => panic!("expected array got {other:?}"),
1753        }
1754        // TAKE with columns subset
1755        let one = lit(LiteralValue::Int(1));
1756        let args_col = vec![
1757            ArgumentHandle::new(&range, &ctx),
1758            ArgumentHandle::new(&n2, &ctx),
1759            ArgumentHandle::new(&one, &ctx),
1760        ];
1761        let v_col = f.dispatch(&args_col, &ctx.function_context(None)).unwrap();
1762        match v_col {
1763            LiteralValue::Array(a) => {
1764                assert_eq!(a[0].len(), 1);
1765            }
1766            other => panic!("expected array got {other:?}"),
1767        }
1768    }
1769
1770    #[test]
1771    fn drop_positive_and_negative() {
1772        let wb = TestWorkbook::new().with_function(Arc::new(DropFn));
1773        let wb = wb
1774            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
1775            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2))
1776            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(3))
1777            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(10))
1778            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(20))
1779            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(30));
1780        let ctx = wb.interpreter();
1781        use formualizer_parse::parser::{ASTNodeType, ReferenceType};
1782        let range = ASTNode::new(
1783            ASTNodeType::Reference {
1784                original: "A1:B3".into(),
1785                reference: ReferenceType::Range {
1786                    sheet: None,
1787                    start_row: Some(1),
1788                    start_col: Some(1),
1789                    end_row: Some(3),
1790                    end_col: Some(2),
1791                },
1792            },
1793            None,
1794        );
1795        let f = ctx.context.get_function("", "DROP").unwrap();
1796        let one = lit(LiteralValue::Int(1));
1797        let args_drop_first_row = vec![
1798            ArgumentHandle::new(&range, &ctx),
1799            ArgumentHandle::new(&one, &ctx),
1800        ];
1801        let v_d1 = f
1802            .dispatch(&args_drop_first_row, &ctx.function_context(None))
1803            .unwrap();
1804        match v_d1 {
1805            LiteralValue::Array(a) => assert_eq!(a.len(), 2),
1806            other => panic!("expected array got {other:?}"),
1807        }
1808        let neg_one = lit(LiteralValue::Int(-1));
1809        let args_drop_last_row = vec![
1810            ArgumentHandle::new(&range, &ctx),
1811            ArgumentHandle::new(&neg_one, &ctx),
1812        ];
1813        let v_d2 = f
1814            .dispatch(&args_drop_last_row, &ctx.function_context(None))
1815            .unwrap();
1816        match v_d2 {
1817            LiteralValue::Array(a) => {
1818                assert_eq!(a.len(), 2);
1819                assert_eq!(a[0][0], LiteralValue::Int(1));
1820            }
1821            other => panic!("expected array got {other:?}"),
1822        }
1823        // Drop columns
1824        let args_drop_col = vec![
1825            ArgumentHandle::new(&range, &ctx),
1826            ArgumentHandle::new(&one, &ctx),
1827            ArgumentHandle::new(&one, &ctx),
1828        ];
1829        let v_dc = f
1830            .dispatch(&args_drop_col, &ctx.function_context(None))
1831            .unwrap();
1832        match v_dc {
1833            LiteralValue::Array(a) => {
1834                assert_eq!(a[0].len(), 1);
1835            }
1836            other => panic!("expected array got {other:?}"),
1837        }
1838    }
1839}
formualizer_eval/builtins/lookup/dynamic.rs

formualizer_eval/builtins/lookup/
dynamic.rs
