Oregami
Repositories/oxedyne/fe2o3

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

16.6 KiB, 31 runs

created by r1870400018:22949, 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//! Writing cells into a `.xlsx` that already exists, one cell at a time.
2//!
3//! # A sheet is sparse, so a write is an insertion and not an assignment
4//!
5//! There is no array to index. A row may be absent, and within a row `<c r="D3">` may follow
6//! `<c r="A3">` with nothing between, so writing `B3` means finding where `B3` WOULD go and putting it
7//! there -- among the cells of row 3 if that row exists, and as a new row in the right place among the
8//! rows if it does not. A writer that appended would put the value in the wrong column, or the wrong
9//! row, and the file would still look like a file.
10//!
11//! # Two things are rewritten besides the cell, and both of them have to be
12//!
13//! `<dimension>` says the rectangle the sheet occupies, and a cell written outside it is a cell some
14//! readers do not draw. It is widened.
15//!
16//! `xl/calcChain.xml` is Excel's record of what order to recalculate in, and it names cells by
17//! position. A formula written into a cell the chain does not know about makes the chain wrong, and
18//! Excel's response to a wrong chain is a repair prompt -- which "fixes" the file and never says why.
19//! So the part is DELETED, along with its content-type override and its relationship, and Excel
20//! rebuilds it. LibreOffice never reads it at all.
21//!
22//! **The chain goes wrong in BOTH directions and both of them drop it.** A formula WRITTEN leaves the
23//! chain short of a cell. A formula DESTROYED -- a plain value put over a cell that held an `<f>`,
24//! which is one call to this module and no formula anywhere in it -- leaves the chain naming a cell
25//! that has none, and ECMA-376 Part 1 §18.6.1 says a `c` in the chain is "a single cell, which shall
26//! contain a formula". The second case is not a lesser version of the first; it is a spec violation
27//! where the first is only a stale hint, and it went unnoticed until the ECMA-376 schemas were pointed
28//! at the output, because no reader on this machine reads the part. An EMPTY `<calcChain/>` would be no
29//! better -- `CT_CalcChain` requires at least one `c` -- so the part is removed rather than emptied.
30//!
31//! # A written formula carries no cached value unless the caller supplies one
32//!
33//! Nothing here calculates -- see [`crate::office::sheet`] on why that is the correct answer -- so a
34//! formula goes in with no `<v>` beside it and the reader computes it on open. The alternative would be
35//! to leave the value that was there, which is the value of the OLD formula, and a cell showing a
36//! number that does not follow from the expression above it is worse than one showing nothing.
37//!
38//! [Written with AI entirely](https://need2know.ai/entirely-ai/code)\
39//! Anthropic Claude
40
41use crate::office::opc;
42use crate::office::sheet::{
43 Ref,
44 Value,
45 stored,
46 typed,
47};
48use crate::zip::{
49 Method,
50 Zip,
51};
52
53use oxedyne_fe2o3_core::prelude::*;
54use oxedyne_fe2o3_text::xml::{
55 Elem,
56 Span,
57 Xml,
58};
59use oxedyne_fe2o3_text::xml::write::{
60 escape,
61 escape_attr,
62};
63
64/// One cell to write.
65#[derive(Clone, Debug)]
66pub struct Set {
67 pub sheet: Option<String>, // by the name on the tab; None means the first
68 pub at: Ref,
69 // The value as a person would type it. `sheet::typed` decides what it is; an empty string
70 // empties the cell.
71 pub value: Option<String>,
72 pub formula: Option<String>, // without its leading `=`, which is stripped if present
73}
74
75/// What an edit of a `.xlsx` produced.
76#[derive(Clone, Debug, Default)]
77pub struct Edited {
78 pub bytes: Vec<u8>,
79 pub cells: usize,
80 pub sheets: Vec<String>, // the tabs that were touched, in the order they were
81}
82
83/// Writes cells into a `.xlsx`, leaving every part of the archive it did not have to change.
84pub fn edit(bytes: &[u8], sets: &[Set]) -> Outcome<Edited> {
85 if sets.is_empty() {
86 return Err(err!("A write to a spreadsheet was asked for with no cells in it."; Invalid, Input));
87 }
88 let cap = super::read::MAX_PART;
89 let mut zip = res!(Zip::read(bytes.to_vec()));
90 let wb_part = res!(opc::main_part(&zip, cap));
91 let dir = opc::dir_of(&wb_part);
92 let rels = res!(opc::rels_of(&zip, &wb_part, cap));
93 let wb_src = res!(String::from_utf8(res!(zip.content_capped(&wb_part, cap))), Decode, String);
94 let wb = res!(Xml::parse(&wb_src));
95
96 // The tabs, in tab order, each with the part that holds it. A sheet the workbook names and whose
97 // part is missing is not writable, and saying so by name beats writing into the wrong one.
98 let mut tabs: Vec<(String, String)> = Vec::new();
99 if let Some(list) = res!(wb.root()).child("sheets") {
100 for s in list.children("sheet") {
101 let name = s.attr("name").unwrap_or("Sheet").to_string();
102 let target = s.attr("r:id")
103 .and_then(|id| rels.get(id))
104 .filter(|(kind, _)| kind == opc::REL_SHEET)
105 .map(|(_, t)| t.clone())
106 .filter(|t| zip.has(t));
107 if let Some(t) = target {
108 tabs.push((name, t));
109 }
110 }
111 }
112 if tabs.is_empty() {
113 return Err(err!(
114 "This workbook has no sheet whose part is in the package, so there is nowhere to write.";
115 Invalid, Input, Missing));
116 }
117 let names: Vec<&str> = tabs.iter().map(|(n, _)| n.as_str()).collect();
118
119 // Grouped by part, because one part is parsed, spliced and written once however many cells land
120 // in it -- and because two sheets may share neither a row nor a dimension.
121 let mut jobs: Vec<(String, String, Vec<&Set>)> = Vec::new();
122 for set in sets {
123 let (name, part) = match &set.sheet {
124 None => tabs[0].clone(),
125 Some(want) => {
126 let found = tabs.iter()
127 .find(|(n, _)| n == want)
128 .or_else(|| tabs.iter().find(|(n, _)| n.eq_ignore_ascii_case(want)));
129 res!(found.cloned().ok_or_else(|| err!(
130 "This workbook has no sheet named '{}'. It has: {}. Nothing has been written.",
131 want, names.join(", "); Invalid, Input, Missing)))
132 }
133 };
134 match jobs.iter_mut().find(|(_, p, _)| *p == part) {
135 Some(j) => j.2.push(set),
136 None => jobs.push((name, part, vec![set])),
137 }
138 }
139 // TWO WRITES TO ONE CELL ARE REFUSED, and it is worth saying why rather than picking one. There
140 // is no order for them to be applied IN: nothing here reads a cell it has written, so "the last
141 // one wins" would be a rule about the order a caller happened to build a list in. A caller that
142 // means to overwrite its own value has changed its mind, and can say so in one write.
143 for (name, _, sets) in &jobs {
144 for (i, one) in sets.iter().enumerate() {
145 if sets[..i].iter().any(|s| s.at == one.at) {
146 return Err(err!(
147 "{} of sheet '{}' is written twice in one call, and there is no order in which \
148 to apply the two. Nothing has been written.", one.at.name(), name;
149 Invalid, Input, Conflict));
150 }
151 }
152 }
153
154 let mut formulas = false; // a formula was written
155 let mut razed = false; // a formula that was there is not any more
156 let mut touched = Vec::new();
157 let mut cells = 0usize;
158 for (name, part, sets) in &jobs {
159 let src = res!(String::from_utf8(res!(zip.content_capped(part, cap))), Decode, String);
160 let mut xml = res!(Xml::parse(&src));
161 razed = res!(write_cells(&mut xml, sets)) || razed;
162 zip.set(part, xml.render().into_bytes(), Method::Deflate);
163 touched.push(name.clone());
164 cells += sets.len();
165 formulas = formulas || sets.iter().any(|s| s.formula.is_some());
166 }
167 if formulas || razed {
168 res!(drop_calc_chain(&mut zip, &dir, &wb_part, cap));
169 }
170 Ok(Edited { bytes: res!(zip.write()), cells, sheets: touched })
171}
172
173/// Puts every cell of one sheet where it belongs, as splices into the part's own bytes.
174///
175/// The answer says whether a cell that held an `<f>` was replaced by one that does not, which the
176/// caller needs because it is the other half of what makes `xl/calcChain.xml` wrong.
177fn write_cells(xml: &mut Xml, sets: &[&Set]) -> Outcome<bool> {
178 let root = res!(xml.root()).clone();
179 let data = res!(root.child("sheetData").cloned().ok_or_else(|| err!(
180 "This sheet has no <sheetData>, so it is not a worksheet."; Invalid, Input, Missing)));
181 let rows: Vec<Elem> = data.children("row").into_iter().cloned().collect();
182
183 // One pass per ROW rather than per cell, because a row is what gets built or rebuilt: two cells
184 // written into a `<row r="3"/>` that has nothing inside it are one replacement of that element,
185 // and done a cell at a time they would be two splices over the same bytes.
186 let mut by_row: Vec<(u32, Vec<&Set>)> = Vec::new();
187 let mut edge = Ref { col: 0, row: 0 };
188 for set in sets {
189 edge = Ref { col: edge.col.max(set.at.col), row: edge.row.max(set.at.row) };
190 match by_row.iter_mut().find(|(r, _)| *r == set.at.row) {
191 Some(g) => g.1.push(set),
192 None => by_row.push((set.at.row, vec![set])),
193 }
194 }
195 by_row.sort_by_key(|(r, _)| *r);
196 for (_, group) in by_row.iter_mut() {
197 group.sort_by_key(|s| s.at.col);
198 }
199
200 let mut splices: Vec<(Span, String)> = Vec::new();
201 let mut razed = false;
202 for (at_row, group) in &by_row {
203 let row = rows.iter().find(|r| {
204 r.attr("r").and_then(|v| v.parse::<u32>().ok()) == Some(at_row + 1)
205 });
206 let (row, inner) = match row {
207 // A row the sheet has not got is built whole and put in among the rows it has.
208 None => {
209 let body: String = group.iter()
210 .map(|s| cell_markup(&s.at, None, s))
211 .collect();
212 let text = fmt!("<row r=\"{}\">{}</row>", at_row + 1, body);
213 let after = rows.iter().find(|r| {
214 r.attr("r").and_then(|v| v.parse::<u32>().ok())
215 .map(|v| v > at_row + 1)
216 .unwrap_or(false)
217 });
218 let at = match after {
219 Some(r) => r.span.start..r.span.start,
220 // An empty `<sheetData/>` has no inside; it is replaced by one that has.
221 None => match &data.inner {
222 Some(i) => i.end..i.end,
223 None => {
224 splices.push((data.span.clone(),
225 fmt!("<sheetData>{}</sheetData>", text)));
226 continue;
227 }
228 },
229 };
230 splices.push((at, text));
231 continue;
232 }
233 // A `<row r="3"/>` is rebuilt from its own open tag, for the same reason.
234 Some(r) if r.inner.is_none() => {
235 let body: String = group.iter()
236 .map(|s| cell_markup(&s.at, None, s))
237 .collect();
238 splices.push((r.span.clone(), fmt!("{}{}</row>", open_of(xml, r), body)));
239 continue;
240 }
241 Some(r) => (r, res!(r.inner.clone().ok_or_else(|| err!(
242 "A row was found to have an inside and then not to."; Bug)))),
243 };
244 let cells = row.children("c");
245 for set in group {
246 let existing = cells.iter().find(|c| {
247 c.attr("r").and_then(|a| Ref::parse(a).ok()).map(|p| p.col) == Some(set.at.col)
248 });
249 match existing {
250 // The style stays on the cell. It is the user's formatting, and a value written over
251 // a number with a currency format is still money.
252 Some(c) => {
253 // The whole `<c>` is replaced, so a formula on the old one is gone unless the
254 // new one carries its own. This is the only place a formula can be destroyed.
255 razed = razed || (c.child("f").is_some() && set.formula.is_none());
256 splices.push((c.span.clone(), cell_markup(&set.at, c.attr("s"), set)));
257 }
258 None => {
259 let after = cells.iter().find(|c| {
260 c.attr("r").and_then(|a| Ref::parse(a).ok())
261 .map(|p| p.col > set.at.col)
262 .unwrap_or(false)
263 });
264 let at = match after {
265 Some(c) => c.span.start,
266 None => inner.end,
267 };
268 splices.push((at..at, cell_markup(&set.at, None, set)));
269 }
270 }
271 }
272 }
273
274 if let Some((span, text)) = widened(&root, &edge) {
275 splices.push((span, text));
276 }
277 // Ascending, because `Xml::splice` refuses one that overlaps an earlier one and two inserts at the
278 // same offset are ordered by the order they are made.
279 splices.sort_by_key(|(s, _)| (s.start, s.end));
280 for (span, text) in splices {
281 res!(xml.splice(span, text));
282 }
283 Ok(razed)
284}
285
286/// The `<dimension>`'s new `ref` value, where a cell was written outside the rectangle it claims.
287///
288/// The bottom right only. A sheet whose dimension starts at `B2` and which gains a cell at `A1` is
289/// rare enough, and shrinking or moving the top left is how a dimension stops describing the sheet.
290fn widened(root: &Elem, edge: &Ref) -> Option<(Span, String)> {
291 let dim = root.child("dimension")?;
292 let attr = dim.attrs.iter().find(|a| a.name.qname == "ref")?;
293 let (from, to) = match attr.value.split_once(':') {
294 Some((a, b)) => (a, b),
295 None => (attr.value.as_str(), attr.value.as_str()),
296 };
297 let from = Ref::parse(from).ok()?;
298 let to = Ref::parse(to).ok()?;
299 if to.col >= edge.col && to.row >= edge.row {
300 return None;
301 }
302 let grown = Ref { col: to.col.max(edge.col), row: to.row.max(edge.row) };
303 Some((attr.val_span.clone(), fmt!("{}:{}", from.name(), grown.name())))
304}
305
306/// One `<c>` element holding what the caller asked for.
307fn cell_markup(at: &Ref, style: Option<&str>, set: &Set) -> String {
308 let value = set.value.as_deref().map(typed);
309 let mut out = fmt!("<c r=\"{}\"", at.name());
310 if let Some(s) = style {
311 out.push_str(&fmt!(" s=\"{}\"", escape_attr(s)));
312 }
313 // The type attribute describes the `<v>`, so a formula cell with no cached value carries none.
314 let kind = match (&set.formula, &value) {
315 (Some(_), None) => None,
316 (_, Some(Value::Text(_))) => Some("inlineStr"),
317 (_, Some(Value::Bool(_))) => Some("b"),
318 (_, Some(Value::Error(_))) => Some("e"),
319 _ => None,
320 };
321 // A formula cell whose cached value is text says `str` rather than `inlineStr`: the latter puts the
322 // text in an `<is>`, and a formula's result goes in the `<v>` like any other.
323 let kind = match (&set.formula, kind) {
324 (Some(_), Some("inlineStr")) => Some("str"),
325 (_, k) => k,
326 };
327 if let Some(k) = kind {
328 out.push_str(&fmt!(" t=\"{}\"", k));
329 }
330 let body = body_of(set, &value);
331 match body.is_empty() {
332 true => out.push_str("/>"),
333 false => out.push_str(&fmt!(">{}</c>", body)),
334 }
335 out
336}
337
338/// What goes between a cell's tags: the formula, and the value where there is one.
339fn body_of(set: &Set, value: &Option<Value>) -> String {
340 let mut out = String::new();
341 if let Some(f) = &set.formula {
342 let f = f.trim_start_matches('=');
343 if !f.is_empty() {
344 out.push_str(&fmt!("<f>{}</f>", escape(f)));
345 }
346 }
347 match value {
348 None | Some(Value::Empty) => {}
349 Some(Value::Number(n)) => out.push_str(&fmt!("<v>{}</v>", stored(*n))),
350 Some(Value::Bool(b)) => out.push_str(match b {
351 true => "<v>1</v>",
352 false => "<v>0</v>",
353 }),
354 Some(Value::Error(e)) => out.push_str(&fmt!("<v>{}</v>", escape(e))),
355 Some(Value::Date(d)) => out.push_str(&fmt!("<v>{}</v>", escape(d))),
356 Some(Value::Text(t)) => match set.formula.is_some() {
357 true => out.push_str(&fmt!("<v>{}</v>", escape(t))),
358 // `xml:space` is not a SpreadsheetML thing; `<t>` in an `<is>` takes it the same way
359 // `<w:t>` does, and without it a value of " " is written and read back as "".
360 false => out.push_str(&fmt!(
361 "<is><t xml:space=\"preserve\">{}</t></is>", escape(t))),
362 },
363 }
364 out
365}
366
367/// A row's open tag exactly as it was written, so rebuilding the element keeps its attributes.
368fn open_of(xml: &Xml, row: &Elem) -> String {
369 let raw = xml.raw(&row.open);
370 // `<row r="3"/>` opens and closes at once; the rebuilt element needs the open tag alone.
371 match raw.strip_suffix("/>") {
372 Some(head) => fmt!("{}>", head),
373 None => raw.to_string(),
374 }
375}
376
377/// Takes `xl/calcChain.xml` out of the package, along with the two declarations that name it.
378///
379/// A part removed while `[Content_Types].xml` still declares it is a package Excel refuses, so the
380/// override and the relationship go with it. See this module's own note on why it goes at all.
381fn drop_calc_chain(zip: &mut Zip, dir: &str, wb_part: &str, cap: u64) -> Outcome<()> {
382 let chain = fmt!("{}calcChain.xml", dir);
383 if !zip.remove(&chain) {
384 return Ok(());
385 }
386 // The override names the part with a leading slash, which is how a package addresses one.
387 let types = "[Content_Types].xml";
388 if zip.has(types) {
389 let src = res!(String::from_utf8(res!(zip.content_capped(types, cap))), Decode, String);
390 let mut xml = res!(Xml::parse(&src));
391 let want = fmt!("/{}", chain);
392 let gone: Vec<Span> = res!(xml.root()).children("Override").iter()
393 .filter(|o| o.attr("PartName") == Some(want.as_str()))
394 .map(|o| o.span.clone())
395 .collect();
396 if !gone.is_empty() {
397 for span in gone {
398 res!(xml.splice(span, String::new()));
399 }
400 zip.set(types, xml.render().into_bytes(), Method::Deflate);
401 }
402 }
403 let rels_part = fmt!("{}_rels/{}.rels", dir, &wb_part[dir.len()..]);
404 if zip.has(&rels_part) {
405 let src = res!(String::from_utf8(res!(zip.content_capped(&rels_part, cap))), Decode, String);
406 let mut xml = res!(Xml::parse(&src));
407 let gone: Vec<Span> = res!(xml.root()).children("Relationship").iter()
408 .filter(|r| r.attr("Type") == Some(REL_CALC_CHAIN))
409 .map(|r| r.span.clone())
410 .collect();
411 if !gone.is_empty() {
412 for span in gone {
413 res!(xml.splice(span, String::new()));
414 }
415 zip.set(&rels_part, xml.render().into_bytes(), Method::Deflate);
416 }
417 }
418 Ok(())
419}
420
421/// The relationship type of the calculation chain.
422const REL_CALC_CHAIN: &str =
423 "http://schemas.openxmlformats.org/officeDocument/2006/relationships/calcChain";