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, 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    })
13}
14
15fn to_text<'a, 'b>(a: &ArgumentHandle<'a, 'b>) -> Result<String, ExcelError> {
16    let v = scalar_like_value(a)?;
17    Ok(match v {
18        LiteralValue::Text(s) => s,
19        LiteralValue::Empty => String::new(),
20        LiteralValue::Boolean(b) => {
21            if b {
22                "TRUE".into()
23            } else {
24                "FALSE".into()
25            }
26        }
27        LiteralValue::Int(i) => i.to_string(),
28        LiteralValue::Number(f) => f.to_string(),
29        LiteralValue::Error(e) => return Err(e),
30        other => other.to_string(),
31    })
32}
33
34// VALUE(text) - parse number
35#[derive(Debug)]
36pub struct ValueFn;
37impl Function for ValueFn {
38    func_caps!(PURE);
39    fn name(&self) -> &'static str {
40        "VALUE"
41    }
42    fn min_args(&self) -> usize {
43        1
44    }
45    fn arg_schema(&self) -> &'static [ArgSchema] {
46        &ARG_ANY_ONE[..]
47    }
48    fn eval<'a, 'b, 'c>(
49        &self,
50        args: &'c [ArgumentHandle<'a, 'b>],
51        ctx: &dyn FunctionContext<'b>,
52    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
53        let s = to_text(&args[0])?;
54        let Some(n) = ctx.locale().parse_number_invariant(&s) else {
55            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
56                ExcelError::new_value(),
57            )));
58        };
59        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(n)))
60    }
61}
62
63// TEXT(value, format_text) - limited formatting (#,0,0.00, percent, yyyy, mm, dd, hh:mm) naive
64#[derive(Debug)]
65pub struct TextFn;
66impl Function for TextFn {
67    func_caps!(PURE);
68    fn name(&self) -> &'static str {
69        "TEXT"
70    }
71    fn min_args(&self) -> usize {
72        2
73    }
74    fn arg_schema(&self) -> &'static [ArgSchema] {
75        &ARG_ANY_ONE[..]
76    }
77    fn eval<'a, 'b, 'c>(
78        &self,
79        args: &'c [ArgumentHandle<'a, 'b>],
80        ctx: &dyn FunctionContext<'b>,
81    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
82        if args.len() != 2 {
83            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
84                ExcelError::new_value(),
85            )));
86        }
87        let val = scalar_like_value(&args[0])?;
88        if let LiteralValue::Error(e) = val {
89            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
90        }
91        let fmt = to_text(&args[1])?;
92        let num = match val {
93            LiteralValue::Number(f) => f,
94            LiteralValue::Int(i) => i as f64,
95            LiteralValue::Text(t) => {
96                let Some(n) = ctx.locale().parse_number_invariant(&t) else {
97                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
98                        ExcelError::new_value(),
99                    )));
100                };
101                n
102            }
103            LiteralValue::Boolean(b) => {
104                if b {
105                    1.0
106                } else {
107                    0.0
108                }
109            }
110            LiteralValue::Empty => 0.0,
111            LiteralValue::Error(e) => {
112                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
113            }
114            _ => 0.0,
115        };
116        let out = if fmt.contains('%') {
117            format_percent(num)
118        } else if fmt.contains('#') && fmt.contains(',') {
119            // Handle formats like #,##0 or #,##0.00
120            format_with_thousands(num, &fmt)
121        } else if fmt.contains("0.00") {
122            format!("{num:.2}")
123        } else if fmt.contains("0") {
124            if fmt.contains(".00") {
125                format!("{num:.2}")
126            } else {
127                format_number_basic(num)
128            }
129        } else {
130            // date tokens naive from serial
131            if fmt.contains("yyyy") || fmt.contains("dd") || fmt.contains("mm") {
132                format_serial_date(num, &fmt)
133            } else {
134                num.to_string()
135            }
136        };
137        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Text(out)))
138    }
139}
140
141fn format_percent(n: f64) -> String {
142    format!("{:.0}%", n * 100.0)
143}
144fn format_number_basic(n: f64) -> String {
145    if n.fract() == 0.0 {
146        format!("{n:.0}")
147    } else {
148        n.to_string()
149    }
150}
151
152fn format_with_thousands(n: f64, fmt: &str) -> String {
153    // Determine decimal places from format
154    let decimal_places = if fmt.contains(".00") {
155        2
156    } else if fmt.contains(".0") {
157        1
158    } else {
159        0
160    };
161
162    let abs_n = n.abs();
163    let formatted = if decimal_places > 0 {
164        format!("{:.prec$}", abs_n, prec = decimal_places)
165    } else {
166        format!("{:.0}", abs_n)
167    };
168
169    // Split into integer and decimal parts
170    let parts: Vec<&str> = formatted.split('.').collect();
171    let int_part = parts[0];
172    let dec_part = parts.get(1);
173
174    // Add thousands separators to integer part
175    let int_with_commas: String = int_part
176        .chars()
177        .rev()
178        .enumerate()
179        .flat_map(|(i, c)| {
180            if i > 0 && i % 3 == 0 {
181                vec![',', c]
182            } else {
183                vec![c]
184            }
185        })
186        .collect::<String>()
187        .chars()
188        .rev()
189        .collect();
190
191    // Combine with decimal part
192    let result = if let Some(dec) = dec_part {
193        format!("{}.{}", int_with_commas, dec)
194    } else {
195        int_with_commas
196    };
197
198    // Handle negative numbers
199    if n < 0.0 {
200        format!("-{}", result)
201    } else {
202        result
203    }
204}
205
206// very naive: treat integer part as days since 1899-12-31 ignoring leap bug for now
207fn format_serial_date(n: f64, fmt: &str) -> String {
208    use chrono::Datelike;
209    let days = n.trunc() as i64;
210    let base = chrono::NaiveDate::from_ymd_opt(1899, 12, 31).unwrap();
211    let date = base
212        .checked_add_signed(chrono::TimeDelta::days(days))
213        .unwrap_or(base);
214    let mut out = fmt.to_string();
215    out = out.replace("yyyy", &format!("{:04}", date.year()));
216    out = out.replace("mm", &format!("{:02}", date.month()));
217    out = out.replace("dd", &format!("{:02}", date.day()));
218    if out.contains("hh:mm") {
219        let frac = n.fract();
220        let total_minutes = (frac * 24.0 * 60.0).round() as i64;
221        let hh = (total_minutes / 60) % 24;
222        let mm = total_minutes % 60;
223        out = out.replace("hh:mm", &format!("{hh:02}:{mm:02}"));
224    }
225    out
226}
227
228pub fn register_builtins() {
229    use std::sync::Arc;
230    crate::function_registry::register_function(Arc::new(ValueFn));
231    crate::function_registry::register_function(Arc::new(TextFn));
232}
233
234#[cfg(test)]
235mod tests {
236    use super::*;
237    use crate::test_workbook::TestWorkbook;
238    use crate::traits::ArgumentHandle;
239    use formualizer_common::LiteralValue;
240    use formualizer_parse::parser::{ASTNode, ASTNodeType};
241    fn lit(v: LiteralValue) -> ASTNode {
242        ASTNode::new(ASTNodeType::Literal(v), None)
243    }
244    #[test]
245    fn value_basic() {
246        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(ValueFn));
247        let ctx = wb.interpreter();
248        let f = ctx.context.get_function("", "VALUE").unwrap();
249        let s = lit(LiteralValue::Text("12.5".into()));
250        let out = f
251            .dispatch(
252                &[ArgumentHandle::new(&s, &ctx)],
253                &ctx.function_context(None),
254            )
255            .unwrap()
256            .into_literal();
257        assert_eq!(out, LiteralValue::Number(12.5));
258    }
259    #[test]
260    fn text_basic_number() {
261        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(TextFn));
262        let ctx = wb.interpreter();
263        let f = ctx.context.get_function("", "TEXT").unwrap();
264        let n = lit(LiteralValue::Number(12.34));
265        let fmt = lit(LiteralValue::Text("0.00".into()));
266        let out = f
267            .dispatch(
268                &[
269                    ArgumentHandle::new(&n, &ctx),
270                    ArgumentHandle::new(&fmt, &ctx),
271                ],
272                &ctx.function_context(None),
273            )
274            .unwrap()
275            .into_literal();
276        assert_eq!(out, LiteralValue::Text("12.34".into()));
277    }
278}
formualizer_eval/builtins/text/value_text.rs

formualizer_eval/builtins/text/
value_text.rs
