craft-libs/crates/xls/tests/validation.rs

//! Data validation: DVal and Dv records ([MS-XLS] 2.4.96, 2.4.95), each assembled here from the
//! spec's structures; no Excel-produced file is used. The expected rules are what a
//! SpreadsheetML reader gives for the same rule. Ported from storytold/gridcraft#3636.

mod common;

use common::*;
use craft_xls::{CellRef, DataValidation, ErrorStyle, Range, ValidationOperator, ValidationType, Workbook};

/// A workbook of one worksheet, "Sheet1", holding `records`, with defined names (Lbl records,
/// each a name and its formula) in the globals.
fn workbook(names: &[(&str, &[u8])], records: &[Vec<u8>]) -> (Workbook, Report) {
    let lbl = |name: &str, rgce: &[u8]| {
        rec(0x0018, &cat(&[&u16s(&[0]), &[0, name.len() as u8], &u16s(&[rgce.len() as u16, 0, 0]), &[0; 4], &[0], name.as_bytes(), rgce]))
    };
    let lbls: Vec<u8> = names.iter().flat_map(|(n, f)| lbl(n, f)).collect();
    let globals = |pos: u32| cat(&[&bof(0x0005), &rec(0x0085, &cat(&[&pos.to_le_bytes(), &[0, 0, 6, 0], b"Sheet1"])), &lbls, &eof()]);
    let sheet = cat(&[&bof(0x0010), &records.concat(), &eof()]);
    let stream = cat(&[&globals(globals(0).len() as u32), &sheet]);
    read_xls(&compound("Workbook", &stream)).unwrap()
}

/// DVal: flags, the input window's position, no drop-down object (-1), then `count` rules.
fn dval(count: u32) -> Vec<u8> {
    rec(0x01B2, &cat(&[&[0, 0], &[0; 8], &(-1i32).to_le_bytes(), &count.to_le_bytes()]))
}

// Dv flag bits ([MS-XLS] 2.4.95).
const STR_LOOKUP: u32 = 1 << 7;
const ALLOW_BLANK: u32 = 1 << 8;
const SUPPRESS_COMBO: u32 = 1 << 9;
const SHOW_INPUT: u32 = 1 << 18;
const SHOW_ERROR: u32 = 1 << 19;
/// What Excel sets on a new rule: blanks allowed, input message and error alert shown.
const DEFAULTS: u32 = ALLOW_BLANK | SHOW_INPUT | SHOW_ERROR;

/// Dv flags: rule type, alert style, operator, plus `bits`.
fn flags(kind: u32, alert: u32, op: u32, bits: u32) -> u32 {
    kind | (alert << 4) | (op << 20) | bits
}

/// XLUnicodeString with compressed characters; "" is stored as Excel stores an empty message,
/// a single NUL character.
fn msg(s: &str) -> Vec<u8> {
    xlstr(if s.is_empty() { "\0" } else { s })
}

/// XLUnicodeString with UTF-16 characters.
fn wide(s: &str) -> Vec<u8> {
    let units: Vec<u16> = s.encode_utf16().collect();
    cat(&[&(units.len() as u16).to_le_bytes(), &[1], &u16s(&units)])
}

/// DVParsedFormula: size, 2 unused bytes (Excel leaves stray data there), the tokens.
fn formula(rgce: &[u8]) -> Vec<u8> {
    cat(&[&(rgce.len() as u16).to_le_bytes(), &[0x6E, 0x74], rgce])
}

/// SqRefU: a count, then each Ref8U as rows (first, last) and columns (first, last).
fn sqref(ranges: &[[u16; 4]]) -> Vec<u8> {
    let list: Vec<u8> = ranges.iter().flat_map(|r| u16s(r)).collect();
    cat(&[&(ranges.len() as u16).to_le_bytes(), &list])
}

/// A Dv record with its four messages (prompt title, error title, prompt, error).
fn dv(flags: u32, msgs: [Vec<u8>; 4], f1: &[u8], f2: &[u8], ranges: &[[u16; 4]]) -> Vec<u8> {
    let [pt, et, p, e] = msgs;
    rec(0x01BE, &cat(&[&flags.to_le_bytes(), &pt, &et, &p, &e, &formula(f1), &formula(f2), &sqref(ranges)]))
}

