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::super::utils::collapse_if_scalar;
23use super::lookup_utils::{cmp_for_lookup, equals_maybe_wildcard, value_to_f64_lenient};
24use crate::args::{ArgSchema, CoercionPolicy, ShapeKind};
25use crate::function::Function; // FnCaps imported via macro
26use crate::traits::{ArgumentHandle, FunctionContext};
27use formualizer_common::{ArgKind, ExcelError, ExcelErrorKind, LiteralValue};
28use formualizer_macros::func_caps;
29use std::collections::HashMap;
30
31/* ───────────────────────── helpers ───────────────────────── */
32
33pub fn super_wildcard_match(pattern: &str, text: &str) -> bool {
34    // public for shared lookup utils
35    // Excel-style wildcards with escape (~): * any seq, ? single char, ~ escapes next (*, ?, ~)
36    // Implement non-recursive DP for performance & to support escapes.
37    #[derive(Clone, Copy, Debug)]
38    enum Token<'a> {
39        AnySeq,
40        AnyChar,
41        Lit(&'a str),
42    }
43    let mut tokens: Vec<Token> = Vec::new();
44    let mut i = 0;
45    let bytes = pattern.as_bytes();
46    let mut lit_start = 0;
47    while i < bytes.len() {
48        match bytes[i] {
49            b'~' => {
50                // escape next if present
51                if i + 1 < bytes.len() {
52                    // flush pending literal
53                    if lit_start < i {
54                        tokens.push(Token::Lit(&pattern[lit_start..i]));
55                    }
56                    tokens.push(Token::Lit(&pattern[i + 1..i + 2]));
57                    i += 2;
58                    lit_start = i;
59                } else {
60                    // trailing ~ treated literal
61                    i += 1;
62                }
63            }
64            b'*' => {
65                if lit_start < i {
66                    tokens.push(Token::Lit(&pattern[lit_start..i]));
67                }
68                tokens.push(Token::AnySeq);
69                i += 1;
70                lit_start = i;
71            }
72            b'?' => {
73                if lit_start < i {
74                    tokens.push(Token::Lit(&pattern[lit_start..i]));
75                }
76                tokens.push(Token::AnyChar);
77                i += 1;
78                lit_start = i;
79            }
80            _ => i += 1,
81        }
82    }
83    if lit_start < bytes.len() {
84        tokens.push(Token::Lit(&pattern[lit_start..]));
85    }
86    // Simplify consecutive AnySeq
87    let mut compact: Vec<Token> = Vec::new();
88    for t in tokens {
89        match t {
90            Token::AnySeq => {
91                if !matches!(compact.last(), Some(Token::AnySeq)) {
92                    compact.push(t);
93                }
94            }
95            _ => compact.push(t),
96        }
97    }
98    // Backtracking matcher
99    fn match_tokens<'a>(tokens: &[Token<'a>], text: &str) -> bool {
100        fn eq_icase(a: &str, b: &str) -> bool {
101            a.eq_ignore_ascii_case(b)
102        }
103        // Convert Lit tokens into lowercase for quick compare
104        let mut ti = 0;
105        let tb = tokens;
106        // Use manual stack for backtracking when encountering AnySeq
107        let mut backtrack: Vec<(usize, usize)> = Vec::new(); // (token_index, text_index after consuming 1 more char by *)
108        let text_bytes = text.as_bytes();
109        let mut si = 0; // text index
110        loop {
111            if ti == tb.len() {
112                // tokens consumed
113                if si == text_bytes.len() {
114                    return true;
115                }
116                // Maybe backtrack
117            } else {
118                match tb[ti] {
119                    Token::AnySeq => {
120                        // try to match zero chars first
121                        ti += 1;
122                        backtrack.push((ti - 1, si + 1));
123                        continue;
124                    }
125                    Token::AnyChar => {
126                        if si < text_bytes.len() {
127                            ti += 1;
128                            si += 1;
129                            continue;
130                        }
131                    }
132                    Token::Lit(l) => {
133                        let l_len = l.len();
134                        if si + l_len <= text_bytes.len() && eq_icase(&text[si..si + l_len], l) {
135                            ti += 1;
136                            si += l_len;
137                            continue;
138                        }
139                    }
140                }
141            }
142            // failed match; attempt backtrack
143            if let Some((tok_star, new_si)) = backtrack.pop() {
144                if new_si <= text_bytes.len() {
145                    ti = tok_star + 1;
146                    si = new_si;
147                    continue;
148                } else {
149                    continue;
150                }
151            }
152            return false;
153        }
154    }
155    match_tokens(&compact, text)
156}
157
158/* ───────────────────────── XLOOKUP() ───────────────────────── */
159
160#[derive(Debug)]
161pub struct XLookupFn;
162
163impl Function for XLookupFn {
164    func_caps!(PURE, LOOKUP);
165    fn name(&self) -> &'static str {
166        "XLOOKUP"
167    }
168    fn min_args(&self) -> usize {
169        3
170    }
171    fn variadic(&self) -> bool {
172        true
173    }
174    fn arg_schema(&self) -> &'static [ArgSchema] {
175        use once_cell::sync::Lazy;
176        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
177            vec![
178                // lookup_value
179                ArgSchema {
180                    kinds: smallvec::smallvec![ArgKind::Any],
181                    required: true,
182                    by_ref: false,
183                    shape: ShapeKind::Scalar,
184                    coercion: CoercionPolicy::None,
185                    max: None,
186                    repeating: None,
187                    default: None,
188                },
189                // lookup_array (range)
190                ArgSchema {
191                    kinds: smallvec::smallvec![ArgKind::Range],
192                    required: true,
193                    by_ref: true,
194                    shape: ShapeKind::Range,
195                    coercion: CoercionPolicy::None,
196                    max: None,
197                    repeating: None,
198                    default: None,
199                },
200                // return_array (range)
201                ArgSchema {
202                    kinds: smallvec::smallvec![ArgKind::Range],
203                    required: true,
204                    by_ref: true,
205                    shape: ShapeKind::Range,
206                    coercion: CoercionPolicy::None,
207                    max: None,
208                    repeating: None,
209                    default: None,
210                },
211                // if_not_found (any optional)
212                ArgSchema {
213                    kinds: smallvec::smallvec![ArgKind::Any],
214                    required: false,
215                    by_ref: false,
216                    shape: ShapeKind::Scalar,
217                    coercion: CoercionPolicy::None,
218                    max: None,
219                    repeating: None,
220                    default: None,
221                },
222                // match_mode (number) default 0
223                ArgSchema {
224                    kinds: smallvec::smallvec![ArgKind::Number],
225                    required: false,
226                    by_ref: false,
227                    shape: ShapeKind::Scalar,
228                    coercion: CoercionPolicy::NumberLenientText,
229                    max: None,
230                    repeating: None,
231                    default: Some(LiteralValue::Int(0)),
232                },
233                // search_mode (number) default 1
234                ArgSchema {
235                    kinds: smallvec::smallvec![ArgKind::Number],
236                    required: false,
237                    by_ref: false,
238                    shape: ShapeKind::Scalar,
239                    coercion: CoercionPolicy::NumberLenientText,
240                    max: None,
241                    repeating: None,
242                    default: Some(LiteralValue::Int(1)),
243                },
244            ]
245        });
246        &SCHEMA
247    }
248    fn eval<'a, 'b, 'c>(
249        &self,
250        args: &'c [ArgumentHandle<'a, 'b>],
251        _ctx: &dyn FunctionContext<'b>,
252    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
253        if args.len() < 3 {
254            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
255                ExcelError::new(ExcelErrorKind::Value),
256            )));
257        }
258        let lookup_value = args[0].value()?.into_literal();
259        if let LiteralValue::Error(ref e) = lookup_value {
260            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
261                e.clone(),
262            )));
263        }
264        let lookup_view = match args[1].range_view() {
265            Ok(v) => v,
266            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
267        };
268        let ret_view = match args[2].range_view() {
269            Ok(v) => v,
270            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
271        };
272
273        let (lookup_rows, lookup_cols) = lookup_view.dims();
274        let (ret_rows, ret_cols) = ret_view.dims();
275
276        // XLOOKUP requires a 1-D lookup array (single row or single column).
277        // If the lookup range is completely empty (used-region trimmed to 0),
278        // fall back to the return range's used-region length and treat missing lookup
279        // cells as Empty.
280        let vertical = if lookup_cols == 1 {
281            true
282        } else if lookup_rows == 1 {
283            false
284        } else if lookup_rows == 0 && lookup_cols == 0 {
285            if ret_cols == 1 {
286                true
287            } else if ret_rows == 1 {
288                false
289            } else {
290                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
291                    ExcelError::new(ExcelErrorKind::Value),
292                )));
293            }
294        } else {
295            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
296                ExcelError::new(ExcelErrorKind::Value),
297            )));
298        };
299
300        let lookup_len = {
301            let raw = if vertical { lookup_rows } else { lookup_cols };
302            if raw == 0 {
303                if vertical { ret_rows } else { ret_cols }
304            } else {
305                raw
306            }
307        };
308
309        if lookup_len == 0 {
310            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
311                ExcelError::new(ExcelErrorKind::Na),
312            )));
313        }
314
315        let match_mode = if args.len() >= 5 {
316            match args[4].value()?.into_literal() {
317                LiteralValue::Int(i) => i,
318                LiteralValue::Number(n) => n as i64,
319                _ => 0,
320            }
321        } else {
322            0
323        };
324        let search_mode = if args.len() >= 6 {
325            match args[5].value()?.into_literal() {
326                LiteralValue::Int(i) => i,
327                LiteralValue::Number(n) => n as i64,
328                _ => 1,
329            }
330        } else {
331            1
332        };
333
334        let wildcard = match_mode == 2;
335
336        let mut found: Option<usize> = None;
337        let needle = lookup_value;
338        if match_mode == 0 || wildcard {
339            if search_mode == 1 && lookup_rows > 0 && lookup_cols > 0 {
340                found =
341                    super::lookup_utils::find_exact_index_in_view(&lookup_view, &needle, wildcard)?;
342            } else if search_mode == -1 {
343                for i in (0..lookup_len).rev() {
344                    let cand = if vertical {
345                        lookup_view.get_cell(i, 0)
346                    } else {
347                        lookup_view.get_cell(0, i)
348                    };
349                    if equals_maybe_wildcard(&needle, &cand, wildcard) {
350                        found = Some(i);
351                        break;
352                    }
353                }
354            } else {
355                // Fallback linear scan (also used when the lookup view is empty and
356                // we are treating missing cells as Empty).
357                for i in 0..lookup_len {
358                    let cand = if vertical {
359                        lookup_view.get_cell(i, 0)
360                    } else {
361                        lookup_view.get_cell(0, i)
362                    };
363                    if equals_maybe_wildcard(&needle, &cand, wildcard) {
364                        found = Some(i);
365                        break;
366                    }
367                }
368            }
369        } else if match_mode == -1 || match_mode == 1 {
370            let needle_num = value_to_f64_lenient(&needle);
371            let mut best_idx: Option<usize> = None;
372            let mut best_val: f64 = if match_mode == -1 {
373                f64::NEG_INFINITY
374            } else {
375                f64::INFINITY
376            };
377
378            let mut prev: Option<LiteralValue> = None;
379            for i in 0..lookup_len {
380                let cand = if vertical {
381                    lookup_view.get_cell(i, 0)
382                } else {
383                    lookup_view.get_cell(0, i)
384                };
385
386                if let Some(p) = prev.as_ref() {
387                    let sorted_ok = cmp_for_lookup(p, &cand).is_some_and(|o| o <= 0);
388                    if !sorted_ok {
389                        return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
390                            ExcelError::new(ExcelErrorKind::Na),
391                        )));
392                    }
393                }
394                prev = Some(cand.clone());
395
396                if cmp_for_lookup(&cand, &needle).is_some_and(|o| o == 0) {
397                    found = Some(i);
398                    break;
399                }
400
401                if let (Some(nn), Some(vv)) = (needle_num, value_to_f64_lenient(&cand)) {
402                    if match_mode == -1 {
403                        if vv <= nn && vv > best_val {
404                            best_val = vv;
405                            best_idx = Some(i);
406                        }
407                    } else if vv >= nn && vv < best_val {
408                        best_val = vv;
409                        best_idx = Some(i);
410                    }
411                }
412            }
413
414            if found.is_none() {
415                found = best_idx;
416            }
417        } else {
418            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
419                ExcelError::new(ExcelErrorKind::Value),
420            )));
421        }
422
423        if let Some(idx) = found {
424            let (ret_rows, ret_cols) = ret_view.dims();
425            if ret_rows == 0 || ret_cols == 0 {
426                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Empty));
427            }
428
429            if vertical {
430                if ret_cols == 1 {
431                    return Ok(crate::traits::CalcValue::Scalar(ret_view.get_cell(idx, 0)));
432                }
433                let mut row_out: Vec<LiteralValue> = Vec::with_capacity(ret_cols);
434                for c in 0..ret_cols {
435                    row_out.push(ret_view.get_cell(idx, c));
436                }
437                return Ok(crate::traits::CalcValue::Range(
438                    crate::engine::range_view::RangeView::from_owned_rows(
439                        vec![row_out],
440                        _ctx.date_system(),
441                    ),
442                ));
443            }
444
445            // Horizontal orientation: treat idx as column.
446            if ret_rows == 1 {
447                return Ok(crate::traits::CalcValue::Scalar(ret_view.get_cell(0, idx)));
448            }
449
450            let mut col_out: Vec<Vec<LiteralValue>> = Vec::with_capacity(ret_rows);
451            for r in 0..ret_rows {
452                col_out.push(vec![ret_view.get_cell(r, idx)]);
453            }
454            return Ok(crate::traits::CalcValue::Range(
455                crate::engine::range_view::RangeView::from_owned_rows(col_out, _ctx.date_system()),
456            ));
457        }
458
459        if args.len() >= 4 {
460            return args[3].value();
461        }
462        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
463            ExcelError::new(ExcelErrorKind::Na),
464        )))
465    }
466}
467
468/* ───────────────────────── XMATCH() ───────────────────────── */
469
470#[derive(Debug)]
471pub struct XMatchFn;
472impl Function for XMatchFn {
473    func_caps!(PURE, LOOKUP);
474    fn name(&self) -> &'static str {
475        "XMATCH"
476    }
477    fn min_args(&self) -> usize {
478        2
479    }
480    fn variadic(&self) -> bool {
481        true
482    }
483    fn arg_schema(&self) -> &'static [ArgSchema] {
484        use once_cell::sync::Lazy;
485        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
486            vec![
487                // lookup_value
488                ArgSchema {
489                    kinds: smallvec::smallvec![ArgKind::Any],
490                    required: true,
491                    by_ref: false,
492                    shape: ShapeKind::Scalar,
493                    coercion: CoercionPolicy::None,
494                    max: None,
495                    repeating: None,
496                    default: None,
497                },
498                // lookup_array (range)
499                ArgSchema {
500                    kinds: smallvec::smallvec![ArgKind::Range],
501                    required: true,
502                    by_ref: true,
503                    shape: ShapeKind::Range,
504                    coercion: CoercionPolicy::None,
505                    max: None,
506                    repeating: None,
507                    default: None,
508                },
509                // match_mode (number) default 0
510                // 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard
511                ArgSchema {
512                    kinds: smallvec::smallvec![ArgKind::Number],
513                    required: false,
514                    by_ref: false,
515                    shape: ShapeKind::Scalar,
516                    coercion: CoercionPolicy::NumberLenientText,
517                    max: None,
518                    repeating: None,
519                    default: Some(LiteralValue::Int(0)),
520                },
521                // search_mode (number) default 1
522                // 1 = first to last (default), -1 = last to first, 2 = binary ascending, -2 = binary descending
523                ArgSchema {
524                    kinds: smallvec::smallvec![ArgKind::Number],
525                    required: false,
526                    by_ref: false,
527                    shape: ShapeKind::Scalar,
528                    coercion: CoercionPolicy::NumberLenientText,
529                    max: None,
530                    repeating: None,
531                    default: Some(LiteralValue::Int(1)),
532                },
533            ]
534        });
535        &SCHEMA
536    }
537    fn eval<'a, 'b, 'c>(
538        &self,
539        args: &'c [ArgumentHandle<'a, 'b>],
540        _ctx: &dyn FunctionContext<'b>,
541    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
542        if args.len() < 2 {
543            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
544                ExcelError::new(ExcelErrorKind::Value),
545            )));
546        }
547        let lookup_value = args[0].value()?.into_literal();
548        if let LiteralValue::Error(ref e) = lookup_value {
549            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
550                e.clone(),
551            )));
552        }
553        let lookup_view = match args[1].range_view() {
554            Ok(v) => v,
555            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
556        };
557
558        let (lookup_rows, lookup_cols) = lookup_view.dims();
559
560        // XMATCH requires a 1-D lookup array (single row or single column).
561        let vertical = if lookup_cols == 1 {
562            true
563        } else if lookup_rows == 1 {
564            false
565        } else if lookup_rows == 0 || lookup_cols == 0 {
566            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
567                ExcelError::new(ExcelErrorKind::Na),
568            )));
569        } else {
570            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
571                ExcelError::new(ExcelErrorKind::Value),
572            )));
573        };
574
575        let lookup_len = if vertical { lookup_rows } else { lookup_cols };
576
577        if lookup_len == 0 {
578            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
579                ExcelError::new(ExcelErrorKind::Na),
580            )));
581        }
582
583        let match_mode = if args.len() >= 3 {
584            match args[2].value()?.into_literal() {
585                LiteralValue::Int(i) => i,
586                LiteralValue::Number(n) => n as i64,
587                _ => 0,
588            }
589        } else {
590            0
591        };
592        let search_mode = if args.len() >= 4 {
593            match args[3].value()?.into_literal() {
594                LiteralValue::Int(i) => i,
595                LiteralValue::Number(n) => n as i64,
596                _ => 1,
597            }
598        } else {
599            1
600        };
601
602        let wildcard = match_mode == 2;
603        let needle = lookup_value;
604
605        let mut found: Option<usize> = None;
606
607        if match_mode == 0 || wildcard {
608            // Exact match or wildcard match
609            if search_mode == 1 || search_mode == 2 {
610                // Forward search (first to last) or binary ascending (treated as forward for exact)
611                if lookup_rows > 0 && lookup_cols > 0 {
612                    found = super::lookup_utils::find_exact_index_in_view(
613                        &lookup_view,
614                        &needle,
615                        wildcard,
616                    )?;
617                }
618            } else if search_mode == -1 || search_mode == -2 {
619                // Reverse search (last to first) or binary descending (treated as reverse for exact)
620                for i in (0..lookup_len).rev() {
621                    let cand = if vertical {
622                        lookup_view.get_cell(i, 0)
623                    } else {
624                        lookup_view.get_cell(0, i)
625                    };
626                    if equals_maybe_wildcard(&needle, &cand, wildcard) {
627                        found = Some(i);
628                        break;
629                    }
630                }
631            } else {
632                // Fallback linear scan
633                for i in 0..lookup_len {
634                    let cand = if vertical {
635                        lookup_view.get_cell(i, 0)
636                    } else {
637                        lookup_view.get_cell(0, i)
638                    };
639                    if equals_maybe_wildcard(&needle, &cand, wildcard) {
640                        found = Some(i);
641                        break;
642                    }
643                }
644            }
645        } else if match_mode == -1 || match_mode == 1 {
646            // Approximate match: -1 = exact or next smaller, 1 = exact or next larger
647            let needle_num = value_to_f64_lenient(&needle);
648            let mut best_idx: Option<usize> = None;
649            let mut best_val: f64 = if match_mode == -1 {
650                f64::NEG_INFINITY
651            } else {
652                f64::INFINITY
653            };
654
655            // Determine iteration direction based on search_mode
656            let use_reverse = search_mode == -1 || search_mode == -2;
657            let indices: Box<dyn Iterator<Item = usize>> = if use_reverse {
658                Box::new((0..lookup_len).rev())
659            } else {
660                Box::new(0..lookup_len)
661            };
662
663            // For binary search modes (2, -2), data should be sorted
664            // We verify sorting for approximate modes
665            if (search_mode == 2 || search_mode == -2) && match_mode != 0 {
666                let ascending = search_mode == 2;
667                let mut prev: Option<LiteralValue> = None;
668                for i in 0..lookup_len {
669                    let cand = if vertical {
670                        lookup_view.get_cell(i, 0)
671                    } else {
672                        lookup_view.get_cell(0, i)
673                    };
674                    if let Some(p) = prev.as_ref() {
675                        let sorted_ok = if ascending {
676                            cmp_for_lookup(p, &cand).is_some_and(|o| o <= 0)
677                        } else {
678                            cmp_for_lookup(p, &cand).is_some_and(|o| o >= 0)
679                        };
680                        if !sorted_ok {
681                            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
682                                ExcelError::new(ExcelErrorKind::Na),
683                            )));
684                        }
685                    }
686                    prev = Some(cand);
687                }
688            }
689
690            for i in indices {
691                let cand = if vertical {
692                    lookup_view.get_cell(i, 0)
693                } else {
694                    lookup_view.get_cell(0, i)
695                };
696
697                if cmp_for_lookup(&cand, &needle).is_some_and(|o| o == 0) {
698                    found = Some(i);
699                    break;
700                }
701
702                if let (Some(nn), Some(vv)) = (needle_num, value_to_f64_lenient(&cand)) {
703                    if match_mode == -1 {
704                        // exact or next smaller
705                        if vv <= nn && vv > best_val {
706                            best_val = vv;
707                            best_idx = Some(i);
708                        }
709                    } else {
710                        // match_mode == 1: exact or next larger
711                        if vv >= nn && vv < best_val {
712                            best_val = vv;
713                            best_idx = Some(i);
714                        }
715                    }
716                }
717            }
718
719            if found.is_none() {
720                found = best_idx;
721            }
722        } else {
723            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
724                ExcelError::new(ExcelErrorKind::Value),
725            )));
726        }
727
728        match found {
729            Some(idx) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
730                (idx + 1) as i64,
731            ))),
732            None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
733                ExcelError::new(ExcelErrorKind::Na),
734            ))),
735        }
736    }
737}
738
739/* ───────────────────────── SORT() ───────────────────────── */
740
741#[derive(Debug)]
742pub struct SortFn;
743impl Function for SortFn {
744    func_caps!(PURE);
745    fn name(&self) -> &'static str {
746        "SORT"
747    }
748    fn min_args(&self) -> usize {
749        1
750    }
751    fn variadic(&self) -> bool {
752        true
753    }
754    fn arg_schema(&self) -> &'static [ArgSchema] {
755        use once_cell::sync::Lazy;
756        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
757            vec![
758                // array
759                ArgSchema {
760                    kinds: smallvec::smallvec![ArgKind::Range],
761                    required: true,
762                    by_ref: true,
763                    shape: ShapeKind::Range,
764                    coercion: CoercionPolicy::None,
765                    max: None,
766                    repeating: None,
767                    default: None,
768                },
769                // sort_index (default 1)
770                ArgSchema {
771                    kinds: smallvec::smallvec![ArgKind::Number],
772                    required: false,
773                    by_ref: false,
774                    shape: ShapeKind::Scalar,
775                    coercion: CoercionPolicy::NumberLenientText,
776                    max: None,
777                    repeating: None,
778                    default: Some(LiteralValue::Int(1)),
779                },
780                // sort_order (default 1 = ascending, -1 = descending)
781                ArgSchema {
782                    kinds: smallvec::smallvec![ArgKind::Number],
783                    required: false,
784                    by_ref: false,
785                    shape: ShapeKind::Scalar,
786                    coercion: CoercionPolicy::NumberLenientText,
787                    max: None,
788                    repeating: None,
789                    default: Some(LiteralValue::Int(1)),
790                },
791                // by_col (default FALSE = sort rows, TRUE = sort columns)
792                ArgSchema {
793                    kinds: smallvec::smallvec![ArgKind::Logical],
794                    required: false,
795                    by_ref: false,
796                    shape: ShapeKind::Scalar,
797                    coercion: CoercionPolicy::Logical,
798                    max: None,
799                    repeating: None,
800                    default: Some(LiteralValue::Boolean(false)),
801                },
802            ]
803        });
804        &SCHEMA
805    }
806    fn eval<'a, 'b, 'c>(
807        &self,
808        args: &'c [ArgumentHandle<'a, 'b>],
809        _ctx: &dyn FunctionContext<'b>,
810    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
811        let view = match args[0].range_view() {
812            Ok(v) => v,
813            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
814        };
815        let (rows, cols) = view.dims();
816        if rows == 0 || cols == 0 {
817            return Ok(crate::traits::CalcValue::Range(
818                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
819            ));
820        }
821
822        let sort_index = if args.len() >= 2 {
823            match args[1].value()?.into_literal() {
824                LiteralValue::Int(i) => i,
825                LiteralValue::Number(n) => n as i64,
826                _ => 1,
827            }
828        } else {
829            1
830        };
831
832        let sort_order = if args.len() >= 3 {
833            match args[2].value()?.into_literal() {
834                LiteralValue::Int(i) => i,
835                LiteralValue::Number(n) => n as i64,
836                _ => 1,
837            }
838        } else {
839            1
840        };
841
842        let by_col = if args.len() >= 4 {
843            matches!(args[3].value()?.into_literal(), LiteralValue::Boolean(true))
844        } else {
845            false
846        };
847
848        let ascending = sort_order >= 0;
849
850        if by_col {
851            // Sort columns by the specified row
852            let sort_row_idx = (sort_index - 1).max(0) as usize;
853            if sort_row_idx >= rows {
854                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
855                    ExcelError::new(ExcelErrorKind::Value),
856                )));
857            }
858
859            // Extract columns as vectors
860            let mut columns: Vec<(usize, Vec<LiteralValue>)> = Vec::with_capacity(cols);
861            for c in 0..cols {
862                let mut col_vals: Vec<LiteralValue> = Vec::with_capacity(rows);
863                for r in 0..rows {
864                    col_vals.push(view.get_cell(r, c));
865                }
866                columns.push((c, col_vals));
867            }
868
869            // Sort columns by the value in sort_row_idx
870            columns.sort_by(|a, b| {
871                let val_a = &a.1[sort_row_idx];
872                let val_b = &b.1[sort_row_idx];
873                let cmp = cmp_for_lookup(val_a, val_b).unwrap_or(0);
874                if ascending { cmp.cmp(&0) } else { 0.cmp(&cmp) }
875            });
876
877            // Reconstruct the array with sorted columns
878            let mut out: Vec<Vec<LiteralValue>> = vec![Vec::with_capacity(cols); rows];
879            for (_orig_idx, col_vals) in columns {
880                for (r, val) in col_vals.into_iter().enumerate() {
881                    out[r].push(val);
882                }
883            }
884
885            Ok(collapse_if_scalar(out, _ctx.date_system()))
886        } else {
887            // Sort rows by the specified column
888            let sort_col_idx = (sort_index - 1).max(0) as usize;
889            if sort_col_idx >= cols {
890                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
891                    ExcelError::new(ExcelErrorKind::Value),
892                )));
893            }
894
895            // Extract rows
896            let mut row_data: Vec<Vec<LiteralValue>> = Vec::with_capacity(rows);
897            for r in 0..rows {
898                let mut row_vals: Vec<LiteralValue> = Vec::with_capacity(cols);
899                for c in 0..cols {
900                    row_vals.push(view.get_cell(r, c));
901                }
902                row_data.push(row_vals);
903            }
904
905            // Sort rows by the value in sort_col_idx
906            row_data.sort_by(|a, b| {
907                let val_a = &a[sort_col_idx];
908                let val_b = &b[sort_col_idx];
909                let cmp = cmp_for_lookup(val_a, val_b).unwrap_or(0);
910                if ascending { cmp.cmp(&0) } else { 0.cmp(&cmp) }
911            });
912
913            Ok(collapse_if_scalar(row_data, _ctx.date_system()))
914        }
915    }
916}
917
918/* ───────────────────────── SORTBY() ───────────────────────── */
919
920#[derive(Debug)]
921pub struct SortByFn;
922impl Function for SortByFn {
923    func_caps!(PURE);
924    fn name(&self) -> &'static str {
925        "SORTBY"
926    }
927    fn min_args(&self) -> usize {
928        2
929    }
930    fn variadic(&self) -> bool {
931        true
932    }
933    fn arg_schema(&self) -> &'static [ArgSchema] {
934        use once_cell::sync::Lazy;
935        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
936            vec![
937                // array
938                ArgSchema {
939                    kinds: smallvec::smallvec![ArgKind::Range],
940                    required: true,
941                    by_ref: true,
942                    shape: ShapeKind::Range,
943                    coercion: CoercionPolicy::None,
944                    max: None,
945                    repeating: None,
946                    default: None,
947                },
948                // by_array1
949                ArgSchema {
950                    kinds: smallvec::smallvec![ArgKind::Range],
951                    required: true,
952                    by_ref: true,
953                    shape: ShapeKind::Range,
954                    coercion: CoercionPolicy::None,
955                    max: None,
956                    repeating: None,
957                    default: None,
958                },
959                // sort_order1 (optional, default 1)
960                ArgSchema {
961                    kinds: smallvec::smallvec![ArgKind::Number],
962                    required: false,
963                    by_ref: false,
964                    shape: ShapeKind::Scalar,
965                    coercion: CoercionPolicy::NumberLenientText,
966                    max: None,
967                    repeating: None,
968                    default: Some(LiteralValue::Int(1)),
969                },
970                // Additional by_array/sort_order pairs can follow (variadic)
971            ]
972        });
973        &SCHEMA
974    }
975    fn eval<'a, 'b, 'c>(
976        &self,
977        args: &'c [ArgumentHandle<'a, 'b>],
978        _ctx: &dyn FunctionContext<'b>,
979    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
980        if args.len() < 2 {
981            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
982                ExcelError::new(ExcelErrorKind::Value),
983            )));
984        }
985
986        let view = match args[0].range_view() {
987            Ok(v) => v,
988            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
989        };
990        let (rows, cols) = view.dims();
991        if rows == 0 || cols == 0 {
992            return Ok(crate::traits::CalcValue::Range(
993                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
994            ));
995        }
996
997        // Parse sort criteria: pairs of (by_array, sort_order)
998        // Arguments after array: by_array1, [sort_order1], [by_array2], [sort_order2], ...
999        let mut sort_criteria: Vec<(Vec<LiteralValue>, bool)> = Vec::new();
1000        let mut arg_idx = 1;
1001
1002        while arg_idx < args.len() {
1003            // by_array
1004            let by_view = match args[arg_idx].range_view() {
1005                Ok(v) => v,
1006                Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
1007            };
1008            let (by_rows, by_cols) = by_view.dims();
1009
1010            // The by_array should be 1-D and match the number of rows in the main array
1011            let by_values: Vec<LiteralValue> = if by_cols == 1 {
1012                if by_rows != rows {
1013                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1014                        ExcelError::new(ExcelErrorKind::Value),
1015                    )));
1016                }
1017                (0..by_rows).map(|r| by_view.get_cell(r, 0)).collect()
1018            } else if by_rows == 1 {
1019                if by_cols != rows {
1020                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1021                        ExcelError::new(ExcelErrorKind::Value),
1022                    )));
1023                }
1024                (0..by_cols).map(|c| by_view.get_cell(0, c)).collect()
1025            } else {
1026                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1027                    ExcelError::new(ExcelErrorKind::Value),
1028                )));
1029            };
1030
1031            arg_idx += 1;
1032
1033            // sort_order (optional)
1034            let ascending = if arg_idx < args.len() {
1035                // TODO(phase6): SORTBY parsing can mis-handle multi-criteria sort_order.
1036                // Check if next arg is a number (sort_order) or a range (next by_array)
1037                match args[arg_idx].value() {
1038                    Ok(v) => {
1039                        let lit = v.into_literal();
1040                        match lit {
1041                            LiteralValue::Int(i) => {
1042                                arg_idx += 1;
1043                                i >= 0
1044                            }
1045                            LiteralValue::Number(n) => {
1046                                arg_idx += 1;
1047                                n >= 0.0
1048                            }
1049                            _ => true, // Next arg is likely a range, use default ascending
1050                        }
1051                    }
1052                    Err(_) => true,
1053                }
1054            } else {
1055                true
1056            };
1057
1058            sort_criteria.push((by_values, ascending));
1059        }
1060
1061        if sort_criteria.is_empty() {
1062            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1063                ExcelError::new(ExcelErrorKind::Value),
1064            )));
1065        }
1066
1067        // Extract rows with their indices
1068        let mut indexed_rows: Vec<(usize, Vec<LiteralValue>)> = Vec::with_capacity(rows);
1069        for r in 0..rows {
1070            let mut row_vals: Vec<LiteralValue> = Vec::with_capacity(cols);
1071            for c in 0..cols {
1072                row_vals.push(view.get_cell(r, c));
1073            }
1074            indexed_rows.push((r, row_vals));
1075        }
1076
1077        // Sort using all criteria
1078        indexed_rows.sort_by(|a, b| {
1079            for (by_values, ascending) in &sort_criteria {
1080                let val_a = &by_values[a.0];
1081                let val_b = &by_values[b.0];
1082                let cmp = cmp_for_lookup(val_a, val_b).unwrap_or(0);
1083                if cmp != 0 {
1084                    return if *ascending { cmp.cmp(&0) } else { 0.cmp(&cmp) };
1085                }
1086            }
1087            std::cmp::Ordering::Equal
1088        });
1089
1090        // Extract sorted rows
1091        let out: Vec<Vec<LiteralValue>> = indexed_rows.into_iter().map(|(_, row)| row).collect();
1092
1093        Ok(collapse_if_scalar(out, _ctx.date_system()))
1094    }
1095}
1096
1097/* ───────────────────────── RANDARRAY() ───────────────────────── */
1098
1099#[derive(Debug)]
1100pub struct RandArrayFn;
1101impl Function for RandArrayFn {
1102    // Note: RANDARRAY is NOT pure - it returns different values on each evaluation
1103    fn caps(&self) -> crate::function::FnCaps {
1104        crate::function::FnCaps::empty()
1105    }
1106    fn name(&self) -> &'static str {
1107        "RANDARRAY"
1108    }
1109    fn min_args(&self) -> usize {
1110        0
1111    }
1112    fn variadic(&self) -> bool {
1113        true
1114    }
1115    fn arg_schema(&self) -> &'static [ArgSchema] {
1116        use once_cell::sync::Lazy;
1117        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
1118            vec![
1119                // rows (default 1)
1120                ArgSchema {
1121                    kinds: smallvec::smallvec![ArgKind::Number],
1122                    required: false,
1123                    by_ref: false,
1124                    shape: ShapeKind::Scalar,
1125                    coercion: CoercionPolicy::NumberLenientText,
1126                    max: None,
1127                    repeating: None,
1128                    default: Some(LiteralValue::Int(1)),
1129                },
1130                // columns (default 1)
1131                ArgSchema {
1132                    kinds: smallvec::smallvec![ArgKind::Number],
1133                    required: false,
1134                    by_ref: false,
1135                    shape: ShapeKind::Scalar,
1136                    coercion: CoercionPolicy::NumberLenientText,
1137                    max: None,
1138                    repeating: None,
1139                    default: Some(LiteralValue::Int(1)),
1140                },
1141                // min (default 0)
1142                ArgSchema {
1143                    kinds: smallvec::smallvec![ArgKind::Number],
1144                    required: false,
1145                    by_ref: false,
1146                    shape: ShapeKind::Scalar,
1147                    coercion: CoercionPolicy::NumberLenientText,
1148                    max: None,
1149                    repeating: None,
1150                    default: Some(LiteralValue::Int(0)),
1151                },
1152                // max (default 1)
1153                ArgSchema {
1154                    kinds: smallvec::smallvec![ArgKind::Number],
1155                    required: false,
1156                    by_ref: false,
1157                    shape: ShapeKind::Scalar,
1158                    coercion: CoercionPolicy::NumberLenientText,
1159                    max: None,
1160                    repeating: None,
1161                    default: Some(LiteralValue::Int(1)),
1162                },
1163                // whole_number (default FALSE)
1164                ArgSchema {
1165                    kinds: smallvec::smallvec![ArgKind::Logical],
1166                    required: false,
1167                    by_ref: false,
1168                    shape: ShapeKind::Scalar,
1169                    coercion: CoercionPolicy::Logical,
1170                    max: None,
1171                    repeating: None,
1172                    default: Some(LiteralValue::Boolean(false)),
1173                },
1174            ]
1175        });
1176        &SCHEMA
1177    }
1178    fn eval<'a, 'b, 'c>(
1179        &self,
1180        args: &'c [ArgumentHandle<'a, 'b>],
1181        _ctx: &dyn FunctionContext<'b>,
1182    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1183        use rand::Rng;
1184
1185        // Extract numbers (allow float but coerce to i64 for dimensions)
1186        let num = |a: &ArgumentHandle| -> Result<f64, ExcelError> {
1187            Ok(match a.value()?.into_literal() {
1188                LiteralValue::Int(i) => i as f64,
1189                LiteralValue::Number(n) => n,
1190                LiteralValue::Error(e) => return Err(e),
1191                _other => {
1192                    return Err(ExcelError::new(ExcelErrorKind::Value));
1193                }
1194            })
1195        };
1196
1197        let rows = if !args.is_empty() {
1198            num(&args[0])? as i64
1199        } else {
1200            1
1201        };
1202        let cols = if args.len() >= 2 {
1203            num(&args[1])? as i64
1204        } else {
1205            1
1206        };
1207        let min_val = if args.len() >= 3 { num(&args[2])? } else { 0.0 };
1208        let max_val = if args.len() >= 4 { num(&args[3])? } else { 1.0 };
1209        let whole_number = if args.len() >= 5 {
1210            matches!(args[4].value()?.into_literal(), LiteralValue::Boolean(true))
1211        } else {
1212            false
1213        };
1214
1215        // Validate dimensions
1216        if rows <= 0 || cols <= 0 {
1217            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1218                ExcelError::new(ExcelErrorKind::Value),
1219            )));
1220        }
1221
1222        // Validate min <= max for whole numbers
1223        if whole_number && min_val > max_val {
1224            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1225                ExcelError::new(ExcelErrorKind::Value),
1226            )));
1227        }
1228
1229        let mut rng = rand::thread_rng();
1230        let mut out: Vec<Vec<LiteralValue>> = Vec::with_capacity(rows as usize);
1231
1232        for _r in 0..rows {
1233            let mut row_vec: Vec<LiteralValue> = Vec::with_capacity(cols as usize);
1234            for _c in 0..cols {
1235                let value = if whole_number {
1236                    // Generate random integer in range [min, max] inclusive
1237                    let min_int = min_val.ceil() as i64;
1238                    let max_int = max_val.floor() as i64;
1239                    if min_int > max_int {
1240                        return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1241                            ExcelError::new(ExcelErrorKind::Value),
1242                        )));
1243                    }
1244                    let rand_int = rng.gen_range(min_int..=max_int);
1245                    LiteralValue::Int(rand_int)
1246                } else {
1247                    // Generate random float in range [min, max)
1248                    let rand_float = rng.r#gen::<f64>() * (max_val - min_val) + min_val;
1249                    LiteralValue::Number(rand_float)
1250                };
1251                row_vec.push(value);
1252            }
1253            out.push(row_vec);
1254        }
1255
1256        Ok(collapse_if_scalar(out, _ctx.date_system()))
1257    }
1258}
1259
1260/* ───────────────────────── GROUPBY() ───────────────────────── */
1261
1262/// Aggregation type for GROUPBY and PIVOTBY
1263#[derive(Debug, Clone, Copy, PartialEq, Eq)]
1264enum GroupAggregation {
1265    Sum,
1266    Average,
1267    Count,
1268    CountA,
1269    Max,
1270    Min,
1271    Product,
1272    StDev,
1273    StDevP,
1274    Var,
1275    VarP,
1276    Median,
1277}
1278
1279impl GroupAggregation {
1280    fn from_literal(val: &LiteralValue) -> Option<Self> {
1281        match val {
1282            LiteralValue::Text(s) => Self::from_str(s),
1283            LiteralValue::Int(n) => Self::from_num(*n as i32),
1284            LiteralValue::Number(n) => Self::from_num(*n as i32),
1285            _ => None,
1286        }
1287    }
1288
1289    fn from_str(s: &str) -> Option<Self> {
1290        let upper = s.to_ascii_uppercase();
1291        match upper.as_str() {
1292            "SUM" => Some(Self::Sum),
1293            "AVERAGE" | "AVG" => Some(Self::Average),
1294            "COUNT" => Some(Self::Count),
1295            "COUNTA" => Some(Self::CountA),
1296            "MAX" => Some(Self::Max),
1297            "MIN" => Some(Self::Min),
1298            "PRODUCT" => Some(Self::Product),
1299            "STDEV" | "STDEV.S" => Some(Self::StDev),
1300            "STDEVP" | "STDEV.P" => Some(Self::StDevP),
1301            "VAR" | "VAR.S" => Some(Self::Var),
1302            "VARP" | "VAR.P" => Some(Self::VarP),
1303            "MEDIAN" => Some(Self::Median),
1304            _ => None,
1305        }
1306    }
1307
1308    fn from_num(n: i32) -> Option<Self> {
1309        // Excel's AGGREGATE function_num mapping (common subset)
1310        match n {
1311            1 => Some(Self::Average),
1312            2 => Some(Self::Count),
1313            3 => Some(Self::CountA),
1314            4 => Some(Self::Max),
1315            5 => Some(Self::Min),
1316            6 => Some(Self::Product),
1317            7 => Some(Self::StDev),
1318            8 => Some(Self::StDevP),
1319            9 => Some(Self::Sum),
1320            10 => Some(Self::Var),
1321            11 => Some(Self::VarP),
1322            12 => Some(Self::Median),
1323            _ => None,
1324        }
1325    }
1326
1327    fn apply(&self, values: &[f64]) -> f64 {
1328        if values.is_empty() {
1329            return match self {
1330                Self::Count | Self::CountA => 0.0,
1331                Self::Sum | Self::Product => 0.0,
1332                _ => f64::NAN,
1333            };
1334        }
1335
1336        match self {
1337            Self::Sum => values.iter().sum(),
1338            Self::Average => values.iter().sum::<f64>() / values.len() as f64,
1339            Self::Count | Self::CountA => values.len() as f64,
1340            Self::Max => values.iter().copied().fold(f64::NEG_INFINITY, f64::max),
1341            Self::Min => values.iter().copied().fold(f64::INFINITY, f64::min),
1342            Self::Product => values.iter().product(),
1343            Self::StDev => {
1344                if values.len() < 2 {
1345                    return f64::NAN;
1346                }
1347                let mean = values.iter().sum::<f64>() / values.len() as f64;
1348                let variance = values.iter().map(|v| (v - mean).powi(2)).sum::<f64>()
1349                    / (values.len() - 1) as f64;
1350                variance.sqrt()
1351            }
1352            Self::StDevP => {
1353                let mean = values.iter().sum::<f64>() / values.len() as f64;
1354                let variance =
1355                    values.iter().map(|v| (v - mean).powi(2)).sum::<f64>() / values.len() as f64;
1356                variance.sqrt()
1357            }
1358            Self::Var => {
1359                if values.len() < 2 {
1360                    return f64::NAN;
1361                }
1362                let mean = values.iter().sum::<f64>() / values.len() as f64;
1363                values.iter().map(|v| (v - mean).powi(2)).sum::<f64>() / (values.len() - 1) as f64
1364            }
1365            Self::VarP => {
1366                let mean = values.iter().sum::<f64>() / values.len() as f64;
1367                values.iter().map(|v| (v - mean).powi(2)).sum::<f64>() / values.len() as f64
1368            }
1369            Self::Median => {
1370                let mut sorted = values.to_vec();
1371                sorted.sort_by(|a, b| a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal));
1372                let mid = sorted.len() / 2;
1373                if sorted.len() % 2 == 0 {
1374                    (sorted[mid - 1] + sorted[mid]) / 2.0
1375                } else {
1376                    sorted[mid]
1377                }
1378            }
1379        }
1380    }
1381}
1382
1383/// Helper to convert LiteralValue to a group key string
1384fn literal_to_group_key(v: &LiteralValue) -> String {
1385    match v {
1386        LiteralValue::Text(s) => s.clone(),
1387        LiteralValue::Int(i) => i.to_string(),
1388        LiteralValue::Number(n) => format!("{:.10}", n),
1389        LiteralValue::Boolean(b) => if *b { "TRUE" } else { "FALSE" }.to_string(),
1390        LiteralValue::Empty => String::new(),
1391        LiteralValue::Error(e) => format!("#{:?}", e.kind),
1392        LiteralValue::Array(_) => "[Array]".to_string(),
1393        LiteralValue::Date(d) => d.to_string(),
1394        LiteralValue::DateTime(dt) => dt.to_string(),
1395        LiteralValue::Time(t) => t.to_string(),
1396        LiteralValue::Duration(d) => format!("{:?}", d),
1397        LiteralValue::Pending => "[Pending]".to_string(),
1398    }
1399}
1400
1401/// Helper to extract numeric value for aggregation
1402fn literal_to_num_opt(v: &LiteralValue) -> Option<f64> {
1403    match v {
1404        LiteralValue::Number(n) => Some(*n),
1405        LiteralValue::Int(i) => Some(*i as f64),
1406        LiteralValue::Boolean(b) => Some(if *b { 1.0 } else { 0.0 }),
1407        _ => None,
1408    }
1409}
1410
1411#[derive(Debug)]
1412pub struct GroupByFn;
1413
1414impl Function for GroupByFn {
1415    func_caps!(PURE);
1416    fn name(&self) -> &'static str {
1417        "GROUPBY"
1418    }
1419    fn min_args(&self) -> usize {
1420        3
1421    }
1422    fn variadic(&self) -> bool {
1423        true
1424    }
1425    fn arg_schema(&self) -> &'static [ArgSchema] {
1426        use once_cell::sync::Lazy;
1427        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
1428            vec![
1429                // row_fields - range to group by
1430                ArgSchema {
1431                    kinds: smallvec::smallvec![ArgKind::Range],
1432                    required: true,
1433                    by_ref: true,
1434                    shape: ShapeKind::Range,
1435                    coercion: CoercionPolicy::None,
1436                    max: None,
1437                    repeating: None,
1438                    default: None,
1439                },
1440                // values - range of values to aggregate
1441                ArgSchema {
1442                    kinds: smallvec::smallvec![ArgKind::Range],
1443                    required: true,
1444                    by_ref: true,
1445                    shape: ShapeKind::Range,
1446                    coercion: CoercionPolicy::None,
1447                    max: None,
1448                    repeating: None,
1449                    default: None,
1450                },
1451                // function - aggregation function (SUM, AVERAGE, etc.)
1452                ArgSchema {
1453                    kinds: smallvec::smallvec![ArgKind::Any],
1454                    required: true,
1455                    by_ref: false,
1456                    shape: ShapeKind::Scalar,
1457                    coercion: CoercionPolicy::None,
1458                    max: None,
1459                    repeating: None,
1460                    default: None,
1461                },
1462                // field_headers (optional) - 0: no headers, 1: has headers (default), 2: generate headers, 3: has headers + generate
1463                ArgSchema {
1464                    kinds: smallvec::smallvec![ArgKind::Number],
1465                    required: false,
1466                    by_ref: false,
1467                    shape: ShapeKind::Scalar,
1468                    coercion: CoercionPolicy::NumberLenientText,
1469                    max: None,
1470                    repeating: None,
1471                    default: Some(LiteralValue::Int(1)),
1472                },
1473                // total_depth (optional) - 0: no totals, 1: grand total, 2: subtotals, etc.
1474                ArgSchema {
1475                    kinds: smallvec::smallvec![ArgKind::Number],
1476                    required: false,
1477                    by_ref: false,
1478                    shape: ShapeKind::Scalar,
1479                    coercion: CoercionPolicy::NumberLenientText,
1480                    max: None,
1481                    repeating: None,
1482                    default: Some(LiteralValue::Int(0)),
1483                },
1484                // sort_order (optional) - 0: no sorting, 1: ascending, -1: descending, 2: by value asc, -2: by value desc
1485                ArgSchema {
1486                    kinds: smallvec::smallvec![ArgKind::Number],
1487                    required: false,
1488                    by_ref: false,
1489                    shape: ShapeKind::Scalar,
1490                    coercion: CoercionPolicy::NumberLenientText,
1491                    max: None,
1492                    repeating: None,
1493                    default: Some(LiteralValue::Int(0)),
1494                },
1495            ]
1496        });
1497        &SCHEMA
1498    }
1499
1500    fn eval<'a, 'b, 'c>(
1501        &self,
1502        args: &'c [ArgumentHandle<'a, 'b>],
1503        _ctx: &dyn FunctionContext<'b>,
1504    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1505        if args.len() < 3 {
1506            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1507                ExcelError::new(ExcelErrorKind::Value),
1508            )));
1509        }
1510
1511        // Get row_fields and values ranges
1512        let row_fields_view = match args[0].range_view() {
1513            Ok(v) => v,
1514            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
1515        };
1516        let values_view = match args[1].range_view() {
1517            Ok(v) => v,
1518            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
1519        };
1520
1521        // Parse aggregation function
1522        let agg_val = args[2].value()?.into_literal();
1523        let aggregation = match GroupAggregation::from_literal(&agg_val) {
1524            Some(a) => a,
1525            None => {
1526                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1527                    ExcelError::new(ExcelErrorKind::Value)
1528                        .with_message("Invalid aggregation function"),
1529                )));
1530            }
1531        };
1532
1533        // Parse optional parameters
1534        let field_headers = if args.len() >= 4 {
1535            match args[3].value()?.into_literal() {
1536                LiteralValue::Int(i) => i as i32,
1537                LiteralValue::Number(n) => n as i32,
1538                _ => 1,
1539            }
1540        } else {
1541            1
1542        };
1543
1544        let total_depth = if args.len() >= 5 {
1545            match args[4].value()?.into_literal() {
1546                LiteralValue::Int(i) => i as i32,
1547                LiteralValue::Number(n) => n as i32,
1548                _ => 0,
1549            }
1550        } else {
1551            0
1552        };
1553
1554        let sort_order = if args.len() >= 6 {
1555            match args[5].value()?.into_literal() {
1556                LiteralValue::Int(i) => i as i32,
1557                LiteralValue::Number(n) => n as i32,
1558                _ => 0,
1559            }
1560        } else {
1561            0
1562        };
1563
1564        let (rf_rows, rf_cols) = row_fields_view.dims();
1565        let (val_rows, val_cols) = values_view.dims();
1566
1567        // Determine if we have headers
1568        let has_headers = field_headers == 1 || field_headers == 3;
1569        let data_start_row = if has_headers { 1 } else { 0 };
1570
1571        // Validate that row counts match (accounting for headers)
1572        if rf_rows != val_rows {
1573            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1574                ExcelError::new(ExcelErrorKind::Value)
1575                    .with_message("Row fields and values must have same number of rows"),
1576            )));
1577        }
1578
1579        if rf_rows <= data_start_row {
1580            // No data rows
1581            return Ok(crate::traits::CalcValue::Range(
1582                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
1583            ));
1584        }
1585
1586        // Build groups: key -> list of values for each value column
1587        // For multi-column row_fields, concatenate keys
1588        let mut groups: std::collections::HashMap<String, Vec<Vec<f64>>> = HashMap::new();
1589        let mut group_order: Vec<String> = Vec::new();
1590
1591        for r in data_start_row..rf_rows {
1592            // Build composite key from all row_field columns
1593            let mut key_parts: Vec<String> = Vec::with_capacity(rf_cols);
1594            for c in 0..rf_cols {
1595                key_parts.push(literal_to_group_key(&row_fields_view.get_cell(r, c)));
1596            }
1597            let key = key_parts.join("\x00"); // Use null separator for composite keys
1598
1599            // Get values for this row
1600            let mut row_values: Vec<Option<f64>> = Vec::with_capacity(val_cols);
1601            for c in 0..val_cols {
1602                row_values.push(literal_to_num_opt(&values_view.get_cell(r, c)));
1603            }
1604
1605            // Add to groups
1606            if !groups.contains_key(&key) {
1607                group_order.push(key.clone());
1608                groups.insert(key.clone(), vec![Vec::new(); val_cols]);
1609            }
1610
1611            let group_vals = groups.get_mut(&key).unwrap();
1612            for (c, val) in row_values.iter().enumerate() {
1613                if let Some(v) = val {
1614                    group_vals[c].push(*v);
1615                }
1616            }
1617        }
1618
1619        // Sort groups if requested
1620        if sort_order != 0 {
1621            group_order.sort_by(|a, b| if sort_order > 0 { a.cmp(b) } else { b.cmp(a) });
1622        }
1623
1624        // Build output
1625        let mut output: Vec<Vec<LiteralValue>> = Vec::new();
1626
1627        // Add header row if requested
1628        let generate_headers = field_headers == 2 || field_headers == 3;
1629        if generate_headers {
1630            let mut header_row: Vec<LiteralValue> = Vec::new();
1631            // Row field headers
1632            for c in 0..rf_cols {
1633                if has_headers {
1634                    header_row.push(row_fields_view.get_cell(0, c));
1635                } else {
1636                    header_row.push(LiteralValue::Text(format!("Field{}", c + 1)));
1637                }
1638            }
1639            // Value headers
1640            for c in 0..val_cols {
1641                if has_headers {
1642                    header_row.push(values_view.get_cell(0, c));
1643                } else {
1644                    header_row.push(LiteralValue::Text(format!("Value{}", c + 1)));
1645                }
1646            }
1647            output.push(header_row);
1648        }
1649
1650        // Add grouped data rows
1651        for key in &group_order {
1652            let mut row: Vec<LiteralValue> = Vec::new();
1653
1654            // Add row field values (split composite key)
1655            let key_parts: Vec<&str> = key.split('\x00').collect();
1656            for part in &key_parts {
1657                row.push(LiteralValue::Text(part.to_string()));
1658            }
1659
1660            // Add aggregated values
1661            let group_vals = groups.get(key).unwrap();
1662            for col_vals in group_vals {
1663                let result = aggregation.apply(col_vals);
1664                if result.is_nan() {
1665                    row.push(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
1666                } else if result.fract() == 0.0 && result.abs() < i64::MAX as f64 {
1667                    row.push(LiteralValue::Int(result as i64));
1668                } else {
1669                    row.push(LiteralValue::Number(result));
1670                }
1671            }
1672            output.push(row);
1673        }
1674
1675        // Add grand total if requested
1676        if total_depth >= 1 {
1677            let mut total_row: Vec<LiteralValue> = Vec::new();
1678            // Empty cells for row fields (except first which says "Grand Total")
1679            total_row.push(LiteralValue::Text("Grand Total".to_string()));
1680            for _ in 1..rf_cols {
1681                total_row.push(LiteralValue::Empty);
1682            }
1683
1684            // Aggregate all values across all groups
1685            for c in 0..val_cols {
1686                let mut all_vals: Vec<f64> = Vec::new();
1687                for group_vals in groups.values() {
1688                    all_vals.extend(&group_vals[c]);
1689                }
1690                let result = aggregation.apply(&all_vals);
1691                if result.is_nan() {
1692                    total_row.push(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
1693                } else if result.fract() == 0.0 && result.abs() < i64::MAX as f64 {
1694                    total_row.push(LiteralValue::Int(result as i64));
1695                } else {
1696                    total_row.push(LiteralValue::Number(result));
1697                }
1698            }
1699            output.push(total_row);
1700        }
1701
1702        if output.is_empty() {
1703            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1704                ExcelError::new(ExcelErrorKind::Calc),
1705            )));
1706        }
1707
1708        Ok(collapse_if_scalar(output, _ctx.date_system()))
1709    }
1710}
1711
1712/* ───────────────────────── PIVOTBY() ───────────────────────── */
1713
1714#[derive(Debug)]
1715pub struct PivotByFn;
1716
1717impl Function for PivotByFn {
1718    func_caps!(PURE);
1719    fn name(&self) -> &'static str {
1720        "PIVOTBY"
1721    }
1722    fn min_args(&self) -> usize {
1723        4
1724    }
1725    fn variadic(&self) -> bool {
1726        true
1727    }
1728    fn arg_schema(&self) -> &'static [ArgSchema] {
1729        use once_cell::sync::Lazy;
1730        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
1731            vec![
1732                // row_fields - range to group rows by
1733                ArgSchema {
1734                    kinds: smallvec::smallvec![ArgKind::Range],
1735                    required: true,
1736                    by_ref: true,
1737                    shape: ShapeKind::Range,
1738                    coercion: CoercionPolicy::None,
1739                    max: None,
1740                    repeating: None,
1741                    default: None,
1742                },
1743                // col_fields - range to group columns by
1744                ArgSchema {
1745                    kinds: smallvec::smallvec![ArgKind::Range],
1746                    required: true,
1747                    by_ref: true,
1748                    shape: ShapeKind::Range,
1749                    coercion: CoercionPolicy::None,
1750                    max: None,
1751                    repeating: None,
1752                    default: None,
1753                },
1754                // values - range of values to aggregate
1755                ArgSchema {
1756                    kinds: smallvec::smallvec![ArgKind::Range],
1757                    required: true,
1758                    by_ref: true,
1759                    shape: ShapeKind::Range,
1760                    coercion: CoercionPolicy::None,
1761                    max: None,
1762                    repeating: None,
1763                    default: None,
1764                },
1765                // function - aggregation function
1766                ArgSchema {
1767                    kinds: smallvec::smallvec![ArgKind::Any],
1768                    required: true,
1769                    by_ref: false,
1770                    shape: ShapeKind::Scalar,
1771                    coercion: CoercionPolicy::None,
1772                    max: None,
1773                    repeating: None,
1774                    default: None,
1775                },
1776                // field_headers (optional)
1777                ArgSchema {
1778                    kinds: smallvec::smallvec![ArgKind::Number],
1779                    required: false,
1780                    by_ref: false,
1781                    shape: ShapeKind::Scalar,
1782                    coercion: CoercionPolicy::NumberLenientText,
1783                    max: None,
1784                    repeating: None,
1785                    default: Some(LiteralValue::Int(1)),
1786                },
1787                // row_total_depth (optional)
1788                ArgSchema {
1789                    kinds: smallvec::smallvec![ArgKind::Number],
1790                    required: false,
1791                    by_ref: false,
1792                    shape: ShapeKind::Scalar,
1793                    coercion: CoercionPolicy::NumberLenientText,
1794                    max: None,
1795                    repeating: None,
1796                    default: Some(LiteralValue::Int(0)),
1797                },
1798                // row_sort_order (optional)
1799                ArgSchema {
1800                    kinds: smallvec::smallvec![ArgKind::Number],
1801                    required: false,
1802                    by_ref: false,
1803                    shape: ShapeKind::Scalar,
1804                    coercion: CoercionPolicy::NumberLenientText,
1805                    max: None,
1806                    repeating: None,
1807                    default: Some(LiteralValue::Int(0)),
1808                },
1809                // col_total_depth (optional)
1810                ArgSchema {
1811                    kinds: smallvec::smallvec![ArgKind::Number],
1812                    required: false,
1813                    by_ref: false,
1814                    shape: ShapeKind::Scalar,
1815                    coercion: CoercionPolicy::NumberLenientText,
1816                    max: None,
1817                    repeating: None,
1818                    default: Some(LiteralValue::Int(0)),
1819                },
1820                // col_sort_order (optional)
1821                ArgSchema {
1822                    kinds: smallvec::smallvec![ArgKind::Number],
1823                    required: false,
1824                    by_ref: false,
1825                    shape: ShapeKind::Scalar,
1826                    coercion: CoercionPolicy::NumberLenientText,
1827                    max: None,
1828                    repeating: None,
1829                    default: Some(LiteralValue::Int(0)),
1830                },
1831            ]
1832        });
1833        &SCHEMA
1834    }
1835
1836    fn eval<'a, 'b, 'c>(
1837        &self,
1838        args: &'c [ArgumentHandle<'a, 'b>],
1839        _ctx: &dyn FunctionContext<'b>,
1840    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1841        if args.len() < 4 {
1842            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1843                ExcelError::new(ExcelErrorKind::Value),
1844            )));
1845        }
1846
1847        // Get ranges
1848        let row_fields_view = match args[0].range_view() {
1849            Ok(v) => v,
1850            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
1851        };
1852        let col_fields_view = match args[1].range_view() {
1853            Ok(v) => v,
1854            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
1855        };
1856        let values_view = match args[2].range_view() {
1857            Ok(v) => v,
1858            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
1859        };
1860
1861        // Parse aggregation function
1862        let agg_val = args[3].value()?.into_literal();
1863        let aggregation = match GroupAggregation::from_literal(&agg_val) {
1864            Some(a) => a,
1865            None => {
1866                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1867                    ExcelError::new(ExcelErrorKind::Value)
1868                        .with_message("Invalid aggregation function"),
1869                )));
1870            }
1871        };
1872
1873        // Parse optional parameters
1874        let field_headers = if args.len() >= 5 {
1875            match args[4].value()?.into_literal() {
1876                LiteralValue::Int(i) => i as i32,
1877                LiteralValue::Number(n) => n as i32,
1878                _ => 1,
1879            }
1880        } else {
1881            1
1882        };
1883
1884        let row_total_depth = if args.len() >= 6 {
1885            match args[5].value()?.into_literal() {
1886                LiteralValue::Int(i) => i as i32,
1887                LiteralValue::Number(n) => n as i32,
1888                _ => 0,
1889            }
1890        } else {
1891            0
1892        };
1893
1894        let row_sort_order = if args.len() >= 7 {
1895            match args[6].value()?.into_literal() {
1896                LiteralValue::Int(i) => i as i32,
1897                LiteralValue::Number(n) => n as i32,
1898                _ => 0,
1899            }
1900        } else {
1901            0
1902        };
1903
1904        let col_total_depth = if args.len() >= 8 {
1905            match args[7].value()?.into_literal() {
1906                LiteralValue::Int(i) => i as i32,
1907                LiteralValue::Number(n) => n as i32,
1908                _ => 0,
1909            }
1910        } else {
1911            0
1912        };
1913
1914        let col_sort_order = if args.len() >= 9 {
1915            match args[8].value()?.into_literal() {
1916                LiteralValue::Int(i) => i as i32,
1917                LiteralValue::Number(n) => n as i32,
1918                _ => 0,
1919            }
1920        } else {
1921            0
1922        };
1923
1924        let (rf_rows, rf_cols) = row_fields_view.dims();
1925        let (cf_rows, _cf_cols) = col_fields_view.dims();
1926        let (val_rows, _val_cols) = values_view.dims();
1927
1928        // Validate dimensions
1929        if rf_rows != cf_rows || rf_rows != val_rows {
1930            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1931                ExcelError::new(ExcelErrorKind::Value)
1932                    .with_message("All ranges must have same number of rows"),
1933            )));
1934        }
1935
1936        let has_headers = field_headers == 1 || field_headers == 3;
1937        let data_start_row = if has_headers { 1 } else { 0 };
1938
1939        if rf_rows <= data_start_row {
1940            return Ok(crate::traits::CalcValue::Range(
1941                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
1942            ));
1943        }
1944
1945        // Collect unique row and column keys
1946        let mut row_keys: Vec<String> = Vec::new();
1947        let mut col_keys: Vec<String> = Vec::new();
1948        let mut row_key_set: std::collections::HashSet<String> = std::collections::HashSet::new();
1949        let mut col_key_set: std::collections::HashSet<String> = std::collections::HashSet::new();
1950
1951        // Build pivot data: (row_key, col_key) -> values
1952        let mut pivot_data: HashMap<(String, String), Vec<f64>> = HashMap::new();
1953
1954        for r in data_start_row..rf_rows {
1955            // Build row key
1956            let mut row_key_parts: Vec<String> = Vec::with_capacity(rf_cols);
1957            for c in 0..rf_cols {
1958                row_key_parts.push(literal_to_group_key(&row_fields_view.get_cell(r, c)));
1959            }
1960            let row_key = row_key_parts.join("\x00");
1961
1962            // Build col key (use first column of col_fields for simplicity)
1963            // TODO(phase6): PIVOTBY only uses first col of col_fields/values.
1964            let col_key = literal_to_group_key(&col_fields_view.get_cell(r, 0));
1965
1966            // Get value (use first column of values)
1967            let val = literal_to_num_opt(&values_view.get_cell(r, 0));
1968
1969            // Track unique keys in order
1970            if !row_key_set.contains(&row_key) {
1971                row_key_set.insert(row_key.clone());
1972                row_keys.push(row_key.clone());
1973            }
1974            if !col_key_set.contains(&col_key) {
1975                col_key_set.insert(col_key.clone());
1976                col_keys.push(col_key.clone());
1977            }
1978
1979            // Add to pivot data
1980            let entry = pivot_data
1981                .entry((row_key, col_key))
1982                .or_insert_with(Vec::new);
1983            if let Some(v) = val {
1984                entry.push(v);
1985            }
1986        }
1987
1988        // Sort keys if requested
1989        if row_sort_order != 0 {
1990            row_keys.sort_by(|a, b| {
1991                if row_sort_order > 0 {
1992                    a.cmp(b)
1993                } else {
1994                    b.cmp(a)
1995                }
1996            });
1997        }
1998        if col_sort_order != 0 {
1999            col_keys.sort_by(|a, b| {
2000                if col_sort_order > 0 {
2001                    a.cmp(b)
2002                } else {
2003                    b.cmp(a)
2004                }
2005            });
2006        }
2007
2008        // Build output grid
2009        let mut output: Vec<Vec<LiteralValue>> = Vec::new();
2010
2011        // Header row: empty cells for row fields + column keys
2012        let generate_headers = field_headers == 2 || field_headers == 3;
2013        if generate_headers || has_headers {
2014            let mut header_row: Vec<LiteralValue> = Vec::new();
2015            // Empty cells for row field columns
2016            for _ in 0..rf_cols {
2017                header_row.push(LiteralValue::Empty);
2018            }
2019            // Column headers
2020            for col_key in &col_keys {
2021                // Split composite key and use the visible parts
2022                let parts: Vec<&str> = col_key.split('\x00').collect();
2023                header_row.push(LiteralValue::Text(parts.join(" ")));
2024            }
2025            // Total column header
2026            if col_total_depth >= 1 {
2027                header_row.push(LiteralValue::Text("Total".to_string()));
2028            }
2029            output.push(header_row);
2030        }
2031
2032        // Data rows
2033        for row_key in &row_keys {
2034            let mut row: Vec<LiteralValue> = Vec::new();
2035
2036            // Row field values
2037            let row_parts: Vec<&str> = row_key.split('\x00').collect();
2038            for part in &row_parts {
2039                row.push(LiteralValue::Text(part.to_string()));
2040            }
2041
2042            // Values for each column
2043            let mut row_total_vals: Vec<f64> = Vec::new();
2044            for col_key in &col_keys {
2045                let key = (row_key.clone(), col_key.clone());
2046                let vals = pivot_data.get(&key).map(|v| v.as_slice()).unwrap_or(&[]);
2047                let result = aggregation.apply(vals);
2048
2049                // Collect for row total
2050                row_total_vals.extend(vals);
2051
2052                if result.is_nan() || vals.is_empty() {
2053                    row.push(LiteralValue::Empty);
2054                } else if result.fract() == 0.0 && result.abs() < i64::MAX as f64 {
2055                    row.push(LiteralValue::Int(result as i64));
2056                } else {
2057                    row.push(LiteralValue::Number(result));
2058                }
2059            }
2060
2061            // Row total
2062            if col_total_depth >= 1 {
2063                let result = aggregation.apply(&row_total_vals);
2064                if result.is_nan() {
2065                    row.push(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
2066                } else if result.fract() == 0.0 && result.abs() < i64::MAX as f64 {
2067                    row.push(LiteralValue::Int(result as i64));
2068                } else {
2069                    row.push(LiteralValue::Number(result));
2070                }
2071            }
2072
2073            output.push(row);
2074        }
2075
2076        // Grand total row
2077        if row_total_depth >= 1 {
2078            let mut total_row: Vec<LiteralValue> = Vec::new();
2079            total_row.push(LiteralValue::Text("Total".to_string()));
2080            for _ in 1..rf_cols {
2081                total_row.push(LiteralValue::Empty);
2082            }
2083
2084            let mut grand_total_vals: Vec<f64> = Vec::new();
2085            for col_key in &col_keys {
2086                let mut col_vals: Vec<f64> = Vec::new();
2087                for row_key in &row_keys {
2088                    let key = (row_key.clone(), col_key.clone());
2089                    if let Some(vals) = pivot_data.get(&key) {
2090                        col_vals.extend(vals);
2091                    }
2092                }
2093                grand_total_vals.extend(&col_vals);
2094                let result = aggregation.apply(&col_vals);
2095                if result.is_nan() {
2096                    total_row.push(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
2097                } else if result.fract() == 0.0 && result.abs() < i64::MAX as f64 {
2098                    total_row.push(LiteralValue::Int(result as i64));
2099                } else {
2100                    total_row.push(LiteralValue::Number(result));
2101                }
2102            }
2103
2104            // Grand total of grand totals
2105            if col_total_depth >= 1 {
2106                let result = aggregation.apply(&grand_total_vals);
2107                if result.is_nan() {
2108                    total_row.push(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
2109                } else if result.fract() == 0.0 && result.abs() < i64::MAX as f64 {
2110                    total_row.push(LiteralValue::Int(result as i64));
2111                } else {
2112                    total_row.push(LiteralValue::Number(result));
2113                }
2114            }
2115
2116            output.push(total_row);
2117        }
2118
2119        if output.is_empty() {
2120            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
2121                ExcelError::new(ExcelErrorKind::Calc),
2122            )));
2123        }
2124
2125        Ok(collapse_if_scalar(output, _ctx.date_system()))
2126    }
2127}
2128
2129/* ───────────────────────── FILTER() ───────────────────────── */
2130
2131#[derive(Debug)]
2132pub struct FilterFn;
2133impl Function for FilterFn {
2134    func_caps!(PURE);
2135    fn name(&self) -> &'static str {
2136        "FILTER"
2137    }
2138    fn min_args(&self) -> usize {
2139        2
2140    }
2141    fn variadic(&self) -> bool {
2142        true
2143    }
2144    fn arg_schema(&self) -> &'static [ArgSchema] {
2145        use once_cell::sync::Lazy;
2146        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
2147            vec![
2148                // array
2149                ArgSchema {
2150                    kinds: smallvec::smallvec![ArgKind::Range],
2151                    required: true,
2152                    by_ref: true,
2153                    shape: ShapeKind::Range,
2154                    coercion: CoercionPolicy::None,
2155                    max: None,
2156                    repeating: None,
2157                    default: None,
2158                },
2159                // include
2160                ArgSchema {
2161                    kinds: smallvec::smallvec![ArgKind::Range],
2162                    required: true,
2163                    by_ref: true,
2164                    shape: ShapeKind::Range,
2165                    coercion: CoercionPolicy::None,
2166                    max: None,
2167                    repeating: None,
2168                    default: None,
2169                },
2170                // if_empty optional scalar
2171                ArgSchema {
2172                    kinds: smallvec::smallvec![ArgKind::Any],
2173                    required: false,
2174                    by_ref: false,
2175                    shape: ShapeKind::Scalar,
2176                    coercion: CoercionPolicy::None,
2177                    max: None,
2178                    repeating: None,
2179                    default: None,
2180                },
2181            ]
2182        });
2183        &SCHEMA
2184    }
2185    fn eval<'a, 'b, 'c>(
2186        &self,
2187        args: &'c [ArgumentHandle<'a, 'b>],
2188        _ctx: &dyn FunctionContext<'b>,
2189    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
2190        if args.len() < 2 {
2191            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
2192                ExcelError::new(ExcelErrorKind::Value),
2193            )));
2194        }
2195        let array_view = args[0].range_view()?;
2196        let include_view = args[1].range_view()?;
2197
2198        let (array_rows, array_cols) = array_view.dims();
2199        if array_rows == 0 || array_cols == 0 {
2200            return Ok(crate::traits::CalcValue::Range(
2201                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
2202            ));
2203        }
2204
2205        let (include_rows, include_cols) = include_view.dims();
2206        if include_rows != array_rows && include_rows != 1 {
2207            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
2208                ExcelError::new(ExcelErrorKind::Value),
2209            )));
2210        }
2211
2212        let mut result: Vec<Vec<LiteralValue>> = Vec::new();
2213        for r in 0..array_rows {
2214            let include_r = if include_rows == array_rows { r } else { 0 };
2215            let mut include = false;
2216            for c in 0..include_cols {
2217                if include_view.get_cell(include_r, c).is_truthy() {
2218                    include = true;
2219                    break;
2220                }
2221            }
2222
2223            if include {
2224                let mut row_out: Vec<LiteralValue> = Vec::with_capacity(array_cols);
2225                for c in 0..array_cols {
2226                    row_out.push(array_view.get_cell(r, c));
2227                }
2228                result.push(row_out);
2229            }
2230        }
2231
2232        if result.is_empty() {
2233            if args.len() >= 3 {
2234                return args[2].value();
2235            }
2236            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
2237                ExcelError::new(ExcelErrorKind::Calc),
2238            )));
2239        }
2240
2241        Ok(crate::traits::CalcValue::Range(
2242            crate::engine::range_view::RangeView::from_owned_rows(result, _ctx.date_system()),
2243        ))
2244    }
2245}
2246
2247/* ───────────────────────── UNIQUE() ───────────────────────── */
2248
2249#[derive(Debug)]
2250pub struct UniqueFn;
2251impl Function for UniqueFn {
2252    func_caps!(PURE);
2253    fn name(&self) -> &'static str {
2254        "UNIQUE"
2255    }
2256    fn min_args(&self) -> usize {
2257        1
2258    }
2259    fn variadic(&self) -> bool {
2260        true
2261    }
2262    fn arg_schema(&self) -> &'static [ArgSchema] {
2263        use once_cell::sync::Lazy;
2264        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
2265            vec![
2266                ArgSchema {
2267                    kinds: smallvec::smallvec![ArgKind::Range],
2268                    required: true,
2269                    by_ref: true,
2270                    shape: ShapeKind::Range,
2271                    coercion: CoercionPolicy::None,
2272                    max: None,
2273                    repeating: None,
2274                    default: None,
2275                },
2276                ArgSchema {
2277                    kinds: smallvec::smallvec![ArgKind::Logical],
2278                    required: false,
2279                    by_ref: false,
2280                    shape: ShapeKind::Scalar,
2281                    coercion: CoercionPolicy::Logical,
2282                    max: None,
2283                    repeating: None,
2284                    default: Some(LiteralValue::Boolean(false)),
2285                },
2286                ArgSchema {
2287                    kinds: smallvec::smallvec![ArgKind::Logical],
2288                    required: false,
2289                    by_ref: false,
2290                    shape: ShapeKind::Scalar,
2291                    coercion: CoercionPolicy::Logical,
2292                    max: None,
2293                    repeating: None,
2294                    default: Some(LiteralValue::Boolean(false)),
2295                },
2296            ]
2297        });
2298        &SCHEMA
2299    }
2300    fn eval<'a, 'b, 'c>(
2301        &self,
2302        args: &'c [ArgumentHandle<'a, 'b>],
2303        _ctx: &dyn FunctionContext<'b>,
2304    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
2305        let view = match args[0].range_view() {
2306            Ok(v) => v,
2307            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
2308        };
2309        let (rows, cols) = view.dims();
2310        if rows == 0 || cols == 0 {
2311            return Ok(crate::traits::CalcValue::Range(
2312                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
2313            ));
2314        }
2315
2316        let by_col = if args.len() >= 2 {
2317            matches!(args[1].value()?.into_literal(), LiteralValue::Boolean(true))
2318        } else {
2319            false
2320        };
2321        let exactly_once = if args.len() >= 3 {
2322            matches!(args[2].value()?.into_literal(), LiteralValue::Boolean(true))
2323        } else {
2324            false
2325        };
2326
2327        if by_col {
2328            #[derive(Hash, Eq, PartialEq, Clone)]
2329            struct ColKey(Vec<LiteralValue>);
2330
2331            let mut order: Vec<ColKey> = Vec::new();
2332            let mut counts: HashMap<ColKey, usize> = HashMap::new();
2333
2334            for c in 0..cols {
2335                let mut col_vals: Vec<LiteralValue> = Vec::with_capacity(rows);
2336                for r in 0..rows {
2337                    col_vals.push(view.get_cell(r, c));
2338                }
2339                let key = ColKey(col_vals);
2340                if !counts.contains_key(&key) {
2341                    order.push(key.clone());
2342                }
2343                *counts.entry(key).or_insert(0) += 1;
2344            }
2345
2346            let mut out: Vec<Vec<LiteralValue>> = Vec::new();
2347            for k in order {
2348                if !exactly_once || counts.get(&k) == Some(&1) {
2349                    out.push(k.0);
2350                }
2351            }
2352            return Ok(collapse_if_scalar(out, _ctx.date_system()));
2353        }
2354
2355        #[derive(Hash, Eq, PartialEq, Clone)]
2356        struct RowKey(Vec<LiteralValue>);
2357
2358        let mut order: Vec<RowKey> = Vec::new();
2359        let mut counts: HashMap<RowKey, usize> = HashMap::new();
2360        for r in 0..rows {
2361            let mut row_vals: Vec<LiteralValue> = Vec::with_capacity(cols);
2362            for c in 0..cols {
2363                row_vals.push(view.get_cell(r, c));
2364            }
2365            let key = RowKey(row_vals);
2366            if !counts.contains_key(&key) {
2367                order.push(key.clone());
2368            }
2369            *counts.entry(key).or_insert(0) += 1;
2370        }
2371
2372        let mut out: Vec<Vec<LiteralValue>> = Vec::new();
2373        for k in order {
2374            if !exactly_once || counts.get(&k) == Some(&1) {
2375                out.push(k.0);
2376            }
2377        }
2378        Ok(collapse_if_scalar(out, _ctx.date_system()))
2379    }
2380}
2381
2382/* ───────────────────────── SEQUENCE() ───────────────────────── */
2383
2384#[derive(Debug)]
2385pub struct SequenceFn;
2386impl Function for SequenceFn {
2387    func_caps!(PURE);
2388    fn name(&self) -> &'static str {
2389        "SEQUENCE"
2390    }
2391    fn min_args(&self) -> usize {
2392        1
2393    }
2394    fn variadic(&self) -> bool {
2395        true
2396    }
2397    fn arg_schema(&self) -> &'static [ArgSchema] {
2398        use once_cell::sync::Lazy;
2399        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
2400            vec![
2401                // rows
2402                ArgSchema {
2403                    kinds: smallvec::smallvec![ArgKind::Number],
2404                    required: true,
2405                    by_ref: false,
2406                    shape: ShapeKind::Scalar,
2407                    coercion: CoercionPolicy::NumberLenientText,
2408                    max: None,
2409                    repeating: None,
2410                    default: None,
2411                },
2412                // columns (default 1)
2413                ArgSchema {
2414                    kinds: smallvec::smallvec![ArgKind::Number],
2415                    required: false,
2416                    by_ref: false,
2417                    shape: ShapeKind::Scalar,
2418                    coercion: CoercionPolicy::NumberLenientText,
2419                    max: None,
2420                    repeating: None,
2421                    default: Some(LiteralValue::Int(1)),
2422                },
2423                // start (default 1)
2424                ArgSchema {
2425                    kinds: smallvec::smallvec![ArgKind::Number],
2426                    required: false,
2427                    by_ref: false,
2428                    shape: ShapeKind::Scalar,
2429                    coercion: CoercionPolicy::NumberLenientText,
2430                    max: None,
2431                    repeating: None,
2432                    default: Some(LiteralValue::Int(1)),
2433                },
2434                // step (default 1)
2435                ArgSchema {
2436                    kinds: smallvec::smallvec![ArgKind::Number],
2437                    required: false,
2438                    by_ref: false,
2439                    shape: ShapeKind::Scalar,
2440                    coercion: CoercionPolicy::NumberLenientText,
2441                    max: None,
2442                    repeating: None,
2443                    default: Some(LiteralValue::Int(1)),
2444                },
2445            ]
2446        });
2447        &SCHEMA
2448    }
2449    fn eval<'a, 'b, 'c>(
2450        &self,
2451        args: &'c [ArgumentHandle<'a, 'b>],
2452        _ctx: &dyn FunctionContext<'b>,
2453    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
2454        // Extract numbers (allow float but coerce to i64 for dimensions)
2455        let num = |a: &ArgumentHandle| -> Result<f64, ExcelError> {
2456            Ok(match a.value()?.into_literal() {
2457                LiteralValue::Int(i) => i as f64,
2458                LiteralValue::Number(n) => n,
2459                _other => {
2460                    return Err(ExcelError::new(ExcelErrorKind::Value));
2461                }
2462            })
2463        };
2464        let rows_f = num(&args[0])?;
2465        let rows = rows_f as i64;
2466        let cols = if args.len() >= 2 {
2467            num(&args[1])? as i64
2468        } else {
2469            1
2470        };
2471        let start = if args.len() >= 3 { num(&args[2])? } else { 1.0 };
2472        let step = if args.len() >= 4 { num(&args[3])? } else { 1.0 };
2473        if rows <= 0 || cols <= 0 {
2474            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
2475                ExcelError::new(ExcelErrorKind::Value),
2476            )));
2477        }
2478        let total = rows.saturating_mul(cols);
2479        // TODO(perf): guard extremely large allocations (#NUM!).
2480        let mut out: Vec<Vec<LiteralValue>> = Vec::with_capacity(rows as usize);
2481        let mut current = start;
2482        for _r in 0..rows {
2483            let mut row_vec: Vec<LiteralValue> = Vec::with_capacity(cols as usize);
2484            for _c in 0..cols {
2485                // Use Int when value integral & within i64 range
2486                if (current.fract().abs() < 1e-12) && current.abs() < (i64::MAX as f64) {
2487                    row_vec.push(LiteralValue::Int(current as i64));
2488                } else {
2489                    row_vec.push(LiteralValue::Number(current));
2490                }
2491                current += step;
2492            }
2493            out.push(row_vec);
2494        }
2495
2496        Ok(collapse_if_scalar(out, _ctx.date_system()))
2497    }
2498}
2499
2500/* ───────────────────────── TRANSPOSE() ───────────────────────── */
2501
2502#[derive(Debug)]
2503pub struct TransposeFn;
2504impl Function for TransposeFn {
2505    func_caps!(PURE);
2506    fn name(&self) -> &'static str {
2507        "TRANSPOSE"
2508    }
2509    fn min_args(&self) -> usize {
2510        1
2511    }
2512    fn variadic(&self) -> bool {
2513        false
2514    }
2515    fn arg_schema(&self) -> &'static [ArgSchema] {
2516        use once_cell::sync::Lazy;
2517        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
2518            vec![ArgSchema {
2519                kinds: smallvec::smallvec![ArgKind::Range],
2520                required: true,
2521                by_ref: true,
2522                shape: ShapeKind::Range,
2523                coercion: CoercionPolicy::None,
2524                max: None,
2525                repeating: None,
2526                default: None,
2527            }]
2528        });
2529        &SCHEMA
2530    }
2531    fn eval<'a, 'b, 'c>(
2532        &self,
2533        args: &'c [ArgumentHandle<'a, 'b>],
2534        _ctx: &dyn FunctionContext<'b>,
2535    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
2536        let view = match args[0].range_view() {
2537            Ok(v) => v,
2538            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
2539        };
2540        let (rows, cols) = view.dims();
2541        if rows == 0 || cols == 0 {
2542            return Ok(crate::traits::CalcValue::Range(
2543                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
2544            ));
2545        }
2546
2547        let mut out: Vec<Vec<LiteralValue>> = vec![Vec::with_capacity(rows); cols];
2548        for (c, col) in out.iter_mut().enumerate().take(cols) {
2549            for r in 0..rows {
2550                col.push(view.get_cell(r, c));
2551            }
2552        }
2553        Ok(collapse_if_scalar(out, _ctx.date_system()))
2554    }
2555}
2556
2557/* ───────────────────────── TAKE() ───────────────────────── */
2558
2559#[derive(Debug)]
2560pub struct TakeFn;
2561impl Function for TakeFn {
2562    func_caps!(PURE);
2563    fn name(&self) -> &'static str {
2564        "TAKE"
2565    }
2566    fn min_args(&self) -> usize {
2567        2
2568    }
2569    fn variadic(&self) -> bool {
2570        true
2571    }
2572    fn arg_schema(&self) -> &'static [ArgSchema] {
2573        use once_cell::sync::Lazy;
2574        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
2575            vec![
2576                ArgSchema {
2577                    kinds: smallvec::smallvec![ArgKind::Range],
2578                    required: true,
2579                    by_ref: true,
2580                    shape: ShapeKind::Range,
2581                    coercion: CoercionPolicy::None,
2582                    max: None,
2583                    repeating: None,
2584                    default: None,
2585                },
2586                ArgSchema {
2587                    kinds: smallvec::smallvec![ArgKind::Number],
2588                    required: true,
2589                    by_ref: false,
2590                    shape: ShapeKind::Scalar,
2591                    coercion: CoercionPolicy::NumberLenientText,
2592                    max: None,
2593                    repeating: None,
2594                    default: None,
2595                },
2596                ArgSchema {
2597                    kinds: smallvec::smallvec![ArgKind::Number],
2598                    required: false,
2599                    by_ref: false,
2600                    shape: ShapeKind::Scalar,
2601                    coercion: CoercionPolicy::NumberLenientText,
2602                    max: None,
2603                    repeating: None,
2604                    default: None,
2605                },
2606            ]
2607        });
2608        &SCHEMA
2609    }
2610    fn eval<'a, 'b, 'c>(
2611        &self,
2612        args: &'c [ArgumentHandle<'a, 'b>],
2613        _ctx: &dyn FunctionContext<'b>,
2614    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
2615        let view = match args[0].range_view() {
2616            Ok(v) => v,
2617            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
2618        };
2619        let (rows, cols) = view.dims();
2620        if rows == 0 || cols == 0 {
2621            return Ok(crate::traits::CalcValue::Range(
2622                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
2623            ));
2624        }
2625
2626        let height = rows as i64;
2627        let width = cols as i64;
2628
2629        let num = |a: &ArgumentHandle| -> Result<i64, ExcelError> {
2630            Ok(match a.value()?.into_literal() {
2631                LiteralValue::Int(i) => i,
2632                LiteralValue::Number(n) => n as i64,
2633                _ => 0,
2634            })
2635        };
2636        let take_rows = num(&args[1])?;
2637        let take_cols = if args.len() >= 3 {
2638            Some(num(&args[2])?)
2639        } else {
2640            None
2641        };
2642
2643        if take_rows.abs() > height {
2644            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
2645                ExcelError::new(ExcelErrorKind::Value),
2646            )));
2647        }
2648
2649        let (row_start, row_end) = if take_rows >= 0 {
2650            (0usize, take_rows as usize)
2651        } else {
2652            ((height + take_rows) as usize, height as usize)
2653        };
2654
2655        let (col_start, col_end) = if let Some(tc) = take_cols {
2656            if tc.abs() > width {
2657                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
2658                    ExcelError::new(ExcelErrorKind::Value),
2659                )));
2660            }
2661            if tc >= 0 {
2662                (0usize, tc as usize)
2663            } else {
2664                ((width + tc) as usize, width as usize)
2665            }
2666        } else {
2667            (0usize, width as usize)
2668        };
2669
2670        if row_start >= row_end || col_start >= col_end {
2671            return Ok(crate::traits::CalcValue::Range(
2672                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
2673            ));
2674        }
2675
2676        let mut out: Vec<Vec<LiteralValue>> = Vec::with_capacity(row_end - row_start);
2677        for r in row_start..row_end {
2678            let mut row_out: Vec<LiteralValue> = Vec::with_capacity(col_end - col_start);
2679            for c in col_start..col_end {
2680                row_out.push(view.get_cell(r, c));
2681            }
2682            out.push(row_out);
2683        }
2684
2685        Ok(collapse_if_scalar(out, _ctx.date_system()))
2686    }
2687}
2688
2689/* ───────────────────────── DROP() ───────────────────────── */
2690
2691#[derive(Debug)]
2692pub struct DropFn;
2693impl Function for DropFn {
2694    func_caps!(PURE);
2695    fn name(&self) -> &'static str {
2696        "DROP"
2697    }
2698    fn min_args(&self) -> usize {
2699        2
2700    }
2701    fn variadic(&self) -> bool {
2702        true
2703    }
2704    fn arg_schema(&self) -> &'static [ArgSchema] {
2705        use once_cell::sync::Lazy;
2706        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
2707            vec![
2708                ArgSchema {
2709                    kinds: smallvec::smallvec![ArgKind::Range],
2710                    required: true,
2711                    by_ref: true,
2712                    shape: ShapeKind::Range,
2713                    coercion: CoercionPolicy::None,
2714                    max: None,
2715                    repeating: None,
2716                    default: None,
2717                },
2718                ArgSchema {
2719                    kinds: smallvec::smallvec![ArgKind::Number],
2720                    required: true,
2721                    by_ref: false,
2722                    shape: ShapeKind::Scalar,
2723                    coercion: CoercionPolicy::NumberLenientText,
2724                    max: None,
2725                    repeating: None,
2726                    default: None,
2727                },
2728                ArgSchema {
2729                    kinds: smallvec::smallvec![ArgKind::Number],
2730                    required: false,
2731                    by_ref: false,
2732                    shape: ShapeKind::Scalar,
2733                    coercion: CoercionPolicy::NumberLenientText,
2734                    max: None,
2735                    repeating: None,
2736                    default: None,
2737                },
2738            ]
2739        });
2740        &SCHEMA
2741    }
2742    fn eval<'a, 'b, 'c>(
2743        &self,
2744        args: &'c [ArgumentHandle<'a, 'b>],
2745        _ctx: &dyn FunctionContext<'b>,
2746    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
2747        let view = match args[0].range_view() {
2748            Ok(v) => v,
2749            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
2750        };
2751        let (rows, cols) = view.dims();
2752        if rows == 0 || cols == 0 {
2753            return Ok(crate::traits::CalcValue::Range(
2754                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
2755            ));
2756        }
2757
2758        let height = rows as i64;
2759        let width = cols as i64;
2760
2761        let num = |a: &ArgumentHandle| -> Result<i64, ExcelError> {
2762            Ok(match a.value()?.into_literal() {
2763                LiteralValue::Int(i) => i,
2764                LiteralValue::Number(n) => n as i64,
2765                _ => 0,
2766            })
2767        };
2768        let drop_rows = num(&args[1])?;
2769        let drop_cols = if args.len() >= 3 {
2770            Some(num(&args[2])?)
2771        } else {
2772            None
2773        };
2774
2775        let (row_start, row_end) = if drop_rows >= 0 {
2776            ((drop_rows as usize).min(height as usize), height as usize)
2777        } else {
2778            (0usize, (height + drop_rows).max(0) as usize)
2779        };
2780
2781        let (col_start, col_end) = if let Some(dc) = drop_cols {
2782            if dc >= 0 {
2783                ((dc as usize).min(width as usize), width as usize)
2784            } else {
2785                (0usize, (width + dc).max(0) as usize)
2786            }
2787        } else {
2788            (0usize, width as usize)
2789        };
2790
2791        if row_start >= row_end || col_start >= col_end {
2792            return Ok(crate::traits::CalcValue::Range(
2793                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
2794            ));
2795        }
2796
2797        let mut out: Vec<Vec<LiteralValue>> = Vec::with_capacity(row_end - row_start);
2798        for r in row_start..row_end {
2799            let mut row_out: Vec<LiteralValue> = Vec::with_capacity(col_end - col_start);
2800            for c in col_start..col_end {
2801                row_out.push(view.get_cell(r, c));
2802            }
2803            out.push(row_out);
2804        }
2805
2806        Ok(collapse_if_scalar(out, _ctx.date_system()))
2807    }
2808}
2809
2810pub fn register_builtins() {
2811    use crate::function_registry::register_function;
2812    use std::sync::Arc;
2813    register_function(Arc::new(XLookupFn));
2814    register_function(Arc::new(FilterFn));
2815    register_function(Arc::new(UniqueFn));
2816    register_function(Arc::new(SequenceFn));
2817    register_function(Arc::new(TransposeFn));
2818    register_function(Arc::new(TakeFn));
2819    register_function(Arc::new(DropFn));
2820    register_function(Arc::new(XMatchFn));
2821    register_function(Arc::new(SortFn));
2822    register_function(Arc::new(SortByFn));
2823    register_function(Arc::new(RandArrayFn));
2824    register_function(Arc::new(GroupByFn));
2825    register_function(Arc::new(PivotByFn));
2826}
2827
2828/* ───────────────────────── tests ───────────────────────── */
2829
2830#[cfg(test)]
2831mod tests {
2832    use super::*;
2833    use crate::test_workbook::TestWorkbook;
2834    use crate::traits::ArgumentHandle;
2835    use formualizer_parse::parser::{ASTNode, ASTNodeType, ReferenceType};
2836    use std::sync::Arc;
2837
2838    #[test]
2839    fn test_all_dynamic_functions_registered() {
2840        // Ensure builtins are registered
2841        crate::builtins::load_builtins();
2842
2843        let functions = [
2844            "XLOOKUP",
2845            "FILTER",
2846            "UNIQUE",
2847            "SEQUENCE",
2848            "TRANSPOSE",
2849            "TAKE",
2850            "DROP",
2851            "XMATCH",
2852            "SORT",
2853            "SORTBY",
2854            "RANDARRAY",
2855            "GROUPBY",
2856            "PIVOTBY",
2857        ];
2858
2859        for name in &functions {
2860            let result = crate::function_registry::get("", name);
2861            assert!(result.is_some(), "Function {} should be registered", name);
2862        }
2863    }
2864
2865    fn lit(v: LiteralValue) -> ASTNode {
2866        ASTNode::new(ASTNodeType::Literal(v), None)
2867    }
2868
2869    fn range(r: &str, sr: u32, sc: u32, er: u32, ec: u32) -> ASTNode {
2870        ASTNode::new(
2871            ASTNodeType::Reference {
2872                original: r.into(),
2873                reference: ReferenceType::range(None, Some(sr), Some(sc), Some(er), Some(ec)),
2874            },
2875            None,
2876        )
2877    }
2878
2879    #[test]
2880    fn xlookup_basic_exact_and_if_not_found() {
2881        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
2882        let wb = wb
2883            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("a".into()))
2884            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("b".into()))
2885            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(10))
2886            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(20));
2887        let ctx = wb.interpreter();
2888        let lookup_range = range("A1:A2", 1, 1, 2, 1);
2889        let return_range = range("B1:B2", 1, 2, 2, 2);
2890        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
2891        let key_b = lit(LiteralValue::Text("b".into()));
2892        let args = vec![
2893            ArgumentHandle::new(&key_b, &ctx),
2894            ArgumentHandle::new(&lookup_range, &ctx),
2895            ArgumentHandle::new(&return_range, &ctx),
2896        ];
2897        let v = f
2898            .dispatch(&args, &ctx.function_context(None))
2899            .unwrap()
2900            .into_literal();
2901        assert_eq!(v, LiteralValue::Number(20.0));
2902        let key_missing = lit(LiteralValue::Text("z".into()));
2903        let if_nf = lit(LiteralValue::Text("NF".into()));
2904        let args_nf = vec![
2905            ArgumentHandle::new(&key_missing, &ctx),
2906            ArgumentHandle::new(&lookup_range, &ctx),
2907            ArgumentHandle::new(&return_range, &ctx),
2908            ArgumentHandle::new(&if_nf, &ctx),
2909        ];
2910        let v_nf = f
2911            .dispatch(&args_nf, &ctx.function_context(None))
2912            .unwrap()
2913            .into_literal();
2914        assert_eq!(v_nf, LiteralValue::Text("NF".into()));
2915    }
2916
2917    #[test]
2918    fn xlookup_match_modes_next_smaller_larger() {
2919        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
2920        let wb = wb
2921            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
2922            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
2923            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
2924            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(1))
2925            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(2))
2926            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(3));
2927        let ctx = wb.interpreter();
2928        let lookup_range = range("A1:A3", 1, 1, 3, 1);
2929        let return_range = range("B1:B3", 1, 2, 3, 2);
2930        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
2931        let needle_25 = lit(LiteralValue::Int(25));
2932        let mm_next_smaller = lit(LiteralValue::Int(-1));
2933        let nf_text = lit(LiteralValue::Text("NF".into()));
2934        let args_smaller = vec![
2935            ArgumentHandle::new(&needle_25, &ctx),
2936            ArgumentHandle::new(&lookup_range, &ctx),
2937            ArgumentHandle::new(&return_range, &ctx),
2938            ArgumentHandle::new(&nf_text, &ctx),
2939            ArgumentHandle::new(&mm_next_smaller, &ctx),
2940        ];
2941        let v_smaller = f
2942            .dispatch(&args_smaller, &ctx.function_context(None))
2943            .unwrap()
2944            .into_literal();
2945        assert_eq!(v_smaller, LiteralValue::Number(2.0));
2946        let mm_next_larger = lit(LiteralValue::Int(1));
2947        let nf_text2 = lit(LiteralValue::Text("NF".into()));
2948        let args_larger = vec![
2949            ArgumentHandle::new(&needle_25, &ctx),
2950            ArgumentHandle::new(&lookup_range, &ctx),
2951            ArgumentHandle::new(&return_range, &ctx),
2952            ArgumentHandle::new(&nf_text2, &ctx),
2953            ArgumentHandle::new(&mm_next_larger, &ctx),
2954        ];
2955        let v_larger = f
2956            .dispatch(&args_larger, &ctx.function_context(None))
2957            .unwrap()
2958            .into_literal();
2959        assert_eq!(v_larger, LiteralValue::Number(3.0));
2960    }
2961
2962    #[test]
2963    fn xlookup_wildcard_and_not_found_default_na() {
2964        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
2965        let wb = wb
2966            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Alpha".into()))
2967            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("Beta".into()))
2968            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("Gamma".into()))
2969            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(100))
2970            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(200))
2971            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(300));
2972        let ctx = wb.interpreter();
2973        let lookup_range = range("A1:A3", 1, 1, 3, 1);
2974        let return_range = range("B1:B3", 1, 2, 3, 2);
2975        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
2976        // Wildcard should match Beta (*et*) with match_mode 2
2977        let pattern = lit(LiteralValue::Text("*et*".into()));
2978        let match_mode_wild = lit(LiteralValue::Int(2));
2979        let nf_binding = lit(LiteralValue::Text("NF".into()));
2980        let args_wild = vec![
2981            ArgumentHandle::new(&pattern, &ctx),
2982            ArgumentHandle::new(&lookup_range, &ctx),
2983            ArgumentHandle::new(&return_range, &ctx),
2984            ArgumentHandle::new(&nf_binding, &ctx),
2985            ArgumentHandle::new(&match_mode_wild, &ctx),
2986        ];
2987        let v_wild = f
2988            .dispatch(&args_wild, &ctx.function_context(None))
2989            .unwrap()
2990            .into_literal();
2991        assert_eq!(v_wild, LiteralValue::Number(200.0));
2992        // Escaped wildcard literal ~* should not match Beta
2993        let pattern_lit_star = lit(LiteralValue::Text("~*eta".into()));
2994        let args_lit = vec![
2995            ArgumentHandle::new(&pattern_lit_star, &ctx),
2996            ArgumentHandle::new(&lookup_range, &ctx),
2997            ArgumentHandle::new(&return_range, &ctx),
2998            ArgumentHandle::new(&nf_binding, &ctx),
2999            ArgumentHandle::new(&match_mode_wild, &ctx),
3000        ];
3001        let v_lit = f
3002            .dispatch(&args_lit, &ctx.function_context(None))
3003            .unwrap()
3004            .into_literal();
3005        match v_lit {
3006            LiteralValue::Text(s) => assert_eq!(s, "NF"),
3007            other => panic!("expected NF text got {other:?}"),
3008        }
3009        // Not found without if_not_found -> #N/A
3010        let missing = lit(LiteralValue::Text("Zeta".into()));
3011        let args_nf = vec![
3012            ArgumentHandle::new(&missing, &ctx),
3013            ArgumentHandle::new(&lookup_range, &ctx),
3014            ArgumentHandle::new(&return_range, &ctx),
3015        ];
3016        let v_nf = f
3017            .dispatch(&args_nf, &ctx.function_context(None))
3018            .unwrap()
3019            .into_literal();
3020        match v_nf {
3021            LiteralValue::Error(e) => assert_eq!(e.kind, ExcelErrorKind::Na),
3022            other => panic!("expected #N/A got {other:?}"),
3023        }
3024    }
3025
3026    #[test]
3027    fn xlookup_reverse_search_mode_picks_last() {
3028        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
3029        let wb = wb
3030            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3031            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2))
3032            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(1))
3033            .with_cell_a1("Sheet1", "B1", LiteralValue::Text("First".into()))
3034            .with_cell_a1("Sheet1", "B2", LiteralValue::Text("Mid".into()))
3035            .with_cell_a1("Sheet1", "B3", LiteralValue::Text("Last".into()));
3036        let ctx = wb.interpreter();
3037        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3038        let return_range = range("B1:B3", 1, 2, 3, 2);
3039        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
3040        let needle_one = lit(LiteralValue::Int(1));
3041        let search_rev = lit(LiteralValue::Int(-1));
3042        let nf_binding2 = lit(LiteralValue::Text("NF".into()));
3043        let match_mode_zero = lit(LiteralValue::Int(0));
3044        let args_rev = vec![
3045            ArgumentHandle::new(&needle_one, &ctx),
3046            ArgumentHandle::new(&lookup_range, &ctx),
3047            ArgumentHandle::new(&return_range, &ctx),
3048            ArgumentHandle::new(&nf_binding2, &ctx),
3049            /* match_mode default */ ArgumentHandle::new(&match_mode_zero, &ctx),
3050            ArgumentHandle::new(&search_rev, &ctx),
3051        ];
3052        let v_rev = f
3053            .dispatch(&args_rev, &ctx.function_context(None))
3054            .unwrap()
3055            .into_literal();
3056        assert_eq!(v_rev, LiteralValue::Text("Last".into()));
3057    }
3058
3059    #[test]
3060    fn xlookup_horizontal_returns_column_vector_for_matrix_return() {
3061        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
3062        let wb = wb
3063            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
3064            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(20))
3065            .with_cell_a1("Sheet1", "C1", LiteralValue::Int(30))
3066            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(1))
3067            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(2))
3068            .with_cell_a1("Sheet1", "C2", LiteralValue::Int(3))
3069            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(4))
3070            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(5))
3071            .with_cell_a1("Sheet1", "C3", LiteralValue::Int(6));
3072        let ctx = wb.interpreter();
3073        let lookup_range = range("A1:C1", 1, 1, 1, 3);
3074        let return_range = range("A2:C3", 2, 1, 3, 3);
3075        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
3076        let needle = lit(LiteralValue::Int(20));
3077        let args = vec![
3078            ArgumentHandle::new(&needle, &ctx),
3079            ArgumentHandle::new(&lookup_range, &ctx),
3080            ArgumentHandle::new(&return_range, &ctx),
3081        ];
3082        let v = f
3083            .dispatch(&args, &ctx.function_context(None))
3084            .unwrap()
3085            .into_literal();
3086        match v {
3087            LiteralValue::Array(a) => {
3088                assert_eq!(
3089                    a,
3090                    vec![
3091                        vec![LiteralValue::Number(2.0)],
3092                        vec![LiteralValue::Number(5.0)]
3093                    ]
3094                );
3095            }
3096            other => panic!("expected array got {other:?}"),
3097        }
3098    }
3099
3100    #[test]
3101    fn xlookup_vertical_returns_row_vector_for_matrix_return() {
3102        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
3103        let wb = wb
3104            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
3105            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
3106            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
3107            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(101))
3108            .with_cell_a1("Sheet1", "C1", LiteralValue::Int(102))
3109            .with_cell_a1("Sheet1", "D1", LiteralValue::Int(103))
3110            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(201))
3111            .with_cell_a1("Sheet1", "C2", LiteralValue::Int(202))
3112            .with_cell_a1("Sheet1", "D2", LiteralValue::Int(203))
3113            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(301))
3114            .with_cell_a1("Sheet1", "C3", LiteralValue::Int(302))
3115            .with_cell_a1("Sheet1", "D3", LiteralValue::Int(303));
3116        let ctx = wb.interpreter();
3117        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3118        let return_range = range("B1:D3", 1, 2, 3, 4);
3119        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
3120        let needle = lit(LiteralValue::Int(20));
3121        let args = vec![
3122            ArgumentHandle::new(&needle, &ctx),
3123            ArgumentHandle::new(&lookup_range, &ctx),
3124            ArgumentHandle::new(&return_range, &ctx),
3125        ];
3126        let v = f
3127            .dispatch(&args, &ctx.function_context(None))
3128            .unwrap()
3129            .into_literal();
3130        match v {
3131            LiteralValue::Array(a) => {
3132                assert_eq!(
3133                    a,
3134                    vec![vec![
3135                        LiteralValue::Number(201.0),
3136                        LiteralValue::Number(202.0),
3137                        LiteralValue::Number(203.0)
3138                    ]]
3139                );
3140            }
3141            other => panic!("expected array got {other:?}"),
3142        }
3143    }
3144
3145    #[test]
3146    fn filter_basic_and_if_empty() {
3147        let wb = TestWorkbook::new().with_function(Arc::new(FilterFn));
3148        let wb = wb
3149            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3150            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2))
3151            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(10))
3152            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(20))
3153            .with_cell_a1("Sheet1", "C1", LiteralValue::Boolean(true))
3154            .with_cell_a1("Sheet1", "C2", LiteralValue::Boolean(false));
3155        let ctx = wb.interpreter();
3156        let array_range = range("A1:B2", 1, 1, 2, 2);
3157        let include_range = range("C1:C2", 1, 3, 2, 3);
3158        let f = ctx.context.get_function("", "FILTER").unwrap();
3159        let args = vec![
3160            ArgumentHandle::new(&array_range, &ctx),
3161            ArgumentHandle::new(&include_range, &ctx),
3162        ];
3163        let v = f
3164            .dispatch(&args, &ctx.function_context(None))
3165            .unwrap()
3166            .into_literal();
3167        match v {
3168            LiteralValue::Array(a) => {
3169                assert_eq!(a.len(), 1);
3170                assert_eq!(
3171                    a[0],
3172                    vec![LiteralValue::Number(1.0), LiteralValue::Number(10.0)]
3173                );
3174            }
3175            other => panic!("expected array got {other:?}"),
3176        }
3177        let wb2 = wb
3178            .with_cell_a1("Sheet1", "C1", LiteralValue::Boolean(false))
3179            .with_cell_a1("Sheet1", "C2", LiteralValue::Boolean(false));
3180        let ctx2 = wb2.interpreter();
3181        let f2 = ctx2.context.get_function("", "FILTER").unwrap();
3182        let empty_text = lit(LiteralValue::Text("EMPTY".into()));
3183        let args_empty = vec![
3184            ArgumentHandle::new(&array_range, &ctx2),
3185            ArgumentHandle::new(&include_range, &ctx2),
3186            ArgumentHandle::new(&empty_text, &ctx2),
3187        ];
3188        let v_empty = f2
3189            .dispatch(&args_empty, &ctx2.function_context(None))
3190            .unwrap()
3191            .into_literal();
3192        assert_eq!(v_empty, LiteralValue::Text("EMPTY".into()));
3193    }
3194
3195    #[test]
3196    fn unique_basic_and_exactly_once() {
3197        let wb = TestWorkbook::new().with_function(Arc::new(UniqueFn));
3198        let wb = wb
3199            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3200            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(1))
3201            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(2))
3202            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(3));
3203        let ctx = wb.interpreter();
3204        let range = range("A1:A4", 1, 1, 4, 1);
3205        let f = ctx.context.get_function("", "UNIQUE").unwrap();
3206        let args = vec![ArgumentHandle::new(&range, &ctx)];
3207        let v = f
3208            .dispatch(&args, &ctx.function_context(None))
3209            .unwrap()
3210            .into_literal();
3211        match v {
3212            LiteralValue::Array(a) => {
3213                assert_eq!(a.len(), 3);
3214                assert_eq!(a[0][0], LiteralValue::Number(1.0));
3215            }
3216            _ => panic!("expected array"),
3217        }
3218    }
3219
3220    #[test]
3221    fn sequence_basic_rows_cols_step() {
3222        let wb = TestWorkbook::new().with_function(Arc::new(SequenceFn));
3223        let ctx = wb.interpreter();
3224        let f = ctx.context.get_function("", "SEQUENCE").unwrap();
3225        let rows = lit(LiteralValue::Int(2));
3226        let cols = lit(LiteralValue::Int(3));
3227        let start = lit(LiteralValue::Int(5));
3228        let step = lit(LiteralValue::Int(2));
3229        let args = vec![
3230            ArgumentHandle::new(&rows, &ctx),
3231            ArgumentHandle::new(&cols, &ctx),
3232            ArgumentHandle::new(&start, &ctx),
3233            ArgumentHandle::new(&step, &ctx),
3234        ];
3235        let v = f
3236            .dispatch(&args, &ctx.function_context(None))
3237            .unwrap()
3238            .into_literal();
3239        match v {
3240            LiteralValue::Array(a) => {
3241                assert_eq!(a.len(), 2);
3242                assert_eq!(a[0][0], LiteralValue::Number(5.0));
3243            }
3244            other => panic!("expected array got {other:?}"),
3245        }
3246    }
3247
3248    #[test]
3249    fn transpose_basic() {
3250        let wb = TestWorkbook::new().with_function(Arc::new(TransposeFn));
3251        let wb = wb
3252            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3253            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2))
3254            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(10))
3255            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(20));
3256        let ctx = wb.interpreter();
3257        let arr = range("A1:B2", 1, 1, 2, 2);
3258        let f = ctx.context.get_function("", "TRANSPOSE").unwrap();
3259        let args = vec![ArgumentHandle::new(&arr, &ctx)];
3260        let v = f
3261            .dispatch(&args, &ctx.function_context(None))
3262            .unwrap()
3263            .into_literal();
3264        match v {
3265            LiteralValue::Array(a) => {
3266                assert_eq!(a.len(), 2);
3267                assert_eq!(
3268                    a[0],
3269                    vec![LiteralValue::Number(1.0), LiteralValue::Number(2.0)]
3270                );
3271            }
3272            other => panic!("expected array got {other:?}"),
3273        }
3274    }
3275
3276    #[test]
3277    fn take_basic() {
3278        let wb = TestWorkbook::new().with_function(Arc::new(TakeFn));
3279        let wb = wb
3280            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3281            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2));
3282        let ctx = wb.interpreter();
3283        let arr = range("A1:A2", 1, 1, 2, 1);
3284        let f = ctx.context.get_function("", "TAKE").unwrap();
3285        let one = lit(LiteralValue::Int(1));
3286        let args = vec![
3287            ArgumentHandle::new(&arr, &ctx),
3288            ArgumentHandle::new(&one, &ctx),
3289        ];
3290        let v = f
3291            .dispatch(&args, &ctx.function_context(None))
3292            .unwrap()
3293            .into_literal();
3294        assert_eq!(v, LiteralValue::Number(1.0));
3295    }
3296
3297    #[test]
3298    fn drop_basic() {
3299        let wb = TestWorkbook::new().with_function(Arc::new(DropFn));
3300        let wb = wb
3301            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3302            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2));
3303        let ctx = wb.interpreter();
3304        let arr = range("A1:A2", 1, 1, 2, 1);
3305        let f = ctx.context.get_function("", "DROP").unwrap();
3306        let one = lit(LiteralValue::Int(1));
3307        let args = vec![
3308            ArgumentHandle::new(&arr, &ctx),
3309            ArgumentHandle::new(&one, &ctx),
3310        ];
3311        let v = f
3312            .dispatch(&args, &ctx.function_context(None))
3313            .unwrap()
3314            .into_literal();
3315        assert_eq!(v, LiteralValue::Number(2.0));
3316    }
3317
3318    #[test]
3319    fn xmatch_exact_match_default() {
3320        let wb = TestWorkbook::new().with_function(Arc::new(XMatchFn));
3321        let wb = wb
3322            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("apple".into()))
3323            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("banana".into()))
3324            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("cherry".into()));
3325        let ctx = wb.interpreter();
3326        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3327        let f = ctx.context.get_function("", "XMATCH").unwrap();
3328        let key = lit(LiteralValue::Text("banana".into()));
3329        let args = vec![
3330            ArgumentHandle::new(&key, &ctx),
3331            ArgumentHandle::new(&lookup_range, &ctx),
3332        ];
3333        let v = f
3334            .dispatch(&args, &ctx.function_context(None))
3335            .unwrap()
3336            .into_literal();
3337        assert_eq!(v, LiteralValue::Int(2));
3338    }
3339
3340    #[test]
3341    fn xmatch_exact_or_next_smaller() {
3342        let wb = TestWorkbook::new().with_function(Arc::new(XMatchFn));
3343        let wb = wb
3344            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
3345            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
3346            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30));
3347        let ctx = wb.interpreter();
3348        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3349        let f = ctx.context.get_function("", "XMATCH").unwrap();
3350        let needle = lit(LiteralValue::Int(25));
3351        let match_mode = lit(LiteralValue::Int(-1)); // exact or next smaller
3352        let args = vec![
3353            ArgumentHandle::new(&needle, &ctx),
3354            ArgumentHandle::new(&lookup_range, &ctx),
3355            ArgumentHandle::new(&match_mode, &ctx),
3356        ];
3357        let v = f
3358            .dispatch(&args, &ctx.function_context(None))
3359            .unwrap()
3360            .into_literal();
3361        assert_eq!(v, LiteralValue::Int(2)); // 20 is the largest <= 25
3362    }
3363
3364    #[test]
3365    fn xmatch_exact_or_next_larger() {
3366        let wb = TestWorkbook::new().with_function(Arc::new(XMatchFn));
3367        let wb = wb
3368            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
3369            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
3370            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30));
3371        let ctx = wb.interpreter();
3372        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3373        let f = ctx.context.get_function("", "XMATCH").unwrap();
3374        let needle = lit(LiteralValue::Int(25));
3375        let match_mode = lit(LiteralValue::Int(1)); // exact or next larger
3376        let args = vec![
3377            ArgumentHandle::new(&needle, &ctx),
3378            ArgumentHandle::new(&lookup_range, &ctx),
3379            ArgumentHandle::new(&match_mode, &ctx),
3380        ];
3381        let v = f
3382            .dispatch(&args, &ctx.function_context(None))
3383            .unwrap()
3384            .into_literal();
3385        assert_eq!(v, LiteralValue::Int(3)); // 30 is the smallest >= 25
3386    }
3387
3388    #[test]
3389    fn xmatch_wildcard() {
3390        let wb = TestWorkbook::new().with_function(Arc::new(XMatchFn));
3391        let wb = wb
3392            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("alpha".into()))
3393            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("beta".into()))
3394            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("gamma".into()));
3395        let ctx = wb.interpreter();
3396        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3397        let f = ctx.context.get_function("", "XMATCH").unwrap();
3398        let pattern = lit(LiteralValue::Text("*eta".into()));
3399        let match_mode = lit(LiteralValue::Int(2)); // wildcard
3400        let args = vec![
3401            ArgumentHandle::new(&pattern, &ctx),
3402            ArgumentHandle::new(&lookup_range, &ctx),
3403            ArgumentHandle::new(&match_mode, &ctx),
3404        ];
3405        let v = f
3406            .dispatch(&args, &ctx.function_context(None))
3407            .unwrap()
3408            .into_literal();
3409        assert_eq!(v, LiteralValue::Int(2)); // "beta" matches "*eta"
3410    }
3411
3412    #[test]
3413    fn xmatch_reverse_search() {
3414        let wb = TestWorkbook::new().with_function(Arc::new(XMatchFn));
3415        let wb = wb
3416            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3417            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2))
3418            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(1)); // duplicate
3419        let ctx = wb.interpreter();
3420        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3421        let f = ctx.context.get_function("", "XMATCH").unwrap();
3422        let needle = lit(LiteralValue::Int(1));
3423        let match_mode = lit(LiteralValue::Int(0));
3424        let search_mode = lit(LiteralValue::Int(-1)); // last to first
3425        let args = vec![
3426            ArgumentHandle::new(&needle, &ctx),
3427            ArgumentHandle::new(&lookup_range, &ctx),
3428            ArgumentHandle::new(&match_mode, &ctx),
3429            ArgumentHandle::new(&search_mode, &ctx),
3430        ];
3431        let v = f
3432            .dispatch(&args, &ctx.function_context(None))
3433            .unwrap()
3434            .into_literal();
3435        assert_eq!(v, LiteralValue::Int(3)); // last occurrence of 1
3436    }
3437
3438    #[test]
3439    fn xmatch_not_found() {
3440        let wb = TestWorkbook::new().with_function(Arc::new(XMatchFn));
3441        let wb = wb
3442            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3443            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2))
3444            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(3));
3445        let ctx = wb.interpreter();
3446        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3447        let f = ctx.context.get_function("", "XMATCH").unwrap();
3448        let needle = lit(LiteralValue::Int(5));
3449        let args = vec![
3450            ArgumentHandle::new(&needle, &ctx),
3451            ArgumentHandle::new(&lookup_range, &ctx),
3452        ];
3453        let v = f
3454            .dispatch(&args, &ctx.function_context(None))
3455            .unwrap()
3456            .into_literal();
3457        match v {
3458            LiteralValue::Error(e) => assert_eq!(e.kind, ExcelErrorKind::Na),
3459            other => panic!("expected #N/A got {other:?}"),
3460        }
3461    }
3462
3463    #[test]
3464    fn sort_basic_ascending() {
3465        let wb = TestWorkbook::new().with_function(Arc::new(SortFn));
3466        let wb = wb
3467            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(30))
3468            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(10))
3469            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(20));
3470        let ctx = wb.interpreter();
3471        let arr = range("A1:A3", 1, 1, 3, 1);
3472        let f = ctx.context.get_function("", "SORT").unwrap();
3473        let args = vec![ArgumentHandle::new(&arr, &ctx)];
3474        let v = f
3475            .dispatch(&args, &ctx.function_context(None))
3476            .unwrap()
3477            .into_literal();
3478        match v {
3479            LiteralValue::Array(a) => {
3480                assert_eq!(a.len(), 3);
3481                assert_eq!(a[0][0], LiteralValue::Number(10.0));
3482                assert_eq!(a[1][0], LiteralValue::Number(20.0));
3483                assert_eq!(a[2][0], LiteralValue::Number(30.0));
3484            }
3485            other => panic!("expected array got {other:?}"),
3486        }
3487    }
3488
3489    #[test]
3490    fn sort_descending() {
3491        let wb = TestWorkbook::new().with_function(Arc::new(SortFn));
3492        let wb = wb
3493            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(30))
3494            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(10))
3495            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(20));
3496        let ctx = wb.interpreter();
3497        let arr = range("A1:A3", 1, 1, 3, 1);
3498        let f = ctx.context.get_function("", "SORT").unwrap();
3499        let sort_index = lit(LiteralValue::Int(1));
3500        let sort_order = lit(LiteralValue::Int(-1)); // descending
3501        let args = vec![
3502            ArgumentHandle::new(&arr, &ctx),
3503            ArgumentHandle::new(&sort_index, &ctx),
3504            ArgumentHandle::new(&sort_order, &ctx),
3505        ];
3506        let v = f
3507            .dispatch(&args, &ctx.function_context(None))
3508            .unwrap()
3509            .into_literal();
3510        match v {
3511            LiteralValue::Array(a) => {
3512                assert_eq!(a.len(), 3);
3513                assert_eq!(a[0][0], LiteralValue::Number(30.0));
3514                assert_eq!(a[1][0], LiteralValue::Number(20.0));
3515                assert_eq!(a[2][0], LiteralValue::Number(10.0));
3516            }
3517            other => panic!("expected array got {other:?}"),
3518        }
3519    }
3520
3521    #[test]
3522    fn sort_by_column() {
3523        let wb = TestWorkbook::new().with_function(Arc::new(SortFn));
3524        let wb = wb
3525            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Charlie".into()))
3526            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(30))
3527            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("Alice".into()))
3528            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(10))
3529            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("Bob".into()))
3530            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(20));
3531        let ctx = wb.interpreter();
3532        let arr = range("A1:B3", 1, 1, 3, 2);
3533        let f = ctx.context.get_function("", "SORT").unwrap();
3534        let sort_index = lit(LiteralValue::Int(2)); // sort by column B
3535        let args = vec![
3536            ArgumentHandle::new(&arr, &ctx),
3537            ArgumentHandle::new(&sort_index, &ctx),
3538        ];
3539        let v = f
3540            .dispatch(&args, &ctx.function_context(None))
3541            .unwrap()
3542            .into_literal();
3543        match v {
3544            LiteralValue::Array(a) => {
3545                assert_eq!(a.len(), 3);
3546                // Should be sorted by column B: Alice(10), Bob(20), Charlie(30)
3547                assert_eq!(a[0][0], LiteralValue::Text("Alice".into()));
3548                assert_eq!(a[1][0], LiteralValue::Text("Bob".into()));
3549                assert_eq!(a[2][0], LiteralValue::Text("Charlie".into()));
3550            }
3551            other => panic!("expected array got {other:?}"),
3552        }
3553    }
3554
3555    #[test]
3556    fn sortby_basic() {
3557        let wb = TestWorkbook::new().with_function(Arc::new(SortByFn));
3558        let wb = wb
3559            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Charlie".into()))
3560            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("Alice".into()))
3561            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("Bob".into()))
3562            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(3))
3563            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(1))
3564            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(2));
3565        let ctx = wb.interpreter();
3566        let arr = range("A1:A3", 1, 1, 3, 1);
3567        let by_arr = range("B1:B3", 1, 2, 3, 2);
3568        let f = ctx.context.get_function("", "SORTBY").unwrap();
3569        let args = vec![
3570            ArgumentHandle::new(&arr, &ctx),
3571            ArgumentHandle::new(&by_arr, &ctx),
3572        ];
3573        let v = f
3574            .dispatch(&args, &ctx.function_context(None))
3575            .unwrap()
3576            .into_literal();
3577        match v {
3578            LiteralValue::Array(a) => {
3579                assert_eq!(a.len(), 3);
3580                // Should be sorted by B values: Alice(1), Bob(2), Charlie(3)
3581                assert_eq!(a[0][0], LiteralValue::Text("Alice".into()));
3582                assert_eq!(a[1][0], LiteralValue::Text("Bob".into()));
3583                assert_eq!(a[2][0], LiteralValue::Text("Charlie".into()));
3584            }
3585            other => panic!("expected array got {other:?}"),
3586        }
3587    }
3588
3589    #[test]
3590    fn sortby_descending() {
3591        let wb = TestWorkbook::new().with_function(Arc::new(SortByFn));
3592        let wb = wb
3593            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Charlie".into()))
3594            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("Alice".into()))
3595            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("Bob".into()))
3596            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(3))
3597            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(1))
3598            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(2));
3599        let ctx = wb.interpreter();
3600        let arr = range("A1:A3", 1, 1, 3, 1);
3601        let by_arr = range("B1:B3", 1, 2, 3, 2);
3602        let sort_order = lit(LiteralValue::Int(-1)); // descending
3603        let f = ctx.context.get_function("", "SORTBY").unwrap();
3604        let args = vec![
3605            ArgumentHandle::new(&arr, &ctx),
3606            ArgumentHandle::new(&by_arr, &ctx),
3607            ArgumentHandle::new(&sort_order, &ctx),
3608        ];
3609        let v = f
3610            .dispatch(&args, &ctx.function_context(None))
3611            .unwrap()
3612            .into_literal();
3613        match v {
3614            LiteralValue::Array(a) => {
3615                assert_eq!(a.len(), 3);
3616                // Should be sorted by B values descending: Charlie(3), Bob(2), Alice(1)
3617                assert_eq!(a[0][0], LiteralValue::Text("Charlie".into()));
3618                assert_eq!(a[1][0], LiteralValue::Text("Bob".into()));
3619                assert_eq!(a[2][0], LiteralValue::Text("Alice".into()));
3620            }
3621            other => panic!("expected array got {other:?}"),
3622        }
3623    }
3624
3625    #[test]
3626    fn randarray_basic() {
3627        let wb = TestWorkbook::new().with_function(Arc::new(RandArrayFn));
3628        let ctx = wb.interpreter();
3629        let f = ctx.context.get_function("", "RANDARRAY").unwrap();
3630
3631        // Test basic 2x3 array with defaults
3632        let rows = lit(LiteralValue::Int(2));
3633        let cols = lit(LiteralValue::Int(3));
3634        let args = vec![
3635            ArgumentHandle::new(&rows, &ctx),
3636            ArgumentHandle::new(&cols, &ctx),
3637        ];
3638        let v = f
3639            .dispatch(&args, &ctx.function_context(None))
3640            .unwrap()
3641            .into_literal();
3642        match v {
3643            LiteralValue::Array(a) => {
3644                assert_eq!(a.len(), 2);
3645                assert_eq!(a[0].len(), 3);
3646                // Check all values are between 0 and 1
3647                for row in &a {
3648                    for cell in row {
3649                        match cell {
3650                            LiteralValue::Number(n) => {
3651                                assert!(*n >= 0.0 && *n < 1.0, "Value {n} not in [0, 1)");
3652                            }
3653                            other => panic!("expected Number got {other:?}"),
3654                        }
3655                    }
3656                }
3657            }
3658            other => panic!("expected array got {other:?}"),
3659        }
3660    }
3661
3662    #[test]
3663    fn randarray_whole_numbers() {
3664        let wb = TestWorkbook::new().with_function(Arc::new(RandArrayFn));
3665        let ctx = wb.interpreter();
3666        let f = ctx.context.get_function("", "RANDARRAY").unwrap();
3667
3668        // Test 3x2 array with whole numbers between 1 and 10
3669        let rows = lit(LiteralValue::Int(3));
3670        let cols = lit(LiteralValue::Int(2));
3671        let min = lit(LiteralValue::Int(1));
3672        let max = lit(LiteralValue::Int(10));
3673        let whole = lit(LiteralValue::Boolean(true));
3674        let args = vec![
3675            ArgumentHandle::new(&rows, &ctx),
3676            ArgumentHandle::new(&cols, &ctx),
3677            ArgumentHandle::new(&min, &ctx),
3678            ArgumentHandle::new(&max, &ctx),
3679            ArgumentHandle::new(&whole, &ctx),
3680        ];
3681        let v = f
3682            .dispatch(&args, &ctx.function_context(None))
3683            .unwrap()
3684            .into_literal();
3685        match v {
3686            LiteralValue::Array(a) => {
3687                assert_eq!(a.len(), 3);
3688                assert_eq!(a[0].len(), 2);
3689                // Check all values are integers between 1 and 10
3690                for row in &a {
3691                    for cell in row {
3692                        let n = match cell {
3693                            LiteralValue::Int(n) => *n as f64,
3694                            LiteralValue::Number(n) => *n,
3695                            other => panic!("expected Int or Number got {other:?}"),
3696                        };
3697                        assert!(n >= 1.0 && n <= 10.0, "Value {n} not in [1, 10]");
3698                        // Verify it's actually a whole number
3699                        assert!(n.fract() == 0.0, "Value {n} is not a whole number");
3700                    }
3701                }
3702            }
3703            other => panic!("expected array got {other:?}"),
3704        }
3705    }
3706}
formualizer_eval/builtins/lookup/dynamic.rs

formualizer_eval/builtins/lookup/
dynamic.rs
