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        _: &dyn FunctionContext<'b>,
52    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
53        let s = to_text(&args[0])?;
54        match s.trim().parse::<f64>() {
55            Ok(n) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Number(n))),
56            Err(_) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
57                ExcelError::new_value(),
58            ))),
59        }
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        _: &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) => t.parse::<f64>().unwrap_or(0.0),
96            LiteralValue::Boolean(b) => {
97                if b {
98                    1.0
99                } else {
100                    0.0
101                }
102            }
103            LiteralValue::Empty => 0.0,
104            LiteralValue::Error(e) => {
105                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
106            }
107            _ => 0.0,
108        };
109        let out = if fmt.contains('%') {
110            format_percent(num)
111        } else if fmt.contains('#') && fmt.contains(',') {
112            // Handle formats like #,##0 or #,##0.00
113            format_with_thousands(num, &fmt)
114        } else if fmt.contains("0.00") {
115            format!("{num:.2}")
116        } else if fmt.contains("0") {
117            if fmt.contains(".00") {
118                format!("{num:.2}")
119            } else {
120                format_number_basic(num)
121            }
122        } else {
123            // date tokens naive from serial
124            if fmt.contains("yyyy") || fmt.contains("dd") || fmt.contains("mm") {
125                format_serial_date(num, &fmt)
126            } else {
127                num.to_string()
128            }
129        };
130        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Text(out)))
131    }
132}
133
134fn format_percent(n: f64) -> String {
135    format!("{:.0}%", n * 100.0)
136}
137fn format_number_basic(n: f64) -> String {
138    if n.fract() == 0.0 {
139        format!("{n:.0}")
140    } else {
141        n.to_string()
142    }
143}
144
145fn format_with_thousands(n: f64, fmt: &str) -> String {
146    // Determine decimal places from format
147    let decimal_places = if fmt.contains(".00") {
148        2
149    } else if fmt.contains(".0") {
150        1
151    } else {
152        0
153    };
154
155    let abs_n = n.abs();
156    let formatted = if decimal_places > 0 {
157        format!("{:.prec$}", abs_n, prec = decimal_places)
158    } else {
159        format!("{:.0}", abs_n)
160    };
161
162    // Split into integer and decimal parts
163    let parts: Vec<&str> = formatted.split('.').collect();
164    let int_part = parts[0];
165    let dec_part = parts.get(1);
166
167    // Add thousands separators to integer part
168    let int_with_commas: String = int_part
169        .chars()
170        .rev()
171        .enumerate()
172        .flat_map(|(i, c)| {
173            if i > 0 && i % 3 == 0 {
174                vec![',', c]
175            } else {
176                vec![c]
177            }
178        })
179        .collect::<String>()
180        .chars()
181        .rev()
182        .collect();
183
184    // Combine with decimal part
185    let result = if let Some(dec) = dec_part {
186        format!("{}.{}", int_with_commas, dec)
187    } else {
188        int_with_commas
189    };
190
191    // Handle negative numbers
192    if n < 0.0 {
193        format!("-{}", result)
194    } else {
195        result
196    }
197}
198
199// very naive: treat integer part as days since 1899-12-31 ignoring leap bug for now
200fn format_serial_date(n: f64, fmt: &str) -> String {
201    use chrono::Datelike;
202    let days = n.trunc() as i64;
203    let base = chrono::NaiveDate::from_ymd_opt(1899, 12, 31).unwrap();
204    let date = base
205        .checked_add_signed(chrono::TimeDelta::days(days))
206        .unwrap_or(base);
207    let mut out = fmt.to_string();
208    out = out.replace("yyyy", &format!("{:04}", date.year()));
209    out = out.replace("mm", &format!("{:02}", date.month()));
210    out = out.replace("dd", &format!("{:02}", date.day()));
211    if out.contains("hh:mm") {
212        let frac = n.fract();
213        let total_minutes = (frac * 24.0 * 60.0).round() as i64;
214        let hh = (total_minutes / 60) % 24;
215        let mm = total_minutes % 60;
216        out = out.replace("hh:mm", &format!("{hh:02}:{mm:02}"));
217    }
218    out
219}
220
221pub fn register_builtins() {
222    use std::sync::Arc;
223    crate::function_registry::register_function(Arc::new(ValueFn));
224    crate::function_registry::register_function(Arc::new(TextFn));
225}
226
227#[cfg(test)]
228mod tests {
229    use super::*;
230    use crate::test_workbook::TestWorkbook;
231    use crate::traits::ArgumentHandle;
232    use formualizer_common::LiteralValue;
233    use formualizer_parse::parser::{ASTNode, ASTNodeType};
234    fn lit(v: LiteralValue) -> ASTNode {
235        ASTNode::new(ASTNodeType::Literal(v), None)
236    }
237    #[test]
238    fn value_basic() {
239        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(ValueFn));
240        let ctx = wb.interpreter();
241        let f = ctx.context.get_function("", "VALUE").unwrap();
242        let s = lit(LiteralValue::Text("12.5".into()));
243        let out = f
244            .dispatch(
245                &[ArgumentHandle::new(&s, &ctx)],
246                &ctx.function_context(None),
247            )
248            .unwrap()
249            .into_literal();
250        assert_eq!(out, LiteralValue::Number(12.5));
251    }
252    #[test]
253    fn text_basic_number() {
254        let wb = TestWorkbook::new().with_function(std::sync::Arc::new(TextFn));
255        let ctx = wb.interpreter();
256        let f = ctx.context.get_function("", "TEXT").unwrap();
257        let n = lit(LiteralValue::Number(12.34));
258        let fmt = lit(LiteralValue::Text("0.00".into()));
259        let out = f
260            .dispatch(
261                &[
262                    ArgumentHandle::new(&n, &ctx),
263                    ArgumentHandle::new(&fmt, &ctx),
264                ],
265                &ctx.function_context(None),
266            )
267            .unwrap()
268            .into_literal();
269        assert_eq!(out, LiteralValue::Text("12.34".into()));
270    }
271}
formualizer_eval/builtins/text/value_text.rs

formualizer_eval/builtins/text/
value_text.rs
