formualizer-eval 0.8.4

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

use formualizer_common::{ExcelErrorKind, LiteralValue};
use formualizer_parse::parser::parse;

use crate::engine::{Engine, EvalConfig, FormulaPlaneMode};
use crate::test_workbook::TestWorkbook;

fn build(mode: FormulaPlaneMode) -> Engine<TestWorkbook> {
    let mut engine = Engine::new(
        TestWorkbook::default(),
        EvalConfig::default().with_formula_plane_mode(mode),
    );
    for (row, value) in [(1, 10.0), (2, 20.0), (3, 10.0)] {
        engine
            .set_cell_value("Sheet1", row, 1, LiteralValue::Number(value))
            .unwrap();
    }
    let formulas = [
        "=SUM(SEQUENCE(3))",
        "=COUNT(UNIQUE(A1:A3))",
        "=COUNTA(SORT(A1:A3))",
        "=AVERAGE(TRANSPOSE(A1:A3))",
        "=MAX((A1:A3=10)*A1:A3)",
        "=SUM(OFFSET(A1,0,0,3,1))",
        "=SUM(INDIRECT(\"A1:A3\"))",
        "=SUM(CHOOSE(1,5,6))",
        "=SUM(CHOOSE(1,SEQUENCE(3),SEQUENCE(2)))",
        "=SUM(INDEX(SEQUENCE(3),0))",
        "=SUM(SEQUENCE(-1))",
        "=SUM(OFFSET(A1,-1,0))",
        "=SUM(INDIRECT(\"not a reference\"))",
    ];
    for (row, formula) in formulas.into_iter().enumerate() {
        engine
            .set_cell_formula("Sheet1", row as u32 + 1, 2, parse(formula).unwrap())
            .unwrap();
    }
    engine.evaluate_all().unwrap();
    engine
}

#[test]
fn arena_computed_array_aggregates_match_off_and_authoritative_modes() {
    let off = build(FormulaPlaneMode::Off);
    let authoritative = build(FormulaPlaneMode::AuthoritativeExperimental);

    for row in 1..=13 {
        assert_eq!(
            authoritative.get_cell_value("Sheet1", row, 2),
            off.get_cell_value("Sheet1", row, 2),
            "mode mismatch at B{row}"
        );
    }

    let expected = [
        LiteralValue::Number(6.0),
        LiteralValue::Number(2.0),
        LiteralValue::Number(3.0),
        LiteralValue::Number(40.0 / 3.0),
        LiteralValue::Number(10.0),
        LiteralValue::Number(40.0),
        LiteralValue::Number(40.0),
        LiteralValue::Number(5.0),
        LiteralValue::Number(6.0),
        LiteralValue::Number(6.0),
    ];
    for (row, expected) in expected.into_iter().enumerate() {
        assert_eq!(
            off.get_cell_value("Sheet1", row as u32 + 1, 2),
            Some(expected)
        );
    }
    assert!(matches!(
        off.get_cell_value("Sheet1", 11, 2),
        Some(LiteralValue::Error(error)) if error.kind == ExcelErrorKind::Value
    ));
    for (row, kind) in [(12, ExcelErrorKind::Ref), (13, ExcelErrorKind::Name)] {
        let Some(LiteralValue::Error(error)) = off.get_cell_value("Sheet1", row, 2) else {
            panic!("expected an error value at B{row}");
        };
        assert_eq!(error.kind, kind, "error kind at B{row}");
        assert_eq!(error.message, None, "error message at B{row}");
    }
}