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; invalid numeric text returns `#VALUE!`.
236/// - Error inputs are propagated unchanged.
237/// - Supported patterns are intentionally limited compared with full Excel formatting.
238///
239/// # Examples
240///
241/// ```yaml,sandbox
242/// title: "Fixed decimal formatting"
243/// formula: '=TEXT(12.3, "0.00")'
244/// expected: "12.30"
245/// ```
246///
247/// ```yaml,sandbox
248/// title: "Percent formatting"
249/// formula: '=TEXT(0.256, "0%")'
250/// expected: "26%"
251/// ```
252///
253/// ```yaml,docs
254/// related:
255///   - VALUE
256///   - FIXED
257///   - DOLLAR
258/// faq:
259///   - q: "How complete is format_text support?"
260///     a: "Only a limited subset of Excel-style numeric/date tokens is supported in this implementation."
261/// ```
262/// [formualizer-docgen:schema:start]
263/// Name: TEXT
264/// Type: TextFn
265/// Min args: 2
266/// Max args: 1
267/// Variadic: false
268/// Signature: TEXT(arg1: any@scalar)
269/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
270/// Caps: PURE
271/// [formualizer-docgen:schema:end]
272impl Function for TextFn {
273    func_caps!(PURE);
274    fn name(&self) -> &'static str {
275        "TEXT"
276    }
277    fn min_args(&self) -> usize {
278        2
279    }
280    fn arg_schema(&self) -> &'static [ArgSchema] {
281        &ARG_ANY_ONE[..]
282    }
283    fn eval<'a, 'b, 'c>(
284        &self,
285        args: &'c [ArgumentHandle<'a, 'b>],
286        ctx: &dyn FunctionContext<'b>,
287    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
288        if args.len() != 2 {
289            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
290                ExcelError::new_value(),
291            )));
292        }
293        let val = scalar_like_value(&args[0])?;
294        if let LiteralValue::Error(e) = val {
295            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
296        }
297        let fmt = to_text(&args[1])?;
298        let num = match val {
299            LiteralValue::Number(f) => f,
300            LiteralValue::Int(i) => i as f64,
301            LiteralValue::Text(t) => {
302                let Some(n) = ctx.locale().parse_number_invariant(&t) else {
303                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
304                        ExcelError::new_value(),
305                    )));
306                };
307                n
308            }
309            LiteralValue::Boolean(b) => {
310                if b {
311                    1.0
312                } else {
313                    0.0
314                }
315            }
316            LiteralValue::Empty => 0.0,
317            LiteralValue::Error(e) => {
318                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
319            }
320            _ => 0.0,
321        };
322        let out = if fmt.contains('%') {
323            format_percent(num)
324        } else if fmt.contains('#') && fmt.contains(',') {
325            // Handle formats like #,##0 or #,##0.00
326            format_with_thousands(num, &fmt)
327        } else if fmt.contains("0.00") {
328            format!("{num:.2}")
329        } else if fmt.contains("0") {
330            if fmt.contains(".00") {
331                format!("{num:.2}")
332            } else {
333                format_number_basic(num)
334            }
335        } else {
336            // date tokens naive from serial
337            if fmt.contains("yyyy") || fmt.contains("dd") || fmt.contains("mm") {
338                format_serial_date(num, &fmt)
339            } else {
340                num.to_string()
341            }
342        };
343        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Text(out)))
344    }
345}
346
347fn format_percent(n: f64) -> String {
348    format!("{:.0}%", n * 100.0)
349}
350fn format_number_basic(n: f64) -> String {
351    if n.fract() == 0.0 {
352        format!("{n:.0}")
353    } else {
354        n.to_string()
355    }
356}
357
358fn format_with_thousands(n: f64, fmt: &str) -> String {
359    // Determine decimal places from format
360    let decimal_places = if fmt.contains(".00") {
361        2
362    } else if fmt.contains(".0") {
363        1
364    } else {
365        0
366    };
367
368    let abs_n = n.abs();
369    let formatted = if decimal_places > 0 {
370        format!("{:.prec$}", abs_n, prec = decimal_places)
371    } else {
372        format!("{:.0}", abs_n)
373    };
374
375    // Split into integer and decimal parts
376    let parts: Vec<&str> = formatted.split('.').collect();
377    let int_part = parts[0];
378    let dec_part = parts.get(1);
379
380    // Add thousands separators to integer part
381    let int_with_commas: String = int_part
382        .chars()
383        .rev()
384        .enumerate()
385        .flat_map(|(i, c)| {
386            if i > 0 && i % 3 == 0 {
387                vec![',', c]
388            } else {
389                vec![c]
390            }
391        })
392        .collect::<String>()
393        .chars()
394        .rev()
395        .collect();
396
397    // Combine with decimal part
398    let result = if let Some(dec) = dec_part {
399        format!("{}.{}", int_with_commas, dec)
400    } else {
401        int_with_commas
402    };
403
404    // Handle negative numbers
405    if n < 0.0 {
406        format!("-{}", result)
407    } else {
408        result
409    }
410}
411
412// very naive: treat integer part as days since 1899-12-31 ignoring leap bug for now
413fn format_serial_date(n: f64, fmt: &str) -> String {
414    use chrono::Datelike;
415    let days = n.trunc() as i64;
416    let base = chrono::NaiveDate::from_ymd_opt(1899, 12, 31).unwrap();
417    let date = base
418        .checked_add_signed(chrono::TimeDelta::days(days))
419        .unwrap_or(base);
420    let mut out = fmt.to_string();
421    out = out.replace("yyyy", &format!("{:04}", date.year()));
422    out = out.replace("mm", &format!("{:02}", date.month()));
423    out = out.replace("dd", &format!("{:02}", date.day()));
424    if out.contains("hh:mm") {
425        let frac = n.fract();
426        let total_minutes = (frac * 24.0 * 60.0).round() as i64;
427        let hh = (total_minutes / 60) % 24;
428        let mm = total_minutes % 60;
429        out = out.replace("hh:mm", &format!("{hh:02}:{mm:02}"));
430    }
431    out
432}
433
434pub fn register_builtins() {
435    use std::sync::Arc;
436    crate::function_registry::register_function(Arc::new(ValueFn));
437    crate::function_registry::register_function(Arc::new(NumberValueFn));
438    crate::function_registry::register_function(Arc::new(TextFn));
439}
440
441#[cfg(test)]
442mod tests {
443    use super::*;
444    use crate::test_workbook::TestWorkbook;
445    use crate::traits::ArgumentHandle;
446    use formualizer_common::{ExcelErrorKind, LiteralValue};
447    use formualizer_parse::parser::{ASTNode, ASTNodeType};
448    fn lit(v: LiteralValue) -> ASTNode {
449        ASTNode::new(ASTNodeType::Literal(v), None)
450    }
451    #[test]
452    fn value_basic() {
453        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(ValueFn));
454        let ctx = wb.interpreter();
455        let f = ctx.context.get_function("", "VALUE").unwrap();
456        let s = lit(LiteralValue::Text("12.5".into()));
457        let out = f
458            .dispatch(
459                &[ArgumentHandle::new(&s, &ctx)],
460                &ctx.function_context(None),
461            )
462            .unwrap()
463            .into_literal();
464        assert_eq!(out, LiteralValue::Number(12.5));
465    }
466
467    #[test]
468    fn value_percent_text() {
469        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(ValueFn));
470        let ctx = wb.interpreter();
471        let f = ctx.context.get_function("", "VALUE").unwrap();
472        let s = lit(LiteralValue::Text("90%".into()));
473        let out = f
474            .dispatch(
475                &[ArgumentHandle::new(&s, &ctx)],
476                &ctx.function_context(None),
477            )
478            .unwrap()
479            .into_literal();
480        assert_eq!(out, LiteralValue::Number(0.9));
481    }
482
483    #[test]
484    fn numbervalue_supports_explicit_separators_and_percent() {
485        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(NumberValueFn));
486        let ctx = wb.interpreter();
487        let f = ctx.context.get_function("", "NUMBERVALUE").unwrap();
488        let text = lit(LiteralValue::Text(" 1.234,50%% ".into()));
489        let dec = lit(LiteralValue::Text(",".into()));
490        let grp = lit(LiteralValue::Text(".".into()));
491        let out = f
492            .dispatch(
493                &[
494                    ArgumentHandle::new(&text, &ctx),
495                    ArgumentHandle::new(&dec, &ctx),
496                    ArgumentHandle::new(&grp, &ctx),
497                ],
498                &ctx.function_context(None),
499            )
500            .unwrap()
501            .into_literal();
502        assert_eq!(out, LiteralValue::Number(0.12345));
503    }
504
505    #[test]
506    fn numbervalue_rejects_bad_separators_and_multiple_decimals() {
507        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(NumberValueFn));
508        let ctx = wb.interpreter();
509        let f = ctx.context.get_function("", "NUMBERVALUE").unwrap();
510        let text = lit(LiteralValue::Text("1.2.3".into()));
511        let out = f
512            .dispatch(
513                &[ArgumentHandle::new(&text, &ctx)],
514                &ctx.function_context(None),
515            )
516            .unwrap()
517            .into_literal();
518        assert!(matches!(out, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Value));
519
520        let sep = lit(LiteralValue::Text(".".into()));
521        let out = f
522            .dispatch(
523                &[
524                    ArgumentHandle::new(&lit(LiteralValue::Text("1.2".into())), &ctx),
525                    ArgumentHandle::new(&sep, &ctx),
526                    ArgumentHandle::new(&sep, &ctx),
527                ],
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
535    #[test]
536    fn text_basic_number() {
537        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(TextFn));
538        let ctx = wb.interpreter();
539        let f = ctx.context.get_function("", "TEXT").unwrap();
540        let n = lit(LiteralValue::Number(12.34));
541        let fmt = lit(LiteralValue::Text("0.00".into()));
542        let out = f
543            .dispatch(
544                &[
545                    ArgumentHandle::new(&n, &ctx),
546                    ArgumentHandle::new(&fmt, &ctx),
547                ],
548                &ctx.function_context(None),
549            )
550            .unwrap()
551            .into_literal();
552        assert_eq!(out, LiteralValue::Text("12.34".into()));
553    }
554}
formualizer_eval/builtins/text/value_text.rs

formualizer_eval/builtins/text/
value_text.rs
