Oregami
Repositories/oxedyne/fe2o3

oxedyne/fe2o3/fe2o3_file/tests/xlsx.rs

15.3 KiB, 27 runs

created by r1870400018:22754, which is this file's identity for as long as the history lasts, whatever it is later renamed to

download · who wrote it · its history

1//! [Written with AI entirely](https://need2know.ai/entirely-ai/code)\
2//! Anthropic Claude
3
4use oxedyne_fe2o3_file::office::odf;
5use oxedyne_fe2o3_file::office::sheet::{
6 Book,
7 Cell,
8 MAX_TAB,
9 Range,
10 Ref,
11 Sheet,
12 Value,
13 col_name,
14 tab_names,
15};
16use oxedyne_fe2o3_file::office::xlsx;
17
18use oxedyne_fe2o3_text::doc::markdown;
19
20use oxedyne_fe2o3_core::{
21 prelude::*,
22 test::test_it,
23};
24
25// A `.xlsx` LibreOffice wrote, holding one of everything that separates this format from a table:
26// shared strings, a formula with the value the last calculation left beside it, a date stored as a
27// serial under a CUSTOM number format, a boolean, an entirely absent row, and a row whose cells skip
28// two columns.
29//
30// Its content is ours -- LibreOffice was handed a spreadsheet this crate wrote and asked to save its
31// own -- so the intent is known and every byte of the encoding is somebody else's. Reading back our
32// own output would prove that the writer and the reader share their assumptions, which is precisely
33// the thing worth doubting in a format this convention-bound.
34const FOREIGN: &[u8] = include_bytes!("data/foreign.xlsx");
35
36fn book() -> Book {
37 let mut s = Sheet::new("Sales");
38 s.rows.push(vec![Cell::text("Region"), Cell::text("Units"), Cell::text("Total")]);
39 s.rows.push(vec![
40 Cell::text("North"),
41 Cell::number(120.0),
42 Cell::formula("B2*3.4", Value::Number(408.0)),
43 ]);
44 s.rows.push(Vec::new());
45 s.rows.push(vec![Cell {
46 value: Value::Date("2026-03-14".to_string()),
47 formula: None,
48 }]);
49 Book { sheets: vec![s] }
50}
51
52// Four headings that reduce to TWO tab names. `Q1/Q2` and `Q1:Q2` both lose their middle character to
53// the set Excel refuses, and the two long ones agree for the first 31 characters.
54const CLASH: &str = "\
55## Q1/Q2
56
57| A | B |
58| --- | --- |
59| 1 | 2 |
60
61## Q1:Q2
62
63| A | B |
64| --- | --- |
65| 3 | 4 |
66
67## A very long heading that runs past the thirty-one character limit, one
68
69| A | B |
70| --- | --- |
71| 5 | 6 |
72
73## A very long heading that runs past the thirty-one character limit, two
74
75| A | B |
76| --- | --- |
77| 7 | 8 |
78";
79
80pub fn test_xlsx(filter: &'static str) -> Outcome<()> {
81
82 res!(test_it(filter, &["A column is named the way a person writes it 000", "all", "xlsx"], || {
83 // The boundaries are where this goes wrong, and it goes wrong silently: an off-by-one puts
84 // every value one column out and the sheet still looks like a sheet.
85 assert_eq!(col_name(0), "A");
86 assert_eq!(col_name(25), "Z");
87 assert_eq!(col_name(26), "AA");
88 assert_eq!(col_name(51), "AZ");
89 assert_eq!(col_name(52), "BA");
90 assert_eq!(col_name(701), "ZZ");
91 assert_eq!(col_name(702), "AAA");
92 assert_eq!(col_name(16_383), "XFD", "the last column a sheet has");
93 // And it round-trips, which is the property that actually matters.
94 for i in [0u32, 1, 25, 26, 27, 700, 701, 702, 16_383] {
95 let r = Ref { col: i, row: 0 };
96 assert_eq!(res!(Ref::parse(&r.name())).col, i, "{} did not round trip", r.name());
97 }
98 Ok(())
99 }));
100
101 res!(test_it(filter, &["A reference is read as a person types it 001", "all", "xlsx"], || {
102 assert_eq!(res!(Ref::parse("A1")), Ref { col: 0, row: 0 });
103 assert_eq!(res!(Ref::parse("D20")), Ref { col: 3, row: 19 });
104 // A `$` is what a person copies out of a formula bar, and it names the same cell.
105 assert_eq!(res!(Ref::parse("$B$4")), Ref { col: 1, row: 3 });
106 assert_eq!(res!(Ref::parse("bc42")), res!(Ref::parse("BC42")));
107 // A range given from its far corner is the rectangle somebody dragged, not an error.
108 assert_eq!(res!(Range::parse("D20:A1")), res!(Range::parse("A1:D20")));
109 assert_eq!(res!(Range::parse("B2")).cells(), 1);
110 assert_eq!(res!(Range::parse("A1:D20")).cells(), 80);
111 assert_eq!(res!(Range::parse("A1:D20")).name(), "A1:D20");
112 for bad in ["", "1", "A", "A0", "$", "A1:", "ZZZZZ1", "A1048577"] {
113 assert!(Ref::parse(bad).is_err() || Range::parse(bad).is_err(),
114 "{:?} was accepted", bad);
115 }
116 Ok(())
117 }));
118
119 res!(test_it(filter, &["A foreign workbook reads back as what it says 002", "all", "xlsx"], || {
120 let r = res!(xlsx::read(FOREIGN));
121 assert_eq!(r.book.names(), vec!["Sales", "Notes"]);
122 let s = res!(r.book.sheet("Sales").ok_or_else(|| err!("no Sales sheet"; Missing)));
123
124 // A shared string is an INDEX and not a number. A reader that took the `<v>` at face value
125 // returns a row of small integers where the column names were.
126 assert_eq!(s.at(&res!(Ref::parse("A1"))).value, Value::Text("Region".to_string()));
127 assert_eq!(s.at(&res!(Ref::parse("A2"))).value, Value::Text("North".to_string()));
128
129 // A formula, and the value the last calculation left. BOTH, and the value is never
130 // recomputed -- see `office::sheet` for why that is the correct answer and not a shortcut.
131 let total = s.at(&res!(Ref::parse("D2")));
132 assert_eq!(total.formula.as_deref(), Some("B2*C2"));
133 assert_eq!(total.value, Value::Number(408.0), "the STORED value, not a fresh one");
134 let sum = s.at(&res!(Ref::parse("D5")));
135 assert_eq!(sum.formula.as_deref(), Some("SUM(D2:D3)"));
136 assert_eq!(sum.value, Value::Number(1343.0));
137
138 // A date is a number, and the ONLY thing that makes it a date is the number format its style
139 // points at -- here a custom one, `m/d/yyyy`, which a reader checking only the built-in
140 // format ids would miss. Serial 46095 is 14 March 2026, and that arithmetic is checked
141 // against LibreOffice's rather than against itself.
142 assert_eq!(s.at(&res!(Ref::parse("E2"))).value, Value::Date("2026-03-14".to_string()));
143 assert_eq!(s.at(&res!(Ref::parse("E3"))).value, Value::Date("2026-04-01".to_string()));
144 // And a number under the OTHER custom format in the same file, `General`, is still a number.
145 assert_eq!(s.at(&res!(Ref::parse("B2"))).value, Value::Number(120.0));
146
147 assert_eq!(s.at(&res!(Ref::parse("F2"))).value, Value::Bool(true));
148 assert_eq!(s.at(&res!(Ref::parse("F3"))).value, Value::Bool(false));
149
150 // The gap. Row 4 is absent from the file entirely and row 5 skips from A to D, so a reader
151 // that pushed cells onto the end of a row would put the total in column B.
152 assert!(s.at(&res!(Ref::parse("A4"))).is_empty(), "the absent row is absent");
153 assert_eq!(s.at(&res!(Ref::parse("A5"))).value, Value::Text("Total".to_string()));
154 assert!(s.at(&res!(Ref::parse("B5"))).is_empty(), "and the skipped columns are empty");
155 assert!(s.at(&res!(Ref::parse("C5"))).is_empty());
156
157 // The second sheet, and the two strings in it that are traps of their own.
158 let n = res!(r.book.sheet("Notes").ok_or_else(|| err!("no Notes sheet"; Missing)));
159 assert_eq!(n.at(&res!(Ref::parse("A1"))).value, Value::Text(" ".to_string()),
160 "a string that is only spaces is still a string");
161 assert_eq!(n.at(&res!(Ref::parse("B1"))).value, Value::Text("a < b & c".to_string()));
162 assert_eq!(n.at(&res!(Ref::parse("C1"))).value, Value::Text("3.40".to_string()),
163 "text that looks like a number is text, and keeps its trailing zero");
164
165 assert!(!r.macros);
166 assert!(r.missing.is_empty());
167 assert!(r.formulas >= 3, "the formulas are counted: {}", r.formulas);
168 Ok(())
169 }));
170
171 res!(test_it(filter, &["A window of a sheet is the window asked for 003", "all", "xlsx"], || {
172 // What the range-reading tool spends. A spreadsheet's useful unit is a rectangle, not a file:
173 // the whole of `xl/worksheets/sheet1.xml` for a real workbook is megabytes and says nothing a
174 // reader wanted.
175 let r = res!(xlsx::read(FOREIGN));
176 let s = res!(r.book.sheet("Sales").ok_or_else(|| err!("no Sales sheet"; Missing)));
177 let win = s.window(&res!(Range::parse("A1:C2")));
178 assert_eq!(win.len(), 2);
179 assert_eq!(win[0].len(), 3);
180 assert_eq!(win[0][0].value.show(), "Region");
181 assert_eq!(win[1][1].value.show(), "120", "a whole number shows without a trailing .0");
182 assert_eq!(win[1][2].value.show(), "3.4");
183 // Past the end is empty rather than an error: a person asking for A1:Z100 of a small sheet
184 // wants the small sheet, not a refusal.
185 let past = s.window(&res!(Range::parse("Y1:Z2")));
186 assert_eq!(past.len(), 2);
187 assert!(past.iter().all(|row| row.iter().all(|c| c.is_empty())));
188 Ok(())
189 }));
190
191 res!(test_it(filter, &["A number shows as the file says it 004", "all", "xlsx"], || {
192 // A spreadsheet showing 12.0 where the file says 12 has changed what the file says, and a
193 // spreadsheet showing 0.30000000000000004 has changed it in the other direction.
194 assert_eq!(Value::Number(12.0).show(), "12");
195 assert_eq!(Value::Number(3.4).show(), "3.4");
196 assert_eq!(Value::Number(0.1 + 0.2).show(), "0.3");
197 assert_eq!(Value::Number(-0.0).show(), "0");
198 assert_eq!(Value::Number(1e15).show(), "1000000000000000");
199 assert_eq!(Value::Number(1.0 / 3.0).show(), "0.333333333333333");
200 assert_eq!(Value::Bool(true).show(), "TRUE");
201 assert_eq!(Value::Empty.show(), "");
202 assert_eq!(Value::Error("#DIV/0!".to_string()).show(), "#DIV/0!");
203 Ok(())
204 }));
205
206 res!(test_it(filter, &["A written workbook reads back as what was put in 005", "all", "xlsx"], || {
207 // Weaker evidence than 002 and worth having for a different reason: it covers what the
208 // foreign fixture cannot reach, and it is the pair the range tool actually runs on.
209 let b = book();
210 let bytes = res!(xlsx::write(&b));
211 let r = res!(xlsx::read(&bytes));
212 let s = res!(r.book.sheet("Sales").ok_or_else(|| err!("no Sales sheet"; Missing)));
213 assert_eq!(s.at(&res!(Ref::parse("A1"))).value, Value::Text("Region".to_string()));
214 assert_eq!(s.at(&res!(Ref::parse("B2"))).value, Value::Number(120.0));
215 let f = s.at(&res!(Ref::parse("C2")));
216 assert_eq!(f.formula.as_deref(), Some("B2*3.4"));
217 assert_eq!(f.value, Value::Number(408.0));
218 assert_eq!(s.at(&res!(Ref::parse("A4"))).value, Value::Date("2026-03-14".to_string()),
219 "a date survives the trip through a serial number and back");
220 // Written twice, the same bytes: nothing here comes from the clock.
221 assert_eq!(bytes, res!(xlsx::write(&b)));
222 // And the archive round-trips, which is what an edit will later rest on.
223 let zip = res!(oxedyne_fe2o3_file::zip::Zip::read(bytes.clone()));
224 assert_eq!(res!(zip.write()), bytes);
225 Ok(())
226 }));
227
228 res!(test_it(filter, &["A workbook that cannot be read is named 006", "all", "xlsx"], || {
229 // An OLE compound file is either an encrypted workbook or a `.xls` from before 2007, which is
230 // a different format entirely. Both are refused by name rather than read as rubble.
231 let ole = [0xD0u8, 0xCF, 0x11, 0xE0, 0xA1, 0xB1, 0x1A, 0xE1, 0, 0, 0, 0, 0, 0, 0, 0];
232 let e = xlsx::read(&ole);
233 assert!(e.is_err());
234 match e {
235 Err(e) => {
236 let s = fmt!("{}", e);
237 assert!(s.contains("OLE compound file"), "{}", s);
238 }
239 Ok(_) => panic!("an OLE file was read"),
240 }
241 assert!(xlsx::read(b"not a workbook").is_err());
242 // A ZIP that is not a workbook says so rather than coming back empty.
243 let mut zip = oxedyne_fe2o3_file::zip::Zip::new();
244 zip.set("hello.txt", b"hi".to_vec(), oxedyne_fe2o3_file::zip::Method::Store);
245 assert!(xlsx::read(&res!(zip.write())).is_err());
246 Ok(())
247 }));
248
249 res!(test_it(filter, &["A sheet the workbook names and cannot supply is said 007", "all", "xlsx"], || {
250 // A workbook that quietly came back with one of its two sheets is worse than one that says
251 // which is absent, because nothing on screen would say a sheet was ever there.
252 let b = book();
253 let bytes = res!(xlsx::write(&b));
254 let mut zip = res!(oxedyne_fe2o3_file::zip::Zip::read(bytes));
255 assert!(zip.remove("xl/worksheets/sheet1.xml"));
256 let r = res!(xlsx::read(&res!(zip.write())));
257 assert_eq!(r.missing, vec!["Sales".to_string()]);
258 assert!(r.book.sheets.is_empty());
259 Ok(())
260 }));
261
262 res!(test_it(filter, &["Two headings that reduce to one tab name are told apart 008", "all", "xlsx"], || {
263 // Excel refuses a workbook with two tabs of one name, and refuses the FILE rather than the
264 // name. LibreOffice and openpyxl each silently RENAME instead, so a reader-based oracle
265 // repairs this defect rather than reporting it and the user simply does not get the tab they
266 // asked for.
267 let doc = res!(markdown::parse(CLASH));
268 let book = Book::from_doc(&doc);
269 assert_eq!(book.sheets.len(), 4, "four tables, four sheets");
270 // The MODEL keeps the heading whole. Truncating there would throw away the characters that
271 // tell the two long headings apart before anything got the chance to use them.
272 assert!(book.sheets[2].name.ends_with("one"), "{}", book.sheets[2].name);
273 assert!(book.sheets[3].name.ends_with("two"), "{}", book.sheets[3].name);
274
275 let tabs = tab_names(&book);
276 // Legal, short enough, and no two alike -- the three things Excel refuses a file over.
277 for t in &tabs {
278 assert!(t.chars().count() <= MAX_TAB, "'{}' is {} characters", t, t.chars().count());
279 assert!(!t.contains([':', '\\', '/', '?', '*', '[', ']']), "'{}' holds a refused character", t);
280 }
281 for (i, t) in tabs.iter().enumerate() {
282 assert!(!tabs[..i].iter().any(|p| p.eq_ignore_ascii_case(t)),
283 "'{}' is used twice, in {:?}", t, tabs);
284 }
285 // And the disambiguation is one a person would recognise, which is Excel's own.
286 assert_eq!(tabs[0], "Q1 Q2");
287 assert_eq!(tabs[1], "Q1 Q2 (2)");
288 assert!(tabs[3].ends_with(" (2)"), "the second long name is not marked: {}", tabs[3]);
289
290 // The names the WRITERS actually put in the file, which is the thing a reader sees. Asserting
291 // on `tab_names` alone would pass even if neither writer called it.
292 let bytes = res!(xlsx::write(&book));
293 let read = res!(xlsx::read(&bytes));
294 assert_eq!(read.book.names(), tabs, "the .xlsx does not carry the names that were settled on");
295 // The `.ods` writer had no deduplication at all, and OpenDocument requires distinct table
296 // names too. It takes the same names so one sheet answers to one name in either format.
297 let ods = res!(odf::sheet::write(&book));
298 let back = res!(odf::sheet::read(&ods));
299 assert_eq!(back.book.names(), tabs, "the .ods does not carry the same names as the .xlsx");
300 Ok(())
301 }));
302
303 res!(test_it(filter, &["The styles part carries every table Excel insists on 009", "all", "xlsx"], || {
304 // Excel is stricter than the schema here and its answer to a missing table is a REPAIR PROMPT,
305 // which "fixes" the file and never names the reason. `cellStyles` was the one missing: it holds
306 // the named style a cell wears when nobody has styled it, the schema makes it optional, and
307 // Excel writes it in every workbook it saves. openpyxl warned "Workbook contains no default
308 // style" over ours and not over LibreOffice's.
309 let bytes = res!(xlsx::write(&book()));
310 let zip = res!(oxedyne_fe2o3_file::zip::Zip::read(bytes));
311 let styles = res!(zip.text("xl/styles.xml"));
312 assert!(styles.contains("<cellStyle name=\"Normal\" xfId=\"0\" builtinId=\"0\"/>"),
313 "no default cell style: {}", styles);
314
315 // In CT_Stylesheet's own order, because the sequence is not a suggestion: a `cellStyles` before
316 // `cellXfs` is invalid however right its content is.
317 const ORDER: [&str; 6] = ["<fonts", "<fills", "<borders", "<cellStyleXfs", "<cellXfs",
318 "<cellStyles"];
319 let mut last = 0usize;
320 for tag in ORDER {
321 let at = res!(styles.find(tag).ok_or_else(|| err!(
322 "xl/styles.xml has no {} element at all.", tag; Test, Missing)));
323 assert!(at > last, "{} comes before the element it must follow", tag);
324 last = at;
325 }
326 // The fill table's first two entries, in this order, whether or not anything uses either.
327 let none_at = res!(styles.find("patternType=\"none\"").ok_or_else(|| err!(
328 "the fill table has no `none` entry"; Test, Missing)));
329 let gray_at = res!(styles.find("patternType=\"gray125\"").ok_or_else(|| err!(
330 "the fill table has no `gray125` entry"; Test, Missing)));
331 assert!(none_at < gray_at, "the two required fills are the wrong way round");
332 Ok(())
333 }));
334
335 Ok(())
336}