formualizer-eval 0.9.2

High-performance Arrow-backed Excel formula engine with dependency graph and incremental recalculation
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103

//! Excel-parity semantics for XLOOKUP no-match and exact-match class rules.
//!
//! - An omitted-in-place `if_not_found` slot (`XLOOKUP(v,l,r,,mode)`) is not a
//!   supplied argument: a no-match returns #N/A, never the slot's implicit 0.
//! - Exact match never crosses value classes: a text needle does not find a
//!   number, whichever search direction or storage path is used.

use crate::engine::{Engine, EvalConfig};
use crate::test_workbook::TestWorkbook;
use formualizer_common::{ExcelErrorKind, LiteralValue};
use formualizer_parse::parser::parse;

fn numeric_grid_engine() -> Engine<TestWorkbook> {
    let mut engine = Engine::new(TestWorkbook::new(), EvalConfig::default());
    for (row, v) in [(1u32, 10.0), (2, 20.0), (3, 30.0)] {
        engine
            .set_cell_value("Sheet1", row, 2, LiteralValue::Number(v))
            .unwrap();
        engine
            .set_cell_value("Sheet1", row, 3, LiteralValue::Number(v * 10.0))
            .unwrap();
    }
    engine
}

fn assert_na(engine: &Engine<TestWorkbook>, row: u32, col: u32) {
    match engine.get_cell_value("Sheet1", row, col) {
        Some(LiteralValue::Error(e)) => assert_eq!(e.kind, ExcelErrorKind::Na),
        other => panic!("expected #N/A, got {other:?}"),
    }
}

#[test]
fn xlookup_omitted_if_not_found_returns_na_on_approximate_no_match() {
    let mut engine = numeric_grid_engine();
    engine
        .set_cell_formula(
            "Sheet1",
            10,
            1,
            parse("=XLOOKUP(5,B1:B3,C1:C3,,-1)").unwrap(),
        )
        .unwrap();
    engine
        .set_cell_formula(
            "Sheet1",
            11,
            1,
            parse("=XLOOKUP(35,B1:B3,C1:C3,,1)").unwrap(),
        )
        .unwrap();
    engine
        .set_cell_formula(
            "Sheet1",
            12,
            1,
            parse("=XLOOKUP(35,B1:B3,C1:C3,\"none\",1)").unwrap(),
        )
        .unwrap();

    engine.evaluate_all().unwrap();

    assert_na(&engine, 10, 1);
    assert_na(&engine, 11, 1);
    assert_eq!(
        engine.get_cell_value("Sheet1", 12, 1),
        Some(LiteralValue::Text("none".into()))
    );
}

#[test]
fn xlookup_text_needle_does_not_coerce_to_number() {
    let mut engine = numeric_grid_engine();
    engine
        .set_cell_formula(
            "Sheet1",
            10,
            1,
            parse("=XLOOKUP(\"20\",B1:B3,C1:C3)").unwrap(),
        )
        .unwrap();
    engine
        .set_cell_formula(
            "Sheet1",
            11,
            1,
            parse("=XLOOKUP(\"20\",B1:B3,C1:C3,,0,-1)").unwrap(),
        )
        .unwrap();
    // Control: a real numeric needle still matches.
    engine
        .set_cell_formula("Sheet1", 12, 1, parse("=XLOOKUP(20,B1:B3,C1:C3)").unwrap())
        .unwrap();

    engine.evaluate_all().unwrap();

    assert_na(&engine, 10, 1);
    assert_na(&engine, 11, 1);
    assert_eq!(
        engine.get_cell_value("Sheet1", 12, 1),
        Some(LiteralValue::Number(200.0))
    );
}