fn no_msgs() -> [Vec<u8>; 4] {
    [msg(""), msg(""), msg(""), msg("")]
}

/// A Dv with Excel's default flags and no messages.
fn rule(kind: u32, alert: u32, op: u32, f1: &[u8], f2: &[u8], ranges: &[[u16; 4]]) -> Vec<u8> {
    dv(flags(kind, alert, op, DEFAULTS), no_msgs(), f1, f2, ranges)
}

fn range(a1: &str) -> Range {
    Range::parse(a1).unwrap()
}

/// A rule with Excel's default flags and no messages.
fn expected(ranges: &[&str], kind: ValidationType, operator: ValidationOperator, f1: &str, f2: Option<&str>) -> DataValidation {
    DataValidation {
        ranges: ranges.iter().map(|r| range(r)).collect(),
        kind,
        operator,
        formula1: f1.into(),
        formula2: f2.map(Into::into),
        allow_blank: true,
        in_cell_dropdown: true,
        prompt_title: String::new(),
        prompt: String::new(),
        show_prompt: true,
        error_title: String::new(),
        error: String::new(),
        error_style: ErrorStyle::Stop,
        show_error: true,
    }
}

fn hex(s: &str) -> Vec<u8> {
    (0..s.len()).step_by(2).map(|i| u8::from_str_radix(&s[i..i + 2], 16).unwrap()).collect()
}

const SKIPPED: &str = "some data validation rules could not be read and were skipped";
const IGNORED: &str =
    "some data validation records were ignored, as Excel ignores them: no DVal record before them, or past the number of rules it gives";

#[test]
fn reads_every_kind_of_rule() {
    use ValidationOperator::*;
    use ValidationType::*;
    let rules = [
        // Whole number between 1 and 10, with all four messages and the stop alert.
        dv(
            flags(1, 0, 0, DEFAULTS),
            [msg("Whole"), msg("Oops"), msg("Enter 1 to 10"), msg("Not 1..10")],
            &hex("1e0100"),
            &hex("1e0a00"),
            &[[1, 5, 1, 1]],
        ),
        // Decimal >= 0.5, warning alert.
        rule(2, 1, 6, &hex("1f000000000000e03f"), &[], &[[1, 5, 2, 2]]),
        // Literal list (NUL-separated items), information alert.
        dv(flags(3, 2, 0, DEFAULTS | STR_LOOKUP), no_msgs(), &hex("170e0052656400477265656e00426c7565"), &[], &[[1, 5, 3, 3]]),
        // List from an absolute range ($H$2:$H$5).
        rule(3, 0, 0, &hex("250100040007000700"), &[], &[[1, 5, 4, 4]]),
        // Date between two serials, time < 0.5, text length <= 5, whole not between 5 and 7.
        rule(4, 0, 0, &hex("1e37ab"), &hex("1ea4ac"), &[[1, 5, 5, 5]]),
        rule(5, 0, 5, &hex("1f000000000000e03f"), &[], &[[1, 5, 6, 6]]),
        rule(6, 0, 7, &hex("1e0500"), &[], &[[1, 5, 8, 8]]),
        rule(1, 0, 1, &hex("1e0500"), &hex("1e0700"), &[[1, 5, 9, 9]]),
        // Custom formula K2>A2 on K2:K6 (relative PtgRefN offsets 0 and -10 columns).
        rule(7, 0, 0, &hex("4c000000c04c0000f6c00d"), &[], &[[1, 5, 10, 10]]),
        // Any value, with an input message only.
        dv(flags(0, 0, 0, DEFAULTS), [msg("Note"), msg(""), msg("Anything goes"), msg("")], &[], &[], &[[1, 5, 13, 13]]),
        // A list from a defined name.
        rule(3, 0, 0, &hex("2301000000"), &[], &[[1, 5, 16, 16]]),
        // A dependent list: INDIRECT($S2) on T2:T6.
        rule(3, 0, 0, &hex("190100004c0000128022019400"), &[], &[[1, 5, 19, 19]]),
        // Decimal between two absolute cells.
        rule(2, 0, 0, &hex("4406000700"), &hex("4407000700"), &[[1, 5, 22, 22]]),
    ];
    let mut records = vec![dval(rules.len() as u32)];
    records.extend(rules);
    let (wb, report) = workbook(&[("Colors", &[0x1E, 1, 0])], &records);
    let got = &wb.sheets[0].validations;
    let mut whole = expected(&["B2:B6"], Whole, Between, "1", Some("10"));
    (whole.prompt_title, whole.error_title, whole.prompt, whole.error) = ("Whole".into(), "Oops".into(), "Enter 1 to 10".into(), "Not 1..10".into());
    let mut decimal = expected(&["C2:C6"], Decimal, GreaterOrEqual, "0.5", None);
    decimal.error_style = ErrorStyle::Warning;
    let mut literal = expected(&["D2:D6"], List, Between, "\"Red,Green,Blue\"", None);
    literal.error_style = ErrorStyle::Information;
    let mut any = expected(&["N2:N6"], Any, Between, "", None);
    (any.prompt_title, any.prompt) = ("Note".into(), "Anything goes".into());
    let want = vec![
        whole,
        decimal,
        literal,
        expected(&["E2:E6"], List, Between, "$H$2:$H$5", None),
        expected(&["F2:F6"], Date, Between, "43831", Some("44196")),
        expected(&["G2:G6"], Time, Less, "0.5", None),
        expected(&["I2:I6"], TextLength, LessOrEqual, "5", None),
        expected(&["J2:J6"], Whole, NotBetween, "5", Some("7")),
        expected(&["K2:K6"], Custom, Between, "K2>A2", None),
        any,
        expected(&["Q2:Q6"], List, Between, "Colors", None),
        expected(&["T2:T6"], List, Between, "INDIRECT($S2)", None),
        expected(&["W2:W6"], Decimal, Between, "$H$7", Some("$H$8")),
    ];
    assert_eq!(got.len(), want.len(), "{:?}", report.warnings);
    for (g, w) in got.iter().zip(&want) {
        assert_eq!(g, w);
    }
    assert!(report.warnings.is_empty(), "{:?}", report.warnings);
}

