excel_lib/function/
mod.rs

1pub mod xirr; 
2
3use crate::{
4    evaluate::{
5        evaluate_expr_with_context, 
6        evaluate_str, 
7        ensure_non_range,
8        value::Value, 
9    }, 
10    reference::Reference, 
11    cell::Cell, 
12    errors::Error, 
13    parser::ast::{Expr, Error as ExcelError},  
14    workbook::Book,
15}; 
16use excel_emulator_macro::function; 
17use chrono::{Months, naive::NaiveDate, Datelike}; 
18
19pub fn get_function_value(name: &str, args: Vec<Value>) -> Result<Value, Error> {
20    match name {
21		"SUM" => Ok(Box::new(Sum::from(args)).evaluate()), 
22		"SUMIF" => Ok(Box::new(Sumifs::from(args)).evaluate()), 
23		"AVERAGE" => Ok(Box::new(Average::from(args)).evaluate()), 
24		"AVERAGEIF" => Ok(Box::new(Averageif::from(args)).evaluate()), 
25		"COUNT" => Ok(Box::new(Count::from(args)).evaluate()),	
26		"EXPONENT" => Ok(Box::new(Exponent::from(args)).evaluate()),	
27		"CONCAT" => Ok(Box::new(Concat::from(args)).evaluate()),	
28		"AND" => Ok(Box::new(Andfunc::from(args)).evaluate()),	
29		"OR" => Ok(Box::new(Orfunc::from(args)).evaluate()),	
30		"MAX" => Ok(Box::new(Max::from(args)).evaluate()),	
31		"MIN" => Ok(Box::new(Min::from(args)).evaluate()),	
32		"MATCH" => Ok(Box::new(Matchfn::from(args)).evaluate()),	
33		"DATE" => Ok(Box::new(Date::from(args)).evaluate()),	
34		"FLOOR" => Ok(Box::new(Floor::from(args)).evaluate()),	
35		"IFERROR" => {
36            let a = args.get(0).unwrap().clone(); 
37            let b = args.get(1).unwrap().clone(); 
38            Ok(Box::new(Iferror { a, b }).evaluate())
39        },	
40		"EOMONTH" => Ok(Box::new(Eomonth::from(args)).evaluate()),	
41		"SUMIFS" => Ok(Box::new(Sumifs::from(args)).evaluate()),	
42		"AVERAGEIFS" => Ok(Box::new(Averageifs::from(args)).evaluate()),	
43		"XIRR" => Ok(Box::new(Xirrfunc::from(args)).evaluate()),	
44		"IF" => Ok(Box::new(Iffunc::from(args)).evaluate()),	
45		"XNPV" => Ok(Box::new(Xnpv::from(args)).evaluate()),	
46		"YEARFRAC" => Ok(Box::new(Yearfrac::from(args)).evaluate()),	
47		"DATEDIF" => Ok(Box::new(Datedif::from(args)).evaluate()),	
48		"PMT" => Ok(Box::new(Pmt::from(args)).evaluate()),	
49		"COUNTA" => Ok(Box::new(Counta::from(args)).evaluate()),	
50		"ROUNDDOWN" => Ok(Box::new(Rounddown::from(args)).evaluate()),	
51        _ => Err(Error::FunctionNotSupport(name.to_string()))
52    }
53}
54
55pub trait Function {
56   fn evaluate(self) -> Value; 
57}
58
59pub fn offset_reference(r: &mut Reference, rows: i32, cols: i32, height: Option<i32>, width: Option<i32>) -> Reference {
60    if r.row() as i32 + rows < 0 || r.column() as i32 + cols < 0 {
61        panic!("Invalid offset");
62    } else {
63        r.offset((rows, cols));
64    }
65    let mut end_cell : Option<Cell> = None;  
66    if height.is_some() || width.is_some() {
67        let h_u : i32 = height.unwrap_or(0); 
68        let w_u : i32 = width.unwrap_or(0); 
69        if h_u.abs() > 1 || w_u.abs() > 1 {
70            let h_offset = match h_u.is_positive() {
71                true => h_u - 1, 
72                false => h_u + 1
73            }; 
74            let w_offset = match w_u.is_positive() {
75                true => w_u - 1, 
76                false => w_u + 1
77            }; 
78            end_cell = Some(
79               Cell::from((
80                        (r.row() as i32 + h_offset) as usize, 
81                        (r.column() as i32 + w_offset) as usize
82                ))
83            ); 
84        }
85    }
86    r.end_cell = end_cell; 
87    if let Some(end_cell) = r.end_cell {
88        if end_cell < r.start_cell {
89            return Reference::from((end_cell, Some(r.start_cell))); 
90        } 
91    }
92    *r
93}
94
95#[function]
96fn exponent(a: Value, b: Value) -> Value {
97    Value::from(a.as_num().powf(b.as_num()))
98}
99
100#[function]
101fn sum(args: Vec<Value>) -> Value {
102    args.into_iter().fold(Value::from(0.0), |mut s, v| {
103        if let Value::Array(arr) = v {
104            for x in arr {
105                if x.is_num() {
106                    s += x
107                }
108            }
109        } else if let Value::Array2(arr2) = v {
110            for x in arr2 {
111                if x.is_num() {
112                    s += x
113                }
114            }
115        } else {
116            s += Value::from(v.as_num())
117        }
118        s
119    })
120}
121
122#[function]
123fn average(args: Vec<Value>) -> Value {
124    let mut count = 0.0;
125    let mut sum_values: Vec<Value> = vec![]; 
126    for arg in args.into_iter() {
127        if let Value::Array(arr) = arg {
128            for x in arr {
129                if x.is_num() {
130                    sum_values.push(x); 
131                    count += 1.0; 
132                }
133            }
134        } else {
135            sum_values.push(Value::from(arg.as_num()));
136            count += 1.0; 
137        }
138   }
139    let average = sum_values.into_iter().fold(0.0, |mut s, v| {
140        s += v.as_num();
141        s
142    }) / count;
143    Value::from(average)
144}
145
146#[function]
147fn count(args: Vec<Value>) -> Value {
148	let mut count = 0.0;
149	for arg in args.iter() {
150		if let Value::Array(arr) = arg {
151            for x in arr.iter() {
152                if x.is_num() {
153                    count += 1.0; 
154                }
155            }
156        } else {
157            count += 1.0; 
158        }
159	}
160    Value::from(count)
161}
162
163#[function]
164fn concat(a: Value, b: Value) -> Value {
165    Value::from(format!("{}{}", a.as_text(), b.as_text()))
166}
167
168#[function]
169fn andfunc(a: Value, b: Value) -> Value {
170    Value::from(a.as_bool() && b.as_bool())
171}
172
173#[function]
174fn orfunc(a: Value, b: Value) -> Value {
175    Value::from(a.as_bool() || b.as_bool())
176}
177
178#[function]
179fn max(args: Vec<Value>) -> Value {
180    let mut output = args[0].clone(); 
181    for v in args.into_iter() {
182        if let Value::Array(arr) = v {
183            for x in arr {
184                if x.is_num() {
185                    output = output.max(x); 
186                }
187            }
188        } else if let Value::Array2(arr2) = v {
189            for x in arr2 {
190                if x.is_num() {
191                    output = output.max(x); 
192                }
193            }
194        } else {
195            output = output.max(v); 
196        }
197    }
198    output
199}
200
201#[function]
202fn min(args: Vec<Value>) -> Value {
203    let mut output = args[0].clone(); 
204    for v in args.into_iter() {
205        if let Value::Array(arr) = v {
206            for x in arr {
207                if x.is_num() {
208                    output = output.min(x); 
209                }
210            }
211        } else if let Value::Array2(arr2) = v {
212            for x in arr2 {
213                if x.is_num() {
214                    output = output.min(x); 
215                }
216            }
217        } else {
218            output = output.min(v); 
219        }
220    }
221    output
222}
223
224#[function]
225fn matchfn(lookup_value: Value, lookup_array: Value, match_type: Value) -> Value {
226    let lookup_value = lookup_value.ensure_single(); 
227    let mut lookup_array_mut = lookup_array.as_array();
228    if match_type.as_num() == -1.0 {
229        // Smallest value that is greater than or equal to the lookup-value.
230        // Lookup array placed in descending order.
231        lookup_array_mut.sort_by(|a, b| b.cmp(a)); // Descending Order
232        match lookup_array.as_array().into_iter().enumerate().filter(|(_,v)| v >= &lookup_value).last() {
233            Some(v) => { Value::from(v.0 + 1) },
234            _ => Value::Error(ExcelError::NA)
235        }
236    } else if match_type.as_num() == 0.0 {
237        match lookup_array_mut.into_iter().position(|v| v == lookup_value) {
238            Some(v) => { Value::from(v + 1) }, 
239            _ => Value::Error(ExcelError::NA)
240        }
241    } else {
242        // Largest value that is less than or equal to the lookup-value
243        // Lookup array placed in ascending order.
244        lookup_array_mut.sort(); // Ascending Order
245        match lookup_array_mut.into_iter().enumerate().filter(|(_, v)| v <= &lookup_value).last() {
246            Some(v) => { Value::from(v.0 + 1) }, 
247            _ => Value::Error(ExcelError::NA)
248        }
249    }
250}
251
252#[function]
253fn date(year: Value, month: Value, day: Value) -> Value {
254   Value::from(NaiveDate::from_ymd(year.as_num() as i32, month.as_num() as u32, day.as_num() as u32))
255}
256
257
258#[function]
259// FIXME: significance
260fn floor(x: Value, _significance: Value) -> Value {
261    Value::from(math::round::floor(x.as_num(), 0))
262}
263
264/*
265 * Index function can return either a value or a reference. 
266 * Excel treats them different depending on what the parent function needs.
267 * This function will always return a Value::Ref and require than 
268 * conversion to an actual value happens higher up the evaluation chain. 
269*/
270pub fn index(args: Vec<Expr>, book: &Book, debug: bool) -> Result<Value, Error> {
271	let mut arg_values = args.into_iter(); 
272	let array: Value = evaluate_expr_with_context(arg_values.next().unwrap(), book, debug)?; // This can be a range or an array
273	let row_num: Value = evaluate_expr_with_context(arg_values.next().unwrap(), book, debug)?; 
274	let col_num_option = arg_values.next(); 
275	let col_num = match col_num_option {
276		Some(expr) => evaluate_expr_with_context(expr, book, debug)?,
277		None => Value::from(1.0)
278	}; 
279    // Pass up Err
280    if array.is_err() {
281        return Ok(array); 
282    } else if row_num.is_err() {
283        return Ok(row_num); 
284    } else if col_num.is_err() {
285        return Ok(col_num); 
286    }
287    let row_idx = row_num.as_num() as usize - 1;
288    let col_idx = col_num.as_num() as usize - 1; 
289    if let Value::Range { sheet, reference, value } = array {
290		let reference = Reference::from(reference); 
291		let (start_row, start_col, _, _) = reference.get_dimensions(); 
292
293        // If row value is zero, reference entire column.
294        // Start cell row index is zero. 
295		if row_num.as_num() == 0.0 {
296            let new_col = start_col + col_idx; 
297			return Ok(Value::Range { sheet: sheet.clone(), reference: Reference::from((0, new_col)), value: None }); 
298		}
299
300        // If column value is zero, reference entire column.
301        // Start cell column index is zero. 
302		if col_num.as_num() == 0.0 {
303            let new_row = start_row + row_idx; 
304			return Ok(Value::Range { sheet: sheet.clone(), reference: Reference::from((new_row, 0)), value: None }); 
305		}
306
307        let new_row = start_row + row_idx;  
308        let new_col = start_col + col_idx; 
309        let new_value: Value = value.unwrap().as_array2()[[row_idx, col_idx]].clone(); 
310        return Ok(Value::Range { sheet: sheet.clone(), reference: Reference::from((new_row, new_col)), value: Some(Box::new(new_value)) }); 
311	} else {
312		panic!("First argument must be a range."); 
313	}
314} 
315
316pub fn offset(args: Vec<Expr>, book: &Book, debug: bool) -> Result<Value, Error> {
317    let array = evaluate_expr_with_context(args.get(0).unwrap().clone(), book, debug)?; 
318	if let Value::Range { sheet, reference, value: _ } = array { 
319		let rows = ensure_non_range(evaluate_expr_with_context(args.get(1).unwrap().clone(), book, debug)?);
320		let cols = ensure_non_range(evaluate_expr_with_context(args.get(2).unwrap().clone(), book, debug)?); 
321		let height = args.get(3); 
322		let height_opt: Option<i32> = height.map(|h| {
323			ensure_non_range(evaluate_expr_with_context(h.clone(), book, debug).unwrap()).as_num() as i32
324		}); 
325		let width = args.get(4); 
326		let width_opt: Option<i32> = width.map(|w| {
327			ensure_non_range(evaluate_expr_with_context(w.clone(), book, debug).unwrap()).as_num() as i32
328		}); 
329		let new_reference = offset_reference(&mut reference.clone(), rows.as_num() as i32, cols.as_num() as i32, height_opt, width_opt); 
330        let new_expr = Expr::Reference { sheet: sheet.clone(), reference: new_reference.to_string() }; 
331        if book.is_calculated(new_expr.clone()) {
332            let reference_value = match evaluate_expr_with_context(new_expr.clone(), book, debug) {
333                Ok(value) => Some(Box::new(ensure_non_range(value))), 
334                _ => panic!("New expression could not be evaluated: {}", new_expr.clone())
335            }; 
336            Ok(Value::Range { sheet: sheet.clone(), reference: new_reference, value:  reference_value})
337        } else {
338            Err(Error::Volatile(Box::new(new_expr)))
339        }
340    } else {
341        panic!("First expression must be a Reference.")
342    }
343}
344
345struct Iferror {
346    a: Value, 
347    b: Value, 
348}
349
350impl Function for Iferror {
351    fn evaluate(self) -> Value {
352        if self.a.is_err() {
353            self.b 
354        } else {
355            self.a
356        }
357    }
358}
359
360#[function]
361fn eomonth(start_date: Value, months: Value) -> Value {
362    let start_date: NaiveDate = start_date.as_date(); 
363    let bom = NaiveDate::from_ymd(start_date.year(), start_date.month(), 1);
364    let eom: NaiveDate; 
365    if months.as_num() > 0.0 {
366        eom = bom.checked_add_months(Months::new((months.as_num()+1.0) as u32)).unwrap(); 
367    } else if months.as_num() < 0.0 {
368        eom = bom.checked_sub_months(Months::new((months.as_num()*-1.0-1.0) as u32)).unwrap(); 
369    } else {
370        eom = bom.checked_add_months(Months::new(1)).unwrap(); 
371    }
372    Value::from(eom.pred())
373}
374
375#[function]
376fn sumifs(sum_range: Value, args: Vec<Value>) -> Value {
377    let mut keep_index: Vec<usize> = vec![]; 
378    for (idx, i) in (0..args.len()).step_by(2).enumerate() {
379        let cell_range: Vec<Value> = args.get(i).unwrap().as_array();
380        let criteria: Value = args.get(i+1).unwrap().ensure_single(); 
381        let criteria_text = criteria.as_text(); 
382        for (y, cell) in cell_range.iter().enumerate() {
383            let eval: bool = parse_criteria(criteria_text.as_str(), cell); 
384            if idx == 0 {
385                if eval {
386                    keep_index.push(y); 
387                }
388            } else {
389                if ! eval && keep_index.contains(&y) {
390                    keep_index.retain(|x| x != &y); 
391                }
392           }
393       } 
394    }
395    Value::from(sum_range.as_array()
396        .into_iter()
397        .enumerate()
398        .filter_map(|(i, v)| match keep_index.contains(&i) {
399            true => Some(v.as_num()), 
400            false => None
401        })
402        .collect::<Vec<f64>>()
403        .iter()
404        .sum::<f64>()) 
405} 
406
407#[function]
408fn sumif(range: Value, criteria: Value, sum_range: Option<Value>) -> Value {
409    let mut keep_index: Vec<usize> = vec![]; 
410    let range: Vec<Value> = range.as_array(); 
411    let criteria = criteria.ensure_single(); 
412    let criteria_text = format!("{}", criteria); 
413    for (i, cell) in range.iter().enumerate() {
414        let eval = parse_criteria(criteria_text.as_str(), cell); 
415        if eval && !keep_index.contains(&i) {
416            keep_index.push(i); 
417        }
418    } 
419    let sum_range = match sum_range {
420        Some(val) => val.as_array(), 
421        None => range
422    }; 
423    Value::from(sum_range
424        .into_iter()
425        .enumerate()
426        .filter_map(|(i, v)| match keep_index.contains(&i) {
427            true => Some(v.as_num()), 
428            false => None
429        }) 
430        .collect::<Vec<f64>>()
431        .iter()
432        .sum::<f64>()) 
433} 
434
435fn parse_criteria(c: &str, cell: &Value) -> bool {
436    let cell = cell.ensure_single().as_text(); 
437    let op: &str = if c.split("<>").count() > 1 {
438        "<>"
439    } else if c.split("<=").count() > 1 {
440        "<="
441    } else if c.split("<").count() > 1 {
442        "<"
443    } else if c.split(">=").count() > 1 {
444        ">="
445    } else if c.split(">").count() > 1 {
446        ">"
447    } else if c.split("=").count() > 1 {
448        "="
449    } else {
450        "" 
451    }; 
452    if ! op.is_empty() {
453        evaluate_str(format!("\"{}\"{}\"{}\"", c.split(op).collect::<Vec<&str>>()[1], op, cell).as_str()).unwrap().as_bool()
454    } else {
455        evaluate_str(format!("\"{}\"=\"{}\"", c, cell).as_str()).unwrap().as_bool()
456    } 
457}
458
459#[function]
460fn averageif(range: Value, criteria: Value, average_range: Option<Value>) -> Value {
461    let mut keep_index: Vec<usize> = vec![]; 
462    let range: Vec<Value> = range.as_array(); 
463    let criteria = criteria.ensure_single(); 
464    let criteria_text = criteria.as_text(); 
465    for (i, cell) in range.iter().enumerate() {
466        let eval = parse_criteria(criteria_text.as_str(), cell); 
467        if eval && !keep_index.contains(&i) {
468            keep_index.push(i); 
469        }
470    } 
471    let average_range = match average_range {
472        Some(val) => val.as_array(), 
473        None => range
474    }; 
475    let average_range_filter = average_range
476        .into_iter()
477        .enumerate()
478        .filter_map(|(i, v)| match keep_index.contains(&i) {
479            true => Some(v.as_num()), 
480            false => None
481        }).collect::<Vec<f64>>(); 
482    Value::from(average_range_filter
483        .iter()
484        .sum::<f64>()/average_range_filter.len() as f64)
485} 
486
487
488
489#[function]
490fn averageifs(average_range: Value, args: Vec<Value>) -> Value {
491    let mut keep_index: Vec<usize> = vec![]; 
492    for i in (0..args.len()).step_by(2) {
493        let cell_range: Vec<Value> = args.get(i).unwrap().as_array(); 
494        let criteria: Value = args.get(i+1).unwrap().ensure_single(); 
495        let criteria_text = criteria.as_text(); 
496        for (i, cell) in cell_range.iter().enumerate() {
497            let eval = parse_criteria(criteria_text.as_str(), cell); 
498            if eval && !keep_index.contains(&i) {
499                keep_index.push(i); 
500            }
501        } 
502    } 
503    let average_range_filter = average_range.as_array()
504        .into_iter()
505        .enumerate()
506        .filter_map(|(i, v)| match keep_index.contains(&i) {
507            true => Some(v.as_num()), 
508            false => None
509        }).collect::<Vec<f64>>(); 
510    Value::from(average_range_filter
511        .iter()
512        .sum::<f64>()/average_range_filter.len() as f64) 
513} 
514
515
516
517#[function]
518fn xirrfunc(values: Value, dates: Value) -> Value {
519    let payments: Vec<xirr::Payment> = values
520        .as_array()
521        .iter()
522        .zip(
523            dates
524            .as_array()
525            .iter()
526        ).map(|(v, d)| xirr::Payment { amount: v.as_num(), date: d.as_date() })
527        .collect(); 
528    match xirr::compute(&payments) {
529        Ok(v) => Value::from(v), 
530        _ => Value::Error(ExcelError::Num), 
531    }
532}
533
534#[function]
535fn iffunc(condition: Value, a: Value, b: Value) -> Value {
536    if condition.as_bool() {
537        a
538    } else {
539        b
540    }
541}
542
543#[function]
544fn xnpv(rate: Value, values: Value, dates: Value) -> Value {
545    let rate: f64 = rate.as_num(); 
546    let dates: Vec<NaiveDate> = dates.as_array().iter().map(|x| x.as_date()).collect(); 
547    let start_date = *dates.get(0).unwrap(); 
548    Value::from(
549        values.as_array().iter().map(|x| x.as_num())
550        .into_iter()
551        .zip(
552            dates
553            .into_iter()
554        ).fold(0.0, |s, (value, date)| {
555            let days = NaiveDate::signed_duration_since(date, start_date).num_days() as f64; 
556            s + (value / ((1.0+rate).powf(days / 365.0)))
557        })
558    ) 
559}
560
561#[function]
562//TODO: Implement basis
563fn yearfrac(start_date: Value, end_date: Value) -> Value {
564    let start_date: NaiveDate = start_date.as_date(); 
565    let end_date: NaiveDate = end_date.as_date(); 
566    Value::from(
567        (
568            ((end_date.year() as i32 - start_date.year() as i32) * 360) + 
569            (end_date.ordinal() as i32 - start_date.ordinal() as i32)
570        ) as f64 / 360.0
571    )    
572}
573
574#[function]
575fn datedif(start_date: Value, end_date: Value, unit: Value) -> Value {
576    let start_date: NaiveDate = start_date.as_date(); 
577    let end_date: NaiveDate = end_date.as_date(); 
578    match unit.as_text().as_str() {
579        "Y" | "y" => Value::from(end_date.year() - start_date.year()),
580        "M" | "m" => Value::from((end_date.year() as i32 - start_date.year() as i32)*12 + (end_date.month() as i32 - start_date.month() as i32)),
581        "D" | "d" => Value::from(NaiveDate::signed_duration_since(end_date, start_date).num_days() as f64),
582        "MD" | "md" => Value::from(end_date.day() as i32 - start_date.day() as i32), 
583        "YM" | "ym" => Value::from(end_date.month() as i32 - start_date.month() as i32), 
584        "YD" | "yd" => Value::from(end_date.ordinal() as i32 - start_date.ordinal() as i32),
585        _ => panic!("Not a valid unit.")
586    }
587}
588
589#[function]
590fn pmt(rate: Value, nper: Value, pv: Value, fv: Option<Value>, f_type: Option<Value>) -> Value {
591    let rate = rate.as_num();
592    let nper = nper.as_num();
593    let pv = pv.as_num();
594    let fv = fv.unwrap_or_else(|| Value::from(0.0)).as_num(); 
595    let f_type = f_type.unwrap_or_else(|| Value::from(0.0)).as_num();
596    let value = rate*(fv*-1.0+pv*(1.0+rate).powf(nper))/((1.0+rate*f_type)*(1.0-(1.0+rate).powf(nper)));
597    if value == f64::INFINITY || value == f64::NEG_INFINITY {
598        Value::Error(ExcelError::Num)
599    } else {
600        Value::from(value)
601    }
602}
603
604#[function]
605fn counta(args: Vec<Value>) -> Value {
606    Value::from(
607        args.into_iter().fold(0, |s, v| {
608            match v {
609                Value::Array(arr) => {
610                    s + arr.into_iter().fold(0, |s, v| match v {
611                        Value::Empty => s, 
612                            _ => s + 1
613                    })
614                },
615                Value::Array2(arr2) => {
616                    s + arr2.into_raw_vec().into_iter().fold(0, |s, v| match v {
617                        Value::Empty => s, 
618                        _ => s + 1
619                    })
620                }, 
621                _ => s + 1
622            }
623        })
624    )
625}
626
627#[function]
628fn rounddown(x: Value, num_digits: Value) -> Value {
629    let x: f64 = x.as_num(); 
630    let num_digits: f64 = num_digits.as_num(); 
631    if num_digits > 0.0 {
632        Value::from(((x * 10.0_f64.powf(num_digits)) as i64) as f64 / 10.0_f64.powf(num_digits))
633    } else if num_digits < 0.0 {
634        Value::from(((x / 10.0_f64.powf(-num_digits)) as i64) as f64 * 10.0_f64.powf(-num_digits))
635    } else {
636        Value::from((x as i64) as f64)
637    }
638}
639
640#[cfg(test)]
641mod tests {
642    use crate::{
643        evaluate::{
644            value:: Value, 
645            evaluate_str 
646        },
647        workbook::Book,
648        errors::Error, 
649    };
650    use chrono::naive::NaiveDate; 
651
652    #[test]
653    fn test_rounddown() -> Result<(), Error> {
654        assert_eq!(evaluate_str("ROUNDDOWN(3.2, 0)")?, Value::from(3.0)); 
655        assert_eq!(evaluate_str("ROUNDDOWN(76.9, 0)")?, Value::from(76.0)); 
656        assert_eq!(evaluate_str("ROUNDDOWN(3.14159, 3)")?, Value::from(3.141)); 
657        assert_eq!(evaluate_str("ROUNDDOWN(-3.14159, 1)")?, Value::from(-3.1)); 
658        assert_eq!(evaluate_str("ROUNDDOWN(31415.92654, -2)")?, Value::from(31400)); 
659        Ok(())
660    }
661
662    #[test]
663    fn test_counta() -> Result<(), Error> {
664        assert_eq!(evaluate_str("COUNTA(1,2,3,4,5)")?, Value::from(5.0)); 
665        assert_eq!(evaluate_str("COUNTA({1,2,3,4,5})")?, Value::from(5.0)); 
666        assert_eq!(evaluate_str("COUNTA({1,2,3,4,5},6,\"7\")")?, Value::from(7.0)); 
667        Ok(())
668    }
669
670    #[test]
671    fn test_pmt() -> Result<(), Error> {
672        assert!((-1037.03 - evaluate_str("PMT(0.08/12, 10, 10000)")?.as_num()).abs() < 0.01); 
673        assert!((-1030.16 - evaluate_str("PMT(0.08/12, 10, 10000, 0, 1)")?.as_num()).abs() < 0.01); 
674        Ok(())
675    }
676
677    #[test]
678    fn test_datedif() -> Result<(), Error> {
679        assert_eq!(evaluate_str("DATEDIF(DATE(2004, 2, 10), DATE(2020, 3, 10), \"Y\")")?, Value::from(16.0)); 
680        assert_eq!(evaluate_str("DATEDIF(DATE(2004, 2, 10), DATE(2020, 3, 10), \"M\")")?, Value::from(193.0)); 
681        assert_eq!(evaluate_str("DATEDIF(DATE(2004, 2, 10), DATE(2020, 3, 10), \"D\")")?, Value::from(5873.0)); 
682        assert_eq!(evaluate_str("DATEDIF(DATE(2004, 2, 10), DATE(2020, 3, 10), \"YM\")")?, Value::from(1.0)); 
683        assert_eq!(evaluate_str("DATEDIF(DATE(2004, 2, 10), DATE(2020, 3, 10), \"MD\")")?, Value::from(0.0)); 
684        assert_eq!(evaluate_str("DATEDIF(DATE(2004, 2, 10), DATE(2020, 3, 10), \"YD\")")?, Value::from(29.0)); 
685        Ok(())
686    }
687
688	#[test]
689    fn test_sum() -> Result<(), Error> {
690		assert_eq!(evaluate_str("SUM(1,2,3,4,5)")?, Value::from(15.0));
691		assert_eq!(evaluate_str("SUM({1,2;3,4})")?, Value::from(10.0));
692		assert_eq!(evaluate_str("SUM({1,2,3,4,5},6,\"7\")")?, Value::from(28.0));
693		assert_eq!(evaluate_str("SUM({1,\"2\",TRUE,4})")?, Value::from(5.0));
694        Ok(())
695    }
696
697    #[test]
698    fn test_average() -> Result<(), Error> {
699		assert_eq!(evaluate_str("AVERAGE(1,2,3,4,5)")?, Value::from(3.0));
700		assert_eq!(evaluate_str("AVERAGE({1,2;3,4})")?, Value::from(2.5));
701		assert_eq!(evaluate_str("AVERAGE({1,2,3,4,5},6,\"7\")")?, Value::from(4.0));
702		assert_eq!(evaluate_str("AVERAGE({1,\"2\",TRUE,4})")?, Value::from(2.5));
703        Ok(())
704    }
705
706    #[test]
707    fn test_count() -> Result<(), Error> {
708		assert_eq!(evaluate_str("COUNT(1,2,3,4,5)")?, Value::from(5.0));
709		assert_eq!(evaluate_str("COUNT({1,2,3,4,5})")?, Value::from(5.0));
710		assert_eq!(evaluate_str("COUNT({1,2,3,4,5},6,\"7\")")?, Value::from(7.0));
711        Ok(())
712    }
713 
714    #[test]
715    fn test_concat() -> Result<(), Error> {
716		assert_eq!(evaluate_str("CONCAT(\"test\", \"func\")")?, Value::from("testfunc".to_string()));
717        Ok(())
718    }
719
720    #[test]
721    fn test_and() -> Result<(), Error> {
722		assert_eq!(evaluate_str("AND(TRUE, TRUE)")?, Value::from(true));
723        Ok(())
724    }
725
726    #[test]
727    fn test_or() -> Result<(), Error>  {
728		assert_eq!(evaluate_str("OR(TRUE, FALSE)")?, Value::from(true));
729        Ok(())
730    }
731
732    #[test]
733    fn test_max_min() -> Result<(), Error> {
734		assert_eq!(evaluate_str("MAX(1, 5, 10)")?, Value::from(10.0));
735		assert_eq!(evaluate_str("MIN(1, 5, 10)")?, Value::from(1.0));
736        Ok(())
737    }
738
739    #[test]
740    fn test_match() -> Result<(), Error> {
741		assert_eq!(evaluate_str("MATCH(3, {1,2,3,4,5}, 0)")?, Value::from(3.0));
742        Ok(())
743    }
744
745    #[test]
746    fn test_index() -> Result<(), Error> {
747        let mut book = Book::from("assets/functions.xlsx"); 
748        book.load(false).unwrap(); 
749        book.calculate(false, false)?; 
750        assert_eq!(book.resolve_str_ref("Sheet1!H3")?[[0,0]].as_num(), 11.0); 
751        Ok(())
752    }
753
754    #[test]
755    fn test_date() -> Result<(), Error> {
756		assert_eq!(evaluate_str("DATE(2022, 1, 1)")?, Value::from(NaiveDate::from_ymd(2022, 1, 1)));
757        Ok(())
758    }
759
760    #[test]
761    fn test_floor() -> Result<(), Error> {
762        assert_eq!(evaluate_str("FLOOR(3.7, 1)")?, Value::from(3.0)); 
763        // assert_eq!(evaluate_str("FLOOR(-2.5, -2)"), Value::from(-2.0)); 
764        // assert_eq!(evaluate_str("FLOOR(1.58, 0.01)"), Value::from(1.5)); 
765        // assert_eq!(evaluate_str("FLOOR(0.234, 0.01)"), Value::from(0.23)); 
766        Ok(())
767    }
768
769    #[test]
770    fn test_iferror() -> Result<(), Error> {
771        assert_eq!(evaluate_str("IFERROR(#VALUE!, 1)")?, Value::from(1.0)); 
772        Ok(())
773    }
774
775    #[test]
776    fn test_eomonth() -> Result<(), Error> {
777        assert_eq!(evaluate_str("EOMONTH(DATE(2004, 2, 29), 12)")?, Value::from(NaiveDate::from_ymd(2005, 2, 28))); 
778        assert_eq!(evaluate_str("EOMONTH(DATE(2004, 2, 28), 12)")?, Value::from(NaiveDate::from_ymd(2005, 2, 28))); 
779        assert_eq!(evaluate_str("EOMONTH(DATE(2004, 1, 15), -23)")?, Value::from(NaiveDate::from_ymd(2002, 2, 28))); 
780        assert_eq!(evaluate_str("EOMONTH(DATE(2004, 1, 15), 0)")?, Value::from(NaiveDate::from_ymd(2004, 1, 31))); 
781        Ok(())
782    }
783
784    #[test]
785    fn test_sumifs() -> Result<(), Error> {
786        let mut book = Book::from("assets/functions.xlsx"); 
787        book.load(false).unwrap(); 
788        book.calculate(false, false)?; 
789        assert_eq!(book.resolve_str_ref("Sheet1!H5")?[[0,0]].as_num(), 2.0); 
790        Ok(())
791    }
792
793    #[test]
794    fn test_averageifs() -> Result<(), Error> {
795        let mut book = Book::from("assets/functions.xlsx"); 
796        book.load(false).unwrap(); 
797        book.calculate(false, false)?; 
798        assert_eq!(book.resolve_str_ref("Sheet1!H8")?[[0,0]].as_num(), 2.0); 
799        Ok(())
800    }
801
802    #[test]
803    fn test_xirr() -> Result<(), Error> {
804        let mut book = Book::from("assets/functions.xlsx"); 
805        book.load(false).unwrap(); 
806        book.calculate(false, false)?; 
807        assert!((0.3340 - book.resolve_str_ref("Sheet1!H4")?[[0,0]].as_num()).abs() < 0.01); 
808        Ok(())
809    }
810
811    #[test]
812    fn test_offset() -> Result<(), Error> {
813        let mut book = Book::from("assets/functions.xlsx"); 
814        book.load(false).unwrap(); 
815        book.calculate(false, false)?; 
816        assert_eq!(book.resolve_str_ref("Sheet1!H6")?[[0,0]].as_num(), 10.0); 
817        Ok(())
818    }
819    
820    #[test]
821    fn test_if() -> Result<(), Error> {
822        assert_eq!(evaluate_str("IF(TRUE, 1, 2)")?, Value::from(1.0)); 
823        assert_eq!(evaluate_str("IF(FALSE, 1, 2)")?, Value::from(2.0)); 
824        Ok(())
825    }
826
827    #[test]
828    fn test_xnpv() -> Result<(), Error> {
829        let mut book = Book::from("assets/functions.xlsx"); 
830        book.load(false).unwrap(); 
831        book.calculate(false, false)?; 
832        assert!((7.657 - book.resolve_str_ref("Sheet1!H7")?[[0,0]].as_num()).abs() < 0.01); 
833        Ok(())
834    }
835
836    #[test]
837    fn test_yearfrac() -> Result<(), Error> {
838        assert!((0.58055 - evaluate_str("YEARFRAC(DATE(2012, 1, 1), DATE(2012, 7, 30))")?.as_num() < 0.01)); 
839        Ok(())
840    }
841}
excel_lib/function/mod.rs

excel_lib/function/
mod.rs
