calamine/
formats.rs

1// SPDX-License-Identifier: MIT
2//
3// Copyright 2016-2025, Johann Tuffe.
4
5use crate::datatype::{Data, DataRef, ExcelDateTime, ExcelDateTimeType};
6
7#[derive(Debug, Clone, Copy, PartialEq)]
8pub enum CellFormat {
9    Other,
10    DateTime,
11    TimeDelta,
12}
13
14/// Check excel number format is datetime
15pub fn detect_custom_number_format(format: &str) -> CellFormat {
16    let mut escaped = false;
17    let mut is_quote = false;
18    let mut brackets = 0u8;
19    let mut prev = ' ';
20    let mut hms = false;
21    let mut ap = false;
22    for s in format.chars() {
23        match (s, escaped, is_quote, ap, brackets) {
24            (_, true, ..) => escaped = false, // if escaped, ignore
25            ('_' | '\\', ..) => escaped = true,
26            ('"', _, true, _, _) => is_quote = false,
27            (_, _, true, _, _) => (),
28            ('"', _, _, _, _) => is_quote = true,
29            (';', ..) => return CellFormat::Other, // first format only
30            ('[', ..) => brackets += 1,
31            (']', .., 1) if hms => return CellFormat::TimeDelta, // if closing
32            (']', ..) => brackets = brackets.saturating_sub(1),
33            ('a' | 'A', _, _, false, 0) => ap = true,
34            ('p' | 'm' | '/' | 'P' | 'M', _, _, true, 0) => return CellFormat::DateTime,
35            ('d' | 'm' | 'h' | 'y' | 's' | 'D' | 'M' | 'H' | 'Y' | 'S', _, _, false, 0) => {
36                return CellFormat::DateTime
37            }
38            _ => {
39                if hms && s.eq_ignore_ascii_case(&prev) {
40                    // ok ...
41                } else {
42                    hms = prev == '[' && matches!(s, 'm' | 'h' | 's' | 'M' | 'H' | 'S');
43                }
44            }
45        }
46        prev = s;
47    }
48    CellFormat::Other
49}
50
51pub fn builtin_format_by_id(id: &[u8]) -> CellFormat {
52    match id {
53        // mm-dd-yy
54        b"14" |
55        // d-mmm-yy
56        b"15" |
57        // d-mmm
58        b"16" |
59        // mmm-yy
60        b"17" |
61        // h:mm AM/PM
62        b"18" |
63        // h:mm:ss AM/PM
64        b"19" |
65        // h:mm
66        b"20" |
67        // h:mm:ss
68        b"21" |
69        // m/d/yy h:mm
70        b"22" |
71        // mm:ss
72        b"45" |
73        // mmss.0
74        b"47" => CellFormat::DateTime,
75        // [h]:mm:ss
76        b"46" => CellFormat::TimeDelta,
77        _ => CellFormat::Other
78    }
79}
80
81/// Check if code corresponds to builtin date format
82///
83/// See `is_builtin_date_format_id`
84pub fn builtin_format_by_code(code: u16) -> CellFormat {
85    match code {
86        14..=22 | 45 | 47 => CellFormat::DateTime,
87        46 => CellFormat::TimeDelta,
88        _ => CellFormat::Other,
89    }
90}
91
92// convert i64 to date, if format == Date
93pub fn format_excel_i64(value: i64, format: Option<&CellFormat>, is_1904: bool) -> Data {
94    match format {
95        Some(CellFormat::DateTime) => Data::DateTime(ExcelDateTime::new(
96            value as f64,
97            ExcelDateTimeType::DateTime,
98            is_1904,
99        )),
100        Some(CellFormat::TimeDelta) => Data::DateTime(ExcelDateTime::new(
101            value as f64,
102            ExcelDateTimeType::TimeDelta,
103            is_1904,
104        )),
105        _ => Data::Int(value),
106    }
107}
108
109// convert f64 to date, if format == Date
110#[inline]
111pub fn format_excel_f64_ref(
112    value: f64,
113    format: Option<&CellFormat>,
114    is_1904: bool,
115) -> DataRef<'static> {
116    match format {
117        Some(CellFormat::DateTime) => DataRef::DateTime(ExcelDateTime::new(
118            value,
119            ExcelDateTimeType::DateTime,
120            is_1904,
121        )),
122        Some(CellFormat::TimeDelta) => DataRef::DateTime(ExcelDateTime::new(
123            value,
124            ExcelDateTimeType::TimeDelta,
125            is_1904,
126        )),
127        _ => DataRef::Float(value),
128    }
129}
130
131// convert f64 to date, if format == Date
132pub fn format_excel_f64(value: f64, format: Option<&CellFormat>, is_1904: bool) -> Data {
133    format_excel_f64_ref(value, format, is_1904).into()
134}
135
136/// Ported from openpyxl, MIT License
137/// https://foss.heptapod.net/openpyxl/openpyxl/-/blob/a5e197c530aaa49814fd1d993dd776edcec35105/openpyxl/styles/tests/test_number_style.py
138#[test]
139fn test_is_date_format() {
140    assert_eq!(
141        detect_custom_number_format("DD/MM/YY"),
142        CellFormat::DateTime
143    );
144    assert_eq!(
145        detect_custom_number_format("H:MM:SS;@"),
146        CellFormat::DateTime
147    );
148    assert_eq!(
149        detect_custom_number_format("#,##0\\ [$\\u20bd-46D]"),
150        CellFormat::Other
151    );
152    assert_eq!(
153        detect_custom_number_format("m\"M\"d\"D\";@"),
154        CellFormat::DateTime
155    );
156    assert_eq!(
157        detect_custom_number_format("[h]:mm:ss"),
158        CellFormat::TimeDelta
159    );
160    assert_eq!(
161        detect_custom_number_format("\"Y: \"0.00\"m\";\"Y: \"-0.00\"m\";\"Y: <num>m\";@"),
162        CellFormat::Other
163    );
164    assert_eq!(
165        detect_custom_number_format("#,##0\\ [$''u20bd-46D]"),
166        CellFormat::Other
167    );
168    assert_eq!(
169        detect_custom_number_format("\"$\"#,##0_);[Red](\"$\"#,##0)"),
170        CellFormat::Other
171    );
172    assert_eq!(
173        detect_custom_number_format("[$-404]e\"\\xfc\"m\"\\xfc\"d\"\\xfc\""),
174        CellFormat::DateTime
175    );
176    assert_eq!(
177        detect_custom_number_format("0_ ;[Red]\\-0\\ "),
178        CellFormat::Other
179    );
180    assert_eq!(detect_custom_number_format("\\Y000000"), CellFormat::Other);
181    assert_eq!(
182        detect_custom_number_format("#,##0.0####\" YMD\""),
183        CellFormat::Other
184    );
185    assert_eq!(detect_custom_number_format("[h]"), CellFormat::TimeDelta);
186    assert_eq!(detect_custom_number_format("[ss]"), CellFormat::TimeDelta);
187    assert_eq!(
188        detect_custom_number_format("[s].000"),
189        CellFormat::TimeDelta
190    );
191    assert_eq!(detect_custom_number_format("[m]"), CellFormat::TimeDelta);
192    assert_eq!(detect_custom_number_format("[mm]"), CellFormat::TimeDelta);
193    assert_eq!(
194        detect_custom_number_format("[Blue]\\+[h]:mm;[Red]\\-[h]:mm;[Green][h]:mm"),
195        CellFormat::TimeDelta
196    );
197    assert_eq!(
198        detect_custom_number_format("[>=100][Magenta][s].00"),
199        CellFormat::TimeDelta
200    );
201    assert_eq!(
202        detect_custom_number_format("[h]:mm;[=0]\\-"),
203        CellFormat::TimeDelta
204    );
205    assert_eq!(
206        detect_custom_number_format("[>=100][Magenta].00"),
207        CellFormat::Other
208    );
209    assert_eq!(
210        detect_custom_number_format("[>=100][Magenta]General"),
211        CellFormat::Other
212    );
213    assert_eq!(
214        detect_custom_number_format("ha/p\\\\m"),
215        CellFormat::DateTime
216    );
217    assert_eq!(
218        detect_custom_number_format("#,##0.00\\ _M\"H\"_);[Red]#,##0.00\\ _M\"S\"_)"),
219        CellFormat::Other
220    );
221}
calamine/formats.rs

calamine/
formats.rs