#[test]
fn reads_flags_messages_lists_and_whole_columns() {
    let off = flags(3, 0, 0, STR_LOOKUP | SUPPRESS_COMBO);
    let records = [
        dval(4),
        dv(off, no_msgs(), &hex("170300610062"), &[], &[[1, 5, 14, 14]]),
        dv(
            flags(1, 0, 2, DEFAULTS),
            [wide("Café"), wide("Ошибка"), wide("日本語のテスト é ü"), wide("Nur 3 ✓")],
            &hex("1e0300"),
            &[],
            &[[1, 5, 20, 20]],
        ),
        // typOperator 6 on a list, as found in real files: ignored.
        dv(flags(3, 0, 6, DEFAULTS | STR_LOOKUP), no_msgs(), &hex("170c00736179202268692200627965"), &[], &[[1, 5, 23, 23]]),
        // Rows 0 to 65535: every row of the BIFF8 grid.
        dv(flags(3, 0, 0, DEFAULTS | STR_LOOKUP), no_msgs(), &hex("170500780079007a"), &[], &[[0, 0xFFFF, 21, 21]]),
    ];
    let (wb, report) = workbook(&[], &records);
    let got = &wb.sheets[0].validations;
    assert_eq!(got.len(), 4, "{:?}", report.warnings);
    let mut flags_off = expected(&["O2:O6"], ValidationType::List, ValidationOperator::Between, "\"a,b\"", None);
    (flags_off.allow_blank, flags_off.in_cell_dropdown, flags_off.show_prompt, flags_off.show_error) = (false, false, false, false);
    assert_eq!(got[0], flags_off);
    let mut unicode = expected(&["U2:U6"], ValidationType::Whole, ValidationOperator::Equal, "3", None);
    (unicode.prompt_title, unicode.error_title, unicode.prompt, unicode.error) =
        ("Café".into(), "Ошибка".into(), "日本語のテスト é ü".into(), "Nur 3 ✓".into());
    assert_eq!(got[1], unicode);
    // The item `say "hi"` keeps its quotes, doubled; the operator is ignored.
    assert_eq!(got[2], expected(&["X2:X6"], ValidationType::List, ValidationOperator::Between, "\"say \"\"hi\"\",bye\"", None));
    assert_eq!(got[3].ranges, vec![range("V:V")]);
}

