formualizer_eval/builtins/lookup/
choose.rs

1//! CHOOSE function - selects a value from a list based on an index
2//!
3//! Excel semantics:
4//! - CHOOSE(index_num, value1, [value2], ...)
5//! - index_num must be between 1 and the number of values
6//! - Returns #VALUE! if index is out of range or not numeric
7//! - Can return references, not just values
8
9use crate::args::{ArgSchema, CoercionPolicy, ShapeKind};
10use crate::builtins::utils::collapse_if_scalar;
11use crate::function::Function;
12use crate::traits::{ArgumentHandle, FunctionContext};
13use formualizer_common::{ArgKind, ExcelError, ExcelErrorKind, LiteralValue};
14use formualizer_macros::func_caps;
15
16#[derive(Debug)]
17pub struct ChooseFn;
18#[derive(Debug)]
19pub struct ChooseColsFn;
20#[derive(Debug)]
21pub struct ChooseRowsFn;
22
23/// Returns one value from a list using a 1-based index.
24///
25/// `CHOOSE` selects from `value1, value2, ...` based on `index_num`.
26///
27/// # Remarks
28/// - `index_num` is 1-based and is truncated to an integer.
29/// - Indexes less than 1 or greater than the number of choices return `#VALUE!`.
30/// - Errors in `index_num` are propagated.
31/// - The selected argument is returned as-is, including non-text/non-numeric values.
32///
33/// # Examples
34/// ```yaml,sandbox
35/// title: "Select a label"
36/// formula: '=CHOOSE(2,"Low","Medium","High")'
37/// expected: "Medium"
38/// ```
39///
40/// ```yaml,sandbox
41/// title: "Use numeric choices"
42/// formula: '=CHOOSE(3,10,20,30,40)'
43/// expected: 30
44/// ```
45///
46/// ```yaml,docs
47/// related:
48///   - INDEX
49///   - CHOOSECOLS
50///   - CHOOSEROWS
51/// faq:
52///   - q: "How are decimal index_num values handled?"
53///     a: "The index is truncated toward zero before selection, so CHOOSE(2.9,...) selects the second argument."
54///   - q: "What error do I get for index_num 0 or too large?"
55///     a: "CHOOSE returns #VALUE! when index_num is less than 1 or greater than the number of provided choices."
56/// ```
57/// [formualizer-docgen:schema:start]
58/// Name: CHOOSE
59/// Type: ChooseFn
60/// Min args: 2
61/// Max args: 2
62/// Variadic: false
63/// Signature: CHOOSE(arg1: number@scalar, arg2: any@scalar)
64/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberStrict,max=None,repeating=None,default=false}; arg2{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=Some(1),default=false}
65/// Caps: PURE, LOOKUP
66/// [formualizer-docgen:schema:end]
67impl Function for ChooseFn {
68    fn name(&self) -> &'static str {
69        "CHOOSE"
70    }
71
72    fn min_args(&self) -> usize {
73        2
74    }
75
76    func_caps!(PURE, LOOKUP);
77
78    fn arg_schema(&self) -> &'static [ArgSchema] {
79        use once_cell::sync::Lazy;
80        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
81            vec![
82                // index_num (strict numeric)
83                ArgSchema {
84                    kinds: smallvec::smallvec![ArgKind::Number],
85                    required: true,
86                    by_ref: false,
87                    shape: ShapeKind::Scalar,
88                    coercion: CoercionPolicy::NumberStrict,
89                    max: None,
90                    repeating: None,
91                    default: None,
92                },
93                // value1, value2, ... (variadic, any type)
94                ArgSchema {
95                    kinds: smallvec::smallvec![ArgKind::Any],
96                    required: true,
97                    by_ref: false, // Could be reference but we'll unwrap value or pass through
98                    shape: ShapeKind::Scalar, // Treat each choice as scalar (top-left if range)
99                    coercion: CoercionPolicy::None,
100                    max: None,
101                    repeating: Some(1), // any number of choices after index
102                    default: None,
103                },
104            ]
105        });
106        &SCHEMA
107    }
108
109    fn eval<'a, 'b, 'c>(
110        &self,
111        args: &'c [ArgumentHandle<'a, 'b>],
112        _ctx: &dyn FunctionContext<'b>,
113    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
114        if args.len() < 2 {
115            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
116                ExcelError::new(ExcelErrorKind::Value),
117            )));
118        }
119
120        // Get index
121        let index_val = args[0].value()?.into_literal();
122        if let LiteralValue::Error(e) = index_val {
123            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
124        }
125
126        let index = match index_val {
127            LiteralValue::Number(n) => n as i64,
128            LiteralValue::Int(i) => i,
129            LiteralValue::Text(s) => s.parse::<f64>().map(|n| n as i64).unwrap_or(-1),
130            _ => {
131                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
132                    ExcelError::new(ExcelErrorKind::Value),
133                )));
134            }
135        };
136
137        // Check bounds
138        if index < 1 || index as usize > args.len() - 1 {
139            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
140                ExcelError::new(ExcelErrorKind::Value),
141            )));
142        }
143
144        // Return the selected value (1-based indexing for the choice)
145        let selected_arg = &args[index as usize];
146        selected_arg.value()
147    }
148}
149
150/* ───────────────────────── CHOOSECOLS() / CHOOSEROWS() ───────────────────────── */
151
152fn materialize_rows_2d<'b>(
153    arg: &ArgumentHandle<'_, 'b>,
154    ctx: &dyn FunctionContext<'b>,
155) -> Result<Vec<Vec<formualizer_common::LiteralValue>>, ExcelError> {
156    if let Ok(r) = arg.as_reference_or_eval() {
157        let mut rows: Vec<Vec<LiteralValue>> = Vec::new();
158        let sheet = ctx.current_sheet();
159        let rv = ctx.resolve_range_view(&r, sheet)?;
160        rv.for_each_row(&mut |row| {
161            rows.push(row.to_vec());
162            Ok(())
163        })?;
164        Ok(rows)
165    } else {
166        let v = arg.value()?.into_literal();
167        match v {
168            LiteralValue::Array(a) => Ok(a),
169            other => Ok(vec![vec![other]]),
170        }
171    }
172}
173
174/// Returns selected columns from an array or range.
175///
176/// `CHOOSECOLS` builds a new spilled array containing only the requested columns, in the
177/// order provided.
178///
179/// # Remarks
180/// - Column indexes are 1-based; negative indexes count from the end (`-1` is last column).
181/// - Repeated indexes are allowed and duplicate columns in the output.
182/// - Index `0` or out-of-bounds indexes return `#VALUE!`.
183/// - The result spills as an array unless it collapses to a single cell.
184///
185/// # Examples
186/// ```yaml,sandbox
187/// title: "Select first and third columns"
188/// grid:
189///   A1: "Name"
190///   B1: "Dept"
191///   C1: "Score"
192///   A2: "Ana"
193///   B2: "Ops"
194///   C2: 91
195/// formula: '=CHOOSECOLS(A1:C2,1,3)'
196/// expected: [["Name","Score"],["Ana",91]]
197/// ```
198///
199/// ```yaml,sandbox
200/// title: "Select the last column with a negative index"
201/// grid:
202///   A1: 10
203///   B1: 20
204///   C1: 30
205/// formula: '=CHOOSECOLS(A1:C1,-1)'
206/// expected: 30
207/// ```
208///
209/// ```yaml,docs
210/// related:
211///   - CHOOSEROWS
212///   - TAKE
213///   - DROP
214/// faq:
215///   - q: "How do negative column indexes work?"
216///     a: "Negative indexes count from the right edge of the array, so -1 selects the last column, -2 the second-to-last, and so on."
217///   - q: "What triggers #VALUE! in CHOOSECOLS?"
218///     a: "Index 0 and any index whose absolute position falls outside the source width return #VALUE!."
219/// ```
220/// [formualizer-docgen:schema:start]
221/// Name: CHOOSECOLS
222/// Type: ChooseColsFn
223/// Min args: 2
224/// Max args: variadic
225/// Variadic: true
226/// Signature: CHOOSECOLS(arg1: range|any@range, arg2...: number@scalar)
227/// Arg schema: arg1{kinds=range|any,required=true,shape=range,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg2{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=Some(1),default=false}
228/// Caps: PURE, LOOKUP
229/// [formualizer-docgen:schema:end]
230impl Function for ChooseColsFn {
231    func_caps!(PURE, LOOKUP);
232    fn name(&self) -> &'static str {
233        "CHOOSECOLS"
234    }
235    fn min_args(&self) -> usize {
236        2
237    }
238    fn variadic(&self) -> bool {
239        true
240    }
241    fn arg_schema(&self) -> &'static [ArgSchema] {
242        use once_cell::sync::Lazy;
243        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
244            vec![
245                // array
246                ArgSchema {
247                    kinds: smallvec::smallvec![ArgKind::Range, ArgKind::Any],
248                    required: true,
249                    by_ref: false,
250                    shape: ShapeKind::Range,
251                    coercion: CoercionPolicy::None,
252                    max: None,
253                    repeating: None,
254                    default: None,
255                },
256                // col_num1 and subsequent
257                ArgSchema {
258                    kinds: smallvec::smallvec![ArgKind::Number],
259                    required: true,
260                    by_ref: false,
261                    shape: ShapeKind::Scalar,
262                    coercion: CoercionPolicy::NumberLenientText,
263                    max: None,
264                    repeating: Some(1),
265                    default: None,
266                },
267            ]
268        });
269        &SCHEMA
270    }
271    fn eval<'a, 'b, 'c>(
272        &self,
273        args: &'c [ArgumentHandle<'a, 'b>],
274        _ctx: &dyn FunctionContext<'b>,
275    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
276        if args.len() < 2 {
277            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
278                ExcelError::new(ExcelErrorKind::Value),
279            )));
280        }
281        let view = args[0].range_view()?;
282        let (rows, cols) = view.dims();
283        if rows == 0 || cols == 0 {
284            return Ok(crate::traits::CalcValue::Range(
285                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
286            ));
287        }
288
289        let mut indices: Vec<usize> = Vec::new();
290        for a in &args[1..] {
291            let v = a.value()?.into_literal();
292            if let LiteralValue::Error(e) = v {
293                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
294            }
295            let raw = match v {
296                LiteralValue::Int(i) => i,
297                LiteralValue::Number(n) => n as i64,
298                _ => {
299                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
300                        ExcelError::new(ExcelErrorKind::Value),
301                    )));
302                }
303            };
304            if raw == 0 {
305                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
306                    ExcelError::new(ExcelErrorKind::Value),
307                )));
308            }
309            let adj = if raw > 0 {
310                raw - 1
311            } else {
312                (cols as i64) + raw
313            };
314            if adj < 0 || adj as usize >= cols {
315                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
316                    ExcelError::new(ExcelErrorKind::Value),
317                )));
318            }
319            indices.push(adj as usize);
320        }
321        let mut out: Vec<Vec<LiteralValue>> = Vec::with_capacity(rows);
322        for r in 0..rows {
323            let mut new_row = Vec::with_capacity(indices.len());
324            for &c in &indices {
325                new_row.push(view.get_cell(r, c));
326            }
327            out.push(new_row);
328        }
329
330        Ok(collapse_if_scalar(out, _ctx.date_system()))
331    }
332}
333
334/// Returns selected rows from an array or range.
335///
336/// `CHOOSEROWS` builds a new spilled array containing only the requested rows, in the
337/// order provided.
338///
339/// # Remarks
340/// - Row indexes are 1-based; negative indexes count from the end (`-1` is last row).
341/// - Repeated indexes are allowed and duplicate rows in the output.
342/// - Index `0` or out-of-bounds indexes return `#VALUE!`.
343/// - The result spills as an array unless it collapses to a single cell.
344///
345/// # Examples
346/// ```yaml,sandbox
347/// title: "Pick first and last rows"
348/// grid:
349///   A1: "Jan"
350///   A2: "Feb"
351///   A3: "Mar"
352/// formula: '=CHOOSEROWS(A1:A3,1,-1)'
353/// expected: [["Jan"],["Mar"]]
354/// ```
355///
356/// ```yaml,sandbox
357/// title: "Duplicate a row in output"
358/// grid:
359///   A1: 5
360///   A2: 9
361/// formula: '=CHOOSEROWS(A1:A2,2,2)'
362/// expected: [[9],[9]]
363/// ```
364///
365/// ```yaml,docs
366/// related:
367///   - CHOOSECOLS
368///   - TAKE
369///   - DROP
370/// faq:
371///   - q: "Can I return rows in a custom order?"
372///     a: "Yes. CHOOSEROWS returns rows in the exact order of the supplied indexes, and repeated indexes duplicate rows."
373///   - q: "When does CHOOSEROWS return #VALUE!?"
374///     a: "It returns #VALUE! for index 0 and for row indexes outside the source height after applying negative-index conversion."
375/// ```
376/// [formualizer-docgen:schema:start]
377/// Name: CHOOSEROWS
378/// Type: ChooseRowsFn
379/// Min args: 2
380/// Max args: variadic
381/// Variadic: true
382/// Signature: CHOOSEROWS(arg1: range|any@range, arg2...: number@scalar)
383/// Arg schema: arg1{kinds=range|any,required=true,shape=range,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg2{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=Some(1),default=false}
384/// Caps: PURE, LOOKUP
385/// [formualizer-docgen:schema:end]
386impl Function for ChooseRowsFn {
387    func_caps!(PURE, LOOKUP);
388    fn name(&self) -> &'static str {
389        "CHOOSEROWS"
390    }
391    fn min_args(&self) -> usize {
392        2
393    }
394    fn variadic(&self) -> bool {
395        true
396    }
397    fn arg_schema(&self) -> &'static [ArgSchema] {
398        use once_cell::sync::Lazy;
399        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
400            vec![
401                // array
402                ArgSchema {
403                    kinds: smallvec::smallvec![ArgKind::Range, ArgKind::Any],
404                    required: true,
405                    by_ref: false,
406                    shape: ShapeKind::Range,
407                    coercion: CoercionPolicy::None,
408                    max: None,
409                    repeating: None,
410                    default: None,
411                },
412                // row_num1...
413                ArgSchema {
414                    kinds: smallvec::smallvec![ArgKind::Number],
415                    required: true,
416                    by_ref: false,
417                    shape: ShapeKind::Scalar,
418                    coercion: CoercionPolicy::NumberLenientText,
419                    max: None,
420                    repeating: Some(1),
421                    default: None,
422                },
423            ]
424        });
425        &SCHEMA
426    }
427    fn eval<'a, 'b, 'c>(
428        &self,
429        args: &'c [ArgumentHandle<'a, 'b>],
430        _ctx: &dyn FunctionContext<'b>,
431    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
432        if args.len() < 2 {
433            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
434                ExcelError::new(ExcelErrorKind::Value),
435            )));
436        }
437        let view = args[0].range_view()?;
438        let (rows, cols) = view.dims();
439        if rows == 0 || cols == 0 {
440            return Ok(crate::traits::CalcValue::Range(
441                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
442            ));
443        }
444
445        let mut indices: Vec<usize> = Vec::new();
446        for a in &args[1..] {
447            let v = a.value()?.into_literal();
448            if let LiteralValue::Error(e) = v {
449                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
450            }
451            let raw = match v {
452                LiteralValue::Int(i) => i,
453                LiteralValue::Number(n) => n as i64,
454                _ => {
455                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
456                        ExcelError::new(ExcelErrorKind::Value),
457                    )));
458                }
459            };
460            if raw == 0 {
461                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
462                    ExcelError::new(ExcelErrorKind::Value),
463                )));
464            }
465            let adj = if raw > 0 {
466                raw - 1
467            } else {
468                (rows as i64) + raw
469            };
470            if adj < 0 || adj as usize >= rows {
471                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
472                    ExcelError::new(ExcelErrorKind::Value),
473                )));
474            }
475            indices.push(adj as usize);
476        }
477        let mut out: Vec<Vec<LiteralValue>> = Vec::with_capacity(indices.len());
478        for &r in &indices {
479            let mut row_vals = Vec::with_capacity(cols);
480            for c in 0..cols {
481                row_vals.push(view.get_cell(r, c));
482            }
483            out.push(row_vals);
484        }
485
486        Ok(collapse_if_scalar(out, _ctx.date_system()))
487    }
488}
489
490#[cfg(test)]
491mod tests {
492    use super::*;
493    use crate::test_workbook::TestWorkbook;
494    use formualizer_parse::parser::{ASTNode, ASTNodeType, ReferenceType};
495    use std::sync::Arc;
496
497    fn lit(v: LiteralValue) -> ASTNode {
498        ASTNode::new(ASTNodeType::Literal(v), None)
499    }
500
501    #[test]
502    fn choose_basic() {
503        let wb = TestWorkbook::new().with_function(Arc::new(ChooseFn));
504        let ctx = wb.interpreter();
505        let f = ctx.context.get_function("", "CHOOSE").unwrap();
506
507        // CHOOSE(2, "A", "B", "C") -> "B"
508        let two = lit(LiteralValue::Int(2));
509        let a = lit(LiteralValue::Text("A".into()));
510        let b = lit(LiteralValue::Text("B".into()));
511        let c = lit(LiteralValue::Text("C".into()));
512
513        let args = vec![
514            ArgumentHandle::new(&two, &ctx),
515            ArgumentHandle::new(&a, &ctx),
516            ArgumentHandle::new(&b, &ctx),
517            ArgumentHandle::new(&c, &ctx),
518        ];
519
520        let result = f
521            .dispatch(&args, &ctx.function_context(None))
522            .unwrap()
523            .into_literal();
524        assert_eq!(result, LiteralValue::Text("B".into()));
525    }
526
527    #[test]
528    fn choose_numeric_values() {
529        let wb = TestWorkbook::new().with_function(Arc::new(ChooseFn));
530        let ctx = wb.interpreter();
531        let f = ctx.context.get_function("", "CHOOSE").unwrap();
532
533        // CHOOSE(3, 10, 20, 30, 40) -> 30
534        let three = lit(LiteralValue::Int(3));
535        let ten = lit(LiteralValue::Int(10));
536        let twenty = lit(LiteralValue::Int(20));
537        let thirty = lit(LiteralValue::Int(30));
538        let forty = lit(LiteralValue::Int(40));
539
540        let args = vec![
541            ArgumentHandle::new(&three, &ctx),
542            ArgumentHandle::new(&ten, &ctx),
543            ArgumentHandle::new(&twenty, &ctx),
544            ArgumentHandle::new(&thirty, &ctx),
545            ArgumentHandle::new(&forty, &ctx),
546        ];
547
548        let result = f
549            .dispatch(&args, &ctx.function_context(None))
550            .unwrap()
551            .into_literal();
552        assert_eq!(result, LiteralValue::Int(30));
553    }
554
555    #[test]
556    fn choose_out_of_range() {
557        let wb = TestWorkbook::new().with_function(Arc::new(ChooseFn));
558        let ctx = wb.interpreter();
559        let f = ctx.context.get_function("", "CHOOSE").unwrap();
560
561        // CHOOSE(5, "A", "B", "C") -> #VALUE! (only 3 choices)
562        let five = lit(LiteralValue::Int(5));
563        let a = lit(LiteralValue::Text("A".into()));
564        let b = lit(LiteralValue::Text("B".into()));
565        let c = lit(LiteralValue::Text("C".into()));
566
567        let args = vec![
568            ArgumentHandle::new(&five, &ctx),
569            ArgumentHandle::new(&a, &ctx),
570            ArgumentHandle::new(&b, &ctx),
571            ArgumentHandle::new(&c, &ctx),
572        ];
573
574        let result = f
575            .dispatch(&args, &ctx.function_context(None))
576            .unwrap()
577            .into_literal();
578        assert!(matches!(result, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Value));
579
580        // CHOOSE(0, "A", "B") -> #VALUE! (index must be >= 1)
581        let zero = lit(LiteralValue::Int(0));
582        let args2 = vec![
583            ArgumentHandle::new(&zero, &ctx),
584            ArgumentHandle::new(&a, &ctx),
585            ArgumentHandle::new(&b, &ctx),
586        ];
587
588        let result2 = f
589            .dispatch(&args2, &ctx.function_context(None))
590            .unwrap()
591            .into_literal();
592        assert!(matches!(result2, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Value));
593    }
594
595    #[test]
596    fn choose_decimal_index() {
597        let wb = TestWorkbook::new().with_function(Arc::new(ChooseFn));
598        let ctx = wb.interpreter();
599        let f = ctx.context.get_function("", "CHOOSE").unwrap();
600
601        // CHOOSE(2.7, "A", "B", "C") -> "B" (truncates to 2)
602        let two_seven = lit(LiteralValue::Number(2.7));
603        let a = lit(LiteralValue::Text("A".into()));
604        let b = lit(LiteralValue::Text("B".into()));
605        let c = lit(LiteralValue::Text("C".into()));
606
607        let args = vec![
608            ArgumentHandle::new(&two_seven, &ctx),
609            ArgumentHandle::new(&a, &ctx),
610            ArgumentHandle::new(&b, &ctx),
611            ArgumentHandle::new(&c, &ctx),
612        ];
613
614        let result = f
615            .dispatch(&args, &ctx.function_context(None))
616            .unwrap()
617            .into_literal();
618        assert_eq!(result, LiteralValue::Text("B".into()));
619    }
620
621    #[test]
622    fn choose_text_index_numeric_string() {
623        let wb = TestWorkbook::new().with_function(Arc::new(ChooseFn));
624        let ctx = wb.interpreter();
625        let f = ctx.context.get_function("", "CHOOSE").unwrap();
626        let two_txt = lit(LiteralValue::Text("2".into()));
627        let a = lit(LiteralValue::Text("A".into()));
628        let b = lit(LiteralValue::Text("B".into()));
629        let c = lit(LiteralValue::Text("C".into()));
630        let args = vec![
631            ArgumentHandle::new(&two_txt, &ctx),
632            ArgumentHandle::new(&a, &ctx),
633            ArgumentHandle::new(&b, &ctx),
634            ArgumentHandle::new(&c, &ctx),
635        ];
636        let result = f
637            .dispatch(&args, &ctx.function_context(None))
638            .unwrap()
639            .into_literal();
640        // Current engine uses NumberStrict coercion for index: numeric text not accepted -> #VALUE!
641        assert!(matches!(result, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Value));
642    }
643
644    #[test]
645    fn choose_decimal_less_than_one() {
646        let wb = TestWorkbook::new().with_function(Arc::new(ChooseFn));
647        let ctx = wb.interpreter();
648        let f = ctx.context.get_function("", "CHOOSE").unwrap();
649        let zero_nine = lit(LiteralValue::Number(0.9));
650        let a = lit(LiteralValue::Text("A".into()));
651        let b = lit(LiteralValue::Text("B".into()));
652        let args = vec![
653            ArgumentHandle::new(&zero_nine, &ctx),
654            ArgumentHandle::new(&a, &ctx),
655            ArgumentHandle::new(&b, &ctx),
656        ];
657        let result = f
658            .dispatch(&args, &ctx.function_context(None))
659            .unwrap()
660            .into_literal();
661        assert!(matches!(result, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Value));
662    }
663
664    fn range(r: &str, sr: u32, sc: u32, er: u32, ec: u32) -> ASTNode {
665        ASTNode::new(
666            ASTNodeType::Reference {
667                original: r.into(),
668                reference: ReferenceType::range(None, Some(sr), Some(sc), Some(er), Some(ec)),
669            },
670            None,
671        )
672    }
673
674    #[test]
675    fn choosecols_basic_and_negative_and_duplicates() {
676        let wb = TestWorkbook::new().with_function(Arc::new(ChooseColsFn));
677        let wb = wb
678            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
679            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(2))
680            .with_cell_a1("Sheet1", "C1", LiteralValue::Int(3))
681            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(10))
682            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(20))
683            .with_cell_a1("Sheet1", "C2", LiteralValue::Int(30));
684        let ctx = wb.interpreter();
685        let arr = range("A1:C2", 1, 1, 2, 3);
686        let f = ctx.context.get_function("", "CHOOSECOLS").unwrap();
687        let one = lit(LiteralValue::Int(1));
688        let three = lit(LiteralValue::Int(3));
689        let neg_one = lit(LiteralValue::Int(-1));
690        // pick first & third (positive indices)
691        let args = vec![
692            ArgumentHandle::new(&arr, &ctx),
693            ArgumentHandle::new(&one, &ctx),
694            ArgumentHandle::new(&three, &ctx),
695        ];
696        let v = f
697            .dispatch(&args, &ctx.function_context(None))
698            .unwrap()
699            .into_literal();
700        match v {
701            LiteralValue::Array(a) => {
702                assert_eq!(a.len(), 2);
703                assert_eq!(
704                    a[0],
705                    vec![LiteralValue::Number(1.0), LiteralValue::Number(3.0)]
706                );
707            }
708            other => panic!("expected array got {other:?}"),
709        }
710        // negative -1 -> last col only
711        let args_neg = vec![
712            ArgumentHandle::new(&arr, &ctx),
713            ArgumentHandle::new(&neg_one, &ctx),
714        ];
715        let v2 = f
716            .dispatch(&args_neg, &ctx.function_context(None))
717            .unwrap()
718            .into_literal();
719        match v2 {
720            LiteralValue::Array(a) => {
721                assert_eq!(a[0], vec![LiteralValue::Number(3.0)]);
722            }
723            other => panic!("expected array last col got {other:?}"),
724        }
725        // duplicates (1,1)
726        let args_dup = vec![
727            ArgumentHandle::new(&arr, &ctx),
728            ArgumentHandle::new(&one, &ctx),
729            ArgumentHandle::new(&one, &ctx),
730        ];
731        let v3 = f
732            .dispatch(&args_dup, &ctx.function_context(None))
733            .unwrap()
734            .into_literal();
735        match v3 {
736            LiteralValue::Array(a) => {
737                assert_eq!(
738                    a[0],
739                    vec![LiteralValue::Number(1.0), LiteralValue::Number(1.0)]
740                );
741            }
742            other => panic!("expected dup cols got {other:?}"),
743        }
744    }
745
746    #[test]
747    fn choosecols_out_of_range() {
748        let wb = TestWorkbook::new().with_function(Arc::new(ChooseColsFn));
749        let wb = wb
750            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
751            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(2));
752        let ctx = wb.interpreter();
753        let arr = range("A1:B1", 1, 1, 1, 2);
754        let f = ctx.context.get_function("", "CHOOSECOLS").unwrap();
755        let three = lit(LiteralValue::Int(3));
756        let args = vec![
757            ArgumentHandle::new(&arr, &ctx),
758            ArgumentHandle::new(&three, &ctx),
759        ];
760        let v = f
761            .dispatch(&args, &ctx.function_context(None))
762            .unwrap()
763            .into_literal();
764        match v {
765            LiteralValue::Error(e) => assert_eq!(e.kind, ExcelErrorKind::Value),
766            other => panic!("expected #VALUE! got {other:?}"),
767        }
768    }
769
770    #[test]
771    fn chooserows_basic_and_negative() {
772        let wb = TestWorkbook::new().with_function(Arc::new(ChooseRowsFn));
773        let wb = wb
774            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
775            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(2))
776            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(10))
777            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(20))
778            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(100))
779            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(200));
780        let ctx = wb.interpreter();
781        let arr = range("A1:B3", 1, 1, 3, 2);
782        let f = ctx.context.get_function("", "CHOOSEROWS").unwrap();
783        let one = lit(LiteralValue::Int(1));
784        let neg_one = lit(LiteralValue::Int(-1));
785        let args = vec![
786            ArgumentHandle::new(&arr, &ctx),
787            ArgumentHandle::new(&one, &ctx),
788            ArgumentHandle::new(&neg_one, &ctx),
789        ];
790        let v = f
791            .dispatch(&args, &ctx.function_context(None))
792            .unwrap()
793            .into_literal();
794        match v {
795            LiteralValue::Array(a) => {
796                assert_eq!(a.len(), 2);
797                assert_eq!(a[0][0], LiteralValue::Number(1.0));
798                assert_eq!(a[1][0], LiteralValue::Number(100.0));
799            }
800            other => panic!("expected array got {other:?}"),
801        }
802    }
803
804    #[test]
805    fn chooserows_out_of_range() {
806        let wb = TestWorkbook::new().with_function(Arc::new(ChooseRowsFn));
807        let wb = wb.with_cell_a1("Sheet1", "A1", LiteralValue::Int(1));
808        let ctx = wb.interpreter();
809        let arr = range("A1:A1", 1, 1, 1, 1);
810        let f = ctx.context.get_function("", "CHOOSEROWS").unwrap();
811        let two = lit(LiteralValue::Int(2));
812        let args = vec![
813            ArgumentHandle::new(&arr, &ctx),
814            ArgumentHandle::new(&two, &ctx),
815        ];
816        let v = f
817            .dispatch(&args, &ctx.function_context(None))
818            .unwrap()
819            .into_literal();
820        match v {
821            LiteralValue::Error(e) => assert_eq!(e.kind, ExcelErrorKind::Value),
822            other => panic!("expected #VALUE! got {other:?}"),
823        }
824    }
825}
formualizer_eval/builtins/lookup/choose.rs

formualizer_eval/builtins/lookup/
choose.rs
