use super::Axis;
use super::quote_sheet_name;
use crate::coordinate::{
CellRef, Col, MAX_COL, MAX_ROW, Range, Row, parse_ref_at, scan_formula, shift_references,
};
use crate::error::{Error, Result};
use crate::model::{Cell, CellValue, Spreadsheet};
pub fn copy_range(
book: &mut Spreadsheet,
from_sheet: usize,
from: Range,
to_sheet: usize,
to: CellRef,
) -> Result<()> {
let (d_col, d_row) = landing(book, from_sheet, from, to_sheet, to)?;
let taken = read_block(book, from_sheet, from, |formula| {
shift_references(formula, d_col, d_row)
});
write_block(book, to_sheet, to, from, &taken)?;
let merges = moved_merges(book, from_sheet, from, d_col, d_row);
place_merges(book, to_sheet, target_of(from, d_col, d_row), merges);
Ok(())
}
pub fn move_range(
book: &mut Spreadsheet,
from_sheet: usize,
from: Range,
to_sheet: usize,
to: CellRef,
) -> Result<()> {
let (d_col, d_row) = landing(book, from_sheet, from, to_sheet, to)?;
if d_col == 0 && d_row == 0 && from_sheet == to_sheet {
return Ok(());
}
let from_name = book
.sheet(from_sheet)
.map_or_else(String::new, |ws| ws.title().to_owned());
let source_name = (from_sheet != to_sheet).then(|| quote_sheet_name(&from_name));
let taken = read_block(book, from_sheet, from, |formula| {
retarget(
formula,
None,
&from_name,
from,
d_col,
d_row,
source_name.as_deref(),
)
});
for at in from.cells() {
if let Some(ws) = book.sheet_mut(from_sheet) {
ws.remove(at);
}
}
let merges = moved_merges(book, from_sheet, from, d_col, d_row);
if let Some(ws) = book.sheet_mut(from_sheet) {
ws.merges.retain(|m| !from.contains_range(m));
}
write_block(book, to_sheet, to, from, &taken)?;
place_merges(book, to_sheet, target_of(from, d_col, d_row), merges);
follow_the_cells(book, from_sheet, to_sheet, from, d_col, d_row);
Ok(())
}
pub fn insert_cells(book: &mut Spreadsheet, sheet: usize, area: Range, axis: Axis) -> Result<()> {
insert_cells_with(book, sheet, area, axis, super::CopyOrigin::Blank)
}
pub fn insert_cells_with(
book: &mut Spreadsheet,
sheet: usize,
area: Range,
axis: Axis,
origin: super::CopyOrigin,
) -> Result<()> {
let (d_col, d_row) = step(area, axis);
let pushed = below(area, axis);
slide(book, sheet, pushed, d_col, d_row)?;
clear(book, sheet, area);
let Some(ws) = book.sheet_mut(sheet) else {
return Ok(());
};
for at in area.cells() {
let source = match (origin, axis) {
(super::CopyOrigin::Blank, _) => None,
(super::CopyOrigin::Before, Axis::Rows) => area
.start
.row
.index()
.checked_sub(1)
.and_then(Row::new)
.map(|row| CellRef::new(at.col, row)),
(super::CopyOrigin::Before, Axis::Columns) => area
.start
.col
.index()
.checked_sub(1)
.and_then(Col::new)
.map(|col| CellRef::new(col, at.row)),
(super::CopyOrigin::After, Axis::Rows) => area
.end
.row
.index()
.checked_add(1)
.and_then(Row::new)
.map(|row| CellRef::new(at.col, row)),
(super::CopyOrigin::After, Axis::Columns) => area
.end
.col
.index()
.checked_add(1)
.and_then(Col::new)
.map(|col| CellRef::new(col, at.row)),
};
if let Some(style) = source.and_then(|from| ws.get(from)).map(|cell| cell.style)
&& style != crate::style::StyleId::default()
{
ws.entry(at).style = style;
}
}
Ok(())
}
pub fn remove_cells(book: &mut Spreadsheet, sheet: usize, area: Range, axis: Axis) -> Result<()> {
let (d_col, d_row) = step(area, axis);
let pulled = beyond(area, axis)?;
clear(book, sheet, area);
slide(book, sheet, pulled, -d_col, -d_row)?;
Ok(())
}
const fn step(area: Range, axis: Axis) -> (i64, i64) {
match axis {
Axis::Rows => (0, area.height() as i64),
Axis::Columns => (area.width() as i64, 0),
}
}
fn below(area: Range, axis: Axis) -> Range {
let end = match axis {
Axis::Rows => CellRef::new(area.end.col, last_row()),
Axis::Columns => CellRef::new(last_col(), area.end.row),
};
Range::new(area.start, end)
}
fn beyond(area: Range, axis: Axis) -> Result<Range> {
let start = match axis {
Axis::Rows => CellRef::new(
area.start.col,
Row::new(area.end.row.index() + 1).ok_or_else(off_the_sheet)?,
),
Axis::Columns => CellRef::new(
Col::new(area.end.col.index() + 1).ok_or_else(off_the_sheet)?,
area.start.row,
),
};
Ok(below(Range::new(start, area.end), axis))
}
fn last_row() -> Row {
Row::new(MAX_ROW - 1).unwrap_or_default()
}
fn last_col() -> Col {
Col::new(MAX_COL - 1).unwrap_or_default()
}
fn off_the_sheet() -> Error {
Error::Xlsx("the cells would be pushed off the sheet".into())
}
fn slide(book: &mut Spreadsheet, sheet: usize, block: Range, d_col: i64, d_row: i64) -> Result<()> {
if d_col > 0 || d_row > 0 {
let first = |end: u32, start: u32, d: i64| {
if d > 0 {
shifted(end, 1 - d).max(start)
} else {
start
}
};
let edge = Range::new(
CellRef::new(
Col::new(first(block.end.col.index(), block.start.col.index(), d_col))
.unwrap_or(block.start.col),
Row::new(first(block.end.row.index(), block.start.row.index(), d_row))
.unwrap_or(block.start.row),
),
block.end,
);
let occupied = book.sheet(sheet).is_some_and(|ws| {
ws.iter().any(|(at, _)| edge.contains(at))
|| ws.merges.iter().any(|m| edge.intersects(m))
});
if occupied {
return Err(Error::WouldPushOffSheet);
}
}
let name = book
.sheet(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?
.title()
.to_owned();
let cells: Vec<(CellRef, Cell)> = book
.sheet(sheet)
.map(|ws| {
ws.iter()
.filter(|(at, _)| block.contains(*at))
.map(|(at, cell)| (at, cell.clone()))
.collect()
})
.unwrap_or_default();
let ws = book
.sheet_mut(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?;
for (at, _) in &cells {
ws.remove(*at);
}
for (at, mut cell) in cells {
let (Some(col), Some(row)) = (
Col::new(shifted(at.col.index(), d_col)),
Row::new(shifted(at.row.index(), d_row)),
) else {
continue;
};
if let CellValue::Formula { formula, .. } = &cell.value {
cell.value = CellValue::Formula {
formula: retarget(formula, None, &name, block, d_col, d_row, None),
cached: None,
};
}
*ws.entry(CellRef::new(col, row)) = cell;
}
follow_the_cells(book, sheet, sheet, block, d_col, d_row);
Ok(())
}
fn clear(book: &mut Spreadsheet, sheet: usize, area: Range) {
if let Some(ws) = book.sheet_mut(sheet) {
for at in area.cells() {
ws.remove(at);
}
}
}
pub fn move_sheet(book: &mut Spreadsheet, from: usize, to: usize) -> Result<()> {
let count = book.sheets().len();
if from >= count {
return Err(Error::SheetIndexOutOfRange(from));
}
if to >= count {
return Err(Error::SheetIndexOutOfRange(to));
}
if from == to {
return Ok(());
}
book.reorder_sheet(from, to);
for name in &mut book.defined_names {
name.sheet = name.sheet.map(|index| renumber(index, from, to));
}
let active = renumber(book.active_index(), from, to);
book.set_active(active)
}
const fn renumber(index: usize, from: usize, to: usize) -> usize {
if index == from {
return to;
}
if from < to && index > from && index <= to {
index - 1
} else if to <= index && index < from {
index + 1
} else {
index
}
}
fn landing(
book: &Spreadsheet,
from_sheet: usize,
from: Range,
to_sheet: usize,
to: CellRef,
) -> Result<(i64, i64)> {
for sheet in [from_sheet, to_sheet] {
if book.sheet(sheet).is_none() {
return Err(Error::SheetIndexOutOfRange(sheet));
}
}
let d_col = i64::from(to.col.index()) - i64::from(from.start.col.index());
let d_row = i64::from(to.row.index()) - i64::from(from.start.row.index());
let last_col = i64::from(from.end.col.index()) + d_col;
let last_row = i64::from(from.end.row.index()) + d_row;
if last_col >= i64::from(MAX_COL) || last_row >= i64::from(MAX_ROW) {
return Err(Error::Xlsx("the block would land off the sheet".into()));
}
Ok((d_col, d_row))
}
fn target_of(from: Range, d_col: i64, d_row: i64) -> Range {
let shift = |at: CellRef| {
let col = Col::new(shifted(at.col.index(), d_col)).unwrap_or(at.col);
let row = Row::new(shifted(at.row.index(), d_row)).unwrap_or(at.row);
CellRef::new(col, row)
};
Range::new(shift(from.start), shift(from.end))
}
fn shifted(index: u32, delta: i64) -> u32 {
u32::try_from(i64::from(index) + delta).unwrap_or(0)
}
fn read_block(
book: &Spreadsheet,
sheet: usize,
area: Range,
rewrite: impl Fn(&str) -> String,
) -> Vec<((u32, u32), Cell)> {
let Some(ws) = book.sheet(sheet) else {
return Vec::new();
};
let mut out = Vec::new();
for at in area.cells() {
let Some(cell) = ws.get(at) else { continue };
let mut cell = cell.clone();
if let CellValue::Formula { formula, .. } = &cell.value {
cell.value = CellValue::Formula {
formula: rewrite(formula),
cached: None,
};
}
out.push((
(
at.col.index() - area.start.col.index(),
at.row.index() - area.start.row.index(),
),
cell,
));
}
out
}
fn write_block(
book: &mut Spreadsheet,
sheet: usize,
to: CellRef,
from: Range,
cells: &[((u32, u32), Cell)],
) -> Result<()> {
let ws = book
.sheet_mut(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?;
let width = from.end.col.index() - from.start.col.index();
let height = from.end.row.index() - from.start.row.index();
for d_row in 0..=height {
for d_col in 0..=width {
let (Some(col), Some(row)) = (
Col::new(to.col.index() + d_col),
Row::new(to.row.index() + d_row),
) else {
continue;
};
let at = CellRef::new(col, row);
match cells.iter().find(|((c, r), _)| *c == d_col && *r == d_row) {
Some((_, cell)) => *ws.entry(at) = cell.clone(),
None => {
ws.remove(at);
}
}
}
}
Ok(())
}
fn moved_merges(
book: &Spreadsheet,
sheet: usize,
area: Range,
d_col: i64,
d_row: i64,
) -> Vec<Range> {
book.sheet(sheet).map_or_else(Vec::new, |ws| {
ws.merges
.iter()
.filter(|m| area.contains_range(m))
.map(|m| target_of(*m, d_col, d_row))
.collect()
})
}
fn place_merges(book: &mut Spreadsheet, sheet: usize, area: Range, merges: Vec<Range>) {
let Some(ws) = book.sheet_mut(sheet) else {
return;
};
ws.merges.retain(|m| !m.intersects(&area));
ws.merges.extend(merges);
}
fn retarget(
formula: &str,
on: Option<&str>,
source_sheet: &str,
source: Range,
d_col: i64,
d_row: i64,
qualify: Option<&str>,
) -> String {
let names_source = |qualifier: Option<&str>| match qualifier {
Some(name) => name.eq_ignore_ascii_case(source_sheet),
None => on.is_none_or(|sheet| sheet.eq_ignore_ascii_case(source_sheet)),
};
scan_formula(
formula,
|qualifier, s| {
let (len, r) = parse_ref_at(s)?;
if names_source(qualifier) && source.contains(CellRef::new(r.col, r.row)) {
let col = Col::new(shifted(r.col.index(), d_col))?;
let row = Row::new(shifted(r.row.index(), d_row))?;
return Some((len, r.render(col, row)));
}
match (qualifier, qualify) {
(None, Some(name)) => Some((len, format!("{name}!{}", r.render(r.col, r.row)))),
_ => None,
}
},
|_| None,
)
}
fn follow_the_cells(
book: &mut Spreadsheet,
from_sheet: usize,
to_sheet: usize,
source: Range,
d_col: i64,
d_row: i64,
) {
if from_sheet != to_sheet {
return;
}
let Some(source_name) = book.sheet(from_sheet).map(|ws| ws.title().to_owned()) else {
return;
};
let target = target_of(source, d_col, d_row);
for index in 0..book.sheets().len() {
let Some(ws) = book.sheet_mut(index) else {
continue;
};
let on = ws.title().to_owned();
let own = index == from_sheet;
let rewritten: Vec<(CellRef, String)> = ws
.iter()
.filter(|(at, _)| !(own && target.contains(*at)))
.filter_map(|(at, cell)| match &cell.value {
CellValue::Formula { formula, .. } => {
let text =
retarget(formula, Some(&on), &source_name, source, d_col, d_row, None);
(&text != formula).then_some((at, text))
}
_ => None,
})
.collect();
for (at, formula) in rewritten {
ws.entry(at).value = CellValue::Formula {
formula,
cached: None,
};
}
}
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub enum SortBy {
Line(u32),
Header(String),
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct SortKey {
pub by: SortBy,
pub descending: bool,
}
impl SortKey {
#[must_use]
pub const fn column(column: Col) -> Self {
Self {
by: SortBy::Line(column.index()),
descending: false,
}
}
#[must_use]
pub const fn row(row: Row) -> Self {
Self {
by: SortBy::Line(row.index()),
descending: false,
}
}
#[must_use]
pub fn header(name: impl Into<String>) -> Self {
Self {
by: SortBy::Header(name.into()),
descending: false,
}
}
#[must_use]
pub fn descending(mut self) -> Self {
self.descending = true;
self
}
}
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub struct SortOptions {
pub header: bool,
pub orientation: Axis,
}
impl Default for SortOptions {
fn default() -> Self {
Self {
header: false,
orientation: Axis::Rows,
}
}
}
pub fn sort_range(
book: &mut Spreadsheet,
sheet: usize,
area: Range,
keys: &[SortKey],
) -> Result<()> {
sort_range_with(book, sheet, area, keys, SortOptions::default())
}
pub fn sort_range_with(
book: &mut Spreadsheet,
sheet: usize,
area: Range,
keys: &[SortKey],
options: SortOptions,
) -> Result<()> {
let ws = book
.sheet(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?;
let rows = options.orientation == Axis::Rows;
let (first, last) = if rows {
(area.start.col.index(), area.end.col.index())
} else {
(area.start.row.index(), area.end.row.index())
};
let header: Vec<(u32, String)> = if options.header {
(first..=last)
.filter_map(|line| {
let at = if rows {
CellRef::new(Col::new(line)?, area.start.row)
} else {
CellRef::new(area.start.col, Row::new(line)?)
};
Some((line, ws.get(at)?.value.plain_text()?))
})
.collect()
} else {
Vec::new()
};
let lines = resolve_keys(keys, &header, first, last)?;
let data = if !options.header {
area
} else if rows {
match Row::new(area.start.row.index() + 1).filter(|r| *r <= area.end.row) {
Some(start) => Range::new(CellRef::new(area.start.col, start), area.end),
None => return Ok(()),
}
} else {
match Col::new(area.start.col.index() + 1).filter(|c| *c <= area.end.col) {
Some(start) => Range::new(CellRef::new(start, area.start.row), area.end),
None => return Ok(()),
}
};
sort_lines(book, sheet, data, &lines, rows)
}
pub fn sort_table(book: &mut Spreadsheet, name: &str, keys: &[SortKey]) -> Result<()> {
let found = book.sheets().iter().enumerate().find_map(|(index, ws)| {
ws.tables
.iter()
.find(|t| {
t.display_name.eq_ignore_ascii_case(name) || t.name.eq_ignore_ascii_case(name)
})
.map(|t| (index, t.clone()))
});
let Some((sheet, table)) = found else {
return Err(Error::InvalidRange(format!("no table called {name}")));
};
let header: Vec<(u32, String)> = table
.columns
.iter()
.zip(table.range.start.col.index()..)
.map(|(column, line)| (line, column.name.clone()))
.collect();
let lines = resolve_keys(
keys,
&header,
table.range.start.col.index(),
table.range.end.col.index(),
)?;
let Some(data) = table.body() else {
return Ok(());
};
sort_lines(book, sheet, data, &lines, true)
}
fn resolve_keys(
keys: &[SortKey],
header: &[(u32, String)],
first: u32,
last: u32,
) -> Result<Vec<(u32, bool)>> {
keys.iter()
.map(|key| {
let line = match &key.by {
SortBy::Line(line) => Some(*line).filter(|l| (first..=last).contains(l)),
SortBy::Header(name) => header
.iter()
.find(|(_, text)| text.trim().to_lowercase() == name.trim().to_lowercase())
.map(|(line, _)| *line),
};
line.map(|line| (line, key.descending)).ok_or_else(|| {
Error::InvalidRange(match &key.by {
SortBy::Line(line) => format!("sort key {} is outside the range", line + 1),
SortBy::Header(name) => format!("no header called {name:?}"),
})
})
})
.collect()
}
fn sort_lines(
book: &mut Spreadsheet,
sheet: usize,
area: Range,
keys: &[(u32, bool)],
rows: bool,
) -> Result<()> {
let ws = book
.sheet_mut(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?;
if ws.merges.iter().any(|m| m.intersects(&area)) {
return Err(Error::InvalidRange(format!(
"a merged range crosses {area}; unmerge it to sort"
)));
}
let (lines, cross) = if rows {
(
area.start.row.index()..=area.end.row.index(),
area.start.col.index()..=area.end.col.index(),
)
} else {
(
area.start.col.index()..=area.end.col.index(),
area.start.row.index()..=area.end.row.index(),
)
};
let at = |line: u32, cross: u32| -> Option<CellRef> {
if rows {
Some(CellRef::new(Col::new(cross)?, Row::new(line)?))
} else {
Some(CellRef::new(Col::new(line)?, Row::new(cross)?))
}
};
let lines: Vec<u32> = lines.collect();
let key_of = |line: u32, key: u32| -> Option<CellValue> {
let value = &ws.get(at(line, key)?)?.value;
match value {
CellValue::Formula { cached, .. } => cached.as_deref().cloned(),
other => Some(other.clone()),
}
.filter(|v| !v.is_empty())
};
let mut order: Vec<(usize, Vec<Option<CellValue>>)> = lines
.iter()
.enumerate()
.map(|(i, &line)| (i, keys.iter().map(|&(key, _)| key_of(line, key)).collect()))
.collect();
order.sort_by(|(_, a), (_, b)| {
keys.iter()
.zip(a.iter().zip(b))
.map(|(&(_, descending), (a, b))| match (a, b) {
(None, None) => std::cmp::Ordering::Equal,
(None, Some(_)) => std::cmp::Ordering::Greater,
(Some(_), None) => std::cmp::Ordering::Less,
(Some(a), Some(b)) if descending => sort_order(b, a),
(Some(a), Some(b)) => sort_order(a, b),
})
.find(|o| o.is_ne())
.unwrap_or(std::cmp::Ordering::Equal)
});
let taken: Vec<Vec<(u32, Cell)>> = lines
.iter()
.map(|&line| {
cross
.clone()
.filter_map(|c| Some((c, ws.remove(at(line, c)?)?)))
.collect()
})
.collect();
for (to, (from, _)) in order.iter().enumerate() {
let delta = i64::try_from(to).unwrap_or(0) - i64::try_from(*from).unwrap_or(0);
for (c, cell) in &taken[*from] {
let Some(target) = at(lines[to], *c) else {
continue;
};
let mut cell = cell.clone();
if delta != 0
&& let CellValue::Formula { formula, .. } = &cell.value
{
let (d_col, d_row) = if rows { (0, delta) } else { (delta, 0) };
cell.value = CellValue::Formula {
formula: shift_references(formula, d_col, d_row),
cached: None,
};
}
*ws.entry(target) = cell;
}
}
Ok(())
}
fn sort_order(a: &CellValue, b: &CellValue) -> std::cmp::Ordering {
let rank = |v: &CellValue| match v {
CellValue::Number(_) => 0,
CellValue::Bool(_) => 2,
CellValue::Error(_) => 3,
_ => 1,
};
match (a, b) {
(CellValue::Number(x), CellValue::Number(y)) => x.total_cmp(y),
(CellValue::Bool(x), CellValue::Bool(y)) => x.cmp(y),
_ if rank(a) != rank(b) => rank(a).cmp(&rank(b)),
(CellValue::Error(_), _) => std::cmp::Ordering::Equal,
_ => {
let text = |v: &CellValue| v.plain_text().unwrap_or_default().to_lowercase();
text(a).cmp(&text(b))
}
}
}
pub fn fill(book: &mut Spreadsheet, sheet: usize, area: Range, axis: Axis) -> Result<()> {
let (source, rest) = match axis {
Axis::Rows => (
Range::new(area.start, CellRef::new(area.end.col, area.start.row)),
area.start.row.index() + 1..=area.end.row.index(),
),
Axis::Columns => (
Range::new(area.start, CellRef::new(area.start.col, area.end.row)),
area.start.col.index() + 1..=area.end.col.index(),
),
};
for index in rest {
let to = match axis {
Axis::Rows => Row::new(index).map(|row| CellRef::new(area.start.col, row)),
Axis::Columns => Col::new(index).map(|col| CellRef::new(col, area.start.row)),
};
if let Some(to) = to {
copy_range(book, sheet, source, sheet, to)?;
}
}
Ok(())
}