1use crate::cell::CellValue;
5use crate::error::{Error, Result};
6use crate::formula::ast::{CellReference, Expr};
7use crate::formula::eval::{coerce_to_number, coerce_to_string, compare_values, Evaluator};
8use crate::formula::functions::check_arg_count;
9use crate::formula::functions::conversion::{
10 checked_trunc_count, checked_trunc_i32, checked_trunc_i64,
11};
12use crate::utils::cell_ref::{column_name_to_number, column_number_to_name};
13
14fn extract_range(expr: &Expr) -> Result<(&CellReference, &CellReference)> {
16 match expr {
17 Expr::Range { start, end } => Ok((start, end)),
18 _ => Err(Error::FormulaError(
19 "expected a range reference".to_string(),
20 )),
21 }
22}
23
24fn read_range(expr: &Expr, ctx: &mut Evaluator) -> Result<(Vec<CellValue>, usize, usize)> {
26 let (start, end) = extract_range(expr)?;
27 let start_col = column_name_to_number(&start.col)?;
28 let end_col = column_name_to_number(&end.col)?;
29 let min_col = start_col.min(end_col);
30 let max_col = start_col.max(end_col);
31 let min_row = start.row.min(end.row);
32 let max_row = start.row.max(end.row);
33 let num_cols = (max_col - min_col + 1) as usize;
34 let num_rows = (max_row - min_row + 1) as usize;
35 let values = ctx.expand_range(start, end)?;
36 Ok((values, num_cols, num_rows))
37}
38
39pub fn fn_vlookup(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
41 check_arg_count("VLOOKUP", args, 3, 4)?;
42 let lookup_val = ctx.eval_expr(&args[0])?;
43 let (values, num_cols, num_rows) = read_range(&args[1], ctx)?;
44 let col_index = checked_trunc_count(coerce_to_number(&ctx.eval_expr(&args[2])?)?).unwrap_or(0);
45 let range_lookup = if args.len() > 3 {
46 match ctx.eval_expr(&args[3])? {
47 CellValue::Bool(b) => b,
48 v => coerce_to_number(&v)? != 0.0,
49 }
50 } else {
51 true
52 };
53
54 if col_index < 1 || col_index > num_cols {
55 return Ok(CellValue::Error("#REF!".to_string()));
56 }
57
58 if range_lookup {
59 let mut best_row: Option<usize> = None;
61 for r in 0..num_rows {
62 let cell = &values[r * num_cols];
63 if compare_values(cell, &lookup_val) != std::cmp::Ordering::Greater {
64 best_row = Some(r);
65 } else {
66 break;
67 }
68 }
69 match best_row {
70 Some(r) => Ok(values[r * num_cols + col_index - 1].clone()),
71 None => Ok(CellValue::Error("#N/A".to_string())),
72 }
73 } else {
74 for r in 0..num_rows {
76 let cell = &values[r * num_cols];
77 if compare_values(cell, &lookup_val) == std::cmp::Ordering::Equal {
78 return Ok(values[r * num_cols + col_index - 1].clone());
79 }
80 }
81 Ok(CellValue::Error("#N/A".to_string()))
82 }
83}
84
85pub fn fn_hlookup(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
87 check_arg_count("HLOOKUP", args, 3, 4)?;
88 let lookup_val = ctx.eval_expr(&args[0])?;
89 let (values, num_cols, num_rows) = read_range(&args[1], ctx)?;
90 let row_index = checked_trunc_count(coerce_to_number(&ctx.eval_expr(&args[2])?)?).unwrap_or(0);
91 let range_lookup = if args.len() > 3 {
92 match ctx.eval_expr(&args[3])? {
93 CellValue::Bool(b) => b,
94 v => coerce_to_number(&v)? != 0.0,
95 }
96 } else {
97 true
98 };
99
100 if row_index < 1 || row_index > num_rows {
101 return Ok(CellValue::Error("#REF!".to_string()));
102 }
103
104 if range_lookup {
105 let mut best_col: Option<usize> = None;
106 for (c, cell) in values.iter().enumerate().take(num_cols) {
107 if compare_values(cell, &lookup_val) != std::cmp::Ordering::Greater {
108 best_col = Some(c);
109 } else {
110 break;
111 }
112 }
113 match best_col {
114 Some(c) => Ok(values[(row_index - 1) * num_cols + c].clone()),
115 None => Ok(CellValue::Error("#N/A".to_string())),
116 }
117 } else {
118 for (c, cell) in values.iter().enumerate().take(num_cols) {
119 if compare_values(cell, &lookup_val) == std::cmp::Ordering::Equal {
120 return Ok(values[(row_index - 1) * num_cols + c].clone());
121 }
122 }
123 Ok(CellValue::Error("#N/A".to_string()))
124 }
125}
126
127pub fn fn_index(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
129 check_arg_count("INDEX", args, 2, 3)?;
130 let (values, num_cols, num_rows) = read_range(&args[0], ctx)?;
131 let row_num = checked_trunc_count(coerce_to_number(&ctx.eval_expr(&args[1])?)?).unwrap_or(0);
132 let col_num = if args.len() > 2 {
133 checked_trunc_count(coerce_to_number(&ctx.eval_expr(&args[2])?)?).unwrap_or(0)
134 } else {
135 1
136 };
137
138 if row_num < 1 || row_num > num_rows || col_num < 1 || col_num > num_cols {
139 return Ok(CellValue::Error("#REF!".to_string()));
140 }
141 Ok(values[(row_num - 1) * num_cols + (col_num - 1)].clone())
142}
143
144pub fn fn_match(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
146 check_arg_count("MATCH", args, 2, 3)?;
147 let lookup_val = ctx.eval_expr(&args[0])?;
148 let (values, num_cols, num_rows) = read_range(&args[1], ctx)?;
149 let match_type = if args.len() > 2 {
150 let value = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
151 let Some(match_type) = checked_trunc_i32(value) else {
152 return Ok(CellValue::Error("#N/A".to_string()));
153 };
154 match_type
155 } else {
156 1
157 };
158
159 let items: &[CellValue] = if num_rows == 1 || num_cols == 1 {
161 &values
162 } else {
163 return Ok(CellValue::Error("#N/A".to_string()));
164 };
165
166 match match_type {
167 0 => {
168 for (i, v) in items.iter().enumerate() {
170 if compare_values(v, &lookup_val) == std::cmp::Ordering::Equal {
171 return Ok(CellValue::Number((i + 1) as f64));
172 }
173 }
174 Ok(CellValue::Error("#N/A".to_string()))
175 }
176 1 => {
177 let mut best: Option<usize> = None;
179 for (i, v) in items.iter().enumerate() {
180 if compare_values(v, &lookup_val) != std::cmp::Ordering::Greater {
181 best = Some(i);
182 }
183 }
184 match best {
185 Some(i) => Ok(CellValue::Number((i + 1) as f64)),
186 None => Ok(CellValue::Error("#N/A".to_string())),
187 }
188 }
189 -1 => {
190 let mut best: Option<usize> = None;
192 for (i, v) in items.iter().enumerate() {
193 if compare_values(v, &lookup_val) != std::cmp::Ordering::Less {
194 best = Some(i);
195 }
196 }
197 match best {
198 Some(i) => Ok(CellValue::Number((i + 1) as f64)),
199 None => Ok(CellValue::Error("#N/A".to_string())),
200 }
201 }
202 _ => Ok(CellValue::Error("#N/A".to_string())),
203 }
204}
205
206pub fn fn_lookup(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
208 check_arg_count("LOOKUP", args, 2, 3)?;
209 let lookup_val = ctx.eval_expr(&args[0])?;
210 let (lookup_values, _, _) = read_range(&args[1], ctx)?;
211
212 let mut best: Option<usize> = None;
214 for (i, v) in lookup_values.iter().enumerate() {
215 if compare_values(v, &lookup_val) != std::cmp::Ordering::Greater {
216 best = Some(i);
217 }
218 }
219 let idx = match best {
220 Some(i) => i,
221 None => return Ok(CellValue::Error("#N/A".to_string())),
222 };
223
224 if args.len() > 2 {
225 let (result_values, _, _) = read_range(&args[2], ctx)?;
226 if idx < result_values.len() {
227 Ok(result_values[idx].clone())
228 } else {
229 Ok(CellValue::Error("#N/A".to_string()))
230 }
231 } else {
232 Ok(lookup_values[idx].clone())
233 }
234}
235
236pub fn fn_row(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
238 check_arg_count("ROW", args, 0, 1)?;
239 if args.is_empty() {
240 return Ok(CellValue::Number(ctx.position().1 as f64));
241 }
242 match &args[0] {
243 Expr::CellRef(cell_ref) => Ok(CellValue::Number(cell_ref.row as f64)),
244 Expr::Range { start, .. } => Ok(CellValue::Number(start.row as f64)),
245 _ => Ok(CellValue::Error("#VALUE!".to_string())),
246 }
247}
248
249pub fn fn_column(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
251 check_arg_count("COLUMN", args, 0, 1)?;
252 if args.is_empty() {
253 return Ok(CellValue::Number(ctx.position().0 as f64));
254 }
255 match &args[0] {
256 Expr::CellRef(cell_ref) => {
257 let col = column_name_to_number(&cell_ref.col)?;
258 Ok(CellValue::Number(col as f64))
259 }
260 Expr::Range { start, .. } => {
261 let col = column_name_to_number(&start.col)?;
262 Ok(CellValue::Number(col as f64))
263 }
264 _ => Ok(CellValue::Error("#VALUE!".to_string())),
265 }
266}
267
268pub fn fn_rows(args: &[Expr], _ctx: &mut Evaluator) -> Result<CellValue> {
270 check_arg_count("ROWS", args, 1, 1)?;
271 match &args[0] {
272 Expr::Range { start, end } => {
273 let rows = (end.row.max(start.row) - end.row.min(start.row) + 1) as f64;
274 Ok(CellValue::Number(rows))
275 }
276 Expr::CellRef(_) => Ok(CellValue::Number(1.0)),
277 _ => Ok(CellValue::Error("#VALUE!".to_string())),
278 }
279}
280
281pub fn fn_columns(args: &[Expr], _ctx: &mut Evaluator) -> Result<CellValue> {
283 check_arg_count("COLUMNS", args, 1, 1)?;
284 match &args[0] {
285 Expr::Range { start, end } => {
286 let start_col = column_name_to_number(&start.col)?;
287 let end_col = column_name_to_number(&end.col)?;
288 let cols = (start_col.max(end_col) - start_col.min(end_col) + 1) as f64;
289 Ok(CellValue::Number(cols))
290 }
291 Expr::CellRef(_) => Ok(CellValue::Number(1.0)),
292 _ => Ok(CellValue::Error("#VALUE!".to_string())),
293 }
294}
295
296pub fn fn_choose(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
298 check_arg_count("CHOOSE", args, 2, 255)?;
299 let index = checked_trunc_count(coerce_to_number(&ctx.eval_expr(&args[0])?)?).unwrap_or(0);
300 if index < 1 || index >= args.len() {
301 return Ok(CellValue::Error("#VALUE!".to_string()));
302 }
303 ctx.eval_expr(&args[index])
304}
305
306pub fn fn_address(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
308 check_arg_count("ADDRESS", args, 2, 5)?;
309 let row = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
310 let col = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
311 let (Some(row), Some(col)) = (
312 checked_trunc_i64(row, 0, i64::from(u32::MAX)),
313 checked_trunc_i64(col, 0, i64::from(u32::MAX)),
314 ) else {
315 return Ok(CellValue::Error("#VALUE!".to_string()));
316 };
317 let (Ok(row), Ok(col)) = (u32::try_from(row), u32::try_from(col)) else {
318 return Ok(CellValue::Error("#VALUE!".to_string()));
319 };
320 let abs_num = if args.len() > 2 {
321 let value = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
322 let Some(abs_num) = checked_trunc_i32(value) else {
323 return Ok(CellValue::Error("#VALUE!".to_string()));
324 };
325 abs_num
326 } else {
327 1
328 };
329 let a1 = if args.len() > 3 {
331 match ctx.eval_expr(&args[3])? {
332 CellValue::Bool(b) => b,
333 v => coerce_to_number(&v)? != 0.0,
334 }
335 } else {
336 true
337 };
338 let sheet_text = if args.len() > 4 {
339 Some(coerce_to_string(&ctx.eval_expr(&args[4])?))
340 } else {
341 None
342 };
343
344 if col < 1 || row < 1 {
345 return Ok(CellValue::Error("#VALUE!".to_string()));
346 }
347
348 let col_name = column_number_to_name(col)?;
349
350 let address = if a1 {
351 match abs_num {
352 1 => format!("${col_name}${row}"), 2 => format!("{col_name}${row}"), 3 => format!("${col_name}{row}"), 4 => format!("{col_name}{row}"), _ => return Ok(CellValue::Error("#VALUE!".to_string())),
357 }
358 } else {
359 match abs_num {
361 1 => format!("R{row}C{col}"),
362 2 => format!("R{row}C[{col}]"),
363 3 => format!("R[{row}]C{col}"),
364 4 => format!("R[{row}]C[{col}]"),
365 _ => return Ok(CellValue::Error("#VALUE!".to_string())),
366 }
367 };
368
369 if let Some(sheet) = sheet_text {
370 Ok(CellValue::String(format!("{sheet}!{address}")))
371 } else {
372 Ok(CellValue::String(address))
373 }
374}
375
376#[cfg(test)]
377mod tests {
378 use super::*;
379 use crate::formula::eval::{evaluate, CellSnapshot};
380 use crate::formula::parser::parse_formula;
381
382 fn eval(formula: &str) -> CellValue {
383 let snap = CellSnapshot::new("Sheet1".to_string());
384 let expr = parse_formula(formula).unwrap();
385 evaluate(&expr, &snap).unwrap()
386 }
387
388 fn eval_with_data(formula: &str, snap: &CellSnapshot) -> CellValue {
389 let expr = parse_formula(formula).unwrap();
390 evaluate(&expr, snap).unwrap()
391 }
392
393 #[test]
394 fn test_vlookup_exact() {
395 let mut snap = CellSnapshot::new("Sheet1".to_string());
396 snap.set_cell("Sheet1", 1, 1, CellValue::Number(1.0));
398 snap.set_cell("Sheet1", 2, 1, CellValue::String("one".to_string()));
399 snap.set_cell("Sheet1", 1, 2, CellValue::Number(2.0));
400 snap.set_cell("Sheet1", 2, 2, CellValue::String("two".to_string()));
401 snap.set_cell("Sheet1", 1, 3, CellValue::Number(3.0));
402 snap.set_cell("Sheet1", 2, 3, CellValue::String("three".to_string()));
403
404 assert_eq!(
405 eval_with_data("VLOOKUP(2,A1:B3,2,FALSE)", &snap),
406 CellValue::String("two".to_string())
407 );
408 }
409
410 #[test]
411 fn test_vlookup_not_found() {
412 let mut snap = CellSnapshot::new("Sheet1".to_string());
413 snap.set_cell("Sheet1", 1, 1, CellValue::Number(1.0));
414 snap.set_cell("Sheet1", 2, 1, CellValue::String("one".to_string()));
415
416 assert_eq!(
417 eval_with_data("VLOOKUP(99,A1:B1,2,FALSE)", &snap),
418 CellValue::Error("#N/A".to_string())
419 );
420 }
421
422 #[test]
423 fn test_vlookup_approximate() {
424 let mut snap = CellSnapshot::new("Sheet1".to_string());
425 snap.set_cell("Sheet1", 1, 1, CellValue::Number(10.0));
426 snap.set_cell("Sheet1", 2, 1, CellValue::String("ten".to_string()));
427 snap.set_cell("Sheet1", 1, 2, CellValue::Number(20.0));
428 snap.set_cell("Sheet1", 2, 2, CellValue::String("twenty".to_string()));
429 snap.set_cell("Sheet1", 1, 3, CellValue::Number(30.0));
430 snap.set_cell("Sheet1", 2, 3, CellValue::String("thirty".to_string()));
431
432 assert_eq!(
433 eval_with_data("VLOOKUP(25,A1:B3,2,TRUE)", &snap),
434 CellValue::String("twenty".to_string())
435 );
436 }
437
438 #[test]
439 fn test_hlookup_exact() {
440 let mut snap = CellSnapshot::new("Sheet1".to_string());
441 snap.set_cell("Sheet1", 1, 1, CellValue::Number(1.0));
443 snap.set_cell("Sheet1", 2, 1, CellValue::Number(2.0));
444 snap.set_cell("Sheet1", 3, 1, CellValue::Number(3.0));
445 snap.set_cell("Sheet1", 1, 2, CellValue::String("one".to_string()));
446 snap.set_cell("Sheet1", 2, 2, CellValue::String("two".to_string()));
447 snap.set_cell("Sheet1", 3, 2, CellValue::String("three".to_string()));
448
449 assert_eq!(
450 eval_with_data("HLOOKUP(2,A1:C2,2,FALSE)", &snap),
451 CellValue::String("two".to_string())
452 );
453 }
454
455 #[test]
456 fn test_index_basic() {
457 let mut snap = CellSnapshot::new("Sheet1".to_string());
458 snap.set_cell("Sheet1", 1, 1, CellValue::Number(1.0));
459 snap.set_cell("Sheet1", 2, 1, CellValue::Number(2.0));
460 snap.set_cell("Sheet1", 1, 2, CellValue::Number(3.0));
461 snap.set_cell("Sheet1", 2, 2, CellValue::Number(4.0));
462
463 assert_eq!(
464 eval_with_data("INDEX(A1:B2,2,2)", &snap),
465 CellValue::Number(4.0)
466 );
467 }
468
469 #[test]
470 fn test_match_exact() {
471 let mut snap = CellSnapshot::new("Sheet1".to_string());
472 snap.set_cell("Sheet1", 1, 1, CellValue::String("apple".to_string()));
473 snap.set_cell("Sheet1", 1, 2, CellValue::String("banana".to_string()));
474 snap.set_cell("Sheet1", 1, 3, CellValue::String("cherry".to_string()));
475
476 assert_eq!(
477 eval_with_data(r#"MATCH("banana",A1:A3,0)"#, &snap),
478 CellValue::Number(2.0)
479 );
480 }
481
482 #[test]
483 fn test_match_not_found() {
484 let mut snap = CellSnapshot::new("Sheet1".to_string());
485 snap.set_cell("Sheet1", 1, 1, CellValue::Number(1.0));
486 snap.set_cell("Sheet1", 1, 2, CellValue::Number(2.0));
487
488 assert_eq!(
489 eval_with_data("MATCH(99,A1:A2,0)", &snap),
490 CellValue::Error("#N/A".to_string())
491 );
492 }
493
494 #[test]
495 fn test_lookup_vector() {
496 let mut snap = CellSnapshot::new("Sheet1".to_string());
497 snap.set_cell("Sheet1", 1, 1, CellValue::Number(1.0));
498 snap.set_cell("Sheet1", 1, 2, CellValue::Number(2.0));
499 snap.set_cell("Sheet1", 1, 3, CellValue::Number(3.0));
500 snap.set_cell("Sheet1", 2, 1, CellValue::String("one".to_string()));
501 snap.set_cell("Sheet1", 2, 2, CellValue::String("two".to_string()));
502 snap.set_cell("Sheet1", 2, 3, CellValue::String("three".to_string()));
503
504 assert_eq!(
505 eval_with_data("LOOKUP(2,A1:A3,B1:B3)", &snap),
506 CellValue::String("two".to_string())
507 );
508 }
509
510 #[test]
511 fn test_row() {
512 assert_eq!(eval("ROW(B5)"), CellValue::Number(5.0));
513 }
514
515 #[test]
516 fn test_column() {
517 assert_eq!(eval("COLUMN(C1)"), CellValue::Number(3.0));
518 }
519
520 #[test]
521 fn test_rows() {
522 assert_eq!(eval("ROWS(A1:A10)"), CellValue::Number(10.0));
523 }
524
525 #[test]
526 fn test_columns() {
527 assert_eq!(eval("COLUMNS(A1:D1)"), CellValue::Number(4.0));
528 }
529
530 #[test]
531 fn test_choose() {
532 assert_eq!(
533 eval(r#"CHOOSE(2,"a","b","c")"#),
534 CellValue::String("b".to_string())
535 );
536 }
537
538 #[test]
539 fn test_choose_out_of_range() {
540 assert_eq!(
541 eval(r#"CHOOSE(5,"a","b","c")"#),
542 CellValue::Error("#VALUE!".to_string())
543 );
544 }
545
546 #[test]
547 fn test_address_absolute() {
548 assert_eq!(eval("ADDRESS(1,1)"), CellValue::String("$A$1".to_string()));
549 }
550
551 #[test]
552 fn test_address_relative() {
553 assert_eq!(eval("ADDRESS(1,1,4)"), CellValue::String("A1".to_string()));
554 }
555
556 #[test]
557 fn test_address_with_sheet() {
558 assert_eq!(
559 eval(r#"ADDRESS(1,1,1,TRUE,"Sheet2")"#),
560 CellValue::String("Sheet2!$A$1".to_string())
561 );
562 }
563
564 #[test]
565 fn test_row_no_args() {
566 assert_eq!(eval("ROW()"), CellValue::Number(1.0));
567 }
568
569 #[test]
570 fn test_column_no_args() {
571 assert_eq!(eval("COLUMN()"), CellValue::Number(1.0));
572 }
573}