formualizer-eval 0.12.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

//! Inserting rows or columns near the grid edge: references pushed off the
//! grid become #REF!, range ends stop at the edge, and an insert that would
//! push content off the sheet is refused without changing anything. Each
//! case used to panic.
use crate::engine::{EditorError, Engine, EvalConfig};
use crate::test_workbook::TestWorkbook;
use formualizer_common::{ExcelErrorKind, LiteralValue};
use formualizer_parse::parse;

const LAST_ROW: u32 = 1_048_576;
const LAST_COL: u32 = 16_384;

fn engine() -> Engine<TestWorkbook> {
    Engine::new(TestWorkbook::new(), EvalConfig::default())
}

fn formula(engine: &mut Engine<TestWorkbook>, row: u32, col: u32, text: &str) {
    engine
        .set_cell_formula("Sheet1", row, col, parse(text).unwrap())
        .unwrap();
}

fn formula_text(engine: &Engine<TestWorkbook>, row: u32, col: u32) -> String {
    let (ast, _) = engine.get_cell("Sheet1", row, col).unwrap();
    formualizer_parse::pretty::canonical_formula(&ast.expect("formula"))
}

fn is_ref_error(value: Option<LiteralValue>) -> bool {
    matches!(value, Some(LiteralValue::Error(e)) if e.kind == ExcelErrorKind::Ref)
}

fn refused(result: Result<impl std::fmt::Debug, EditorError>) -> bool {
    matches!(result, Err(EditorError::Excel(e)) if e.kind == ExcelErrorKind::Ref)
}

#[test]
fn references_pushed_off_the_grid_become_ref_errors() {
    for (text, expected) in [
        (format!("=A{LAST_ROW}"), "=#REF!"),
        (format!("=SUM(A{LAST_ROW}:B{LAST_ROW})"), "=SUM(#REF!)"),
    ] {
        let mut e = engine();
        formula(&mut e, 1, 2, &text);
        e.evaluate_all().unwrap();
        e.insert_rows("Sheet1", 2, 1).unwrap();
        e.evaluate_all().unwrap();
        assert_eq!(formula_text(&e, 1, 2), expected, "{text}");
        assert!(is_ref_error(e.get_cell_value("Sheet1", 1, 2)), "{text}");
    }
    let mut e = engine();
    formula(&mut e, 2, 1, "=XFD1");
    e.evaluate_all().unwrap();
    e.insert_columns("Sheet1", 2, 1).unwrap();
    e.evaluate_all().unwrap();
    assert!(is_ref_error(e.get_cell_value("Sheet1", 2, 1)));
}

#[test]
fn range_ends_stop_at_the_grid_edge() {
    let mut e = engine();
    e.set_cell_value("Sheet1", 2, 1, LiteralValue::Number(5.0))
        .unwrap();
    formula(&mut e, 1, 2, &format!("=SUM(A2:A{LAST_ROW})"));
    formula(&mut e, 1, 3, "=SUM(D1:XFD1)");
    e.evaluate_all().unwrap();
    e.insert_rows("Sheet1", 2, 1).unwrap();
    e.evaluate_all().unwrap();
    assert_eq!(formula_text(&e, 1, 2), format!("=SUM(A3:A{LAST_ROW})"));
    assert_eq!(
        e.get_cell_value("Sheet1", 1, 2),
        Some(LiteralValue::Number(5.0))
    );
    e.insert_columns("Sheet1", 4, 1).unwrap();
    e.evaluate_all().unwrap();
    assert_eq!(formula_text(&e, 1, 3), "=SUM(E1:XFD1)");
}

#[test]
fn insert_that_would_push_content_off_the_sheet_is_refused() {
    let mut e = engine();
    e.set_cell_value("Sheet1", LAST_ROW, 1, LiteralValue::Number(1.0))
        .unwrap();
    formula(&mut e, 1, 2, &format!("=A{LAST_ROW}"));
    e.evaluate_all().unwrap();
    assert!(refused(e.insert_rows("Sheet1", 2, 1)));
    e.evaluate_all().unwrap();
    assert_eq!(formula_text(&e, 1, 2), format!("=A{LAST_ROW}"));
    assert_eq!(
        e.get_cell_value("Sheet1", 1, 2),
        Some(LiteralValue::Number(1.0))
    );

    let mut e = engine();
    formula(&mut e, LAST_ROW, 2, "=A1");
    assert!(refused(e.insert_rows("Sheet1", 2, 1)));

    let mut e = engine();
    e.set_cell_value("Sheet1", 1, LAST_COL, LiteralValue::Number(1.0))
        .unwrap();
    assert!(refused(e.insert_columns("Sheet1", 2, 1)));

    // Room for exactly the inserted rows is not a refusal.
    let mut e = engine();
    e.set_cell_value("Sheet1", LAST_ROW - 1, 1, LiteralValue::Number(1.0))
        .unwrap();
    e.insert_rows("Sheet1", 2, 1).unwrap();
    assert_eq!(
        e.get_cell_value("Sheet1", LAST_ROW, 1),
        Some(LiteralValue::Number(1.0))
    );
}