88f5a00e389b
xlsx.rs
//! Excel workbooks (`.xlsx`) → a story holding one table: the first worksheet's used range, cell
//! values as shown text (shared and inline strings, numbers, booleans). Data merge uses
//! [`records`], which keeps empty cells and can name a sheet.
use std::collections::BTreeMap;
use designcraft_doc::{CharFormat, ParaFormat, Story, StoryId, Table};
use crate::{ImportError, Imported};
fn display(value: craft_xlsx::Value) -> String {
match value {
craft_xlsx::Value::Empty => String::new(),
craft_xlsx::Value::Number(n) => general(n),
craft_xlsx::Value::Text(s) => s.to_string(),
craft_xlsx::Value::Bool(b) => if b { "TRUE" } else { "FALSE" }.into(),
craft_xlsx::Value::Error(e) => e.to_string(),
}
}
fn sheet_cells(
bytes: &[u8],
wanted: Option<&str>,
max_cells: usize,
max_text_bytes: usize,
) -> Result<(Vec<(usize, usize, String)>, bool, Vec<String>), ImportError> {
let mut package = craft_xlsx::Package::open(bytes, &craft_xlsx::Limits::default()).map_err(|e| ImportError::Corrupt(e.to_string()))?;
let workbook = package.workbook().map_err(|e| ImportError::Corrupt(e.to_string()))?;
let entry = match wanted {
Some(name) => workbook.sheets.iter().find(|sheet| sheet.name == name),
None => workbook.sheets.first(),
}
.ok_or_else(|| ImportError::Corrupt(wanted.map_or("the workbook has no sheets".into(), |name| format!("no worksheet \"{name}\""))))?;
let date_1904 = workbook.date_1904;
let strings = package.shared_strings().map_err(|e| ImportError::Corrupt(e.to_string()))?;
let mut cells = Vec::new();
let mut text_bytes = 0usize;
let mut cut = false;
let result = package.sheet(entry, |row| {
for cell in row.cells {
let value = match cell.value {
// A value that doesn't read as its type says (`inf`, `n/a` in a number cell) is
// shown as written, as the importer did before the shared reader.
craft_xlsx::CellValue::Invalid(raw) => raw,
value => display(value.resolve(&strings, date_1904)),
};
text_bytes = text_bytes.saturating_add(value.len());
if cells.len() >= max_cells || text_bytes > max_text_bytes {
cut = true;
return Err(craft_xlsx::Error::TooLarge("worksheet text or cell limit reached".into()));
}
cells.push((cell.at.row as usize, cell.at.col as usize, value));
}
Ok(())
});
if let Err(error) = result
&& !cut
{
return Err(ImportError::Corrupt(error.to_string()));
}
let warnings = package.warnings().to_vec();
Ok((cells, cut, warnings))
}
fn general(f: f64) -> String {
let r = format!("{f:.14e}").parse::<f64>().unwrap_or(f);
let a = r.abs();
if r == 0.0 {
"0".into()
} else if (1e-9..1e15).contains(&a) {
r.to_string()
} else {
let s = format!("{r:E}");
match s.split_once('E') {
Some((m, e)) if !e.starts_with('-') => format!("{m}E+{e}"),
_ => s,
}
}
}
#[cfg(test)]
fn cell_ref(reference: &str) -> Option<(usize, usize)> {
craft_xlsx::CellRef::parse(reference).map(|cell| (cell.row as usize, cell.col as usize))
}
fn header_span(cells: &[(usize, usize, String)], header_row: usize) -> Option<(usize, usize)> {
cells.iter().filter(|(r, _, _)| *r == header_row).fold(None, |span, (_, c, _)| match span {
None => Some((*c, *c)),
Some((lo, hi)) => Some((lo.min(*c), hi.max(*c))),
})
}
fn place_row(cols: &[(usize, String)], min_c: usize, width: usize) -> (Vec<String>, Vec<String>) {
let mut row = vec![String::new(); width];
let mut extra = Vec::new();
for (c, value) in cols {
match c.checked_sub(min_c).filter(|at| *at < width) {
Some(at) => row[at] = value.clone(),
None if !value.is_empty() => extra.push(value.clone()),
None => {}
}
}
(row, extra)
}
/// One worksheet as a grid of cached cell text, empty cells kept, for data merge.
/// The first used row is the header. Later rows that are entirely empty are left out.
/// Cells past the header are kept so the caller can warn. Over 200 columns or 100,000
/// data rows is an error and the grid is not built.
/// What [`records`] reads of a sheet at most: the text of its cells and their number (the data
/// merge limits, rows × columns, with room for cells outside the table).
const MAX_RECORD_TEXT_BYTES: usize = 256 << 20;
const MAX_RECORD_CELLS: usize = 2 * crate::DATA_MERGE_MAX_ROWS * crate::DATA_MERGE_MAX_COLS;
pub fn records(bytes: &[u8], sheet: Option<&str>) -> Result<Vec<Vec<String>>, ImportError> {
let (cells, cut, _) = sheet_cells(bytes, sheet, MAX_RECORD_CELLS, MAX_RECORD_TEXT_BYTES)?;
if cut {
return Err(ImportError::Corrupt(format!(
"the sheet holds more than {} MB of text or {MAX_RECORD_CELLS} cells; the limit for a data source",
MAX_RECORD_TEXT_BYTES >> 20
)));
}
if cells.is_empty() {
return Err(ImportError::Corrupt("the worksheet is empty".into()));
}
let header_row = cells.iter().map(|cell| cell.0).min().unwrap_or(0);
let Some((min_c, max_c)) = header_span(&cells, header_row) else {
return Err(ImportError::Corrupt("the worksheet has no header".into()));
};
let width = max_c.saturating_sub(min_c).saturating_add(1);
if width > crate::DATA_MERGE_MAX_COLS {
return Err(ImportError::Corrupt(format!("the sheet has {width} columns; the limit is {}", crate::DATA_MERGE_MAX_COLS)));
}
let mut by_row: BTreeMap<usize, Vec<(usize, String)>> = BTreeMap::new();
for (r, c, v) in cells {
by_row.entry(r).or_default().push((c, v));
}
let data_rows = by_row.iter().filter(|(r, cols)| **r != header_row && cols.iter().any(|(_, v)| !v.is_empty())).count();
if data_rows > crate::DATA_MERGE_MAX_ROWS {
return Err(ImportError::Corrupt(format!("the sheet has {data_rows} data rows; the limit is {}", crate::DATA_MERGE_MAX_ROWS)));
}
let mut out = Vec::with_capacity(data_rows.saturating_add(1));
for (r, cols) in &by_row {
let (mut row, extra) = place_row(cols, min_c, width);
if *r != header_row && row.iter().all(String::is_empty) && extra.is_empty() {
continue;
}
row.extend(extra);
out.push(row);
}
Ok(out)
}
pub fn import(bytes: &[u8]) -> Result<Imported, ImportError> {
const MAX_TEXT_BYTES: usize = 64 << 20;
const MAX_COLLECTED_CELLS: usize = 400_000;
let (cells, cut, mut warnings) = sheet_cells(bytes, None, MAX_COLLECTED_CELLS, MAX_TEXT_BYTES)?;
if cut {
warnings.push(format!(
"the worksheet holds more than {} cells or {} MB of text: the rest was not read",
MAX_COLLECTED_CELLS,
MAX_TEXT_BYTES / (1 << 20)
));
}
if cells.is_empty() {
warnings.push("the worksheet is empty".into());
}
const MAX_CELLS: usize = 100_000;
const MAX_COLS: usize = 1_000;
let (r0, c0) = cells.iter().fold((usize::MAX, usize::MAX), |(r, c), (a, b, _)| (r.min(*a), c.min(*b)));
let (r1, c1) = cells.iter().fold((0, 0), |(r, c), (a, b, _)| (r.max(*a), c.max(*b)));
let (nrows, ncols) = if cells.is_empty() { (1, 1) } else { ((r1 - r0).saturating_add(1), (c1 - c0).saturating_add(1)) };
let ncols_kept = ncols.min(MAX_COLS);
if ncols_kept < ncols {
warnings.push(format!("only the first {ncols_kept} columns were placed"));
}
let nrows_kept = nrows.min((MAX_CELLS / ncols_kept).max(1));
if nrows_kept < nrows {
warnings.push(format!("only the first {nrows_kept} rows were placed"));
}
let mut rows: Vec<Vec<String>> = vec![vec![String::new(); ncols_kept]; nrows_kept];
for (r, c, v) in cells.into_iter().filter(|(_, _, value)| !value.is_empty()) {
if let Some(cell) = rows.get_mut(r - r0).and_then(|row| row.get_mut(c - c0)) {
*cell = v;
}
}
let mut story = Story::new(StoryId(0));
let width = 72.0 * ncols_kept as f64;
let table = Table::from_strings(1, &rows, width, &ParaFormat::default(), &CharFormat::default());
story.insert_table(0, table);
Ok(Imported { story, para_styles: vec![], char_styles: vec![], warnings })
}
#[cfg(test)]
mod tests {
use std::io::Write;
use super::*;
fn workbook() -> Vec<u8> {
workbook_with(
r#"<worksheet><sheetData><row r="2"><c r="B2" t="s"><v>0</v></c><c r="C2" t="s"><v>1</v></c></row><row r="3"><c r="B3" t="s"><v>2</v></c><c r="C3"><v>3.5</v></c></row><row r="4"><c r="B4" t="inlineStr"><is><t>Cake</t></is></c><c r="C4"><v>12.0</v></c></row></sheetData></worksheet>"#,
)
}
fn workbook_with(sheet: &str) -> Vec<u8> {
let mut buf = std::io::Cursor::new(Vec::new());
{
let mut z = zip::ZipWriter::new(&mut buf);
let o = zip::write::SimpleFileOptions::default();
for (name, body) in [
(
"xl/workbook.xml",
r#"<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="Prices" sheetId="1" r:id="rId1"/></sheets></workbook>"#,
),
(
"xl/_rels/workbook.xml.rels",
r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="x" Target="worksheets/sheet1.xml"/></Relationships>"#,
),
("xl/sharedStrings.xml", r#"<sst><si><t>Item</t></si><si><t>Price</t></si><si><r><t>Te</t></r><r><t>a</t></r></si></sst>"#),
("xl/worksheets/sheet1.xml", sheet),
] {
z.start_file(name, o).unwrap();
z.write_all(body.as_bytes()).unwrap();
}
z.finish().unwrap();
}
buf.into_inner()
}
#[test]
fn cell_ref_rejects_signed_or_malformed_rows() {
assert_eq!(cell_ref("B12"), Some((11, 1)));
assert_eq!(cell_ref("A+1"), None);
assert_eq!(cell_ref("A-1"), None);
assert_eq!(cell_ref("A"), None);
assert_eq!(cell_ref("A1x"), None);
}
/// A `t="s"` cell materialises its own copy of the shared string, so one big string with many
/// references multiplies: the audit's case is a 1 MB string and 3M cells, terabytes, built
/// before the grid cap applies (#1584). Collection now carries its own byte budget.
#[test]
fn a_shared_string_referenced_many_times_does_not_multiply() {
// A 64 KB shared string referenced 2,000 times would be 128 MB of cell text; the budget
// is 64 MB, so collection stops and says so.
let big = "y".repeat(64 * 1024);
let cells: String = (1..=2_000).map(|r| format!(r#"<row r="{r}"><c r="A{r}" t="s"><v>0</v></c></row>"#)).collect();
let mut buf = std::io::Cursor::new(Vec::new());
{
let mut z = zip::ZipWriter::new(&mut buf);
let o = zip::write::SimpleFileOptions::default();
for (name, body) in [
(
"xl/workbook.xml",
r#"<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="Big" sheetId="1" r:id="rId1"/></sheets></workbook>"#.to_string(),
),
(
"xl/_rels/workbook.xml.rels",
r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="x" Target="worksheets/sheet1.xml"/></Relationships>"#.to_string(),
),
("xl/sharedStrings.xml", format!("<sst><si><t>{big}</t></si></sst>")),
("xl/worksheets/sheet1.xml", format!("<worksheet><sheetData>{cells}</sheetData></worksheet>")),
] {
z.start_file(name, o).unwrap();
z.write_all(body.as_bytes()).unwrap();
}
z.finish().unwrap();
}
let imp = import(&buf.into_inner()).unwrap();
let table = imp.story.tables.values().next().expect("a table");
let text: usize = (0..table.nrows())
.flat_map(|r| (0..table.ncols()).map(move |c| (r, c)))
.filter_map(|(r, c)| table.cell(r, c).map(|cell| cell.text.text.len()))
.sum();
assert!(text <= 64 << 20, "the table holds at most the budget: {text} bytes");
assert!(imp.warnings.iter().any(|w| w.contains("was not read")), "the report says the sheet was cut: {:?}", imp.warnings);
// An ordinary workbook is unaffected and reports nothing of the sort.
let plain = import(&workbook()).unwrap();
assert!(!plain.warnings.iter().any(|w| w.contains("was not read")), "{:?}", plain.warnings);
}
#[test]
fn first_sheet_used_range_becomes_a_table() {
let imp = import(&workbook()).unwrap();
let t = imp.story.tables.values().next().expect("a table");
assert_eq!((t.nrows(), t.ncols()), (3, 2), "the used range B2:C4");
let cell = |r, c| t.cell(r, c).unwrap().text.text.clone();
assert_eq!([cell(0, 0), cell(0, 1), cell(1, 0), cell(1, 1), cell(2, 0), cell(2, 1)], ["Item", "Price", "Tea", "3.5", "Cake", "12"]);
assert_eq!(cell_ref("AA10"), Some((9, 26)));
}
/// A cell far away from the others used to allocate the whole used range (billions of cells)
/// before the cell cap applied; a long column name overflowed the column number.
#[test]
fn far_away_cell_references_are_capped() {
let wb = workbook_with(
r#"<worksheet><sheetData><row><c r="A1" t="inlineStr"><is><t>a</t></is></c><c r="A999999999" t="inlineStr"><is><t>z</t></is></c><c r="XFDXFDXFDXFDXFDXFD1" t="inlineStr"><is><t>far</t></is></c></row></sheetData></worksheet>"#,
);
let imp = import(&wb).unwrap();
let t = imp.story.tables.values().next().expect("a table");
assert!(t.nrows() * t.ncols() <= 100_000);
assert!(!imp.warnings.is_empty());
assert_eq!(cell_ref("XFDXFDXFDXFDXFDXFD1"), None);
}
fn sheets(parts: &[(&str, &str)]) -> Vec<u8> {
let mut buf = std::io::Cursor::new(Vec::new());
{
let mut z = zip::ZipWriter::new(&mut buf);
let o = zip::write::SimpleFileOptions::default();
for (name, body) in parts {
z.start_file(*name, o).unwrap();
z.write_all(body.as_bytes()).unwrap();
}
z.finish().unwrap();
}
buf.into_inner()
}
#[test]
fn records_keep_empty_cells_cached_values_and_a_named_sheet() {
let first = workbook();
let rows = records(&first, None).unwrap();
assert_eq!(rows[0], vec!["Item".to_string(), "Price".to_string()]);
assert_eq!(rows[1], vec!["Tea".to_string(), "3.5".to_string()]);
assert_eq!(rows[2][1], "12");
let book = sheets(&[
(
"xl/workbook.xml",
r#"<workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="Ignore" sheetId="1" r:id="rId1"/><sheet name="People" sheetId="2" r:id="rId2"/></sheets></workbook>"#,
),
(
"xl/_rels/workbook.xml.rels",
r#"<Relationships><Relationship Id="rId1" Type="x" Target="worksheets/sheet1.xml"/><Relationship Id="rId2" Type="x" Target="worksheets/sheet2.xml"/></Relationships>"#,
),
(
"xl/worksheets/sheet1.xml",
r#"<worksheet><sheetData><row r="1"><c r="A1" t="inlineStr"><is><t>Nope</t></is></c></row></sheetData></worksheet>"#,
),
(
"xl/worksheets/sheet2.xml",
r#"<worksheet><sheetData><row r="1"><c r="A1" t="inlineStr"><is><t>Name</t></is></c><c r="B1" t="inlineStr"><is><t>Qty</t></is></c><c r="C1" t="inlineStr"><is><t>On</t></is></c><c r="D1" t="inlineStr"><is><t>When</t></is></c></row><row r="2"><c r="A2" t="inlineStr"><is><t>Ada</t></is></c><c r="B2"><v>12.0</v></c><c r="C2" t="b"><v>1</v></c><c r="D2"><v>44927</v></c></row><row r="3"><c r="A3" t="inlineStr"><is><t>Grace</t></is></c><c r="C3"><f>1+1</f></c></row><row r="4"><c r="B4"><v>1</v></c></row></sheetData></worksheet>"#,
),
]);
let rows = records(&book, Some("People")).unwrap();
assert_eq!(rows[0], vec!["Name".to_string(), "Qty".to_string(), "On".to_string(), "When".to_string()]);
assert_eq!(rows[1], vec!["Ada".to_string(), "12".to_string(), "TRUE".to_string(), "44927".to_string()]);
// Formula with no cached value is empty. The short row is padded. The all-empty gap is skipped
// only when every cell is empty; row 4 has a Qty, so it stays, and Name is empty.
assert_eq!(rows[2], vec!["Grace".to_string(), String::new(), String::new(), String::new()]);
assert_eq!(rows[3][0], "");
assert_eq!(rows[3][1], "1");
assert!(records(&book, Some("Missing")).is_err());
}
#[test]
fn phonetic_guide_text_is_not_cell_text() {
let book = sheets(&[
(
"xl/workbook.xml",
r#"<workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="S" sheetId="1" r:id="rId1"/></sheets></workbook>"#,
),
("xl/_rels/workbook.xml.rels", r#"<Relationships><Relationship Id="rId1" Type="x" Target="worksheets/sheet1.xml"/></Relationships>"#),
(
"xl/sharedStrings.xml",
r#"<sst><si><t>日本</t><rPh sb="0" eb="2"><t>ニホン</t></rPh><phoneticPr fontId="1"/></si><si><r><t>東</t></r><r><t>京</t></r><rPh sb="0" eb="2"><t>トウキョウ</t></rPh></si></sst>"#,
),
(
"xl/worksheets/sheet1.xml",
r#"<worksheet><sheetData><row r="1"><c r="A1" t="s"><v>0</v></c><c r="B1" t="s"><v>1</v></c><c r="C1" t="inlineStr"><is><t>大阪</t><rPh sb="0" eb="2"><t>オオサカ</t></rPh><phoneticPr/></is></c></row></sheetData></worksheet>"#,
),
]);
let want = ["日本", "東京", "大阪"].map(String::from).to_vec();
assert_eq!(records(&book, None).unwrap(), vec![want.clone()]);
let imp = import(&book).unwrap();
let t = imp.story.tables.values().next().expect("a table");
let got: Vec<String> = (0..3).map(|c| t.cell(0, c).unwrap().text.text.clone()).collect();
assert_eq!(got, want);
}
fn one_value(cell: &str) -> Vec<u8> {
workbook_with(&format!(
r#"<worksheet><sheetData><row r="1"><c r="A1" t="inlineStr"><is><t>Value</t></is></c></row><row r="2">{cell}</row></sheetData></worksheet>"#
))
}
#[test]
fn numeric_values_keep_their_precision_in_tables_and_records() {
for text in ["1e-12", "-0.0000000000123", "1.23456789012345", "1e300", "12.0", "-2.5e-300"] {
let book = one_value(&format!(r#"<c r="A2"><v>{text}</v></c>"#));
let placed = crate::import("values.xlsx", &book).unwrap();
let table = placed.story.tables.values().next().unwrap();
let rows = crate::xlsx_records(&book, None).unwrap();
for got in [&table.cell(1, 0).unwrap().text.text, &rows[1][0]] {
assert_eq!(got.parse::<f64>().unwrap(), text.parse::<f64>().unwrap(), "cell {text} became {got}");
}
}
}
#[test]
fn numeric_values_show_as_excel_general_format() {
for (text, shown) in [
("0.30000000000000004", "0.3"),
("2.0000000000000004", "2"),
("12.0", "12"),
("-0.5", "-0.5"),
("0", "0"),
("123456789012345", "123456789012345"),
("1e15", "1E+15"),
("1e300", "1E+300"),
("1e-12", "1E-12"),
("-1.5e-10", "-1.5E-10"),
("inf", "inf"),
] {
let book = one_value(&format!(r#"<c r="A2"><v>{text}</v></c>"#));
let rows = crate::xlsx_records(&book, None).unwrap();
assert_eq!(rows[1][0], shown, "cell {text}");
}
}
#[test]
fn number_cells_that_are_not_numbers_show_as_written() {
for text in ["inf", "Infinity", "NaN", "n/a", "12 apples"] {
let book = one_value(&format!(r#"<c r="A2"><v>{text}</v></c>"#));
let rows = crate::xlsx_records(&book, None).unwrap();
assert_eq!(rows[1][0], text, "cell {text}");
}
}
#[test]
fn cached_string_values_are_not_reformatted_as_numbers() {
for text in ["00123", "1e3", "12.0", " 42 "] {
let book = one_value(&format!(r#"<c r="A2" t="str"><f>"{text}"</f><v>{text}</v></c>"#));
let placed = crate::import("values.xlsx", &book).unwrap();
let table = placed.story.tables.values().next().unwrap();
let rows = crate::xlsx_records(&book, None).unwrap();
assert_eq!(table.cell(1, 0).unwrap().text.text, text);
assert_eq!(rows[1][0], text);
}
}
}
No file is open. Choose one in the tree, or press Ctrl+O (⌘O on a Mac) to find one by name.
Syntax highlighting could not load. The unmodified read-only text remains available above.