datui_lib/
excel.rs

1//! Excel workbooks: the sheets of one, listed on the home screen and summed up on the
2//! Info panel.
3//!
4//! A sheet is read whole by calamine when it is opened (see
5//! `DataTableState::from_excel`). What is said of the others comes from what that open
6//! read anyway, or from the start of each sheet: an `.xlsx` sheet declares its range
7//! (`<dimension ref="A1:D100"/>`) before its cells, so its size costs no read of them.
8//!
9//! The home screen lists an `.xlsx` or `.xlsm` workbook's sheets from its directory and
10//! `xl/workbook.xml`, a few KB however large the workbook. An `.xls` or `.xlsb` file
11//! keeps its sheet names in a binary stream that only a read of the workbook finds, so
12//! it opens its first sheet and `--table` names another.
13
14use std::io::Read;
15use std::path::Path;
16
17use calamine::{Data, Dimensions, Range, Reader, Sheets};
18use color_eyre::Result;
19
20use crate::FileFormat;
21use crate::model_files::MetaValue;
22use crate::sqlite::Table;
23use crate::text_formats::{Detail, count};
24
25/// The workbook part of an `.xlsx` or `.xlsm` file, and its relationships.
26const WORKBOOK: &str = "xl/workbook.xml";
27const WORKBOOK_RELS: &str = "xl/_rels/workbook.xml.rels";
28/// The most of either part read: a workbook of thousands of sheets is still far less.
29const MAX_PART: u64 = 4 << 20;
30
31/// Whether `head`, the first bytes of `file`, begin a workbook whose sheets can be listed
32/// without reading it: a zip file holding `xl/workbook.xml`.
33pub fn is_listable(head: &[u8], file: Option<&Path>) -> bool {
34    head.starts_with(b"PK\x03\x04")
35        && file.is_some_and(|file| {
36            std::fs::File::open(file)
37                .ok()
38                .and_then(|f| zip::ZipArchive::new(f).ok())
39                .is_some_and(|zip| zip.index_for_name(WORKBOOK).is_some())
40        })
41}
42
43/// The sheets of the workbook at `path`, in the workbook's order, for the home screen
44/// and `--table`. A hidden sheet is the workbook's own, listed after Ctrl+A; a chart
45/// sheet holds no cells and is left out.
46pub fn sheets(path: &Path) -> Result<Vec<Table>> {
47    let mut zip = zip::ZipArchive::new(std::fs::File::open(path)?)?;
48    let workbook = part(&mut zip, WORKBOOK)?;
49    let rels = part(&mut zip, WORKBOOK_RELS).unwrap_or_default();
50    let charts: Vec<String> = tags(&rels, "Relationship")
51        .into_iter()
52        .filter(|attrs| attr(attrs, "Type").is_some_and(|t| t.ends_with("/chartsheet")))
53        .filter_map(|attrs| attr(&attrs, "Id"))
54        .collect();
55    Ok(tags(&workbook, "sheet")
56        .into_iter()
57        .filter(|attrs| attr(attrs, "id").is_none_or(|id| !charts.contains(&id)))
58        .filter_map(|attrs| {
59            Some(Table {
60                name: attr(&attrs, "name")?,
61                kind: "worksheet".to_string(),
62                internal: attr(&attrs, "state").is_some_and(|s| s != "visible"),
63                columns: Vec::new(),
64            })
65        })
66        .collect())
67}
68
69/// One part of a zip file as text.
70fn part(zip: &mut zip::ZipArchive<std::fs::File>, name: &str) -> Result<String> {
71    let mut text = String::new();
72    zip.by_name(name)?
73        .take(MAX_PART)
74        .read_to_string(&mut text)?;
75    Ok(text)
76}
77
78/// The attributes of each element named `name` (with or without a namespace prefix) in
79/// `xml`, as written: `<sheet name="Sales" sheetId="1" r:id="rId1"/>`.
80fn tags<'a>(xml: &'a str, name: &str) -> Vec<Vec<(&'a str, String)>> {
81    let mut found = Vec::new();
82    let mut rest = xml;
83    while let Some(at) = rest.find('<') {
84        rest = &rest[at + 1..];
85        let end = rest.find('>').unwrap_or(rest.len());
86        let tag = &rest[..end];
87        rest = &rest[end..];
88        let element = tag.split(|c: char| c.is_whitespace() || c == '/').next();
89        let local = element.map(|e| e.rsplit(':').next().unwrap_or(e));
90        if local == Some(name) {
91            found.push(attributes(&tag[element.map_or(0, str::len)..]));
92        }
93    }
94    found
95}
96
97/// The attributes of a tag, after its name, with entities resolved. A name keeps no
98/// prefix: `r:id` is `id`.
99fn attributes(text: &str) -> Vec<(&str, String)> {
100    let mut attrs = Vec::new();
101    let mut rest = text;
102    while let Some(eq) = rest.find('=') {
103        let key = rest[..eq].trim().trim_start_matches('/');
104        let key = key.rsplit(':').next().unwrap_or(key);
105        let value = rest[eq + 1..].trim_start();
106        let Some(quote) = value.chars().next().filter(|q| *q == '"' || *q == '\'') else {
107            break;
108        };
109        let Some(close) = value[1..].find(quote) else {
110            break;
111        };
112        attrs.push((key, unescape(&value[1..1 + close])));
113        rest = &value[close + 2..];
114    }
115    attrs
116}
117
118fn attr(attrs: &[(&str, String)], key: &str) -> Option<String> {
119    attrs
120        .iter()
121        .find(|(k, _)| *k == key)
122        .map(|(_, v)| v.clone())
123}
124
125/// XML's five entities and character references.
126fn unescape(text: &str) -> String {
127    if !text.contains('&') {
128        return text.to_string();
129    }
130    let mut out = String::with_capacity(text.len());
131    let mut rest = text;
132    while let Some(at) = rest.find('&') {
133        out.push_str(&rest[..at]);
134        rest = &rest[at..];
135        let Some(end) = rest.find(';') else {
136            break;
137        };
138        let entity = &rest[1..end];
139        let ch = match entity {
140            "amp" => Some('&'),
141            "lt" => Some('<'),
142            "gt" => Some('>'),
143            "quot" => Some('"'),
144            "apos" => Some('\''),
145            _ => entity
146                .strip_prefix("#x")
147                .map(|hex| u32::from_str_radix(hex, 16))
148                .or_else(|| entity.strip_prefix('#').map(str::parse))
149                .and_then(|n| n.ok())
150                .and_then(char::from_u32),
151        };
152        match ch {
153            Some(ch) => {
154                out.push(ch);
155                rest = &rest[end + 1..];
156            }
157            None => {
158                out.push('&');
159                rest = &rest[1..];
160            }
161        }
162    }
163    out.push_str(rest);
164    out
165}
166
167/// The Excel tab of the Info panel for a workbook whose sheet `opened` was read as
168/// `range`: each sheet's range, the opened one's from its cells and the others' as
169/// their files declare it or as the open already parsed them.
170pub fn detail<RS: std::io::Read + std::io::Seek>(
171    workbook: &mut Sheets<RS>,
172    opened: &str,
173    range: &Range<Data>,
174) -> Detail {
175    let meta: Vec<calamine::Sheet> = workbook.sheets_metadata().to_vec();
176    let mut list = Vec::with_capacity(meta.len());
177    let mut tables = Vec::new();
178    let mut hidden = 0u64;
179    let mut charts = 0u64;
180    for sheet in &meta {
181        let mut said = Vec::new();
182        let other = match sheet.typ {
183            calamine::SheetType::WorkSheet => None,
184            calamine::SheetType::ChartSheet => Some("chart sheet"),
185            calamine::SheetType::DialogSheet => Some("dialog sheet"),
186            calamine::SheetType::MacroSheet => Some("macro sheet"),
187            calamine::SheetType::Vba => Some("VBA module"),
188        };
189        if other.is_none() {
190            tables.push(sheet.name.clone());
191        }
192        if let Some(other) = other {
193            charts += 1;
194            said.push(other.to_string());
195        } else if sheet.name == opened {
196            said.push(size(range.start().zip(range.end())));
197            said.push("opened".to_string());
198        } else if let Some(dims) =
199            // A sheet that declares no range reads as the default one.
200            declared(workbook, &sheet.name).filter(|d| *d != Dimensions::default())
201        {
202            said.push(size(Some((dims.start, dims.end))));
203        }
204        if sheet.visible != calamine::SheetVisible::Visible {
205            hidden += 1;
206            said.push("hidden".to_string());
207        }
208        list.push((sheet.name.clone(), MetaValue::Text(said.join(", "))));
209    }
210    let mut first = count(meta.len() as u64, "worksheet", "worksheets");
211    let middot = crate::glyphs::get().middot;
212    if hidden > 0 {
213        first.push_str(&format!(" {middot} {hidden} hidden"));
214    }
215    if charts > 0 {
216        first.push_str(&format!(
217            " {middot} {}",
218            count(charts, "without cells", "without cells")
219        ));
220    }
221    Detail {
222        tab: crate::text_formats::tab(FileFormat::Excel),
223        lines: vec![first, format!("Opened: {opened}")],
224        list_title: "Worksheets",
225        list,
226        tables,
227        table: Some(opened.to_string()),
228        ..Default::default()
229    }
230}
231
232/// The range a sheet other than the one opened says it covers, read no further than
233/// its cells: an `.xlsx` or `.xlsb` sheet's declared range, or an `.xls` or `.ods`
234/// sheet as the open already parsed it.
235fn declared<RS: std::io::Read + std::io::Seek>(
236    workbook: &mut Sheets<RS>,
237    name: &str,
238) -> Option<Dimensions> {
239    match workbook {
240        Sheets::Xlsx(xlsx) => xlsx
241            .worksheet_cells_reader(name)
242            .ok()
243            .map(|r| r.dimensions()),
244        Sheets::Xlsb(xlsb) => xlsb
245            .worksheet_cells_reader(name)
246            .ok()
247            .map(|r| r.dimensions()),
248        Sheets::Xls(_) | Sheets::Ods(_) => {
249            let range = workbook.worksheet_range(name).ok()?;
250            let (start, end) = range.start().zip(range.end())?;
251            Some(Dimensions { start, end })
252        }
253    }
254}
255
256/// A range as a sheet names it, and its size: `A1:D100, 100 × 4`. Empty: `empty`.
257fn size(range: Option<((u32, u32), (u32, u32))>) -> String {
258    let Some(((r0, c0), (r1, c1))) = range.filter(|((r0, c0), (r1, c1))| r1 >= r0 && c1 >= c0)
259    else {
260        return "empty".to_string();
261    };
262    let times = crate::glyphs::get().times;
263    format!(
264        "{}{}:{}{}, {} {times} {}",
265        column(c0),
266        r0 + 1,
267        column(c1),
268        r1 + 1,
269        crate::numfmt::group_chrome((r1 - r0 + 1) as usize),
270        crate::numfmt::group_chrome((c1 - c0 + 1) as usize)
271    )
272}
273
274/// A 0-based column as a sheet letters it: 0 is `A`, 26 is `AA`.
275fn column(mut c: u32) -> String {
276    let mut letters = Vec::new();
277    loop {
278        letters.push(b'A' + (c % 26) as u8);
279        if c < 26 {
280            break;
281        }
282        c = c / 26 - 1;
283    }
284    letters.reverse();
285    String::from_utf8(letters).unwrap_or_default()
286}
287
288#[cfg(test)]
289mod tests {
290    use super::*;
291
292    #[test]
293    fn columns_are_lettered_as_a_sheet_letters_them() {
294        for (c, letters) in [
295            (0, "A"),
296            (3, "D"),
297            (25, "Z"),
298            (26, "AA"),
299            (701, "ZZ"),
300            (702, "AAA"),
301        ] {
302            assert_eq!(column(c), letters, "{c}");
303        }
304        assert_eq!(
305            size(Some(((0, 0), (99, 3)))),
306            "A1:D100, 100 × 4".replace('×', crate::glyphs::get().times)
307        );
308        assert_eq!(size(None), "empty");
309    }
310
311    #[test]
312    fn sheets_are_read_from_the_workbook_part() {
313        let workbook = r#"<?xml version="1.0"?>
314<workbook xmlns:r="x"><sheets>
315<sheet name="Sales &amp; Costs" sheetId="1" r:id="rId1"/>
316<sheet name='2023' sheetId="2" r:id="rId2" state="hidden"/>
317<x:sheet name="Chart" sheetId="3" r:id="rId3"/>
318</sheets></workbook>"#;
319        let rels = r#"<Relationships>
320<Relationship Id="rId1" Type="http://x/worksheet" Target="worksheets/sheet1.xml"/>
321<Relationship Id="rId2" Type="http://x/worksheet" Target="worksheets/sheet2.xml"/>
322<Relationship Id="rId3" Type="http://x/chartsheet" Target="chartsheets/sheet1.xml"/>
323</Relationships>"#;
324        let dir = tempfile::tempdir().unwrap();
325        let path = dir.path().join("book.xlsx");
326        let mut zip = zip::ZipWriter::new(std::fs::File::create(&path).unwrap());
327        for (name, text) in [(WORKBOOK, workbook), (WORKBOOK_RELS, rels)] {
328            zip.start_file(name, zip::write::SimpleFileOptions::default())
329                .unwrap();
330            std::io::Write::write_all(&mut zip, text.as_bytes()).unwrap();
331        }
332        zip.finish().unwrap();
333        let head = std::fs::read(&path).unwrap();
334        assert!(is_listable(&head, Some(&path)));
335        let sheets = sheets(&path).unwrap();
336        let named: Vec<(&str, bool)> = sheets
337            .iter()
338            .map(|t| (t.name.as_str(), t.internal))
339            .collect();
340        assert_eq!(named, [("Sales & Costs", false), ("2023", true)]);
341    }
342
343    #[test]
344    fn entities_resolve() {
345        assert_eq!(unescape("a&lt;b&gt;&#65;&#x42;&bogus;"), "a<b>AB&bogus;");
346    }
347}
datui_lib/excel.rs

datui_lib/
excel.rs
