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

formualizer_eval/builtins/lookup/
choose.rs
