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(), RIGHT(), LEFT()
• Operations on lists of values (one dimensional range)
• Add or subtract dates and excel funtion DAYS()
• Custom functions with number arguments
• Handle blank/null values in calculation

Installation

Add the corresponding entry to your Cargo.toml dependency list: toml [dependencies] xlformula_engine = "0.1.14" and add this to your crate root: rust extern crate xlformula_engine;

Examples

Here are simple examples of parsing an Excel formula string and evaluating to a result: ```rust extern crate xlformulaengine; use xlformulaengine::calculate; use xlformulaengine::parseformula; use xlformulaengine::NoReference; use xlformulaengine::NoCustomFunction;

fn main() { let formula = parseformula::parsestringtoformula(&"=1+2", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result));

let formula = parseformula::parsestringtoformula(&"=(1(2+3))2", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result));

let formula = parseformula::parsestringtoformula(&"=1+3/0", None::); // error (#DIV/0!) let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result)); } ``` The last string is evaluated to #DIV/0!.

Concatenating strings: ```rust extern crate xlformulaengine; use xlformulaengine::calculate; use xlformulaengine::parseformula; use xlformulaengine::NoReference; use xlformulaengine::NoCustomFunction;

fn main() { let formula = parseformula::parsestringtoformula(&"=\"Hello \" & \" World!\"", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result));

let formula = parseformula::parsestringtoformula(&"=1 + \"Hello\"", None::); // error (#CAST!) let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result)); } ``` Concatenating number and string results in a #CAST! error.

Constants ( i.e. a string without '=' ): ```rust extern crate xlformulaengine; use xlformulaengine::calculate; use xlformulaengine::parseformula; use xlformulaengine::NoReference; use xlformulaengine::NoCustomFunction;

fn main() { let formula = parseformula::parsestringtoformula(&"1.2", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result));

let formula = parseformula::parsestringtoformula(&"Hello World", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result)); } ```

Excel functions: ```rust extern crate xlformulaengine; use xlformulaengine::calculate; use xlformulaengine::parseformula; use xlformulaengine::NoReference; use xlformulaengine::NoCustomFunction;

fn main() { let formula = parseformula::parsestringtoformula(&"=ABS(-1)", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result));

let formula = parseformula::parsestringtoformula(&"=SUM(1,2,\"3\")", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result));

let formula = parseformula::parsestringtoformula(&"=PRODUCT(ABS(1),21, 3,41)", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result));

let formula = parseformula::parsestringtoformula(&"=RIGHT(\"apple\", 3)", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result));

let formula = parseformula::parsestringtoformula(&"=LEFT(\"apple\", 3)", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result));

let formula = parseformula::parsestringtoformula(&"=LEFT(\"apple\")", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result)); } ```

Logical expressions: ```rust extern crate xlformulaengine; use xlformulaengine::calculate; use xlformulaengine::parseformula; use xlformulaengine::NoReference; use xlformulaengine::NoCustomFunction;

fn main() { let formula = parseformula::parsestringtoformula(&"=2>=1", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result));

let formula = parseformula::parsestringtoformula(&"=OR(1>1,1<>1)", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result));

let formula = parseformula::parsestringtoformula(&"=AND(\"test\",\"True\", 1, true) ", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result)); } ```

References: ```rust extern crate xlformulaengine; use xlformulaengine::calculate; use xlformulaengine::parseformula; use xlformulaengine::types; use xlformulaengine::NoReference; use xlformula_engine::NoCustomFunction;

fn main() { let datafunction = |s: String| match s.asstr() { "A" => types::Value::Text("=1+B".tostring()), "B" => types::Value::Number(3.0), _ => types::Value::Error(types::Error::Value), }; let formula = parseformula::parsestringtoformula(&"=A+B", None::); let result = calculate::calculateformula(formula, Some(&datafunction)); println!("Result is {}", calculate::resultto_string(result)); } ```

List: ```rust extern crate xlformulaengine; use xlformulaengine::calculate; use xlformulaengine::parseformula; use xlformulaengine::NoReference; use xlformulaengine::NoCustomFunction;

fn main() { let formula = parseformula::parsestringtoformula(&"={1,2,3}+{1,2,3}", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result));

let formula = parseformula::parsestringtoformula(&"=XOR({0,0,0})", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result));

let formula = parseformula::parsestringtoformula(&"=AVERAGE({1,2,3},1,2,3)", None::); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resultto_string(result)); } ```

Date: ```rust extern crate xlformulaengine; use xlformulaengine::calculate; use xlformulaengine::parseformula; use xlformulaengine::types; use chrono::format::ParseError; use chrono::{DateTime, FixedOffset}; use xlformulaengine::NoReference; use xlformula_engine::NoCustomFunction;

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

let formula = parseformula::parsestringtoformula(&"=DAYS(end, start)", None::); let result = calculate::calculateformula(formula, Some(&datafunction)); println!("Result is {}", calculate::resulttostring(result));

let formula = parseformula::parsestringtoformula(&"=start+1", None::); let result = calculate::calculateformula(formula, Some(&datafunction)); println!("Result is {}", calculate::resulttostring(result));

let formula = parseformula::parsestringtoformula(&"=end-3", None::); let result = calculate::calculateformula(formula, Some(&datafunction)); println!("Result is {}", calculate::resulttostring(result)); Ok(()) } ```

Custom Function: ```rust extern crate xlformulaengine; use xlformulaengine::calculate; use xlformulaengine::parseformula; use xlformulaengine::types; use xlformulaengine::NoReference;

fn main() { let customfunctions = |s: String, params: Vec| match s.asstr() { "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 = parseformula::parsestringtoformula(&"=Increase(1)+1", Some(&customfunctions)); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resulttostring(result));

let formula = parseformula::parsestringtoformula(&"=EqualFive()+1", Some(&customfunctions)); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resulttostring(result));

let formula = parseformula::parsestringtoformula(&"=SimpleSum(1,2)", Some(&customfunctions)); let result = calculate::calculateformula(formula, None::); println!("Result is {}", calculate::resulttostring(result)); } ```

Handle blank in calculation: ```rust extern crate xlformulaengine; use xlformulaengine::calculate; use xlformulaengine::parseformula; use xlformulaengine::types; use chrono::format::ParseError; use chrono::{DateTime, FixedOffset}; use xlformulaengine::NoReference; use xlformula_engine::NoCustomFunction;

fn main() -> { let datafunction = |s: String| match s.asstr() { "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));

} ```

License

Licensed under MIT License (see the LICENSE file for the full text).

Contact

Please feel free to contact us at jirada.herbst@data2impact.com.