ironcalc_base/number_format.rs
1use crate::{
2 formatter::{self, format::Formatted},
3 locale::get_locale,
4 types::NumFmt,
5};
6
7// The standard built-in number formats defined by ECMA-376 (§18.8.30), indexed by `numFmtId`
8// so that `DEFAULT_NUM_FMTS[id]` is the format code for that built-in id. Ids 23–36 are
9// reserved/undefined by the spec and resolve to "general". A workbook-defined `numFmt` always
10// takes precedence over this table (see `get_num_fmt`), so this is only consulted for a
11// referenced-but-undefined built-in id.
12const DEFAULT_NUM_FMTS: &[&str] = &[
13 "general", // 0
14 "0", // 1
15 "0.00", // 2
16 "#,##0", // 3
17 "#,##0.00", // 4
18 "$#,##0_);($#,##0)", // 5
19 "$#,##0_);[Red]($#,##0)", // 6
20 "$#,##0.00_);($#,##0.00)", // 7
21 "$#,##0.00_);[Red]($#,##0.00)", // 8
22 "0%", // 9
23 "0.00%", // 10
24 "0.00E+00", // 11
25 "# ?/?", // 12
26 "# ??/??", // 13
27 "mm-dd-yy", // 14
28 "d-mmm-yy", // 15
29 "d-mmm", // 16
30 "mmm-yy", // 17
31 "h:mm AM/PM", // 18
32 "h:mm:ss AM/PM", // 19
33 "h:mm", // 20
34 "h:mm:ss", // 21
35 "m/d/yy h:mm", // 22
36 "general", // 23 (reserved)
37 "general", // 24 (reserved)
38 "general", // 25 (reserved)
39 "general", // 26 (reserved)
40 "general", // 27 (reserved)
41 "general", // 28 (reserved)
42 "general", // 29 (reserved)
43 "general", // 30 (reserved)
44 "general", // 31 (reserved)
45 "general", // 32 (reserved)
46 "general", // 33 (reserved)
47 "general", // 34 (reserved)
48 "general", // 35 (reserved)
49 "general", // 36 (reserved)
50 "#,##0_);(#,##0)", // 37
51 "#,##0_);[Red](#,##0)", // 38
52 "#,##0.00_);(#,##0.00)", // 39
53 "#,##0.00_);[Red](#,##0.00)", // 40
54 "_(* #,##0_);_(* \\(#,##0\\);_(* \"-\"_);_(@_)", // 41
55 "_(\"$\"* #,##0_);_(\"$\"* \\(#,##0\\);_(\"$\"* \"-\"_);_(@_)", // 42
56 "_(* #,##0.00_);_(* \\(#,##0.00\\);_(* \"-\"??_);_(@_)", // 43
57 "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)", // 44
58 "mm:ss", // 45
59 "[h]:mm:ss", // 46
60 "mmss.0", // 47
61 "##0.0E+0", // 48
62 "@", // 49
63];
64
65pub fn get_default_num_fmt_id(num_fmt: &str) -> Option<i32> {
66 for (index, default_num_fmt) in DEFAULT_NUM_FMTS.iter().enumerate() {
67 if default_num_fmt == &num_fmt {
68 return Some(index as i32);
69 };
70 }
71 None
72}
73
74pub fn get_num_fmt(num_fmt_id: i32, num_fmts: &[NumFmt]) -> String {
75 // Check if it defined
76 for num_fmt in num_fmts {
77 if num_fmt.num_fmt_id == num_fmt_id {
78 return num_fmt.format_code.clone();
79 }
80 }
81 // Return one of the default ones
82 if num_fmt_id < DEFAULT_NUM_FMTS.len() as i32 {
83 return DEFAULT_NUM_FMTS[num_fmt_id as usize].to_string();
84 }
85 // Return general
86 DEFAULT_NUM_FMTS[0].to_string()
87}
88
89pub fn get_new_num_fmt_index(num_fmts: &[NumFmt]) -> i32 {
90 let mut index = DEFAULT_NUM_FMTS.len() as i32;
91 let mut found = true;
92 while found {
93 found = false;
94 for num_fmt in num_fmts {
95 if num_fmt.num_fmt_id == index {
96 found = true;
97 index += 1;
98 break;
99 }
100 }
101 }
102 index
103}
104
105pub fn to_precision(value: f64, precision: usize) -> f64 {
106 if value.is_infinite() || value.is_nan() {
107 return value;
108 }
109 to_precision_str(value, precision)
110 .parse::<f64>()
111 .unwrap_or({
112 // TODO: do this in a way that does not require a possible error
113 0.0
114 })
115}
116
117/// It rounds a `f64` with `p` significant figures:
118/// ```
119/// use ironcalc_base::number_format;
120/// assert_eq!(number_format::to_precision(0.1+0.2, 15), 0.3);
121/// assert_eq!(number_format::to_excel_precision_str(0.1+0.2), "0.3");
122/// ```
123/// This intends to be equivalent to the js: `${parseFloat(value.toPrecision(precision)})`
124/// See ([ecma](https://tc39.es/ecma262/#sec-number.prototype.toprecision)).
125pub fn to_excel_precision_str(value: f64) -> String {
126 to_precision_str(value, 15)
127}
128
129pub fn to_excel_precision(value: f64, precision: usize) -> f64 {
130 if !value.is_finite() {
131 return value;
132 }
133
134 let s = format!("{:.*e}", precision.saturating_sub(1), value);
135 s.parse::<f64>().unwrap_or(value)
136}
137
138pub fn to_precision_str(value: f64, precision: usize) -> String {
139 if !value.is_finite() {
140 if value.is_infinite() {
141 return "inf".to_string();
142 } else {
143 return "NaN".to_string();
144 }
145 }
146
147 let s = format!("{:.*e}", precision.saturating_sub(1), value);
148 let parsed = s.parse::<f64>().unwrap_or(value);
149
150 // I would love to use the std library. There is not a speed concern here
151 // problem is it doesn't do the right thing
152 // Also ryu is my favorite _modern_ algorithm
153 let mut buffer = ryu::Buffer::new();
154 let text = buffer.format(parsed);
155 // The above algorithm converts 2 to 2.0 regrettably
156 if let Some(stripped) = text.strip_suffix(".0") {
157 return stripped.to_string();
158 }
159 text.to_string()
160}
161
162pub fn format_number(value: f64, format_code: &str, locale: &str) -> Formatted {
163 let locale = match get_locale(locale) {
164 Ok(l) => l,
165 Err(_) => {
166 return Formatted {
167 text: "#ERROR!".to_owned(),
168 color: None,
169 error: Some("Invalid locale".to_string()),
170 }
171 }
172 };
173 formatter::format::format_number(value, format_code, locale)
174}
175
176#[cfg(test)]
177mod tests {
178 use super::*;
179
180 // The default table is indexed by `numFmtId`: `get_num_fmt(id, &[])` must return the
181 // ECMA-376 §18.8.30 built-in code for that id.
182 #[test]
183 fn builtin_ids_map_to_the_ecma376_codes() {
184 assert_eq!(get_num_fmt(0, &[]), "general");
185 assert_eq!(get_num_fmt(5, &[]), "$#,##0_);($#,##0)");
186 assert_eq!(get_num_fmt(8, &[]), "$#,##0.00_);[Red]($#,##0.00)");
187 assert_eq!(get_num_fmt(11, &[]), "0.00E+00");
188 assert_eq!(get_num_fmt(37, &[]), "#,##0_);(#,##0)");
189 // id 39 is the one that produced `#VALUE!` in the wild (was "t0.00").
190 assert_eq!(get_num_fmt(39, &[]), "#,##0.00_);(#,##0.00)");
191 assert_eq!(
192 get_num_fmt(44, &[]),
193 "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)"
194 );
195 assert_eq!(get_num_fmt(49, &[]), "@");
196 // Reserved ids 23–36 resolve to "general".
197 assert_eq!(get_num_fmt(25, &[]), "general");
198 // Out-of-range falls back to "general" too.
199 assert_eq!(get_num_fmt(500, &[]), "general");
200 }
201
202 // A workbook-defined `numFmt` still wins over the default table.
203 #[test]
204 fn workbook_defined_num_fmt_takes_precedence() {
205 let defined = vec![NumFmt {
206 num_fmt_id: 39,
207 format_code: "FILE-OWN".to_string(),
208 }];
209 assert_eq!(get_num_fmt(39, &defined), "FILE-OWN");
210 }
211
212 // Every corrected built-in code must actually be formattable by IronCalc's own formatter
213 // (a valid number must NOT come back as an error). This is what was broken: the old garbage
214 // codes (e.g. "t0.00") made the formatter fail on valid numbers.
215 //
216 // Exception: id 47 (`mmss.0`, elapsed minutes:seconds.tenths) is the correct ECMA-376 code
217 // but IronCalc's *formatter* does not yet parse that pattern — a separate formatter gap, not
218 // a table bug (the old code at id 47 didn't render either). Kept correct in the table so a
219 // future formatter fix makes it work; excluded from this render smoke-check.
220 #[test]
221 fn corrected_builtins_format_without_error() {
222 let mut failures = Vec::new();
223 for id in [
224 5, 6, 7, 8, 11, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 48, 49,
225 ] {
226 let code = get_num_fmt(id, &[]);
227 let out = format_number(1234.5, &code, "en");
228 if out.error.is_some() || out.text.contains("#VALUE!") || out.text.contains("#ERROR!") {
229 failures.push((id, code, out.text, out.error));
230 }
231 }
232 assert!(
233 failures.is_empty(),
234 "codes the formatter rejected: {failures:?}"
235 );
236 }
237
238 // The specific regression: id 39 formats 175000 as a grouped 2-decimal number, not `#VALUE!`.
239 #[test]
240 fn id_39_formats_currency_value() {
241 let out = format_number(175000.0, &get_num_fmt(39, &[]), "en");
242 assert!(out.error.is_none());
243 assert!(out.text.contains("175,000.00"), "got {:?}", out.text);
244 }
245}