XLFormula Engine

XLFormula Engine 是一个用于解析和评估 Excel 公式的 Rust 包。它目前支持 f32 类型。

功能

它支持

• 任何数字，负数和正数，作为浮点数或整数
• 算术运算 +, -, /, *, ^
• 逻辑运算 AND(), OR(), NOT(), XOR()
• 比较运算 =, >, >=, <, <=, <>
• 字符串运算 & (连接)
• 内置变量 TRUE, FALSE
• Excel 函数 ABS(), SUM(), PRODUCT(), AVERAGE(), RIGHT(), LEFT(), IF(), ISBLANK()
• 值列表上的操作（一维范围）
• 添加或减去日期和 Excel 函数 DAYS()
• 带数字参数的自定义函数
• 处理计算中的空白/空值
• 将函数调用中空/缺失的参数视为空白值

安装

将相应的条目添加到您的 Cargo.toml 依赖项列表中

[dependencies]
xlformula_engine = "0.1.17"

并在您的包根目录中添加以下内容

extern crate xlformula_engine;

示例

以下是一些解析 Excel 公式字符串并将其评估为结果的简单示例

extern crate xlformula_engine;
use xlformula_engine::calculate;
use xlformula_engine::parse_formula;
use xlformula_engine::NoReference;
use xlformula_engine::NoCustomFunction;

fn main() {
let formula = parse_formula::parse_string_to_formula(&"=1+2", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));

let formula = parse_formula::parse_string_to_formula(&"=(1*(2+3))*2", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));

let formula = parse_formula::parse_string_to_formula(&"=1+3/0", None::<NoCustomFunction>); // error (#DIV/0!)
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));
}

最后一个字符串评估为 #DIV/0!。

连接字符串

extern crate xlformula_engine;
use xlformula_engine::calculate;
use xlformula_engine::parse_formula;
use xlformula_engine::NoReference;
use xlformula_engine::NoCustomFunction;

fn main() {
let formula = parse_formula::parse_string_to_formula(&"=\"Hello \" & \" World!\"", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));

let formula = parse_formula::parse_string_to_formula(&"=1 + \"Hello\"", None::<NoCustomFunction>); // error (#CAST!)
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));
}

连接数字和字符串会导致 #CAST! 错误。

常量（即不含 '=' 的字符串）

extern crate xlformula_engine;
use xlformula_engine::calculate;
use xlformula_engine::parse_formula;
use xlformula_engine::NoReference;
use xlformula_engine::NoCustomFunction;

fn main() {
let formula = parse_formula::parse_string_to_formula(&"1.2", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));

let formula = parse_formula::parse_string_to_formula(&"Hello World", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));
}

Excel 函数

extern crate xlformula_engine;
use xlformula_engine::calculate;
use xlformula_engine::parse_formula;
use xlformula_engine::NoReference;
use xlformula_engine::NoCustomFunction;

fn main() {
let formula = parse_formula::parse_string_to_formula(&"=ABS(-1)", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));

let formula = parse_formula::parse_string_to_formula(&"=SUM(1,2,\"3\")", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));

let formula = parse_formula::parse_string_to_formula(&"=PRODUCT(ABS(1),2*1, 3,4*1)", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));

let formula = parse_formula::parse_string_to_formula(&"=RIGHT(\"apple\", 3)", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));

let formula = parse_formula::parse_string_to_formula(&"=LEFT(\"apple\", 3)", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));

let formula = parse_formula::parse_string_to_formula(&"=LEFT(\"apple\")", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));

let formula = parse_formula::parse_string_to_formula(&"=IF(TRUE,1,0)", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));
}

逻辑表达式

extern crate xlformula_engine;
use xlformula_engine::calculate;
use xlformula_engine::parse_formula;
use xlformula_engine::NoReference;
use xlformula_engine::NoCustomFunction;

fn main() {
let formula = parse_formula::parse_string_to_formula(&"=2>=1", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));

let formula = parse_formula::parse_string_to_formula(&"=OR(1>1,1<>1)", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));

let formula = parse_formula::parse_string_to_formula(&"=AND(\"test\",\"True\", 1, true) ", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));
}

引用

extern crate xlformula_engine;
use xlformula_engine::calculate;
use xlformula_engine::parse_formula;
use xlformula_engine::types;
use xlformula_engine::NoReference;
use xlformula_engine::NoCustomFunction;

fn main() {
let data_function = |s: String| match s.as_str() {
"A" => types::Value::Text("=1+B".to_string()),
"B" => types::Value::Number(3.0),
_ => types::Value::Error(types::Error::Value),
};
let formula = parse_formula::parse_string_to_formula(&"=A+B", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, Some(&data_function));
println!("Result is {}", calculate::result_to_string(result));
}

列表

extern crate xlformula_engine;
use xlformula_engine::calculate;
use xlformula_engine::parse_formula;
use xlformula_engine::NoReference;
use xlformula_engine::NoCustomFunction;

