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#[derive(Debug)]
88pub struct IndexFn;
89
90/// Returns the value or reference at a 1-based row and column within an array or range.
91///
92/// `INDEX` can operate on both references and array literals. When the first argument is
93/// a reference, this implementation resolves a referenced cell and materializes its value in
94/// value context.
95///
96/// # Remarks
97/// - Indexing is 1-based for both `row_num` and `column_num`.
98/// - If `column_num` is omitted for a single-row or single-column input, `row_num` selects the
99///   position along that 1D vector.
100/// - For rectangular 2D inputs, omitted `column_num` defaults to the first column.
101/// - `row_num <= 0`, `column_num <= 0`, or out-of-bounds indexes return `#REF!`.
102/// - Non-numeric index arguments return `#VALUE!`.
103///
104/// # Examples
105/// ```yaml,sandbox
106/// title: "Pick a value from a 2D table"
107/// grid:
108///   A1: "Item"
109///   B1: "Price"
110///   A2: "Pen"
111///   B2: 2.5
112///   A3: "Book"
113///   B3: 8
114/// formula: '=INDEX(A1:B3,3,2)'
115/// expected: 8
116/// ```
117///
118/// ```yaml,sandbox
119/// title: "Index into a 1D vector"
120/// grid:
121///   A1: "Q1"
122///   A2: "Q2"
123///   A3: "Q3"
124/// formula: '=INDEX(A1:A3,2)'
125/// expected: "Q2"
126/// ```
127///
128/// ```yaml,docs
129/// related:
130///   - MATCH
131///   - XLOOKUP
132///   - OFFSET
133/// faq:
134///   - q: "How does INDEX behave when column_num is omitted?"
135///     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."
136///   - q: "Which errors indicate bad indexes?"
137///     a: "Non-numeric index arguments return #VALUE!, while 0/negative or out-of-bounds indexes return #REF!."
138/// ```
139/// [formualizer-docgen:schema:start]
140/// Name: INDEX
141/// Type: IndexFn
142/// Min args: 2
143/// Max args: 3
144/// Variadic: false
145/// Signature: INDEX(arg1: any@range, arg2: number@scalar, arg3?: number@scalar)
146/// 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}
147/// Caps: PURE, RETURNS_REFERENCE
148/// [formualizer-docgen:schema:end]
149impl Function for IndexFn {
150    fn caps(&self) -> FnCaps {
151        FnCaps::PURE | FnCaps::RETURNS_REFERENCE
152    }
153    fn name(&self) -> &'static str {
154        "INDEX"
155    }
156    fn min_args(&self) -> usize {
157        2
158    }
159    fn arg_schema(&self) -> &'static [ArgSchema] {
160        use once_cell::sync::Lazy;
161        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(arg_byref_array);
162        &SCHEMA
163    }
164
165    fn eval_reference<'a, 'b, 'c>(
166        &self,
167        args: &'c [ArgumentHandle<'a, 'b>],
168        _ctx: &dyn FunctionContext<'b>,
169    ) -> Option<Result<ReferenceType, ExcelError>> {
170        // args: array(by_ref), row, col (col optional for 1D)
171        if args.len() < 2 {
172            return Some(Err(ExcelError::new(ExcelErrorKind::Value)));
173        }
174        // Return None for array literals so eval() handles them
175        let base = match args[0].as_reference_or_eval() {
176            Ok(r) => r,
177            Err(_) => return None,
178        };
179        let position = match args[1].value() {
180            Ok(cv) => match cv.into_literal() {
181                LiteralValue::Number(n) => n as i64,
182                LiteralValue::Int(i) => i,
183                _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
184            },
185            Err(e) => return Some(Err(e)),
186        };
187        let explicit_col = if args.len() >= 3 {
188            Some(match args[2].value() {
189                Ok(cv) => match cv.into_literal() {
190                    LiteralValue::Number(n) => n as i64,
191                    LiteralValue::Int(i) => i,
192                    _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
193                },
194                Err(e) => return Some(Err(e)),
195            })
196        } else {
197            None
198        };
199
200        // Only Range supported for now
201        let (sheet, sr, sc, er, ec) = match base {
202            ReferenceType::Range {
203                sheet,
204                start_row,
205                start_col,
206                end_row,
207                end_col,
208                ..
209            } => match (start_row, start_col, end_row, end_col) {
210                (Some(sr), Some(sc), Some(er), Some(ec)) => (sheet, sr, sc, er, ec),
211                _ => return Some(Err(ExcelError::new(ExcelErrorKind::Ref))),
212            },
213            ReferenceType::Cell {
214                sheet, row, col, ..
215            } => (sheet, row, col, row, col),
216            _ => return Some(Err(ExcelError::new(ExcelErrorKind::Ref))),
217        };
218
219        let (row, col) = match explicit_col {
220            Some(col) => (position, col),
221            None if sr == er => {
222                // Excel treats INDEX(single_row_range, n) as horizontal indexing.
223                (1, position)
224            }
225            None => {
226                // Excel treats INDEX(single_col_range, n) as vertical indexing and defaults
227                // 2-D ranges to the first column when column_num is omitted.
228                (position, 1)
229            }
230        };
231
232        // 1-based indexing per Excel
233        if row <= 0 || col <= 0 {
234            return Some(Err(ExcelError::new(ExcelErrorKind::Ref)));
235        }
236        let r = sr + (row as u32) - 1;
237        let c = sc + (col as u32) - 1;
238        if r > er || c > ec {
239            return Some(Err(ExcelError::new(ExcelErrorKind::Ref)));
240        }
241
242        Some(Ok(ReferenceType::cell(sheet, r, c)))
243    }
244
245    fn eval<'a, 'b, 'c>(
246        &self,
247        args: &'c [ArgumentHandle<'a, 'b>],
248        ctx: &dyn FunctionContext<'b>,
249    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
250        // First try to handle as a reference
251        if let Some(result) = self.eval_reference(args, ctx) {
252            match result {
253                Ok(r) => {
254                    // Materialize to value
255                    let current_sheet = ctx.current_sheet();
256                    match ctx.resolve_range_view(&r, current_sheet) {
257                        Ok(rv) => {
258                            let (rows, cols) = rv.dims();
259                            if rows == 1 && cols == 1 {
260                                Ok(crate::traits::CalcValue::Scalar(
261                                    rv.as_1x1().unwrap_or(LiteralValue::Empty),
262                                ))
263                            } else {
264                                Ok(crate::traits::CalcValue::Range(rv))
265                            }
266                        }
267                        Err(e) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
268                    }
269                }
270                Err(e) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
271            }
272        } else {
273            // Handle array literal
274            if args.len() < 2 {
275                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
276                    ExcelError::new(ExcelErrorKind::Value),
277                )));
278            }
279            let v = args[0].value()?.into_literal();
280            let table: Vec<Vec<LiteralValue>> = match v {
281                LiteralValue::Array(rows) => rows,
282                other => vec![vec![other]],
283            };
284            let index = match args[1].value()?.into_literal() {
285                LiteralValue::Number(n) => n as i64,
286                LiteralValue::Int(i) => i,
287                _ => {
288                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
289                        ExcelError::new(ExcelErrorKind::Value),
290                    )));
291                }
292            };
293
294            // Determine if this is a 1D array (single row or single column)
295            let is_single_row = table.len() == 1;
296            let is_single_col = table.iter().all(|r| r.len() == 1);
297
298            // For 1D arrays with 2 args, index is position in the array
299            if args.len() == 2 && (is_single_row || is_single_col) {
300                if index <= 0 {
301                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
302                        ExcelError::new(ExcelErrorKind::Ref),
303                    )));
304                }
305                let idx = (index - 1) as usize;
306                let val = if is_single_row {
307                    table[0].get(idx).cloned()
308                } else {
309                    table.get(idx).and_then(|r| r.first()).cloned()
310                };
311                return Ok(crate::traits::CalcValue::Scalar(val.unwrap_or_else(|| {
312                    LiteralValue::Error(ExcelError::new(ExcelErrorKind::Ref))
313                })));
314            }
315
316            // 2D array or 3 arguments: use row and col indexing
317            let row = index as usize;
318            let col = if args.len() >= 3 {
319                match args[2].value()?.into_literal() {
320                    LiteralValue::Number(n) => n as usize,
321                    LiteralValue::Int(i) => i as usize,
322                    _ => {
323                        return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
324                            ExcelError::new(ExcelErrorKind::Value),
325                        )));
326                    }
327                }
328            } else {
329                1
330            };
331
332            // 1-based indexing
333            if row == 0 || col == 0 {
334                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
335                    ExcelError::new(ExcelErrorKind::Ref),
336                )));
337            }
338            let val = table
339                .get(row - 1)
340                .and_then(|r| r.get(col - 1))
341                .cloned()
342                .unwrap_or_else(|| LiteralValue::Error(ExcelError::new(ExcelErrorKind::Ref)));
343            Ok(crate::traits::CalcValue::Scalar(val))
344        }
345    }
346}
347
348#[derive(Debug)]
349pub struct OffsetFn;
350
351/// Returns a reference shifted from a starting reference by rows and columns.
352///
353/// `OFFSET` is volatile and returns a reference that can point to a single cell or a resized
354/// range, depending on the optional `height` and `width` arguments.
355///
356/// # Remarks
357/// - `rows` and `cols` shift from the top-left of `reference`.
358/// - If omitted, `height` and `width` default to the original reference size.
359/// - Non-positive target coordinates or dimensions return `#REF!`.
360/// - Non-numeric offset/size inputs return `#VALUE!`.
361/// - In value context, a 1x1 result returns a scalar; larger results spill as an array.
362///
363/// # Examples
364/// ```yaml,sandbox
365/// title: "Move one row down and one column right"
366/// grid:
367///   A1: 10
368///   B2: 42
369/// formula: '=OFFSET(A1,1,1)'
370/// expected: 42
371/// ```
372///
373/// ```yaml,sandbox
374/// title: "Offset and resize a range"
375/// grid:
376///   A1: 1
377///   A2: 2
378///   A3: 3
379///   B1: 4
380///   B2: 5
381///   B3: 6
382/// formula: '=SUM(OFFSET(A1,1,0,2,2))'
383/// expected: 16
384/// ```
385///
386/// ```yaml,docs
387/// related:
388///   - INDEX
389///   - INDIRECT
390///   - ADDRESS
391/// faq:
392///   - q: "What defaults are used when height and width are omitted?"
393///     a: "OFFSET keeps the source reference size, then applies the row/column shift to that same-sized block."
394///   - q: "When does OFFSET return #REF!?"
395///     a: "It returns #REF! if the shifted start goes to row/column <= 0 or if requested height/width are non-positive."
396/// ```
397/// [formualizer-docgen:schema:start]
398/// Name: OFFSET
399/// Type: OffsetFn
400/// Min args: 3
401/// Max args: 5
402/// Variadic: false
403/// Signature: OFFSET(arg1: range@range, arg2: number@scalar, arg3: number@scalar, arg4?: number@scalar, arg5?: number@scalar)
404/// 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}
405/// Caps: PURE, VOLATILE, RETURNS_REFERENCE, DYNAMIC_DEPENDENCY
406/// [formualizer-docgen:schema:end]
407impl Function for OffsetFn {
408    fn caps(&self) -> FnCaps {
409        // OFFSET is volatile in Excel semantics and has runtime-dynamic dependencies.
410        FnCaps::PURE | FnCaps::RETURNS_REFERENCE | FnCaps::VOLATILE | FnCaps::DYNAMIC_DEPENDENCY
411    }
412    fn name(&self) -> &'static str {
413        "OFFSET"
414    }
415    fn min_args(&self) -> usize {
416        3
417    }
418    fn arg_schema(&self) -> &'static [ArgSchema] {
419        use once_cell::sync::Lazy;
420        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(arg_byref_reference);
421        &SCHEMA
422    }
423
424    fn eval_reference<'a, 'b, 'c>(
425        &self,
426        args: &'c [ArgumentHandle<'a, 'b>],
427        _ctx: &dyn FunctionContext<'b>,
428    ) -> Option<Result<ReferenceType, ExcelError>> {
429        if args.len() < 3 {
430            return Some(Err(ExcelError::new(ExcelErrorKind::Value)));
431        }
432        let base = match args[0].as_reference_or_eval() {
433            Ok(r) => r,
434            Err(e) => return Some(Err(e)),
435        };
436        let dr = match args[1].value() {
437            Ok(cv) => match cv.into_literal() {
438                LiteralValue::Number(n) => n as i64,
439                LiteralValue::Int(i) => i,
440                _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
441            },
442            Err(e) => return Some(Err(e)),
443        };
444        let dc = match args[2].value() {
445            Ok(cv) => match cv.into_literal() {
446                LiteralValue::Number(n) => n as i64,
447                LiteralValue::Int(i) => i,
448                _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
449            },
450            Err(e) => return Some(Err(e)),
451        };
452
453        let (sheet, sr, sc, er, ec) = match base {
454            ReferenceType::Range {
455                sheet,
456                start_row,
457                start_col,
458                end_row,
459                end_col,
460                ..
461            } => match (start_row, start_col, end_row, end_col) {
462                (Some(sr), Some(sc), Some(er), Some(ec)) => (sheet, sr, sc, er, ec),
463                _ => return Some(Err(ExcelError::new(ExcelErrorKind::Ref))),
464            },
465            ReferenceType::Cell {
466                sheet, row, col, ..
467            } => (sheet, row, col, row, col),
468            _ => return Some(Err(ExcelError::new(ExcelErrorKind::Ref))),
469        };
470
471        let nsr = (sr as i64) + dr;
472        let nsc = (sc as i64) + dc;
473        let height = if args.len() >= 4 {
474            match args[3].value() {
475                Ok(cv) => match cv.into_literal() {
476                    LiteralValue::Number(n) => n as i64,
477                    LiteralValue::Int(i) => i,
478                    _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
479                },
480                Err(e) => return Some(Err(e)),
481            }
482        } else {
483            (er as i64) - (sr as i64) + 1
484        };
485        let width = if args.len() >= 5 {
486            match args[4].value() {
487                Ok(cv) => match cv.into_literal() {
488                    LiteralValue::Number(n) => n as i64,
489                    LiteralValue::Int(i) => i,
490                    _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
491                },
492                Err(e) => return Some(Err(e)),
493            }
494        } else {
495            (ec as i64) - (sc as i64) + 1
496        };
497
498        if nsr <= 0 || nsc <= 0 || height <= 0 || width <= 0 {
499            return Some(Err(ExcelError::new(ExcelErrorKind::Ref)));
500        }
501        let ner = nsr + height - 1;
502        let nec = nsc + width - 1;
503
504        if height == 1 && width == 1 {
505            Some(Ok(ReferenceType::cell(sheet, nsr as u32, nsc as u32)))
506        } else {
507            Some(Ok(ReferenceType::range(
508                sheet,
509                Some(nsr as u32),
510                Some(nsc as u32),
511                Some(ner as u32),
512                Some(nec as u32),
513            )))
514        }
515    }
516
517    fn eval<'a, 'b, 'c>(
518        &self,
519        args: &'c [ArgumentHandle<'a, 'b>],
520        ctx: &dyn FunctionContext<'b>,
521    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
522        if let Some(Ok(r)) = self.eval_reference(args, ctx) {
523            let current_sheet = ctx.current_sheet();
524            match ctx.resolve_range_view(&r, current_sheet) {
525                Ok(rv) => {
526                    let (rows, cols) = rv.dims();
527                    if rows == 1 && cols == 1 {
528                        Ok(crate::traits::CalcValue::Scalar(
529                            rv.as_1x1().unwrap_or(LiteralValue::Empty),
530                        ))
531                    } else {
532                        Ok(crate::traits::CalcValue::Range(rv))
533                    }
534                }
535                Err(e) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
536            }
537        } else {
538            Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
539                ExcelError::new(ExcelErrorKind::Ref),
540            )))
541        }
542    }
543}
544
545fn arg_indirect() -> Vec<ArgSchema> {
546    vec![
547        ArgSchema {
548            kinds: smallvec::smallvec![ArgKind::Text],
549            required: true,
550            by_ref: false,
551            shape: ShapeKind::Scalar,
552            coercion: CoercionPolicy::None,
553            max: None,
554            repeating: None,
555            default: None,
556        },
557        ArgSchema {
558            kinds: smallvec::smallvec![ArgKind::Logical, ArgKind::Number],
559            required: false,
560            by_ref: false,
561            shape: ShapeKind::Scalar,
562            coercion: CoercionPolicy::Logical,
563            max: None,
564            repeating: None,
565            default: Some(LiteralValue::Boolean(true)),
566        },
567    ]
568}
569
570#[derive(Debug)]
571pub struct IndirectFn;
572
573/// Converts text into a reference and returns the referenced value or range.
574///
575/// `INDIRECT` lets formulas build references dynamically from strings such as `"A1"` or
576/// `"Sheet2!B3:C5"`.
577///
578/// # Remarks
579/// - `a1_style` defaults to `TRUE` (A1 style parsing).
580/// - `a1_style=FALSE` (R1C1 parsing) is currently not implemented and returns `#N/IMPL!`.
581/// - Invalid or unresolved references return `#REF!`.
582/// - The function is volatile because target references can change without direct dependency links.
583///
584/// # Examples
585/// ```yaml,sandbox
586/// title: "Resolve a direct cell reference"
587/// grid:
588///   A1: 99
589/// formula: '=INDIRECT("A1")'
590/// expected: 99
591/// ```
592///
593/// ```yaml,sandbox
594/// title: "Resolve a range and aggregate it"
595/// grid:
596///   A1: 5
597///   A2: 7
598///   A3: 9
599/// formula: '=SUM(INDIRECT("A1:A3"))'
600/// expected: 21
601/// ```
602///
603/// ```yaml,docs
604/// related:
605///   - ADDRESS
606///   - INDEX
607///   - OFFSET
608/// faq:
609///   - q: "What happens if a1_style is FALSE?"
610///     a: "R1C1 parsing is not implemented here yet, so INDIRECT(...,FALSE) returns #N/IMPL!."
611///   - q: "How are bad reference strings reported?"
612///     a: "If the text cannot be parsed or resolved to a valid reference, INDIRECT returns #REF!."
613/// ```
614/// [formualizer-docgen:schema:start]
615/// Name: INDIRECT
616/// Type: IndirectFn
617/// Min args: 1
618/// Max args: 2
619/// Variadic: false
620/// Signature: INDIRECT(arg1: text@scalar, arg2?: logical|number@scalar)
621/// 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}
622/// Caps: PURE, VOLATILE, RETURNS_REFERENCE, DYNAMIC_DEPENDENCY
623/// [formualizer-docgen:schema:end]
624impl Function for IndirectFn {
625    fn caps(&self) -> FnCaps {
626        FnCaps::PURE | FnCaps::RETURNS_REFERENCE | FnCaps::VOLATILE | FnCaps::DYNAMIC_DEPENDENCY
627    }
628    fn name(&self) -> &'static str {
629        "INDIRECT"
630    }
631    fn min_args(&self) -> usize {
632        1
633    }
634    fn arg_schema(&self) -> &'static [ArgSchema] {
635        use once_cell::sync::Lazy;
636        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(arg_indirect);
637        &SCHEMA
638    }
639
640    fn eval_reference<'a, 'b, 'c>(
641        &self,
642        args: &'c [ArgumentHandle<'a, 'b>],
643        _ctx: &dyn FunctionContext<'b>,
644    ) -> Option<Result<ReferenceType, ExcelError>> {
645        if args.is_empty() {
646            return Some(Err(ExcelError::new(ExcelErrorKind::Value)));
647        }
648
649        let ref_text = match args[0].value() {
650            Ok(cv) => match cv.into_literal() {
651                LiteralValue::Text(s) => s.to_string(),
652                _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
653            },
654            Err(e) => return Some(Err(e)),
655        };
656
657        let a1_style = if args.len() >= 2 {
658            match args[1].value() {
659                Ok(cv) => match cv.into_literal() {
660                    LiteralValue::Boolean(b) => b,
661                    LiteralValue::Int(i) => i != 0,
662                    LiteralValue::Number(n) => n != 0.0,
663                    _ => return Some(Err(ExcelError::new(ExcelErrorKind::Value))),
664                },
665                Err(e) => return Some(Err(e)),
666            }
667        } else {
668            true
669        };
670
671        if !a1_style {
672            return Some(Err(ExcelError::new(ExcelErrorKind::NImpl).with_message(
673                "INDIRECT with R1C1 style (second argument FALSE) is not yet supported",
674            )));
675        }
676
677        let parsed = formualizer_parse::parser::ReferenceType::parse_sheet_ref(&ref_text);
678
679        match parsed {
680            Ok(formualizer_common::SheetRef::Cell(cell)) => {
681                let sheet = match cell.sheet {
682                    formualizer_common::SheetLocator::Current => None,
683                    formualizer_common::SheetLocator::Name(name) => Some(name.to_string()),
684                    formualizer_common::SheetLocator::Id(_) => None,
685                };
686                Some(Ok(ReferenceType::Cell {
687                    sheet,
688                    row: cell.coord.row() + 1,
689                    col: cell.coord.col() + 1,
690                    row_abs: cell.coord.row_abs(),
691                    col_abs: cell.coord.col_abs(),
692                }))
693            }
694            Ok(formualizer_common::SheetRef::Range(range)) => {
695                let sheet = match range.sheet {
696                    formualizer_common::SheetLocator::Current => None,
697                    formualizer_common::SheetLocator::Name(name) => Some(name.to_string()),
698                    formualizer_common::SheetLocator::Id(_) => None,
699                };
700                Some(Ok(ReferenceType::Range {
701                    sheet,
702                    start_row: range.start_row.map(|b| b.index + 1),
703                    start_col: range.start_col.map(|b| b.index + 1),
704                    end_row: range.end_row.map(|b| b.index + 1),
705                    end_col: range.end_col.map(|b| b.index + 1),
706                    start_row_abs: range.start_row.map(|b| b.abs).unwrap_or(false),
707                    start_col_abs: range.start_col.map(|b| b.abs).unwrap_or(false),
708                    end_row_abs: range.end_row.map(|b| b.abs).unwrap_or(false),
709                    end_col_abs: range.end_col.map(|b| b.abs).unwrap_or(false),
710                }))
711            }
712            Err(_) => match formualizer_parse::parser::ReferenceType::from_string(&ref_text) {
713                Ok(ReferenceType::NamedRange(name)) => Some(Ok(ReferenceType::NamedRange(name))),
714                Ok(ReferenceType::Table(tref)) => Some(Ok(ReferenceType::Table(tref))),
715                _ => Some(Err(ExcelError::new(ExcelErrorKind::Ref))),
716            },
717        }
718    }
719
720    fn eval<'a, 'b, 'c>(
721        &self,
722        args: &'c [ArgumentHandle<'a, 'b>],
723        ctx: &dyn FunctionContext<'b>,
724    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
725        match self.eval_reference(args, ctx) {
726            Some(Ok(r)) => {
727                let current_sheet = ctx.current_sheet();
728                match ctx.resolve_range_view(&r, current_sheet) {
729                    Ok(rv) => {
730                        let (rows, cols) = rv.dims();
731                        if rows == 1 && cols == 1 {
732                            Ok(crate::traits::CalcValue::Scalar(
733                                rv.as_1x1().unwrap_or(LiteralValue::Empty),
734                            ))
735                        } else {
736                            Ok(crate::traits::CalcValue::Range(rv))
737                        }
738                    }
739                    Err(e) => {
740                        let mapped = if e.kind == ExcelErrorKind::Name {
741                            ExcelError::new(ExcelErrorKind::Ref)
742                        } else {
743                            e
744                        };
745                        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
746                            mapped,
747                        )))
748                    }
749                }
750            }
751            Some(Err(e)) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
752            None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
753                ExcelError::new(ExcelErrorKind::Ref),
754            ))),
755        }
756    }
757}
758
759pub fn register_builtins() {
760    crate::function_registry::register_function(std::sync::Arc::new(IndexFn));
761    crate::function_registry::register_function(std::sync::Arc::new(OffsetFn));
762    crate::function_registry::register_function(std::sync::Arc::new(IndirectFn));
763}
764
765#[cfg(test)]
766mod tests {
767    use super::*;
768    use crate::builtins::lookup::MatchFn;
769    use crate::test_workbook::TestWorkbook;
770    use crate::traits::ArgumentHandle;
771    use formualizer_common::error::{ExcelError, ExcelErrorKind};
772    use formualizer_parse::parser::{ASTNode, ASTNodeType, Parser};
773
774    fn interp(wb: &TestWorkbook) -> crate::interpreter::Interpreter<'_> {
775        wb.interpreter()
776    }
777
778    fn evaluate_formula(formula: &str, wb: &TestWorkbook) -> Result<LiteralValue, ExcelError> {
779        let mut parser = Parser::new(formula).unwrap();
780        let ast = parser
781            .parse()
782            .map_err(|e| ExcelError::new(ExcelErrorKind::Error).with_message(e.message.clone()))?;
783        Ok(interp(wb).evaluate_ast(&ast)?.into_literal())
784    }
785
786    #[test]
787    fn index_returns_reference_and_materializes_in_value_context() {
788        let wb = TestWorkbook::new()
789            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(42))
790            .with_function(std::sync::Arc::new(IndexFn));
791        let ctx = interp(&wb);
792
793        // Build INDEX(A1:C3,2,2) expecting B2
794        let array_ref = ASTNode::new(
795            ASTNodeType::Reference {
796                original: "A1:C3".into(),
797                reference: ReferenceType::Range {
798                    sheet: None,
799                    start_row: Some(1),
800                    start_col: Some(1),
801                    end_row: Some(3),
802                    end_col: Some(3),
803                    start_row_abs: false,
804                    start_col_abs: false,
805                    end_row_abs: false,
806                    end_col_abs: false,
807                },
808            },
809            None,
810        );
811        let row = ASTNode::new(ASTNodeType::Literal(LiteralValue::Int(2)), None);
812        let col = ASTNode::new(ASTNodeType::Literal(LiteralValue::Int(2)), None);
813        let call = ASTNode::new(
814            ASTNodeType::Function {
815                name: "INDEX".into(),
816                args: vec![array_ref.clone(), row.clone(), col.clone()],
817            },
818            None,
819        );
820
821        // Reference context
822        let r = ctx.evaluate_ast_as_reference(&call).expect("ref ok");
823        match r {
824            ReferenceType::Cell { row, col, .. } => {
825                assert_eq!((row, col), (2, 2));
826            }
827            _ => panic!(),
828        }
829
830        // Value context (scalar materialization)
831        let args = vec![
832            ArgumentHandle::new(&array_ref, &ctx),
833            ArgumentHandle::new(&row, &ctx),
834            ArgumentHandle::new(&col, &ctx),
835        ];
836        let f = ctx.context.get_function("", "INDEX").unwrap();
837        let v = f
838            .dispatch(&args, &ctx.function_context(None))
839            .unwrap()
840            .into_literal();
841        assert_eq!(v, LiteralValue::Number(42.0));
842    }
843
844    #[test]
845    fn index_single_row_reference_uses_omitted_col_as_horizontal_position() {
846        let wb = TestWorkbook::new()
847            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
848            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(20))
849            .with_cell_a1("Sheet1", "C1", LiteralValue::Int(30))
850            .with_function(std::sync::Arc::new(IndexFn));
851        let ctx = interp(&wb);
852
853        let array_ref = ASTNode::new(
854            ASTNodeType::Reference {
855                original: "A1:C1".into(),
856                reference: ReferenceType::Range {
857                    sheet: None,
858                    start_row: Some(1),
859                    start_col: Some(1),
860                    end_row: Some(1),
861                    end_col: Some(3),
862                    start_row_abs: false,
863                    start_col_abs: false,
864                    end_row_abs: false,
865                    end_col_abs: false,
866                },
867            },
868            None,
869        );
870        let index = ASTNode::new(ASTNodeType::Literal(LiteralValue::Int(2)), None);
871        let call = ASTNode::new(
872            ASTNodeType::Function {
873                name: "INDEX".into(),
874                args: vec![array_ref.clone(), index.clone()],
875            },
876            None,
877        );
878
879        let r = ctx.evaluate_ast_as_reference(&call).expect("ref ok");
880        match r {
881            ReferenceType::Cell { row, col, .. } => assert_eq!((row, col), (1, 2)),
882            _ => panic!(),
883        }
884
885        let args = vec![
886            ArgumentHandle::new(&array_ref, &ctx),
887            ArgumentHandle::new(&index, &ctx),
888        ];
889        let f = ctx.context.get_function("", "INDEX").unwrap();
890        let v = f
891            .dispatch(&args, &ctx.function_context(None))
892            .unwrap()
893            .into_literal();
894        assert_eq!(v, LiteralValue::Number(20.0));
895    }
896
897    #[test]
898    fn index_single_column_reference_keeps_omitted_col_as_vertical_position() {
899        let wb = TestWorkbook::new()
900            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
901            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
902            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
903            .with_function(std::sync::Arc::new(IndexFn));
904        let ctx = interp(&wb);
905
906        let array_ref = ASTNode::new(
907            ASTNodeType::Reference {
908                original: "A1:A3".into(),
909                reference: ReferenceType::Range {
910                    sheet: None,
911                    start_row: Some(1),
912                    start_col: Some(1),
913                    end_row: Some(3),
914                    end_col: Some(1),
915                    start_row_abs: false,
916                    start_col_abs: false,
917                    end_row_abs: false,
918                    end_col_abs: false,
919                },
920            },
921            None,
922        );
923        let index = ASTNode::new(ASTNodeType::Literal(LiteralValue::Int(2)), None);
924        let args = vec![
925            ArgumentHandle::new(&array_ref, &ctx),
926            ArgumentHandle::new(&index, &ctx),
927        ];
928        let f = ctx.context.get_function("", "INDEX").unwrap();
929        let v = f
930            .dispatch(&args, &ctx.function_context(None))
931            .unwrap()
932            .into_literal();
933        assert_eq!(v, LiteralValue::Number(20.0));
934    }
935
936    #[test]
937    fn index_rectangular_reference_defaults_omitted_col_to_first_column() {
938        let wb = TestWorkbook::new()
939            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
940            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
941            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(200))
942            .with_function(std::sync::Arc::new(IndexFn));
943
944        let value = evaluate_formula("=INDEX(A1:B2,2)", &wb).unwrap();
945        assert_eq!(value, LiteralValue::Number(20.0));
946    }
947
948    #[test]
949    fn index_single_row_reference_match_position_materializes_value() {
950        let wb = TestWorkbook::new()
951            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
952            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(20))
953            .with_cell_a1("Sheet1", "C1", LiteralValue::Int(30))
954            .with_function(std::sync::Arc::new(IndexFn))
955            .with_function(std::sync::Arc::new(MatchFn));
956
957        let value = evaluate_formula("=INDEX(A1:C1,MATCH(20,A1:C1,0))", &wb).unwrap();
958        assert_eq!(value, LiteralValue::Number(20.0));
959    }
960
961    #[test]
962    fn index_single_row_reference_out_of_bounds_is_ref() {
963        let wb = TestWorkbook::new()
964            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
965            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(20))
966            .with_cell_a1("Sheet1", "C1", LiteralValue::Int(30))
967            .with_function(std::sync::Arc::new(IndexFn));
968
969        let value = evaluate_formula("=INDEX(A1:C1,4)", &wb).unwrap();
970        match value {
971            LiteralValue::Error(err) => assert_eq!(err.kind, ExcelErrorKind::Ref),
972            other => panic!("expected #REF!, got {other:?}"),
973        }
974    }
975
976    #[test]
977    fn offset_returns_reference_and_materializes() {
978        let wb = TestWorkbook::new()
979            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
980            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(5))
981            .with_function(std::sync::Arc::new(OffsetFn));
982        let ctx = interp(&wb);
983
984        let base = ASTNode::new(
985            ASTNodeType::Reference {
986                original: "A1".into(),
987                reference: ReferenceType::Cell {
988                    sheet: None,
989                    row: 1,
990                    col: 1,
991                    row_abs: false,
992                    col_abs: false,
993                },
994            },
995            None,
996        );
997        let dr = ASTNode::new(ASTNodeType::Literal(LiteralValue::Int(1)), None);
998        let dc = ASTNode::new(ASTNodeType::Literal(LiteralValue::Int(1)), None);
999        let call = ASTNode::new(
1000            ASTNodeType::Function {
1001                name: "OFFSET".into(),
1002                args: vec![base.clone(), dr.clone(), dc.clone()],
1003            },
1004            None,
1005        );
1006
1007        let r = ctx.evaluate_ast_as_reference(&call).expect("ref ok");
1008        match r {
1009            ReferenceType::Cell { row, col, .. } => assert_eq!((row, col), (2, 2)),
1010            _ => panic!(),
1011        }
1012
1013        let args = vec![
1014            ArgumentHandle::new(&base, &ctx),
1015            ArgumentHandle::new(&dr, &ctx),
1016            ArgumentHandle::new(&dc, &ctx),
1017        ];
1018        let f = ctx.context.get_function("", "OFFSET").unwrap();
1019        let v = f
1020            .dispatch(&args, &ctx.function_context(None))
1021            .unwrap()
1022            .into_literal();
1023        assert_eq!(v, LiteralValue::Number(5.0));
1024    }
1025}
formualizer_eval/builtins/reference_fns.rs

formualizer_eval/builtins/
reference_fns.rs
