formualizer-eval 0.11.0

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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152

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

fn check(formula: &str, expected: LiteralValue) {
    for mode in [
        FormulaPlaneMode::Off,
        FormulaPlaneMode::AuthoritativeExperimental,
    ] {
        let mut engine = Engine::new(
            TestWorkbook::new(),
            EvalConfig {
                formula_plane_mode: mode,
                ..EvalConfig::default()
            },
        );
        for (i, text) in [
            "a*",
            "a~*",
            "apple",
            "a?",
            "a~?",
            "a~",
            "a~~",
            "a.b[+](c)^$",
            "axabQcdZ",
            "axabQcd",
            "a_%literal",
            "axyliteral",
        ]
        .iter()
        .enumerate()
        {
            engine
                .set_cell_value(
                    "Sheet1",
                    i as u32 + 1,
                    6,
                    LiteralValue::Text((*text).into()),
                )
                .unwrap();
            engine
                .set_cell_value(
                    "Sheet1",
                    i as u32 + 1,
                    7,
                    LiteralValue::Number(10_f64.powi(i as i32 + 1)),
                )
                .unwrap();
        }
        engine
            .set_cell_formula("Sheet1", 1, 1, parse(formula).unwrap())
            .unwrap();
        engine.evaluate_all().unwrap();
        let actual = engine.get_cell_value("Sheet1", 1, 1).unwrap();
        match (&actual, &expected) {
            (LiteralValue::Number(a), LiteralValue::Int(b)) => {
                assert_eq!(*a, *b as f64, "{mode:?}: {formula}")
            }
            _ => assert_eq!(actual, expected, "{mode:?}: {formula}"),
        }
    }
}

#[test]
fn wildcard_escapes_weighted_sumif_range() {
    check("=SUMIF(F1:F3,\"a~*\",G1:G3)", LiteralValue::Int(10));
}

#[test]
fn wildcard_escapes_countif_range() {
    check("=COUNTIF(F1:F3,\"a~*\")", LiteralValue::Int(1));
    // Distinguish equal counts by including only the escaped spelling as a decoy.
    check("=COUNTIF(F2:F3,\"a~*\")", LiteralValue::Int(0));
}

#[test]
fn wildcard_escapes_criteria_matrix() {
    for (pattern, sum) in [
        ("a~*", 10),
        ("a~?", 10000),
        ("a~~", 1000000),
        ("a.b[+](c)^$", 100000000),
        ("a*b?c*d?", 1000000000),
        ("a_%*", 100000000000),
    ] {
        check(
            &format!("=SUMIF(F1:F12,\"{pattern}\",G1:G12)"),
            LiteralValue::Int(sum),
        );
        check(
            &format!("=COUNTIF(F1:F12,\"{pattern}\")"),
            LiteralValue::Int(1),
        );
    }
    check(
        "=SUMIF({\"axabQcdZ\",\"axabQcd\"},\"a*b?c*d?\",{7,70})",
        LiteralValue::Int(7),
    );
    check(
        "=COUNTIF({\"a_%literal\",\"axyliteral\"},\"a_%*\")",
        LiteralValue::Int(1),
    );
    for (pattern, sum) in [("a~*", 10), ("a~?", 1000), ("a~~", 10000)] {
        check(
            &format!(
                "=SUMIF({{\"a*\";\"a~*\";\"a?\";\"a~\";\"apple\"}},\"{pattern}\",{{10;100;1000;10000;100000}})"
            ),
            LiteralValue::Int(sum),
        );
        check(
            &format!("=COUNTIF({{\"a*\";\"a~*\";\"a?\";\"a~\";\"apple\"}},\"{pattern}\")"),
            LiteralValue::Int(1),
        );
    }
}

#[test]
fn wildcard_escapes_search_literal_star() {
    check("=SEARCH(\"~*\",\"a*b\")", LiteralValue::Int(2));
    check("=SEARCH(\"~*\",F1)", LiteralValue::Int(2));
    check("=SEARCH(\"~*\",INDEX(F1:F2,2))", LiteralValue::Int(3));
    check(
        "=SEARCH(\"~*\",INDEX({\"a*b\",\"ab*\"},1,2))",
        LiteralValue::Int(3),
    );
}

#[test]
fn wildcard_escapes_search_matrix() {
    for (pattern, hay, start, expected) in [
        ("~?", "a?b", 1, 2),
        ("~~", "a~b", 1, 2),
        ("~", "a~b", 1, 2),
        ("~x", "a~xb", 1, 2),
        ("~*", "a*b*c", 3, 4),
        ("a*b?c*d?", "--axabQcdZ!", 1, 3),
        (".[+](c)^$", "x.[+](c)^$y", 1, 2),
        ("*~?", "abc?tail", 1, 1),
        ("~*?", "é*x", 1, 2),
    ] {
        check(
            &format!("=SEARCH(\"{pattern}\",\"{hay}\",{start})"),
            LiteralValue::Int(expected),
        );
    }
    check(
        "=SEARCH(\"~*\",\"abc\")",
        LiteralValue::Error(ExcelError::new_value()),
    );
}