fn main() {
let formula = parse_formula::parse_string_to_formula(&"={1,2,3}+{1,2,3}", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));    

let formula = parse_formula::parse_string_to_formula(&"=XOR({0,0,0})", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));

let formula = parse_formula::parse_string_to_formula(&"=AVERAGE({1,2,3},1,2,3)", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));
}

日期

extern crate xlformula_engine;
use xlformula_engine::calculate;
use xlformula_engine::parse_formula;
use xlformula_engine::types;
use chrono::format::ParseError;
use chrono::{DateTime, FixedOffset};
use xlformula_engine::NoReference;
use xlformula_engine::NoCustomFunction;


fn main() -> Result<(), ParseError> {
let start: DateTime<FixedOffset> = DateTime::parse_from_rfc3339("2019-03-01T02:00:00.000Z")?;
let end: DateTime<FixedOffset> = DateTime::parse_from_rfc3339("2019-08-30T02:00:00.000Z")?;
let data_function = |s: String| match s.as_str() {
"start" => types::Value::Date(start),
"end" => types::Value::Date(end),
_ => types::Value::Error(types::Error::Value),
};

let formula = parse_formula::parse_string_to_formula(&"=DAYS(end, start)", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, Some(&data_function));
println!("Result is {}", calculate::result_to_string(result));

let formula = parse_formula::parse_string_to_formula(&"=start+1", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, Some(&data_function));
println!("Result is {}", calculate::result_to_string(result));

let formula = parse_formula::parse_string_to_formula(&"=end-3", None::<NoCustomFunction>);
let result = calculate::calculate_formula(formula, Some(&data_function));
println!("Result is {}", calculate::result_to_string(result));
Ok(())
}

自定义函数

extern crate xlformula_engine;
use xlformula_engine::calculate;
use xlformula_engine::parse_formula;
use xlformula_engine::types;
use xlformula_engine::NoReference;

fn main() {
let custom_functions = |s: String, params: Vec<f32>| match s.as_str() {
"Increase" => types::Value::Number(params[0] + 1.0),
"SimpleSum" => types::Value::Number(params[0] + params[1]),
"EqualFive" => types::Value::Number(5.0),
_ => types::Value::Error(types::Error::Value),
};

let formula =
parse_formula::parse_string_to_formula(&"=Increase(1)+1", Some(&custom_functions));
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));

let formula =
parse_formula::parse_string_to_formula(&"=EqualFive()+1", Some(&custom_functions));
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));

let formula =
parse_formula::parse_string_to_formula(&"=SimpleSum(1,2)", Some(&custom_functions));
let result = calculate::calculate_formula(formula, None::<NoReference>);
println!("Result is {}", calculate::result_to_string(result));
}

处理计算中的空白

extern crate xlformula_engine;
use xlformula_engine::calculate;
use xlformula_engine::parse_formula;
use xlformula_engine::types;
use chrono::format::ParseError;
use chrono::{DateTime, FixedOffset};
use xlformula_engine::NoReference;
use xlformula_engine::NoCustomFunction;

fn main() -> {
    let data_function = |s: String| match s.as_str() {
        "B" => types::Value::Blank,
        _ => types::Value::Error(types::Error::Value),
    };

    let custom_functions = |s: String, params: Vec<f32>| match s.as_str() {
        "BLANK" => types::Value::Blank,
        _ => types::Value::Error(types::Error::Value),
    };

    let formula = parse_formula::parse_string_to_formula(&"=SUM(B, 1)", None::<NoCustomFunction>);
    let result = calculate::calculate_formula(formula, Some(&data_function));
    println!("Result is {}", calculate::result_to_string(result));

    let formula =
        parse_formula::parse_string_to_formula(&"=SUM(BLANK(), 1)", Some(&custom_functions));
    let result = calculate::calculate_formula(formula, None::<NoReference>);
    println!("Result is {}", calculate::result_to_string(result));

    //takes list as input
    let formula = parse_formula::parse_string_to_formula(&"=SUM({B, 1})", None::<NoCustomFunction>);
    let result = calculate::calculate_formula(formula, Some(&data_function));
    println!("Result is {}", calculate::result_to_string(result));
    let formula = parse_formula::parse_string_to_formula(&"=XOR({F,B,T,B,F,{F,B,T,B,F}})", None::<NoCustomFunction>);
    let result = calculate::calculate_formula(formula, Some(&data_function));
    println!("Result is {}", calculate::result_to_string(result));

    let formula = parse_formula::parse_string_to_formula(&"=SUM(1, 2, , 3)", None::<NoCustomFunction>);
    let result = calculate::calculate_formula(formula, None::<NoReference>);
    println!("Result is {}", calculate::result_to_string(result));
}

许可证

许可协议为 MIT 许可证（请参阅 LICENSE 文件以获取完整内容）。

联系方式

请随时通过 [email protected] 联系我们。