xlsx_batch_reader 0.4.21

// 集成测试
use std::collections::HashMap;
use xlsx_batch_reader::{get_num_from_ord, get_ord_from_num, get_tuple_from_ord, resolve_formula};

#[test]
pub fn test_ord_to_num() {
    assert!(get_num_from_ord("B3".as_bytes()).unwrap() == 2);
    assert!(get_num_from_ord("Z".as_bytes()).unwrap() == 26);
    assert!(get_num_from_ord("AB".as_bytes()).unwrap() == 28);

    assert!(get_ord_from_num(1).unwrap() == "A".to_string());
    assert!(get_ord_from_num(27).unwrap() == "AA".to_string());
    assert!(get_ord_from_num(37).unwrap() == "AK".to_string());

    assert!(get_tuple_from_ord("A1".as_bytes()).unwrap() == (1, 1));
    assert!(get_tuple_from_ord("B3".as_bytes()).unwrap() == (3, 2));
}

// ---- Formula resolution tests (without headercols) ----

#[test]
fn formula_col_basic() {
    assert_eq!(resolve_formula("${$c}", 5, 5, None).unwrap(), "E");
}

#[test]
fn formula_col_subtract() {
    assert_eq!(resolve_formula("${$c-3}", 5, 5, None).unwrap(), "B");
}

#[test]
fn formula_row_basic() {
    assert_eq!(resolve_formula("${$r}", 5, 5, None).unwrap(), "5");
}

#[test]
fn formula_row_subtract() {
    assert_eq!(resolve_formula("${$r-1}", 5, 5, None).unwrap(), "4");
}

#[test]
fn formula_col_add() {
    assert_eq!(resolve_formula("${$c+2}", 1, 3, None).unwrap(), "E");
}

#[test]
fn formula_row_add() {
    assert_eq!(resolve_formula("${$r+3}", 2, 1, None).unwrap(), "5");
}

#[test]
fn formula_parentheses() {
    assert_eq!(resolve_formula("${($c-3)+($c-3)}", 5, 5, None).unwrap(), "D");
}

#[test]
fn formula_full_sum() {
    let formula = "SUM(${$c-3}${$r-1}:${$c-1}${$r-1})";
    assert_eq!(resolve_formula(formula, 5, 5, None).unwrap(), "SUM(B4:D4)");
}

#[test]
fn formula_multiple_substitutions() {
    assert_eq!(
        resolve_formula("A${$c-2}:A${$c+2}", 1, 10, None).unwrap(),
        "AH:AL"
    );
}

#[test]
fn formula_no_substitution() {
    assert_eq!(resolve_formula("SUM(A1:B2)", 1, 1, None).unwrap(), "SUM(A1:B2)");
}

#[test]
fn formula_col_a() {
    assert_eq!(resolve_formula("${$c}", 1, 1, None).unwrap(), "A");
}

#[test]
fn formula_col_z() {
    assert_eq!(resolve_formula("${$c}", 1, 26, None).unwrap(), "Z");
}

#[test]
fn formula_col_aa() {
    assert_eq!(resolve_formula("${$c}", 1, 27, None).unwrap(), "AA");
}

#[test]
fn formula_col_large_add() {
    assert_eq!(resolve_formula("${$c+15}", 1, 5, None).unwrap(), "T");
}

#[test]
fn formula_col_large_subtract() {
    assert_eq!(resolve_formula("${$c-12}", 1, 20, None).unwrap(), "H");
}

#[test]
fn formula_row_large_add() {
    assert_eq!(resolve_formula("${$r+20}", 3, 1, None).unwrap(), "23");
}

#[test]
fn formula_row_large_subtract() {
    assert_eq!(resolve_formula("${$r-15}", 30, 1, None).unwrap(), "15");
}

#[test]
fn formula_large_offset_formula() {
    let formula = "SUM(${$c-15}${$r-10}:${$c+10}${$r+15})";
    assert_eq!(resolve_formula(formula, 15, 20, None).unwrap(), "SUM(E5:AD30)");
}

// ---- col() function tests (without headercols) ----

#[test]
fn formula_col_fn_basic() {
    assert_eq!(resolve_formula("${col(2)}", 1, 1, None).unwrap(), "B");
}

#[test]
fn formula_col_fn_aa() {
    assert_eq!(resolve_formula("${col(27)}", 1, 1, None).unwrap(), "AA");
}

#[test]
fn formula_col_fn_with_c() {
    assert_eq!(resolve_formula("${col($c-3)}", 5, 5, None).unwrap(), "B");
}

#[test]
fn formula_col_fn_in_formula() {
    let formula = "SUM(${col(2)}:${col(3)})";
    assert_eq!(resolve_formula(formula, 1, 1, None).unwrap(), "SUM(B:C)");
}

