formualizer_eval/builtins/text/
value_text.rs

1use super::super::utils::ARG_ANY_ONE;
2use crate::args::ArgSchema;
3use crate::function::Function;
4use crate::traits::{ArgumentHandle, FunctionContext};
5use formualizer_common::{ExcelError, ExcelErrorKind, LiteralValue};
6use formualizer_macros::func_caps;
7
8fn scalar_like_value(arg: &ArgumentHandle<'_, '_>) -> Result<LiteralValue, ExcelError> {
9    Ok(match arg.value()? {
10        crate::traits::CalcValue::Scalar(v) => v,
11        crate::traits::CalcValue::Range(rv) => rv.get_cell(0, 0),
12        crate::traits::CalcValue::Callable(_) => LiteralValue::Error(
13            ExcelError::new(ExcelErrorKind::Calc).with_message("LAMBDA value must be invoked"),
14        ),
15    })
16}
17
18fn to_text<'a, 'b>(a: &ArgumentHandle<'a, 'b>) -> Result<String, ExcelError> {
19    let v = scalar_like_value(a)?;
20    Ok(match v {
21        LiteralValue::Text(s) => s,
22        LiteralValue::Empty => String::new(),
23        LiteralValue::Boolean(b) => {
24            if b {
25                "TRUE".into()
26            } else {
27                "FALSE".into()
28            }
29        }
30        LiteralValue::Int(i) => i.to_string(),
31        LiteralValue::Number(f) => f.to_string(),
32        LiteralValue::Error(e) => return Err(e),
33        other => other.to_string(),
34    })
35}
36
37// VALUE(text) - parse number
38#[derive(Debug)]
39pub struct ValueFn;
40/// Converts text that represents a number into a numeric value.
41///
42/// # Remarks
43/// - Parsing uses locale-aware invariant number parsing from the function context.
44/// - Non-numeric text returns `#VALUE!`.
45/// - Booleans and numbers are first coerced to text, then parsed.
46/// - Errors are propagated unchanged.
47///
48/// # Examples
49///
50/// ```yaml,sandbox
51/// title: "Parse decimal text"
52/// formula: '=VALUE("12.5")'
53/// expected: 12.5
54/// ```
55///
56/// ```yaml,sandbox
57/// title: "Invalid numeric text"
58/// formula: '=VALUE("abc")'
59/// expected: "#VALUE!"
60/// ```
61///
62/// ```yaml,docs
63/// related:
64///   - TEXT
65///   - N
66///   - ISNUMBER
67/// faq:
68///   - q: "Does VALUE coerce arbitrary text like TRUE/FALSE?"
69///     a: "VALUE parses numeric text only; non-numeric strings return #VALUE!."
70/// ```
71/// [formualizer-docgen:schema:start]
72/// Name: VALUE
73/// Type: ValueFn
74/// Min args: 1
75/// Max args: 1
76/// Variadic: false
77/// Signature: VALUE(arg1: any@scalar)
78/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
79/// Caps: PURE
80/// [formualizer-docgen:schema:end]
81impl Function for ValueFn {
82    func_caps!(PURE);
83    fn name(&self) -> &'static str {
84        "VALUE"
85    }
86    fn min_args(&self) -> usize {
87        1
88    }
89    fn arg_schema(&self) -> &'static [ArgSchema] {
90        &ARG_ANY_ONE[..]
91    }
92    fn eval<'a, 'b, 'c>(
93        &self,
94        args: &'c [ArgumentHandle<'a, 'b>],
95        ctx: &dyn FunctionContext<'b>,
96    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
97        let s = to_text(&args[0])?;
98        let Some(n) = ctx.locale().parse_number_invariant(&s) else {
99            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
100                ExcelError::new_value(),
101            )));
102        };
103        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(n)))
104    }
105}
106
107/// Converts locale-delimited text to a number.
108///
109/// Parses text using explicit decimal and group separators, independent of the
110/// workbook's invariant locale.
111///
112/// # Remarks
113/// - The decimal separator defaults to `.`.
114/// - The group separator defaults to `,`.
115/// - Percent suffixes are supported and scale the result by 100 per suffix.
116///
117/// ```yaml,sandbox
118/// title: "Parse with explicit separators"
119/// formula: '=NUMBERVALUE("1.234,56",",",".")'
120/// expected: 1234.56
121/// ```
122///
123/// ```yaml,sandbox
124/// title: "Parse percent suffix"
125/// formula: '=NUMBERVALUE("12.5%")'
126/// expected: 0.125
127/// ```
128///
129/// ```yaml,docs
130/// related:
131///   - VALUE
132///   - TEXT
133///   - DOLLAR
134/// faq:
135///   - q: "Does NUMBERVALUE use the global locale?"
136///     a: "No. Decimal and group separators are passed explicitly as arguments."
137/// ```
138#[derive(Debug)]
139pub struct NumberValueFn;
140
141/// [formualizer-docgen:schema:start]
142/// Name: NUMBERVALUE
143/// Type: NumberValueFn
144/// Min args: 1
145/// Max args: variadic
146/// Variadic: true
147/// Signature: NUMBERVALUE(arg1...: any@scalar)
148/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
149/// Caps: PURE
150/// [formualizer-docgen:schema:end]
151impl Function for NumberValueFn {
152    func_caps!(PURE);
153    fn name(&self) -> &'static str {
154        "NUMBERVALUE"
155    }
156    fn min_args(&self) -> usize {
157        1
158    }
159    fn variadic(&self) -> bool {
160        true
161    }
162    fn arg_schema(&self) -> &'static [ArgSchema] {
163        &ARG_ANY_ONE[..]
164    }
165    fn eval<'a, 'b, 'c>(
166        &self,
167        args: &'c [ArgumentHandle<'a, 'b>],
168        _ctx: &dyn FunctionContext<'b>,
169    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
170        if args.is_empty() || args.len() > 3 {
171            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
172                ExcelError::new_value(),
173            )));
174        }
175
176        let text = to_text(&args[0])?;
177        let decimal_sep = if args.len() >= 2 {
178            to_text(&args[1])?
179        } else {
180            ".".to_string()
181        };
182        let group_sep = if args.len() >= 3 {
183            to_text(&args[2])?
184        } else {
185            ",".to_string()
186        };
187
188        if decimal_sep.is_empty() || decimal_sep == group_sep {
189            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
190                ExcelError::new_value(),
191            )));
192        }
193
194        let mut trimmed = text.trim();
195        let mut pct_count = 0u32;
196        while let Some(prefix) = trimmed.strip_suffix('%') {
197            trimmed = prefix.trim_end();
198            pct_count += 1;
199        }
200        if trimmed.is_empty() {
201            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
202                ExcelError::new_value(),
203            )));
204        }
205
206        let cleaned = trimmed.replace(&group_sep, "").replace(&decimal_sep, ".");
207        if cleaned.matches('.').count() > 1 {
208            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
209                ExcelError::new_value(),
210            )));
211        }
212
213        let Ok(mut n) = cleaned.parse::<f64>() else {
214            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
215                ExcelError::new_value(),
216            )));
217        };
218        for _ in 0..pct_count {
219            n /= 100.0;
220        }
221
222        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(n)))
223    }
224}
225
226// TEXT(value, format_text) - limited formatting (#,0,0.00, percent, yyyy, mm, dd, hh:mm) naive
227#[derive(Debug)]
228pub struct TextFn;
229/// Formats a value as text using a format pattern.
230///
231/// This implementation supports common numeric, percent, grouping, and basic date tokens.
232///
233/// # Remarks
234/// - Requires exactly two arguments: value and format text.
235/// - Numeric text is parsed before formatting. Text that is *clearly* non-numeric (no
236///   digits) is returned unchanged (e.g. `=TEXT("abc","00")` -> `"abc"`), matching Excel.
237///   Digit-bearing text that is not a plain number (dates, currency, fractions, or
238///   locale-ambiguous values like `"1.234,56"`) still returns `#VALUE!` for now.
239/// - Error inputs are propagated unchanged.
240/// - Supported patterns are intentionally limited compared with full Excel formatting.
241///
242/// # Examples
243///
244/// ```yaml,sandbox
245/// title: "Fixed decimal formatting"
246/// formula: '=TEXT(12.3, "0.00")'
247/// expected: "12.30"
248/// ```
249///
250/// ```yaml,sandbox
251/// title: "Percent formatting"
252/// formula: '=TEXT(0.256, "0%")'
253/// expected: "26%"
254/// ```
255///
256/// ```yaml,docs
257/// related:
258///   - VALUE
259///   - FIXED
260///   - DOLLAR
261/// faq:
262///   - q: "How complete is format_text support?"
263///     a: "Only a limited subset of Excel-style numeric/date tokens is supported in this implementation."
264/// ```
265/// [formualizer-docgen:schema:start]
266/// Name: TEXT
267/// Type: TextFn
268/// Min args: 2
269/// Max args: 1
270/// Variadic: false
271/// Signature: TEXT(arg1: any@scalar)
272/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
273/// Caps: PURE
274/// [formualizer-docgen:schema:end]
275impl Function for TextFn {
276    func_caps!(PURE);
277    fn name(&self) -> &'static str {
278        "TEXT"
279    }
280    fn min_args(&self) -> usize {
281        2
282    }
283    fn arg_schema(&self) -> &'static [ArgSchema] {
284        &ARG_ANY_ONE[..]
285    }
286    fn eval<'a, 'b, 'c>(
287        &self,
288        args: &'c [ArgumentHandle<'a, 'b>],
289        ctx: &dyn FunctionContext<'b>,
290    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
291        if args.len() != 2 {
292            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
293                ExcelError::new_value(),
294            )));
295        }
296        let val = scalar_like_value(&args[0])?;
297        if let LiteralValue::Error(e) = val {
298            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
299        }
300        let fmt = to_text(&args[1])?;
301        let num = match val {
302            LiteralValue::Number(f) => f,
303            LiteralValue::Int(i) => i as f64,
304            LiteralValue::Text(t) => match ctx.locale().parse_number_invariant(&t) {
305                Some(n) => n,
306                None => {
307                    // Excel returns the text argument unchanged only when it is
308                    // *clearly* non-numeric (e.g. =TEXT("abc","00") -> "abc"). Text
309                    // that contains digits may be a number, date, currency or
310                    // fraction that Excel would coerce and format (e.g. "3-1",
311                    // "$5", "1/2", or locale-ambiguous "1.234,56"); handling those
312                    // requires a shared TEXT/VALUE coercion that does not exist yet,
313                    // so we conservatively keep returning #VALUE! for them rather
314                    // than passing them through unformatted.
315                    if t.chars().any(|c| c.is_ascii_digit()) {
316                        return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
317                            ExcelError::new_value(),
318                        )));
319                    }
320                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Text(t)));
321                }
322            },
323            LiteralValue::Boolean(b) => {
324                if b {
325                    1.0
326                } else {
327                    0.0
328                }
329            }
330            LiteralValue::Empty => 0.0,
331            LiteralValue::Error(e) => {
332                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
333            }
334            _ => 0.0,
335        };
336        let out = if fmt.contains('%') {
337            format_percent(num)
338        } else if fmt.contains('#') && fmt.contains(',') {
339            // Handle formats like #,##0 or #,##0.00
340            format_with_thousands(num, &fmt)
341        } else if fmt.contains("0.00") {
342            format!("{num:.2}")
343        } else if fmt.contains("0") {
344            if fmt.contains(".00") {
345                format!("{num:.2}")
346            } else {
347                format_number_basic(num)
348            }
349        } else {
350            // date tokens naive from serial
351            if fmt.contains("yyyy") || fmt.contains("dd") || fmt.contains("mm") {
352                format_serial_date(num, &fmt)
353            } else {
354                num.to_string()
355            }
356        };
357        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Text(out)))
358    }
359}
360
361fn format_percent(n: f64) -> String {
362    format!("{:.0}%", n * 100.0)
363}
364fn format_number_basic(n: f64) -> String {
365    if n.fract() == 0.0 {
366        format!("{n:.0}")
367    } else {
368        n.to_string()
369    }
370}
371
372fn format_with_thousands(n: f64, fmt: &str) -> String {
373    // Determine decimal places from format
374    let decimal_places = if fmt.contains(".00") {
375        2
376    } else if fmt.contains(".0") {
377        1
378    } else {
379        0
380    };
381
382    let abs_n = n.abs();
383    let formatted = if decimal_places > 0 {
384        format!("{:.prec$}", abs_n, prec = decimal_places)
385    } else {
386        format!("{:.0}", abs_n)
387    };
388
389    // Split into integer and decimal parts
390    let parts: Vec<&str> = formatted.split('.').collect();
391    let int_part = parts[0];
392    let dec_part = parts.get(1);
393
394    // Add thousands separators to integer part
395    let int_with_commas: String = int_part
396        .chars()
397        .rev()
398        .enumerate()
399        .flat_map(|(i, c)| {
400            if i > 0 && i % 3 == 0 {
401                vec![',', c]
402            } else {
403                vec![c]
404            }
405        })
406        .collect::<String>()
407        .chars()
408        .rev()
409        .collect();
410
411    // Combine with decimal part
412    let result = if let Some(dec) = dec_part {
413        format!("{}.{}", int_with_commas, dec)
414    } else {
415        int_with_commas
416    };
417
418    // Handle negative numbers
419    if n < 0.0 {
420        format!("-{}", result)
421    } else {
422        result
423    }
424}
425
426// very naive: treat integer part as days since 1899-12-31 ignoring leap bug for now
427fn format_serial_date(n: f64, fmt: &str) -> String {
428    use chrono::Datelike;
429    let days = n.trunc() as i64;
430    let base = chrono::NaiveDate::from_ymd_opt(1899, 12, 31).unwrap();
431    let date = base
432        .checked_add_signed(chrono::TimeDelta::days(days))
433        .unwrap_or(base);
434    let mut out = fmt.to_string();
435    out = out.replace("yyyy", &format!("{:04}", date.year()));
436    out = out.replace("mm", &format!("{:02}", date.month()));
437    out = out.replace("dd", &format!("{:02}", date.day()));
438    if out.contains("hh:mm") {
439        let frac = n.fract();
440        let total_minutes = (frac * 24.0 * 60.0).round() as i64;
441        let hh = (total_minutes / 60) % 24;
442        let mm = total_minutes % 60;
443        out = out.replace("hh:mm", &format!("{hh:02}:{mm:02}"));
444    }
445    out
446}
447
448pub fn register_builtins() {
449    use std::sync::Arc;
450    crate::function_registry::register_function(Arc::new(ValueFn));
451    crate::function_registry::register_function(Arc::new(NumberValueFn));
452    crate::function_registry::register_function(Arc::new(TextFn));
453}
454
455#[cfg(test)]
456mod tests {
457    use super::*;
458    use crate::test_workbook::TestWorkbook;
459    use crate::traits::ArgumentHandle;
460    use formualizer_common::{ExcelErrorKind, LiteralValue};
461    use formualizer_parse::parser::{ASTNode, ASTNodeType};
462    fn lit(v: LiteralValue) -> ASTNode {
463        ASTNode::new(ASTNodeType::Literal(v), None)
464    }
465    #[test]
466    fn value_basic() {
467        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(ValueFn));
468        let ctx = wb.interpreter();
469        let f = ctx.context.get_function("", "VALUE").unwrap();
470        let s = lit(LiteralValue::Text("12.5".into()));
471        let out = f
472            .dispatch(
473                &[ArgumentHandle::new(&s, &ctx)],
474                &ctx.function_context(None),
475            )
476            .unwrap()
477            .into_literal();
478        assert_eq!(out, LiteralValue::Number(12.5));
479    }
480
481    #[test]
482    fn value_percent_text() {
483        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(ValueFn));
484        let ctx = wb.interpreter();
485        let f = ctx.context.get_function("", "VALUE").unwrap();
486        let s = lit(LiteralValue::Text("90%".into()));
487        let out = f
488            .dispatch(
489                &[ArgumentHandle::new(&s, &ctx)],
490                &ctx.function_context(None),
491            )
492            .unwrap()
493            .into_literal();
494        assert_eq!(out, LiteralValue::Number(0.9));
495    }
496
497    #[test]
498    fn numbervalue_supports_explicit_separators_and_percent() {
499        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(NumberValueFn));
500        let ctx = wb.interpreter();
501        let f = ctx.context.get_function("", "NUMBERVALUE").unwrap();
502        let text = lit(LiteralValue::Text(" 1.234,50%% ".into()));
503        let dec = lit(LiteralValue::Text(",".into()));
504        let grp = lit(LiteralValue::Text(".".into()));
505        let out = f
506            .dispatch(
507                &[
508                    ArgumentHandle::new(&text, &ctx),
509                    ArgumentHandle::new(&dec, &ctx),
510                    ArgumentHandle::new(&grp, &ctx),
511                ],
512                &ctx.function_context(None),
513            )
514            .unwrap()
515            .into_literal();
516        assert_eq!(out, LiteralValue::Number(0.12345));
517    }
518
519    #[test]
520    fn numbervalue_rejects_bad_separators_and_multiple_decimals() {
521        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(NumberValueFn));
522        let ctx = wb.interpreter();
523        let f = ctx.context.get_function("", "NUMBERVALUE").unwrap();
524        let text = lit(LiteralValue::Text("1.2.3".into()));
525        let out = f
526            .dispatch(
527                &[ArgumentHandle::new(&text, &ctx)],
528                &ctx.function_context(None),
529            )
530            .unwrap()
531            .into_literal();
532        assert!(matches!(out, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Value));
533
534        let sep = lit(LiteralValue::Text(".".into()));
535        let out = f
536            .dispatch(
537                &[
538                    ArgumentHandle::new(&lit(LiteralValue::Text("1.2".into())), &ctx),
539                    ArgumentHandle::new(&sep, &ctx),
540                    ArgumentHandle::new(&sep, &ctx),
541                ],
542                &ctx.function_context(None),
543            )
544            .unwrap()
545            .into_literal();
546        assert!(matches!(out, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Value));
547    }
548
549    #[test]
550    fn text_basic_number() {
551        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(TextFn));
552        let ctx = wb.interpreter();
553        let f = ctx.context.get_function("", "TEXT").unwrap();
554        let n = lit(LiteralValue::Number(12.34));
555        let fmt = lit(LiteralValue::Text("0.00".into()));
556        let out = f
557            .dispatch(
558                &[
559                    ArgumentHandle::new(&n, &ctx),
560                    ArgumentHandle::new(&fmt, &ctx),
561                ],
562                &ctx.function_context(None),
563            )
564            .unwrap()
565            .into_literal();
566        assert_eq!(out, LiteralValue::Text("12.34".into()));
567    }
568
569    #[test]
570    fn text_clearly_non_numeric_text_passes_through() {
571        // Excel returns the text argument unchanged when it is *clearly* not a
572        // number (no digits): =TEXT("abc","00") -> "abc" (not #VALUE!). A numeric
573        // format does not coerce arbitrary letters.
574        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(TextFn));
575        let ctx = wb.interpreter();
576        let f = ctx.context.get_function("", "TEXT").unwrap();
577        for input in ["abc", "N/A", "hello world"] {
578            let v = lit(LiteralValue::Text(input.into()));
579            let fmt = lit(LiteralValue::Text("00".into()));
580            let out = f
581                .dispatch(
582                    &[
583                        ArgumentHandle::new(&v, &ctx),
584                        ArgumentHandle::new(&fmt, &ctx),
585                    ],
586                    &ctx.function_context(None),
587                )
588                .unwrap()
589                .into_literal();
590            assert_eq!(
591                out,
592                LiteralValue::Text(input.into()),
593                "TEXT({input:?},\"00\")"
594            );
595        }
596    }
597
598    #[test]
599    fn text_digit_bearing_text_still_errors() {
600        // Text that contains digits may be a number/date/currency/fraction that
601        // Excel would coerce and format. Until a shared TEXT/VALUE coercion exists
602        // we keep returning #VALUE! rather than passing it through unformatted,
603        // and we must not change the locale-ambiguous "1.234,56" case.
604        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(TextFn));
605        let ctx = wb.interpreter();
606        let f = ctx.context.get_function("", "TEXT").unwrap();
607        for input in ["3-1", "10-", "1.234,56", "$5", "1/2"] {
608            let v = lit(LiteralValue::Text(input.into()));
609            let fmt = lit(LiteralValue::Text("00".into()));
610            let out = f
611                .dispatch(
612                    &[
613                        ArgumentHandle::new(&v, &ctx),
614                        ArgumentHandle::new(&fmt, &ctx),
615                    ],
616                    &ctx.function_context(None),
617                )
618                .unwrap()
619                .into_literal();
620            match out {
621                LiteralValue::Error(e) => {
622                    assert_eq!(e.to_string(), "#VALUE!", "TEXT({input:?},\"00\")")
623                }
624                other => panic!("expected #VALUE! for TEXT({input:?},\"00\"), got {other:?}"),
625            }
626        }
627    }
628}
formualizer_eval/builtins/text/value_text.rs

formualizer_eval/builtins/text/
value_text.rs
