formualizer_eval/builtins/lookup/
address.rs

1//! ADDRESS function - creates a cell reference as text
2//!
3//! Excel semantics:
4//! - ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
5//! - abs_num: 1 = absolute ($A$1), 2 = abs row (A$1), 3 = abs col ($A1), 4 = relative (A1)
6//! - a1: TRUE = A1 style, FALSE = R1C1 style
7//! - sheet_text: optional sheet name to include
8
9use crate::args::{ArgSchema, CoercionPolicy, ShapeKind};
10use crate::function::Function;
11use crate::traits::{ArgumentHandle, FunctionContext};
12use formualizer_common::{ArgKind, ExcelError, ExcelErrorKind, LiteralValue};
13use formualizer_macros::func_caps;
14
15/// Convert a column number to Excel letter notation (1 = A, 27 = AA, etc.)
16fn column_to_letters(mut col: u32) -> String {
17    if col == 0 {
18        return String::new();
19    }
20
21    let mut result = String::new();
22    while col > 0 {
23        col -= 1;
24        let remainder = col % 26;
25        result.push((b'A' + remainder as u8) as char);
26        col /= 26;
27    }
28    result.chars().rev().collect()
29}
30
31#[derive(Debug)]
32pub struct AddressFn;
33
34impl Function for AddressFn {
35    fn name(&self) -> &'static str {
36        "ADDRESS"
37    }
38
39    fn min_args(&self) -> usize {
40        2
41    }
42
43    func_caps!(PURE);
44
45    fn arg_schema(&self) -> &'static [ArgSchema] {
46        use once_cell::sync::Lazy;
47        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
48            vec![
49                // row_num (required, strict number)
50                ArgSchema {
51                    kinds: smallvec::smallvec![ArgKind::Number],
52                    required: true,
53                    by_ref: false,
54                    shape: ShapeKind::Scalar,
55                    coercion: CoercionPolicy::NumberStrict,
56                    max: None,
57                    repeating: None,
58                    default: None,
59                },
60                // column_num (required, strict number)
61                ArgSchema {
62                    kinds: smallvec::smallvec![ArgKind::Number],
63                    required: true,
64                    by_ref: false,
65                    shape: ShapeKind::Scalar,
66                    coercion: CoercionPolicy::NumberStrict,
67                    max: None,
68                    repeating: None,
69                    default: None,
70                },
71                // abs_num (optional, default 1)
72                ArgSchema {
73                    kinds: smallvec::smallvec![ArgKind::Number],
74                    required: false,
75                    by_ref: false,
76                    shape: ShapeKind::Scalar,
77                    coercion: CoercionPolicy::NumberStrict,
78                    max: None,
79                    repeating: None,
80                    default: Some(LiteralValue::Int(1)),
81                },
82                // a1 (optional, default TRUE)
83                ArgSchema {
84                    kinds: smallvec::smallvec![ArgKind::Logical],
85                    required: false,
86                    by_ref: false,
87                    shape: ShapeKind::Scalar,
88                    coercion: CoercionPolicy::Logical,
89                    max: None,
90                    repeating: None,
91                    default: Some(LiteralValue::Boolean(true)),
92                },
93                // sheet_text (optional)
94                ArgSchema {
95                    kinds: smallvec::smallvec![ArgKind::Text],
96                    required: false,
97                    by_ref: false,
98                    shape: ShapeKind::Scalar,
99                    coercion: CoercionPolicy::None,
100                    max: None,
101                    repeating: None,
102                    default: None,
103                },
104            ]
105        });
106        &SCHEMA
107    }
108
109    fn eval_scalar<'a, 'b>(
110        &self,
111        args: &'a [ArgumentHandle<'a, 'b>],
112        _ctx: &dyn FunctionContext,
113    ) -> Result<LiteralValue, ExcelError> {
114        if args.len() < 2 {
115            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
116        }
117
118        // Get row number
119        let row_val = args[0].value()?;
120        if let LiteralValue::Error(e) = row_val.as_ref() {
121            return Ok(LiteralValue::Error(e.clone()));
122        }
123        let row = match row_val.as_ref() {
124            LiteralValue::Number(n) => *n as i64,
125            LiteralValue::Int(i) => *i,
126            _ => return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value))),
127        };
128
129        if !(1..=1_048_576).contains(&row) {
130            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
131        }
132
133        // Get column number
134        let col_val = args[1].value()?;
135        if let LiteralValue::Error(e) = col_val.as_ref() {
136            return Ok(LiteralValue::Error(e.clone()));
137        }
138        let col = match col_val.as_ref() {
139            LiteralValue::Number(n) => *n as i64,
140            LiteralValue::Int(i) => *i,
141            _ => return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value))),
142        };
143
144        if !(1..=16384).contains(&col) {
145            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
146        }
147
148        // Get abs_num (default 1 = absolute)
149        let abs_num = if args.len() > 2 {
150            let abs_val = args[2].value()?;
151            if let LiteralValue::Error(e) = abs_val.as_ref() {
152                return Ok(LiteralValue::Error(e.clone()));
153            }
154            match abs_val.as_ref() {
155                LiteralValue::Number(n) => *n as i64,
156                LiteralValue::Int(i) => *i,
157                _ => 1,
158            }
159        } else {
160            1
161        };
162
163        if !(1..=4).contains(&abs_num) {
164            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
165        }
166
167        // Get a1 (default TRUE = A1 notation)
168        let a1_style = if args.len() > 3 {
169            let a1_val = args[3].value()?;
170            if let LiteralValue::Error(e) = a1_val.as_ref() {
171                return Ok(LiteralValue::Error(e.clone()));
172            }
173            match a1_val.as_ref() {
174                LiteralValue::Boolean(b) => *b,
175                _ => true,
176            }
177        } else {
178            true
179        };
180
181        // Get sheet name (optional)
182        let sheet_name = if args.len() > 4 {
183            let sheet_val = args[4].value()?;
184            if let LiteralValue::Error(e) = sheet_val.as_ref() {
185                return Ok(LiteralValue::Error(e.clone()));
186            }
187            match sheet_val.as_ref() {
188                LiteralValue::Text(s) => Some(s.clone()),
189                _ => None,
190            }
191        } else {
192            None
193        };
194
195        // Build the address
196        let address = if a1_style {
197            // A1 notation
198            let col_letters = column_to_letters(col as u32);
199            let (col_abs, row_abs) = match abs_num {
200                1 => (true, true),   // $A$1
201                2 => (false, true),  // A$1
202                3 => (true, false),  // $A1
203                4 => (false, false), // A1
204                _ => (true, true),
205            };
206
207            let col_str = if col_abs {
208                format!("${col_letters}")
209            } else {
210                col_letters
211            };
212            let row_str = if row_abs {
213                format!("${row}")
214            } else {
215                row.to_string()
216            };
217            format!("{col_str}{row_str}")
218        } else {
219            // R1C1 notation
220            let (col_abs, row_abs) = match abs_num {
221                1 => (true, true),
222                2 => (false, true),
223                3 => (true, false),
224                4 => (false, false),
225                _ => (true, true),
226            };
227
228            let row_str = if row_abs {
229                format!("R{row}")
230            } else {
231                format!("R[{row}]")
232            };
233            let col_str = if col_abs {
234                format!("C{col}")
235            } else {
236                format!("C[{col}]")
237            };
238            format!("{row_str}{col_str}")
239        };
240
241        // Add sheet name if provided
242        let final_address = if let Some(sheet) = sheet_name {
243            // Quote sheet name if it contains spaces or special characters
244            if sheet.contains(' ') || sheet.contains('!') || sheet.contains('\'') {
245                format!("'{}'!{address}", sheet.replace('\'', "''"))
246            } else {
247                format!("{sheet}!{address}")
248            }
249        } else {
250            address
251        };
252
253        Ok(LiteralValue::Text(final_address))
254    }
255}
256
257#[cfg(test)]
258mod tests {
259    use super::*;
260    use crate::test_workbook::TestWorkbook;
261    use formualizer_parse::parser::{ASTNode, ASTNodeType};
262    use std::sync::Arc;
263
264    fn lit(v: LiteralValue) -> ASTNode {
265        ASTNode::new(ASTNodeType::Literal(v), None)
266    }
267
268    #[test]
269    fn test_column_to_letters() {
270        assert_eq!(column_to_letters(1), "A");
271        assert_eq!(column_to_letters(26), "Z");
272        assert_eq!(column_to_letters(27), "AA");
273        assert_eq!(column_to_letters(52), "AZ");
274        assert_eq!(column_to_letters(53), "BA");
275        assert_eq!(column_to_letters(702), "ZZ");
276        assert_eq!(column_to_letters(703), "AAA");
277    }
278
279    #[test]
280    fn address_basic() {
281        let wb = TestWorkbook::new().with_function(Arc::new(AddressFn));
282        let ctx = wb.interpreter();
283        let f = ctx.context.get_function("", "ADDRESS").unwrap();
284
285        // ADDRESS(2, 3) -> "$C$2" (default absolute)
286        let two = lit(LiteralValue::Int(2));
287        let three = lit(LiteralValue::Int(3));
288
289        let args = vec![
290            ArgumentHandle::new(&two, &ctx),
291            ArgumentHandle::new(&three, &ctx),
292        ];
293
294        let result = f.dispatch(&args, &ctx.function_context(None)).unwrap();
295        assert_eq!(result, LiteralValue::Text("$C$2".into()));
296    }
297
298    #[test]
299    fn address_abs_variations() {
300        let wb = TestWorkbook::new().with_function(Arc::new(AddressFn));
301        let ctx = wb.interpreter();
302        let f = ctx.context.get_function("", "ADDRESS").unwrap();
303
304        let row = lit(LiteralValue::Int(5));
305        let col = lit(LiteralValue::Int(4)); // Column D
306
307        // abs_num = 1: $D$5
308        let abs1 = lit(LiteralValue::Int(1));
309        let args1 = vec![
310            ArgumentHandle::new(&row, &ctx),
311            ArgumentHandle::new(&col, &ctx),
312            ArgumentHandle::new(&abs1, &ctx),
313        ];
314        assert_eq!(
315            f.dispatch(&args1, &ctx.function_context(None)).unwrap(),
316            LiteralValue::Text("$D$5".into())
317        );
318
319        // abs_num = 2: D$5
320        let abs2 = lit(LiteralValue::Int(2));
321        let args2 = vec![
322            ArgumentHandle::new(&row, &ctx),
323            ArgumentHandle::new(&col, &ctx),
324            ArgumentHandle::new(&abs2, &ctx),
325        ];
326        assert_eq!(
327            f.dispatch(&args2, &ctx.function_context(None)).unwrap(),
328            LiteralValue::Text("D$5".into())
329        );
330
331        // abs_num = 3: $D5
332        let abs3 = lit(LiteralValue::Int(3));
333        let args3 = vec![
334            ArgumentHandle::new(&row, &ctx),
335            ArgumentHandle::new(&col, &ctx),
336            ArgumentHandle::new(&abs3, &ctx),
337        ];
338        assert_eq!(
339            f.dispatch(&args3, &ctx.function_context(None)).unwrap(),
340            LiteralValue::Text("$D5".into())
341        );
342
343        // abs_num = 4: D5
344        let abs4 = lit(LiteralValue::Int(4));
345        let args4 = vec![
346            ArgumentHandle::new(&row, &ctx),
347            ArgumentHandle::new(&col, &ctx),
348            ArgumentHandle::new(&abs4, &ctx),
349        ];
350        assert_eq!(
351            f.dispatch(&args4, &ctx.function_context(None)).unwrap(),
352            LiteralValue::Text("D5".into())
353        );
354    }
355
356    #[test]
357    fn address_with_sheet() {
358        let wb = TestWorkbook::new().with_function(Arc::new(AddressFn));
359        let ctx = wb.interpreter();
360        let f = ctx.context.get_function("", "ADDRESS").unwrap();
361
362        let row = lit(LiteralValue::Int(1));
363        let col = lit(LiteralValue::Int(1));
364        let abs_num = lit(LiteralValue::Int(1));
365        let a1_style = lit(LiteralValue::Boolean(true));
366
367        // Simple sheet name
368        let sheet1 = lit(LiteralValue::Text("Sheet1".into()));
369        let args1 = vec![
370            ArgumentHandle::new(&row, &ctx),
371            ArgumentHandle::new(&col, &ctx),
372            ArgumentHandle::new(&abs_num, &ctx),
373            ArgumentHandle::new(&a1_style, &ctx),
374            ArgumentHandle::new(&sheet1, &ctx),
375        ];
376        assert_eq!(
377            f.dispatch(&args1, &ctx.function_context(None)).unwrap(),
378            LiteralValue::Text("Sheet1!$A$1".into())
379        );
380
381        // Sheet name with spaces (needs quoting)
382        let sheet2 = lit(LiteralValue::Text("My Sheet".into()));
383        let args2 = vec![
384            ArgumentHandle::new(&row, &ctx),
385            ArgumentHandle::new(&col, &ctx),
386            ArgumentHandle::new(&abs_num, &ctx),
387            ArgumentHandle::new(&a1_style, &ctx),
388            ArgumentHandle::new(&sheet2, &ctx),
389        ];
390        assert_eq!(
391            f.dispatch(&args2, &ctx.function_context(None)).unwrap(),
392            LiteralValue::Text("'My Sheet'!$A$1".into())
393        );
394    }
395
396    #[test]
397    fn address_r1c1_style() {
398        let wb = TestWorkbook::new().with_function(Arc::new(AddressFn));
399        let ctx = wb.interpreter();
400        let f = ctx.context.get_function("", "ADDRESS").unwrap();
401
402        let row = lit(LiteralValue::Int(5));
403        let col = lit(LiteralValue::Int(3));
404        let abs1 = lit(LiteralValue::Int(1));
405        let r1c1 = lit(LiteralValue::Boolean(false));
406
407        // R1C1 absolute
408        let args = vec![
409            ArgumentHandle::new(&row, &ctx),
410            ArgumentHandle::new(&col, &ctx),
411            ArgumentHandle::new(&abs1, &ctx),
412            ArgumentHandle::new(&r1c1, &ctx),
413        ];
414        assert_eq!(
415            f.dispatch(&args, &ctx.function_context(None)).unwrap(),
416            LiteralValue::Text("R5C3".into())
417        );
418
419        // R1C1 relative
420        let abs4 = lit(LiteralValue::Int(4));
421        let args2 = vec![
422            ArgumentHandle::new(&row, &ctx),
423            ArgumentHandle::new(&col, &ctx),
424            ArgumentHandle::new(&abs4, &ctx),
425            ArgumentHandle::new(&r1c1, &ctx),
426        ];
427        assert_eq!(
428            f.dispatch(&args2, &ctx.function_context(None)).unwrap(),
429            LiteralValue::Text("R[5]C[3]".into())
430        );
431    }
432
433    #[test]
434    fn address_edge_cases() {
435        let wb = TestWorkbook::new().with_function(Arc::new(AddressFn));
436        let ctx = wb.interpreter();
437        let f = ctx.context.get_function("", "ADDRESS").unwrap();
438
439        // Row too large
440        let big_row = lit(LiteralValue::Int(1_048_577));
441        let col = lit(LiteralValue::Int(1));
442        let args = vec![
443            ArgumentHandle::new(&big_row, &ctx),
444            ArgumentHandle::new(&col, &ctx),
445        ];
446        let result = f.dispatch(&args, &ctx.function_context(None)).unwrap();
447        assert!(matches!(result, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Value));
448
449        // Column too large
450        let row = lit(LiteralValue::Int(1));
451        let big_col = lit(LiteralValue::Int(16385));
452        let args2 = vec![
453            ArgumentHandle::new(&row, &ctx),
454            ArgumentHandle::new(&big_col, &ctx),
455        ];
456        let result2 = f.dispatch(&args2, &ctx.function_context(None)).unwrap();
457        assert!(matches!(result2, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Value));
458
459        // Invalid abs_num
460        let abs5 = lit(LiteralValue::Int(5));
461        let normal_col = lit(LiteralValue::Int(1));
462        let args3 = vec![
463            ArgumentHandle::new(&row, &ctx),
464            ArgumentHandle::new(&normal_col, &ctx),
465            ArgumentHandle::new(&abs5, &ctx),
466        ];
467        let result3 = f.dispatch(&args3, &ctx.function_context(None)).unwrap();
468        assert!(matches!(result3, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Value));
469    }
470}
formualizer_eval/builtins/lookup/address.rs

formualizer_eval/builtins/lookup/
address.rs