#[test]
fn formula_col_fn_arithmetic() {
    assert_eq!(resolve_formula("${col(1+2)}", 1, 1, None).unwrap(), "C");
}

#[test]
fn formula_col_fn_out_of_range() {
    let err = resolve_formula("${col(0)}", 1, 1, None).unwrap_err();
    assert!(err.to_string().contains("out of range"));
}

// ---- col() with headercols: part1!part2 ----

fn make_headercols<'a>(
    sheets: &'a Vec<String>,
    map: &'a HashMap<String, HashMap<String, String>>,
) -> Option<(&'a Vec<String>, &'a HashMap<String, HashMap<String, String>>)> {
    Some((sheets, map))
}

#[test]
fn formula_col_fn_header_index() {
    let sheets = vec!["Sheet1".into(), "Sheet2".into()];
    let mut sheet1: HashMap<String, String> = HashMap::new();
    sheet1.insert("金额".into(), "3".into());
    sheet1.insert("数量".into(), "2".into());
    let mut headers: HashMap<String, HashMap<String, String>> = HashMap::new();
    headers.insert("Sheet1".into(), sheet1);

    // col(0!金额) → index 0 = Sheet1, header "金额" = col 3 → C
    assert_eq!(
        resolve_formula("${col(0!金额)}", 1, 1, make_headercols(&sheets, &headers)).unwrap(),
        "C"
    );
}

#[test]
fn formula_col_fn_header_name() {
    let mut sheet1: HashMap<String, String> = HashMap::new();
    sheet1.insert("数量".into(), "5".into());
    let mut headers: HashMap<String, HashMap<String, String>> = HashMap::new();
    headers.insert("Sheet1".into(), sheet1);

    // col(Sheet1!数量) → sheet "Sheet1", header "数量" = col 5 → E
    assert_eq!(
        resolve_formula("${col(Sheet1!数量)}", 1, 1, make_headercols(&vec![], &headers)).unwrap(),
        "E"
    );
}

#[test]
fn formula_col_fn_header_in_formula() {
    let sheets = vec!["Data".into()];
    let mut sheet1: HashMap<String, String> = HashMap::new();
    sheet1.insert("金额".into(), "2".into());
    sheet1.insert("税率".into(), "4".into());
    let mut headers: HashMap<String, HashMap<String, String>> = HashMap::new();
    headers.insert("Data".into(), sheet1);

    let formula = "SUM(${col(0!金额)}:${col(0!税率)})";
    assert_eq!(
        resolve_formula(formula, 1, 1, make_headercols(&sheets, &headers)).unwrap(),
        "SUM(B:D)"
    );
}

#[test]
fn formula_col_fn_header_index_fallback() {
    // Index 5 out of bounds → fallback to literal "5" as sheet name
    let sheets = vec!["Sheet1".into()];
    let mut headers: HashMap<String, HashMap<String, String>> = HashMap::new();
    headers.insert("5".into(), {
        let mut m = HashMap::new();
        m.insert("X".into(), "7".into());
        m
    });

    assert_eq!(
        resolve_formula("${col(5!X)}", 1, 1, make_headercols(&sheets, &headers)).unwrap(),
        "G"
    );
}

#[test]
fn formula_col_fn_header_not_found() {
    let sheets = vec!["Sheet1".into()];
    let headers: HashMap<String, HashMap<String, String>> = HashMap::new();

    let err = resolve_formula("${col(0!Missing)}", 1, 1, make_headercols(&sheets, &headers)).unwrap_err();
    assert!(err.to_string().contains("not found"));
}

// ---- Error tests ----

#[test]
fn formula_error_col_out_of_range() {
    let err = resolve_formula("${$c-3}", 1, 1, None).unwrap_err();
    assert!(err.to_string().contains("out of range"));
}

#[test]
fn formula_error_row_out_of_range() {
    let err = resolve_formula("${$r-3}", 1, 1, None).unwrap_err();
    assert!(err.to_string().contains("out of range"));
}

#[test]
fn formula_error_mixed_variables() {
    let err = resolve_formula("${$r+$c}", 1, 1, None).unwrap_err();
    assert!(err.to_string().contains("cannot mix"));
}

#[test]
fn formula_error_empty_expression() {
    let err = resolve_formula("${}", 1, 1, None).unwrap_err();
    assert!(err.to_string().contains("empty"));
}

#[test]
fn formula_error_invalid_syntax() {
    let err = resolve_formula("${$c+}", 1, 1, None).unwrap_err();
    assert!(err.to_string().contains("unexpected"));
}