use std::collections::HashSet;
use crate::coordinate::{CellRef, Col, Range, Row, shift_references};
use crate::error::{Error, Result};
use crate::model::{Cell, CellValue, Spreadsheet};
#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
pub struct Deduplicated {
pub removed: usize,
pub remaining: usize,
}
pub fn remove_duplicates(
book: &mut Spreadsheet,
sheet: usize,
area: Range,
columns: &[Col],
header: bool,
) -> Result<Deduplicated> {
let ws = book
.sheet(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?;
if let Some(column) = columns
.iter()
.find(|c| **c < area.start.col || **c > area.end.col)
{
return Err(Error::InvalidRange(format!(
"column {} is outside {area}",
column.to_letters()
)));
}
if ws.merges.iter().any(|m| m.intersects(&area)) {
return Err(Error::InvalidRange(format!(
"a merged range crosses {area}; unmerge it to remove duplicates"
)));
}
let Some(used) = ws.dimension() else {
return Ok(Deduplicated::default());
};
let first = area.start.row.index() + u32::from(header);
let last = area.end.row.index().min(used.end.row.index());
if first > last {
return Ok(Deduplicated::default());
}
let keys: Vec<Col> = if columns.is_empty() {
(area.start.col.index()..=area.end.col.index())
.filter_map(Col::new)
.collect()
} else {
columns.to_vec()
};
let mut seen = HashSet::new();
let mut keep = Vec::new();
for index in first..=last {
let Some(row) = Row::new(index) else { continue };
let key: Vec<String> = keys
.iter()
.map(|&col| book.formatted(sheet, CellRef::new(col, row)).to_lowercase())
.collect();
if seen.insert(key) {
keep.push(index);
}
}
let total = usize::try_from(last - first + 1).unwrap_or(usize::MAX);
let report = Deduplicated {
removed: total - keep.len(),
remaining: keep.len(),
};
if report.removed == 0 {
return Ok(report);
}
let ws = book
.sheet_mut(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?;
let cross: Vec<Col> = (area.start.col.index()..=area.end.col.index())
.filter_map(Col::new)
.collect();
let taken: Vec<(u32, Vec<(Col, Cell)>)> = (first..=last)
.filter_map(|index| {
let row = Row::new(index)?;
let cells = cross
.iter()
.filter_map(|&col| Some((col, ws.remove(CellRef::new(col, row))?)))
.collect();
Some((index, cells))
})
.collect();
let mut taken: std::collections::HashMap<u32, Vec<(Col, Cell)>> = taken.into_iter().collect();
for (to, from) in (first..).zip(&keep) {
let (Some(row), Some(cells)) = (Row::new(to), taken.remove(from)) else {
continue;
};
let delta = i64::from(to) - i64::from(*from);
for (col, mut cell) in cells {
if delta != 0
&& let CellValue::Formula { formula, .. } = &cell.value
{
cell.value = CellValue::Formula {
formula: shift_references(formula, 0, delta),
cached: None,
};
}
*ws.entry(CellRef::new(col, row)) = cell;
}
}
Ok(report)
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub enum SplitBy {
Delimited {
delimiters: Vec<char>,
merge_consecutive: bool,
qualifier: Option<char>,
},
FixedWidth(Vec<usize>),
}
impl SplitBy {
#[must_use]
pub fn delimiter(delimiter: char) -> Self {
Self::Delimited {
delimiters: vec![delimiter],
merge_consecutive: false,
qualifier: Some('"'),
}
}
#[must_use]
pub fn split(&self, text: &str) -> Vec<String> {
match self {
Self::Delimited {
delimiters,
merge_consecutive,
qualifier,
} => split_delimited(text, delimiters, *merge_consecutive, *qualifier),
Self::FixedWidth(breaks) => {
let chars: Vec<char> = text.chars().collect();
let mut cuts = vec![0];
let mut breaks: Vec<usize> = breaks
.iter()
.copied()
.filter(|&b| b > 0 && b < chars.len())
.collect();
breaks.sort_unstable();
breaks.dedup();
cuts.extend(breaks);
cuts.push(chars.len());
cuts.windows(2)
.map(|w| {
chars[w[0]..w[1]]
.iter()
.collect::<String>()
.trim()
.to_owned()
})
.collect()
}
}
}
}
pub fn text_to_columns(
book: &mut Spreadsheet,
sheet: usize,
source: Range,
destination: CellRef,
split: &SplitBy,
) -> Result<u32> {
if book.sheet(sheet).is_none() {
return Err(Error::SheetIndexOutOfRange(sheet));
}
if source.start.col != source.end.col {
return Err(Error::InvalidRange(format!(
"{source} spans more than one column; Text to Columns splits one"
)));
}
let column = source.start.col;
let rows: Vec<Vec<String>> = (source.start.row.index()..=source.end.row.index())
.map(|index| {
Row::new(index).map_or_else(Vec::new, |row| {
let at = CellRef::new(column, row);
match book.sheet(sheet).and_then(|ws| ws.get(at)) {
Some(cell) if !cell.value.result().is_empty() => {
split.split(&book.formatted(sheet, at))
}
_ => Vec::new(),
}
})
})
.collect();
let widest = rows.iter().map(Vec::len).max().unwrap_or(0);
let widest = u32::try_from(widest).map_err(|_| Error::WouldPushOffSheet)?;
if widest == 0 {
return Ok(0);
}
if Col::new(destination.col.index() + widest - 1).is_none()
|| Row::new(destination.row.index() + source.height() - 1).is_none()
{
return Err(Error::WouldPushOffSheet);
}
let ws = book
.sheet_mut(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?;
for (i, pieces) in (0u32..).zip(rows) {
if pieces.is_empty() {
continue;
}
let Some(row) = Row::new(destination.row.index() + i) else {
continue;
};
for (j, piece) in (0u32..).zip(pieces) {
let Some(col) = Col::new(destination.col.index() + j) else {
continue;
};
let at = CellRef::new(col, row);
if piece.is_empty() {
match ws.get(at) {
Some(cell) if cell.style != crate::style::StyleId::default() => {
ws.entry(at).value = CellValue::Empty;
}
Some(_) => {
ws.remove(at);
}
None => {}
}
} else {
ws.set(at, crate::reader::csv::value_of(&piece));
}
}
}
Ok(widest)
}
fn split_delimited(
text: &str,
delimiters: &[char],
merge_consecutive: bool,
qualifier: Option<char>,
) -> Vec<String> {
let mut out = Vec::new();
let mut current = String::new();
let mut quoted = false;
let mut after_delimiter = false;
let mut chars = text.chars().peekable();
while let Some(c) = chars.next() {
if Some(c) == qualifier && (quoted || current.is_empty()) {
if quoted && chars.peek() == Some(&c) {
current.push(c);
chars.next();
} else {
quoted = !quoted;
}
after_delimiter = false;
continue;
}
if !quoted && delimiters.contains(&c) {
if !(merge_consecutive && after_delimiter) {
out.push(std::mem::take(&mut current));
}
after_delimiter = true;
continue;
}
after_delimiter = false;
current.push(c);
}
out.push(current);
out
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn delimited_with_quotes_and_runs() {
let by = SplitBy::Delimited {
delimiters: vec![',', ' '],
merge_consecutive: true,
qualifier: Some('"'),
};
assert_eq!(
by.split(r#"a, "b, c" ,"say ""hi""""#),
["a", "b, c", "say \"hi\""].map(String::from)
);
assert_eq!(
SplitBy::delimiter(';').split("1;;x"),
["1", "", "x"].map(String::from)
);
assert_eq!(
SplitBy::delimiter(',').split(r#""a,b",c"#),
["a,b", "c"].map(String::from)
);
assert_eq!(
SplitBy::delimiter(',').split(r#"a"b,c"d"#),
[r#"a"b"#, r#"c"d"#].map(String::from)
);
}
#[test]
fn fixed_width_trims_and_ignores_breaks_past_the_end() {
let by = SplitBy::FixedWidth(vec![3, 8, 40]);
assert_eq!(
by.split("ab cdef gh"),
["ab", "cdef", "gh"].map(String::from)
);
assert_eq!(by.split("ab"), ["ab"].map(String::from));
}
}
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum FilterOutput {
InPlace,
CopyTo(usize, CellRef),
}
fn wildcard(pattern: &str, text: &str) -> bool {
let pattern: Vec<char> = pattern.to_lowercase().chars().collect();
let text: Vec<char> = text.to_lowercase().chars().collect();
wildcard_chars(&pattern, &text)
}
fn wildcard_chars(p: &[char], t: &[char]) -> bool {
match p.first() {
None => t.is_empty(),
Some('*') => (0..=t.len()).any(|i| wildcard_chars(&p[1..], &t[i..])),
Some('?') => !t.is_empty() && wildcard_chars(&p[1..], &t[1..]),
Some('~') if p.len() > 1 => t.first() == Some(&p[1]) && wildcard_chars(&p[2..], &t[1..]),
Some(c) => t.first() == Some(c) && wildcard_chars(&p[1..], &t[1..]),
}
}
fn passes(criterion: &str, shown: &str, number: Option<f64>) -> bool {
let criterion = criterion.trim();
if criterion.is_empty() {
return true;
}
let (op, operand) = ["<>", ">=", "<=", "=", ">", "<"]
.iter()
.find_map(|op| criterion.strip_prefix(op).map(|rest| (*op, rest)))
.unwrap_or(("", criterion));
let wanted = operand.trim().parse::<f64>().ok();
let order = match (number, wanted) {
(Some(n), Some(w)) => Some(n.total_cmp(&w)),
_ if op.starts_with(['<', '>']) && op != "<>" => {
(number.is_none() && wanted.is_none() && !shown.is_empty())
.then(|| shown.to_lowercase().cmp(&operand.to_lowercase()))
}
_ => None,
};
let same = || match (number, wanted) {
(Some(n), Some(w)) => n.total_cmp(&w).is_eq(),
_ => wildcard(operand, shown),
};
match op {
"" => match (number, wanted) {
(Some(n), Some(w)) => n.total_cmp(&w).is_eq(),
_ => wildcard(&format!("{operand}*"), shown),
},
"=" if operand.is_empty() => shown.is_empty(),
"=" => same(),
"<>" if operand.is_empty() => !shown.is_empty(),
"<>" => !same(),
">" => order.is_some_and(std::cmp::Ordering::is_gt),
"<" => order.is_some_and(std::cmp::Ordering::is_lt),
">=" => order.is_some_and(std::cmp::Ordering::is_ge),
"<=" => order.is_some_and(std::cmp::Ordering::is_le),
_ => false,
}
}
pub fn advanced_filter(
book: &mut Spreadsheet,
sheet: usize,
list: Range,
criteria: Range,
output: FilterOutput,
unique: bool,
) -> Result<usize> {
let ws = book
.sheet(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?;
let header = list.start.row;
let last_row = ws.dimension().map_or_else(
|| header.index(),
|used| list.end.row.index().min(used.end.row.index()),
);
let columns: Vec<Col> = (list.start.col.index()..=list.end.col.index())
.filter_map(Col::new)
.collect();
let shown = |c: Col, r: Row| book.formatted(sheet, CellRef::new(c, r));
let names: Vec<String> = columns
.iter()
.map(|&c| shown(c, header).to_lowercase())
.collect();
let mut tests: Vec<(Col, Col)> = Vec::new();
for index in criteria.start.col.index()..=criteria.end.col.index() {
let Some(col) = Col::new(index) else { continue };
let name = shown(col, criteria.start.row).to_lowercase();
if name.is_empty() {
continue;
}
let at = names
.iter()
.position(|n| *n == name)
.ok_or_else(|| Error::InvalidRange(format!("the list has no column {name:?}")))?;
tests.push((col, columns[at]));
}
let criteria_rows: Vec<Vec<(Col, String)>> = (criteria.start.row.index() + 1
..=criteria.end.row.index())
.filter_map(Row::new)
.map(|r| tests.iter().map(|&(cc, lc)| (lc, shown(cc, r))).collect())
.collect();
let mut seen = HashSet::new();
let mut passed: Vec<Row> = Vec::new();
let mut failed: Vec<Row> = Vec::new();
for index in header.index() + 1..=last_row {
let Some(row) = Row::new(index) else { continue };
let ok = criteria_rows.is_empty()
|| criteria_rows.iter().any(|conditions| {
conditions.iter().all(|(col, criterion)| {
let at = CellRef::new(*col, row);
let number = match ws.get(at).map(|c| c.value.result()) {
Some(CellValue::Number(n)) => Some(*n),
_ => None,
};
passes(criterion, &shown(*col, row), number)
})
});
let fresh = !unique || {
let key: Vec<String> = columns
.iter()
.map(|&c| shown(c, row).to_lowercase())
.collect();
!ok || seen.insert(key)
};
if ok && fresh {
passed.push(row);
} else {
failed.push(row);
}
}
match output {
FilterOutput::InPlace => {
let ws = book
.sheet_mut(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?;
for row in &passed {
if let Some(p) = ws.rows.get_mut(row) {
p.hidden = false;
}
}
for row in failed {
ws.rows.entry(row).or_default().hidden = true;
}
}
FilterOutput::CopyTo(to_sheet, to) => {
copy_rows(book, sheet, list, &columns, header, &passed, (to_sheet, to))?;
}
}
Ok(passed.len())
}
fn copy_rows(
book: &mut Spreadsheet,
sheet: usize,
list: Range,
columns: &[Col],
header: Row,
passed: &[Row],
(to_sheet, to): (usize, CellRef),
) -> Result<()> {
let ws = book
.sheet(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?;
let width = list.width();
let height = u32::try_from(passed.len())
.unwrap_or(u32::MAX)
.saturating_add(1);
let end = CellRef::new(
Col::new(to.col.index() + width - 1).ok_or(Error::WouldPushOffSheet)?,
Row::new(to.row.index() + height - 1).ok_or(Error::WouldPushOffSheet)?,
);
if to_sheet == sheet && Range::new(to, end).intersects(&list) {
return Err(Error::InvalidRange(
"the copy would land inside the list".into(),
));
}
let source_rows: Vec<Row> = std::iter::once(header)
.chain(passed.iter().copied())
.collect();
let cells: Vec<Vec<Option<Cell>>> = source_rows
.iter()
.map(|&r| {
columns
.iter()
.map(|&c| {
ws.get(CellRef::new(c, r)).map(|cell| Cell {
value: cell.value.result().clone(),
..cell.clone()
})
})
.collect()
})
.collect();
let target = book
.sheet_mut(to_sheet)
.ok_or(Error::SheetIndexOutOfRange(to_sheet))?;
let stale: Vec<CellRef> = target
.iter()
.map(|(at, _)| at)
.filter(|at| at.row >= to.row && at.col >= to.col && at.col <= end.col)
.collect();
for at in stale {
target.remove(at);
}
for (r, row) in (0u32..).zip(cells) {
for (c, cell) in (0u32..).zip(row) {
let (Some(cell), Some(col), Some(rr)) = (
cell,
Col::new(to.col.index() + c),
Row::new(to.row.index() + r),
) else {
continue;
};
*target.entry(CellRef::new(col, rr)) = cell;
}
}
Ok(())
}
pub type ConsolidateSource = (usize, Range);
fn aggregate(how: super::SubtotalFunction, numbers: &[f64], filled: usize) -> Option<f64> {
use super::SubtotalFunction as F;
#[allow(clippy::cast_precision_loss, reason = "counts of cells")]
let n = numbers.len() as f64;
let sum: f64 = numbers.iter().sum();
let mean = sum / n;
let squares = || numbers.iter().map(|x| (x - mean).powi(2)).sum::<f64>();
#[allow(clippy::cast_precision_loss, reason = "counts of cells")]
match how {
F::Count => (filled > 0).then_some(filled as f64),
_ if numbers.is_empty() => None,
F::Sum => Some(sum),
F::CountNumbers => Some(n),
F::Average => Some(mean),
F::Max => numbers.iter().copied().reduce(f64::max),
F::Min => numbers.iter().copied().reduce(f64::min),
F::Product => Some(numbers.iter().product()),
F::StdDev => (n > 1.0).then(|| (squares() / (n - 1.0)).sqrt()),
F::StdDevP => Some((squares() / n).sqrt()),
F::Var => (n > 1.0).then(|| squares() / (n - 1.0)),
F::VarP => Some(squares() / n),
}
}
#[allow(
clippy::too_many_lines,
reason = "labels, matching and writing in one pass"
)]
pub fn consolidate(
book: &mut Spreadsheet,
sources: &[ConsolidateSource],
destination: (usize, CellRef),
how: super::SubtotalFunction,
top_row: bool,
left_column: bool,
) -> Result<Range> {
if sources.is_empty() {
return Err(Error::InvalidRange("nothing to consolidate".into()));
}
let mut row_labels: Vec<String> = Vec::new();
let mut col_labels: Vec<String> = Vec::new();
let label_index = |labels: &mut Vec<String>, label: String| -> usize {
labels
.iter()
.position(|l| l.to_lowercase() == label.to_lowercase())
.unwrap_or_else(|| {
labels.push(label);
labels.len() - 1
})
};
let mut cells: std::collections::HashMap<(usize, usize), (Vec<f64>, usize)> =
std::collections::HashMap::new();
let (mut rows, mut cols) = (0usize, 0usize);
for &(sheet, range) in sources {
let ws = book
.sheet(sheet)
.ok_or(Error::SheetIndexOutOfRange(sheet))?;
let used = ws.dimension();
let last_row = used.map_or_else(
|| range.start.row.index(),
|u| range.end.row.index().min(u.end.row.index()),
);
let last_col = used.map_or_else(
|| range.start.col.index(),
|u| range.end.col.index().min(u.end.col.index()),
);
let first_row = range.start.row.index() + u32::from(top_row);
let first_col = range.start.col.index() + u32::from(left_column);
let text = |c: u32, r: u32| match (Col::new(c), Row::new(r)) {
(Some(c), Some(r)) => book.formatted(sheet, CellRef::new(c, r)),
_ => String::new(),
};
for r in first_row..=last_row {
for c in first_col..=last_col {
let (Some(col), Some(row)) = (Col::new(c), Row::new(r)) else {
continue;
};
let rk = if left_column {
label_index(&mut row_labels, text(range.start.col.index(), r))
} else {
(r - first_row) as usize
};
let ck = if top_row {
label_index(&mut col_labels, text(c, range.start.row.index()))
} else {
(c - first_col) as usize
};
rows = rows.max(rk + 1);
cols = cols.max(ck + 1);
let entry = cells.entry((rk, ck)).or_default();
match ws
.get(CellRef::new(col, row))
.map(|cell| cell.value.result())
{
Some(CellValue::Number(n)) => {
entry.0.push(*n);
entry.1 += 1;
}
Some(v) if !v.is_empty() => entry.1 += 1,
_ => {}
}
}
}
}
let (to_sheet, to) = destination;
let off_r = u32::from(top_row);
let off_c = u32::from(left_column);
let height = u32::try_from(rows).unwrap_or(u32::MAX) + off_r;
let width = u32::try_from(cols).unwrap_or(u32::MAX) + off_c;
let end = CellRef::new(
Col::new(to.col.index() + width.max(1) - 1).ok_or(Error::WouldPushOffSheet)?,
Row::new(to.row.index() + height.max(1) - 1).ok_or(Error::WouldPushOffSheet)?,
);
let target = book
.sheet_mut(to_sheet)
.ok_or(Error::SheetIndexOutOfRange(to_sheet))?;
let place = |dr: u32, dc: u32| {
CellRef::new(
Col::new(to.col.index() + dc).unwrap_or(to.col),
Row::new(to.row.index() + dr).unwrap_or(to.row),
)
};
for (i, label) in (0u32..).zip(&col_labels) {
target.set(place(0, off_c + i), label.as_str());
}
for (i, label) in (0u32..).zip(&row_labels) {
target.set(place(off_r + i, 0), label.as_str());
}
for ((rk, ck), (numbers, filled)) in &cells {
let (Ok(r), Ok(c)) = (u32::try_from(*rk), u32::try_from(*ck)) else {
continue;
};
let at = place(off_r + r, off_c + c);
match aggregate(how, numbers, *filled) {
Some(v) => target.set(at, v),
None => {
target.remove(at);
}
}
}
Ok(Range::new(to, end))
}