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
105/// Reads a number written with digits: `12`, `-3.5`, `.5`, `1e5`.
106///
107/// It is `str::parse::<f64>` without its two surprises. That one also reads
108/// the words "NaN", "inf" and "infinity", and it turns a number too large to
109/// hold, like `1e400`, into infinity. In a spreadsheet none of those is a
110/// number, they are text: what is read here is always a number one can count
111/// with.
112pub(crate) fn parse_finite_number(text: &str) -> Option<f64> {
113 text.parse::<f64>().ok().filter(|number| number.is_finite())
114}
115
116pub fn to_precision(value: f64, precision: usize) -> f64 {
117 if value.is_infinite() || value.is_nan() {
118 return value;
119 }
120 to_precision_str(value, precision)
121 .parse::<f64>()
122 .unwrap_or({
123 // TODO: do this in a way that does not require a possible error
124 0.0
125 })
126}
127
128/// It rounds a `f64` with `p` significant figures:
129/// ```
130/// use ironcalc_base::number_format;
131/// assert_eq!(number_format::to_precision(0.1+0.2, 15), 0.3);
132/// assert_eq!(number_format::to_excel_precision_str(0.1+0.2), "0.3");
133/// ```
134/// This intends to be equivalent to the js: `${parseFloat(value.toPrecision(precision)})`
135/// See ([ecma](https://tc39.es/ecma262/#sec-number.prototype.toprecision)).
136pub fn to_excel_precision_str(value: f64) -> String {
137 to_precision_str(value, 15)
138}
139
140pub fn to_excel_precision(value: f64, precision: usize) -> f64 {
141 if !value.is_finite() {
142 return value;
143 }
144
145 let s = format!("{:.*e}", precision.saturating_sub(1), value);
146 s.parse::<f64>().unwrap_or(value)
147}
148
149pub fn to_precision_str(value: f64, precision: usize) -> String {
150 if !value.is_finite() {
151 if value.is_infinite() {
152 return "inf".to_string();
153 } else {
154 return "NaN".to_string();
155 }
156 }
157
158 let s = format!("{:.*e}", precision.saturating_sub(1), value);
159 let parsed = s.parse::<f64>().unwrap_or(value);
160
161 // I would love to use the std library. There is not a speed concern here
162 // problem is it doesn't do the right thing
163 // Also ryu is my favorite _modern_ algorithm
164 let mut buffer = ryu::Buffer::new();
165 let text = buffer.format(parsed);
166 // The above algorithm converts 2 to 2.0 regrettably
167 if let Some(stripped) = text.strip_suffix(".0") {
168 return stripped.to_string();
169 }
170 text.to_string()
171}
172
173pub fn format_number(value: f64, format_code: &str, locale: &str) -> Formatted {
174 let locale = match get_locale(locale) {
175 Ok(l) => l,
176 Err(_) => {
177 return Formatted {
178 text: "#ERROR!".to_owned(),
179 color: None,
180 error: Some("Invalid locale".to_string()),
181 }
182 }
183 };
184 formatter::format::format_number(value, format_code, locale)
185}
186
187#[cfg(test)]
188mod tests {
189 use super::*;
190
191 // The default table is indexed by `numFmtId`: `get_num_fmt(id, &[])` must return the
192 // ECMA-376 §18.8.30 built-in code for that id.
193 #[test]
194 fn builtin_ids_map_to_the_ecma376_codes() {
195 assert_eq!(get_num_fmt(0, &[]), "general");
196 assert_eq!(get_num_fmt(5, &[]), "$#,##0_);($#,##0)");
197 assert_eq!(get_num_fmt(8, &[]), "$#,##0.00_);[Red]($#,##0.00)");
198 assert_eq!(get_num_fmt(11, &[]), "0.00E+00");
199 assert_eq!(get_num_fmt(37, &[]), "#,##0_);(#,##0)");
200 // id 39 is the one that produced `#VALUE!` in the wild (was "t0.00").
201 assert_eq!(get_num_fmt(39, &[]), "#,##0.00_);(#,##0.00)");
202 assert_eq!(
203 get_num_fmt(44, &[]),
204 "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)"
205 );
206 assert_eq!(get_num_fmt(49, &[]), "@");
207 // Reserved ids 23–36 resolve to "general".
208 assert_eq!(get_num_fmt(25, &[]), "general");
209 // Out-of-range falls back to "general" too.
210 assert_eq!(get_num_fmt(500, &[]), "general");
211 }
212
213 // A workbook-defined `numFmt` still wins over the default table.
214 #[test]
215 fn workbook_defined_num_fmt_takes_precedence() {
216 let defined = vec![NumFmt {
217 num_fmt_id: 39,
218 format_code: "FILE-OWN".to_string(),
219 }];
220 assert_eq!(get_num_fmt(39, &defined), "FILE-OWN");
221 }
222
223 // Every corrected built-in code must actually be formattable by IronCalc's own formatter
224 // (a valid number must NOT come back as an error). This is what was broken: the old garbage
225 // codes (e.g. "t0.00") made the formatter fail on valid numbers.
226 //
227 // Exception: id 47 (`mmss.0`, elapsed minutes:seconds.tenths) is the correct ECMA-376 code
228 // but IronCalc's *formatter* does not yet parse that pattern — a separate formatter gap, not
229 // a table bug (the old code at id 47 didn't render either). Kept correct in the table so a
230 // future formatter fix makes it work; excluded from this render smoke-check.
231 #[test]
232 fn corrected_builtins_format_without_error() {
233 let mut failures = Vec::new();
234 for id in [
235 5, 6, 7, 8, 11, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 48, 49,
236 ] {
237 let code = get_num_fmt(id, &[]);
238 let out = format_number(1234.5, &code, "en");
239 if out.error.is_some() || out.text.contains("#VALUE!") || out.text.contains("#ERROR!") {
240 failures.push((id, code, out.text, out.error));
241 }
242 }
243 assert!(
244 failures.is_empty(),
245 "codes the formatter rejected: {failures:?}"
246 );
247 }
248
249 // The specific regression: id 39 formats 175000 as a grouped 2-decimal number, not `#VALUE!`.
250 #[test]
251 fn id_39_formats_currency_value() {
252 let out = format_number(175000.0, &get_num_fmt(39, &[]), "en");
253 assert!(out.error.is_none());
254 assert!(out.text.contains("175,000.00"), "got {:?}", out.text);
255 }
256}