Oregami
Repositories/oxedyne/fe2o3

oxedyne/fe2o3/fe2o3_file/src/office/xlsx/write.rs

12.6 KiB, 19 runs

created by r1870400018:22748, 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//! Creating a `.xlsx` from the neutral spreadsheet.
2//!
3//! Six parts, and every byte of them is written here. A cell's text goes into the shared string table
4//! rather than into the cell, because that is the shape every other writer produces and the shape a
5//! reader is therefore obliged to handle -- writing the easier `inlineStr` everywhere would leave the
6//! reader's shared-string path exercised only by files this project did not write.
7//!
8//! # Excel is stricter than the schema about `styles.xml`
9//!
10//! It wants a fill table whose first two entries are `none` and `gray125`, in that order, and it
11//! wants at least one font, one border and one `cellStyleXfs` entry, whether or not anything uses
12//! them. A file omitting any of them opens with a repair prompt rather than an error, which is the
13//! worst kind of failure to debug: the file is "fixed" and the reason is never named. So they are all
14//! written.
15//!
16//! `<cellStyles>` belongs to that list and was missing from it. The schema makes the element optional
17//! and Excel writes `<cellStyle name="Normal" xfId="0" builtinId="0"/>` in every workbook it saves --
18//! it is the named style a cell has when nobody has styled it. openpyxl says "Workbook contains no
19//! default style" over every workbook this wrote and says nothing over LibreOffice's, which is how the
20//! omission was pinned on us rather than on the reader.
21//!
22//! [Written with AI entirely](https://need2know.ai/entirely-ai/code)\
23//! Anthropic Claude
24
25use crate::office::opc::{
26 CT_SHEET,
27 CT_SHEET_STYLES,
28 CT_STRINGS,
29 CT_WORKBOOK,
30 NS_R,
31 REL_DOC,
32 REL_SHEET,
33 REL_STRINGS,
34 REL_STYLES,
35 Rels,
36 Types,
37};
38use crate::office::sheet::{
39 Book,
40 Cell,
41 Ref,
42 Value,
43 tab_names,
44};
45use crate::office::xlsx::NS_S;
46use crate::zip::{
47 Method,
48 Zip,
49};
50
51use oxedyne_fe2o3_core::prelude::*;
52use oxedyne_fe2o3_text::xml::write::Out;
53
54use std::collections::BTreeMap;
55
56/// The style index a date cell wears. See [`styles`].
57///
58/// One fact written in two places -- here, and the order of the entries in `cellXfs` -- so the two
59/// must move together. There is no third place.
60const STYLE_DATE: &str = "1";
61
62/// A book with no sheets gets one empty sheet, because a workbook with none is a file every reader
63/// refuses, and refusing to write one here would be refusing at the wrong end.
64pub fn write(book: &Book) -> Outcome<Vec<u8>> {
65 let mut owned;
66 let book = match book.sheets.is_empty() {
67 false => book,
68 true => {
69 owned = Book::new();
70 owned.sheets.push(crate::office::sheet::Sheet::new("Sheet1"));
71 &owned
72 }
73 };
74
75 // Every distinct string in the book, once, in the order first seen. The order is what the cells
76 // refer to by index, so it has to be settled before a single cell is written.
77 let mut strings: Vec<&str> = Vec::new();
78 let mut seen: BTreeMap<&str, usize> = BTreeMap::new();
79 for s in &book.sheets {
80 for row in &s.rows {
81 for cell in row {
82 if let Value::Text(t) = &cell.value {
83 if !seen.contains_key(t.as_str()) {
84 seen.insert(t.as_str(), strings.len());
85 strings.push(t.as_str());
86 }
87 }
88 }
89 }
90 }
91
92 let mut zip = Zip::new();
93 let mut types = Types::new();
94 types.over("/xl/workbook.xml", CT_WORKBOOK);
95 types.over("/xl/styles.xml", CT_SHEET_STYLES);
96 types.over("/xl/sharedStrings.xml", CT_STRINGS);
97
98 let mut root = Rels::new();
99 let _ = root.add(REL_DOC, "xl/workbook.xml");
100
101 // The workbook's own relationships, and the ids the workbook part names its sheets by.
102 let mut wb_rels = Rels::new();
103 let mut ids = Vec::with_capacity(book.sheets.len());
104 for i in 0..book.sheets.len() {
105 ids.push(wb_rels.add(REL_SHEET, &fmt!("worksheets/sheet{}.xml", i + 1)));
106 types.over(&fmt!("/xl/worksheets/sheet{}.xml", i + 1), CT_SHEET);
107 }
108 let _ = wb_rels.add(REL_STYLES, "styles.xml");
109 let _ = wb_rels.add(REL_STRINGS, "sharedStrings.xml");
110
111 let mut wb = Out::declared();
112 wb.open("workbook", &[("xmlns", NS_S), ("xmlns:r", NS_R)]);
113 wb.open("sheets", &[]);
114 // Every tab's name at once, because whether one is legal depends on the others: see
115 // [`crate::office::sheet::tab_names`].
116 let tabs = tab_names(book);
117 for (i, name) in tabs.iter().enumerate() {
118 wb.empty("sheet", &[
119 ("name", name),
120 ("sheetId", &fmt!("{}", i + 1)),
121 ("r:id", &ids[i]),
122 ]);
123 }
124 res!(wb.close("sheets"));
125 res!(wb.close("workbook"));
126
127 zip.set("[Content_Types].xml", res!(types.write()).into_bytes(), Method::Deflate);
128 zip.set("_rels/.rels", res!(root.write()).into_bytes(), Method::Deflate);
129 zip.set("xl/workbook.xml", res!(wb.finish()).into_bytes(), Method::Deflate);
130 zip.set("xl/_rels/workbook.xml.rels", res!(wb_rels.write()).into_bytes(), Method::Deflate);
131 for (i, s) in book.sheets.iter().enumerate() {
132 let part = res!(sheet_part(s, &seen));
133 zip.set(&fmt!("xl/worksheets/sheet{}.xml", i + 1), part.into_bytes(), Method::Deflate);
134 }
135 zip.set("xl/sharedStrings.xml", res!(shared(&strings)).into_bytes(), Method::Deflate);
136 zip.set("xl/styles.xml", res!(styles()).into_bytes(), Method::Deflate);
137 zip.write()
138}
139
140fn sheet_part(s: &crate::office::sheet::Sheet, seen: &BTreeMap<&str, usize>) -> Outcome<String> {
141 let mut out = Out::declared();
142 out.open("worksheet", &[("xmlns", NS_S), ("xmlns:r", NS_R)]);
143 // What rectangle the sheet occupies. A reader is not obliged to believe it -- this one reads the
144 // cells' own addresses -- but a reader that sizes a grid before filling it wants the hint.
145 if let Some(extent) = s.extent() {
146 out.empty("dimension", &[("ref", &extent.name())]);
147 }
148 out.open("sheetData", &[]);
149 for (r, row) in s.rows.iter().enumerate() {
150 // A row of nothing is not written at all. A sheet is sparse and a reader reads the addresses,
151 // so an omitted row costs nothing and a written empty one costs a line.
152 if row.iter().all(|c| c.is_empty()) {
153 continue;
154 }
155 out.open("row", &[("r", &fmt!("{}", r + 1))]);
156 for (c, cell) in row.iter().enumerate() {
157 if cell.is_empty() {
158 continue;
159 }
160 res!(cell_part(&mut out, cell, &Ref { col: c as u32, row: r as u32 }, seen));
161 }
162 res!(out.close("row"));
163 }
164 res!(out.close("sheetData"));
165 res!(out.close("worksheet"));
166 out.finish()
167}
168
169/// One cell.
170///
171/// The address is written on every cell, because that is what makes a sheet sparse: a reader places a
172/// cell by its `r` and not by its position, and a writer that left the attribute off would produce a
173/// file every reader misaligns at the first gap.
174fn cell_part(
175 out: &mut Out,
176 cell: &Cell,
177 at: &Ref,
178 seen: &BTreeMap<&str, usize>,
179)
180 -> Outcome<()>
181{
182 let name = at.name();
183 let mut attrs: Vec<(&str, &str)> = vec![("r", &name)];
184 let kind = match &cell.value {
185 Value::Text(_) => Some("s"),
186 Value::Bool(_) => Some("b"),
187 Value::Error(_) => Some("e"),
188 // A number and a date are both numbers; what separates them is the style.
189 _ => None,
190 };
191 if let Some(kind) = kind {
192 attrs.push(("t", kind));
193 }
194 if matches!(cell.value, Value::Date(_)) {
195 attrs.push(("s", STYLE_DATE));
196 }
197 out.open("c", &attrs);
198 if let Some(f) = &cell.formula {
199 // The formula, and then the value beside it. Both, always: a formula written without its
200 // cached value shows as blank in every reader that does not calculate, which includes every
201 // reader on a phone.
202 out.leaf("f", &[], f);
203 }
204 let v = match &cell.value {
205 Value::Empty => None,
206 Value::Text(t) => Some(fmt!("{}", seen.get(t.as_str()).copied().unwrap_or(0))),
207 Value::Number(n) => Some(repr(*n)),
208 Value::Bool(b) => Some(match b {
209 true => "1".to_string(),
210 false => "0".to_string(),
211 }),
212 Value::Error(e) => Some(e.clone()),
213 Value::Date(d) => Some(repr(serial_of(d))),
214 };
215 if let Some(v) = v {
216 out.leaf("v", &[], &v);
217 }
218 res!(out.close("c"));
219 Ok(())
220}
221
222/// A number as the file stores it, which is not how a person reads it.
223///
224/// Seventeen significant figures round-trips every `f64` exactly, and the file is not read by a
225/// person -- `Value::show` is what a person sees. Writing fewer here would lose precision the caller
226/// handed over.
227fn repr(n: f64) -> String {
228 if !n.is_finite() {
229 return "0".to_string();
230 }
231 if n == n.trunc() && n.abs() < 1e15 {
232 return fmt!("{}", n as i64);
233 }
234 let mut s = fmt!("{}", n);
235 if s.parse::<f64>() != Ok(n) {
236 s = fmt!("{:.17e}", n);
237 }
238 s
239}
240
241/// The serial number a date text stands for, counting days from the last day of 1899.
242///
243/// The epoch is 30 December 1899 and not 1 January 1900, which looks like an off-by-two and is not:
244/// Lotus 1-2-3 treated 1900 as a leap year, Excel copied the bug deliberately for compatibility, and
245/// every spreadsheet since has kept it. Serial 60 is 29 February 1900, a day that did not happen. The
246/// two errors cancel for every date from 1 March 1900 onward, which is every date anybody stores.
247fn serial_of(text: &str) -> f64 {
248 let (date, time) = match text.split_once(['T', ' ']) {
249 Some((d, t)) => (d, Some(t)),
250 None => (text, None),
251 };
252 let parts: Vec<&str> = date.split('-').collect();
253 if parts.len() != 3 {
254 return 0.0;
255 }
256 let y: i64 = parts[0].parse().unwrap_or(1900);
257 let m: i64 = parts[1].parse().unwrap_or(1);
258 let d: i64 = parts[2].parse().unwrap_or(1);
259 let days = days_from_civil(y, m, d) - days_from_civil(1899, 12, 30);
260 let frac = match time {
261 None => 0.0,
262 Some(t) => {
263 let hms: Vec<&str> = t.trim_end_matches('Z').split(':').collect();
264 let h: f64 = hms.first().and_then(|s| s.parse().ok()).unwrap_or(0.0);
265 let mi: f64 = hms.get(1).and_then(|s| s.parse().ok()).unwrap_or(0.0);
266 let se: f64 = hms.get(2).and_then(|s| s.parse().ok()).unwrap_or(0.0);
267 (h * 3600.0 + mi * 60.0 + se) / 86_400.0
268 }
269 };
270 days as f64 + frac
271}
272
273/// Days from 1970-01-01 to a civil date, by Howard Hinnant's algorithm.
274///
275/// Written out rather than reached for through a calendar crate: this is the only date arithmetic
276/// here, it is eleven lines, and a dependency for eleven lines is a dependency.
277pub(crate) fn days_from_civil(y: i64, m: i64, d: i64) -> i64 {
278 let y = y - i64::from(m <= 2);
279 let era = if y >= 0 { y } else { y - 399 } / 400;
280 let yoe = y - era * 400;
281 let doy = (153 * (m + if m > 2 { -3 } else { 9 }) + 2) / 5 + d - 1;
282 let doe = yoe * 365 + yoe / 4 - yoe / 100 + doy;
283 era * 146_097 + doe - 719_468
284}
285
286fn shared(strings: &[&str]) -> Outcome<String> {
287 let n = fmt!("{}", strings.len());
288 let mut out = Out::declared();
289 out.open("sst", &[("xmlns", NS_S), ("count", &n), ("uniqueCount", &n)]);
290 for s in strings {
291 out.open("si", &[]);
292 // `xml:space="preserve"` or a string that is spaces arrives as nothing, and a column of
293 // deliberate blanks becomes a column of empties.
294 out.leaf("t", &[("xml:space", "preserve")], s);
295 res!(out.close("si"));
296 }
297 res!(out.close("sst"));
298 out.finish()
299}
300
301/// The styles part: the tables Excel insists on, and the two formats this writer uses.
302fn styles() -> Outcome<String> {
303 let mut out = Out::declared();
304 out.open("styleSheet", &[("xmlns", NS_S)]);
305
306 out.open("fonts", &[("count", "1")]);
307 out.open("font", &[]);
308 out.empty("sz", &[("val", "11")]);
309 out.empty("name", &[("val", "Calibri")]);
310 res!(out.close("font"));
311 res!(out.close("fonts"));
312
313 // Both of these, in this order. Excel wants `none` then `gray125` whether or not anything uses
314 // either, and a file without them opens with a repair prompt that names no reason.
315 out.open("fills", &[("count", "2")]);
316 for pattern in ["none", "gray125"] {
317 out.open("fill", &[]);
318 out.empty("patternFill", &[("patternType", pattern)]);
319 res!(out.close("fill"));
320 }
321 res!(out.close("fills"));
322
323 out.open("borders", &[("count", "1")]);
324 out.open("border", &[]);
325 for side in ["left", "right", "top", "bottom", "diagonal"] {
326 out.empty(side, &[]);
327 }
328 res!(out.close("border"));
329 res!(out.close("borders"));
330
331 out.open("cellStyleXfs", &[("count", "1")]);
332 out.empty("xf", &[("numFmtId", "0"), ("fontId", "0"), ("fillId", "0"), ("borderId", "0")]);
333 res!(out.close("cellStyleXfs"));
334
335 // Index 0 plain, index 1 a date. Nothing else, because nothing else is used: an unused style is
336 // a number somebody later trusts.
337 out.open("cellXfs", &[("count", "2")]);
338 out.empty("xf", &[("numFmtId", "0"), ("fontId", "0"), ("fillId", "0"), ("borderId", "0"),
339 ("xfId", "0")]);
340 // Number format 14 is the built-in short date, which each reader renders in its own locale --
341 // which is right: a date is a date, and how it is written belongs to whoever is reading it.
342 out.empty("xf", &[("numFmtId", "14"), ("fontId", "0"), ("fillId", "0"), ("borderId", "0"),
343 ("xfId", "0"), ("applyNumberFormat", "1")]);
344 res!(out.close("cellXfs"));
345
346 // The named style a cell wears when nobody has styled it. `builtinId="0"` is what makes it Excel's
347 // own Normal rather than a style of ours that happens to be called that. After `cellXfs`, which is
348 // where CT_Stylesheet's sequence puts it.
349 out.open("cellStyles", &[("count", "1")]);
350 out.empty("cellStyle", &[("name", "Normal"), ("xfId", "0"), ("builtinId", "0")]);
351 res!(out.close("cellStyles"));
352
353 res!(out.close("styleSheet"));
354 out.finish()
355}