ravenrs_sheet/numfmt/
mod.rs

1//! GridCraft number formats: Excel format codes (`#,##0.00;[Red](#,##0.00)`, dates, fractions,
2//! scientific…), the General display format and the `TEXT()` worksheet function. en-US only.
3#![deny(clippy::unwrap_used, clippy::expect_used, clippy::panic, clippy::unimplemented, clippy::todo, clippy::unreachable)]
4#![forbid(unsafe_code)]
5
6mod builtin;
7mod decimal;
8mod parse;
9mod render;
10
11use std::sync::Arc;
12
13use crate::base::{CellError, DateSystem, Value};
14
15pub use builtin::{builtin_format, builtin_id};
16pub use decimal::format_general_fit;
17
18use parse::{DatePart, SecKind, Section, Tok};
19use render::Out;
20
21/// Width (in characters) of General output in a cell.
22const GENERAL_WIDTH: usize = 11;
23/// What Excel paints when a date is out of range.
24const OVERFLOW: &str = "########";
25
26/// Colour from a `[Red]` or `[ColorN]` section tag.
27#[derive(Clone, Copy, Debug, PartialEq, Eq, Hash)]
28pub enum FormatColor {
29    Black,
30    Blue,
31    Cyan,
32    Green,
33    Magenta,
34    Red,
35    White,
36    Yellow,
37    /// `[Color1]`..`[Color56]` (palette index).
38    Indexed(u8),
39}
40
41impl FormatColor {
42    /// RGB value used to render this number-format color.
43    pub fn rgb(self) -> Option<[u8; 3]> {
44        use FormatColor as F;
45        Some(match self {
46            F::Black => [0, 0, 0],
47            F::Blue => [0, 0, 255],
48            F::Cyan => [0, 255, 255],
49            F::Green => [0, 255, 0],
50            F::Magenta => [255, 0, 255],
51            F::Red => [255, 0, 0],
52            F::White => [255, 255, 255],
53            F::Yellow => [255, 255, 0],
54            F::Indexed(index) => return crate::base::color::indexed_color(index),
55        })
56    }
57}
58
59/// Best guess at the ribbon's number-format category.
60#[derive(Clone, Copy, Debug, PartialEq, Eq, Hash)]
61pub enum FormatKind {
62    General,
63    Number,
64    Currency,
65    Accounting,
66    Date,
67    Time,
68    Percentage,
69    Fraction,
70    Scientific,
71    Text,
72    /// Zip codes, phone numbers, social security numbers.
73    Special,
74    Custom,
75}
76
77/// A formatted value ready to paint.
78#[derive(Clone, Debug, PartialEq, Eq, Default)]
79pub struct Formatted {
80    pub text: String,
81    pub color: Option<FormatColor>,
82    /// Repeat-fill char from `*x` and its byte position in `text`, for the renderer to expand to
83    /// the cell width.
84    pub fill: Option<(char, usize)>,
85    /// True when the result should be right-aligned by default (numbers); General text is left
86    /// aligned.
87    pub numeric: bool,
88}
89
90#[derive(Debug)]
91struct Inner {
92    code: String,
93    general: bool,
94    sections: Vec<Section>,
95    /// Sections that apply to numbers (a trailing text section is excluded).
96    num_sections: usize,
97    /// Index of the section that applies to text, if any.
98    text_section: Option<usize>,
99}
100
101/// A parsed number format code. Cheap to clone.
102#[derive(Clone, Debug)]
103pub struct NumberFormat {
104    inner: Arc<Inner>,
105}
106
107impl PartialEq for NumberFormat {
108    fn eq(&self, other: &Self) -> bool {
109        self.inner.code == other.inner.code
110    }
111}
112
113impl Default for NumberFormat {
114    fn default() -> Self {
115        NumberFormat::general()
116    }
117}
118
119/// Longest number format code, in characters (not bytes), that is parsed: Excel's own limit.
120/// A longer code is invalid: [`NumberFormat::parse`] returns a General format (the cell shows
121/// its value unformatted; the stored code is kept as is) and `TEXT` returns `#VALUE!`. The cap
122/// also bounds every allocation that scales with the number of placeholders in a code.
123pub const MAX_FORMAT_CODE_CHARS: usize = 255;
124
125/// True when `code` is longer than [`MAX_FORMAT_CODE_CHARS`] characters.
126fn too_long(code: &str) -> bool {
127    // A code of more than 255 bytes can still be short in characters; count only when needed.
128    code.len() > MAX_FORMAT_CODE_CHARS && code.chars().count() > MAX_FORMAT_CODE_CHARS
129}
130
131impl NumberFormat {
132    /// Parses a format code. Never fails: unknown or malformed parts degrade to literal text, and
133    /// a code longer than [`MAX_FORMAT_CODE_CHARS`] characters degrades to General.
134    pub fn parse(code: &str) -> NumberFormat {
135        Self::parse_inner(code, false)
136    }
137
138    /// `parse` without the length cap, so tests can reach renderer paths a capped code cannot.
139    #[cfg(test)]
140    fn parse_uncapped(code: &str) -> NumberFormat {
141        Self::parse_inner(code, true)
142    }
143
144    fn parse_inner(code: &str, uncapped: bool) -> NumberFormat {
145        #[cfg(test)]
146        parse_cache::count_parse();
147        let trimmed = code.trim();
148        // `checkbox` marks a checkbox cell (Insert › Checkbox): TRUE/FALSE draw as a box, any other
149        // value displays as General.
150        if trimmed.is_empty() || trimmed.eq_ignore_ascii_case("general") || trimmed == "checkbox" || (!uncapped && too_long(code)) {
151            return NumberFormat {
152                inner: Arc::new(Inner { code: code.to_string(), general: true, sections: Vec::new(), num_sections: 0, text_section: None }),
153            };
154        }
155        let sections = parse::parse_sections(code);
156        let len = sections.len();
157        let last_is_text = sections.last().is_some_and(|s| s.kind == SecKind::Text);
158        let text_section = if len >= 4 {
159            Some(3)
160        } else if last_is_text {
161            Some(len - 1)
162        } else {
163            None
164        };
165        let num_sections = if len >= 4 {
166            3
167        } else if last_is_text {
168            len - 1
169        } else {
170            len
171        };
172        NumberFormat { inner: Arc::new(Inner { code: code.to_string(), general: false, sections, num_sections, text_section }) }
173    }
174
175    pub fn general() -> NumberFormat {
176        NumberFormat::parse("General")
177    }
178
179    pub fn code(&self) -> &str {
180        &self.inner.code
181    }
182
183    pub fn is_general(&self) -> bool {
184        self.inner.general
185    }
186
187    fn num_sections(&self) -> &[Section] {
188        self.inner.sections.get(..self.inner.num_sections).unwrap_or(&[])
189    }
190
191    /// True when the code contains date or time tokens.
192    pub fn is_date(&self) -> bool {
193        self.num_sections().iter().any(|s| s.kind == SecKind::Date)
194    }
195
196    /// For a date or time format, whether the section that formats `value` shows calendar parts (year, month,
197    /// day) and whether it shows time parts, as `(date, time)`. `None` for any other format and for
198    /// elapsed-time codes like `[h]:mm`, whose value is a duration, not a moment.
199    pub fn date_shape(&self, value: f64) -> Option<(bool, bool)> {
200        let s = self.select(value).map(|(s, _)| s).filter(|s| s.kind == SecKind::Date)?;
201        let parts = || s.toks.iter().filter_map(|t| if let Tok::Date(d) = t { Some(*d) } else { None });
202        if parts().any(|d| matches!(d, DatePart::ElapsedH(_) | DatePart::ElapsedM(_) | DatePart::ElapsedS(_))) {
203            return None;
204        }
205        Some((parts().any(DatePart::is_calendar), parts().any(|d| !d.is_calendar())))
206    }
207
208    pub fn is_percent(&self) -> bool {
209        self.num_sections().iter().any(|s| s.kind == SecKind::Number && s.percent > 0)
210    }
211
212    /// True for a pure text format such as `@`.
213    pub fn is_text(&self) -> bool {
214        self.inner.num_sections == 0 && self.inner.text_section.is_some()
215    }
216
217    /// Best guess at the format category for the ribbon's dropdown.
218    pub fn kind(&self) -> FormatKind {
219        if self.is_general() {
220            return FormatKind::General;
221        }
222        if self.is_text() {
223            return FormatKind::Text;
224        }
225        const SPECIAL: [&str; 4] = ["00000", "00000-0000", "[<=9999999]###-####;(###) ###-####", "000-00-0000"];
226        if SPECIAL.contains(&self.code()) {
227            return FormatKind::Special;
228        }
229        let Some(s) = self.inner.sections.first() else { return FormatKind::Custom };
230        match s.kind {
231            SecKind::General => return FormatKind::General,
232            SecKind::Text => return FormatKind::Text,
233            SecKind::Date => {
234                let calendar = s.toks.iter().any(|t| matches!(t, Tok::Date(d) if d.is_calendar()));
235                return if calendar { FormatKind::Date } else { FormatKind::Time };
236            }
237            SecKind::Number => {}
238        }
239        if s.fraction {
240            FormatKind::Fraction
241        } else if s.exp {
242            FormatKind::Scientific
243        } else if s.percent > 0 {
244            FormatKind::Percentage
245        } else if s.fill && s.has_digits {
246            FormatKind::Accounting
247        } else if s.currency && s.has_digits {
248            FormatKind::Currency
249        } else if s.has_digits {
250            FormatKind::Number
251        } else {
252            FormatKind::Custom
253        }
254    }
255
256    /// Picks the number section for `v`. Returns the section and whether the minus sign is
257    /// dropped (the default negative section shows the absolute value). `None` = General.
258    fn select(&self, v: f64) -> Option<(&Section, bool)> {
259        let secs = self.num_sections();
260        let k = secs.len();
261        let s0 = secs.first()?;
262        let c0 = s0.cond;
263        let c1 = secs.get(1).and_then(|s| s.cond);
264        if c0.is_some() || c1.is_some() {
265            let idx = if c0.is_some_and(|c| c.test(v)) {
266                0
267            } else if c1.is_some_and(|c| c.test(v)) {
268                1
269            } else if c0.is_some() && c1.is_some() {
270                2
271            } else if c0.is_none() {
272                0
273            } else {
274                1
275            };
276            // Excel never adds a minus sign in a multi-section conditional format.
277            return secs.get(idx).map(|s| (s, k >= 2));
278        }
279        if v < 0.0 && k >= 2 {
280            return secs.get(1).map(|s| (s, true));
281        }
282        if v == 0.0 && k >= 3 {
283            return secs.get(2).map(|s| (s, false));
284        }
285        Some((s0, false))
286    }
287
288    /// Formats a number. The flag is true when the value cannot be shown (date out of range).
289    fn format_number(&self, n: f64, sys: DateSystem) -> (Formatted, bool) {
290        let general = |n: f64| Formatted {
291            text: format_general_fit(n, GENERAL_WIDTH).unwrap_or_else(|| OVERFLOW.into()),
292            color: None,
293            fill: None,
294            numeric: true,
295        };
296        if self.is_general() {
297            return (general(n), false);
298        }
299        let Some((sec, drop_sign)) = self.select(n) else { return (general(n), false) };
300        let minus = n < 0.0 && !drop_sign;
301        let out: Option<Out> = match sec.kind {
302            SecKind::Text => Some(render::render_text(sec, &format_general_fit(n, GENERAL_WIDTH).unwrap_or_default())),
303            SecKind::General => {
304                let width = if minus { GENERAL_WIDTH - 1 } else { GENERAL_WIDTH };
305                Some(render::render_general(sec, n.abs(), width))
306            }
307            SecKind::Date => {
308                if minus {
309                    None
310                } else {
311                    render::render_date(sec, n.abs(), sys)
312                }
313            }
314            SecKind::Number => Some(render::render_number(sec, n.abs())),
315        };
316        let Some(mut out) = out else {
317            return (Formatted { text: OVERFLOW.into(), color: sec.color, fill: None, numeric: true }, true);
318        };
319        // Excel drops the minus sign when the first section shows a negative number as all zeros.
320        if minus && sec.kind != SecKind::Text && !out.zero {
321            out.prepend("-");
322        }
323        (Formatted { text: out.text, color: sec.color, fill: out.fill, numeric: true }, false)
324    }
325
326    /// The number `n` as this format displays it: what Excel stores when "Set precision as
327    /// displayed" is on. Fixed decimals, percent, thousands scaling (`0.0,,`), scientific and
328    /// fraction sections round to what they show; the section is picked as for display, so
329    /// `0.00;(0.0)` keeps one decimal for negatives. Observed in Excel (COM,
330    /// `Workbook.PrecisionAsDisplayed`), these keep the full value: General, date and time
331    /// formats (`mm:ss.0` included), text formats and sections without digits (`"x"`, `;;;`).
332    /// The section is picked by [`NumberFormat::rounding_section`], which ignores conditions.
333    /// Never returns `-0`.
334    pub fn round_as_displayed(&self, n: f64) -> f64 {
335        if self.is_general() || !n.is_finite() {
336            return n;
337        }
338        let Some(sec) = self.rounding_section(n) else { return n };
339        if sec.kind != SecKind::Number {
340            return n;
341        }
342        let Some(m) = render::shown_value(sec, n) else { return n };
343        if m == 0.0 {
344            0.0
345        } else if n < 0.0 {
346            -m
347        } else {
348            m
349        }
350    }
351
352    /// The section whose digits "Set precision as displayed" rounds `n` by. Observed in Excel
353    /// (COM), this differs from the section that displays `n`:
354    /// - Conditions are ignored; the sign picks the section, the first for positives and the
355    ///   second for negatives. `[<1]0.00;0` shows 2.333 as `2` but stores 2.33, and stores -10/3
356    ///   as -3 although it shows `-3.00`. `[>1]0.0;0.000` stores 1/3 as 0.3.
357    /// - A format with a condition and a single number section keeps negatives at full value
358    ///   (`[<1]0.00`, `[>=-100]0.0`); with an `@` section added (`[<1]0.00;@`) negatives use the
359    ///   first section, as every format without a condition does.
360    /// - A completely empty section (no characters, not even a colour) passes to the next one:
361    ///   `0.0;;0.000` stores -2.333 as -2.333, `;0.0` stores 2.333 as 2.3, `;;0.0` too. A section
362    ///   with only a colour or a literal (`0.0;[Red];0.000`, `0.0;" "`) has no digits and keeps
363    ///   the full value.
364    ///
365    /// `None` keeps the full value.
366    fn rounding_section(&self, n: f64) -> Option<&Section> {
367        let secs = self.num_sections();
368        let conditional = secs.iter().any(|s| s.cond.is_some());
369        let slot = if n < 0.0 && secs.len() >= 2 {
370            1
371        } else if n < 0.0 && conditional && self.inner.text_section.is_none() {
372            return None;
373        } else {
374            0
375        };
376        let empty = |s: &Section| s.toks.is_empty() && s.color.is_none() && s.cond.is_none();
377        secs.get(slot..)?.iter().find(|s| !empty(s))
378    }
379
380    fn format_text(&self, t: &str) -> Formatted {
381        match self.inner.text_section.and_then(|i| self.inner.sections.get(i)) {
382            Some(sec) => {
383                let out = render::render_text(sec, t);
384                Formatted { text: out.text, color: sec.color, fill: out.fill, numeric: false }
385            }
386            None => Formatted { text: t.to_string(), color: None, fill: None, numeric: false },
387        }
388    }
389}
390
391/// Formats a cell value for display.
392pub fn format_value(v: &Value, fmt: &NumberFormat, sys: DateSystem) -> Formatted {
393    match v {
394        Value::Empty => Formatted::default(),
395        Value::Number(n) if !n.is_finite() => Formatted { text: CellError::Num.as_str().into(), ..Formatted::default() },
396        Value::Number(n) => fmt.format_number(*n, sys).0,
397        Value::Text(t) => fmt.format_text(t),
398        Value::Bool(b) => Formatted { text: if *b { "TRUE" } else { "FALSE" }.into(), ..Formatted::default() },
399        Value::Error(e) => Formatted { text: e.as_str().into(), ..Formatted::default() },
400        Value::Array(a) => match a.data.first() {
401            Some(Value::Array(_)) | None => Formatted::default(),
402            Some(first) => format_value(first, fmt, sys),
403        },
404    }
405}
406
407/// A small per-thread cache of parsed format codes for callers that format many values with the
408/// same code (`TEXT` over a range, chart labels). A parsed format depends on the code text only
409/// (the date system and values are applied when formatting), so the code is the whole key.
410mod parse_cache {
411    use std::cell::RefCell;
412    use std::collections::HashMap;
413
414    use super::NumberFormat;
415
416    /// Most codes kept per thread. A full cache is emptied, so memory stays bounded.
417    pub(super) const CAPACITY: usize = 64;
418    /// Longer codes are parsed every time and never stored.
419    pub(super) const MAX_CODE_LEN: usize = 256;
420
421    thread_local! {
422        static CACHE: RefCell<HashMap<String, NumberFormat>> = RefCell::new(HashMap::new());
423    }
424
425    pub(super) fn get(code: &str) -> NumberFormat {
426        if code.len() > MAX_CODE_LEN {
427            return NumberFormat::parse(code);
428        }
429        // Not usable (thread shutting down, re-entered): parse without the cache.
430        let cached = CACHE
431            .try_with(|c| {
432                let mut c = c.try_borrow_mut().ok()?;
433                if let Some(f) = c.get(code) {
434                    return Some(f.clone());
435                }
436                let f = NumberFormat::parse(code);
437                if c.len() >= CAPACITY {
438                    c.clear();
439                }
440                c.insert(code.to_string(), f.clone());
441                Some(f)
442            })
443            .ok()
444            .flatten();
445        cached.unwrap_or_else(|| NumberFormat::parse(code))
446    }
447
448    #[cfg(test)]
449    thread_local! {
450        static PARSES: std::cell::Cell<usize> = const { std::cell::Cell::new(0) };
451    }
452
453    #[cfg(test)]
454    pub(super) fn count_parse() {
455        let _ = PARSES.try_with(|p| p.set(p.get() + 1));
456    }
457
458    /// Parses done on this thread so far (tests only).
459    #[cfg(test)]
460    pub(super) fn parses() -> usize {
461        PARSES.try_with(std::cell::Cell::get).unwrap_or(0)
462    }
463
464    /// Empties this thread's cache (tests only).
465    #[cfg(test)]
466    pub(super) fn clear() {
467        let _ = CACHE.try_with(|c| c.borrow_mut().clear());
468    }
469}
470
471impl NumberFormat {
472    /// Like [`NumberFormat::parse`], but remembers recently parsed codes (per thread, bounded) so
473    /// formatting many values with one code parses it once. Same result as `parse`.
474    pub fn parse_cached(code: &str) -> NumberFormat {
475        parse_cache::get(code)
476    }
477}
478
479/// The `TEXT()` worksheet function: formats a value with a format code. Numeric text is
480/// converted to a number first; other text and booleans (as `TRUE`/`FALSE`) go through the text
481/// section, or are returned as is.
482pub fn text_function(v: &Value, code: &str, sys: DateSystem) -> Result<String, CellError> {
483    if code.is_empty() {
484        return Ok(String::new());
485    }
486    if too_long(code) {
487        return Err(CellError::Value);
488    }
489    let fmt = NumberFormat::parse_cached(code);
490    let n = match v.scalar() {
491        Value::Error(e) => return Err(e),
492        Value::Empty => 0.0,
493        Value::Number(n) => n,
494        Value::Bool(b) => return Ok(fmt.format_text(if b { "TRUE" } else { "FALSE" }).text),
495        Value::Text(t) => match crate::base::parse::parse_number_text(&t) {
496            Some(n) => n,
497            None => return Ok(fmt.format_text(&t).text),
498        },
499        Value::Array(_) => return Err(CellError::Value),
500    };
501    if !n.is_finite() {
502        return Err(CellError::Num);
503    }
504    let (f, overflow) = fmt.format_number(n, sys);
505    if overflow { Err(CellError::Value) } else { Ok(f.text) }
506}
507
508#[cfg(test)]
509mod tests;
ravenrs_sheet/numfmt/mod.rs

ravenrs_sheet/numfmt/
mod.rs
