mod anchor;
mod chart;
mod extension;
mod range;
mod series;
pub use range::{
SortBy, SortKey, SortOptions, copy_range, fill, insert_cells, insert_cells_with, move_range,
move_sheet, remove_cells, sort_range, sort_range_with, sort_table,
};
pub use series::{fill_series, fill_series_back};
use crate::coordinate::{
CellRef, Col, MAX_COL, MAX_ROW, Range, Row, parse_ref_at, scan_formula, scan_references,
};
use crate::error::{Error, Result};
use crate::model::sparkline::SparklineGroup;
use crate::model::{AutoFilter, CellValue, Spreadsheet, Worksheet};
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum Axis {
Rows,
Columns,
}
#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
pub enum CopyOrigin {
#[default]
Blank,
Before,
After,
}
pub fn insert_rows(book: &mut Spreadsheet, sheet: usize, at: Row, count: u32) -> Result<()> {
insert_rows_with(book, sheet, at, count, CopyOrigin::Blank)
}
pub fn insert_rows_with(
book: &mut Spreadsheet,
sheet: usize,
at: Row,
count: u32,
origin: CopyOrigin,
) -> Result<()> {
apply(book, sheet, Shift::insert(Axis::Rows, at.index(), count))?;
if let Some(target) = book.sheet_mut(sheet) {
format_inserted(target, Axis::Rows, at.index(), count, origin);
}
Ok(())
}
pub fn remove_rows(book: &mut Spreadsheet, sheet: usize, at: Row, count: u32) -> Result<()> {
apply(book, sheet, Shift::remove(Axis::Rows, at.index(), count))
}
pub fn insert_columns(book: &mut Spreadsheet, sheet: usize, at: Col, count: u32) -> Result<()> {
insert_columns_with(book, sheet, at, count, CopyOrigin::Blank)
}
pub fn insert_columns_with(
book: &mut Spreadsheet,
sheet: usize,
at: Col,
count: u32,
origin: CopyOrigin,
) -> Result<()> {
apply(book, sheet, Shift::insert(Axis::Columns, at.index(), count))?;
if let Some(target) = book.sheet_mut(sheet) {
format_inserted(target, Axis::Columns, at.index(), count, origin);
}
Ok(())
}
fn format_inserted(sheet: &mut Worksheet, axis: Axis, at: u32, count: u32, origin: CopyOrigin) {
let source = match origin {
CopyOrigin::Blank => return,
CopyOrigin::Before => match at.checked_sub(1) {
Some(source) => source,
None => return,
},
CopyOrigin::After => match at.checked_add(count) {
Some(source) => source,
None => return,
},
};
let last = at.saturating_add(count);
match axis {
Axis::Rows => {
let Some(from) = Row::new(source) else { return };
let styles: Vec<(Col, _)> = sheet
.row_cells(from)
.filter(|(_, cell)| cell.style != crate::style::StyleId::default())
.map(|(col, cell)| (col, cell.style))
.collect();
let props = sheet.rows.get(&from).cloned();
for row in (at..last).filter_map(Row::new) {
for &(col, style) in &styles {
sheet.entry(CellRef::new(col, row)).style = style;
}
match &props {
Some(props) => {
let mut copy = props.clone();
copy.hidden = false;
copy.collapsed = false;
sheet.rows.insert(row, copy);
}
None => {
sheet.rows.remove(&row);
}
}
}
}
Axis::Columns => {
let Some(from) = Col::new(source) else { return };
let styles: Vec<(Row, _)> = sheet
.iter()
.filter(|(cell_at, cell)| {
cell_at.col == from && cell.style != crate::style::StyleId::default()
})
.map(|(cell_at, cell)| (cell_at.row, cell.style))
.collect();
let run = sheet.column_run(from).cloned();
for col in (at..last).filter_map(Col::new) {
for &(row, style) in &styles {
sheet.entry(CellRef::new(col, row)).style = style;
}
let entry = sheet.column_entry(col);
let (first, last) = (entry.first, entry.last);
*entry = run.clone().map_or_else(
|| crate::model::ColumnRun::new(first, last),
|mut run| {
run.hidden = false;
run.collapsed = false;
run
},
);
entry.first = first;
entry.last = last;
}
}
}
}
pub fn remove_columns(book: &mut Spreadsheet, sheet: usize, at: Col, count: u32) -> Result<()> {
apply(book, sheet, Shift::remove(Axis::Columns, at.index(), count))
}
#[derive(Debug, Clone, Copy)]
struct Shift {
axis: Axis,
at: u32,
delta: i64,
}
impl Shift {
const fn insert(axis: Axis, at: u32, count: u32) -> Self {
Self {
axis,
at,
delta: count as i64,
}
}
const fn remove(axis: Axis, at: u32, count: u32) -> Self {
Self {
axis,
at,
delta: -(count as i64),
}
}
fn last_removed(self) -> u32 {
let count = u32::try_from(self.delta.unsigned_abs()).unwrap_or(u32::MAX);
self.at.saturating_add(count).saturating_sub(1)
}
fn removes(self, i: u32) -> bool {
self.delta < 0 && i >= self.at && i <= self.last_removed()
}
const fn ceiling(self) -> u32 {
match self.axis {
Axis::Rows => MAX_ROW - 1,
Axis::Columns => MAX_COL - 1,
}
}
fn moved(self, i: u32) -> Option<u32> {
if self.removes(i) {
return None;
}
if i < self.at {
return Some(i);
}
let moved = i64::from(i) + self.delta;
u32::try_from(moved).ok().filter(|&i| i <= self.ceiling())
}
fn moved_edge(self, i: u32, start: bool) -> Option<u32> {
if self.removes(i) {
return if start {
Some(self.at)
} else {
self.at.checked_sub(1)
};
}
self.moved(i)
}
fn range(self, r: Range) -> Option<Range> {
let (start, end) = match self.axis {
Axis::Rows => (r.start.row.index(), r.end.row.index()),
Axis::Columns => (r.start.col.index(), r.end.col.index()),
};
if self.delta < 0 && start >= self.at && end <= self.last_removed() {
return None;
}
let (start, end) = (self.moved_edge(start, true)?, self.moved_edge(end, false)?);
if start > end {
return None;
}
Some(match self.axis {
Axis::Rows => Range::new(
CellRef::new(r.start.col, Row::new(start)?),
CellRef::new(r.end.col, Row::new(end)?),
),
Axis::Columns => Range::new(
CellRef::new(Col::new(start)?, r.start.row),
CellRef::new(Col::new(end)?, r.end.row),
),
})
}
const fn of(self, at: CellRef) -> u32 {
match self.axis {
Axis::Rows => at.row.index(),
Axis::Columns => at.col.index(),
}
}
fn with(self, at: CellRef, i: u32) -> Option<CellRef> {
Some(match self.axis {
Axis::Rows => CellRef::new(at.col, Row::new(i)?),
Axis::Columns => CellRef::new(Col::new(i)?, at.row),
})
}
}
fn apply(book: &mut Spreadsheet, sheet: usize, shift: Shift) -> Result<()> {
let title = book
.sheet(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?
.title()
.to_owned();
let names: Vec<String> = book.sheets().iter().map(|s| s.title().to_owned()).collect();
for (index, own) in names.iter().enumerate() {
let Some(target) = book.sheet_mut(index) else {
continue;
};
let rewritten: Vec<(CellRef, String)> = target
.iter()
.filter_map(|(at, cell)| match &cell.value {
CellValue::Formula { formula, .. } => {
let text = adjust(formula, shift, &title, own == &title);
(&text != formula).then_some((at, text))
}
_ => None,
})
.collect();
for (at, formula) in rewritten {
target.entry(at).value = CellValue::Formula {
formula,
cached: None,
};
}
}
for name in &mut book.defined_names {
let own = name.sheet.and_then(|i| names.get(i)) == Some(&title);
name.formula = adjust(&name.formula, shift, &title, own);
}
anchor::move_anchors(book, sheet, shift);
chart::rewrite_series(book, |series| adjust(series, shift, &title, false));
chart::rewrite_model(book, |series| adjust(series, shift, &title, false));
rewrite_sparklines(book, |formula, on| {
adjust(formula, shift, &title, on == title)
});
let range = |r: Range| shift.range(r);
for index in 0..book.sheets().len() {
let Some(target) = book.sheet_mut(index) else {
continue;
};
let own = target.title().eq_ignore_ascii_case(&title);
if let Some(ext) = target.extensions.take() {
let moved: Option<&dyn Fn(Range) -> Option<Range>> = own.then_some(&range);
target.extensions = Some(extension::rewrite(
&ext,
|f| adjust(f, shift, &title, own),
moved,
));
}
}
let Some(target) = book.sheet_mut(sheet) else {
return Ok(());
};
move_cells(target, shift);
move_furniture(target, shift);
Ok(())
}
fn move_cells(sheet: &mut Worksheet, shift: Shift) {
let moved: Vec<(CellRef, Option<CellRef>)> = sheet
.iter()
.map(|(at, _)| {
(
at,
shift.moved(shift.of(at)).and_then(|i| shift.with(at, i)),
)
})
.collect();
let mut carried = Vec::with_capacity(moved.len());
for (from, to) in moved {
if let Some(cell) = sheet.remove(from)
&& let Some(to) = to
{
carried.push((to, cell));
}
}
for (at, cell) in carried {
*sheet.entry(at) = cell;
}
}
fn moved_filter(mut filter: AutoFilter, shift: Shift) -> Option<AutoFilter> {
filter.range = shift.range(filter.range)?;
filter.sort_state = filter
.sort_state
.take()
.and_then(|sort| sort.moved(|r| shift.range(r)));
Some(filter)
}
fn move_sparklines(sheet: &mut Worksheet, shift: Shift) {
sheet.sparklines.retain_mut(|group| {
group.sparklines.retain_mut(|line| {
let at = line.location;
let Some(moved) = shift.moved(shift.of(at)).and_then(|i| shift.with(at, i)) else {
return false;
};
line.location = moved;
true
});
!group.sparklines.is_empty()
});
}
fn move_furniture(sheet: &mut Worksheet, shift: Shift) {
sheet.merges = sheet
.merges
.iter()
.filter_map(|&r| shift.range(r))
.collect();
sheet.array_formulas = sheet
.array_formulas
.iter()
.filter_map(|&r| shift.range(r))
.collect();
sheet
.hyperlinks
.retain_mut(|link| match shift.range(link.range) {
Some(range) => {
link.range = range;
true
}
None => false,
});
sheet.data_validations.retain_mut(|v| {
v.sqref = v.sqref.iter().filter_map(|&r| shift.range(r)).collect();
!v.sqref.is_empty()
});
sheet.conditional_formats.retain_mut(|f| {
f.sqref = f.sqref.iter().filter_map(|&r| shift.range(r)).collect();
!f.sqref.is_empty()
});
sheet.protected_ranges.retain_mut(|p| {
p.sqref = p.sqref.iter().filter_map(|&r| shift.range(r)).collect();
!p.sqref.is_empty()
});
sheet.auto_filter = sheet
.auto_filter
.take()
.and_then(|f| moved_filter(f, shift));
sheet.sort_state = sheet
.sort_state
.take()
.and_then(|sort| sort.moved(|r| shift.range(r)));
move_sparklines(sheet, shift);
sheet.comments = std::mem::take(&mut sheet.comments)
.into_iter()
.filter_map(|(at, note)| {
let moved = shift.moved(shift.of(at))?;
Some((shift.with(at, moved)?, note))
})
.collect();
sheet.tables.retain_mut(|t| {
let Some(range) = shift.range(t.range) else {
return false;
};
t.range = range;
t.auto_filter = t.auto_filter.take().and_then(|f| moved_filter(f, shift));
t.sort_state = t
.sort_state
.take()
.and_then(|sort| sort.moved(|r| shift.range(r)));
true
});
move_drawn_objects(sheet, shift);
match shift.axis {
Axis::Rows => {
sheet.rows = std::mem::take(&mut sheet.rows)
.into_iter()
.filter_map(|(row, props)| Some((Row::new(shift.moved(row.index())?)?, props)))
.collect();
sheet.row_breaks.retain_mut(|b| match shift.moved(b.at) {
Some(at) => {
b.at = at;
true
}
None => false,
});
}
Axis::Columns => {
sheet.columns.retain_mut(|run| {
match (
shift.moved_edge(run.first.index(), true),
shift.moved_edge(run.last.index(), false),
) {
(Some(first), Some(last)) if first <= last => {
let (Some(first), Some(last)) = (Col::new(first), Col::new(last)) else {
return false;
};
run.first = first;
run.last = last;
true
}
_ => false,
}
});
sheet.col_breaks.retain_mut(|b| match shift.moved(b.at) {
Some(at) => {
b.at = at;
true
}
None => false,
});
}
}
}
fn move_drawn_objects(sheet: &mut Worksheet, shift: Shift) {
for chart in &mut sheet.charts {
let untouched = chart.is_unchanged();
anchor::move_anchor(&mut chart.anchor, shift);
if untouched {
chart.settle();
}
}
for chart in &mut sheet.extended_charts {
let untouched = chart.is_unchanged();
anchor::move_anchor(&mut chart.anchor, shift);
if untouched {
chart.settle();
}
}
for image in &mut sheet.images {
let untouched = image.is_unchanged();
anchor::move_anchor(&mut image.anchor, shift);
if untouched {
image.settle();
}
}
for shape in &mut sheet.shapes {
let untouched = shape.is_unchanged();
anchor::move_anchor(&mut shape.anchor, shift);
if untouched {
shape.settle();
}
}
}
fn adjust(formula: &str, shift: Shift, target: &str, own: bool) -> String {
scan_references(formula, |qualifier, s| {
let aims_at_target = qualifier.map_or(own, |q| q.eq_ignore_ascii_case(target));
if !aims_at_target {
return None;
}
let (len, first) = parse_ref_at(s)?;
if s.get(len) == Some(&':')
&& let Some((second_len, second)) = parse_ref_at(&s[len + 1..])
{
let range = Range::new(
CellRef::new(first.col, first.row),
CellRef::new(second.col, second.row),
);
let text = shift.range(range).map_or_else(
|| "#REF!".to_owned(),
|moved| {
format!(
"{}:{}",
first.render(moved.start.col, moved.start.row),
second.render(moved.end.col, moved.end.row)
)
},
);
return Some((len + 1 + second_len, text));
}
let at = CellRef::new(first.col, first.row);
let text = shift
.moved(shift.of(at))
.and_then(|i| shift.with(at, i))
.map_or_else(
|| "#REF!".to_owned(),
|moved| first.render(moved.col, moved.row),
);
Some((len, text))
})
}
pub fn rename_sheet(book: &mut Spreadsheet, sheet: usize, to: &str) -> Result<()> {
let from = book
.sheet(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?
.title()
.to_owned();
if from == to {
return Ok(());
}
if let Some(other) = book.sheet_by_name(to)
&& other.title() != from
{
return Err(Error::DuplicateSheetName(to.to_owned()));
}
book.sheet_mut(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?
.set_title(to)?;
rewrite_qualifiers(book, |names| {
if !names.iter().any(|n| n.eq_ignore_ascii_case(&from)) {
return None;
}
Some(render_qualifier(names, |n| {
if n.eq_ignore_ascii_case(&from) {
to.to_owned()
} else {
n.clone()
}
}))
});
Ok(())
}
pub fn remove_sheet(book: &mut Spreadsheet, sheet: usize) -> Result<()> {
let order: Vec<String> = book.sheets().iter().map(|s| s.title().to_owned()).collect();
let gone = order
.get(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?
.clone();
if order.len() == 1 {
return Err(Error::Xlsx(
"a workbook must keep at least one sheet".into(),
));
}
let survivors: Vec<String> = order
.iter()
.enumerate()
.filter(|(i, _)| *i != sheet)
.map(|(_, n)| n.clone())
.collect();
rewrite_qualifiers(book, |names| match names {
[one] => one.eq_ignore_ascii_case(&gone).then(|| "#REF!".to_owned()),
[first, last] => {
let ends = [
nearest_inside(&order, &survivors, first, last),
nearest_inside(&order, &survivors, last, first),
];
match ends {
[Some(a), Some(b)] => {
(a != *first || b != *last).then(|| render_qualifier(&[a, b], Clone::clone))
}
_ => Some("#REF!".to_owned()),
}
}
_ => None,
});
book.defined_names.retain(|n| n.sheet != Some(sheet));
for name in &mut book.defined_names {
if let Some(index) = name.sheet
&& index > sheet
{
name.sheet = Some(index - 1);
}
}
book.take_sheet(sheet);
Ok(())
}
fn nearest_inside(
order: &[String],
survivors: &[String],
end: &str,
other: &str,
) -> Option<String> {
let alive = |name: &str| survivors.iter().any(|s| s.eq_ignore_ascii_case(name));
let position = |name: &str| order.iter().position(|s| s.eq_ignore_ascii_case(name));
let (from, to) = (position(end)?, position(other)?);
let mut inwards: Box<dyn Iterator<Item = &String>> = if from <= to {
Box::new(order[from..=to].iter())
} else {
Box::new(order[to..=from].iter().rev())
};
inwards.find(|n| alive(n)).cloned()
}
fn render_qualifier(names: &[String], f: impl Fn(&String) -> String) -> String {
let parts: Vec<String> = names.iter().map(|n| quote_sheet_name(&f(n))).collect();
format!("{}!", parts.join(":"))
}
fn quote_sheet_name(name: &str) -> String {
let bare = !name.is_empty()
&& name
.chars()
.next()
.is_some_and(|c| c.is_alphabetic() || c == '_')
&& name
.chars()
.all(|c| c.is_alphanumeric() || matches!(c, '_' | '.'));
if bare {
name.to_owned()
} else {
format!("'{}'", name.replace('\'', "''"))
}
}
fn rewrite_qualifiers(book: &mut Spreadsheet, rename: impl Fn(&[String]) -> Option<String>) {
for index in 0..book.sheets().len() {
let Some(target) = book.sheet_mut(index) else {
continue;
};
let rewritten: Vec<(CellRef, String)> = target
.iter()
.filter_map(|(at, cell)| match &cell.value {
CellValue::Formula { formula, .. } => {
let text = scan_formula(formula, |_, _| None, &rename);
(&text != formula).then_some((at, text))
}
_ => None,
})
.collect();
for (at, formula) in rewritten {
target.entry(at).value = CellValue::Formula {
formula,
cached: None,
};
}
}
for name in &mut book.defined_names {
name.formula = scan_formula(&name.formula, |_, _| None, &rename);
}
chart::rewrite_series(book, |series| scan_formula(series, |_, _| None, &rename));
chart::rewrite_model(book, |series| scan_formula(series, |_, _| None, &rename));
rewrite_sparklines(book, |formula, _| {
scan_formula(formula, |_, _| None, &rename)
});
for index in 0..book.sheets().len() {
if let Some(target) = book.sheet_mut(index)
&& let Some(ext) = target.extensions.take()
{
target.extensions = Some(extension::rewrite(
&ext,
|f| scan_formula(f, |_, _| None, &rename),
None,
));
}
}
}
fn rewrite_sparklines(book: &mut Spreadsheet, rewrite: impl Fn(&str, &str) -> String) {
for index in 0..book.sheets().len() {
let Some(sheet) = book.sheet_mut(index) else {
continue;
};
let title = sheet.title().to_owned();
for formula in sheet
.sparklines
.iter_mut()
.flat_map(SparklineGroup::formulas_mut)
{
*formula = rewrite(formula, &title);
}
}
}