formualizer_eval/builtins/text/
extended.rs

1//! Extended text functions: CLEAN, UNICHAR, UNICODE, TEXTBEFORE, TEXTAFTER, TEXTSPLIT, DOLLAR, FIXED
2
3use 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        CalcValue::Callable(_) => LiteralValue::Error(
15            ExcelError::new(ExcelErrorKind::Calc).with_message("LAMBDA value must be invoked"),
16        ),
17    })
18}
19
20/// Coerce a LiteralValue to text
21fn coerce_text(v: &LiteralValue) -> String {
22    match v {
23        LiteralValue::Text(s) => s.clone(),
24        LiteralValue::Empty => String::new(),
25        LiteralValue::Boolean(b) => if *b { "TRUE" } else { "FALSE" }.to_string(),
26        LiteralValue::Int(i) => i.to_string(),
27        LiteralValue::Number(f) => {
28            let s = f.to_string();
29            if s.ends_with(".0") {
30                s[..s.len() - 2].to_string()
31            } else {
32                s
33            }
34        }
35        other => other.to_string(),
36    }
37}
38
39// ============================================================================
40// CLEAN - Remove non-printable characters (ASCII 0-31)
41// ============================================================================
42
43#[derive(Debug)]
44pub struct CleanFn;
45/// Removes non-printable ASCII control characters from text.
46///
47/// # Remarks
48/// - Characters with codes `0..31` are removed.
49/// - Printable whitespace like regular spaces is preserved.
50/// - Non-text inputs are coerced to text before cleaning.
51/// - Errors are propagated unchanged.
52///
53/// # Examples
54///
55/// ```yaml,sandbox
56/// title: "Strip control characters"
57/// formula: '=CLEAN("A"&CHAR(10)&"B")'
58/// expected: "AB"
59/// ```
60///
61/// ```yaml,sandbox
62/// title: "Printable spaces remain"
63/// formula: '=CLEAN("A B")'
64/// expected: "A B"
65/// ```
66///
67/// ```yaml,docs
68/// related:
69///   - TRIM
70///   - CHAR
71///   - SUBSTITUTE
72/// faq:
73///   - q: "Does CLEAN remove normal spaces or only control characters?"
74///     a: "It removes only ASCII control characters (0-31); regular printable spaces remain."
75/// ```
76/// [formualizer-docgen:schema:start]
77/// Name: CLEAN
78/// Type: CleanFn
79/// Min args: 1
80/// Max args: 1
81/// Variadic: false
82/// Signature: CLEAN(arg1: any@scalar)
83/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
84/// Caps: PURE
85/// [formualizer-docgen:schema:end]
86impl Function for CleanFn {
87    func_caps!(PURE);
88    fn name(&self) -> &'static str {
89        "CLEAN"
90    }
91    fn min_args(&self) -> usize {
92        1
93    }
94    fn arg_schema(&self) -> &'static [ArgSchema] {
95        &ARG_ANY_ONE[..]
96    }
97    fn eval<'a, 'b, 'c>(
98        &self,
99        args: &'c [ArgumentHandle<'a, 'b>],
100        _: &dyn FunctionContext<'b>,
101    ) -> Result<CalcValue<'b>, ExcelError> {
102        let v = scalar_like_value(&args[0])?;
103        let text = match v {
104            LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
105            other => coerce_text(&other),
106        };
107
108        // Remove non-printable characters (ASCII 0-31)
109        let cleaned: String = text.chars().filter(|&c| c as u32 >= 32).collect();
110        Ok(CalcValue::Scalar(LiteralValue::Text(cleaned)))
111    }
112}
113
114// ============================================================================
115// UNICHAR - Return Unicode character from code point
116// ============================================================================
117
118#[derive(Debug)]
119pub struct UnicharFn;
120/// Returns the Unicode character for a given code point.
121///
122/// # Remarks
123/// - Input is truncated to an integer code point.
124/// - Code point `0`, surrogate range, and values above `0x10FFFF` return `#VALUE!`.
125/// - Errors are propagated unchanged.
126/// - Non-numeric inputs are coerced with numeric coercion rules.
127///
128/// # Examples
129///
130/// ```yaml,sandbox
131/// title: "Basic Unicode code point"
132/// formula: '=UNICHAR(9731)'
133/// expected: "☃"
134/// ```
135///
136/// ```yaml,sandbox
137/// title: "Invalid code point"
138/// formula: '=UNICHAR(0)'
139/// expected: "#VALUE!"
140/// ```
141///
142/// ```yaml,docs
143/// related:
144///   - UNICODE
145///   - CHAR
146///   - CODE
147/// faq:
148///   - q: "Which code points are invalid?"
149///     a: "0, surrogate values, and anything above 0x10FFFF return #VALUE!."
150/// ```
151/// [formualizer-docgen:schema:start]
152/// Name: UNICHAR
153/// Type: UnicharFn
154/// Min args: 1
155/// Max args: 1
156/// Variadic: false
157/// Signature: UNICHAR(arg1: any@scalar)
158/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
159/// Caps: PURE
160/// [formualizer-docgen:schema:end]
161impl Function for UnicharFn {
162    func_caps!(PURE);
163    fn name(&self) -> &'static str {
164        "UNICHAR"
165    }
166    fn min_args(&self) -> usize {
167        1
168    }
169    fn arg_schema(&self) -> &'static [ArgSchema] {
170        &ARG_ANY_ONE[..]
171    }
172    fn eval<'a, 'b, 'c>(
173        &self,
174        args: &'c [ArgumentHandle<'a, 'b>],
175        _: &dyn FunctionContext<'b>,
176    ) -> Result<CalcValue<'b>, ExcelError> {
177        let v = scalar_like_value(&args[0])?;
178        let n = match v {
179            LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
180            other => coerce_num(&other)?,
181        };
182
183        let code = n.trunc() as u32;
184
185        // Valid Unicode range (excluding surrogates)
186        if code == 0 || (0xD800..=0xDFFF).contains(&code) || code > 0x10FFFF {
187            return Ok(CalcValue::Scalar(LiteralValue::Error(
188                ExcelError::new_value(),
189            )));
190        }
191
192        match char::from_u32(code) {
193            Some(c) => Ok(CalcValue::Scalar(LiteralValue::Text(c.to_string()))),
194            None => Ok(CalcValue::Scalar(LiteralValue::Error(
195                ExcelError::new_value(),
196            ))),
197        }
198    }
199}
200
201// ============================================================================
202// UNICODE - Return Unicode code point of first character
203// ============================================================================
204
205#[derive(Debug)]
206pub struct UnicodeFn;
207/// Returns the Unicode code point of the first character in text.
208///
209/// # Remarks
210/// - Only the first character is evaluated.
211/// - Empty text returns `#VALUE!`.
212/// - Non-text inputs are coerced to text before inspection.
213/// - Errors are propagated unchanged.
214///
215/// # Examples
216///
217/// ```yaml,sandbox
218/// title: "Code point for letter A"
219/// formula: '=UNICODE("A")'
220/// expected: 65
221/// ```
222///
223/// ```yaml,sandbox
224/// title: "Code point for emoji"
225/// formula: '=UNICODE("😀")'
226/// expected: 128512
227/// ```
228///
229/// ```yaml,docs
230/// related:
231///   - UNICHAR
232///   - CODE
233///   - CHAR
234/// faq:
235///   - q: "If text has multiple characters, which one is used?"
236///     a: "UNICODE inspects only the first character and ignores the rest."
237/// ```
238/// [formualizer-docgen:schema:start]
239/// Name: UNICODE
240/// Type: UnicodeFn
241/// Min args: 1
242/// Max args: 1
243/// Variadic: false
244/// Signature: UNICODE(arg1: any@scalar)
245/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
246/// Caps: PURE
247/// [formualizer-docgen:schema:end]
248impl Function for UnicodeFn {
249    func_caps!(PURE);
250    fn name(&self) -> &'static str {
251        "UNICODE"
252    }
253    fn min_args(&self) -> usize {
254        1
255    }
256    fn arg_schema(&self) -> &'static [ArgSchema] {
257        &ARG_ANY_ONE[..]
258    }
259    fn eval<'a, 'b, 'c>(
260        &self,
261        args: &'c [ArgumentHandle<'a, 'b>],
262        _: &dyn FunctionContext<'b>,
263    ) -> Result<CalcValue<'b>, ExcelError> {
264        let v = scalar_like_value(&args[0])?;
265        let text = match v {
266            LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
267            other => coerce_text(&other),
268        };
269
270        if text.is_empty() {
271            return Ok(CalcValue::Scalar(LiteralValue::Error(
272                ExcelError::new_value(),
273            )));
274        }
275
276        let code = text.chars().next().unwrap() as u32;
277        Ok(CalcValue::Scalar(LiteralValue::Number(code as f64)))
278    }
279}
280
281// ============================================================================
282// TEXTBEFORE - Return text before a delimiter
283// ============================================================================
284
285fn arg_textbefore() -> Vec<ArgSchema> {
286    vec![
287        ArgSchema {
288            kinds: smallvec::smallvec![ArgKind::Any],
289            required: true,
290            by_ref: false,
291            shape: ShapeKind::Scalar,
292            coercion: CoercionPolicy::None,
293            max: None,
294            repeating: None,
295            default: None,
296        },
297        ArgSchema {
298            kinds: smallvec::smallvec![ArgKind::Any],
299            required: true,
300            by_ref: false,
301            shape: ShapeKind::Scalar,
302            coercion: CoercionPolicy::None,
303            max: None,
304            repeating: None,
305            default: None,
306        },
307        ArgSchema {
308            kinds: smallvec::smallvec![ArgKind::Number],
309            required: false,
310            by_ref: false,
311            shape: ShapeKind::Scalar,
312            coercion: CoercionPolicy::NumberLenientText,
313            max: None,
314            repeating: None,
315            default: Some(LiteralValue::Number(1.0)),
316        },
317    ]
318}
319
320#[derive(Debug)]
321pub struct TextBeforeFn;
322/// Returns text that appears before a delimiter.
323///
324/// # Remarks
325/// - Delimiter matching is case-sensitive.
326/// - `instance_num` defaults to `1`; negative instances search from the end.
327/// - `instance_num=0` or empty delimiter returns `#VALUE!`.
328/// - If requested delimiter occurrence is not found, returns `#N/A`.
329///
330/// # Examples
331///
332/// ```yaml,sandbox
333/// title: "Text before first delimiter"
334/// formula: '=TEXTBEFORE("a-b-c", "-")'
335/// expected: "a"
336/// ```
337///
338/// ```yaml,sandbox
339/// title: "Text before last delimiter"
340/// formula: '=TEXTBEFORE("a-b-c", "-", -1)'
341/// expected: "a-b"
342/// ```
343///
344/// ```yaml,docs
345/// related:
346///   - TEXTAFTER
347///   - FIND
348///   - SEARCH
349/// faq:
350///   - q: "What happens when the delimiter is missing?"
351///     a: "TEXTBEFORE returns #N/A when the requested delimiter occurrence is not found."
352/// ```
353/// [formualizer-docgen:schema:start]
354/// Name: TEXTBEFORE
355/// Type: TextBeforeFn
356/// Min args: 2
357/// Max args: 3
358/// Variadic: false
359/// Signature: TEXTBEFORE(arg1: any@scalar, arg2: any@scalar, arg3?: number@scalar)
360/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg2{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg3{kinds=number,required=false,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=true}
361/// Caps: PURE
362/// [formualizer-docgen:schema:end]
363impl Function for TextBeforeFn {
364    func_caps!(PURE);
365    fn name(&self) -> &'static str {
366        "TEXTBEFORE"
367    }
368    fn min_args(&self) -> usize {
369        2
370    }
371    fn arg_schema(&self) -> &'static [ArgSchema] {
372        use once_cell::sync::Lazy;
373        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(arg_textbefore);
374        &SCHEMA
375    }
376    fn eval<'a, 'b, 'c>(
377        &self,
378        args: &'c [ArgumentHandle<'a, 'b>],
379        _: &dyn FunctionContext<'b>,
380    ) -> Result<CalcValue<'b>, ExcelError> {
381        let v1 = scalar_like_value(&args[0])?;
382        let text = match v1 {
383            LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
384            other => coerce_text(&other),
385        };
386
387        let v2 = scalar_like_value(&args[1])?;
388        let delimiter = match v2 {
389            LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
390            other => coerce_text(&other),
391        };
392
393        let instance = if args.len() >= 3 {
394            match scalar_like_value(&args[2])? {
395                LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
396                other => coerce_num(&other)?.trunc() as i32,
397            }
398        } else {
399            1
400        };
401
402        if delimiter.is_empty() {
403            return Ok(CalcValue::Scalar(LiteralValue::Error(
404                ExcelError::new_value(),
405            )));
406        }
407
408        if instance == 0 {
409            return Ok(CalcValue::Scalar(LiteralValue::Error(
410                ExcelError::new_value(),
411            )));
412        }
413
414        let result = if instance > 0 {
415            // Find nth occurrence from start
416            let mut pos = 0;
417            let mut found_count = 0;
418            for (idx, _) in text.match_indices(&delimiter) {
419                found_count += 1;
420                if found_count == instance {
421                    pos = idx;
422                    break;
423                }
424            }
425            if found_count < instance {
426                return Ok(CalcValue::Scalar(LiteralValue::Error(ExcelError::new(
427                    ExcelErrorKind::Na,
428                ))));
429            }
430            text[..pos].to_string()
431        } else {
432            // Find nth occurrence from end
433            let matches: Vec<_> = text.match_indices(&delimiter).collect();
434            let idx = matches.len() as i32 + instance; // instance is negative
435            if idx < 0 || idx as usize >= matches.len() {
436                return Ok(CalcValue::Scalar(LiteralValue::Error(ExcelError::new(
437                    ExcelErrorKind::Na,
438                ))));
439            }
440            text[..matches[idx as usize].0].to_string()
441        };
442
443        Ok(CalcValue::Scalar(LiteralValue::Text(result)))
444    }
445}
446
447// ============================================================================
448// TEXTAFTER - Return text after a delimiter
449// ============================================================================
450
451#[derive(Debug)]
452pub struct TextAfterFn;
453/// Returns text that appears after a delimiter.
454///
455/// # Remarks
456/// - Delimiter matching is case-sensitive.
457/// - `instance_num` defaults to `1`; negative instances search from the end.
458/// - `instance_num=0` or empty delimiter returns `#VALUE!`.
459/// - If requested delimiter occurrence is not found, returns `#N/A`.
460///
461/// # Examples
462///
463/// ```yaml,sandbox
464/// title: "Text after first delimiter"
465/// formula: '=TEXTAFTER("a-b-c", "-")'
466/// expected: "b-c"
467/// ```
468///
469/// ```yaml,sandbox
470/// title: "Text after last delimiter"
471/// formula: '=TEXTAFTER("a-b-c", "-", -1)'
472/// expected: "c"
473/// ```
474///
475/// ```yaml,docs
476/// related:
477///   - TEXTBEFORE
478///   - FIND
479///   - SEARCH
480/// faq:
481///   - q: "Is matching case-sensitive?"
482///     a: "Yes. TEXTAFTER performs case-sensitive delimiter matching in this implementation."
483/// ```
484/// [formualizer-docgen:schema:start]
485/// Name: TEXTAFTER
486/// Type: TextAfterFn
487/// Min args: 2
488/// Max args: 3
489/// Variadic: false
490/// Signature: TEXTAFTER(arg1: any@scalar, arg2: any@scalar, arg3?: number@scalar)
491/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg2{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg3{kinds=number,required=false,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=true}
492/// Caps: PURE
493/// [formualizer-docgen:schema:end]
494impl Function for TextAfterFn {
495    func_caps!(PURE);
496    fn name(&self) -> &'static str {
497        "TEXTAFTER"
498    }
499    fn min_args(&self) -> usize {
500        2
501    }
502    fn arg_schema(&self) -> &'static [ArgSchema] {
503        use once_cell::sync::Lazy;
504        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(arg_textbefore);
505        &SCHEMA
506    }
507    fn eval<'a, 'b, 'c>(
508        &self,
509        args: &'c [ArgumentHandle<'a, 'b>],
510        _: &dyn FunctionContext<'b>,
511    ) -> Result<CalcValue<'b>, ExcelError> {
512        let v1 = scalar_like_value(&args[0])?;
513        let text = match v1 {
514            LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
515            other => coerce_text(&other),
516        };
517
518        let v2 = scalar_like_value(&args[1])?;
519        let delimiter = match v2 {
520            LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
521            other => coerce_text(&other),
522        };
523
524        let instance = if args.len() >= 3 {
525            match scalar_like_value(&args[2])? {
526                LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
527                other => coerce_num(&other)?.trunc() as i32,
528            }
529        } else {
530            1
531        };
532
533        if delimiter.is_empty() {
534            return Ok(CalcValue::Scalar(LiteralValue::Error(
535                ExcelError::new_value(),
536            )));
537        }
538
539        if instance == 0 {
540            return Ok(CalcValue::Scalar(LiteralValue::Error(
541                ExcelError::new_value(),
542            )));
543        }
544
545        let result = if instance > 0 {
546            // Find nth occurrence from start
547            let mut end_pos = 0;
548            let mut found_count = 0;
549            for (idx, matched) in text.match_indices(&delimiter) {
550                found_count += 1;
551                if found_count == instance {
552                    end_pos = idx + matched.len();
553                    break;
554                }
555            }
556            if found_count < instance {
557                return Ok(CalcValue::Scalar(LiteralValue::Error(ExcelError::new(
558                    ExcelErrorKind::Na,
559                ))));
560            }
561            text[end_pos..].to_string()
562        } else {
563            // Find nth occurrence from end
564            let matches: Vec<_> = text.match_indices(&delimiter).collect();
565            let idx = matches.len() as i32 + instance;
566            if idx < 0 || idx as usize >= matches.len() {
567                return Ok(CalcValue::Scalar(LiteralValue::Error(ExcelError::new(
568                    ExcelErrorKind::Na,
569                ))));
570            }
571            let (pos, matched) = matches[idx as usize];
572            text[pos + matched.len()..].to_string()
573        };
574
575        Ok(CalcValue::Scalar(LiteralValue::Text(result)))
576    }
577}
578
579// ============================================================================
580// DOLLAR - Format number as currency
581// ============================================================================
582
583fn arg_dollar() -> Vec<ArgSchema> {
584    vec![
585        ArgSchema {
586            kinds: smallvec::smallvec![ArgKind::Number],
587            required: true,
588            by_ref: false,
589            shape: ShapeKind::Scalar,
590            coercion: CoercionPolicy::NumberLenientText,
591            max: None,
592            repeating: None,
593            default: None,
594        },
595        ArgSchema {
596            kinds: smallvec::smallvec![ArgKind::Number],
597            required: false,
598            by_ref: false,
599            shape: ShapeKind::Scalar,
600            coercion: CoercionPolicy::NumberLenientText,
601            max: None,
602            repeating: None,
603            default: Some(LiteralValue::Number(2.0)),
604        },
605    ]
606}
607
608#[derive(Debug)]
609pub struct DollarFn;
610/// Formats a number as currency text.
611///
612/// # Remarks
613/// - Default decimal places is `2` when omitted.
614/// - Negative values are rendered in parentheses, such as `($1,234.00)`.
615/// - Uses comma group separators and dollar symbol.
616/// - Input coercion failures or propagated errors return an error.
617///
618/// # Examples
619///
620/// ```yaml,sandbox
621/// title: "Default currency formatting"
622/// formula: '=DOLLAR(1234.5)'
623/// expected: "$1,234.50"
624/// ```
625///
626/// ```yaml,sandbox
627/// title: "Negative value with zero decimals"
628/// formula: '=DOLLAR(-999.4, 0)'
629/// expected: "($999)"
630/// ```
631///
632/// ```yaml,docs
633/// related:
634///   - FIXED
635///   - TEXT
636///   - VALUE
637/// faq:
638///   - q: "How are negative numbers displayed?"
639///     a: "Negative results are formatted in parentheses, for example ($1,234.00)."
640/// ```
641/// [formualizer-docgen:schema:start]
642/// Name: DOLLAR
643/// Type: DollarFn
644/// Min args: 1
645/// Max args: 2
646/// Variadic: false
647/// Signature: DOLLAR(arg1: number@scalar, arg2?: number@scalar)
648/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg2{kinds=number,required=false,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=true}
649/// Caps: PURE
650/// [formualizer-docgen:schema:end]
651impl Function for DollarFn {
652    func_caps!(PURE);
653    fn name(&self) -> &'static str {
654        "DOLLAR"
655    }
656    fn min_args(&self) -> usize {
657        1
658    }
659    fn arg_schema(&self) -> &'static [ArgSchema] {
660        use once_cell::sync::Lazy;
661        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(arg_dollar);
662        &SCHEMA
663    }
664    fn eval<'a, 'b, 'c>(
665        &self,
666        args: &'c [ArgumentHandle<'a, 'b>],
667        _: &dyn FunctionContext<'b>,
668    ) -> Result<CalcValue<'b>, ExcelError> {
669        let v = scalar_like_value(&args[0])?;
670        let num = match v {
671            LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
672            other => coerce_num(&other)?,
673        };
674
675        let decimals = if args.len() >= 2 {
676            match scalar_like_value(&args[1])? {
677                LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
678                other => coerce_num(&other)?.trunc() as i32,
679            }
680        } else {
681            2
682        };
683
684        // Round to specified decimals
685        let factor = 10f64.powi(decimals);
686        let rounded = (num * factor).round() / factor;
687
688        // Format with thousands separator and currency symbol
689        let abs_val = rounded.abs();
690        let decimals_usize = decimals.max(0) as usize;
691
692        let formatted = if decimals >= 0 {
693            format!("{:.prec$}", abs_val, prec = decimals_usize)
694        } else {
695            format!("{:.0}", abs_val)
696        };
697
698        // Add thousands separators
699        let parts: Vec<&str> = formatted.split('.').collect();
700        let int_part = parts[0];
701        let dec_part = parts.get(1);
702
703        let int_with_commas: String = int_part
704            .chars()
705            .rev()
706            .enumerate()
707            .flat_map(|(i, c)| {
708                if i > 0 && i % 3 == 0 {
709                    vec![',', c]
710                } else {
711                    vec![c]
712                }
713            })
714            .collect::<Vec<_>>()
715            .into_iter()
716            .rev()
717            .collect();
718
719        let result = if let Some(dec) = dec_part {
720            if rounded < 0.0 {
721                format!("(${}.{})", int_with_commas, dec)
722            } else {
723                format!("${}.{}", int_with_commas, dec)
724            }
725        } else if rounded < 0.0 {
726            format!("(${})", int_with_commas)
727        } else {
728            format!("${}", int_with_commas)
729        };
730
731        Ok(CalcValue::Scalar(LiteralValue::Text(result)))
732    }
733}
734
735// ============================================================================
736// FIXED - Format number with fixed decimals
737// ============================================================================
738
739fn arg_fixed() -> Vec<ArgSchema> {
740    vec![
741        ArgSchema {
742            kinds: smallvec::smallvec![ArgKind::Number],
743            required: true,
744            by_ref: false,
745            shape: ShapeKind::Scalar,
746            coercion: CoercionPolicy::NumberLenientText,
747            max: None,
748            repeating: None,
749            default: None,
750        },
751        ArgSchema {
752            kinds: smallvec::smallvec![ArgKind::Number],
753            required: false,
754            by_ref: false,
755            shape: ShapeKind::Scalar,
756            coercion: CoercionPolicy::NumberLenientText,
757            max: None,
758            repeating: None,
759            default: Some(LiteralValue::Number(2.0)),
760        },
761        ArgSchema {
762            kinds: smallvec::smallvec![ArgKind::Logical],
763            required: false,
764            by_ref: false,
765            shape: ShapeKind::Scalar,
766            coercion: CoercionPolicy::Logical,
767            max: None,
768            repeating: None,
769            default: Some(LiteralValue::Boolean(false)),
770        },
771    ]
772}
773
774#[derive(Debug)]
775pub struct FixedFn;
776/// Formats a number as text with fixed decimal places.
777///
778/// # Remarks
779/// - Default decimal places is `2` when omitted.
780/// - Third argument controls comma grouping (`TRUE` disables commas).
781/// - Values are rounded to the requested decimal precision.
782/// - Numeric coercion failures return `#VALUE!`.
783///
784/// # Examples
785///
786/// ```yaml,sandbox
787/// title: "Fixed with commas"
788/// formula: '=FIXED(12345.678, 1, FALSE)'
789/// expected: "12,345.7"
790/// ```
791///
792/// ```yaml,sandbox
793/// title: "Fixed without commas"
794/// formula: '=FIXED(12345.678, 1, TRUE)'
795/// expected: "12345.7"
796/// ```
797///
798/// ```yaml,docs
799/// related:
800///   - DOLLAR
801///   - TEXT
802///   - VALUE
803/// faq:
804///   - q: "What does the third argument control?"
805///     a: "Set it to TRUE to suppress thousands separators; FALSE keeps comma grouping."
806/// ```
807/// [formualizer-docgen:schema:start]
808/// Name: FIXED
809/// Type: FixedFn
810/// Min args: 1
811/// Max args: 3
812/// Variadic: false
813/// Signature: FIXED(arg1: number@scalar, arg2?: number@scalar, arg3?: logical@scalar)
814/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg2{kinds=number,required=false,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=true}; arg3{kinds=logical,required=false,shape=scalar,by_ref=false,coercion=Logical,max=None,repeating=None,default=true}
815/// Caps: PURE
816/// [formualizer-docgen:schema:end]
817impl Function for FixedFn {
818    func_caps!(PURE);
819    fn name(&self) -> &'static str {
820        "FIXED"
821    }
822    fn min_args(&self) -> usize {
823        1
824    }
825    fn arg_schema(&self) -> &'static [ArgSchema] {
826        use once_cell::sync::Lazy;
827        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(arg_fixed);
828        &SCHEMA
829    }
830    fn eval<'a, 'b, 'c>(
831        &self,
832        args: &'c [ArgumentHandle<'a, 'b>],
833        _: &dyn FunctionContext<'b>,
834    ) -> Result<CalcValue<'b>, ExcelError> {
835        let v = scalar_like_value(&args[0])?;
836        let num = match v {
837            LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
838            other => coerce_num(&other)?,
839        };
840
841        let decimals = if args.len() >= 2 {
842            match scalar_like_value(&args[1])? {
843                LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
844                other => coerce_num(&other)?.trunc() as i32,
845            }
846        } else {
847            2
848        };
849
850        let no_commas = if args.len() >= 3 {
851            match scalar_like_value(&args[2])? {
852                LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
853                LiteralValue::Boolean(b) => b,
854                other => coerce_num(&other)? != 0.0,
855            }
856        } else {
857            false
858        };
859
860        // Round to specified decimals
861        let factor = 10f64.powi(decimals);
862        let rounded = (num * factor).round() / factor;
863
864        let decimals_usize = decimals.max(0) as usize;
865
866        let formatted = if decimals >= 0 {
867            format!("{:.prec$}", rounded.abs(), prec = decimals_usize)
868        } else {
869            format!("{:.0}", rounded.abs())
870        };
871
872        let result = if no_commas {
873            if rounded < 0.0 {
874                format!("-{}", formatted)
875            } else {
876                formatted
877            }
878        } else {
879            // Add thousands separators
880            let parts: Vec<&str> = formatted.split('.').collect();
881            let int_part = parts[0];
882            let dec_part = parts.get(1);
883
884            let int_with_commas: String = int_part
885                .chars()
886                .rev()
887                .enumerate()
888                .flat_map(|(i, c)| {
889                    if i > 0 && i % 3 == 0 {
890                        vec![',', c]
891                    } else {
892                        vec![c]
893                    }
894                })
895                .collect::<Vec<_>>()
896                .into_iter()
897                .rev()
898                .collect();
899
900            if let Some(dec) = dec_part {
901                if rounded < 0.0 {
902                    format!("-{}.{}", int_with_commas, dec)
903                } else {
904                    format!("{}.{}", int_with_commas, dec)
905                }
906            } else if rounded < 0.0 {
907                format!("-{}", int_with_commas)
908            } else {
909                int_with_commas
910            }
911        };
912
913        Ok(CalcValue::Scalar(LiteralValue::Text(result)))
914    }
915}
916
917// ============================================================================
918// Registration
919// ============================================================================
920
921pub fn register_builtins() {
922    use crate::function_registry::register_function;
923    use std::sync::Arc;
924
925    register_function(Arc::new(CleanFn));
926    register_function(Arc::new(UnicharFn));
927    register_function(Arc::new(UnicodeFn));
928    register_function(Arc::new(TextBeforeFn));
929    register_function(Arc::new(TextAfterFn));
930    register_function(Arc::new(DollarFn));
931    register_function(Arc::new(FixedFn));
932}
933
934#[cfg(test)]
935mod tests {
936    use super::*;
937    use crate::test_workbook::TestWorkbook;
938    use crate::traits::ArgumentHandle;
939    use formualizer_parse::parser::{ASTNode, ASTNodeType};
940
941    fn interp(wb: &TestWorkbook) -> crate::interpreter::Interpreter<'_> {
942        wb.interpreter()
943    }
944
945    fn make_text_ast(s: &str) -> ASTNode {
946        ASTNode::new(
947            ASTNodeType::Literal(LiteralValue::Text(s.to_string())),
948            None,
949        )
950    }
951
952    fn make_num_ast(n: f64) -> ASTNode {
953        ASTNode::new(ASTNodeType::Literal(LiteralValue::Number(n)), None)
954    }
955
956    #[test]
957    fn test_clean() {
958        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(CleanFn));
959        let ctx = interp(&wb);
960        let clean = ctx.context.get_function("", "CLEAN").unwrap();
961
962        let input = make_text_ast("Hello\x00\x01\x1FWorld");
963        let args = vec![ArgumentHandle::new(&input, &ctx)];
964        match clean
965            .dispatch(&args, &ctx.function_context(None))
966            .unwrap()
967            .into_literal()
968        {
969            LiteralValue::Text(s) => assert_eq!(s, "HelloWorld"),
970            v => panic!("unexpected {v:?}"),
971        }
972    }
973
974    #[test]
975    fn test_unichar_unicode() {
976        let wb = TestWorkbook::new()
977            .with_function(std::sync::Arc::new(UnicharFn))
978            .with_function(std::sync::Arc::new(UnicodeFn));
979        let ctx = interp(&wb);
980
981        // UNICHAR
982        let unichar = ctx.context.get_function("", "UNICHAR").unwrap();
983        let code = make_num_ast(65.0);
984        let args = vec![ArgumentHandle::new(&code, &ctx)];
985        match unichar
986            .dispatch(&args, &ctx.function_context(None))
987            .unwrap()
988            .into_literal()
989        {
990            LiteralValue::Text(s) => assert_eq!(s, "A"),
991            v => panic!("unexpected {v:?}"),
992        }
993
994        // UNICODE
995        let unicode = ctx.context.get_function("", "UNICODE").unwrap();
996        let text = make_text_ast("A");
997        let args = vec![ArgumentHandle::new(&text, &ctx)];
998        match unicode
999            .dispatch(&args, &ctx.function_context(None))
1000            .unwrap()
1001            .into_literal()
1002        {
1003            LiteralValue::Number(n) => assert_eq!(n, 65.0),
1004            v => panic!("unexpected {v:?}"),
1005        }
1006    }
1007
1008    #[test]
1009    fn test_textbefore_textafter() {
1010        let wb = TestWorkbook::new()
1011            .with_function(std::sync::Arc::new(TextBeforeFn))
1012            .with_function(std::sync::Arc::new(TextAfterFn));
1013        let ctx = interp(&wb);
1014
1015        let textbefore = ctx.context.get_function("", "TEXTBEFORE").unwrap();
1016        let text = make_text_ast("hello-world-test");
1017        let delim = make_text_ast("-");
1018        let args = vec![
1019            ArgumentHandle::new(&text, &ctx),
1020            ArgumentHandle::new(&delim, &ctx),
1021        ];
1022        match textbefore
1023            .dispatch(&args, &ctx.function_context(None))
1024            .unwrap()
1025            .into_literal()
1026        {
1027            LiteralValue::Text(s) => assert_eq!(s, "hello"),
1028            v => panic!("unexpected {v:?}"),
1029        }
1030
1031        let textafter = ctx.context.get_function("", "TEXTAFTER").unwrap();
1032        match textafter
1033            .dispatch(&args, &ctx.function_context(None))
1034            .unwrap()
1035            .into_literal()
1036        {
1037            LiteralValue::Text(s) => assert_eq!(s, "world-test"),
1038            v => panic!("unexpected {v:?}"),
1039        }
1040    }
1041}
formualizer_eval/builtins/text/extended.rs

formualizer_eval/builtins/text/
extended.rs
