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

formualizer_eval/builtins/lookup/
address.rs
