1pub(crate) mod conversion;
7pub mod date_time;
8pub mod engineering;
9pub mod financial;
10pub mod information;
11pub mod logical;
12pub mod lookup;
13pub mod math;
14pub mod statistical;
15pub mod text;
16
17use std::borrow::Cow;
18
19use crate::cell::CellValue;
20use crate::error::{Error, Result};
21use crate::formula::ast::Expr;
22use crate::formula::eval::{coerce_to_number, coerce_to_string, Evaluator};
23
24pub type FunctionFn = fn(&[Expr], &mut Evaluator) -> Result<CellValue>;
29
30pub fn lookup_function(name: &str) -> Option<FunctionFn> {
32 match name.to_ascii_uppercase().as_str() {
33 "SUM" => Some(fn_sum),
34 "AVERAGE" => Some(fn_average),
35 "COUNT" => Some(fn_count),
36 "COUNTA" => Some(fn_counta),
37 "MIN" => Some(fn_min),
38 "MAX" => Some(fn_max),
39 "IF" => Some(fn_if),
40 "ABS" => Some(fn_abs),
41 "INT" => Some(fn_int),
42 "ROUND" => Some(fn_round),
43 "MOD" => Some(fn_mod),
44 "POWER" => Some(fn_power),
45 "SQRT" => Some(fn_sqrt),
46 "LEN" => Some(fn_len),
47 "LOWER" => Some(fn_lower),
48 "UPPER" => Some(fn_upper),
49 "TRIM" => Some(fn_trim),
50 "LEFT" => Some(fn_left),
51 "RIGHT" => Some(fn_right),
52 "MID" => Some(fn_mid),
53 "CONCATENATE" => Some(fn_concatenate),
54 "AND" => Some(fn_and),
55 "OR" => Some(fn_or),
56 "NOT" => Some(fn_not),
57 "ISNUMBER" => Some(fn_isnumber),
58 "ISTEXT" => Some(fn_istext),
59 "ISBLANK" => Some(fn_isblank),
60 "ISERROR" => Some(fn_iserror),
61 "VALUE" => Some(fn_value),
62 "TEXT" => Some(fn_text),
63 "SUMIF" => Some(math::fn_sumif),
64 "SUMIFS" => Some(math::fn_sumifs),
65 "ROUNDUP" => Some(math::fn_roundup),
66 "ROUNDDOWN" => Some(math::fn_rounddown),
67 "CEILING" => Some(math::fn_ceiling),
68 "FLOOR" => Some(math::fn_floor),
69 "SIGN" => Some(math::fn_sign),
70 "RAND" => Some(math::fn_rand),
71 "RANDBETWEEN" => Some(math::fn_randbetween),
72 "PI" => Some(math::fn_pi),
73 "LOG" => Some(math::fn_log),
74 "LOG10" => Some(math::fn_log10),
75 "LN" => Some(math::fn_ln),
76 "EXP" => Some(math::fn_exp),
77 "PRODUCT" => Some(math::fn_product),
78 "QUOTIENT" => Some(math::fn_quotient),
79 "FACT" => Some(math::fn_fact),
80 "AVERAGEIF" => Some(statistical::fn_averageif),
81 "AVERAGEIFS" => Some(statistical::fn_averageifs),
82 "COUNTBLANK" => Some(statistical::fn_countblank),
83 "COUNTIF" => Some(statistical::fn_countif),
84 "COUNTIFS" => Some(statistical::fn_countifs),
85 "MEDIAN" => Some(statistical::fn_median),
86 "MODE" => Some(statistical::fn_mode),
87 "LARGE" => Some(statistical::fn_large),
88 "SMALL" => Some(statistical::fn_small),
89 "RANK" => Some(statistical::fn_rank),
90 "ISERR" => Some(information::fn_iserr),
91 "ISNA" => Some(information::fn_isna),
92 "ISLOGICAL" => Some(information::fn_islogical),
93 "ISEVEN" => Some(information::fn_iseven),
94 "ISODD" => Some(information::fn_isodd),
95 "TYPE" => Some(information::fn_type),
96 "N" => Some(information::fn_n),
97 "NA" => Some(information::fn_na),
98 "ERROR.TYPE" => Some(information::fn_error_type),
99 "CONCAT" => Some(text::fn_concat),
100 "FIND" => Some(text::fn_find),
101 "SEARCH" => Some(text::fn_search),
102 "SUBSTITUTE" => Some(text::fn_substitute),
103 "REPLACE" => Some(text::fn_replace),
104 "REPT" => Some(text::fn_rept),
105 "EXACT" => Some(text::fn_exact),
106 "T" => Some(text::fn_t),
107 "PROPER" => Some(text::fn_proper),
108 "TRUE" => Some(logical::fn_true),
109 "FALSE" => Some(logical::fn_false),
110 "IFERROR" => Some(logical::fn_iferror),
111 "IFNA" => Some(logical::fn_ifna),
112 "IFS" => Some(logical::fn_ifs),
113 "SWITCH" => Some(logical::fn_switch),
114 "XOR" => Some(logical::fn_xor),
115 "DATE" => Some(date_time::fn_date),
116 "TODAY" => Some(date_time::fn_today),
117 "NOW" => Some(date_time::fn_now),
118 "YEAR" => Some(date_time::fn_year),
119 "MONTH" => Some(date_time::fn_month),
120 "DAY" => Some(date_time::fn_day),
121 "HOUR" => Some(date_time::fn_hour),
122 "MINUTE" => Some(date_time::fn_minute),
123 "SECOND" => Some(date_time::fn_second),
124 "DATEDIF" => Some(date_time::fn_datedif),
125 "EDATE" => Some(date_time::fn_edate),
126 "EOMONTH" => Some(date_time::fn_eomonth),
127 "DATEVALUE" => Some(date_time::fn_datevalue),
128 "WEEKDAY" => Some(date_time::fn_weekday),
129 "WEEKNUM" => Some(date_time::fn_weeknum),
130 "NETWORKDAYS" => Some(date_time::fn_networkdays),
131 "WORKDAY" => Some(date_time::fn_workday),
132 "VLOOKUP" => Some(lookup::fn_vlookup),
133 "HLOOKUP" => Some(lookup::fn_hlookup),
134 "INDEX" => Some(lookup::fn_index),
135 "MATCH" => Some(lookup::fn_match),
136 "LOOKUP" => Some(lookup::fn_lookup),
137 "ROW" => Some(lookup::fn_row),
138 "COLUMN" => Some(lookup::fn_column),
139 "ROWS" => Some(lookup::fn_rows),
140 "COLUMNS" => Some(lookup::fn_columns),
141 "CHOOSE" => Some(lookup::fn_choose),
142 "ADDRESS" => Some(lookup::fn_address),
143 "FV" => Some(financial::fn_fv),
144 "PV" => Some(financial::fn_pv),
145 "NPV" => Some(financial::fn_npv),
146 "IRR" => Some(financial::fn_irr),
147 "PMT" => Some(financial::fn_pmt),
148 "IPMT" => Some(financial::fn_ipmt),
149 "PPMT" => Some(financial::fn_ppmt),
150 "RATE" => Some(financial::fn_rate),
151 "NPER" => Some(financial::fn_nper),
152 "DB" => Some(financial::fn_db),
153 "DDB" => Some(financial::fn_ddb),
154 "SLN" => Some(financial::fn_sln),
155 "SYD" => Some(financial::fn_syd),
156 "EFFECT" => Some(financial::fn_effect),
157 "NOMINAL" => Some(financial::fn_nominal),
158 "DOLLARDE" => Some(financial::fn_dollarde),
159 "DOLLARFR" => Some(financial::fn_dollarfr),
160 "CUMIPMT" => Some(financial::fn_cumipmt),
161 "CUMPRINC" => Some(financial::fn_cumprinc),
162 "XNPV" => Some(financial::fn_xnpv),
163 "XIRR" => Some(financial::fn_xirr),
164 "BIN2DEC" => Some(engineering::fn_bin2dec),
165 "BIN2HEX" => Some(engineering::fn_bin2hex),
166 "BIN2OCT" => Some(engineering::fn_bin2oct),
167 "DEC2BIN" => Some(engineering::fn_dec2bin),
168 "DEC2HEX" => Some(engineering::fn_dec2hex),
169 "DEC2OCT" => Some(engineering::fn_dec2oct),
170 "HEX2BIN" => Some(engineering::fn_hex2bin),
171 "HEX2DEC" => Some(engineering::fn_hex2dec),
172 "HEX2OCT" => Some(engineering::fn_hex2oct),
173 "OCT2BIN" => Some(engineering::fn_oct2bin),
174 "OCT2DEC" => Some(engineering::fn_oct2dec),
175 "OCT2HEX" => Some(engineering::fn_oct2hex),
176 "DELTA" => Some(engineering::fn_delta),
177 "GESTEP" => Some(engineering::fn_gestep),
178 "ERF" => Some(engineering::fn_erf),
179 "ERFC" => Some(engineering::fn_erfc),
180 "COMPLEX" => Some(engineering::fn_complex),
181 "IMREAL" => Some(engineering::fn_imreal),
182 "IMAGINARY" => Some(engineering::fn_imaginary),
183 "IMABS" => Some(engineering::fn_imabs),
184 "IMARGUMENT" => Some(engineering::fn_imargument),
185 "IMCONJUGATE" => Some(engineering::fn_imconjugate),
186 "IMSUM" => Some(engineering::fn_imsum),
187 "IMSUB" => Some(engineering::fn_imsub),
188 "IMPRODUCT" => Some(engineering::fn_improduct),
189 "IMDIV" => Some(engineering::fn_imdiv),
190 "IMPOWER" => Some(engineering::fn_impower),
191 "IMSQRT" => Some(engineering::fn_imsqrt),
192 "CONVERT" => Some(engineering::fn_convert),
193 "BESSELI" => Some(engineering::fn_besseli),
194 "BESSELJ" => Some(engineering::fn_besselj),
195 "BESSELK" => Some(engineering::fn_besselk),
196 "BESSELY" => Some(engineering::fn_bessely),
197 _ => None,
198 }
199}
200
201pub fn check_arg_count(name: &str, args: &[Expr], min: usize, max: usize) -> Result<()> {
203 if args.len() < min || args.len() > max {
204 let expected = if min == max {
205 format!("{min}")
206 } else {
207 format!("{min}..{max}")
208 };
209 return Err(Error::WrongArgCount {
210 name: name.to_string(),
211 expected,
212 got: args.len(),
213 });
214 }
215 Ok(())
216}
217
218fn finite_number_or_num_error(value: f64) -> Result<CellValue> {
219 if value.is_finite() {
220 Ok(CellValue::Number(value))
221 } else {
222 Ok(CellValue::Error("#NUM!".to_string()))
223 }
224}
225
226pub fn matches_criteria(cell_value: &CellValue, criteria: &str, ctx: &Evaluator) -> Result<bool> {
233 matches_criteria_with_prepaid(cell_value, criteria, 0, ctx)
234}
235
236fn matches_criteria_with_prepaid(
237 cell_value: &CellValue,
238 criteria: &str,
239 prepaid_pattern_bytes: u64,
240 ctx: &Evaluator,
241) -> Result<bool> {
242 if criteria.is_empty() {
243 return Ok(matches!(cell_value, CellValue::Empty));
244 }
245 let (op, val_str) = if let Some(rest) = criteria.strip_prefix("<=") {
246 ("<=", rest)
247 } else if let Some(rest) = criteria.strip_prefix(">=") {
248 (">=", rest)
249 } else if let Some(rest) = criteria.strip_prefix("<>") {
250 ("<>", rest)
251 } else if let Some(rest) = criteria.strip_prefix('<') {
252 ("<", rest)
253 } else if let Some(rest) = criteria.strip_prefix('>') {
254 (">", rest)
255 } else if let Some(rest) = criteria.strip_prefix('=') {
256 ("=", rest)
257 } else {
258 ("=", criteria)
259 };
260
261 let cell_num = coerce_to_number(cell_value).ok();
262 let crit_num: Option<f64> = val_str.parse().ok();
263
264 if crit_num.is_some() && matches!(cell_value, CellValue::Empty) {
265 return Ok(false);
266 }
267
268 if let (Some(cn), Some(crn)) = (cell_num, crit_num) {
269 return Ok(match op {
270 "<=" => cn <= crn,
271 ">=" => cn >= crn,
272 "<>" => (cn - crn).abs() > f64::EPSILON,
273 "<" => cn < crn,
274 ">" => cn > crn,
275 "=" => (cn - crn).abs() < f64::EPSILON,
276 _ => false,
277 });
278 }
279
280 let uses_wildcards = val_str.contains(['*', '?', '~']);
281 if matches!(op, "=" | "<>") && uses_wildcards {
282 let (cell_text, prepaid_text_bytes) = cell_value_as_wildcard_text(cell_value, ctx)?;
283 let prepaid_decode_bytes = prepaid_pattern_bytes.saturating_add(prepaid_text_bytes);
284 let matched =
285 wildcard_match_with_prepaid(cell_text.as_ref(), val_str, prepaid_decode_bytes, ctx)?;
286 return Ok(if op == "=" { matched } else { !matched });
287 }
288 let cell_text = cell_value_as_text(cell_value);
289 let cell_lower = cell_text.to_lowercase();
290 let crit_lower = val_str.to_lowercase();
291
292 Ok(match op {
293 "=" => cell_lower == crit_lower,
294 "<>" => cell_lower != crit_lower,
295 "<" => cell_lower < crit_lower,
296 ">" => cell_lower > crit_lower,
297 "<=" => cell_lower <= crit_lower,
298 ">=" => cell_lower >= crit_lower,
299 _ => false,
300 })
301}
302
303pub(crate) struct PreparedCriteria {
304 text: String,
305 prepaid_match_bytes: u64,
306}
307
308pub(crate) fn prepare_criteria(
309 criteria_value: CellValue,
310 ctx: &Evaluator,
311) -> Result<PreparedCriteria> {
312 let (text, prepaid_match_bytes) = cell_value_into_criteria_text(criteria_value, ctx)?;
313 Ok(PreparedCriteria {
314 text,
315 prepaid_match_bytes,
316 })
317}
318
319pub(crate) fn matches_prepared_criteria(
320 cell_value: &CellValue,
321 criteria: &PreparedCriteria,
322 ctx: &Evaluator,
323) -> Result<bool> {
324 matches_criteria_with_prepaid(
325 cell_value,
326 &criteria.text,
327 criteria.prepaid_match_bytes,
328 ctx,
329 )
330}
331
332fn cell_value_as_text(value: &CellValue) -> Cow<'_, str> {
333 match value {
334 CellValue::String(text) | CellValue::Error(text) => Cow::Borrowed(text),
335 CellValue::Formula {
336 result: Some(result),
337 ..
338 } => cell_value_as_text(result),
339 CellValue::Empty | CellValue::Formula { result: None, .. } => Cow::Borrowed(""),
340 _ => Cow::Owned(coerce_to_string(value)),
341 }
342}
343
344fn cell_value_into_criteria_text(value: CellValue, ctx: &Evaluator) -> Result<(String, u64)> {
345 match value {
346 CellValue::String(text) | CellValue::Error(text) => Ok((text, 0)),
347 CellValue::Formula {
348 result: Some(result),
349 ..
350 } => cell_value_into_criteria_text(*result, ctx),
351 CellValue::Empty | CellValue::Formula { result: None, .. } => Ok((String::new(), 0)),
352 CellValue::RichString(runs) => flatten_rich_text_with_budget(&runs, ctx),
353 other => Ok((coerce_to_string(&other), 0)),
354 }
355}
356
357fn cell_value_as_wildcard_text<'a>(
358 value: &'a CellValue,
359 ctx: &Evaluator,
360) -> Result<(Cow<'a, str>, u64)> {
361 match value {
362 CellValue::String(text) | CellValue::Error(text) => Ok((Cow::Borrowed(text), 0)),
363 CellValue::Formula {
364 result: Some(result),
365 ..
366 } => cell_value_as_wildcard_text(result, ctx),
367 CellValue::Empty | CellValue::Formula { result: None, .. } => Ok((Cow::Borrowed(""), 0)),
368 CellValue::RichString(runs) => {
369 let (text, prepaid_bytes) = flatten_rich_text_with_budget(runs, ctx)?;
370 Ok((Cow::Owned(text), prepaid_bytes))
371 }
372 other => Ok((Cow::Owned(coerce_to_string(other)), 0)),
373 }
374}
375
376fn flatten_rich_text_with_budget(
377 runs: &[crate::rich_text::RichTextRun],
378 ctx: &Evaluator,
379) -> Result<(String, u64)> {
380 let byte_len = runs
381 .iter()
382 .try_fold(0usize, |total, run| total.checked_add(run.text.len()))
383 .and_then(|len| u64::try_from(len).ok())
384 .unwrap_or(u64::MAX);
385 ctx.charge_match_work(byte_len)?;
386 let capacity = usize::try_from(byte_len)
387 .map_err(|_| Error::Internal("rich criteria length overflow".to_string()))?;
388 let mut text = String::new();
389 text.try_reserve_exact(capacity)
390 .map_err(|_| Error::Internal("rich criteria allocation failed".to_string()))?;
391 for run in runs {
392 text.push_str(&run.text);
393 }
394 Ok((text, byte_len))
395}
396
397#[derive(Clone, Copy)]
398enum WildcardToken {
399 Star,
400 AnyOne,
401 Literal(char),
402}
403
404#[cfg(test)]
405fn wildcard_match(text: &str, pattern: &str, ctx: &Evaluator) -> Result<bool> {
406 wildcard_match_with_prepaid(text, pattern, 0, ctx)
407}
408
409fn wildcard_match_with_prepaid(
410 text: &str,
411 pattern: &str,
412 prepaid_decode_bytes: u64,
413 ctx: &Evaluator,
414) -> Result<bool> {
415 let decode_work = u64::try_from(text.len())
416 .ok()
417 .and_then(|text_bytes| {
418 u64::try_from(pattern.len())
419 .ok()
420 .and_then(|pattern_bytes| text_bytes.checked_add(pattern_bytes))
421 })
422 .unwrap_or(u64::MAX);
423 ctx.charge_match_work(decode_work.saturating_sub(prepaid_decode_bytes))?;
424
425 let text_len = u64::try_from(text.chars().count()).unwrap_or(u64::MAX);
426 let pattern_len = wildcard_pattern_token_count(pattern);
427 let work = text_len
428 .checked_add(1)
429 .and_then(|rows| {
430 pattern_len
431 .checked_add(1)
432 .and_then(|cols| rows.checked_mul(cols))
433 })
434 .unwrap_or(u64::MAX);
435 ctx.charge_match_work(work)?;
436
437 let tokens = tokenize_wildcard_pattern(pattern)?;
438 debug_assert_eq!(u64::try_from(tokens.len()).unwrap_or(u64::MAX), pattern_len);
439 let row_len = tokens
440 .len()
441 .checked_add(1)
442 .ok_or_else(|| Error::Internal("wildcard row length overflow".to_string()))?;
443 let mut row = Vec::new();
444 row.try_reserve_exact(row_len)
445 .map_err(|_| Error::Internal("wildcard row allocation failed".to_string()))?;
446 row.resize(row_len, false);
447 row[0] = true;
448 for index in 1..row_len {
449 row[index] = matches!(tokens[index - 1], WildcardToken::Star) && row[index - 1];
450 }
451
452 for text_char in text.chars() {
453 let mut diagonal = row[0];
454 row[0] = false;
455 for index in 1..row_len {
456 let above = row[index];
457 row[index] = match tokens[index - 1] {
458 WildcardToken::Star => row[index - 1] || above,
459 WildcardToken::AnyOne => diagonal,
460 WildcardToken::Literal(pattern_char) => {
461 diagonal && chars_equal_case_insensitive(text_char, pattern_char)
462 }
463 };
464 diagonal = above;
465 }
466 }
467
468 Ok(row[row_len - 1])
469}
470
471pub(crate) fn wildcard_search(text: &str, pattern: &str, ctx: &Evaluator) -> Result<Option<usize>> {
472 let decode_work = u64::try_from(text.len())
473 .ok()
474 .and_then(|text_bytes| {
475 u64::try_from(pattern.len())
476 .ok()
477 .and_then(|pattern_bytes| text_bytes.checked_add(pattern_bytes))
478 })
479 .unwrap_or(u64::MAX);
480 ctx.charge_match_work(decode_work)?;
481
482 let text_len = u64::try_from(text.chars().count()).unwrap_or(u64::MAX);
483 let pattern_len = wildcard_pattern_token_count(pattern);
484 let work = text_len
485 .checked_add(1)
486 .and_then(|rows| {
487 pattern_len
488 .checked_add(1)
489 .and_then(|cols| rows.checked_mul(cols))
490 })
491 .unwrap_or(u64::MAX);
492 ctx.charge_match_work(work)?;
493
494 let tokens = tokenize_wildcard_pattern(pattern)?;
495 let row_len = tokens
496 .len()
497 .checked_add(1)
498 .ok_or_else(|| Error::Internal("wildcard search row length overflow".to_string()))?;
499 let mut row = Vec::new();
500 row.try_reserve_exact(row_len)
501 .map_err(|_| Error::Internal("wildcard search allocation failed".to_string()))?;
502 row.resize(row_len, None);
503 let mut next = Vec::new();
504 next.try_reserve_exact(row_len)
505 .map_err(|_| Error::Internal("wildcard search allocation failed".to_string()))?;
506 next.resize(row_len, None);
507
508 for (char_index, text_char) in text.chars().enumerate() {
509 row[0] = Some(char_index);
510 apply_wildcard_search_epsilon(&mut row, &tokens);
511 if let Some(start) = row[row_len - 1] {
512 return Ok(Some(start));
513 }
514
515 next.fill(None);
516 for index in 1..row_len {
517 next[index] = match tokens[index - 1] {
518 WildcardToken::Star => row[index],
519 WildcardToken::AnyOne => row[index - 1],
520 WildcardToken::Literal(pattern_char) => {
521 row[index - 1].filter(|_| chars_equal_case_insensitive(text_char, pattern_char))
522 }
523 };
524 }
525 std::mem::swap(&mut row, &mut next);
526 }
527
528 row[0] = Some(usize::try_from(text_len).unwrap_or(usize::MAX));
529 apply_wildcard_search_epsilon(&mut row, &tokens);
530 Ok(row[row_len - 1])
531}
532
533fn apply_wildcard_search_epsilon(row: &mut [Option<usize>], tokens: &[WildcardToken]) {
534 for index in 1..row.len() {
535 if matches!(tokens[index - 1], WildcardToken::Star) {
536 row[index] = match (row[index], row[index - 1]) {
537 (Some(current), Some(candidate)) => Some(current.min(candidate)),
538 (current @ Some(_), None) => current,
539 (None, candidate) => candidate,
540 };
541 }
542 }
543}
544
545fn wildcard_pattern_token_count(pattern: &str) -> u64 {
546 let mut count = 0u64;
547 let mut previous_was_star = false;
548 let mut chars = pattern.chars().peekable();
549 while let Some(ch) = chars.next() {
550 let is_star = ch == '*';
551 if ch == '~' && matches!(chars.peek(), Some('*' | '?' | '~')) {
552 chars.next();
553 }
554 if is_star && previous_was_star {
555 continue;
556 }
557 count = count.saturating_add(1);
558 previous_was_star = is_star;
559 }
560 count
561}
562
563fn tokenize_wildcard_pattern(pattern: &str) -> Result<Vec<WildcardToken>> {
564 let mut tokens = Vec::new();
565 tokens
566 .try_reserve_exact(pattern.len())
567 .map_err(|_| Error::Internal("wildcard pattern allocation failed".to_string()))?;
568 let mut chars = pattern.chars().peekable();
569 while let Some(ch) = chars.next() {
570 let token = match ch {
571 '*' => WildcardToken::Star,
572 '?' => WildcardToken::AnyOne,
573 '~' => match chars.peek().copied() {
574 Some(next @ ('*' | '?' | '~')) => {
575 chars.next();
576 WildcardToken::Literal(next)
577 }
578 _ => WildcardToken::Literal('~'),
579 },
580 literal => WildcardToken::Literal(literal),
581 };
582 if matches!(token, WildcardToken::Star)
583 && matches!(tokens.last(), Some(WildcardToken::Star))
584 {
585 continue;
586 }
587 tokens.push(token);
588 }
589 Ok(tokens)
590}
591
592fn chars_equal_case_insensitive(left: char, right: char) -> bool {
593 left.to_lowercase().eq(right.to_lowercase())
594}
595
596pub fn collect_criteria_range_values(arg: &Expr, ctx: &mut Evaluator) -> Result<Vec<CellValue>> {
598 match arg {
599 Expr::Range { start, end } => ctx.expand_range(start, end),
600 _ => {
601 let v = ctx.eval_expr(arg)?;
602 Ok(vec![v])
603 }
604 }
605}
606
607pub(crate) fn error_value(value: &CellValue) -> Option<CellValue> {
610 match value {
611 CellValue::Error(_) => Some(value.clone()),
612 CellValue::Formula {
613 result: Some(inner),
614 ..
615 } => error_value(inner),
616 _ => None,
617 }
618}
619
620pub(crate) fn eval_text_arg(
625 expr: &Expr,
626 ctx: &mut Evaluator,
627) -> Result<std::result::Result<String, CellValue>> {
628 let value = ctx.eval_expr(expr)?;
629 Ok(match error_value(&value) {
630 Some(error) => Err(error),
631 None => Ok(coerce_to_string(&value)),
632 })
633}
634
635fn fn_sum(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
638 check_arg_count("SUM", args, 1, 255)?;
639 let nums = ctx.collect_numbers(args)?;
640 Ok(CellValue::Number(nums.iter().sum()))
641}
642
643fn fn_average(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
644 check_arg_count("AVERAGE", args, 1, 255)?;
645 let nums = ctx.collect_numbers(args)?;
646 if nums.is_empty() {
647 return Ok(CellValue::Error("#DIV/0!".to_string()));
648 }
649 let sum: f64 = nums.iter().sum();
650 Ok(CellValue::Number(sum / nums.len() as f64))
651}
652
653fn fn_count(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
659 check_arg_count("COUNT", args, 1, 255)?;
660 let mut count = 0usize;
661 for arg in args {
662 match arg {
663 Expr::Range { start, end } => {
664 let values = ctx.expand_range(start, end)?;
665 count += values
666 .iter()
667 .filter(|v| crate::formula::eval::reference_number(v).is_some())
668 .count();
669 }
670 Expr::CellRef(_) => {
671 let v = ctx.eval_expr(arg)?;
672 count += usize::from(crate::formula::eval::reference_number(&v).is_some());
673 }
674 _ => {
675 let v = ctx.eval_expr(arg)?;
676 let counted = !matches!(v, CellValue::Error(_))
677 && crate::formula::eval::coerce_to_number(&v).is_ok_and(f64::is_finite);
678 count += usize::from(counted);
679 }
680 }
681 }
682 Ok(CellValue::Number(count as f64))
683}
684
685fn fn_counta(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
686 check_arg_count("COUNTA", args, 1, 255)?;
687 let values = ctx.flatten_args_to_values(args)?;
688 let count = values
689 .iter()
690 .filter(|v| !matches!(v, CellValue::Empty))
691 .count();
692 Ok(CellValue::Number(count as f64))
693}
694
695fn fn_min(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
696 check_arg_count("MIN", args, 1, 255)?;
697 let nums = ctx.collect_numbers(args)?;
698 if nums.is_empty() {
699 return Ok(CellValue::Number(0.0));
700 }
701 let min = nums.iter().copied().fold(f64::INFINITY, f64::min);
702 Ok(CellValue::Number(min))
703}
704
705fn fn_max(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
706 check_arg_count("MAX", args, 1, 255)?;
707 let nums = ctx.collect_numbers(args)?;
708 if nums.is_empty() {
709 return Ok(CellValue::Number(0.0));
710 }
711 let max = nums.iter().copied().fold(f64::NEG_INFINITY, f64::max);
712 Ok(CellValue::Number(max))
713}
714
715fn fn_if(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
718 check_arg_count("IF", args, 1, 3)?;
719 let cond = ctx.eval_expr(&args[0])?;
720 let truth = crate::formula::eval::coerce_to_bool(&cond)?;
721 if truth {
722 if args.len() > 1 {
723 ctx.eval_expr(&args[1])
724 } else {
725 Ok(CellValue::Bool(true))
726 }
727 } else if args.len() > 2 {
728 ctx.eval_expr(&args[2])
729 } else {
730 Ok(CellValue::Bool(false))
731 }
732}
733
734fn fn_and(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
735 check_arg_count("AND", args, 1, 255)?;
736 let values = ctx.flatten_args_to_values(args)?;
737 for v in &values {
738 if matches!(v, CellValue::Empty) {
739 continue;
740 }
741 if !crate::formula::eval::coerce_to_bool(v)? {
742 return Ok(CellValue::Bool(false));
743 }
744 }
745 Ok(CellValue::Bool(true))
746}
747
748fn fn_or(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
749 check_arg_count("OR", args, 1, 255)?;
750 let values = ctx.flatten_args_to_values(args)?;
751 for v in &values {
752 if matches!(v, CellValue::Empty) {
753 continue;
754 }
755 if crate::formula::eval::coerce_to_bool(v)? {
756 return Ok(CellValue::Bool(true));
757 }
758 }
759 Ok(CellValue::Bool(false))
760}
761
762fn fn_not(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
763 check_arg_count("NOT", args, 1, 1)?;
764 let v = ctx.eval_expr(&args[0])?;
765 let b = crate::formula::eval::coerce_to_bool(&v)?;
766 Ok(CellValue::Bool(!b))
767}
768
769fn fn_abs(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
772 check_arg_count("ABS", args, 1, 1)?;
773 let v = ctx.eval_expr(&args[0])?;
774 let n = crate::formula::eval::coerce_to_number(&v)?;
775 Ok(CellValue::Number(n.abs()))
776}
777
778fn fn_int(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
779 check_arg_count("INT", args, 1, 1)?;
780 let v = ctx.eval_expr(&args[0])?;
781 let n = crate::formula::eval::coerce_to_number(&v)?;
782 Ok(CellValue::Number(n.floor()))
783}
784
785fn fn_round(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
786 check_arg_count("ROUND", args, 2, 2)?;
787 let v = ctx.eval_expr(&args[0])?;
788 let d = ctx.eval_expr(&args[1])?;
789 let n = crate::formula::eval::coerce_to_number(&v)?;
790 let digits = crate::formula::eval::coerce_to_number(&d)?;
791 Ok(math::round_to_digits(n, digits, math::RoundMode::Nearest))
792}
793
794fn fn_mod(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
795 check_arg_count("MOD", args, 2, 2)?;
796 let a = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[0])?)?;
797 let b = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[1])?)?;
798 if b == 0.0 {
799 return Ok(CellValue::Error("#DIV/0!".to_string()));
800 }
801 let result = a - (a / b).floor() * b;
803 Ok(CellValue::Number(result))
804}
805
806fn fn_power(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
807 check_arg_count("POWER", args, 2, 2)?;
808 let base = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[0])?)?;
809 let exp = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[1])?)?;
810 let result = base.powf(exp);
811 if !result.is_finite() {
812 return Ok(CellValue::Error("#NUM!".to_string()));
813 }
814 Ok(CellValue::Number(result))
815}
816
817fn fn_sqrt(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
818 check_arg_count("SQRT", args, 1, 1)?;
819 let n = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[0])?)?;
820 if n < 0.0 {
821 return Ok(CellValue::Error("#NUM!".to_string()));
822 }
823 Ok(CellValue::Number(n.sqrt()))
824}
825
826fn fn_len(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
829 check_arg_count("LEN", args, 1, 1)?;
830 let s = match eval_text_arg(&args[0], ctx)? {
831 Ok(s) => s,
832 Err(error) => return Ok(error),
833 };
834 Ok(CellValue::Number(s.chars().count() as f64))
835}
836
837fn fn_lower(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
838 check_arg_count("LOWER", args, 1, 1)?;
839 let s = match eval_text_arg(&args[0], ctx)? {
840 Ok(s) => s,
841 Err(error) => return Ok(error),
842 };
843 Ok(CellValue::String(s.to_lowercase()))
844}
845
846fn fn_upper(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
847 check_arg_count("UPPER", args, 1, 1)?;
848 let s = match eval_text_arg(&args[0], ctx)? {
849 Ok(s) => s,
850 Err(error) => return Ok(error),
851 };
852 Ok(CellValue::String(s.to_uppercase()))
853}
854
855fn fn_trim(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
856 check_arg_count("TRIM", args, 1, 1)?;
857 let s = match eval_text_arg(&args[0], ctx)? {
858 Ok(s) => s,
859 Err(error) => return Ok(error),
860 };
861 let trimmed: String = s.split_whitespace().collect::<Vec<_>>().join(" ");
863 Ok(CellValue::String(trimmed))
864}
865
866fn fn_left(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
867 check_arg_count("LEFT", args, 1, 2)?;
868 let s = match eval_text_arg(&args[0], ctx)? {
869 Ok(s) => s,
870 Err(error) => return Ok(error),
871 };
872 let n = if args.len() > 1 {
873 let count = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[1])?)?;
874 let Some(count) = conversion::checked_trunc_count(count) else {
875 return Ok(CellValue::Error("#VALUE!".to_string()));
876 };
877 count
878 } else {
879 1
880 };
881 let result: String = s.chars().take(n).collect();
882 Ok(CellValue::String(result))
883}
884
885fn fn_right(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
886 check_arg_count("RIGHT", args, 1, 2)?;
887 let s = match eval_text_arg(&args[0], ctx)? {
888 Ok(s) => s,
889 Err(error) => return Ok(error),
890 };
891 let n = if args.len() > 1 {
892 let count = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[1])?)?;
893 let Some(count) = conversion::checked_trunc_count(count) else {
894 return Ok(CellValue::Error("#VALUE!".to_string()));
895 };
896 count
897 } else {
898 1
899 };
900 let chars: Vec<char> = s.chars().collect();
901 let start = chars.len().saturating_sub(n);
902 let result: String = chars[start..].iter().collect();
903 Ok(CellValue::String(result))
904}
905
906const MID_MAX_START: usize = 2_147_483_648;
908const MID_MAX_COUNT: usize = 2_147_483_647;
910
911fn fn_mid(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
916 check_arg_count("MID", args, 3, 3)?;
917 let s = match eval_text_arg(&args[0], ctx)? {
918 Ok(s) => s,
919 Err(error) => return Ok(error),
920 };
921 let start = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[1])?)?;
922 let count = crate::formula::eval::coerce_to_number(&ctx.eval_expr(&args[2])?)?;
923 let (Some(start), Some(count)) = (
924 conversion::checked_trunc_count(start),
925 conversion::checked_trunc_count(count),
926 ) else {
927 return Ok(CellValue::Error("#VALUE!".to_string()));
928 };
929 if !(1..=MID_MAX_START).contains(&start) || count > MID_MAX_COUNT {
930 return Ok(CellValue::Error("#VALUE!".to_string()));
931 }
932 let result: String = s.chars().skip(start - 1).take(count).collect();
933 Ok(CellValue::String(result))
934}
935
936fn fn_concatenate(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
937 check_arg_count("CONCATENATE", args, 1, 255)?;
938 let mut result = String::new();
939 for arg in args {
940 match eval_text_arg(arg, ctx)? {
941 Ok(s) => result.push_str(&s),
942 Err(error) => return Ok(error),
943 }
944 }
945 Ok(CellValue::String(result))
946}
947
948fn fn_isnumber(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
951 check_arg_count("ISNUMBER", args, 1, 1)?;
952 let v = ctx.eval_expr(&args[0])?;
953 Ok(CellValue::Bool(matches!(
954 v,
955 CellValue::Number(_) | CellValue::Date(_)
956 )))
957}
958
959fn fn_istext(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
960 check_arg_count("ISTEXT", args, 1, 1)?;
961 let v = ctx.eval_expr(&args[0])?;
962 Ok(CellValue::Bool(matches!(v, CellValue::String(_))))
963}
964
965fn fn_isblank(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
966 check_arg_count("ISBLANK", args, 1, 1)?;
967 let v = ctx.eval_expr(&args[0])?;
968 Ok(CellValue::Bool(matches!(v, CellValue::Empty)))
969}
970
971fn fn_iserror(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
972 check_arg_count("ISERROR", args, 1, 1)?;
973 let v = ctx.eval_expr(&args[0])?;
974 Ok(CellValue::Bool(matches!(v, CellValue::Error(_))))
975}
976
977fn fn_value(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
980 check_arg_count("VALUE", args, 1, 1)?;
981 let v = ctx.eval_expr(&args[0])?;
982 if let Some(error) = error_value(&v) {
983 return Ok(error);
984 }
985 match crate::formula::eval::coerce_to_number(&v) {
986 Ok(n) => Ok(CellValue::Number(n)),
987 Err(_) => Ok(CellValue::Error("#VALUE!".to_string())),
988 }
989}
990
991fn fn_text(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
992 check_arg_count("TEXT", args, 2, 2)?;
993 let value = ctx.eval_expr(&args[0])?;
994 let format = ctx.eval_expr(&args[1])?;
995 let CellValue::String(format_code) = format else {
996 return Ok(CellValue::Error("#VALUE!".to_string()));
997 };
998 let Ok(number) = crate::formula::eval::coerce_to_number(&value) else {
999 return Ok(CellValue::Error("#VALUE!".to_string()));
1000 };
1001 Ok(CellValue::String(crate::numfmt::format_number(
1002 number,
1003 &format_code,
1004 )))
1005}
1006
1007#[cfg(test)]
1008mod tests {
1009 use super::{engineering, financial, matches_criteria, math, text, wildcard_match};
1010 use crate::cell::CellValue;
1011 use crate::formula::ast::Expr;
1012 use crate::formula::budget::{FormulaBudget, FormulaLimits};
1013 use crate::formula::eval::{evaluate, CellSnapshot, Evaluator};
1014 use crate::formula::parser::parse_formula;
1015 use crate::rich_text::RichTextRun;
1016
1017 fn eval(formula: &str) -> CellValue {
1018 let snapshot = CellSnapshot::new("Sheet1".to_string());
1019 let expr = parse_formula(formula).unwrap();
1020 evaluate(&expr, &snapshot).unwrap()
1021 }
1022
1023 #[test]
1024 fn numeric_criteria_does_not_match_blank() {
1025 let snapshot = CellSnapshot::new("Sheet1".to_string());
1026 let evaluator = Evaluator::new(&snapshot);
1027 assert!(!matches_criteria(&CellValue::Empty, "0", &evaluator).unwrap());
1028 assert!(matches_criteria(&CellValue::Number(0.0), "0", &evaluator).unwrap());
1029 assert!(matches_criteria(&CellValue::Empty, "", &evaluator).unwrap());
1030 assert!(matches_criteria(&CellValue::Empty, "<>word", &evaluator).unwrap());
1031 }
1032
1033 #[test]
1034 fn wildcard_match_preserves_excel_escaping_and_unicode_semantics() {
1035 let snapshot = CellSnapshot::new("Sheet1".to_string());
1036 let evaluator = Evaluator::new(&snapshot);
1037
1038 assert!(wildcard_match("CAFÉ", "caf?", &evaluator).unwrap());
1039 assert!(wildcard_match("report.xlsx", "*.xlsx", &evaluator).unwrap());
1040 assert!(wildcard_match("*", "~*", &evaluator).unwrap());
1041 assert!(wildcard_match("?", "~?", &evaluator).unwrap());
1042 assert!(wildcard_match("~", "~~", &evaluator).unwrap());
1043 assert!(!wildcard_match("ab", "a?c", &evaluator).unwrap());
1044 }
1045
1046 #[test]
1047 fn wildcard_match_work_is_exact_and_cumulative() {
1048 let snapshot = CellSnapshot::new("Sheet1".to_string());
1049 let limits = FormulaLimits {
1050 operation_match_work: 17,
1051 ..FormulaLimits::secure_default()
1052 };
1053 let budget = FormulaBudget::for_calculation(limits).unwrap();
1054 let evaluator = Evaluator::with_budget(&snapshot, budget);
1055 assert!(wildcard_match("abc", "a*", &evaluator).unwrap());
1056
1057 let limits = FormulaLimits {
1058 operation_match_work: 16,
1059 ..FormulaLimits::secure_default()
1060 };
1061 let budget = FormulaBudget::for_calculation(limits).unwrap();
1062 let evaluator = Evaluator::with_budget(&snapshot, budget);
1063 let error = wildcard_match("abc", "a*", &evaluator).unwrap_err();
1064 let metadata = error.resource_metadata().unwrap();
1065 assert_eq!(metadata.dimension_code(), "match_work");
1066 assert_eq!((metadata.limit(), metadata.observed()), (16, 17));
1067
1068 let limits = FormulaLimits {
1069 operation_match_work: 33,
1070 ..FormulaLimits::secure_default()
1071 };
1072 let budget = FormulaBudget::for_calculation(limits).unwrap();
1073 let evaluator = Evaluator::with_budget(&snapshot, budget);
1074 assert!(wildcard_match("abc", "a*", &evaluator).unwrap());
1075 let error = wildcard_match("abc", "a*", &evaluator).unwrap_err();
1076 assert_eq!(error.resource_metadata().unwrap().observed(), 34);
1077 }
1078
1079 #[test]
1080 fn criteria_families_share_one_match_budget() {
1081 let mut snapshot = CellSnapshot::new("Sheet1".to_string());
1082 snapshot.set_cell("Sheet1", 1, 1, CellValue::String("CAFÉ".to_string()));
1083 snapshot.set_cell("Sheet1", 2, 1, CellValue::Number(2.0));
1084 let expression = parse_formula(
1085 "SUM(SUMIF(A1,\"caf?\",B1),SUMIFS(B1,A1,\"caf?\"),AVERAGEIF(A1,\"caf?\",B1),AVERAGEIFS(B1,A1,\"caf?\"),COUNTIFS(A1,\"caf?\"))",
1086 )
1087 .unwrap();
1088
1089 let exact = FormulaLimits {
1090 operation_match_work: 170,
1091 ..FormulaLimits::secure_default()
1092 };
1093 let budget = FormulaBudget::for_calculation(exact).unwrap();
1094 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1095 assert_eq!(
1096 evaluator.eval_expr(&expression).unwrap(),
1097 CellValue::Number(9.0)
1098 );
1099
1100 let over = FormulaLimits {
1101 operation_match_work: 169,
1102 ..FormulaLimits::secure_default()
1103 };
1104 let budget = FormulaBudget::for_calculation(over).unwrap();
1105 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1106 let error = evaluator.eval_expr(&expression).unwrap_err();
1107 let metadata = error.resource_metadata().unwrap();
1108 assert_eq!(metadata.dimension_code(), "match_work");
1109 assert_eq!((metadata.limit(), metadata.observed()), (169, 170));
1110 }
1111
1112 #[test]
1113 fn rich_text_criterion_is_precharged_and_flattened_once() {
1114 let mut snapshot = CellSnapshot::new("Sheet1".to_string());
1115 for row in 1..=3 {
1116 snapshot.set_cell("Sheet1", 1, row, CellValue::String("abc".to_string()));
1117 snapshot.set_cell("Sheet1", 3, row, CellValue::Number(f64::from(row)));
1118 }
1119 snapshot.set_cell(
1120 "Sheet1",
1121 2,
1122 1,
1123 CellValue::RichString(vec![
1124 RichTextRun {
1125 text: "*".to_string(),
1126 font: None,
1127 size: None,
1128 bold: false,
1129 italic: false,
1130 color: None,
1131 },
1132 RichTextRun {
1133 text: "a*".to_string(),
1134 font: None,
1135 size: None,
1136 bold: false,
1137 italic: false,
1138 color: None,
1139 },
1140 ]),
1141 );
1142 let expression = parse_formula("SUMIF(A1:A3,B1,C1:C3)").unwrap();
1143
1144 let exact = FormulaLimits {
1145 operation_match_work: 60,
1146 ..FormulaLimits::secure_default()
1147 };
1148 let budget = FormulaBudget::for_calculation(exact).unwrap();
1149 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1150 assert_eq!(
1151 evaluator.eval_expr(&expression).unwrap(),
1152 CellValue::Number(6.0)
1153 );
1154
1155 let over = FormulaLimits {
1156 operation_match_work: 59,
1157 ..FormulaLimits::secure_default()
1158 };
1159 let budget = FormulaBudget::for_calculation(over).unwrap();
1160 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1161 let error = evaluator.eval_expr(&expression).unwrap_err();
1162 let metadata = error.resource_metadata().unwrap();
1163 assert_eq!(metadata.dimension_code(), "match_work");
1164 assert_eq!((metadata.limit(), metadata.observed()), (59, 60));
1165 }
1166
1167 fn assert_evaluation_work_boundary(
1168 function: super::FunctionFn,
1169 args: &[Expr],
1170 exact_work: u64,
1171 ) {
1172 let snapshot = CellSnapshot::new("Sheet1".to_string());
1173 let limits = FormulaLimits {
1174 operation_evaluation_steps: exact_work,
1175 ..FormulaLimits::secure_default()
1176 };
1177 let budget = FormulaBudget::for_calculation(limits).unwrap();
1178 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1179 function(args, &mut evaluator).unwrap();
1180
1181 let limits = FormulaLimits {
1182 operation_evaluation_steps: exact_work - 1,
1183 ..FormulaLimits::secure_default()
1184 };
1185 let budget = FormulaBudget::for_calculation(limits).unwrap();
1186 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1187 let error = function(args, &mut evaluator).unwrap_err();
1188 let metadata = error.resource_metadata().unwrap();
1189 assert_eq!(metadata.dimension_code(), "operation_count");
1190 assert_eq!(
1191 (metadata.limit(), metadata.observed()),
1192 (exact_work - 1, exact_work)
1193 );
1194 }
1195
1196 #[test]
1197 fn iterative_low_formula_functions_charge_exact_work() {
1198 assert_evaluation_work_boundary(math::fn_fact, &[Expr::Number(5.0)], 5);
1199 assert_evaluation_work_boundary(
1200 financial::fn_db,
1201 &[
1202 Expr::Number(1_000.0),
1203 Expr::Number(100.0),
1204 Expr::Number(4.0),
1205 Expr::Number(4.0),
1206 ],
1207 6,
1208 );
1209 assert_evaluation_work_boundary(
1210 financial::fn_ddb,
1211 &[
1212 Expr::Number(1_000.0),
1213 Expr::Number(100.0),
1214 Expr::Number(4.0),
1215 Expr::Number(3.0),
1216 ],
1217 7,
1218 );
1219 assert_evaluation_work_boundary(
1220 financial::fn_cumipmt,
1221 &[
1222 Expr::Number(0.01),
1223 Expr::Number(3.0),
1224 Expr::Number(1_000.0),
1225 Expr::Number(1.0),
1226 Expr::Number(3.0),
1227 Expr::Number(0.0),
1228 ],
1229 9,
1230 );
1231 for function in [engineering::fn_besselk, engineering::fn_bessely] {
1232 assert_evaluation_work_boundary(function, &[Expr::Number(16.0), Expr::Number(3.0)], 4);
1233 }
1234 }
1235
1236 #[test]
1237 fn iterative_formula_work_cannot_reset_between_calls() {
1238 let snapshot = CellSnapshot::new("Sheet1".to_string());
1239 let limits = FormulaLimits {
1240 operation_evaluation_steps: 9,
1241 ..FormulaLimits::secure_default()
1242 };
1243 let budget = FormulaBudget::for_calculation(limits).unwrap();
1244 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1245 let args = [Expr::Number(5.0)];
1246 math::fn_fact(&args, &mut evaluator).unwrap();
1247 let error = math::fn_fact(&args, &mut evaluator).unwrap_err();
1248 assert_eq!(error.resource_metadata().unwrap().observed(), 10);
1249 }
1250
1251 #[test]
1252 fn iterative_formula_inputs_use_checked_conversions() {
1253 let snapshot = CellSnapshot::new("Sheet1".to_string());
1254 for function in [engineering::fn_besselk, engineering::fn_bessely] {
1255 let mut evaluator = Evaluator::new(&snapshot);
1256 assert_eq!(
1257 function(&[Expr::Number(16.0), Expr::Number(1e100)], &mut evaluator,).unwrap(),
1258 CellValue::Error("#NUM!".to_string())
1259 );
1260 }
1261 let mut evaluator = Evaluator::new(&snapshot);
1262 assert_eq!(
1263 financial::fn_cumipmt(
1264 &[
1265 Expr::Number(0.01),
1266 Expr::Number(3.0),
1267 Expr::Number(1_000.0),
1268 Expr::Number(1.0),
1269 Expr::Number(1e100),
1270 Expr::Number(0.0),
1271 ],
1272 &mut evaluator,
1273 )
1274 .unwrap(),
1275 CellValue::Error("#NUM!".to_string())
1276 );
1277
1278 let mut evaluator = Evaluator::new(&snapshot);
1279 let fact_error = math::fn_fact(&[Expr::Number(1e100)], &mut evaluator).unwrap_err();
1280 assert_eq!(
1281 fact_error.resource_metadata().unwrap().dimension_code(),
1282 "operation_count"
1283 );
1284
1285 for (function, args) in [
1286 (
1287 financial::fn_db as super::FunctionFn,
1288 vec![
1289 Expr::Number(1_000.0),
1290 Expr::Number(100.0),
1291 Expr::Number(f64::from(i32::MAX - 1)),
1292 Expr::Number(f64::from(i32::MAX - 1)),
1293 ],
1294 ),
1295 (
1296 financial::fn_ddb as super::FunctionFn,
1297 vec![
1298 Expr::Number(1_000.0),
1299 Expr::Number(100.0),
1300 Expr::Number(f64::from(i32::MAX)),
1301 Expr::Number(f64::from(i32::MAX)),
1302 ],
1303 ),
1304 ] {
1305 let mut evaluator = Evaluator::new(&snapshot);
1306 let error = function(&args, &mut evaluator).unwrap_err();
1307 assert_eq!(
1308 error.resource_metadata().unwrap().dimension_code(),
1309 "operation_count"
1310 );
1311 }
1312 }
1313
1314 #[test]
1315 fn countif_and_search_share_exact_match_work() {
1316 let mut snapshot = CellSnapshot::new("Sheet1".to_string());
1317 snapshot.set_cell("Sheet1", 1, 1, CellValue::String("abc".to_string()));
1318 let countif = parse_formula("COUNTIF(A1,\"a*\")").unwrap();
1319 let limits = FormulaLimits {
1320 operation_match_work: 17,
1321 ..FormulaLimits::secure_default()
1322 };
1323 let budget = FormulaBudget::for_calculation(limits).unwrap();
1324 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1325 assert_eq!(
1326 evaluator.eval_expr(&countif).unwrap(),
1327 CellValue::Number(1.0)
1328 );
1329
1330 let limits = FormulaLimits {
1331 operation_match_work: 16,
1332 ..FormulaLimits::secure_default()
1333 };
1334 let budget = FormulaBudget::for_calculation(limits).unwrap();
1335 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1336 let error = evaluator.eval_expr(&countif).unwrap_err();
1337 assert_eq!(error.resource_metadata().unwrap().observed(), 17);
1338
1339 let args = [
1340 Expr::String("a*c".to_string()),
1341 Expr::String("abc".to_string()),
1342 ];
1343 let limits = FormulaLimits {
1344 operation_match_work: 22,
1345 ..FormulaLimits::secure_default()
1346 };
1347 let budget = FormulaBudget::for_calculation(limits).unwrap();
1348 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1349 assert_eq!(
1350 text::fn_search(&args, &mut evaluator).unwrap(),
1351 CellValue::Number(1.0)
1352 );
1353
1354 let limits = FormulaLimits {
1355 operation_match_work: 21,
1356 ..FormulaLimits::secure_default()
1357 };
1358 let budget = FormulaBudget::for_calculation(limits).unwrap();
1359 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1360 let error = text::fn_search(&args, &mut evaluator).unwrap_err();
1361 assert_eq!(error.resource_metadata().unwrap().observed(), 22);
1362 }
1363
1364 #[test]
1365 fn search_preserves_excel_escaping_unicode_and_cumulative_work() {
1366 assert_eq!(eval(r#"SEARCH("~*","a*b")"#), CellValue::Number(2.0));
1367 assert_eq!(eval(r#"SEARCH("~?","a?b")"#), CellValue::Number(2.0));
1368 assert_eq!(eval(r#"SEARCH("~~","a~b")"#), CellValue::Number(2.0));
1369 assert_eq!(eval(r#"SEARCH("caf?","xxCAFÉ")"#), CellValue::Number(3.0));
1370
1371 let snapshot = CellSnapshot::new("Sheet1".to_string());
1372 let limits = FormulaLimits {
1373 operation_match_work: 43,
1374 ..FormulaLimits::secure_default()
1375 };
1376 let budget = FormulaBudget::for_calculation(limits).unwrap();
1377 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1378 let args = [
1379 Expr::String("a*c".to_string()),
1380 Expr::String("abc".to_string()),
1381 ];
1382 text::fn_search(&args, &mut evaluator).unwrap();
1383 let error = text::fn_search(&args, &mut evaluator).unwrap_err();
1384 assert_eq!(error.resource_metadata().unwrap().observed(), 44);
1385 }
1386
1387 #[test]
1388 fn search_rejects_large_failure_before_state_allocation() {
1389 let snapshot = CellSnapshot::new("Sheet1".to_string());
1390 let args = [
1391 Expr::String(format!("{}z", "*a".repeat(512))),
1392 Expr::String("a".repeat(1_024)),
1393 ];
1394 let mut evaluator = Evaluator::new(&snapshot);
1395 let error = text::fn_search(&args, &mut evaluator).unwrap_err();
1396 let metadata = error.resource_metadata().unwrap();
1397 assert_eq!(metadata.dimension_code(), "match_work");
1398 assert!(metadata.observed() > metadata.limit());
1399 }
1400
1401 #[test]
1402 fn substitute_and_width_outputs_are_precharged_exactly() {
1403 let snapshot = CellSnapshot::new("Sheet1".to_string());
1404 let substitute = Expr::Function {
1405 name: "SUBSTITUTE".to_string(),
1406 args: vec![
1407 Expr::String("aaaa".to_string()),
1408 Expr::String("a".to_string()),
1409 Expr::String("bb".to_string()),
1410 ],
1411 };
1412 let limits = FormulaLimits {
1413 operation_output_bytes: 15,
1414 ..FormulaLimits::secure_default()
1415 };
1416 let budget = FormulaBudget::for_calculation(limits).unwrap();
1417 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1418 assert_eq!(
1419 evaluator.eval_expr(&substitute).unwrap(),
1420 CellValue::String("bbbbbbbb".to_string())
1421 );
1422
1423 let limits = FormulaLimits {
1424 operation_output_bytes: 14,
1425 ..FormulaLimits::secure_default()
1426 };
1427 let budget = FormulaBudget::for_calculation(limits).unwrap();
1428 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1429 let error = evaluator.eval_expr(&substitute).unwrap_err();
1430 assert_eq!(error.resource_metadata().unwrap().observed(), 15);
1431
1432 for (function_name, args, exact) in [
1433 ("DEC2HEX", vec![Expr::Number(1.0), Expr::Number(10.0)], 10),
1434 (
1435 "HEX2BIN",
1436 vec![Expr::String("1".to_string()), Expr::Number(10.0)],
1437 11,
1438 ),
1439 (
1440 "HEX2OCT",
1441 vec![Expr::String("1".to_string()), Expr::Number(10.0)],
1442 11,
1443 ),
1444 ] {
1445 let expression = Expr::Function {
1446 name: function_name.to_string(),
1447 args,
1448 };
1449 let limits = FormulaLimits {
1450 operation_output_bytes: exact,
1451 ..FormulaLimits::secure_default()
1452 };
1453 let budget = FormulaBudget::for_calculation(limits).unwrap();
1454 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1455 evaluator.eval_expr(&expression).unwrap();
1456
1457 let limits = FormulaLimits {
1458 operation_output_bytes: exact - 1,
1459 ..FormulaLimits::secure_default()
1460 };
1461 let budget = FormulaBudget::for_calculation(limits).unwrap();
1462 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1463 let error = evaluator.eval_expr(&expression).unwrap_err();
1464 assert_eq!(error.resource_metadata().unwrap().observed(), exact);
1465 }
1466 }
1467
1468 #[test]
1469 fn substitute_rejects_large_amplification_before_allocation() {
1470 let snapshot = CellSnapshot::new("Sheet1".to_string());
1471 let args = [
1472 Expr::String("a".repeat(32_767)),
1473 Expr::String("a".to_string()),
1474 Expr::String("b".repeat(32_767)),
1475 ];
1476 let mut evaluator = Evaluator::new(&snapshot);
1477 let error = text::fn_substitute(&args, &mut evaluator).unwrap_err();
1478 let metadata = error.resource_metadata().unwrap();
1479 assert_eq!(metadata.dimension_code(), "output_bytes");
1480 assert!(metadata.observed() > metadata.limit());
1481 }
1482
1483 #[test]
1484 fn substitute_output_budget_is_cumulative() {
1485 let snapshot = CellSnapshot::new("Sheet1".to_string());
1486 let limits = FormulaLimits {
1487 operation_output_bytes: 29,
1488 ..FormulaLimits::secure_default()
1489 };
1490 let budget = FormulaBudget::for_calculation(limits).unwrap();
1491 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1492 let expression = Expr::Function {
1493 name: "SUBSTITUTE".to_string(),
1494 args: vec![
1495 Expr::String("aaaa".to_string()),
1496 Expr::String("a".to_string()),
1497 Expr::String("bb".to_string()),
1498 ],
1499 };
1500 evaluator.eval_expr(&expression).unwrap();
1501 let error = evaluator.eval_expr(&expression).unwrap_err();
1502 assert_eq!(error.resource_metadata().unwrap().observed(), 30);
1503 }
1504
1505 #[test]
1506 fn cumprinc_charges_checked_period_work_before_iteration() {
1507 let snapshot = CellSnapshot::new("Sheet1".to_string());
1508 let args = [
1509 Expr::Number(0.01),
1510 Expr::Number(3.0),
1511 Expr::Number(1_000.0),
1512 Expr::Number(1.0),
1513 Expr::Number(3.0),
1514 Expr::Number(0.0),
1515 ];
1516 let exact = FormulaLimits {
1517 operation_evaluation_steps: 9,
1518 ..FormulaLimits::secure_default()
1519 };
1520 let budget = FormulaBudget::for_calculation(exact).unwrap();
1521 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1522 assert!(matches!(
1523 financial::fn_cumprinc(&args, &mut evaluator).unwrap(),
1524 CellValue::Number(_)
1525 ));
1526
1527 let over = FormulaLimits {
1528 operation_evaluation_steps: 8,
1529 ..FormulaLimits::secure_default()
1530 };
1531 let budget = FormulaBudget::for_calculation(over).unwrap();
1532 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1533 let error = financial::fn_cumprinc(&args, &mut evaluator).unwrap_err();
1534 let metadata = error.resource_metadata().unwrap();
1535 assert_eq!(metadata.dimension_code(), "operation_count");
1536 assert_eq!((metadata.limit(), metadata.observed()), (8, 9));
1537 }
1538
1539 #[test]
1540 fn cumprinc_rejects_unrepresentable_periods_without_iteration() {
1541 let snapshot = CellSnapshot::new("Sheet1".to_string());
1542 let args = [
1543 Expr::Number(0.01),
1544 Expr::Number(3.0),
1545 Expr::Number(1_000.0),
1546 Expr::Number(1.0),
1547 Expr::Number(1e100),
1548 Expr::Number(0.0),
1549 ];
1550 let limits = FormulaLimits {
1551 operation_evaluation_steps: 6,
1552 ..FormulaLimits::secure_default()
1553 };
1554 let budget = FormulaBudget::for_calculation(limits).unwrap();
1555 let mut evaluator = Evaluator::with_budget(&snapshot, budget);
1556 assert_eq!(
1557 financial::fn_cumprinc(&args, &mut evaluator).unwrap(),
1558 CellValue::Error("#NUM!".to_string())
1559 );
1560 }
1561
1562 #[test]
1563 fn cumprinc_rejects_non_finite_intermediate_results() {
1564 let snapshot = CellSnapshot::new("Sheet1".to_string());
1565 let args = [
1566 Expr::Number(f64::MAX),
1567 Expr::Number(1.0),
1568 Expr::Number(f64::MAX),
1569 Expr::Number(1.0),
1570 Expr::Number(1.0),
1571 Expr::Number(0.0),
1572 ];
1573 let mut evaluator = Evaluator::new(&snapshot);
1574 assert_eq!(
1575 financial::fn_cumprinc(&args, &mut evaluator).unwrap(),
1576 CellValue::Error("#NUM!".to_string())
1577 );
1578 }
1579
1580 #[test]
1581 fn power_rejects_non_finite_results() {
1582 assert_eq!(eval("POWER(-1,0.5)"), CellValue::Error("#NUM!".to_string()));
1583 assert_eq!(
1584 eval("POWER(1E308,2)"),
1585 CellValue::Error("#NUM!".to_string())
1586 );
1587 }
1588
1589 #[test]
1590 fn len_counts_unicode_scalars() {
1591 assert_eq!(eval("LEN(\"café\")"), CellValue::Number(4.0));
1592 assert_eq!(eval("LEN(\"한글🚀\")"), CellValue::Number(3.0));
1593 }
1594
1595 #[test]
1596 fn text_applies_excel_number_formats() {
1597 assert_eq!(
1598 eval("TEXT(12.345,\"0.00\")"),
1599 CellValue::String("12.35".to_string())
1600 );
1601 assert_eq!(
1602 eval("TEXT(1234.5,\"#,##0.00\")"),
1603 CellValue::String("1,234.50".to_string())
1604 );
1605 assert_eq!(
1606 eval("TEXT(0.256,\"0.0%\")"),
1607 CellValue::String("25.6%".to_string())
1608 );
1609 assert_eq!(
1610 eval("TEXT(45292,\"m/d/yyyy\")"),
1611 CellValue::String("1/1/2024".to_string())
1612 );
1613 assert_eq!(
1614 eval("TEXT(0.625,\"h:mm AM/PM\")"),
1615 CellValue::String("3:00 PM".to_string())
1616 );
1617 }
1618
1619 #[test]
1620 fn text_requires_numeric_value_and_string_format() {
1621 assert_eq!(eval("TEXT(12,0)"), CellValue::Error("#VALUE!".to_string()));
1622 assert_eq!(
1623 eval("TEXT(\"word\",\"0.00\")"),
1624 CellValue::Error("#VALUE!".to_string())
1625 );
1626 }
1627}