1use super::super::utils::{ARG_ANY_ONE, coerce_num};
4use crate::args::{ArgSchema, ShapeKind};
5use crate::function::Function;
6use crate::traits::{ArgumentHandle, CalcValue, FunctionContext};
7use formualizer_common::{ArgKind, CoercionPolicy, ExcelError, ExcelErrorKind, LiteralValue};
8use formualizer_macros::func_caps;
9
10fn scalar_like_value(arg: &ArgumentHandle<'_, '_>) -> Result<LiteralValue, ExcelError> {
11 Ok(match arg.value()? {
12 CalcValue::Scalar(v) => v,
13 CalcValue::Range(rv) => rv.get_cell(0, 0),
14 })
15}
16
17fn coerce_text(v: &LiteralValue) -> String {
19 match v {
20 LiteralValue::Text(s) => s.clone(),
21 LiteralValue::Empty => String::new(),
22 LiteralValue::Boolean(b) => if *b { "TRUE" } else { "FALSE" }.to_string(),
23 LiteralValue::Int(i) => i.to_string(),
24 LiteralValue::Number(f) => {
25 let s = f.to_string();
26 if s.ends_with(".0") {
27 s[..s.len() - 2].to_string()
28 } else {
29 s
30 }
31 }
32 other => other.to_string(),
33 }
34}
35
36#[derive(Debug)]
41pub struct CleanFn;
42impl Function for CleanFn {
43 func_caps!(PURE);
44 fn name(&self) -> &'static str {
45 "CLEAN"
46 }
47 fn min_args(&self) -> usize {
48 1
49 }
50 fn arg_schema(&self) -> &'static [ArgSchema] {
51 &ARG_ANY_ONE[..]
52 }
53 fn eval<'a, 'b, 'c>(
54 &self,
55 args: &'c [ArgumentHandle<'a, 'b>],
56 _: &dyn FunctionContext<'b>,
57 ) -> Result<CalcValue<'b>, ExcelError> {
58 let v = scalar_like_value(&args[0])?;
59 let text = match v {
60 LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
61 other => coerce_text(&other),
62 };
63
64 let cleaned: String = text.chars().filter(|&c| c as u32 >= 32).collect();
66 Ok(CalcValue::Scalar(LiteralValue::Text(cleaned)))
67 }
68}
69
70#[derive(Debug)]
75pub struct UnicharFn;
76impl Function for UnicharFn {
77 func_caps!(PURE);
78 fn name(&self) -> &'static str {
79 "UNICHAR"
80 }
81 fn min_args(&self) -> usize {
82 1
83 }
84 fn arg_schema(&self) -> &'static [ArgSchema] {
85 &ARG_ANY_ONE[..]
86 }
87 fn eval<'a, 'b, 'c>(
88 &self,
89 args: &'c [ArgumentHandle<'a, 'b>],
90 _: &dyn FunctionContext<'b>,
91 ) -> Result<CalcValue<'b>, ExcelError> {
92 let v = scalar_like_value(&args[0])?;
93 let n = match v {
94 LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
95 other => coerce_num(&other)?,
96 };
97
98 let code = n.trunc() as u32;
99
100 if code == 0 || (0xD800..=0xDFFF).contains(&code) || code > 0x10FFFF {
102 return Ok(CalcValue::Scalar(LiteralValue::Error(
103 ExcelError::new_value(),
104 )));
105 }
106
107 match char::from_u32(code) {
108 Some(c) => Ok(CalcValue::Scalar(LiteralValue::Text(c.to_string()))),
109 None => Ok(CalcValue::Scalar(LiteralValue::Error(
110 ExcelError::new_value(),
111 ))),
112 }
113 }
114}
115
116#[derive(Debug)]
121pub struct UnicodeFn;
122impl Function for UnicodeFn {
123 func_caps!(PURE);
124 fn name(&self) -> &'static str {
125 "UNICODE"
126 }
127 fn min_args(&self) -> usize {
128 1
129 }
130 fn arg_schema(&self) -> &'static [ArgSchema] {
131 &ARG_ANY_ONE[..]
132 }
133 fn eval<'a, 'b, 'c>(
134 &self,
135 args: &'c [ArgumentHandle<'a, 'b>],
136 _: &dyn FunctionContext<'b>,
137 ) -> Result<CalcValue<'b>, ExcelError> {
138 let v = scalar_like_value(&args[0])?;
139 let text = match v {
140 LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
141 other => coerce_text(&other),
142 };
143
144 if text.is_empty() {
145 return Ok(CalcValue::Scalar(LiteralValue::Error(
146 ExcelError::new_value(),
147 )));
148 }
149
150 let code = text.chars().next().unwrap() as u32;
151 Ok(CalcValue::Scalar(LiteralValue::Number(code as f64)))
152 }
153}
154
155fn arg_textbefore() -> Vec<ArgSchema> {
160 vec![
161 ArgSchema {
162 kinds: smallvec::smallvec![ArgKind::Any],
163 required: true,
164 by_ref: false,
165 shape: ShapeKind::Scalar,
166 coercion: CoercionPolicy::None,
167 max: None,
168 repeating: None,
169 default: None,
170 },
171 ArgSchema {
172 kinds: smallvec::smallvec![ArgKind::Any],
173 required: true,
174 by_ref: false,
175 shape: ShapeKind::Scalar,
176 coercion: CoercionPolicy::None,
177 max: None,
178 repeating: None,
179 default: None,
180 },
181 ArgSchema {
182 kinds: smallvec::smallvec![ArgKind::Number],
183 required: false,
184 by_ref: false,
185 shape: ShapeKind::Scalar,
186 coercion: CoercionPolicy::NumberLenientText,
187 max: None,
188 repeating: None,
189 default: Some(LiteralValue::Number(1.0)),
190 },
191 ]
192}
193
194#[derive(Debug)]
195pub struct TextBeforeFn;
196impl Function for TextBeforeFn {
197 func_caps!(PURE);
198 fn name(&self) -> &'static str {
199 "TEXTBEFORE"
200 }
201 fn min_args(&self) -> usize {
202 2
203 }
204 fn arg_schema(&self) -> &'static [ArgSchema] {
205 use once_cell::sync::Lazy;
206 static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(arg_textbefore);
207 &SCHEMA
208 }
209 fn eval<'a, 'b, 'c>(
210 &self,
211 args: &'c [ArgumentHandle<'a, 'b>],
212 _: &dyn FunctionContext<'b>,
213 ) -> Result<CalcValue<'b>, ExcelError> {
214 let v1 = scalar_like_value(&args[0])?;
215 let text = match v1 {
216 LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
217 other => coerce_text(&other),
218 };
219
220 let v2 = scalar_like_value(&args[1])?;
221 let delimiter = match v2 {
222 LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
223 other => coerce_text(&other),
224 };
225
226 let instance = if args.len() >= 3 {
227 match scalar_like_value(&args[2])? {
228 LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
229 other => coerce_num(&other)?.trunc() as i32,
230 }
231 } else {
232 1
233 };
234
235 if delimiter.is_empty() {
236 return Ok(CalcValue::Scalar(LiteralValue::Error(
237 ExcelError::new_value(),
238 )));
239 }
240
241 if instance == 0 {
242 return Ok(CalcValue::Scalar(LiteralValue::Error(
243 ExcelError::new_value(),
244 )));
245 }
246
247 let result = if instance > 0 {
248 let mut pos = 0;
250 let mut found_count = 0;
251 for (idx, _) in text.match_indices(&delimiter) {
252 found_count += 1;
253 if found_count == instance {
254 pos = idx;
255 break;
256 }
257 }
258 if found_count < instance {
259 return Ok(CalcValue::Scalar(LiteralValue::Error(ExcelError::new(
260 ExcelErrorKind::Na,
261 ))));
262 }
263 text[..pos].to_string()
264 } else {
265 let matches: Vec<_> = text.match_indices(&delimiter).collect();
267 let idx = matches.len() as i32 + instance; if idx < 0 || idx as usize >= matches.len() {
269 return Ok(CalcValue::Scalar(LiteralValue::Error(ExcelError::new(
270 ExcelErrorKind::Na,
271 ))));
272 }
273 text[..matches[idx as usize].0].to_string()
274 };
275
276 Ok(CalcValue::Scalar(LiteralValue::Text(result)))
277 }
278}
279
280#[derive(Debug)]
285pub struct TextAfterFn;
286impl Function for TextAfterFn {
287 func_caps!(PURE);
288 fn name(&self) -> &'static str {
289 "TEXTAFTER"
290 }
291 fn min_args(&self) -> usize {
292 2
293 }
294 fn arg_schema(&self) -> &'static [ArgSchema] {
295 use once_cell::sync::Lazy;
296 static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(arg_textbefore);
297 &SCHEMA
298 }
299 fn eval<'a, 'b, 'c>(
300 &self,
301 args: &'c [ArgumentHandle<'a, 'b>],
302 _: &dyn FunctionContext<'b>,
303 ) -> Result<CalcValue<'b>, ExcelError> {
304 let v1 = scalar_like_value(&args[0])?;
305 let text = match v1 {
306 LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
307 other => coerce_text(&other),
308 };
309
310 let v2 = scalar_like_value(&args[1])?;
311 let delimiter = match v2 {
312 LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
313 other => coerce_text(&other),
314 };
315
316 let instance = if args.len() >= 3 {
317 match scalar_like_value(&args[2])? {
318 LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
319 other => coerce_num(&other)?.trunc() as i32,
320 }
321 } else {
322 1
323 };
324
325 if delimiter.is_empty() {
326 return Ok(CalcValue::Scalar(LiteralValue::Error(
327 ExcelError::new_value(),
328 )));
329 }
330
331 if instance == 0 {
332 return Ok(CalcValue::Scalar(LiteralValue::Error(
333 ExcelError::new_value(),
334 )));
335 }
336
337 let result = if instance > 0 {
338 let mut end_pos = 0;
340 let mut found_count = 0;
341 for (idx, matched) in text.match_indices(&delimiter) {
342 found_count += 1;
343 if found_count == instance {
344 end_pos = idx + matched.len();
345 break;
346 }
347 }
348 if found_count < instance {
349 return Ok(CalcValue::Scalar(LiteralValue::Error(ExcelError::new(
350 ExcelErrorKind::Na,
351 ))));
352 }
353 text[end_pos..].to_string()
354 } else {
355 let matches: Vec<_> = text.match_indices(&delimiter).collect();
357 let idx = matches.len() as i32 + instance;
358 if idx < 0 || idx as usize >= matches.len() {
359 return Ok(CalcValue::Scalar(LiteralValue::Error(ExcelError::new(
360 ExcelErrorKind::Na,
361 ))));
362 }
363 let (pos, matched) = matches[idx as usize];
364 text[pos + matched.len()..].to_string()
365 };
366
367 Ok(CalcValue::Scalar(LiteralValue::Text(result)))
368 }
369}
370
371fn arg_dollar() -> Vec<ArgSchema> {
376 vec![
377 ArgSchema {
378 kinds: smallvec::smallvec![ArgKind::Number],
379 required: true,
380 by_ref: false,
381 shape: ShapeKind::Scalar,
382 coercion: CoercionPolicy::NumberLenientText,
383 max: None,
384 repeating: None,
385 default: None,
386 },
387 ArgSchema {
388 kinds: smallvec::smallvec![ArgKind::Number],
389 required: false,
390 by_ref: false,
391 shape: ShapeKind::Scalar,
392 coercion: CoercionPolicy::NumberLenientText,
393 max: None,
394 repeating: None,
395 default: Some(LiteralValue::Number(2.0)),
396 },
397 ]
398}
399
400#[derive(Debug)]
401pub struct DollarFn;
402impl Function for DollarFn {
403 func_caps!(PURE);
404 fn name(&self) -> &'static str {
405 "DOLLAR"
406 }
407 fn min_args(&self) -> usize {
408 1
409 }
410 fn arg_schema(&self) -> &'static [ArgSchema] {
411 use once_cell::sync::Lazy;
412 static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(arg_dollar);
413 &SCHEMA
414 }
415 fn eval<'a, 'b, 'c>(
416 &self,
417 args: &'c [ArgumentHandle<'a, 'b>],
418 _: &dyn FunctionContext<'b>,
419 ) -> Result<CalcValue<'b>, ExcelError> {
420 let v = scalar_like_value(&args[0])?;
421 let num = match v {
422 LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
423 other => coerce_num(&other)?,
424 };
425
426 let decimals = if args.len() >= 2 {
427 match scalar_like_value(&args[1])? {
428 LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
429 other => coerce_num(&other)?.trunc() as i32,
430 }
431 } else {
432 2
433 };
434
435 let factor = 10f64.powi(decimals);
437 let rounded = (num * factor).round() / factor;
438
439 let abs_val = rounded.abs();
441 let decimals_usize = decimals.max(0) as usize;
442
443 let formatted = if decimals >= 0 {
444 format!("{:.prec$}", abs_val, prec = decimals_usize)
445 } else {
446 format!("{:.0}", abs_val)
447 };
448
449 let parts: Vec<&str> = formatted.split('.').collect();
451 let int_part = parts[0];
452 let dec_part = parts.get(1);
453
454 let int_with_commas: String = int_part
455 .chars()
456 .rev()
457 .enumerate()
458 .flat_map(|(i, c)| {
459 if i > 0 && i % 3 == 0 {
460 vec![',', c]
461 } else {
462 vec![c]
463 }
464 })
465 .collect::<Vec<_>>()
466 .into_iter()
467 .rev()
468 .collect();
469
470 let result = if let Some(dec) = dec_part {
471 if rounded < 0.0 {
472 format!("(${}.{})", int_with_commas, dec)
473 } else {
474 format!("${}.{}", int_with_commas, dec)
475 }
476 } else if rounded < 0.0 {
477 format!("(${})", int_with_commas)
478 } else {
479 format!("${}", int_with_commas)
480 };
481
482 Ok(CalcValue::Scalar(LiteralValue::Text(result)))
483 }
484}
485
486fn arg_fixed() -> Vec<ArgSchema> {
491 vec![
492 ArgSchema {
493 kinds: smallvec::smallvec![ArgKind::Number],
494 required: true,
495 by_ref: false,
496 shape: ShapeKind::Scalar,
497 coercion: CoercionPolicy::NumberLenientText,
498 max: None,
499 repeating: None,
500 default: None,
501 },
502 ArgSchema {
503 kinds: smallvec::smallvec![ArgKind::Number],
504 required: false,
505 by_ref: false,
506 shape: ShapeKind::Scalar,
507 coercion: CoercionPolicy::NumberLenientText,
508 max: None,
509 repeating: None,
510 default: Some(LiteralValue::Number(2.0)),
511 },
512 ArgSchema {
513 kinds: smallvec::smallvec![ArgKind::Logical],
514 required: false,
515 by_ref: false,
516 shape: ShapeKind::Scalar,
517 coercion: CoercionPolicy::Logical,
518 max: None,
519 repeating: None,
520 default: Some(LiteralValue::Boolean(false)),
521 },
522 ]
523}
524
525#[derive(Debug)]
526pub struct FixedFn;
527impl Function for FixedFn {
528 func_caps!(PURE);
529 fn name(&self) -> &'static str {
530 "FIXED"
531 }
532 fn min_args(&self) -> usize {
533 1
534 }
535 fn arg_schema(&self) -> &'static [ArgSchema] {
536 use once_cell::sync::Lazy;
537 static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(arg_fixed);
538 &SCHEMA
539 }
540 fn eval<'a, 'b, 'c>(
541 &self,
542 args: &'c [ArgumentHandle<'a, 'b>],
543 _: &dyn FunctionContext<'b>,
544 ) -> Result<CalcValue<'b>, ExcelError> {
545 let v = scalar_like_value(&args[0])?;
546 let num = match v {
547 LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
548 other => coerce_num(&other)?,
549 };
550
551 let decimals = if args.len() >= 2 {
552 match scalar_like_value(&args[1])? {
553 LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
554 other => coerce_num(&other)?.trunc() as i32,
555 }
556 } else {
557 2
558 };
559
560 let no_commas = if args.len() >= 3 {
561 match scalar_like_value(&args[2])? {
562 LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
563 LiteralValue::Boolean(b) => b,
564 other => coerce_num(&other)? != 0.0,
565 }
566 } else {
567 false
568 };
569
570 let factor = 10f64.powi(decimals);
572 let rounded = (num * factor).round() / factor;
573
574 let decimals_usize = decimals.max(0) as usize;
575
576 let formatted = if decimals >= 0 {
577 format!("{:.prec$}", rounded.abs(), prec = decimals_usize)
578 } else {
579 format!("{:.0}", rounded.abs())
580 };
581
582 let result = if no_commas {
583 if rounded < 0.0 {
584 format!("-{}", formatted)
585 } else {
586 formatted
587 }
588 } else {
589 let parts: Vec<&str> = formatted.split('.').collect();
591 let int_part = parts[0];
592 let dec_part = parts.get(1);
593
594 let int_with_commas: String = int_part
595 .chars()
596 .rev()
597 .enumerate()
598 .flat_map(|(i, c)| {
599 if i > 0 && i % 3 == 0 {
600 vec![',', c]
601 } else {
602 vec![c]
603 }
604 })
605 .collect::<Vec<_>>()
606 .into_iter()
607 .rev()
608 .collect();
609
610 if let Some(dec) = dec_part {
611 if rounded < 0.0 {
612 format!("-{}.{}", int_with_commas, dec)
613 } else {
614 format!("{}.{}", int_with_commas, dec)
615 }
616 } else if rounded < 0.0 {
617 format!("-{}", int_with_commas)
618 } else {
619 int_with_commas
620 }
621 };
622
623 Ok(CalcValue::Scalar(LiteralValue::Text(result)))
624 }
625}
626
627pub fn register_builtins() {
632 use crate::function_registry::register_function;
633 use std::sync::Arc;
634
635 register_function(Arc::new(CleanFn));
636 register_function(Arc::new(UnicharFn));
637 register_function(Arc::new(UnicodeFn));
638 register_function(Arc::new(TextBeforeFn));
639 register_function(Arc::new(TextAfterFn));
640 register_function(Arc::new(DollarFn));
641 register_function(Arc::new(FixedFn));
642}
643
644#[cfg(test)]
645mod tests {
646 use super::*;
647 use crate::test_workbook::TestWorkbook;
648 use crate::traits::ArgumentHandle;
649 use formualizer_parse::parser::{ASTNode, ASTNodeType};
650
651 fn interp(wb: &TestWorkbook) -> crate::interpreter::Interpreter<'_> {
652 wb.interpreter()
653 }
654
655 fn make_text_ast(s: &str) -> ASTNode {
656 ASTNode::new(
657 ASTNodeType::Literal(LiteralValue::Text(s.to_string())),
658 None,
659 )
660 }
661
662 fn make_num_ast(n: f64) -> ASTNode {
663 ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(n)), None)
664 }
665
666 #[test]
667 fn test_clean() {
668 let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CleanFn));
669 let ctx = interp(&wb);
670 let clean = ctx.context.get_function("", "CLEAN").unwrap();
671
672 let input = make_text_ast("Hello\x00\x01\x1FWorld");
673 let args = vec![ArgumentHandle::new(&input, &ctx)];
674 match clean
675 .dispatch(&args, &ctx.function_context(None))
676 .unwrap()
677 .into_literal()
678 {
679 LiteralValue::Text(s) => assert_eq!(s, "HelloWorld"),
680 v => panic!("unexpected {v:?}"),
681 }
682 }
683
684 #[test]
685 fn test_unichar_unicode() {
686 let wb = TestWorkbook::new()
687 .with_function(std::sync::Arc::new(UnicharFn))
688 .with_function(std::sync::Arc::new(UnicodeFn));
689 let ctx = interp(&wb);
690
691 let unichar = ctx.context.get_function("", "UNICHAR").unwrap();
693 let code = make_num_ast(65.0);
694 let args = vec![ArgumentHandle::new(&code, &ctx)];
695 match unichar
696 .dispatch(&args, &ctx.function_context(None))
697 .unwrap()
698 .into_literal()
699 {
700 LiteralValue::Text(s) => assert_eq!(s, "A"),
701 v => panic!("unexpected {v:?}"),
702 }
703
704 let unicode = ctx.context.get_function("", "UNICODE").unwrap();
706 let text = make_text_ast("A");
707 let args = vec![ArgumentHandle::new(&text, &ctx)];
708 match unicode
709 .dispatch(&args, &ctx.function_context(None))
710 .unwrap()
711 .into_literal()
712 {
713 LiteralValue::Number(n) => assert_eq!(n, 65.0),
714 v => panic!("unexpected {v:?}"),
715 }
716 }
717
718 #[test]
719 fn test_textbefore_textafter() {
720 let wb = TestWorkbook::new()
721 .with_function(std::sync::Arc::new(TextBeforeFn))
722 .with_function(std::sync::Arc::new(TextAfterFn));
723 let ctx = interp(&wb);
724
725 let textbefore = ctx.context.get_function("", "TEXTBEFORE").unwrap();
726 let text = make_text_ast("hello-world-test");
727 let delim = make_text_ast("-");
728 let args = vec![
729 ArgumentHandle::new(&text, &ctx),
730 ArgumentHandle::new(&delim, &ctx),
731 ];
732 match textbefore
733 .dispatch(&args, &ctx.function_context(None))
734 .unwrap()
735 .into_literal()
736 {
737 LiteralValue::Text(s) => assert_eq!(s, "hello"),
738 v => panic!("unexpected {v:?}"),
739 }
740
741 let textafter = ctx.context.get_function("", "TEXTAFTER").unwrap();
742 match textafter
743 .dispatch(&args, &ctx.function_context(None))
744 .unwrap()
745 .into_literal()
746 {
747 LiteralValue::Text(s) => assert_eq!(s, "world-test"),
748 v => panic!("unexpected {v:?}"),
749 }
750 }
751}