#[test]
fn relative_references_start_from_the_top_left_of_all_ranges() {
    let records = [
        dval(3),
        // =AE1>0 for AF5:AF9 AG1:AG3 (column offset -1 from AF1).
        rule(7, 0, 0, &hex("4c0000ffc01e00000d"), &[], &[[4, 8, 31, 31], [0, 2, 32, 32]]),
        // =AB1>0 for AC1:AC3 AA5:AA9 (column offset +1 from AA1).
        rule(7, 0, 0, &hex("4c000001c01e00000d"), &[], &[[0, 2, 28, 28], [4, 8, 26, 26]]),
        // PtgRefErr: a reference that fell off the grid.
        rule(7, 0, 0, &hex("2a0000f4c01e00000d"), &[], &[[4, 8, 12, 12], [0, 2, 11, 11]]),
    ];
    let (wb, report) = workbook(&[], &records);
    let got = &wb.sheets[0].validations;
    assert_eq!(got.len(), 3, "{:?}", report.warnings);
    assert_eq!(got[0].ranges, vec![range("AF5:AF9"), range("AG1:AG3")]);
    assert_eq!(got[0].formula1, "AE1>0");
    assert_eq!(got[1].formula1, "AB1>0");
    assert_eq!(got[2].formula1, "#REF!>0");
}

#[test]
fn only_the_dvals_count_of_rules_is_read() {
    let a = || rule(1, 0, 4, &hex("1e0000"), &[], &[[0, 0, 0, 0]]);
    let b = || rule(1, 0, 4, &hex("1e0000"), &[], &[[1, 1, 0, 0]]);
    let (wb, report) = workbook(&[], &[dval(1), a(), b(), b()]);
    assert_eq!(wb.sheets[0].validations.iter().map(|v| v.ranges.clone()).collect::<Vec<_>>(), vec![vec![range("A1")]]);
    assert_eq!(report.warnings, [IGNORED]);
    let (wb, report) = workbook(&[], &[a(), b()]);
    assert!(wb.sheets[0].validations.is_empty());
    assert_eq!(report.warnings, [IGNORED]);
}

