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

formualizer_eval/builtins/
reference_fns.rs
