1. 1//! Excel ops. Vak does not calculate (deferred): a changed input or formula
2. 2//! sets `fullCalcOnLoad`, so Excel recalculates on open, and the reader
3. 3//! reports every cached formula value as stale until then.
4. 4
5. 5use std::collections::{BTreeMap, BTreeSet};
6. 6use std::io::{Read, Seek};
7. 7
8. 8use super::{CellValue, EditError, Expect, Outcome, Work, fail, free_part_name};
9. 9use crate::read::column_name;
10. 10use crate::splice::{Splice, Tree, escape_attr, escape_text, start_tag};
11. 11
12. 12const S: &str = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
13. 13const S_STRICT: &str = "http://purl.oclc.org/ooxml/spreadsheetml/main";
14. 14const R: &str = "http://schemas.openxmlformats.org/officeDocument/2006/relationships";
15. 15const R_STRICT: &str = "http://purl.oclc.org/ooxml/officeDocument/relationships";
16. 16const WORKSHEET_TYPE: &str =
17. 17 "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml";
18. 18const MAX_ROW: u32 = 1_048_576;
19. 19const MAX_COLUMN: u32 = 16_384;
20. 20
21. 21fn prefix(tree: &Tree) -> Result<String, EditError> {
22. 22 match tree.prefix_for(S).or_else(|| tree.prefix_for(S_STRICT)) {
23. 23 Some(prefix) => Ok(prefix),
24. 24 None => fail("the part does not declare the SpreadsheetML namespace on its root"),
25. 25 }
26. 26}
27. 27
28. 28/// `B4` or `$B$4` → (column, row), both 1-based.
29. 29fn address(text: &str) -> Result<(u32, u32), EditError> {
30. 30 let cleaned = text.trim().replace('$', "").to_ascii_uppercase();
31. 31 let split = cleaned
32. 32 .find(|character: char| character.is_ascii_digit())
33. 33 .unwrap_or(cleaned.len());
34. 34 let (letters, digits) = cleaned.split_at(split);
35. 35 let column = if letters.is_empty()
36. 36 || letters.len() > 3
37. 37 || !letters.chars().all(|c| c.is_ascii_uppercase())
38. 38 {
39. 39 None
40. 40 } else {
41. 41 letters.chars().try_fold(0u32, |total, character| {
42. 42 Some(total * 26 + (character as u32 - 'A' as u32 + 1))
43. 43 })
44. 44 };
45. 45 let row = digits.parse::<u32>().ok();
46. 46 match (column, row) {
47. 47 (Some(column), Some(row))
48. 48 if (1..=MAX_COLUMN).contains(&column) && (1..=MAX_ROW).contains(&row) =>
49. 49 {
50. 50 Ok((column, row))
51. 51 }
52. 52 _ => fail(format!("{text:?} is not a cell address like B4")),
53. 53 }
54. 54}
55. 55
56. 56fn quote_sheet(name: &str) -> String {
57. 57 if name
58. 58 .chars()
59. 59 .all(|character| character.is_alphanumeric() || character == '_' || character == '.')
60. 60 {
61. 61 name.to_string()
62. 62 } else {
63. 63 format!("'{}'", name.replace('\'', "''"))
64. 64 }
65. 65}
66. 66
67. 67struct Book {
68. 68 part: String,
69. 69 bytes: Vec<u8>,
70. 70 tree: Tree,
71. 71 s: String,
72. 72}
73. 73
74. 74fn book<R2: Read + Seek>(work: &mut Work<'_, R2>) -> Result<Book, EditError> {
75. 75 let part = work.main_part();
76. 76 let bytes = work.get(&part)?;
77. 77 let tree = Tree::parse(&bytes, &part, work.limits())?;
78. 78 let s = prefix(&tree)?;
79. 79 Ok(Book {
80. 80 part,
81. 81 bytes,
82. 82 tree,
83. 83 s,
84. 84 })
85. 85}
86. 86
87. 87/// The sheet's part and its name as the workbook spells it.
88. 88fn sheet_part<R2: Read + Seek>(
89. 89 work: &mut Work<'_, R2>,
90. 90 wanted: &str,
91. 91) -> Result<(String, String), EditError> {
92. 92 let book = book(work)?;
93. 93 let mut names = Vec::new();
94. 94 for sheet in book.tree.descendants(0, "sheet") {
95. 95 let element = &book.tree.nodes[sheet].element;
96. 96 let Some(name) = element.attr("name") else {
97. 97 continue;
98. 98 };
99. 99 if name.eq_ignore_ascii_case(wanted.trim().trim_matches('\'')) {
100. 100 let Some(id) = element.attr_prefixed("id") else {
101. 101 return fail(format!("sheet {name:?} has no relationship id"));
102. 102 };
103. 103 let main = book.part.clone();
104. 104 return match work.by_id(&main, id)? {
105. 105 Some(part) => Ok((part, name.to_string())),
106. 106 None => fail(format!("sheet {name:?} points at no part")),
107. 107 };
108. 108 }
109. 109 names.push(name.to_string());
110. 110 }
111. 111 fail(format!("no sheet {wanted:?}; sheets: {}", names.join(", ")))
112. 112}
113. 113
114. 114fn cell_xml(
115. 115 s: &str,
116. 116 reference: &str,
117. 117 value: &CellValue,
118. 118 style: Option<&str>,
119. 119) -> Result<(String, bool), EditError> {
120. 120 let style = style
121. 121 .map(|style| format!(r#" s="{}""#, escape_attr(style)))
122. 122 .unwrap_or_default();
123. 123 Ok(match value {
124. 124 CellValue::Number(number) => {
125. 125 if !number.is_finite() {
126. 126 return fail(format!("{reference}: {number} is not a finite number"));
127. 127 }
128. 128 (
129. 129 format!(r#"<{s}c r="{reference}"{style}><{s}v>{number}</{s}v></{s}c>"#),
130. 130 false,
131. 131 )
132. 132 }
133. 133 CellValue::Bool(value) => (
134. 134 format!(
135. 135 r#"<{s}c r="{reference}"{style} t="b"><{s}v>{}</{s}v></{s}c>"#,
136. 136 u8::from(*value)
137. 137 ),
138. 138 false,
139. 139 ),
140. 140 CellValue::Text(text) => match text.strip_prefix('=') {
141. 141 Some(formula) if !formula.trim().is_empty() => (
142. 142 format!(
143. 143 r#"<{s}c r="{reference}"{style}><{s}f>{}</{s}f></{s}c>"#,
144. 144 escape_text(formula.trim())
145. 145 ),
146. 146 true,
147. 147 ),
148. 148 _ => {
149. 149 let literal = text.strip_prefix('\'').unwrap_or(text);
150. 150 (
151. 151 format!(
152. 152 r#"<{s}c r="{reference}"{style} t="inlineStr"><{s}is><{s}t xml:space="preserve">{}</{s}t></{s}is></{s}c>"#,
153. 153 escape_text(literal)
154. 154 ),
155. 155 false,
156. 156 )
157. 157 }
158. 158 },
159. 159 })
160. 160}
161. 161
162. 162/// What the reader will show for a written value.
163. 163fn shown(value: &CellValue) -> String {
164. 164 match value {
165. 165 CellValue::Number(number) => number.to_string(),
166. 166 CellValue::Bool(true) => "TRUE".into(),
167. 167 CellValue::Bool(false) => "FALSE".into(),
168. 168 CellValue::Text(text) => match text.strip_prefix('=') {
169. 169 Some(formula) if !formula.trim().is_empty() => format!("={} [", formula.trim()),
170. 170 _ => text.strip_prefix('\'').unwrap_or(text).to_string(),
171. 171 },
172. 172 }
173. 173}
174. 174
175. 175pub(super) fn set_cells<R2: Read + Seek>(
176. 176 work: &mut Work<'_, R2>,
177. 177 sheet: &str,
178. 178 cells: &BTreeMap<String, CellValue>,
179. 179) -> Result<Outcome, EditError> {
180. 180 if cells.is_empty() {
181. 181 return fail("cells is empty; give at least one address, e.g. {\"B4\": 120}");
182. 182 }
183. 183 let (part, name) = sheet_part(work, sheet)?;
184. 184 let mut targets: BTreeMap<u32, BTreeMap<u32, (&String, &CellValue)>> = BTreeMap::new();
185. 185 for (reference, value) in cells {
186. 186 let (column, row) = address(reference)?;
187. 187 if targets
188. 188 .entry(row)
189. 189 .or_default()
190. 190 .insert(column, (reference, value))
191. 191 .is_some()
192. 192 {
193. 193 return fail(format!("{reference} is given twice"));
194. 194 }
195. 195 }
196. 196 let bytes = work.get(&part)?;
197. 197 let tree = Tree::parse(&bytes, &part, work.limits())?;
198. 198 let s = prefix(&tree)?;
199. 199 if let Some(protection) = tree.descendants(0, "sheetProtection").next()
200. 200 && tree.nodes[protection]
201. 201 .element
202. 202 .attr("sheet")
203. 203 .is_some_and(|value| value == "1" || value == "true")
204. 204 {
205. 205 return fail(format!(
206. 206 "sheet {name:?} is protected; the owner must remove the protection before it can be edited"
207. 207 ));
208. 208 }
209. 209 let Some(data) = tree.descendants(0, "sheetData").next() else {
210. 210 return fail(format!("sheet {name:?} has no sheetData"));
211. 211 };
212. 212 let mut rows: BTreeMap<u32, usize> = BTreeMap::new();
213. 213 for row in tree.children(data, "row") {
214. 214 let Some(number) = tree.nodes[row]
215. 215 .element
216. 216 .attr("r")
217. 217 .and_then(|r| r.parse::<u32>().ok())
218. 218 else {
219. 219 return fail(format!(
220. 220 "sheet {name:?} has a row without a number, which this op does not edit"
221. 221 ));
222. 222 };
223. 223 rows.insert(number, row);
224. 224 }
225. 225
226. 226 let mut splice = Splice::default();
227. 227 let mut formulas_touched = false;
228. 228 let mut new_rows: Vec<(u32, String)> = Vec::new();
229. 229 for (row_number, columns) in &targets {
230. 230 let mut built = Vec::new();
231. 231 for (column, (_, value)) in columns {
232. 232 let reference = format!("{}{row_number}", column_name(*column));
233. 233 built.push((*column, reference, *value));
234. 234 }
235. 235 match rows.get(row_number) {
236. 236 None => {
237. 237 let mut row_xml = format!(r#"<{s}row r="{row_number}">"#);
238. 238 for (_, reference, value) in &built {
239. 239 let (xml, formula) = cell_xml(&s, reference, value, None)?;
240. 240 formulas_touched |= formula;
241. 241 row_xml.push_str(&xml);
242. 242 }
243. 243 row_xml.push_str(&format!("</{s}row>"));
244. 244 new_rows.push((*row_number, row_xml));
245. 245 }
246. 246 Some(row) => {
247. 247 let row_node = &tree.nodes[*row];
248. 248 let mut existing: BTreeMap<u32, usize> = BTreeMap::new();
249. 249 for cell in tree.children(*row, "c") {
250. 250 let Some((column, _)) = tree.nodes[cell]
251. 251 .element
252. 252 .attr("r")
253. 253 .and_then(|reference| address(reference).ok())
254. 254 else {
255. 255 return fail(format!("row {row_number} has a cell without an address"));
256. 256 };
257. 257 existing.insert(column, cell);
258. 258 }
259. 259 let mut attributes = row_node.element.attributes.clone();
260. 260 attributes.retain(|(key, _)| key != "spans");
261. 261 if row_node.is_empty_element() || existing.is_empty() {
262. 262 let mut row_xml = start_tag(&row_node.element.name, &attributes, false);
263. 263 for (_, reference, value) in &built {
264. 264 let (xml, formula) = cell_xml(&s, reference, value, None)?;
265. 265 formulas_touched |= formula;
266. 266 row_xml.push_str(&xml);
267. 267 }
268. 268 row_xml.push_str(&format!("</{}>", row_node.element.name));
269. 269 splice.replace(row_node.span.clone(), row_xml);
270. 270 continue;
271. 271 }
272. 272 if attributes.len() != row_node.element.attributes.len() {
273. 273 splice.replace(
274. 274 row_node.span.start..row_node.inner.start,
275. 275 start_tag(&row_node.element.name, &attributes, false),
276. 276 );
277. 277 }
278. 278 for (column, reference, value) in &built {
279. 279 match existing.get(column) {
280. 280 Some(cell) => {
281. 281 let cell_node = &tree.nodes[*cell];
282. 282 if let Some(formula) = tree.children(*cell, "f").next() {
283. 283 let formula = &tree.nodes[formula].element;
284. 284 if formula.attr("t") == Some("array") {
285. 285 return fail(format!(
286. 286 "{reference} holds an array formula, which this op does not replace"
287. 287 ));
288. 288 }
289. 289 if formula.attr("t") == Some("shared")
290. 290 && formula.attr("ref").is_some()
291. 291 {
292. 292 return fail(format!(
293. 293 "{reference} anchors a shared formula other cells use; set those cells too or pick another cell"
294. 294 ));
295. 295 }
296. 296 formulas_touched = true;
297. 297 }
298. 298 let (xml, formula) =
299. 299 cell_xml(&s, reference, value, cell_node.element.attr("s"))?;
300. 300 formulas_touched |= formula;
301. 301 splice.replace(cell_node.span.clone(), xml);
302. 302 }
303. 303 None => {
304. 304 let at = existing
305. 305 .range(column + 1..)
306. 306 .next()
307. 307 .map(|(_, cell)| tree.nodes[*cell].span.start)
308. 308 .unwrap_or(row_node.inner.end);
309. 309 let (xml, formula) = cell_xml(&s, reference, value, None)?;
310. 310 formulas_touched |= formula;
311. 311 splice.insert(at, xml);
312. 312 }
313. 313 }
314. 314 }
315. 315 }
316. 316 }
317. 317 }
318. 318 let data_node = &tree.nodes[data];
319. 319 if data_node.is_empty_element() {
320. 320 let inner: String = new_rows.iter().map(|(_, xml)| xml.as_str()).collect();
321. 321 splice.replace(
322. 322 data_node.span.clone(),
323. 323 format!("<{0}>{inner}</{0}>", data_node.element.name),
324. 324 );
325. 325 } else {
326. 326 for (number, xml) in new_rows {
327. 327 let at = rows
328. 328 .range(number + 1..)
329. 329 .next()
330. 330 .map(|(_, row)| tree.nodes[*row].span.start)
331. 331 .unwrap_or(data_node.inner.end);
332. 332 splice.insert(at, xml);
333. 333 }
334. 334 }
335. 335 if let Some(dimension) = tree.descendants(0, "dimension").next() {
336. 336 let node = &tree.nodes[dimension];
337. 337 let mut bounds: Vec<(u32, u32)> = node
338. 338 .element
339. 339 .attr("ref")
340. 340 .map(|reference| {
341. 341 reference
342. 342 .split(':')
343. 343 .filter_map(|end| address(end).ok())
344. 344 .collect()
345. 345 })
346. 346 .unwrap_or_default();
347. 347 for (row, columns) in &targets {
348. 348 for column in columns.keys() {
349. 349 bounds.push((*column, *row));
350. 350 }
351. 351 }
352. 352 let (min_column, max_column) = (
353. 353 bounds.iter().map(|b| b.0).min().unwrap_or(1),
354. 354 bounds.iter().map(|b| b.0).max().unwrap_or(1),
355. 355 );
356. 356 let (min_row, max_row) = (
357. 357 bounds.iter().map(|b| b.1).min().unwrap_or(1),
358. 358 bounds.iter().map(|b| b.1).max().unwrap_or(1),
359. 359 );
360. 360 let mut attributes = node.element.attributes.clone();
361. 361 attributes.retain(|(key, _)| key != "ref");
362. 362 attributes.push((
363. 363 "ref".into(),
364. 364 format!(
365. 365 "{}{min_row}:{}{max_row}",
366. 366 column_name(min_column),
367. 367 column_name(max_column)
368. 368 ),
369. 369 ));
370. 370 let end = if node.is_empty_element() {
371. 371 node.span.end
372. 372 } else {
373. 373 node.inner.start
374. 374 };
375. 375 splice.replace(
376. 376 node.span.start..end,
377. 377 start_tag(&node.element.name, &attributes, node.is_empty_element()),
378. 378 );
379. 379 }
380. 380 work.put(&part, splice.apply(&bytes, &part)?);
381. 381 mark_stale(work, formulas_touched)?;
382. 382
383. 383 let quoted = quote_sheet(&name);
384. 384 let expect = cells
385. 385 .iter()
386. 386 .map(|(reference, value)| {
387. 387 let (column, row) = address(reference).unwrap_or((1, 1));
388. 388 Expect::AnyUnitContains {
389. 389 prefix: format!("{quoted}!"),
390. 390 needle: format!("{}{row}: {}", column_name(column), shown(value)),
391. 391 }
392. 392 })
393. 393 .collect();
394. 394 Ok(Outcome {
395. 395 summary: format!(
396. 396 "{} cell(s) set on {name}; formulas recalculate when the file is opened",
397. 397 cells.len()
398. 398 ),
399. 399 expect,
400. 400 created: Vec::new(),
401. 401 })
402. 402}
403. 403
404. 404/// Sets `fullCalcOnLoad` so Excel recalculates on open, and when formulas
405. 405/// changed removes the calculation chain, which would otherwise name cells
406. 406/// that no longer hold the formulas it lists.
407. 407fn mark_stale<R2: Read + Seek>(
408. 408 work: &mut Work<'_, R2>,
409. 409 formulas_touched: bool,
410. 410) -> Result<(), EditError> {
411. 411 let book = book(work)?;
412. 412 let mut splice = Splice::default();
413. 413 match book.tree.children(0, "calcPr").next() {
414. 414 Some(calc) => {
415. 415 let node = &book.tree.nodes[calc];
416. 416 let mut attributes = node.element.attributes.clone();
417. 417 attributes.retain(|(key, _)| key != "fullCalcOnLoad");
418. 418 attributes.push(("fullCalcOnLoad".into(), "1".into()));
419. 419 let end = if node.is_empty_element() {
420. 420 node.span.end
421. 421 } else {
422. 422 node.inner.start
423. 423 };
424. 424 splice.replace(
425. 425 node.span.start..end,
426. 426 start_tag(&node.element.name, &attributes, node.is_empty_element()),
427. 427 );
428. 428 }
429. 429 None => {
430. 430 let after = [
431. 431 "sheets",
432. 432 "functionGroups",
433. 433 "externalReferences",
434. 434 "definedNames",
435. 435 ]
436. 436 .iter()
437. 437 .filter_map(|local| book.tree.children(0, local).next())
438. 438 .map(|index| book.tree.nodes[index].span.end)
439. 439 .max();
440. 440 let Some(after) = after else {
441. 441 return fail("the workbook has no sheets element");
442. 442 };
443. 443 splice.insert(after, format!(r#"<{}calcPr fullCalcOnLoad="1"/>"#, book.s));
444. 444 }
445. 445 }
446. 446 work.put(&book.part, splice.apply(&book.bytes, &book.part)?);
447. 447 if formulas_touched {
448. 448 let main = book.part.clone();
449. 449 let chain = work
450. 450 .relationships(&main)?
451. 451 .into_iter()
452. 452 .find(|relationship| relationship.short_kind() == "calcChain");
453. 453 if let Some(chain) = chain {
454. 454 work.remove_relationship(&main, &chain.id)?;
455. 455 work.remove(&chain.target);
456. 456 work.remove_override(&chain.target)?;
457. 457 }
458. 458 }
459. 459 Ok(())
460. 460}
461. 461
462. 462pub(super) fn append_rows<R2: Read + Seek>(
463. 463 work: &mut Work<'_, R2>,
464. 464 sheet: &str,
465. 465 rows: &[Vec<CellValue>],
466. 466) -> Result<Outcome, EditError> {
467. 467 if rows.is_empty() {
468. 468 return fail("rows is empty");
469. 469 }
470. 470 let (part, name) = sheet_part(work, sheet)?;
471. 471 let bytes = work.get(&part)?;
472. 472 let tree = Tree::parse(&bytes, &part, work.limits())?;
473. 473 let last = tree
474. 474 .descendants(0, "row")
475. 475 .filter_map(|row| {
476. 476 tree.nodes[row]
477. 477 .element
478. 478 .attr("r")
479. 479 .and_then(|r| r.parse::<u32>().ok())
480. 480 })
481. 481 .max()
482. 482 .unwrap_or(0);
483. 483 let mut cells = BTreeMap::new();
484. 484 for (offset, row) in rows.iter().enumerate() {
485. 485 for (column, value) in row.iter().enumerate() {
486. 486 let reference = format!(
487. 487 "{}{}",
488. 488 column_name(column as u32 + 1),
489. 489 last + 1 + offset as u32
490. 490 );
491. 491 cells.insert(reference, value.clone());
492. 492 }
493. 493 }
494. 494 let mut outcome = set_cells(work, &name, &cells)?;
495. 495 outcome.summary = format!(
496. 496 "{} row(s) appended to {name} from row {}",
497. 497 rows.len(),
498. 498 last + 1
499. 499 );
500. 500 Ok(outcome)
501. 501}
502. 502
503. 503fn check_sheet_name(name: &str) -> Result<(), EditError> {
504. 504 if name.is_empty()
505. 505 || name.chars().count() > 31
506. 506 || name.contains(['[', ']', ':', '*', '?', '/', '\\'])
507. 507 || name.starts_with('\'')
508. 508 || name.ends_with('\'')
509. 509 {
510. 510 return fail(format!(
511. 511 "{name:?} is not a valid sheet name (1–31 characters, none of [ ] : * ? / \\, not starting or ending with ')"
512. 512 ));
513. 513 }
514. 514 Ok(())
515. 515}
516. 516
517. 517fn refuse_locked_structure(book: &Book) -> Result<(), EditError> {
518. 518 if let Some(protection) = book.tree.children(0, "workbookProtection").next()
519. 519 && book.tree.nodes[protection]
520. 520 .element
521. 521 .attr("lockStructure")
522. 522 .is_some_and(|value| value == "1" || value == "true")
523. 523 {
524. 524 return fail(
525. 525 "the workbook structure is protected; the owner must remove the protection first",
526. 526 );
527. 527 }
528. 528 Ok(())
529. 529}
530. 530
531. 531pub(super) fn add_sheet<R2: Read + Seek>(
532. 532 work: &mut Work<'_, R2>,
533. 533 name: &str,
534. 534) -> Result<Outcome, EditError> {
535. 535 let name = name.trim();
536. 536 check_sheet_name(name)?;
537. 537 if work.strict() {
538. 538 return fail("adding a sheet to a Strict workbook is not supported yet");
539. 539 }
540. 540 let book = book(work)?;
541. 541 refuse_locked_structure(&book)?;
542. 542 let mut ids = BTreeSet::new();
543. 543 for sheet in book.tree.descendants(0, "sheet") {
544. 544 let element = &book.tree.nodes[sheet].element;
545. 545 if element
546. 546 .attr("name")
547. 547 .is_some_and(|existing| existing.eq_ignore_ascii_case(name))
548. 548 {
549. 549 return fail(format!("a sheet named {name:?} already exists"));
550. 550 }
551. 551 if let Some(id) = element
552. 552 .attr("sheetId")
553. 553 .and_then(|id| id.parse::<u32>().ok())
554. 554 {
555. 555 ids.insert(id);
556. 556 }
557. 557 }
558. 558 let Some(sheets) = book.tree.children(0, "sheets").next() else {
559. 559 return fail("the workbook has no sheets element");
560. 560 };
561. 561 let Some(r) = book
562. 562 .tree
563. 563 .prefix_for(R)
564. 564 .or_else(|| book.tree.prefix_for(R_STRICT))
565. 565 else {
566. 566 return fail("the workbook does not declare the relationships namespace");
567. 567 };
568. 568 let part = free_part_name(work, "xl/worksheets/sheet", ".xml");
569. 569 work.put(
570. 570 &part,
571. 571 format!(
572. 572 r#"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
573. 573<worksheet xmlns="{S}" xmlns:r="{R}"><sheetData/></worksheet>"#
574. 574 )
575. 575 .into_bytes(),
576. 576 );
577. 577 work.set_override(&part, WORKSHEET_TYPE)?;
578. 578 let main = book.part.clone();
579. 579 let relationship = work.add_relationship(&main, &format!("{R}/worksheet"), &part)?;
580. 580 let sheet_id = ids.last().copied().unwrap_or(0) + 1;
581. 581 let sheets_node = &book.tree.nodes[sheets];
582. 582 let mut splice = Splice::default();
583. 583 splice.insert(
584. 584 sheets_node.inner.end,
585. 585 format!(
586. 586 r#"<{}sheet name="{}" sheetId="{sheet_id}" {r}id="{relationship}"/>"#,
587. 587 book.s,
588. 588 escape_attr(name)
589. 589 ),
590. 590 );
591. 591 work.put(&book.part, splice.apply(&book.bytes, &book.part)?);
592. 592 Ok(Outcome {
593. 593 summary: format!("sheet {name:?} added"),
594. 594 expect: vec![Expect::Section(format!("{}!", quote_sheet(name)))],
595. 595 created: Vec::new(),
596. 596 })
597. 597}
598. 598
599. 599// ---- names, formats and widths (docs/design/72, "Creating from scratch") ---
600. 600
601. 601fn refuse_protected_sheet(tree: &Tree, name: &str) -> Result<(), EditError> {
602. 602 if let Some(protection) = tree.descendants(0, "sheetProtection").next()
603. 603 && tree.nodes[protection]
604. 604 .element
605. 605 .attr("sheet")
606. 606 .is_some_and(|value| value == "1" || value == "true")
607. 607 {
608. 608 return fail(format!(
609. 609 "sheet {name:?} is protected; the owner must remove the protection before it can be edited"
610. 610 ));
611. 611 }
612. 612 Ok(())
613. 613}
614. 614
615. 615/// Whether XML text names `sheet` the way a formula, a defined name, a chart
616. 616/// series or a pivot source does (`Sheet1!A1`, `'My sheet'!A1`,
617. 617/// `Sheet1:Sheet3!A1`, `sheet="Sheet1"`), ignoring case as Excel does.
618. 618fn names_sheet(text: &str, sheet: &str) -> bool {
619. 619 let text = text
620. 620 .replace("'", "'")
621. 621 .replace(""", "\"")
622. 622 .replace("&", "&")
623. 623 .to_lowercase();
624. 624 let sheet = sheet.to_lowercase();
625. 625 let quoted = sheet.replace('\'', "''");
626. 626 if text.contains(&format!("'{quoted}'!"))
627. 627 || text.contains(&format!("'{quoted}:"))
628. 628 || text.contains(&format!(":{quoted}'!"))
629. 629 || text.contains(&format!("sheet=\"{sheet}\""))
630. 630 {
631. 631 return true;
632. 632 }
633. 633 let name_char = |c: char| c.is_alphanumeric() || c == '_' || c == '.';
634. 634 let starts_a_name = |at: usize| text[..at].chars().next_back().is_none_or(|c| !name_char(c));
635. 635 let bare = format!("{sheet}!");
636. 636 if text
637. 637 .match_indices(bare.as_str())
638. 638 .any(|(at, _)| starts_a_name(at))
639. 639 {
640. 640 return true;
641. 641 }
642. 642 // The first sheet of a 3-D reference, `Sheet1:Sheet3!A1`; a namespace
643. 643 // prefix such as `r:id` is not followed by a name and `!`.
644. 644 let range = format!("{sheet}:");
645. 645 text.match_indices(range.as_str()).any(|(at, _)| {
646. 646 let rest = &text[at + range.len()..];
647. 647 let name = rest.chars().take_while(|c| name_char(*c)).count();
648. 648 starts_a_name(at) && name > 0 && rest.chars().nth(name) == Some('!')
649. 649 })
650. 650}
651. 651
652. 652pub(super) fn rename_sheet<R2: Read + Seek>(
653. 653 work: &mut Work<'_, R2>,
654. 654 sheet: &str,
655. 655 name: &str,
656. 656) -> Result<Outcome, EditError> {
657. 657 let name = name.trim();
658. 658 check_sheet_name(name)?;
659. 659 let book = book(work)?;
660. 660 refuse_locked_structure(&book)?;
661. 661 let wanted = sheet.trim().trim_matches('\'');
662. 662 let mut found = None;
663. 663 let mut names = Vec::new();
664. 664 for node in book.tree.descendants(0, "sheet") {
665. 665 let Some(existing) = book.tree.nodes[node].element.attr("name") else {
666. 666 continue;
667. 667 };
668. 668 if existing.eq_ignore_ascii_case(wanted) {
669. 669 found = Some((node, existing.to_string()));
670. 670 } else if existing.eq_ignore_ascii_case(name) {
671. 671 return fail(format!("a sheet named {existing:?} already exists"));
672. 672 }
673. 673 names.push(existing.to_string());
674. 674 }
675. 675 let Some((node, old)) = found else {
676. 676 return fail(format!("no sheet {sheet:?}; sheets: {}", names.join(", ")));
677. 677 };
678. 678 if old == name {
679. 679 return fail(format!("sheet {old:?} already has that name"));
680. 680 }
681. 681 let mut referring = Vec::new();
682. 682 for part in work.part_names() {
683. 683 let lower = part.to_ascii_lowercase();
684. 684 let text_only = lower.contains("sharedstrings")
685. 685 || lower.contains("comments")
686. 686 || lower.starts_with("docprops/");
687. 687 if text_only || !(lower.ends_with(".xml") || lower.ends_with(".vml")) {
688. 688 continue;
689. 689 }
690. 690 let bytes = work.get(&part)?;
691. 691 if names_sheet(&String::from_utf8_lossy(&bytes), &old) {
692. 692 referring.push(part);
693. 693 }
694. 694 }
695. 695 if !referring.is_empty() {
696. 696 referring.sort();
697. 697 return fail(format!(
698. 698 "sheet {old:?} is named in {} (a formula, defined name, chart or other reference), which renaming would break; rename the sheet before writing anything that names it, or keep its name",
699. 699 referring.join(", ")
700. 700 ));
701. 701 }
702. 702 let element = &book.tree.nodes[node];
703. 703 let mut attributes = element.element.attributes.clone();
704. 704 set_attribute(&mut attributes, "name", name.to_string());
705. 705 let end = if element.is_empty_element() {
706. 706 element.span.end
707. 707 } else {
708. 708 element.inner.start
709. 709 };
710. 710 let mut splice = Splice::default();
711. 711 splice.replace(
712. 712 element.span.start..end,
713. 713 start_tag(
714. 714 &element.element.name,
715. 715 &attributes,
716. 716 element.is_empty_element(),
717. 717 ),
718. 718 );
719. 719 work.put(&book.part, splice.apply(&book.bytes, &book.part)?);
720. 720 Ok(Outcome {
721. 721 summary: format!("sheet {old:?} renamed {name:?}"),
722. 722 expect: vec![
723. 723 Expect::Section(format!("{}!", quote_sheet(name))),
724. 724 Expect::NoSection(format!("{}!", quote_sheet(&old))),
725. 725 ],
726. 726 created: Vec::new(),
727. 727 })
728. 728}
729. 729
730. 730fn set_attribute(attributes: &mut Vec<(String, String)>, key: &str, value: String) {
731. 731 match attributes.iter_mut().find(|(existing, _)| existing == key) {
732. 732 Some((_, existing)) => *existing = value,
733. 733 None => attributes.push((key.to_string(), value)),
734. 734 }
735. 735}
736. 736
737. 737/// Formatting `format_cells` applies to each cell; `None` leaves that part
738. 738/// of a cell's format as it is. `fill` is six hex digits, `number_format`
739. 739/// an Excel format code.
740. 740#[derive(Debug, Clone, PartialEq, Default)]
741. 741pub(crate) struct Format {
742. 742 pub bold: Option<bool>,
743. 743 pub italic: Option<bool>,
744. 744 pub number_format: Option<String>,
745. 745 pub fill: Option<String>,
746. 746 pub wrap: Option<bool>,
747. 747}
748. 748
749. 749impl Format {
750. 750 fn is_empty(&self) -> bool {
751. 751 self.bold.is_none()
752. 752 && self.italic.is_none()
753. 753 && self.number_format.is_none()
754. 754 && self.fill.is_none()
755. 755 && self.wrap.is_none()
756. 756 }
757. 757
758. 758 fn describe(&self) -> String {
759. 759 let mut parts = Vec::new();
760. 760 let flag = |on: bool, what: &str| {
761. 761 if on {
762. 762 what.to_string()
763. 763 } else {
764. 764 format!("not {what}")
765. 765 }
766. 766 };
767. 767 parts.extend(self.bold.map(|on| flag(on, "bold")));
768. 768 parts.extend(self.italic.map(|on| flag(on, "italic")));
769. 769 parts.extend(
770. 770 self.number_format
771. 771 .as_ref()
772. 772 .map(|code| format!("number format {code}")),
773. 773 );
774. 774 parts.extend(self.fill.as_ref().map(|fill| format!("fill #{fill}")));
775. 775 parts.extend(self.wrap.map(|on| flag(on, "wrapped")));
776. 776 parts.join(", ")
777. 777 }
778. 778}
779. 779
780. 780/// Number formats every Excel version knows by id without declaring them.
781. 781const BUILTIN_FORMATS: &[(u32, &str)] = &[
782. 782 (0, "General"),
783. 783 (1, "0"),
784. 784 (2, "0.00"),
785. 785 (3, "#,##0"),
786. 786 (4, "#,##0.00"),
787. 787 (9, "0%"),
788. 788 (10, "0.00%"),
789. 789 (11, "0.00E+00"),
790. 790 (12, "# ?/?"),
791. 791 (13, "# ??/??"),
792. 792 (49, "@"),
793. 793];
794. 794
795. 795/// Most cells one `format_cells` call formats.
796. 796const MAX_FORMAT_CELLS: u64 = 20_000;
797. 797
798. 798/// `A1:D4` or `B4`, optionally after `Sheet!` → (first column, first row,
799. 799/// last column, last row).
800. 800fn cell_range(text: &str) -> Result<(u32, u32, u32, u32), EditError> {
801. 801 let text = text.trim();
802. 802 let text = text.rsplit_once('!').map_or(text, |(_, cells)| cells);
803. 803 let (from, to) = text.split_once(':').unwrap_or((text, text));
804. 804 let (first_column, first_row) = address(from)?;
805. 805 let (last_column, last_row) = address(to)?;
806. 806 Ok((
807. 807 first_column.min(last_column),
808. 808 first_row.min(last_row),
809. 809 first_column.max(last_column),
810. 810 first_row.max(last_row),
811. 811 ))
812. 812}
813. 813
814. 814/// `#1f4e79`, `1F4E79` or `FF1F4E79` → `1F4E79`.
815. 815fn fill_colour(text: &str) -> Result<String, EditError> {
816. 816 let hex = text.trim().trim_start_matches('#').to_ascii_uppercase();
817. 817 let hex = if hex.len() == 8 && hex.starts_with("FF") {
818. 818 hex[2..].to_string()
819. 819 } else {
820. 820 hex
821. 821 };
822. 822 if hex.len() == 6 && hex.chars().all(|c| c.is_ascii_hexdigit()) {
823. 823 Ok(hex)
824. 824 } else {
825. 825 fail(format!(
826. 826 "fill {text:?} is not a colour; give six hex digits such as \"D9E2F3\""
827. 827 ))
828. 828 }
829. 829}
830. 830
831. 831/// The workbook's styles part and the entries `format_cells` adds to it.
832. 832struct Styles {
833. 833 part: String,
834. 834 bytes: Vec<u8>,
835. 835 tree: Tree,
836. 836 s: String,
837. 837 fonts: Vec<usize>,
838. 838 fills: Vec<usize>,
839. 839 xfs: Vec<usize>,
840. 840 custom: BTreeMap<u32, String>,
841. 841 new_fonts: Vec<String>,
842. 842 new_fills: Vec<String>,
843. 843 new_formats: Vec<(u32, String)>,
844. 844 new_xfs: Vec<String>,
845. 845}
846. 846
847. 847impl Styles {
848. 848 fn load<R2: Read + Seek>(work: &mut Work<'_, R2>) -> Result<Self, EditError> {
849. 849 let main = work.main_part();
850. 850 let Some(part) = work.related(&main, "styles")? else {
851. 851 return fail("the workbook has no styles part, so no format can be applied");
852. 852 };
853. 853 let bytes = work.get(&part)?;
854. 854 let tree = Tree::parse(&bytes, &part, work.limits())?;
855. 855 let s = prefix(&tree)?;
856. 856 let list = |local: &str, item: &str| -> Vec<usize> {
857. 857 tree.children(0, local)
858. 858 .next()
859. 859 .map(|list| tree.children(list, item).collect())
860. 860 .unwrap_or_default()
861. 861 };
862. 862 let fonts = list("fonts", "font");
863. 863 let fills = list("fills", "fill");
864. 864 let xfs = list("cellXfs", "xf");
865. 865 if xfs.is_empty() {
866. 866 return fail("the styles part has no cell formats to build on");
867. 867 }
868. 868 let custom = list("numFmts", "numFmt")
869. 869 .into_iter()
870. 870 .filter_map(|format| {
871. 871 let element = &tree.nodes[format].element;
872. 872 Some((
873. 873 element.attr("numFmtId")?.parse::<u32>().ok()?,
874. 874 element.attr("formatCode")?.to_string(),
875. 875 ))
876. 876 })
877. 877 .collect();
878. 878 Ok(Self {
879. 879 part,
880. 880 bytes,
881. 881 tree,
882. 882 s,
883. 883 fonts,
884. 884 fills,
885. 885 xfs,
886. 886 custom,
887. 887 new_fonts: Vec::new(),
888. 888 new_fills: Vec::new(),
889. 889 new_formats: Vec::new(),
890. 890 new_xfs: Vec::new(),
891. 891 })
892. 892 }
893. 893
894. 894 fn slice(&self, node: usize) -> String {
895. 895 String::from_utf8_lossy(&self.bytes[self.tree.nodes[node].span.clone()]).into_owned()
896. 896 }
897. 897
898. 898 fn is_on(&self, node: usize, local: &str) -> bool {
899. 899 self.tree.children(node, local).next().is_some_and(|child| {
900. 900 !matches!(
901. 901 self.tree.nodes[child].element.attr("val"),
902. 902 Some("0" | "false")
903. 903 )
904. 904 })
905. 905 }
906. 906
907. 907 fn number(&self, node: usize, attribute: &str) -> usize {
908. 908 self.tree.nodes[node]
909. 909 .element
910. 910 .attr(attribute)
911. 911 .and_then(|value| value.parse().ok())
912. 912 .unwrap_or(0)
913. 913 }
914. 914
915. 915 /// The index of a cell format like `old` with `format` applied, adding
916. 916 /// the font, fill, number format and cell format that needs.
917. 917 fn derive(&mut self, old: usize, format: &Format) -> Result<usize, EditError> {
918. 918 let Some(&xf) = self.xfs.get(old) else {
919. 919 return fail(format!(
920. 920 "a cell uses cell format {old}, which the styles part does not have"
921. 921 ));
922. 922 };
923. 923 let s = self.s.clone();
924. 924 let mut attributes = self.tree.nodes[xf].element.attributes.clone();
925. 925 if format.bold.is_some() || format.italic.is_some() {
926. 926 let font_index = self.number(xf, "fontId");
927. 927 let Some(&font) = self.fonts.get(font_index) else {
928. 928 return fail(format!(
929. 929 "cell format {old} names a font the styles part does not have"
930. 930 ));
931. 931 };
932. 932 let bold = format.bold.unwrap_or_else(|| self.is_on(font, "b"));
933. 933 let italic = format.italic.unwrap_or_else(|| self.is_on(font, "i"));
934. 934 let mut inner = String::new();
935. 935 if bold {
936. 936 inner.push_str(&format!("<{s}b/>"));
937. 937 }
938. 938 if italic {
939. 939 inner.push_str(&format!("<{s}i/>"));
940. 940 }
941. 941 for child in self.tree.nodes[font].children.clone() {
942. 942 if !matches!(self.tree.nodes[child].local(), "b" | "i") {
943. 943 inner.push_str(&self.slice(child));
944. 944 }
945. 945 }
946. 946 let xml = format!("<{s}font>{inner}</{s}font>");
947. 947 let index = find_or_add(
948. 948 self.fonts.iter().map(|font| self.slice(*font)).collect(),
949. 949 &mut self.new_fonts,
950. 950 xml,
951. 951 );
952. 952 set_attribute(&mut attributes, "fontId", index.to_string());
953. 953 set_attribute(&mut attributes, "applyFont", "1".into());
954. 954 }
955. 955 if let Some(fill) = &format.fill {
956. 956 let xml = format!(
957. 957 r#"<{s}fill><{s}patternFill patternType="solid"><{s}fgColor rgb="FF{fill}"/><{s}bgColor indexed="64"/></{s}patternFill></{s}fill>"#
958. 958 );
959. 959 let index = find_or_add(
960. 960 self.fills.iter().map(|fill| self.slice(*fill)).collect(),
961. 961 &mut self.new_fills,
962. 962 xml,
963. 963 );
964. 964 set_attribute(&mut attributes, "fillId", index.to_string());
965. 965 set_attribute(&mut attributes, "applyFill", "1".into());
966. 966 }
967. 967 if let Some(code) = &format.number_format {
968. 968 let id = match BUILTIN_FORMATS.iter().find(|(_, known)| known == code) {
969. 969 Some((id, _)) => *id,
970. 970 None => match self
971. 971 .custom
972. 972 .iter()
973. 973 .map(|(id, known)| (*id, known.clone()))
974. 974 .chain(self.new_formats.iter().cloned())
975. 975 .find(|(_, known)| known == code)
976. 976 {
977. 977 Some((id, _)) => id,
978. 978 None => {
979. 979 let id = self
980. 980 .custom
981. 981 .keys()
982. 982 .copied()
983. 983 .chain(self.new_formats.iter().map(|(id, _)| *id))
984. 984 .max()
985. 985 .unwrap_or(163)
986. 986 .max(163)
987. 987 + 1;
988. 988 self.new_formats.push((id, code.clone()));
989. 989 id
990. 990 }
991. 991 },
992. 992 };
993. 993 set_attribute(&mut attributes, "numFmtId", id.to_string());
994. 994 set_attribute(&mut attributes, "applyNumberFormat", "1".into());
995. 995 }
996. 996 let mut children = String::new();
997. 997 let alignment = self.tree.children(xf, "alignment").next();
998. 998 match (format.wrap, alignment) {
999. 999 (Some(wrap), Some(alignment)) => {
1000. 1000 let element = &self.tree.nodes[alignment].element;