use std::collections::BTreeMap;
use crate::cell::cell::{Cell, CellContext, CellValue, DataType};
use crate::cell::formula::FormulaStore;
use crate::cell::utils::{column_index_from_string, coordinate_from_string, get_column_letter};
use crate::comments::Comment;
use crate::datavalidation::DataValidation;
use crate::drawing::Image;
use crate::exceptions::{Error, Result};
use crate::formatting::ConditionalFormatting;
use crate::styles::style::Style;
use crate::styles::Style as StyleType;
use crate::units::{points_to_pixels, DEFAULT_COLUMN_WIDTH, DEFAULT_ROW_HEIGHT};
use crate::worksheet::dimensions::{ColumnDimension, RowDimension};
use crate::worksheet::filters::AutoFilter;
use crate::worksheet::header_footer::HeaderFooter;
use crate::worksheet::page::{PageMargins, PageSetup};
use crate::worksheet::protection::SheetProtection;
use crate::worksheet::relationship::{Relationship, RelationshipType};
use std::num::NonZeroU32;
#[derive(Debug, Clone)]
pub struct Worksheet {
pub title: String,
pub row_dimensions: BTreeMap<u32, RowDimension>,
pub column_dimensions: BTreeMap<String, ColumnDimension>,
pub page_breaks: Vec<u32>,
cells: BTreeMap<String, Cell>,
styles: BTreeMap<String, StyleType>,
pub charts: Vec<crate::charts::Chart>,
pub images: Vec<Image>,
pub relationships: Vec<Relationship>,
pub tables: crate::worksheet::table::TableList,
cached_formula_values: BTreeMap<String, CellValue>,
pub data_validations: Vec<DataValidation>,
pub selected_cell: String,
pub active_cell: String,
pub sheet_state: String,
pub page_setup: PageSetup,
pub page_margins: PageMargins,
pub header_footer: HeaderFooter,
pub protection: SheetProtection,
pub show_gridlines: bool,
pub print_gridlines: bool,
pub show_summary_below: bool,
pub show_summary_right: bool,
pub auto_filter: AutoFilter,
pub freeze_panes: Option<String>,
pub paper_size: Option<String>,
pub formula_store: FormulaStore,
pub context: CellContext,
pub conditional_formatting: ConditionalFormatting,
merged_cells: Vec<String>,
comment_count: usize,
pub xml_source: Option<Vec<u8>>,
pub index: usize,
}
impl Worksheet {
pub const BREAK_NONE: u32 = 0;
pub const BREAK_ROW: u32 = 1;
pub const BREAK_COLUMN: u32 = 2;
pub const SHEETSTATE_VISIBLE: &'static str = "visible";
pub const SHEETSTATE_HIDDEN: &'static str = "hidden";
pub const SHEETSTATE_VERYHIDDEN: &'static str = "veryHidden";
pub const ORIENTATION_PORTRAIT: &'static str = "portrait";
pub const ORIENTATION_LANDSCAPE: &'static str = "landscape";
pub const PAPERSIZES: [(&'static str, &str); 11] = [
("PAPERSIZE_LETTER", "1"),
("PAPERSIZE_LETTER_SMALL", "2"),
("PAPERSIZE_TABLOID", "3"),
("PAPERSIZE_LEDGER", "4"),
("PAPERSIZE_LEGAL", "5"),
("PAPERSIZE_STATEMENT", "6"),
("PAPERSIZE_EXECUTIVE", "7"),
("PAPERSIZE_A3", "8"),
("PAPERSIZE_A4", "9"),
("PAPERSIZE_A4_SMALL", "10"),
("PAPERSIZE_A5", "11"),
];
pub fn new(title: &str) -> Result<Self> {
Worksheet::with_title(title, 0)
}
pub fn with_title(title: &str, index: usize) -> Result<Self> {
let title = if title.is_empty() {
format!("Sheet{}", index + 1)
} else {
title.to_string()
};
let worksheet = Worksheet {
title: String::new(),
cached_formula_values: BTreeMap::new(),
row_dimensions: BTreeMap::new(),
column_dimensions: BTreeMap::new(),
page_breaks: Vec::new(),
cells: BTreeMap::new(),
styles: BTreeMap::new(),
charts: Vec::new(),
images: Vec::new(),
relationships: Vec::new(),
tables: crate::worksheet::table::TableList::new(),
data_validations: Vec::new(),
selected_cell: "A1".to_string(),
active_cell: "A1".to_string(),
sheet_state: Worksheet::SHEETSTATE_VISIBLE.to_string(),
page_setup: PageSetup::default(),
page_margins: PageMargins::default(),
header_footer: HeaderFooter::new(),
protection: SheetProtection::new(),
show_gridlines: true,
print_gridlines: false,
show_summary_below: true,
show_summary_right: true,
auto_filter: AutoFilter::new(),
freeze_panes: None,
paper_size: None,
formula_store: FormulaStore::new(),
context: CellContext::default(),
conditional_formatting: ConditionalFormatting::new(),
merged_cells: Vec::new(),
comment_count: 0,
xml_source: None,
index,
};
let mut worksheet = worksheet;
worksheet.set_title(&title, &[])?;
Ok(worksheet)
}
pub fn title(&self) -> &str {
&self.title
}
pub fn set_title(&mut self, value: &str, existing: &[String]) -> Result<()> {
if value.contains(['\\', '*', '?', ':', '/', '[', ']']) {
return Err(Error::SheetTitle(
"Invalid character found in sheet title".into(),
));
}
let value = self.unique_sheet_name(value, existing);
if value.chars().count() > 31 {
return Err(Error::SheetTitle(
"Maximum 31 characters allowed in sheet title".into(),
));
}
self.title = value;
Ok(())
}
fn unique_sheet_name(&self, value: &str, existing: &[String]) -> String {
if !existing.iter().any(|title| title == value) {
return value.to_string();
}
let mut highest = 0u32;
for title in existing {
let Some(rest) = title.strip_prefix(value) else {
continue;
};
if rest.is_empty() {
continue;
}
if let Ok(count) = rest.parse::<u32>() {
highest = highest.max(count);
}
}
format!("{value}{}", highest + 1)
}
pub fn cell(&mut self, coordinate: &str) -> Result<&Cell> {
self.create_cell(coordinate)?;
Ok(self.cells.get(coordinate).expect("cell just created"))
}
pub fn cell_mut(&mut self, coordinate: &str) -> Result<&mut Cell> {
self.create_cell(coordinate)?;
Ok(self.cells.get_mut(coordinate).expect("cell just created"))
}
pub fn get_cell(&self, coordinate: &str) -> Option<&Cell> {
self.cells.get(coordinate)
}
pub fn get_cell_mut(&mut self, coordinate: &str) -> Option<&mut Cell> {
self.cells.get_mut(coordinate)
}
fn create_cell(&mut self, coordinate: &str) -> Result<()> {
if self.cells.contains_key(coordinate) {
return Ok(());
}
let (column, row) = coordinate_from_string(coordinate)?;
let cell = Cell::new(&column, row);
self.cells.insert(coordinate.to_string(), cell);
self.column_dimensions
.entry(column.clone())
.or_insert_with(|| ColumnDimension::new(&column));
self.row_dimensions
.entry(row)
.or_insert_with(|| RowDimension::new(row));
Ok(())
}
pub fn cell_at(&mut self, row: u32, column: u32) -> Result<&Cell> {
let coordinate = format!("{}{}", get_column_letter(column)?, row);
self.cell(&coordinate)
}
pub fn set_cell_value(&mut self, coordinate: &str, value: impl Into<CellValue>) -> Result<()> {
let context = self.context;
self.create_cell(coordinate)?;
let cell = self.cells.get_mut(coordinate).expect("cell just created");
let format = cell.set_value(value, context);
if let Some(code) = format {
self.get_style_mut(coordinate)?
.set_number_format_code(&code);
}
Ok(())
}
pub fn cell_value(&self, coordinate: &str) -> Option<CellValue> {
let cell = self.cells.get(coordinate)?;
let format = self.styles.get(coordinate).map(|s| s.number_format_code());
Some(cell.display_value_in(format, self.context.base_date))
}
pub fn set(&mut self, coordinate: &str, value: impl Into<CellValue>) -> Result<()> {
self.set_cell_value(coordinate, value)
}
pub fn cached_value(&self, coordinate: &str) -> Option<&CellValue> {
self.cached_formula_values.get(coordinate)
}
pub fn set_cached_value(&mut self, coordinate: &str, value: CellValue) {
self.cached_formula_values
.insert(coordinate.to_string(), value);
}
pub fn clear_cached_value(&mut self, coordinate: &str) {
self.cached_formula_values.remove(coordinate);
}
pub fn cached_values(&self) -> &BTreeMap<String, CellValue> {
&self.cached_formula_values
}
pub fn cells(&self) -> impl Iterator<Item = &Cell> {
self.cells.values()
}
pub fn cell_count(&self) -> usize {
self.cells.len()
}
pub fn garbage_collect(&mut self) {
let defaults = crate::styles::defaults();
let to_delete: Vec<String> = self
.cells
.iter()
.filter(|(coordinate, cell)| {
!cell.merged
&& cell.internal_value().is_empty()
&& cell.comment.is_none()
&& match self.styles.get(*coordinate) {
None => true,
Some(style) => crate::styles::same_visual_style(style, &defaults),
}
})
.map(|(coordinate, _)| coordinate.clone())
.collect();
for coordinate in to_delete {
self.cells.remove(&coordinate);
}
}
pub fn get_style(&self, coordinate: &str) -> Style {
self.styles
.get(coordinate)
.cloned()
.unwrap_or_else(Style::new)
}
pub fn get_style_read_only(&self, coordinate: &str) -> Option<&Style> {
self.styles.get(coordinate)
}
pub fn get_style_mut(&mut self, coordinate: &str) -> Result<&mut Style> {
if coordinate_from_string(coordinate).is_ok() {
self.create_cell(coordinate)?;
}
let entry = self.styles.entry(coordinate.to_string()).or_default();
if entry.is_static {
*entry = entry.copy_style();
}
Ok(entry)
}
pub fn has_style(&self, coordinate: &str) -> bool {
self.styles.contains_key(coordinate)
}
pub fn set_style(&mut self, coordinate: &str, style: Style) -> Result<()> {
if coordinate_from_string(coordinate).is_ok() {
self.create_cell(coordinate)?;
}
self.styles.insert(coordinate.to_string(), style);
Ok(())
}
pub fn style_values(&self) -> Vec<Style> {
self.styles.values().cloned().collect()
}
pub fn number_format(&self, coordinate: &str) -> String {
self.styles
.get(coordinate)
.map(|s| s.number_format_code().to_string())
.unwrap_or_else(|| "General".to_string())
}
pub fn set_number_format(&mut self, coordinate: &str, code: &str) -> Result<()> {
self.get_style_mut(coordinate)?.set_number_format_code(code);
Ok(())
}
pub fn highest_row(&self) -> u32 {
self.row_dimensions.keys().copied().max().unwrap_or(1)
}
pub fn highest_column(&self) -> u32 {
self.column_dimensions
.keys()
.filter_map(|key| column_index_from_string(key).ok())
.max()
.unwrap_or(1)
}
pub fn calculate_dimension(&self) -> Result<String> {
Ok(format!(
"A1:{}{}",
get_column_letter(self.highest_column())?,
self.highest_row()
))
}
pub fn range_bounds(
&self,
range_string: &str,
) -> Result<crate::worksheet::iter_worksheet::RangeBounds> {
crate::worksheet::iter_worksheet::get_range_boundaries(range_string, 0, 1)
}
pub fn range(
&self,
range_string: &str,
row_offset: Option<u32>,
column_offset: Option<u32>,
) -> Result<crate::worksheet::cell_range::CellRange> {
let parsed = crate::worksheet::cell_range::CellRange::parse(range_string)?;
if row_offset.is_none() && column_offset.is_none() {
return Ok(parsed);
}
let rows = row_offset.unwrap_or(0);
let columns = column_offset.unwrap_or(0);
if range_string.contains(':') {
return parsed.shift(columns as i64, rows as i64);
}
parsed.expand(columns, rows, 0, 0)
}
pub fn range_cells(
&self,
range_string: &str,
row_offset: Option<u32>,
column_offset: Option<u32>,
) -> Result<Vec<Vec<Option<&Cell>>>> {
let bounds = self.range(range_string, row_offset, column_offset)?;
let mut out = Vec::new();
for row in bounds.min_row..=bounds.max_row {
let mut cells = Vec::new();
for column in bounds.min_col..=bounds.max_col {
let coordinate = format!("{}{row}", get_column_letter(column)?);
cells.push(self.get_cell(&coordinate));
}
out.push(cells);
}
Ok(out)
}
pub fn range_coordinates(&self, range_string: &str) -> Result<Vec<String>> {
let bounds = self.range_bounds(range_string)?;
let mut out = Vec::new();
for row in bounds.min_row..=bounds.max_row {
for column in bounds.min_col..bounds.max_col {
out.push(format!("{}{}", get_column_letter(column)?, row));
}
}
Ok(out)
}
pub fn range_values(&self, range_string: &str) -> Result<Vec<Vec<CellValue>>> {
let bounds = self.range_bounds(range_string)?;
let mut rows = Vec::new();
for row in bounds.min_row..=bounds.max_row {
let mut values = Vec::new();
for column in bounds.min_col..bounds.max_col {
let coordinate = format!("{}{}", get_column_letter(column)?, row);
values.push(self.cell_value(&coordinate).unwrap_or(CellValue::None));
}
rows.push(values);
}
Ok(rows)
}
pub fn iter_rows(&self) -> Vec<Vec<Option<&Cell>>> {
let (min_col, min_row, max_col, max_row) = self.used_bounds();
self.iter_rows_within(min_row, max_row, min_col, max_col)
}
pub fn iter_rows_within(
&self,
min_row: u32,
max_row: u32,
min_col: u32,
max_col: u32,
) -> Vec<Vec<Option<&Cell>>> {
(min_row..=max_row)
.map(|row| {
(min_col..=max_col)
.map(|column| self.get_cell_by_index(column, row))
.collect()
})
.collect()
}
pub fn iter_cols(&self) -> Vec<Vec<Option<&Cell>>> {
let (min_col, min_row, max_col, max_row) = self.used_bounds();
self.iter_cols_within(min_col, max_col, min_row, max_row)
}
pub fn iter_cols_within(
&self,
min_col: u32,
max_col: u32,
min_row: u32,
max_row: u32,
) -> Vec<Vec<Option<&Cell>>> {
(min_col..=max_col)
.map(|column| {
(min_row..=max_row)
.map(|row| self.get_cell_by_index(column, row))
.collect()
})
.collect()
}
fn used_bounds(&self) -> (u32, u32, u32, u32) {
(
1,
1,
self.highest_column().max(1),
self.highest_row().max(1),
)
}
fn get_cell_by_index(&self, column: u32, row: u32) -> Option<&Cell> {
let letters = get_column_letter(column).ok()?;
self.get_cell(&format!("{letters}{row}"))
}
pub fn write_row(&mut self, row: u32, start_column: u32, values: &[CellValue]) -> Result<()> {
for (offset, value) in values.iter().enumerate() {
let coordinate = format!(
"{}{}",
get_column_letter(start_column + offset as u32)?,
row
);
self.set_cell_value(&coordinate, value.clone())?;
}
Ok(())
}
pub fn append(&mut self, values: &[(u32, CellValue)]) -> Result<()> {
let row = self.row_dimensions.len() as u32 + 1;
for (column, value) in values {
let coordinate = format!("{}{}", get_column_letter(*column + 1)?, row);
self.set_cell_value(&coordinate, value.clone())?;
}
Ok(())
}
pub fn set_printer_settings(&mut self, paper_size: &str, orientation: &str) -> Result<()> {
self.page_setup.paper_size = Some(paper_size.to_string());
if orientation != Worksheet::ORIENTATION_PORTRAIT
&& orientation != Worksheet::ORIENTATION_LANDSCAPE
{
return Err(Error::value(format!(
"Values should be {} or {}",
Worksheet::ORIENTATION_PORTRAIT,
Worksheet::ORIENTATION_LANDSCAPE
)));
}
self.page_setup.orientation = Some(orientation.to_string());
Ok(())
}
pub fn create_relationship(&mut self, relationship_type: RelationshipType) -> usize {
self.relationships
.push(Relationship::new(relationship_type, None));
let index = self.relationships.len() - 1;
self.relationships[index].id = Some(format!("rId{}", index + 1));
index
}
pub fn add_data_validation(&mut self, validation: DataValidation) {
self.data_validations.push(validation);
}
pub fn add_table(
&mut self,
table: crate::worksheet::table::Table,
) -> Result<&crate::worksheet::table::TableList> {
let mut table = table;
if table.columns.is_empty() {
table.initialise_columns(self)?;
}
let style = crate::worksheet::table::TableStyleInfo::banded();
if table.style_info == Default::default() {
table.style_info = style;
}
self.tables.add(table)?;
Ok(&self.tables)
}
pub fn table_relationship_ids(&self) -> Vec<String> {
let first = if self.charts.is_empty() && self.images.is_empty() {
1
} else {
2
};
(0..self.tables.len())
.map(|index| format!("rId{}", first + index))
.collect()
}
pub fn table(&self, name: &str) -> Option<&crate::worksheet::table::Table> {
self.tables.get(name)
}
pub fn table_mut(&mut self, name: &str) -> Option<&mut crate::worksheet::table::Table> {
self.tables.get_mut(name)
}
pub fn add_image(&mut self, image: Image) {
self.images.push(image);
}
pub fn merge_cells(&mut self, range_string: &str) -> Result<()> {
let parts: Vec<&str> = range_string.split(':').collect();
if parts.len() != 2 {
return Err(Error::InsufficientCoordinates(
"Range must be a cell range (e.g. A1:E1)".into(),
));
}
let range_string = range_string.replace('$', "");
let bounds = self.range_bounds(&range_string)?;
if bounds.min_col > bounds.max_col || bounds.min_row > bounds.max_row {
return Err(Error::InsufficientCoordinates(format!(
"{range_string} runs backwards; the top-left corner comes first, as in A1:C3"
)));
}
for row in bounds.min_row..=bounds.max_row {
for column in bounds.min_col..bounds.max_col {
if row == bounds.min_row && column == bounds.min_col {
continue;
}
let coordinate = format!("{}{}", get_column_letter(column)?, row);
self.create_cell(&coordinate)?;
let cell = self.cells.get_mut(&coordinate).expect("cell just created");
cell.set_value(CellValue::None, self.context);
cell.merged = true;
}
}
if !self.merged_cells.contains(&range_string) {
self.merged_cells.push(range_string);
}
Ok(())
}
pub fn unmerge_cells(&mut self, range_string: &str) -> Result<()> {
let range_string = range_string.replace('$', "");
if !self.merged_cells.contains(&range_string) {
return Err(Error::InsufficientCoordinates(format!(
"Cell range {range_string} not known as merged."
)));
}
self.merged_cells.retain(|item| item != &range_string);
let bounds = self.range_bounds(&range_string)?;
for row in bounds.min_row..=bounds.max_row {
for column in bounds.min_col..bounds.max_col {
if row == bounds.min_row && column == bounds.min_col {
continue;
}
let coordinate = format!("{}{}", get_column_letter(column)?, row);
self.create_cell(&coordinate)?;
self.cells
.get_mut(&coordinate)
.expect("cell just created")
.merged = false;
}
}
Ok(())
}
pub fn merged_cells(&self) -> &[String] {
&self.merged_cells
}
pub fn set_freeze_panes(&mut self, top_left_cell: &str) {
let normalised = top_left_cell.to_uppercase();
self.freeze_panes = if normalised.is_empty() || normalised == "A1" {
None
} else {
Some(normalised)
};
}
pub fn comment_count(&self) -> usize {
self.comment_count
}
pub fn recount_comments(&mut self) {
self.comment_count = self.cells.values().filter(|c| c.comment.is_some()).count();
}
pub fn set_comment(&mut self, coordinate: &str, comment: Option<Comment>) -> Result<()> {
self.create_cell(coordinate)?;
self.cells
.get_mut(coordinate)
.expect("cell just created")
.comment = comment;
self.recount_comments();
Ok(())
}
pub fn cell_anchor(&self, coordinate: &str) -> Result<(i64, i64)> {
let (column, row) = coordinate_from_string(coordinate)?;
let left_columns = column_index_from_string(&column)? - 1;
let dpi = NonZeroU32::new(96).expect("96 dpi");
let default_width = points_to_pixels(DEFAULT_COLUMN_WIDTH, dpi);
let mut left_anchor = 0i64;
for index in 1..=left_columns {
let letter = get_column_letter(index)?;
match self.column_dimensions.get(&letter) {
Some(dimension) if dimension.width > 0.0 => {
left_anchor += points_to_pixels(dimension.width, dpi);
}
_ => left_anchor += default_width,
}
}
let default_height = points_to_pixels(DEFAULT_ROW_HEIGHT, dpi);
let mut top_anchor = 0i64;
for index in 1..=row.saturating_sub(1) {
match self.row_dimensions.get(&index) {
Some(dimension) if dimension.height > 0.0 => {
top_anchor += points_to_pixels(dimension.height, dpi);
}
_ => top_anchor += default_height,
}
}
Ok((left_anchor, top_anchor))
}
pub fn point_pos(&self, left: i64, top: i64) -> Result<(String, u32)> {
let dpi = NonZeroU32::new(96).expect("96 dpi");
let default_width = points_to_pixels(DEFAULT_COLUMN_WIDTH, dpi);
let default_height = points_to_pixels(DEFAULT_ROW_HEIGHT, dpi);
let mut current_col = 1u32;
let mut left_pos = 0i64;
let mut letter;
loop {
letter = get_column_letter(current_col)?;
current_col += 1;
match self.column_dimensions.get(&letter) {
Some(dimension) if dimension.width > 0.0 => {
left_pos += points_to_pixels(dimension.width, dpi);
}
_ => left_pos += default_width,
}
if left_pos > left {
break;
}
if current_col > crate::cell::utils::MAX_COLUMN_INDEX {
break;
}
}
let mut current_row = 1u32;
let mut top_pos = 0i64;
let mut row;
loop {
row = current_row;
current_row += 1;
match self.row_dimensions.get(&row) {
Some(dimension) if dimension.height > 0.0 => {
top_pos += points_to_pixels(dimension.height, dpi);
}
_ => top_pos += default_height,
}
if top_pos > top {
break;
}
if current_row > crate::xml::constants::MAX_ROW {
break;
}
}
Ok((letter, row))
}
pub fn set_hyperlink(&mut self, coordinate: &str, target: &str) -> Result<String> {
let relationship_index = match self
.cells
.get(coordinate)
.and_then(|cell| cell.hyperlink_rel_id.clone())
{
Some(_) => self
.relationships
.iter()
.position(|rel| {
self.cells
.get(coordinate)
.and_then(|cell| cell.hyperlink_rel_id.clone())
.as_deref()
== rel.id.as_deref()
})
.unwrap_or_else(|| self.create_relationship(RelationshipType::Hyperlink)),
None => self.create_relationship(RelationshipType::Hyperlink),
};
let id = self.relationships[relationship_index]
.id
.clone()
.unwrap_or_else(|| format!("rId{}", relationship_index + 1));
self.relationships[relationship_index].target = Some(target.to_string());
self.relationships[relationship_index].target_mode = Some("External".to_string());
self.create_cell(coordinate)?;
let cell = self.cells.get_mut(coordinate).expect("cell just created");
cell.hyperlink_rel_id = Some(id.clone());
cell.set_hyperlink(target);
Ok(id)
}
pub fn cells_with_hyperlinks(&self) -> impl Iterator<Item = &Cell> {
self.cells
.values()
.filter(|cell| cell.hyperlink_rel_id.is_some())
}
pub fn cell_data_type(&self, coordinate: &str) -> Option<DataType> {
self.cells.get(coordinate).map(|cell| cell.data_type)
}
pub fn builtin_style(name: &str) -> Result<crate::styles::named_style::NamedStyle> {
crate::styles::named_style::NamedStyle::builtin(name)
.ok_or_else(|| Error::Key(format!("{name} is not one of Excel's built-in styles")))
}
pub fn apply_named_style(&mut self, coordinate: &str, name: &str) -> Result<&'static str> {
let style = Self::builtin_style(name)?;
self.set_style(coordinate, style.to_style())?;
Ok(
crate::styles::named_style::builtin_name(&style.builtin_id.unwrap_or_default())
.unwrap_or(""),
)
}
pub fn builtin_style_name(&self, coordinate: &str) -> Option<&'static str> {
let style = self.get_style(coordinate);
for (_, name) in crate::styles::named_style::BUILTIN_STYLES {
let Some(builtin) = crate::styles::named_style::NamedStyle::builtin(name) else {
continue;
};
if builtin.to_style() == style {
return Some(name);
}
}
None
}
pub fn print_title_range(n: u32, rows_or_cols: &str) -> Result<String> {
if rows_or_cols == "cols" {
Ok(format!("$A:${}", get_column_letter(n)?))
} else {
Ok(format!("$1:${n}"))
}
}
}
impl std::fmt::Display for Worksheet {
fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
write!(f, "<Worksheet \"{}\">", self.title)
}
}
#[test]
fn rows_come_back_left_to_right() {
let mut ws = Worksheet::new("S").expect("title");
ws.set("A1", 1).expect("A1");
ws.set("C1", 3).expect("C1");
ws.set("A2", 4).expect("A2");
let rows = ws.iter_rows();
assert_eq!(rows.len(), 2);
assert_eq!(rows[0].len(), 3);
assert_eq!(
rows[0][0].map(|c| c.internal_value().clone()),
Some(CellValue::Number(1.0))
);
assert!(rows[0][1].is_none(), "B1 is empty but present");
assert_eq!(
rows[0][2].map(|c| c.internal_value().clone()),
Some(CellValue::Number(3.0))
);
assert_eq!(
rows[1][0].map(|c| c.internal_value().clone()),
Some(CellValue::Number(4.0))
);
}
#[test]
fn columns_come_back_top_to_bottom() {
let mut ws = Worksheet::new("S").expect("title");
ws.set("A1", 1).expect("A1");
ws.set("A3", 3).expect("A3");
ws.set("B1", 4).expect("B1");
let cols = ws.iter_cols();
assert_eq!(cols.len(), 2);
assert_eq!(cols[0].len(), 3);
assert_eq!(
cols[0][0].map(|c| c.internal_value().clone()),
Some(CellValue::Number(1.0))
);
assert!(cols[0][1].is_none(), "A2 is empty but present");
assert_eq!(
cols[0][2].map(|c| c.internal_value().clone()),
Some(CellValue::Number(3.0))
);
}
#[test]
fn rows_and_columns_are_transposes_of_each_other() {
let mut ws = Worksheet::new("S").expect("title");
for (coordinate, value) in [("A1", 1), ("B1", 2), ("A2", 3), ("B2", 4)] {
ws.set(coordinate, value).expect("set");
}
let rows = ws.iter_rows();
let cols = ws.iter_cols();
for (r, row) in rows.iter().enumerate() {
for (c, cell) in row.iter().enumerate() {
let value = cell.map(|c| c.internal_value().clone());
assert_eq!(
value,
cols[c][r].map(|c| c.internal_value().clone()),
"row {r} column {c} disagrees with its transpose"
);
}
}
}
#[test]
fn an_empty_sheet_yields_one_empty_row() {
let ws = Worksheet::new("S").expect("title");
let rows = ws.iter_rows();
assert_eq!(rows.len(), 1);
assert_eq!(rows[0].len(), 1);
assert!(rows[0][0].is_none());
}
#[test]
fn the_bounds_are_inclusive_at_both_ends() {
let mut ws = Worksheet::new("S").expect("title");
ws.set("B5", 1).expect("B5");
let rows = ws.iter_rows_within(1, 5, 1, 2);
assert_eq!(rows.len(), 5);
assert!(rows.iter().all(|row| row.len() == 2));
assert!(rows[4][1].is_some(), "B5 is inside the bounds");
}
#[cfg(test)]
mod tests {
use super::*;
use chrono::NaiveDate;
fn sheet() -> Worksheet {
Worksheet::new("Sheet1").unwrap()
}
#[test]
fn a_bare_coordinate_with_offsets_expands_to_a_block() {
let mut ws = sheet();
ws.set("A1", CellValue::number(1.0)).unwrap();
ws.set("B2", CellValue::number(4.0)).unwrap();
let block = ws.range("A1", Some(1), Some(1)).expect("a block");
assert_eq!(block.to_string(), "A1:B2");
assert_eq!(block.size(), 4);
let cells = ws.range_cells("A1", Some(1), Some(1)).expect("cells");
assert_eq!(cells.len(), 2);
assert_eq!(cells[0].len(), 2);
assert_eq!(
cells[0][0].map(|c| c.internal_value().clone()),
Some(CellValue::Number(1.0))
);
assert!(cells[0][1].is_none());
assert_eq!(
cells[1][1].map(|c| c.internal_value().clone()),
Some(CellValue::Number(4.0))
);
}
#[test]
fn no_offsets_leaves_the_range_alone() {
let ws = sheet();
assert_eq!(ws.range("A1", None, None).expect("one cell").size(), 1);
assert_eq!(
ws.range("A1:C3", None, None).expect("a block").to_string(),
"A1:C3"
);
}
#[test]
fn offsets_on_a_range_shift_it_rather_than_growing_it() {
let ws = sheet();
assert_eq!(
ws.range("A1:B2", Some(2), Some(1))
.expect("shifted")
.to_string(),
"B3:C4"
);
}
#[test]
fn one_offset_extends_that_way_only() {
let ws = sheet();
assert_eq!(
ws.range("A1", Some(2), None).expect("down").to_string(),
"A1:A3"
);
assert_eq!(
ws.range("A1", None, Some(2)).expect("across").to_string(),
"A1:C1"
);
}
#[test]
fn titles_are_validated() {
let mut ws = sheet();
assert!(ws.set_title("My Sheet", &["Sheet1".to_string()]).is_ok());
assert_eq!(ws.title(), "My Sheet");
for bad in ["a/b", "a\\b", "a*b", "a?b", "a:b", "a[b", "a]b"] {
assert!(matches!(ws.set_title(bad, &[]), Err(Error::SheetTitle(_))));
}
let long = "x".repeat(32);
assert!(matches!(
ws.set_title(&long, &[]),
Err(Error::SheetTitle(_))
));
assert_eq!(
ws.title(),
"My Sheet",
"failed set must not clobber the title"
);
}
#[test]
fn duplicate_titles_get_a_counter() {
let existing = vec!["Sheet".to_string(), "Sheet2".to_string()];
let mut ws = sheet();
ws.set_title("Sheet", &existing).unwrap();
assert_eq!(ws.title(), "Sheet3");
}
#[test]
fn empty_title_gets_a_default() {
let ws = Worksheet::with_title("", 0).unwrap();
assert_eq!(ws.title(), "Sheet1");
let ws = Worksheet::with_title("", 1).unwrap();
assert_eq!(ws.title(), "Sheet2");
}
#[test]
fn cells_are_created_lazily_with_dimensions() {
let mut ws = sheet();
assert_eq!(ws.cell_count(), 0);
ws.cell("B3").unwrap();
assert_eq!(ws.cell_count(), 1);
assert!(ws.column_dimensions.contains_key("B"));
assert!(ws.row_dimensions.contains_key(&3));
}
#[test]
fn values_round_trip() {
let mut ws = sheet();
ws.set("A1", "hello").unwrap();
assert_eq!(ws.cell_value("A1"), Some(CellValue::text("hello")));
ws.set("A2", 42.0).unwrap();
assert_eq!(ws.cell_value("A2"), Some(CellValue::Number(42.0)));
ws.set("A3", true).unwrap();
assert_eq!(ws.cell_value("A3"), Some(CellValue::Bool(true)));
ws.set("A4", CellValue::None).unwrap();
assert_eq!(ws.cell_value("A4"), Some(CellValue::Text(String::new())));
}
#[test]
fn dates_get_a_number_format_and_convert_back() {
let mut ws = sheet();
let date = NaiveDate::from_ymd_opt(2010, 1, 18).unwrap();
ws.set("A1", CellValue::Date(date)).unwrap();
assert_eq!(ws.number_format("A1"), "yyyy-mm-dd");
assert_eq!(ws.cell_value("A1"), Some(CellValue::Date(date)));
}
#[test]
fn dimensions_track_data() {
let mut ws = sheet();
assert_eq!(ws.highest_row(), 1);
assert_eq!(ws.highest_column(), 1);
ws.set("C5", 1.0).unwrap();
assert_eq!(ws.highest_row(), 5);
assert_eq!(ws.highest_column(), 3);
assert_eq!(ws.calculate_dimension().unwrap(), "A1:C5");
}
#[test]
fn ranges_are_read_row_major() {
let mut ws = sheet();
ws.set("A1", 1.0).unwrap();
ws.set("B1", 2.0).unwrap();
ws.set("A2", 3.0).unwrap();
let rows = ws.range_values("A1:B2").unwrap();
assert_eq!(rows.len(), 2);
assert_eq!(
rows[0],
vec![CellValue::Number(1.0), CellValue::Number(2.0)]
);
assert_eq!(rows[1][0], CellValue::Number(3.0));
assert_eq!(rows[1][1], CellValue::None);
assert_eq!(ws.range_coordinates("A1:B1").unwrap().len(), 2);
}
#[test]
fn writing_rows() {
let mut ws = sheet();
ws.write_row(1, 1, &[CellValue::text("a"), CellValue::Number(2.0)])
.unwrap();
assert_eq!(ws.cell_value("A1"), Some(CellValue::text("a")));
assert_eq!(ws.cell_value("B1"), Some(CellValue::Number(2.0)));
}
#[test]
fn merge_and_unmerge() {
let mut ws = sheet();
ws.merge_cells("A1:B2").unwrap();
assert_eq!(ws.merged_cells(), &["A1:B2".to_string()]);
let cell = ws.get_cell("B2").unwrap();
assert!(cell.merged);
assert!(cell.internal_value().is_empty());
ws.unmerge_cells("A1:B2").unwrap();
assert!(ws.merged_cells().is_empty());
assert!(!ws.get_cell("B2").unwrap().merged);
assert!(ws.merge_cells("A1").is_err());
assert!(ws.unmerge_cells("A9:B9").is_err());
}
#[test]
fn merging_stays_inside_the_range() {
let mut ws = sheet();
ws.merge_cells("A6:D6").unwrap();
for coordinate in ["B6", "C6", "D6"] {
assert!(
ws.get_cell(coordinate).is_some_and(|cell| cell.merged),
"{coordinate}"
);
}
assert!(ws.get_cell("E6").is_none(), "E6 is outside A6:D6");
assert_eq!(ws.calculate_dimension().unwrap(), "A1:D6");
ws.unmerge_cells("A6:D6").unwrap();
assert!(
ws.get_cell("E6").is_none(),
"unmerging must not create E6 either"
);
}
#[test]
fn a_merge_whose_corners_are_backwards_is_refused() {
let mut ws = sheet();
let error = ws.merge_cells("C3:A1").unwrap_err();
assert!(error.to_string().contains("backwards"), "{error}");
assert!(
ws.merged_cells.is_empty(),
"a refused merge must not be recorded: {:?}",
ws.merged_cells
);
assert!(ws.get_cell("A1").is_none_or(|cell| !cell.merged));
assert!(ws.merge_cells("A3:C1").is_err());
ws.merge_cells("A1:C3").unwrap();
assert_eq!(ws.merged_cells, vec!["A1:C3".to_string()]);
ws.merge_cells("B2:B2").unwrap();
assert!(ws.merged_cells.contains(&"B2:B2".to_string()));
}
#[test]
fn garbage_collect_drops_empty_cells() {
let mut ws = sheet();
ws.set("A1", "x").unwrap();
ws.set("B1", "").unwrap();
ws.set("C1", CellValue::None).unwrap();
ws.get_style_mut("D1").unwrap().font.bold = true;
ws.set("E1", "").unwrap();
ws.garbage_collect();
assert!(ws.get_cell("A1").is_some());
assert!(ws.get_cell("B1").is_none());
assert!(ws.get_cell("C1").is_none());
assert!(ws.get_cell("D1").is_some());
assert!(ws.get_cell("E1").is_none());
}
#[test]
fn static_styles_are_copied_on_write() {
let mut ws = sheet();
let mut static_style = Style::static_style();
static_style.font.bold = true;
let table_entry = static_style.clone();
ws.set_style("A1", static_style).unwrap();
assert!(ws.get_style_read_only("A1").unwrap().is_static);
let style = ws.get_style_mut("A1").unwrap();
assert!(!style.is_static);
assert!(style.font.bold);
style.font.italic = true;
assert!(table_entry.is_static);
assert!(!table_entry.font.italic);
assert!(!ws.get_style_read_only("A1").unwrap().is_static);
assert!(ws.get_style_read_only("A1").unwrap().font.italic);
}
#[test]
fn freeze_panes_normalise() {
let mut ws = sheet();
ws.set_freeze_panes("b2");
assert_eq!(ws.freeze_panes.as_deref(), Some("B2"));
ws.set_freeze_panes("A1");
assert_eq!(ws.freeze_panes, None);
ws.set_freeze_panes("");
assert_eq!(ws.freeze_panes, None);
}
#[test]
fn printer_settings_validate_orientation() {
let mut ws = sheet();
ws.set_printer_settings("9", Worksheet::ORIENTATION_LANDSCAPE)
.unwrap();
assert_eq!(ws.page_setup.paper_size.as_deref(), Some("9"));
assert_eq!(ws.page_setup.orientation.as_deref(), Some("landscape"));
assert!(ws.set_printer_settings("9", "sideways").is_err());
}
#[test]
fn hyperlinks_create_relationships() {
let mut ws = sheet();
let id = ws.set_hyperlink("A1", "http://example.com").unwrap();
assert_eq!(id, "rId1");
let cell = ws.get_cell("A1").unwrap();
assert_eq!(cell.hyperlink(), "http://example.com");
assert_eq!(cell.hyperlink_rel_id.as_deref(), Some("rId1"));
assert_eq!(ws.relationships.len(), 1);
assert_eq!(ws.cells_with_hyperlinks().count(), 1);
ws.set_hyperlink("A1", "http://other.com").unwrap();
assert_eq!(ws.relationships.len(), 1);
assert_eq!(ws.get_cell("A1").unwrap().hyperlink(), "http://other.com");
}
#[test]
fn comments_are_counted() {
let mut ws = sheet();
ws.set_comment("A1", Some(Comment::new("hi", "me")))
.unwrap();
assert_eq!(ws.comment_count(), 1);
ws.set_comment("B2", Some(Comment::new("yo", "me")))
.unwrap();
assert_eq!(ws.comment_count(), 2);
ws.set_comment("A1", None).unwrap();
assert_eq!(ws.comment_count(), 1);
}
#[test]
fn anchors_and_point_positions() {
let mut ws = sheet();
ws.set("D4", 1.0).unwrap();
let anchor = ws.cell_anchor("D4").unwrap();
assert!(anchor.0 > 0 && anchor.1 > 0);
let (column, row) = ws.point_pos(anchor.0 + 1, anchor.1 + 1).unwrap();
assert!(!column.is_empty());
assert!(row > 0);
}
#[test]
fn append_writes_to_the_next_row() {
let mut ws = sheet();
ws.set("A1", 1.0).unwrap();
ws.append(&[(0, CellValue::text("x")), (2, CellValue::Number(3.0))])
.unwrap();
assert_eq!(ws.cell_value("A1"), Some(CellValue::Number(1.0)));
assert_eq!(ws.cell_value("A2"), Some(CellValue::text("x")));
assert_eq!(ws.cell_value("C2"), Some(CellValue::Number(3.0)));
}
#[test]
fn print_title_ranges() {
assert_eq!(Worksheet::print_title_range(2, "rows").unwrap(), "$1:$2");
assert_eq!(Worksheet::print_title_range(3, "cols").unwrap(), "$A:$C");
}
#[test]
fn display_formatting() {
let ws = sheet();
assert_eq!(ws.to_string(), "<Worksheet \"Sheet1\">");
}
}