Crate xlformula_engine[−][src]
XLFormula Engine
XLFormula Engine is a Rust crate for parsing and evaluating Excel formulas. It currently works with f32 types.
Features
It supports:
- Any numbers, negative and positive, as float or integer;
- Arithmetic operations +, -, /, *, ^;
- Logical operations AND(), OR(), NOT(), XOR();
- Comparison operations =, >, >=, <, <=, <>;
- String operation & (concatenation);
- Build-in variables TRUE, FALSE;
- Excel functions ABS(), SUM(), PRODUCT(), AVERAGE();
- Operations on lists of values (one dimensional range);
- Add or subtract dates and excel funtion DAYS();
- Custom functions with number arguments.
Installation
Add the corresponding entry to your Cargo.toml dependency list:
[dependencies]
xlformula_engine = "0.1.10"
and add this to your crate root:
extern crate xlformula_engine;
Examples
Here are simple examples of parsing an Excel formula string and evaluating to a result:
extern crate xlformula_engine; use xlformula_engine::calculate; use xlformula_engine::parse_formula; use xlformula_engine::NoReference; use xlformula_engine::NoCustomFunction; 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));
The last string is evaluated to #DIV/0!.
Concatenating strings:
extern crate xlformula_engine; use xlformula_engine::calculate; use xlformula_engine::parse_formula; use xlformula_engine::NoReference; use xlformula_engine::NoCustomFunction; 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));
Concatenating number and string results in a #CAST! error.
Constants ( i.e. a string without ‘=’ ):
extern crate xlformula_engine; use xlformula_engine::calculate; use xlformula_engine::parse_formula; use xlformula_engine::NoReference; use xlformula_engine::NoCustomFunction; 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 functions:
extern crate xlformula_engine; use xlformula_engine::calculate; use xlformula_engine::parse_formula; use xlformula_engine::NoReference; use xlformula_engine::NoCustomFunction; 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));
Logical expressions:
extern crate xlformula_engine; use xlformula_engine::calculate; use xlformula_engine::parse_formula; use xlformula_engine::NoReference; use xlformula_engine::NoCustomFunction; 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));
References:
extern crate xlformula_engine; use xlformula_engine::calculate; use xlformula_engine::parse_formula; use xlformula_engine::types; use xlformula_engine::NoCustomFunction; 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));
List:
extern crate xlformula_engine; use xlformula_engine::calculate; use xlformula_engine::parse_formula; use xlformula_engine::NoReference; use xlformula_engine::NoCustomFunction; 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));
Date:
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::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(()) }
Custom Function:
extern crate xlformula_engine; use xlformula_engine::calculate; use xlformula_engine::parse_formula; use xlformula_engine::types; use xlformula_engine::NoReference; 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));
Modules
calculate | Evaluates a formula. |
parse_formula | Parses a string using |
types | The Structs and Enums for the calculation. |
Type Definitions
NoCustomFunction | |
NoReference |