#[test]
fn malformed_rules_are_repaired_or_skipped() {
    let ok = |row: u16| rule(1, 0, 4, &hex("1e0000"), &[], &[[row, row, 0, 0]]);
    let cases: Vec<(&str, Vec<u8>, Option<&str>)> = vec![
        // Excel opens an unknown type as "any value".
        ("unknown type", rule(9, 0, 0, &hex("1e0100"), &[], &[[1, 5, 1, 1]]), Some("B2:B6")),
        // Excel refuses an unknown alert style, or an unknown operator on a rule that uses one.
        ("unknown alert", rule(1, 5, 0, &hex("1e0100"), &hex("1e0a00"), &[[1, 5, 1, 1]]), None),
        ("unknown operator", rule(1, 0, 9, &hex("1e0100"), &[], &[[1, 5, 1, 1]]), None),
        // Excel drops a reversed range, and clamps columns past IV.
        ("reversed range", rule(1, 0, 4, &hex("1e0000"), &[], &[[5, 1, 2, 2]]), None),
        ("past column IV", rule(1, 0, 4, &hex("1e0000"), &[], &[[1, 5, 3, 0x100]]), Some("D2:IV6")),
        // Excel refuses a range count or a formula size that runs past the record.
        (
            "too many ranges",
            rec(
                0x01BE,
                &cat(&[
                    &flags(1, 0, 4, DEFAULTS).to_le_bytes(),
                    &no_msgs().concat(),
                    &formula(&hex("1e0000")),
                    &formula(&[]),
                    &u16s(&[2, 1, 5, 4, 4]),
                ]),
            ),
            None,
        ),
        (
            "formula past the record",
            rec(0x01BE, &cat(&[&flags(1, 0, 4, DEFAULTS).to_le_bytes(), &no_msgs().concat(), &u16s(&[200, 0]), &hex("1e0000")])),
            None,
        ),
        ("truncated message", rec(0x01BE, &cat(&[&flags(1, 0, 4, DEFAULTS).to_le_bytes(), &u16s(&[40]), &[0], b"short"])), None),
        ("empty record", rec(0x01BE, &[]), None),
        // A formula that can't be decoded (an unknown token).
        ("unknown token", rule(7, 0, 0, &[0xFF], &[], &[[1, 5, 1, 1]]), None),
        ("no ranges", rule(1, 0, 4, &hex("1e0000"), &[], &[]), None),
    ];
    for (what, bad, want) in cases {
        let (wb, report) = workbook(&[], &[dval(3), ok(10), bad, ok(11)]);
        let got = &wb.sheets[0].validations;
        let ranges: Vec<Range> = got.iter().flat_map(|v| v.ranges.clone()).collect();
        let mut expect = vec![range("A11")];
        expect.extend(want.map(range));
        expect.push(range("A12"));
        assert_eq!(ranges, expect, "{what}: {:?}", report.warnings);
        assert_eq!(report.warnings.iter().any(|w| w == SKIPPED), want.is_none(), "{what}: {:?}", report.warnings);
        if what == "unknown type" {
            assert_eq!((got[1].kind, got[1].formula1.as_str(), got[1].formula2.as_deref()), (ValidationType::Any, "", None));
        }
    }
}

#[test]
fn hostile_validation_records_never_panic() {
    let f = flags(1, 0, 4, DEFAULTS).to_le_bytes();
    let big = [
        rec(0x01B2, &[0xFF; 18]),
        rec(0x01BE, &cat(&[&f, &u16s(&[0xFFFF]), &[1]])),
        rec(0x01BE, &cat(&[&f, &no_msgs().concat(), &u16s(&[0xFFFF, 0xFFFF])])),
        rec(0x01BE, &cat(&[&f, &no_msgs().concat(), &formula(&[]), &formula(&[]), &u16s(&[0xFFFF])])),
        // A literal list whose PtgStr claims 255 UTF-16 characters and holds none.
        rec(
            0x01BE,
            &cat(&[
                &flags(3, 0, 0, DEFAULTS | STR_LOOKUP).to_le_bytes(),
                &no_msgs().concat(),
                &formula(&[0x17, 0xFF, 1]),
                &formula(&[]),
                &sqref(&[[0, 0, 0, 0]]),
            ]),
        ),
        // Every flag bit set: type 15, alert 7, operator 15.
        rec(0x01BE, &cat(&[&[0xFF; 4], &no_msgs().concat(), &formula(&[]), &formula(&[]), &sqref(&[[0, 0, 0, 0]])])),
    ];
    let (wb, _) = workbook(&[], &big);
    assert!(wb.sheets[0].validations.is_empty());
    let mut many = vec![dval(u32::MAX)];
    many.extend((0..200u16).map(|r| rule(1, 0, 4, &hex("1e0000"), &[], &[[r, r, 0, 0]])));
    let (wb, _) = workbook(&[], &many);
    assert_eq!(wb.sheets[0].validations.len(), 200);
    assert_eq!(wb.sheets[0].validations[199].ranges, vec![Range::new(CellRef::new(199, 0), CellRef::new(199, 0))]);
}

#[test]
fn rules_past_the_cap_are_skipped() {
    // A sheet keeps 65,536 rules; one more is skipped with a warning, not kept.
    let one = rule(1, 0, 4, &hex("1e0000"), &[], &[[0, 0, 0, 0]]);
    let mut records = vec![dval(65_537)];
    records.extend(std::iter::repeat_n(one, 65_537));
    let (wb, report) = workbook(&[], &records);
    assert_eq!(wb.sheets[0].validations.len(), 65_536);
    assert_eq!(report.warnings, [SKIPPED]);
}