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// TEXT(value, format_text) - limited formatting (#,0,0.00, percent, yyyy, mm, dd, hh:mm) naive
108#[derive(Debug)]
109pub struct TextFn;
110/// Formats a value as text using a format pattern.
111///
112/// This implementation supports common numeric, percent, grouping, and basic date tokens.
113///
114/// # Remarks
115/// - Requires exactly two arguments: value and format text.
116/// - Numeric text is parsed before formatting; invalid numeric text returns `#VALUE!`.
117/// - Error inputs are propagated unchanged.
118/// - Supported patterns are intentionally limited compared with full Excel formatting.
119///
120/// # Examples
121///
122/// ```yaml,sandbox
123/// title: "Fixed decimal formatting"
124/// formula: '=TEXT(12.3, "0.00")'
125/// expected: "12.30"
126/// ```
127///
128/// ```yaml,sandbox
129/// title: "Percent formatting"
130/// formula: '=TEXT(0.256, "0%")'
131/// expected: "26%"
132/// ```
133///
134/// ```yaml,docs
135/// related:
136///   - VALUE
137///   - FIXED
138///   - DOLLAR
139/// faq:
140///   - q: "How complete is format_text support?"
141///     a: "Only a limited subset of Excel-style numeric/date tokens is supported in this implementation."
142/// ```
143/// [formualizer-docgen:schema:start]
144/// Name: TEXT
145/// Type: TextFn
146/// Min args: 2
147/// Max args: 1
148/// Variadic: false
149/// Signature: TEXT(arg1: any@scalar)
150/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}
151/// Caps: PURE
152/// [formualizer-docgen:schema:end]
153impl Function for TextFn {
154    func_caps!(PURE);
155    fn name(&self) -> &'static str {
156        "TEXT"
157    }
158    fn min_args(&self) -> usize {
159        2
160    }
161    fn arg_schema(&self) -> &'static [ArgSchema] {
162        &ARG_ANY_ONE[..]
163    }
164    fn eval<'a, 'b, 'c>(
165        &self,
166        args: &'c [ArgumentHandle<'a, 'b>],
167        ctx: &dyn FunctionContext<'b>,
168    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
169        if args.len() != 2 {
170            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
171                ExcelError::new_value(),
172            )));
173        }
174        let val = scalar_like_value(&args[0])?;
175        if let LiteralValue::Error(e) = val {
176            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
177        }
178        let fmt = to_text(&args[1])?;
179        let num = match val {
180            LiteralValue::Number(f) => f,
181            LiteralValue::Int(i) => i as f64,
182            LiteralValue::Text(t) => {
183                let Some(n) = ctx.locale().parse_number_invariant(&t) else {
184                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
185                        ExcelError::new_value(),
186                    )));
187                };
188                n
189            }
190            LiteralValue::Boolean(b) => {
191                if b {
192                    1.0
193                } else {
194                    0.0
195                }
196            }
197            LiteralValue::Empty => 0.0,
198            LiteralValue::Error(e) => {
199                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
200            }
201            _ => 0.0,
202        };
203        let out = if fmt.contains('%') {
204            format_percent(num)
205        } else if fmt.contains('#') && fmt.contains(',') {
206            // Handle formats like #,##0 or #,##0.00
207            format_with_thousands(num, &fmt)
208        } else if fmt.contains("0.00") {
209            format!("{num:.2}")
210        } else if fmt.contains("0") {
211            if fmt.contains(".00") {
212                format!("{num:.2}")
213            } else {
214                format_number_basic(num)
215            }
216        } else {
217            // date tokens naive from serial
218            if fmt.contains("yyyy") || fmt.contains("dd") || fmt.contains("mm") {
219                format_serial_date(num, &fmt)
220            } else {
221                num.to_string()
222            }
223        };
224        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Text(out)))
225    }
226}
227
228fn format_percent(n: f64) -> String {
229    format!("{:.0}%", n * 100.0)
230}
231fn format_number_basic(n: f64) -> String {
232    if n.fract() == 0.0 {
233        format!("{n:.0}")
234    } else {
235        n.to_string()
236    }
237}
238
239fn format_with_thousands(n: f64, fmt: &str) -> String {
240    // Determine decimal places from format
241    let decimal_places = if fmt.contains(".00") {
242        2
243    } else if fmt.contains(".0") {
244        1
245    } else {
246        0
247    };
248
249    let abs_n = n.abs();
250    let formatted = if decimal_places > 0 {
251        format!("{:.prec$}", abs_n, prec = decimal_places)
252    } else {
253        format!("{:.0}", abs_n)
254    };
255
256    // Split into integer and decimal parts
257    let parts: Vec<&str> = formatted.split('.').collect();
258    let int_part = parts[0];
259    let dec_part = parts.get(1);
260
261    // Add thousands separators to integer part
262    let int_with_commas: String = int_part
263        .chars()
264        .rev()
265        .enumerate()
266        .flat_map(|(i, c)| {
267            if i > 0 && i % 3 == 0 {
268                vec![',', c]
269            } else {
270                vec![c]
271            }
272        })
273        .collect::<String>()
274        .chars()
275        .rev()
276        .collect();
277
278    // Combine with decimal part
279    let result = if let Some(dec) = dec_part {
280        format!("{}.{}", int_with_commas, dec)
281    } else {
282        int_with_commas
283    };
284
285    // Handle negative numbers
286    if n < 0.0 {
287        format!("-{}", result)
288    } else {
289        result
290    }
291}
292
293// very naive: treat integer part as days since 1899-12-31 ignoring leap bug for now
294fn format_serial_date(n: f64, fmt: &str) -> String {
295    use chrono::Datelike;
296    let days = n.trunc() as i64;
297    let base = chrono::NaiveDate::from_ymd_opt(1899, 12, 31).unwrap();
298    let date = base
299        .checked_add_signed(chrono::TimeDelta::days(days))
300        .unwrap_or(base);
301    let mut out = fmt.to_string();
302    out = out.replace("yyyy", &format!("{:04}", date.year()));
303    out = out.replace("mm", &format!("{:02}", date.month()));
304    out = out.replace("dd", &format!("{:02}", date.day()));
305    if out.contains("hh:mm") {
306        let frac = n.fract();
307        let total_minutes = (frac * 24.0 * 60.0).round() as i64;
308        let hh = (total_minutes / 60) % 24;
309        let mm = total_minutes % 60;
310        out = out.replace("hh:mm", &format!("{hh:02}:{mm:02}"));
311    }
312    out
313}
314
315pub fn register_builtins() {
316    use std::sync::Arc;
317    crate::function_registry::register_function(Arc::new(ValueFn));
318    crate::function_registry::register_function(Arc::new(TextFn));
319}
320
321#[cfg(test)]
322mod tests {
323    use super::*;
324    use crate::test_workbook::TestWorkbook;
325    use crate::traits::ArgumentHandle;
326    use formualizer_common::LiteralValue;
327    use formualizer_parse::parser::{ASTNode, ASTNodeType};
328    fn lit(v: LiteralValue) -> ASTNode {
329        ASTNode::new(ASTNodeType::Literal(v), None)
330    }
331    #[test]
332    fn value_basic() {
333        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(ValueFn));
334        let ctx = wb.interpreter();
335        let f = ctx.context.get_function("", "VALUE").unwrap();
336        let s = lit(LiteralValue::Text("12.5".into()));
337        let out = f
338            .dispatch(
339                &[ArgumentHandle::new(&s, &ctx)],
340                &ctx.function_context(None),
341            )
342            .unwrap()
343            .into_literal();
344        assert_eq!(out, LiteralValue::Number(12.5));
345    }
346    #[test]
347    fn text_basic_number() {
348        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(TextFn));
349        let ctx = wb.interpreter();
350        let f = ctx.context.get_function("", "TEXT").unwrap();
351        let n = lit(LiteralValue::Number(12.34));
352        let fmt = lit(LiteralValue::Text("0.00".into()));
353        let out = f
354            .dispatch(
355                &[
356                    ArgumentHandle::new(&n, &ctx),
357                    ArgumentHandle::new(&fmt, &ctx),
358                ],
359                &ctx.function_context(None),
360            )
361            .unwrap()
362            .into_literal();
363        assert_eq!(out, LiteralValue::Text("12.34".into()));
364    }
365}
formualizer_eval/builtins/text/value_text.rs

formualizer_eval/builtins/text/
value_text.rs
