xlformula_engine/lib.rs
1//!# XLFormula Engine
2//!XLFormula Engine is a Rust crate for parsing and evaluating Excel formulas. It currently works with f32 types.
3//!
4//!## Features
5//!It supports:
6//!
7//!* Any numbers, negative and positive, as float or integer
8//!* Arithmetic operations +, -, /, *, ^
9//!* Logical operations AND(), OR(), NOT(), XOR()
10//!* Comparison operations =, >, >=, <, <=, <>
11//!* String operation & (concatenation)
12//!* Build-in variables TRUE, FALSE
13//!* Excel functions ABS(), SUM(), PRODUCT(), AVERAGE(), RIGHT(), LEFT(), IF(), ISBLANK()
14//!* Operations on lists of values (one dimensional range)
15//!* Add or subtract dates and excel funtion DAYS()
16//!* Custom functions with number arguments
17//!* Handle blank/null values in calculation
18//!* Handle empty/missing parameters of function calls as blank values
19//!
20//!## Installation
21//!
22//!Add the corresponding entry to your Cargo.toml dependency list:
23//!```toml
24//![dependencies]
25//!xlformula_engine = "0.1.17"
26//!```
27//!and add this to your crate root:
28//!```rust
29//!extern crate xlformula_engine;
30//!```
31//!
32//!## Examples
33//!
34//!Here are simple examples of parsing an Excel formula string and evaluating to a result:
35//!```rust
36//!extern crate xlformula_engine;
37//!use xlformula_engine::calculate;
38//!use xlformula_engine::parse_formula;
39//!use xlformula_engine::NoReference;
40//!use xlformula_engine::NoCustomFunction;
41//!
42//!let formula = parse_formula::parse_string_to_formula(&"=1+2", None::<NoCustomFunction>);
43//!let result = calculate::calculate_formula(formula, None::<NoReference>);
44//!println!("Result is {}", calculate::result_to_string(result));
45//!
46//!let formula = parse_formula::parse_string_to_formula(&"=(1*(2+3))*2", None::<NoCustomFunction>);
47//!let result = calculate::calculate_formula(formula, None::<NoReference>);
48//!println!("Result is {}", calculate::result_to_string(result));
49//!
50//!let formula = parse_formula::parse_string_to_formula(&"=1+3/0", None::<NoCustomFunction>); // error (#DIV/0!)
51//!let result = calculate::calculate_formula(formula, None::<NoReference>);
52//!println!("Result is {}", calculate::result_to_string(result));
53//!```
54//!The last string is evaluated to #DIV/0!.
55//!
56//!Concatenating strings:
57//!```rust
58//!extern crate xlformula_engine;
59//!use xlformula_engine::calculate;
60//!use xlformula_engine::parse_formula;
61//!use xlformula_engine::NoReference;
62//!use xlformula_engine::NoCustomFunction;
63//!
64//!let formula = parse_formula::parse_string_to_formula(&"=\"Hello \" & \" World!\"", None::<NoCustomFunction>);
65//!let result = calculate::calculate_formula(formula, None::<NoReference>);
66//!println!("Result is {}", calculate::result_to_string(result));
67//!
68//!let formula = parse_formula::parse_string_to_formula(&"=1 + \"Hello\"", None::<NoCustomFunction>); // error (#CAST!)
69//!let result = calculate::calculate_formula(formula, None::<NoReference>);
70//!println!("Result is {}", calculate::result_to_string(result));
71//!```
72//!Concatenating number and string results in a #CAST! error.
73//!
74//!Constants ( i.e. a string without '=' ):
75//!```rust
76//!extern crate xlformula_engine;
77//!use xlformula_engine::calculate;
78//!use xlformula_engine::parse_formula;
79//!use xlformula_engine::NoReference;
80//!use xlformula_engine::NoCustomFunction;
81//!
82//!let formula = parse_formula::parse_string_to_formula(&"1.2", None::<NoCustomFunction>);
83//!let result = calculate::calculate_formula(formula, None::<NoReference>);
84//!println!("Result is {}", calculate::result_to_string(result));
85//!
86//!let formula = parse_formula::parse_string_to_formula(&"Hello World", None::<NoCustomFunction>);
87//!let result = calculate::calculate_formula(formula, None::<NoReference>);
88//!println!("Result is {}", calculate::result_to_string(result));
89//!```
90//!
91//!Excel functions:
92//!```rust
93//!extern crate xlformula_engine;
94//!use xlformula_engine::calculate;
95//!use xlformula_engine::parse_formula;
96//!use xlformula_engine::NoReference;
97//!use xlformula_engine::NoCustomFunction;
98//!
99//!let formula = parse_formula::parse_string_to_formula(&"=ABS(-1)", None::<NoCustomFunction>);
100//!let result = calculate::calculate_formula(formula, None::<NoReference>);
101//!println!("Result is {}", calculate::result_to_string(result));
102//!
103//!let formula = parse_formula::parse_string_to_formula(&"=SUM(1,2,\"3\")", None::<NoCustomFunction>);
104//!let result = calculate::calculate_formula(formula, None::<NoReference>);
105//!println!("Result is {}", calculate::result_to_string(result));
106//!
107//!let formula = parse_formula::parse_string_to_formula(&"=PRODUCT(ABS(1),2*1, 3,4*1)", None::<NoCustomFunction>);
108//!let result = calculate::calculate_formula(formula, None::<NoReference>);
109//!println!("Result is {}", calculate::result_to_string(result));
110//!
111//!let formula = parse_formula::parse_string_to_formula(&"=RIGHT(\"apple\", 3)", None::<NoCustomFunction>);
112//!let result = calculate::calculate_formula(formula, None::<NoReference>);
113//!println!("Result is {}", calculate::result_to_string(result));
114//!
115//!let formula = parse_formula::parse_string_to_formula(&"=LEFT(\"apple\", 3)", None::<NoCustomFunction>);
116//!let result = calculate::calculate_formula(formula, None::<NoReference>);
117//!println!("Result is {}", calculate::result_to_string(result));
118//!
119//!let formula = parse_formula::parse_string_to_formula(&"=LEFT(\"apple\")", None::<NoCustomFunction>);
120//!let result = calculate::calculate_formula(formula, None::<NoReference>);
121//!println!("Result is {}", calculate::result_to_string(result));
122//!
123//!let formula = parse_formula::parse_string_to_formula(&"=IF(TRUE,1,0)", None::<NoCustomFunction>);
124//!let result = calculate::calculate_formula(formula, None::<NoReference>);
125//!println!("Result is {}", calculate::result_to_string(result));
126//!```
127//!
128//!Logical expressions:
129//!```rust
130//!extern crate xlformula_engine;
131//!use xlformula_engine::calculate;
132//!use xlformula_engine::parse_formula;
133//!use xlformula_engine::NoReference;
134//!use xlformula_engine::NoCustomFunction;
135//!
136//!let formula = parse_formula::parse_string_to_formula(&"=2>=1", None::<NoCustomFunction>);
137//!let result = calculate::calculate_formula(formula, None::<NoReference>);
138//!println!("Result is {}", calculate::result_to_string(result));
139//!
140//!let formula = parse_formula::parse_string_to_formula(&"=OR(1>1,1<>1)", None::<NoCustomFunction>);
141//!let result = calculate::calculate_formula(formula, None::<NoReference>);
142//!println!("Result is {}", calculate::result_to_string(result));
143//!
144//!let formula = parse_formula::parse_string_to_formula(&"=AND(\"test\",\"True\", 1, true) ", None::<NoCustomFunction>);
145//!let result = calculate::calculate_formula(formula, None::<NoReference>);
146//!println!("Result is {}", calculate::result_to_string(result));
147//!```
148//!
149//!References:
150//!```rust
151//!extern crate xlformula_engine;
152//!use xlformula_engine::calculate;
153//!use xlformula_engine::parse_formula;
154//!use xlformula_engine::types;
155//!use xlformula_engine::NoCustomFunction;
156//!
157//!let data_function = |s: String| match s.as_str() {
158//!"A" => types::Value::Text("=1+B".to_string()),
159//!"B" => types::Value::Number(3.0),
160//!_ => types::Value::Error(types::Error::Value),
161//!};
162//!let formula = parse_formula::parse_string_to_formula(&"=A+B", None::<NoCustomFunction>);
163//!let result = calculate::calculate_formula(formula, Some(&data_function));
164//!println!("Result is {}", calculate::result_to_string(result));
165//!```
166//!
167//!List:
168//!```rust
169//!extern crate xlformula_engine;
170//!use xlformula_engine::calculate;
171//!use xlformula_engine::parse_formula;
172//!use xlformula_engine::NoReference;
173//!use xlformula_engine::NoCustomFunction;
174//!
175//!let formula = parse_formula::parse_string_to_formula(&"={1,2,3}+{1,2,3}", None::<NoCustomFunction>);
176//!let result = calculate::calculate_formula(formula, None::<NoReference>);
177//!println!("Result is {}", calculate::result_to_string(result));
178//!
179//!let formula = parse_formula::parse_string_to_formula(&"=XOR({0,0,0})", None::<NoCustomFunction>);
180//!let result = calculate::calculate_formula(formula, None::<NoReference>);
181//!println!("Result is {}", calculate::result_to_string(result));
182//!
183//!let formula = parse_formula::parse_string_to_formula(&"=AVERAGE({1,2,3},1,2,3)", None::<NoCustomFunction>);
184//!let result = calculate::calculate_formula(formula, None::<NoReference>);
185//!println!("Result is {}", calculate::result_to_string(result));
186//!```
187//!
188//!Date:
189//!```rust
190//!extern crate xlformula_engine;
191//!use xlformula_engine::calculate;
192//!use xlformula_engine::parse_formula;
193//!use xlformula_engine::types;
194//!use chrono::format::ParseError;
195//!use chrono::{DateTime, FixedOffset};
196//!use xlformula_engine::NoCustomFunction;
197//!
198//!fn main() -> Result<(), ParseError> {
199//!let start: DateTime<FixedOffset> = DateTime::parse_from_rfc3339("2019-03-01T02:00:00.000Z")?;
200//!let end: DateTime<FixedOffset> = DateTime::parse_from_rfc3339("2019-08-30T02:00:00.000Z")?;
201//!let data_function = |s: String| match s.as_str() {
202//!"start" => types::Value::Date(start),
203//!"end" => types::Value::Date(end),
204//!_ => types::Value::Error(types::Error::Value),
205//!};
206//!
207//!let formula = parse_formula::parse_string_to_formula(&"=DAYS(end, start)", None::<NoCustomFunction>);
208//!let result = calculate::calculate_formula(formula, Some(&data_function));
209//!println!("Result is {}", calculate::result_to_string(result));
210//!
211//!let formula = parse_formula::parse_string_to_formula(&"=start+1", None::<NoCustomFunction>);
212//!let result = calculate::calculate_formula(formula, Some(&data_function));
213//!println!("Result is {}", calculate::result_to_string(result));
214//!
215//!let formula = parse_formula::parse_string_to_formula(&"=end-3", None::<NoCustomFunction>);
216//!let result = calculate::calculate_formula(formula, Some(&data_function));
217//!println!("Result is {}", calculate::result_to_string(result));
218//! Ok(())
219//!}
220//!```
221//!
222//!Custom Function:
223//!```rust
224//!extern crate xlformula_engine;
225//!use xlformula_engine::calculate;
226//!use xlformula_engine::parse_formula;
227//!use xlformula_engine::types;
228//!use xlformula_engine::NoReference;
229//!
230//!let custom_functions = |s: String, params: Vec<f32>| match s.as_str() {
231//!"Increase" => types::Value::Number(params[0] + 1.0),
232//!"SimpleSum" => types::Value::Number(params[0] + params[1]),
233//!"EqualFive" => types::Value::Number(5.0),
234//!_ => types::Value::Error(types::Error::Value),
235//!};
236//!
237//!let formula =
238//!parse_formula::parse_string_to_formula(&"=Increase(1)+1", Some(&custom_functions));
239//!let result = calculate::calculate_formula(formula, None::<NoReference>);
240//!println!("Result is {}", calculate::result_to_string(result));
241//!
242//!let formula =
243//!parse_formula::parse_string_to_formula(&"=EqualFive()+1", Some(&custom_functions));
244//!let result = calculate::calculate_formula(formula, None::<NoReference>);
245//!println!("Result is {}", calculate::result_to_string(result));
246//!
247//!let formula =
248//!parse_formula::parse_string_to_formula(&"=SimpleSum(1,2)", Some(&custom_functions));
249//!let result = calculate::calculate_formula(formula, None::<NoReference>);
250//!println!("Result is {}", calculate::result_to_string(result));
251//!```
252//!
253//!Handle blank in calculation:
254//!```rust
255//!extern crate xlformula_engine;
256//!use xlformula_engine::calculate;
257//!use xlformula_engine::parse_formula;
258//!use xlformula_engine::types;
259//!use chrono::format::ParseError;
260//!use chrono::{DateTime, FixedOffset};
261//!use xlformula_engine::NoReference;
262//!use xlformula_engine::NoCustomFunction;
263//!
264//!let data_function = |s: String| match s.as_str() {
265//!"B" => types::Value::Blank,
266//!_ => types::Value::Error(types::Error::Value),
267//!};
268//!
269//!let custom_functions = |s: String, params: Vec<f32>| match s.as_str() {
270//!"BLANK" => types::Value::Blank,
271//!_ => types::Value::Error(types::Error::Value),
272//!};
273//!
274//!let formula = parse_formula::parse_string_to_formula(&"=SUM(B, 1)", None::<NoCustomFunction>);
275//!let result = calculate::calculate_formula(formula, Some(&data_function));
276//!println!("Result is {}", calculate::result_to_string(result));
277//!
278//!let formula =
279//!parse_formula::parse_string_to_formula(&"=SUM(BLANK(), 1)", Some(&custom_functions));
280//!let result = calculate::calculate_formula(formula, None::<NoReference>);
281//!println!("Result is {}", calculate::result_to_string(result));
282//!
283//!//takes list as input
284//!let formula = parse_formula::parse_string_to_formula(&"=SUM({B, 1})", None::<NoCustomFunction>);
285//!let result = calculate::calculate_formula(formula, Some(&data_function));
286//!println!("Result is {}", calculate::result_to_string(result));
287//!
288//!let formula =
289//!parse_formula::parse_string_to_formula(&"=SUM(1, 2, , 3)", None::<NoCustomFunction>);
290//!let result = calculate::calculate_formula(formula, None::<NoReference>);
291//!println!("Result is {}", calculate::result_to_string(result));
292//!```
293
294#[macro_use]
295extern crate pest_derive;
296
297/// Evaluates a formula.
298pub mod calculate;
299
300/// The Structs and Enums for the calculation.
301pub mod types;
302
303/// Parses a string using `pest` and `pest::prec_climber`.
304pub mod parse_formula;
305
306pub type NoReference<'a> = &'a fn(String) -> types::Value;
307pub type NoCustomFunction<'a> = &'a fn(String, Vec<f32>) -> types::Value;