#![allow(clippy::unwrap_used, clippy::expect_used, clippy::panic)]
use excelerate::coordinate::Row;
use excelerate::edit::{insert_rows, remove_rows};
use excelerate::model::Spreadsheet;
use excelerate::reader::xlsx::read_xlsx_from;
use excelerate::writer::xlsx::write_xlsx_to;
use std::io::Cursor;
fn fixture() -> Vec<u8> {
std::fs::read(concat!(
env!("CARGO_MANIFEST_DIR"),
"/tests/fixtures/table.xlsx"
))
.unwrap()
}
fn row(one_based: u64) -> Row {
Row::from_one_based(one_based).unwrap()
}
fn cycle(book: &Spreadsheet) -> Spreadsheet {
let mut buf = Vec::new();
write_xlsx_to(book, Cursor::new(&mut buf)).expect("workbook writes");
read_xlsx_from(Cursor::new(buf)).expect("what we wrote reads back")
}
#[test]
fn a_table_is_read_with_its_columns_and_style() {
let book = read_xlsx_from(Cursor::new(fixture())).unwrap();
let table = &book.sheets()[0].tables[0];
assert_eq!(table.name, "Sales");
assert_eq!(table.display_name, "Sales");
assert_eq!(table.range.to_string(), "A1:C5");
assert_eq!(
table
.columns
.iter()
.map(|c| c.name.as_str())
.collect::<Vec<_>>(),
["Region", "Quarter", "Sales"]
);
assert_eq!(table.header_row_count, None);
assert_eq!(table.totals_row_count, None);
assert_eq!(
table
.auto_filter
.as_ref()
.map(|f| f.range.to_string())
.as_deref(),
Some("A1:C5")
);
let style = table.style.as_ref().unwrap();
assert_eq!(style.name.as_deref(), Some("TableStyleMedium2"));
assert!(style.show_first_column);
assert!(style.show_row_stripes);
assert!(!style.show_column_stripes);
assert_eq!(table.body().unwrap().to_string(), "A2:C5");
}
#[test]
fn the_table_survives_a_cycle() {
let before = read_xlsx_from(Cursor::new(fixture())).unwrap();
let after = cycle(&before);
assert_eq!(after.sheets()[0].tables, before.sheets()[0].tables);
}
#[test]
fn an_inserted_row_takes_the_table_with_it() {
let mut book = read_xlsx_from(Cursor::new(fixture())).unwrap();
insert_rows(&mut book, 0, row(3), 2).unwrap();
let table = &book.sheets()[0].tables[0];
assert_eq!(table.range.to_string(), "A1:C7");
assert_eq!(
table
.auto_filter
.as_ref()
.map(|f| f.range.to_string())
.as_deref(),
Some("A1:C7")
);
let after = cycle(&book);
assert_eq!(after.sheets()[0].tables[0].range.to_string(), "A1:C7");
}
#[test]
fn a_removed_row_narrows_the_table() {
let mut book = read_xlsx_from(Cursor::new(fixture())).unwrap();
remove_rows(&mut book, 0, row(3), 1).unwrap();
assert_eq!(book.sheets()[0].tables[0].range.to_string(), "A1:C4");
let mut all = read_xlsx_from(Cursor::new(fixture())).unwrap();
remove_rows(&mut all, 0, row(1), 5).unwrap();
assert_eq!(all.sheets()[0].tables, []);
}
#[test]
fn a_chart_follows_the_cells_it_reads() {
use excelerate::coordinate::Row;
use excelerate::edit::{insert_rows, remove_sheet, rename_sheet};
let bytes = std::fs::read(concat!(
env!("CARGO_MANIFEST_DIR"),
"/tests/fixtures/chart.xlsx"
))
.unwrap();
let series = |book: &Spreadsheet| -> Vec<String> {
let part = book
.parts
.iter()
.find(|p| p.path == "xl/charts/chart1.xml")
.expect("the chart part is carried");
let text = String::from_utf8(part.data.clone()).unwrap();
text.split("<f>")
.skip(1)
.filter_map(|s| Some(s.split("</f>").next()?.to_owned()))
.collect()
};
let before = read_xlsx_from(Cursor::new(bytes.clone())).unwrap();
assert_eq!(
series(&before),
["Sheet1!$B$1", "Sheet1!$A$2:$A$5", "Sheet1!$B$2:$B$5"]
);
let mut moved = read_xlsx_from(Cursor::new(bytes.clone())).unwrap();
insert_rows(&mut moved, 0, Row::from_one_based(1).unwrap(), 2).unwrap();
assert_eq!(
series(&moved),
["Sheet1!$B$3", "Sheet1!$A$4:$A$7", "Sheet1!$B$4:$B$7"]
);
assert_eq!(series(&cycle(&moved)), series(&moved));
let mut renamed = read_xlsx_from(Cursor::new(bytes.clone())).unwrap();
rename_sheet(&mut renamed, 0, "Продажи").unwrap();
assert_eq!(series(&renamed)[0], "Продажи!$B$1");
let mut gone = read_xlsx_from(Cursor::new(bytes)).unwrap();
gone.add_sheet(excelerate::model::Worksheet::new("Другой").unwrap())
.unwrap();
remove_sheet(&mut gone, 0).unwrap();
assert_eq!(series(&gone)[0], "#REF!$B$1");
}
#[test]
fn a_formula_can_name_the_table_instead_of_its_cells() {
use excelerate::coordinate::CellRef;
use excelerate::formula::eval::{Engine, Origin};
use excelerate::formula::value::Value;
let book = read_xlsx_from(Cursor::new(fixture())).unwrap();
let mut engine = Engine::new(&book);
let outside = Origin::new(0, CellRef::parse("F20").unwrap());
let number =
|engine: &mut Engine<'_>, at: Origin, formula: &str| -> Value { engine.eval(at, formula) };
assert_eq!(
number(&mut engine, outside, "SUM(Sales[Sales])"),
Value::Number(465.0)
);
assert_eq!(
number(&mut engine, outside, "COUNT(Sales[Sales])"),
Value::Number(4.0)
);
assert_eq!(
number(&mut engine, outside, "COUNTA(Sales[[#All],[Sales]])"),
Value::Number(5.0)
);
assert_eq!(
number(&mut engine, outside, "Sales[[#Headers],[Sales]]"),
Value::Text("Sales".into())
);
assert_eq!(
number(&mut engine, outside, "COUNTA(Sales[[Region]:[Quarter]])"),
Value::Number(8.0)
);
assert_eq!(
number(&mut engine, outside, "COUNTA(Sales[#Data])"),
Value::Number(12.0)
);
let inside = Origin::new(0, CellRef::parse("E3").unwrap());
assert_eq!(
number(&mut engine, inside, "Sales[@Sales]"),
Value::Number(140.0)
);
assert_eq!(
number(&mut engine, outside, "Sales[@Sales]"),
Value::Error(excelerate::error::CellError::Value)
);
assert_eq!(
number(&mut engine, outside, "Sales[[#Totals],[Sales]]"),
Value::Error(excelerate::error::CellError::Ref)
);
assert_eq!(
number(&mut engine, outside, "Sales[Profit]"),
Value::Error(excelerate::error::CellError::Name)
);
assert_eq!(
number(&mut engine, outside, "Costs[Sales]"),
Value::Error(excelerate::error::CellError::Name)
);
let within = Origin::new(0, CellRef::parse("B3").unwrap());
assert_eq!(
number(&mut engine, within, "SUM([Sales])"),
Value::Number(465.0)
);
assert_eq!(
number(&mut engine, outside, "SUM([Sales])"),
Value::Error(excelerate::error::CellError::Name)
);
}
fn patched(edit: impl Fn(&str) -> String) -> Vec<u8> {
use std::io::{Read, Write};
let mut source = zip::ZipArchive::new(Cursor::new(fixture())).unwrap();
let mut out = zip::ZipWriter::new(Cursor::new(Vec::new()));
let options = zip::write::SimpleFileOptions::default();
for i in 0..source.len() {
let mut part = source.by_index(i).unwrap();
let name = part.name().to_owned();
let mut data = String::new();
part.read_to_string(&mut data).unwrap();
if name.starts_with("xl/tables/") {
data = edit(&data);
}
out.start_file(name, options).unwrap();
out.write_all(data.as_bytes()).unwrap();
}
out.finish().unwrap().into_inner()
}
#[test]
fn a_table_keeps_its_hidden_buttons_and_its_extensions() {
let bytes = patched(|xml| {
xml.replace(
"</autoFilter>",
r#"<filterColumn colId="1" hiddenButton="1"/></autoFilter>"#,
)
.replace(
"</table>",
r#"<extLst><ext uri="{504A1905-F514-4f6f-8877-14C23A59335A}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"><x14:table altTextSummary="alt"/></ext></extLst></table>"#,
)
});
let book = read_xlsx_from(Cursor::new(bytes)).unwrap();
for book in [cycle(&book), book] {
let table = &book.sheets()[0].tables[0];
assert_eq!(table.name, "Sales");
assert_ne!(table.id, 0);
let filter = table.auto_filter.as_ref().expect("the table filters");
assert!(
filter
.columns
.iter()
.any(|c| c.col_id == 1 && c.hidden_button)
);
assert!(
table
.extensions
.as_deref()
.is_some_and(|x| x.contains(r#"altTextSummary="alt""#))
);
}
}
fn hidden(book: &Spreadsheet) -> Vec<bool> {
(2..=5)
.map(|r| book.sheets()[0].rows.get(&row(r)).is_some_and(|p| p.hidden))
.collect()
}
#[test]
fn a_filter_hides_the_rows_its_criteria_reject() {
use excelerate::edit::filter_table;
use excelerate::model::autofilter::{ColumnFilter, CustomFilter, FilterOperator};
let mut book = read_xlsx_from(Cursor::new(fixture())).unwrap();
let rule = |operator, value: &str| CustomFilter {
operator,
value: value.into(),
};
let over_100 = ColumnFilter::Custom {
and: false,
rules: vec![rule(FilterOperator::GreaterThan, "100")],
};
filter_table(&mut book, "Sales", 2, Some(over_100)).unwrap();
assert_eq!(hidden(&book), [false, false, true, false]);
let north = ColumnFilter::Custom {
and: false,
rules: vec![rule(FilterOperator::Equal, "n*")],
};
filter_table(&mut book, "sales", 0, Some(north)).unwrap();
assert_eq!(hidden(&book), [false, false, true, true]);
filter_table(&mut book, "Sales", 0, None).unwrap();
let smallest = ColumnFilter::Top10 {
value: Some("1".into()),
percent: false,
top: false,
filter_value: None,
};
filter_table(&mut book, "Sales", 2, Some(smallest)).unwrap();
assert_eq!(hidden(&book), [true, true, false, true]);
let table = &book.sheets()[0].tables[0];
let filter = table.auto_filter.as_ref().unwrap();
assert!(matches!(
&filter.columns[0].filter,
Some(ColumnFilter::Top10 { filter_value: Some(v), .. }) if v == "90"
));
let back = cycle(&book);
assert_eq!(hidden(&back), [true, true, false, true]);
assert_eq!(back.sheets()[0].tables[0].auto_filter, table.auto_filter);
filter_table(&mut book, "Sales", 2, None).unwrap();
assert_eq!(hidden(&book), [false; 4]);
assert!(filter_table(&mut book, "Sales", 3, None).is_err());
assert!(filter_table(&mut book, "Nope", 0, None).is_err());
}