formualizer_eval/builtins/
reference_fns.rs

1use crate::args::{ArgSchema, CoercionPolicy, ShapeKind};
2use crate::function::{FnCaps, Function};
3use crate::traits::{ArgumentHandle, FunctionContext};
4use formualizer_common::{ArgKind, ExcelError, ExcelErrorKind, LiteralValue};
5use formualizer_parse::parser::ReferenceType;
6
7fn number_strict_scalar() -> ArgSchema {
8    ArgSchema {
9        kinds: smallvec::smallvec![ArgKind::Number],
10        required: true,
11        by_ref: false,
12        shape: ShapeKind::Scalar,
13        coercion: CoercionPolicy::NumberStrict,
14        max: None,
15        repeating: None,
16        default: None,
17    }
18}
19
20fn arg_byref_array() -> Vec<ArgSchema> {
21    vec![
22        // Accept both references and array literals
23        ArgSchema {
24            kinds: smallvec::smallvec![ArgKind::Any],
25            required: true,
26            by_ref: false,
27            shape: ShapeKind::Range,
28            coercion: CoercionPolicy::None,
29            max: None,
30            repeating: None,
31            default: None,
32        },
33        number_strict_scalar(),
34        // Column is optional for 1D arrays
35        ArgSchema {
36            kinds: smallvec::smallvec![ArgKind::Number],
37            required: false,
38            by_ref: false,
39            shape: ShapeKind::Scalar,
40            coercion: CoercionPolicy::NumberStrict,
41            max: None,
42            repeating: None,
43            default: None,
44        },
45    ]
46}
47
48fn arg_byref_reference() -> Vec<ArgSchema> {
49    vec![
50        ArgSchema {
51            kinds: smallvec::smallvec![ArgKind::Range],
52            required: true,
53            by_ref: true,
54            shape: ShapeKind::Range,
55            coercion: CoercionPolicy::None,
56            max: None,
57            repeating: None,
58            default: None,
59        },
60        number_strict_scalar(),
61        number_strict_scalar(),
62        ArgSchema {
63            // height optional
64            kinds: smallvec::smallvec![ArgKind::Number],
65            required: false,
66            by_ref: false,
67            shape: ShapeKind::Scalar,
68            coercion: CoercionPolicy::NumberStrict,
69            max: None,
70            repeating: None,
71            default: None,
72        },
73        ArgSchema {
74            // width optional
75            kinds: smallvec::smallvec![ArgKind::Number],
76            required: false,
77            by_ref: false,
78            shape: ShapeKind::Scalar,
79            coercion: CoercionPolicy::NumberStrict,
80            max: None,
81            repeating: None,
82            default: None,
83        },
84    ]
85}
86
87/// Resolve a reference's concrete 1-based inclusive bounds as
88/// `(sheet, start_row, start_col, end_row, end_col)`.
89///
90/// Fully bounded ranges use their declared bounds directly. Unbounded
91/// whole-column/whole-row (or open-ended) ranges are clamped to the used
92/// region via `ctx.resolve_range_view`, mirroring how MATCH/VLOOKUP resolve
93/// the same references. An empty resolved view yields `#REF!`.
94fn resolve_reference_bounds<'b>(
95    ctx: &dyn FunctionContext<'b>,
96    base: &ReferenceType,
97) -> Result<(Option<String>, u32, u32, u32, u32), ExcelError> {
98    match base {
99        ReferenceType::Range {
100            sheet,
101            start_row,
102            start_col,
103            end_row,
104            end_col,
105            ..
106        } => {
107            if let (Some(sr), Some(sc), Some(er), Some(ec)) =
108                (start_row, start_col, end_row, end_col)
109            {
110                return Ok((sheet.clone(), *sr, *sc, *er, *ec));
111            }
112            let rv = ctx.resolve_range_view(base, ctx.current_sheet())?;
113            if rv.is_empty() {
114                return Err(ExcelError::new(ExcelErrorKind::Ref));
115            }
116            // RangeView exposes absolute 0-based coordinates; ReferenceType is 1-based.
117            Ok((
118                sheet.clone(),
119                rv.start_row() as u32 + 1,
120                rv.start_col() as u32 + 1,
121                rv.end_row() as u32 + 1,
122                rv.end_col() as u32 + 1,
123            ))
124        }
125        ReferenceType::Cell {
126            sheet, row, col, ..
127        } => Ok((sheet.clone(), *row, *col, *row, *col)),
128        _ => Err(ExcelError::new(ExcelErrorKind::Ref)),
129    }
130}
131
132#[derive(Debug)]
133pub struct IndexFn;
134
135/// Returns the value or reference at a 1-based row and column within an array or range.
136///
137/// `INDEX` can operate on both references and array literals. When the first argument is
138/// a reference, this implementation resolves a referenced cell and materializes its value in
139/// value context.
140///
141/// # Remarks
142/// - Indexing is 1-based for both `row_num` and `column_num`.
143/// - If `column_num` is omitted for a single-row or single-column input, `row_num` selects the
144///   position along that 1D vector.
145/// - For rectangular 2D inputs, omitted `column_num` defaults to the first column.
146/// - A `row_num` or `column_num` of `0` selects the entire column or row respectively
147///   (both `0` selects the whole range), matching Excel.
148/// - Negative or out-of-bounds indexes return `#REF!`.
149/// - Non-numeric index arguments return `#VALUE!`.
150///
151/// # Examples
152/// ```yaml,sandbox
153/// title: "Pick a value from a 2D table"
154/// grid:
155///   A1: "Item"
156///   B1: "Price"
157///   A2: "Pen"
158///   B2: 2.5
159///   A3: "Book"
160///   B3: 8
161/// formula: '=INDEX(A1:B3,3,2)'
162/// expected: 8
163/// ```
164///
165/// ```yaml,sandbox
166/// title: "Index into a 1D vector"
167/// grid:
168///   A1: "Q1"
169///   A2: "Q2"
170///   A3: "Q3"
171/// formula: '=INDEX(A1:A3,2)'
172/// expected: "Q2"
173/// ```
174///
175/// ```yaml,docs
176/// related:
177///   - MATCH
178///   - XLOOKUP
179///   - OFFSET
180/// faq:
181///   - q: "How does INDEX behave when column_num is omitted?"
182///     a: "For single-row or single-column inputs, row_num selects the position along that vector; for 2D inputs, omitted column_num defaults to the first column."
183///   - q: "Which errors indicate bad indexes?"
184///     a: "Non-numeric index arguments return #VALUE!. A 0 row_num/column_num selects an entire column/row (Excel behavior); negative or out-of-bounds indexes return #REF!."
185/// ```
186/// [formualizer-docgen:schema:start]
187/// Name: INDEX
188/// Type: IndexFn
189/// Min args: 2
190/// Max args: 3
191/// Variadic: false
192/// Signature: INDEX(arg1: any@range, arg2: number@scalar, arg3?: number@scalar)
193/// Arg schema: arg1{kinds=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=NumberStrict,max=None,repeating=None,default=false}; arg3{kinds=number,required=false,shape=scalar,by_ref=false,coercion=NumberStrict,max=None,repeating=None,default=false}
194/// Caps: PURE, RETURNS_REFERENCE
195/// [formualizer-docgen:schema:end]
196impl Function for IndexFn {
197    fn caps(&self) -> FnCaps {
198        FnCaps::PURE | FnCaps::RETURNS_REFERENCE
199    }
200    fn name(&self) -> &'static str {
201        "INDEX"
202    }
203    fn min_args(&self) -> usize {
204        2
205    }
206    fn arg_schema(&self) -> &'static [ArgSchema] {
207        use once_cell::sync::Lazy;
208        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(arg_byref_array);
209        &SCHEMA
210    }
211
212    fn eval_reference<'a, 'b, 'c>(
213        &self,
214        args: &'c [ArgumentHandle<'a, 'b>],
215        ctx: &dyn FunctionContext<'b>,
216    ) -> Option<Result<ReferenceType, ExcelError>> {
217        // args: array(by_ref), row, col (col optional for 1D)
218        if args.len() < 2 {
219            return Some(Err(ExcelError::new(ExcelErrorKind::Value)));
220        }
221        // Return None for array literals so eval() handles them
222        let base = match args[0].as_reference_or_eval() {
223            Ok(r) => r,
224            Err(_) => return None,
225        };
226        let position = match args[1].value() {
227            Ok(cv) => match cv.into_literal() {
228                LiteralValue::Number(n) => n as i64,
229                LiteralValue::Int(i) => i,
230                _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
231            },
232            Err(e) => return Some(Err(e)),
233        };
234        let explicit_col = if args.len() >= 3 {
235            Some(match args[2].value() {
236                Ok(cv) => match cv.into_literal() {
237                    LiteralValue::Number(n) => n as i64,
238                    LiteralValue::Int(i) => i,
239                    _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
240                },
241                Err(e) => return Some(Err(e)),
242            })
243        } else {
244            None
245        };
246
247        // Only Range/Cell supported for now; unbounded ranges (e.g. B:B, 2:2)
248        // are clamped to the used region instead of erroring.
249        let (sheet, sr, sc, er, ec) = match resolve_reference_bounds(ctx, &base) {
250            Ok(bounds) => bounds,
251            Err(e) => return Some(Err(e)),
252        };
253
254        let (row, col) = match explicit_col {
255            Some(col) => (position, col),
256            None if sr == er => {
257                // Excel treats INDEX(single_row_range, n) as horizontal indexing.
258                (1, position)
259            }
260            None => {
261                // Excel treats INDEX(single_col_range, n) as vertical indexing and defaults
262                // 2-D ranges to the first column when column_num is omitted.
263                (position, 1)
264            }
265        };
266
267        // 1-based indexing per Excel. A 0 means "the entire row" (column_num == 0)
268        // or "the entire column" (row_num == 0); 0 for both yields the whole range.
269        // Negative indices are #REF!.
270        if row < 0 || col < 0 {
271            return Some(Err(ExcelError::new(ExcelErrorKind::Ref)));
272        }
273        let range_ref = |sheet, sr, sc, er, ec| ReferenceType::Range {
274            sheet,
275            start_row: Some(sr),
276            start_col: Some(sc),
277            end_row: Some(er),
278            end_col: Some(ec),
279            start_row_abs: false,
280            start_col_abs: false,
281            end_row_abs: false,
282            end_col_abs: false,
283        };
284        if col == 0 {
285            if row == 0 {
286                // INDEX(range, 0, 0) -> the entire range.
287                return Some(Ok(range_ref(sheet, sr, sc, er, ec)));
288            }
289            // INDEX(range, r, 0) -> the entire row r (degenerates to a cell for a
290            // single-column range).
291            let r = sr + (row as u32) - 1;
292            if r > er {
293                return Some(Err(ExcelError::new(ExcelErrorKind::Ref)));
294            }
295            return Some(Ok(if sc == ec {
296                ReferenceType::cell(sheet, r, sc)
297            } else {
298                range_ref(sheet, r, sc, r, ec)
299            }));
300        }
301        if row == 0 {
302            // INDEX(range, 0, c) -> the entire column c (degenerates to a cell for a
303            // single-row range).
304            let c = sc + (col as u32) - 1;
305            if c > ec {
306                return Some(Err(ExcelError::new(ExcelErrorKind::Ref)));
307            }
308            return Some(Ok(if sr == er {
309                ReferenceType::cell(sheet, sr, c)
310            } else {
311                range_ref(sheet, sr, c, er, c)
312            }));
313        }
314        let r = sr + (row as u32) - 1;
315        let c = sc + (col as u32) - 1;
316        if r > er || c > ec {
317            return Some(Err(ExcelError::new(ExcelErrorKind::Ref)));
318        }
319
320        Some(Ok(ReferenceType::cell(sheet, r, c)))
321    }
322
323    fn eval<'a, 'b, 'c>(
324        &self,
325        args: &'c [ArgumentHandle<'a, 'b>],
326        ctx: &dyn FunctionContext<'b>,
327    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
328        // First try to handle as a reference
329        if let Some(result) = self.eval_reference(args, ctx) {
330            match result {
331                Ok(r) => {
332                    // Materialize to value
333                    let current_sheet = ctx.current_sheet();
334                    match ctx.resolve_range_view(&r, current_sheet) {
335                        Ok(rv) => {
336                            let (rows, cols) = rv.dims();
337                            if rows == 1 && cols == 1 {
338                                Ok(crate::traits::CalcValue::Scalar(
339                                    rv.as_1x1().unwrap_or(LiteralValue::Empty),
340                                ))
341                            } else {
342                                Ok(crate::traits::CalcValue::Range(rv))
343                            }
344                        }
345                        Err(e) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
346                    }
347                }
348                Err(e) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
349            }
350        } else {
351            // Handle array literal
352            if args.len() < 2 {
353                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
354                    ExcelError::new(ExcelErrorKind::Value),
355                )));
356            }
357            let v = args[0].value()?.into_literal();
358            let table: Vec<Vec<LiteralValue>> = match v {
359                LiteralValue::Array(rows) => rows,
360                other => vec![vec![other]],
361            };
362            let index = match args[1].value()?.into_literal() {
363                LiteralValue::Number(n) => n as i64,
364                LiteralValue::Int(i) => i,
365                _ => {
366                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
367                        ExcelError::new(ExcelErrorKind::Value),
368                    )));
369                }
370            };
371
372            // Optional explicit column_num (third argument).
373            let explicit_col = if args.len() >= 3 {
374                Some(match args[2].value()?.into_literal() {
375                    LiteralValue::Number(n) => n as i64,
376                    LiteralValue::Int(i) => i,
377                    _ => {
378                        return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
379                            ExcelError::new(ExcelErrorKind::Value),
380                        )));
381                    }
382                })
383            } else {
384                None
385            };
386
387            let nrows = table.len();
388            let ncols = table.iter().map(|r| r.len()).max().unwrap_or(0);
389            let single_row = nrows == 1;
390
391            // Map (index, optional column) to (row, col) exactly like eval_reference:
392            // for a single-row input the lone index selects the column, otherwise it
393            // selects the row and the column defaults to 1.
394            let (row, col) = match explicit_col {
395                Some(c) => (index, c),
396                None if single_row => (1, index),
397                None => (index, 1),
398            };
399
400            // Negative indices are #REF!. A 0 selects the entire row (column_num == 0)
401            // or entire column (row_num == 0); 0 for both yields the whole array.
402            // This mirrors the reference path so the two don't drift apart.
403            let ref_err = || {
404                crate::traits::CalcValue::Scalar(LiteralValue::Error(ExcelError::new(
405                    ExcelErrorKind::Ref,
406                )))
407            };
408            if row < 0 || col < 0 {
409                return Ok(ref_err());
410            }
411
412            // Wrap a multi-cell array result in a RangeView so aggregations
413            // (SUM, etc.) iterate it, matching how the reference path returns
414            // CalcValue::Range. A literal LiteralValue::Array would otherwise be
415            // strict-coerced as a single scalar by numeric callers.
416            let as_range = |rows: Vec<Vec<LiteralValue>>| {
417                crate::traits::CalcValue::Range(
418                    crate::engine::range_view::RangeView::from_owned_rows(rows, ctx.date_system()),
419                )
420            };
421
422            if col == 0 {
423                if row == 0 {
424                    // INDEX(array, 0, 0) -> the whole array.
425                    return Ok(as_range(table));
426                }
427                // INDEX(array, r, 0) -> the entire row r (scalar for a single-column array).
428                if row as usize > nrows {
429                    return Ok(ref_err());
430                }
431                let r = &table[row as usize - 1];
432                if ncols == 1 {
433                    return Ok(crate::traits::CalcValue::Scalar(
434                        r.first().cloned().unwrap_or(LiteralValue::Empty),
435                    ));
436                }
437                return Ok(as_range(vec![r.clone()]));
438            }
439            if row == 0 {
440                // INDEX(array, 0, c) -> the entire column c (scalar for a single-row array).
441                if col as usize > ncols {
442                    return Ok(ref_err());
443                }
444                let cidx = col as usize - 1;
445                if single_row {
446                    return Ok(crate::traits::CalcValue::Scalar(
447                        table[0].get(cidx).cloned().unwrap_or(LiteralValue::Empty),
448                    ));
449                }
450                let column: Vec<Vec<LiteralValue>> = table
451                    .iter()
452                    .map(|r| vec![r.get(cidx).cloned().unwrap_or(LiteralValue::Empty)])
453                    .collect();
454                return Ok(as_range(column));
455            }
456
457            // 1-based positive indexing.
458            if row as usize > nrows || col as usize > ncols {
459                return Ok(ref_err());
460            }
461            let val = table
462                .get(row as usize - 1)
463                .and_then(|r| r.get(col as usize - 1))
464                .cloned()
465                .unwrap_or_else(|| LiteralValue::Error(ExcelError::new(ExcelErrorKind::Ref)));
466            Ok(crate::traits::CalcValue::Scalar(val))
467        }
468    }
469}
470
471#[derive(Debug)]
472pub struct OffsetFn;
473
474/// Returns a reference shifted from a starting reference by rows and columns.
475///
476/// `OFFSET` is volatile and returns a reference that can point to a single cell or a resized
477/// range, depending on the optional `height` and `width` arguments.
478///
479/// # Remarks
480/// - `rows` and `cols` shift from the top-left of `reference`.
481/// - If omitted, `height` and `width` default to the original reference size.
482/// - Non-positive target coordinates or dimensions return `#REF!`.
483/// - Non-numeric offset/size inputs return `#VALUE!`.
484/// - In value context, a 1x1 result returns a scalar; larger results spill as an array.
485///
486/// # Examples
487/// ```yaml,sandbox
488/// title: "Move one row down and one column right"
489/// grid:
490///   A1: 10
491///   B2: 42
492/// formula: '=OFFSET(A1,1,1)'
493/// expected: 42
494/// ```
495///
496/// ```yaml,sandbox
497/// title: "Offset and resize a range"
498/// grid:
499///   A1: 1
500///   A2: 2
501///   A3: 3
502///   B1: 4
503///   B2: 5
504///   B3: 6
505/// formula: '=SUM(OFFSET(A1,1,0,2,2))'
506/// expected: 16
507/// ```
508///
509/// ```yaml,docs
510/// related:
511///   - INDEX
512///   - INDIRECT
513///   - ADDRESS
514/// faq:
515///   - q: "What defaults are used when height and width are omitted?"
516///     a: "OFFSET keeps the source reference size, then applies the row/column shift to that same-sized block."
517///   - q: "When does OFFSET return #REF!?"
518///     a: "It returns #REF! if the shifted start goes to row/column <= 0 or if requested height/width are non-positive."
519/// ```
520/// [formualizer-docgen:schema:start]
521/// Name: OFFSET
522/// Type: OffsetFn
523/// Min args: 3
524/// Max args: 5
525/// Variadic: false
526/// Signature: OFFSET(arg1: range@range, arg2: number@scalar, arg3: number@scalar, arg4?: number@scalar, arg5?: number@scalar)
527/// Arg schema: arg1{kinds=range,required=true,shape=range,by_ref=true,coercion=None,max=None,repeating=None,default=false}; arg2{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberStrict,max=None,repeating=None,default=false}; arg3{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberStrict,max=None,repeating=None,default=false}; arg4{kinds=number,required=false,shape=scalar,by_ref=false,coercion=NumberStrict,max=None,repeating=None,default=false}; arg5{kinds=number,required=false,shape=scalar,by_ref=false,coercion=NumberStrict,max=None,repeating=None,default=false}
528/// Caps: PURE, VOLATILE, RETURNS_REFERENCE, DYNAMIC_DEPENDENCY
529/// [formualizer-docgen:schema:end]
530impl Function for OffsetFn {
531    fn caps(&self) -> FnCaps {
532        // OFFSET is volatile in Excel semantics and has runtime-dynamic dependencies.
533        FnCaps::PURE | FnCaps::RETURNS_REFERENCE | FnCaps::VOLATILE | FnCaps::DYNAMIC_DEPENDENCY
534    }
535    fn name(&self) -> &'static str {
536        "OFFSET"
537    }
538    fn min_args(&self) -> usize {
539        3
540    }
541    fn arg_schema(&self) -> &'static [ArgSchema] {
542        use once_cell::sync::Lazy;
543        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(arg_byref_reference);
544        &SCHEMA
545    }
546
547    fn eval_reference<'a, 'b, 'c>(
548        &self,
549        args: &'c [ArgumentHandle<'a, 'b>],
550        ctx: &dyn FunctionContext<'b>,
551    ) -> Option<Result<ReferenceType, ExcelError>> {
552        if args.len() < 3 {
553            return Some(Err(ExcelError::new(ExcelErrorKind::Value)));
554        }
555        let base = match args[0].as_reference_or_eval() {
556            Ok(r) => r,
557            Err(e) => return Some(Err(e)),
558        };
559        let dr = match args[1].value() {
560            Ok(cv) => match cv.into_literal() {
561                LiteralValue::Number(n) => n as i64,
562                LiteralValue::Int(i) => i,
563                _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
564            },
565            Err(e) => return Some(Err(e)),
566        };
567        let dc = match args[2].value() {
568            Ok(cv) => match cv.into_literal() {
569                LiteralValue::Number(n) => n as i64,
570                LiteralValue::Int(i) => i,
571                _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
572            },
573            Err(e) => return Some(Err(e)),
574        };
575
576        // Unbounded ranges (e.g. B:B, 2:2) are clamped to the used region
577        // instead of erroring.
578        let (sheet, sr, sc, er, ec) = match resolve_reference_bounds(ctx, &base) {
579            Ok(bounds) => bounds,
580            Err(e) => return Some(Err(e)),
581        };
582
583        let nsr = (sr as i64) + dr;
584        let nsc = (sc as i64) + dc;
585        let height = if args.len() >= 4 {
586            match args[3].value() {
587                Ok(cv) => match cv.into_literal() {
588                    LiteralValue::Number(n) => n as i64,
589                    LiteralValue::Int(i) => i,
590                    _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
591                },
592                Err(e) => return Some(Err(e)),
593            }
594        } else {
595            (er as i64) - (sr as i64) + 1
596        };
597        let width = if args.len() >= 5 {
598            match args[4].value() {
599                Ok(cv) => match cv.into_literal() {
600                    LiteralValue::Number(n) => n as i64,
601                    LiteralValue::Int(i) => i,
602                    _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
603                },
604                Err(e) => return Some(Err(e)),
605            }
606        } else {
607            (ec as i64) - (sc as i64) + 1
608        };
609
610        if nsr <= 0 || nsc <= 0 || height <= 0 || width <= 0 {
611            return Some(Err(ExcelError::new(ExcelErrorKind::Ref)));
612        }
613        let ner = nsr + height - 1;
614        let nec = nsc + width - 1;
615
616        if height == 1 && width == 1 {
617            Some(Ok(ReferenceType::cell(sheet, nsr as u32, nsc as u32)))
618        } else {
619            Some(Ok(ReferenceType::range(
620                sheet,
621                Some(nsr as u32),
622                Some(nsc as u32),
623                Some(ner as u32),
624                Some(nec as u32),
625            )))
626        }
627    }
628
629    fn eval<'a, 'b, 'c>(
630        &self,
631        args: &'c [ArgumentHandle<'a, 'b>],
632        ctx: &dyn FunctionContext<'b>,
633    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
634        if let Some(Ok(r)) = self.eval_reference(args, ctx) {
635            let current_sheet = ctx.current_sheet();
636            match ctx.resolve_range_view(&r, current_sheet) {
637                Ok(rv) => {
638                    let (rows, cols) = rv.dims();
639                    if rows == 1 && cols == 1 {
640                        Ok(crate::traits::CalcValue::Scalar(
641                            rv.as_1x1().unwrap_or(LiteralValue::Empty),
642                        ))
643                    } else {
644                        Ok(crate::traits::CalcValue::Range(rv))
645                    }
646                }
647                Err(e) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
648            }
649        } else {
650            Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
651                ExcelError::new(ExcelErrorKind::Ref),
652            )))
653        }
654    }
655}
656
657fn arg_indirect() -> Vec<ArgSchema> {
658    vec![
659        ArgSchema {
660            kinds: smallvec::smallvec![ArgKind::Text],
661            required: true,
662            by_ref: false,
663            shape: ShapeKind::Scalar,
664            coercion: CoercionPolicy::None,
665            max: None,
666            repeating: None,
667            default: None,
668        },
669        ArgSchema {
670            kinds: smallvec::smallvec![ArgKind::Logical, ArgKind::Number],
671            required: false,
672            by_ref: false,
673            shape: ShapeKind::Scalar,
674            coercion: CoercionPolicy::Logical,
675            max: None,
676            repeating: None,
677            default: Some(LiteralValue::Boolean(true)),
678        },
679    ]
680}
681
682#[derive(Debug)]
683pub struct IndirectFn;
684
685/// Converts text into a reference and returns the referenced value or range.
686///
687/// `INDIRECT` lets formulas build references dynamically from strings such as `"A1"` or
688/// `"Sheet2!B3:C5"`.
689///
690/// # Remarks
691/// - `a1_style` defaults to `TRUE` (A1 style parsing).
692/// - `a1_style=FALSE` (R1C1 parsing) is currently not implemented and returns `#N/IMPL!`.
693/// - Invalid or unresolved references return `#REF!`.
694/// - The function is volatile because target references can change without direct dependency links.
695///
696/// # Examples
697/// ```yaml,sandbox
698/// title: "Resolve a direct cell reference"
699/// grid:
700///   A1: 99
701/// formula: '=INDIRECT("A1")'
702/// expected: 99
703/// ```
704///
705/// ```yaml,sandbox
706/// title: "Resolve a range and aggregate it"
707/// grid:
708///   A1: 5
709///   A2: 7
710///   A3: 9
711/// formula: '=SUM(INDIRECT("A1:A3"))'
712/// expected: 21
713/// ```
714///
715/// ```yaml,docs
716/// related:
717///   - ADDRESS
718///   - INDEX
719///   - OFFSET
720/// faq:
721///   - q: "What happens if a1_style is FALSE?"
722///     a: "R1C1 parsing is not implemented here yet, so INDIRECT(...,FALSE) returns #N/IMPL!."
723///   - q: "How are bad reference strings reported?"
724///     a: "If the text cannot be parsed or resolved to a valid reference, INDIRECT returns #REF!."
725/// ```
726/// [formualizer-docgen:schema:start]
727/// Name: INDIRECT
728/// Type: IndirectFn
729/// Min args: 1
730/// Max args: 2
731/// Variadic: false
732/// Signature: INDIRECT(arg1: text@scalar, arg2?: logical|number@scalar)
733/// Arg schema: arg1{kinds=text,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg2{kinds=logical|number,required=false,shape=scalar,by_ref=false,coercion=Logical,max=None,repeating=None,default=true}
734/// Caps: PURE, VOLATILE, RETURNS_REFERENCE, DYNAMIC_DEPENDENCY
735/// [formualizer-docgen:schema:end]
736impl Function for IndirectFn {
737    fn caps(&self) -> FnCaps {
738        FnCaps::PURE | FnCaps::RETURNS_REFERENCE | FnCaps::VOLATILE | FnCaps::DYNAMIC_DEPENDENCY
739    }
740    fn name(&self) -> &'static str {
741        "INDIRECT"
742    }
743    fn min_args(&self) -> usize {
744        1
745    }
746    fn arg_schema(&self) -> &'static [ArgSchema] {
747        use once_cell::sync::Lazy;
748        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(arg_indirect);
749        &SCHEMA
750    }
751
752    fn eval_reference<'a, 'b, 'c>(
753        &self,
754        args: &'c [ArgumentHandle<'a, 'b>],
755        _ctx: &dyn FunctionContext<'b>,
756    ) -> Option<Result<ReferenceType, ExcelError>> {
757        if args.is_empty() {
758            return Some(Err(ExcelError::new(ExcelErrorKind::Value)));
759        }
760
761        let ref_text = match args[0].value() {
762            Ok(cv) => match cv.into_literal() {
763                LiteralValue::Text(s) => s.to_string(),
764                _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
765            },
766            Err(e) => return Some(Err(e)),
767        };
768
769        let a1_style = if args.len() >= 2 {
770            match args[1].value() {
771                Ok(cv) => match cv.into_literal() {
772                    LiteralValue::Boolean(b) => b,
773                    LiteralValue::Int(i) => i != 0,
774                    LiteralValue::Number(n) => n != 0.0,
775                    _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
776                },
777                Err(e) => return Some(Err(e)),
778            }
779        } else {
780            true
781        };
782
783        if !a1_style {
784            // The A1/R1C1 flag does not apply to defined names or tables (they are
785            // neither A1 nor R1C1 syntax). Excel resolves `INDIRECT(name, FALSE)`
786            // exactly like `INDIRECT(name)`, so handle those before refusing R1C1.
787            // Real R1C1 cell/range text remains unsupported.
788            return match formualizer_parse::parser::ReferenceType::from_string(&ref_text) {
789                Ok(ReferenceType::NamedRange(name)) => Some(Ok(ReferenceType::NamedRange(name))),
790                Ok(ReferenceType::Table(tref)) => Some(Ok(ReferenceType::Table(tref))),
791                _ => Some(Err(ExcelError::new(ExcelErrorKind::NImpl).with_message(
792                    "INDIRECT with R1C1 style (second argument FALSE) is not yet supported",
793                ))),
794            };
795        }
796
797        let parsed = formualizer_parse::parser::ReferenceType::parse_sheet_ref(&ref_text);
798
799        match parsed {
800            Ok(formualizer_common::SheetRef::Cell(cell)) => {
801                let sheet = match cell.sheet {
802                    formualizer_common::SheetLocator::Current => None,
803                    formualizer_common::SheetLocator::Name(name) => Some(name.to_string()),
804                    formualizer_common::SheetLocator::Id(_) => None,
805                };
806                Some(Ok(ReferenceType::Cell {
807                    sheet,
808                    row: cell.coord.row() + 1,
809                    col: cell.coord.col() + 1,
810                    row_abs: cell.coord.row_abs(),
811                    col_abs: cell.coord.col_abs(),
812                }))
813            }
814            Ok(formualizer_common::SheetRef::Range(range)) => {
815                let sheet = match range.sheet {
816                    formualizer_common::SheetLocator::Current => None,
817                    formualizer_common::SheetLocator::Name(name) => Some(name.to_string()),
818                    formualizer_common::SheetLocator::Id(_) => None,
819                };
820                Some(Ok(ReferenceType::Range {
821                    sheet,
822                    start_row: range.start_row.map(|b| b.index + 1),
823                    start_col: range.start_col.map(|b| b.index + 1),
824                    end_row: range.end_row.map(|b| b.index + 1),
825                    end_col: range.end_col.map(|b| b.index + 1),
826                    start_row_abs: range.start_row.map(|b| b.abs).unwrap_or(false),
827                    start_col_abs: range.start_col.map(|b| b.abs).unwrap_or(false),
828                    end_row_abs: range.end_row.map(|b| b.abs).unwrap_or(false),
829                    end_col_abs: range.end_col.map(|b| b.abs).unwrap_or(false),
830                }))
831            }
832            Err(_) => match formualizer_parse::parser::ReferenceType::from_string(&ref_text) {
833                Ok(ReferenceType::NamedRange(name)) => Some(Ok(ReferenceType::NamedRange(name))),
834                Ok(ReferenceType::Table(tref)) => Some(Ok(ReferenceType::Table(tref))),
835                _ => Some(Err(ExcelError::new(ExcelErrorKind::Ref))),
836            },
837        }
838    }
839
840    fn eval<'a, 'b, 'c>(
841        &self,
842        args: &'c [ArgumentHandle<'a, 'b>],
843        ctx: &dyn FunctionContext<'b>,
844    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
845        match self.eval_reference(args, ctx) {
846            Some(Ok(r)) => {
847                let current_sheet = ctx.current_sheet();
848                match ctx.resolve_range_view(&r, current_sheet) {
849                    Ok(rv) => {
850                        let (rows, cols) = rv.dims();
851                        if rows == 1 && cols == 1 {
852                            Ok(crate::traits::CalcValue::Scalar(
853                                rv.as_1x1().unwrap_or(LiteralValue::Empty),
854                            ))
855                        } else {
856                            Ok(crate::traits::CalcValue::Range(rv))
857                        }
858                    }
859                    Err(e) => {
860                        let mapped = if e.kind == ExcelErrorKind::Name {
861                            ExcelError::new(ExcelErrorKind::Ref)
862                        } else {
863                            e
864                        };
865                        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
866                            mapped,
867                        )))
868                    }
869                }
870            }
871            Some(Err(e)) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
872            None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
873                ExcelError::new(ExcelErrorKind::Ref),
874            ))),
875        }
876    }
877}
878
879pub fn register_builtins() {
880    crate::function_registry::register_function(std::sync::Arc::new(IndexFn));
881    crate::function_registry::register_function(std::sync::Arc::new(OffsetFn));
882    crate::function_registry::register_function(std::sync::Arc::new(IndirectFn));
883}
884
885#[cfg(test)]
886mod tests {
887    use super::*;
888    use crate::builtins::lookup::MatchFn;
889    use crate::test_workbook::TestWorkbook;
890    use crate::traits::ArgumentHandle;
891    use formualizer_common::error::{ExcelError, ExcelErrorKind};
892    use formualizer_parse::parser::{ASTNode, ASTNodeType, Parser};
893
894    fn interp(wb: &TestWorkbook) -> crate::interpreter::Interpreter<'_> {
895        wb.interpreter()
896    }
897
898    fn evaluate_formula(formula: &str, wb: &TestWorkbook) -> Result<LiteralValue, ExcelError> {
899        let mut parser = Parser::new(formula).unwrap();
900        let ast = parser
901            .parse()
902            .map_err(|e| ExcelError::new(ExcelErrorKind::Error).with_message(e.message.clone()))?;
903        Ok(interp(wb).evaluate_ast(&ast)?.into_literal())
904    }
905
906    #[test]
907    fn index_returns_reference_and_materializes_in_value_context() {
908        let wb = TestWorkbook::new()
909            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(42))
910            .with_function(std::sync::Arc::new(IndexFn));
911        let ctx = interp(&wb);
912
913        // Build INDEX(A1:C3,2,2) expecting B2
914        let array_ref = ASTNode::new(
915            ASTNodeType::Reference {
916                original: "A1:C3".into(),
917                reference: ReferenceType::Range {
918                    sheet: None,
919                    start_row: Some(1),
920                    start_col: Some(1),
921                    end_row: Some(3),
922                    end_col: Some(3),
923                    start_row_abs: false,
924                    start_col_abs: false,
925                    end_row_abs: false,
926                    end_col_abs: false,
927                },
928            },
929            None,
930        );
931        let row = ASTNode::new(ASTNodeType::Literal(LiteralValue::Int(2)), None);
932        let col = ASTNode::new(ASTNodeType::Literal(LiteralValue::Int(2)), None);
933        let call = ASTNode::new(
934            ASTNodeType::Function {
935                name: "INDEX".into(),
936                args: vec![array_ref.clone(), row.clone(), col.clone()],
937            },
938            None,
939        );
940
941        // Reference context
942        let r = ctx.evaluate_ast_as_reference(&call).expect("ref ok");
943        match r {
944            ReferenceType::Cell { row, col, .. } => {
945                assert_eq!((row, col), (2, 2));
946            }
947            _ => panic!(),
948        }
949
950        // Value context (scalar materialization)
951        let args = vec![
952            ArgumentHandle::new(&array_ref, &ctx),
953            ArgumentHandle::new(&row, &ctx),
954            ArgumentHandle::new(&col, &ctx),
955        ];
956        let f = ctx.context.get_function("", "INDEX").unwrap();
957        let v = f
958            .dispatch(&args, &ctx.function_context(None))
959            .unwrap()
960            .into_literal();
961        assert_eq!(v, LiteralValue::Number(42.0));
962    }
963
964    #[test]
965    fn index_single_row_reference_uses_omitted_col_as_horizontal_position() {
966        let wb = TestWorkbook::new()
967            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
968            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(20))
969            .with_cell_a1("Sheet1", "C1", LiteralValue::Int(30))
970            .with_function(std::sync::Arc::new(IndexFn));
971        let ctx = interp(&wb);
972
973        let array_ref = ASTNode::new(
974            ASTNodeType::Reference {
975                original: "A1:C1".into(),
976                reference: ReferenceType::Range {
977                    sheet: None,
978                    start_row: Some(1),
979                    start_col: Some(1),
980                    end_row: Some(1),
981                    end_col: Some(3),
982                    start_row_abs: false,
983                    start_col_abs: false,
984                    end_row_abs: false,
985                    end_col_abs: false,
986                },
987            },
988            None,
989        );
990        let index = ASTNode::new(ASTNodeType::Literal(LiteralValue::Int(2)), None);
991        let call = ASTNode::new(
992            ASTNodeType::Function {
993                name: "INDEX".into(),
994                args: vec![array_ref.clone(), index.clone()],
995            },
996            None,
997        );
998
999        let r = ctx.evaluate_ast_as_reference(&call).expect("ref ok");
1000        match r {
1001            ReferenceType::Cell { row, col, .. } => assert_eq!((row, col), (1, 2)),
1002            _ => panic!(),
1003        }
1004
1005        let args = vec![
1006            ArgumentHandle::new(&array_ref, &ctx),
1007            ArgumentHandle::new(&index, &ctx),
1008        ];
1009        let f = ctx.context.get_function("", "INDEX").unwrap();
1010        let v = f
1011            .dispatch(&args, &ctx.function_context(None))
1012            .unwrap()
1013            .into_literal();
1014        assert_eq!(v, LiteralValue::Number(20.0));
1015    }
1016
1017    #[test]
1018    fn index_single_column_reference_keeps_omitted_col_as_vertical_position() {
1019        let wb = TestWorkbook::new()
1020            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
1021            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
1022            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
1023            .with_function(std::sync::Arc::new(IndexFn));
1024        let ctx = interp(&wb);
1025
1026        let array_ref = ASTNode::new(
1027            ASTNodeType::Reference {
1028                original: "A1:A3".into(),
1029                reference: ReferenceType::Range {
1030                    sheet: None,
1031                    start_row: Some(1),
1032                    start_col: Some(1),
1033                    end_row: Some(3),
1034                    end_col: Some(1),
1035                    start_row_abs: false,
1036                    start_col_abs: false,
1037                    end_row_abs: false,
1038                    end_col_abs: false,
1039                },
1040            },
1041            None,
1042        );
1043        let index = ASTNode::new(ASTNodeType::Literal(LiteralValue::Int(2)), None);
1044        let args = vec![
1045            ArgumentHandle::new(&array_ref, &ctx),
1046            ArgumentHandle::new(&index, &ctx),
1047        ];
1048        let f = ctx.context.get_function("", "INDEX").unwrap();
1049        let v = f
1050            .dispatch(&args, &ctx.function_context(None))
1051            .unwrap()
1052            .into_literal();
1053        assert_eq!(v, LiteralValue::Number(20.0));
1054    }
1055
1056    #[test]
1057    fn index_rectangular_reference_defaults_omitted_col_to_first_column() {
1058        let wb = TestWorkbook::new()
1059            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
1060            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
1061            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(200))
1062            .with_function(std::sync::Arc::new(IndexFn));
1063
1064        let value = evaluate_formula("=INDEX(A1:B2,2)", &wb).unwrap();
1065        assert_eq!(value, LiteralValue::Number(20.0));
1066    }
1067
1068    #[test]
1069    fn index_single_row_reference_match_position_materializes_value() {
1070        let wb = TestWorkbook::new()
1071            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
1072            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(20))
1073            .with_cell_a1("Sheet1", "C1", LiteralValue::Int(30))
1074            .with_function(std::sync::Arc::new(IndexFn))
1075            .with_function(std::sync::Arc::new(MatchFn));
1076
1077        let value = evaluate_formula("=INDEX(A1:C1,MATCH(20,A1:C1,0))", &wb).unwrap();
1078        assert_eq!(value, LiteralValue::Number(20.0));
1079    }
1080
1081    #[test]
1082    fn index_single_row_reference_out_of_bounds_is_ref() {
1083        let wb = TestWorkbook::new()
1084            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
1085            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(20))
1086            .with_cell_a1("Sheet1", "C1", LiteralValue::Int(30))
1087            .with_function(std::sync::Arc::new(IndexFn));
1088
1089        let value = evaluate_formula("=INDEX(A1:C1,4)", &wb).unwrap();
1090        match value {
1091            LiteralValue::Error(err) => assert_eq!(err.kind, ExcelErrorKind::Ref),
1092            other => panic!("expected #REF!, got {other:?}"),
1093        }
1094    }
1095
1096    #[test]
1097    fn index_zero_column_degenerates_to_cell_in_single_column_range() {
1098        // INDEX(B1:B5, 2, 0) -> entire row 2 of a single-column range = B2.
1099        let wb = TestWorkbook::new()
1100            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(10))
1101            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(20))
1102            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(30))
1103            .with_function(std::sync::Arc::new(IndexFn));
1104
1105        let value = evaluate_formula("=INDEX(B1:B5,2,0)", &wb).unwrap();
1106        assert_eq!(value, LiteralValue::Number(20.0));
1107    }
1108
1109    #[test]
1110    fn index_zero_row_degenerates_to_cell_in_single_row_range() {
1111        // INDEX(A1:C1, 0, 2) -> entire column 2 of a single-row range = B1.
1112        let wb = TestWorkbook::new()
1113            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
1114            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(20))
1115            .with_cell_a1("Sheet1", "C1", LiteralValue::Int(30))
1116            .with_function(std::sync::Arc::new(IndexFn));
1117
1118        let value = evaluate_formula("=INDEX(A1:C1,0,2)", &wb).unwrap();
1119        assert_eq!(value, LiteralValue::Number(20.0));
1120    }
1121
1122    #[test]
1123    fn index_zero_column_returns_entire_row_range() {
1124        // INDEX(A1:C3, 2, 0) -> entire row 2 (A2:C2); SUM materializes it.
1125        let wb = TestWorkbook::new()
1126            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(1))
1127            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(2))
1128            .with_cell_a1("Sheet1", "C2", LiteralValue::Int(3))
1129            .with_function(std::sync::Arc::new(IndexFn))
1130            .with_function(std::sync::Arc::new(crate::builtins::math::aggregate::SumFn));
1131
1132        let value = evaluate_formula("=SUM(INDEX(A1:C3,2,0))", &wb).unwrap();
1133        assert_eq!(value, LiteralValue::Number(6.0));
1134    }
1135
1136    #[test]
1137    fn index_zero_row_returns_entire_column_range() {
1138        // INDEX(A1:C3, 0, 2) -> entire column 2 (B1:B3); SUM materializes it.
1139        let wb = TestWorkbook::new()
1140            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(4))
1141            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(5))
1142            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(6))
1143            .with_function(std::sync::Arc::new(IndexFn))
1144            .with_function(std::sync::Arc::new(crate::builtins::math::aggregate::SumFn));
1145
1146        let value = evaluate_formula("=SUM(INDEX(A1:C3,0,2))", &wb).unwrap();
1147        assert_eq!(value, LiteralValue::Number(15.0));
1148    }
1149
1150    #[test]
1151    fn index_negative_index_is_ref() {
1152        let wb = TestWorkbook::new()
1153            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
1154            .with_function(std::sync::Arc::new(IndexFn));
1155
1156        let value = evaluate_formula("=INDEX(A1:C3,-1,2)", &wb).unwrap();
1157        match value {
1158            LiteralValue::Error(err) => assert_eq!(err.kind, ExcelErrorKind::Ref),
1159            other => panic!("expected #REF!, got {other:?}"),
1160        }
1161    }
1162
1163    fn as_number(v: &LiteralValue) -> f64 {
1164        match v {
1165            LiteralValue::Number(n) => *n,
1166            LiteralValue::Int(i) => *i as f64,
1167            other => panic!("expected number, got {other:?}"),
1168        }
1169    }
1170
1171    #[test]
1172    fn index_array_constant_zero_column_returns_entire_row() {
1173        // INDEX({1,2,3},0) over an array constant -> the whole row {1,2,3}.
1174        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(IndexFn));
1175
1176        let raw = evaluate_formula("=INDEX({1,2,3},0)", &wb).unwrap();
1177        let LiteralValue::Array(rows) = raw else {
1178            panic!("expected a 1x3 array, got {raw:?}");
1179        };
1180        assert_eq!(rows.len(), 1);
1181        let flat: Vec<f64> = rows[0].iter().map(as_number).collect();
1182        assert_eq!(flat, vec![1.0, 2.0, 3.0]);
1183    }
1184
1185    #[test]
1186    fn index_array_constant_zero_row_returns_entire_column() {
1187        // INDEX({1,2;3,4},0,2) over an array constant -> the whole column {2;4}.
1188        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(IndexFn));
1189
1190        let raw = evaluate_formula("=INDEX({1,2;3,4},0,2)", &wb).unwrap();
1191        let LiteralValue::Array(rows) = raw else {
1192            panic!("expected a 2x1 array, got {raw:?}");
1193        };
1194        let flat: Vec<f64> = rows.iter().map(|r| as_number(&r[0])).collect();
1195        assert_eq!(flat, vec![2.0, 4.0]);
1196    }
1197
1198    #[test]
1199    fn index_array_constant_zero_zero_returns_whole_array() {
1200        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(IndexFn));
1201
1202        let raw = evaluate_formula("=INDEX({1,2;3,4},0,0)", &wb).unwrap();
1203        let LiteralValue::Array(rows) = raw else {
1204            panic!("expected the whole 2x2 array, got {raw:?}");
1205        };
1206        let flat: Vec<f64> = rows.iter().flatten().map(as_number).collect();
1207        assert_eq!(flat, vec![1.0, 2.0, 3.0, 4.0]);
1208    }
1209
1210    #[test]
1211    fn index_array_constant_row_zero_for_single_row_degenerates_to_scalar() {
1212        // INDEX({1,2,3},0,2): single-row array, entire column 2 -> scalar 2.
1213        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(IndexFn));
1214
1215        let value = evaluate_formula("=INDEX({1,2,3},0,2)", &wb).unwrap();
1216        assert_eq!(as_number(&value), 2.0);
1217    }
1218
1219    #[test]
1220    fn index_array_constant_negative_is_ref() {
1221        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(IndexFn));
1222
1223        let value = evaluate_formula("=INDEX({1,2,3},-1)", &wb).unwrap();
1224        match value {
1225            LiteralValue::Error(err) => assert_eq!(err.kind, ExcelErrorKind::Ref),
1226            other => panic!("expected #REF!, got {other:?}"),
1227        }
1228    }
1229
1230    #[test]
1231    fn offset_returns_reference_and_materializes() {
1232        let wb = TestWorkbook::new()
1233            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
1234            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(5))
1235            .with_function(std::sync::Arc::new(OffsetFn));
1236        let ctx = interp(&wb);
1237
1238        let base = ASTNode::new(
1239            ASTNodeType::Reference {
1240                original: "A1".into(),
1241                reference: ReferenceType::Cell {
1242                    sheet: None,
1243                    row: 1,
1244                    col: 1,
1245                    row_abs: false,
1246                    col_abs: false,
1247                },
1248            },
1249            None,
1250        );
1251        let dr = ASTNode::new(ASTNodeType::Literal(LiteralValue::Int(1)), None);
1252        let dc = ASTNode::new(ASTNodeType::Literal(LiteralValue::Int(1)), None);
1253        let call = ASTNode::new(
1254            ASTNodeType::Function {
1255                name: "OFFSET".into(),
1256                args: vec![base.clone(), dr.clone(), dc.clone()],
1257            },
1258            None,
1259        );
1260
1261        let r = ctx.evaluate_ast_as_reference(&call).expect("ref ok");
1262        match r {
1263            ReferenceType::Cell { row, col, .. } => assert_eq!((row, col), (2, 2)),
1264            _ => panic!(),
1265        }
1266
1267        let args = vec![
1268            ArgumentHandle::new(&base, &ctx),
1269            ArgumentHandle::new(&dr, &ctx),
1270            ArgumentHandle::new(&dc, &ctx),
1271        ];
1272        let f = ctx.context.get_function("", "OFFSET").unwrap();
1273        let v = f
1274            .dispatch(&args, &ctx.function_context(None))
1275            .unwrap()
1276            .into_literal();
1277        assert_eq!(v, LiteralValue::Number(5.0));
1278    }
1279}
formualizer_eval/builtins/reference_fns.rs

formualizer_eval/builtins/
reference_fns.rs
