use std::fmt;
use crate::cell::formula::shift_references;
use crate::cell::utils::{column_index_from_string, coordinate_from_string, get_column_letter};
use crate::exceptions::{Error, Result};
const MAX_ROW: u32 = 1_048_576;
const MAX_COLUMN: u32 = 16_384;
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct TranslatorError {
pub reference: String,
pub reason: String,
}
impl fmt::Display for TranslatorError {
fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
write!(f, "cannot translate {}: {}", self.reference, self.reason)
}
}
impl std::error::Error for TranslatorError {}
impl From<TranslatorError> for Error {
fn from(err: TranslatorError) -> Self {
Error::Value(err.to_string())
}
}
#[derive(Debug, Clone)]
pub struct Translator {
formula: String,
origin: (u32, u32),
}
impl Translator {
pub fn new(formula: &str, origin: &str) -> Result<Self> {
Ok(Self {
formula: formula.strip_prefix('=').unwrap_or(formula).to_string(),
origin: coordinate_tuple(origin)?,
})
}
pub fn formula(&self) -> &str {
&self.formula
}
pub fn origin(&self) -> (u32, u32) {
self.origin
}
pub fn translate_formula(&self, dest: Option<&str>) -> Result<String> {
let (row_delta, col_delta) = match dest {
Some(dest) => {
let (row, col) = coordinate_tuple(dest)?;
(
row as i64 - self.origin.0 as i64,
col as i64 - self.origin.1 as i64,
)
}
None => (0, 0),
};
self.translate_formula_by(row_delta, col_delta)
}
pub fn translate_formula_by(&self, row_delta: i64, col_delta: i64) -> Result<String> {
if row_delta == 0 && col_delta == 0 {
return Ok(format!("={}", self.formula));
}
self.check_references_stay_on_the_grid(row_delta, col_delta)?;
let shifted = shift_references(&self.formula, row_delta, col_delta);
Ok(format!(
"={}",
self.translate_row_and_column_ranges(&shifted, row_delta, col_delta)?
))
}
fn check_references_stay_on_the_grid(&self, row_delta: i64, col_delta: i64) -> Result<()> {
let formula = &self.formula;
let bytes = formula.as_bytes();
let mut i = 0usize;
while i < bytes.len() {
let ch = bytes[i] as char;
if ch == '"' || ch == '\'' {
i = closing_quote(formula, i, ch as u8);
continue;
}
if i > 0 {
let before = bytes[i - 1] as char;
if before.is_ascii_alphanumeric() || before == '_' || before == '.' || before == '!'
{
i += 1;
continue;
}
}
let column = match read_column_token(formula, i) {
Some(token) if token.has_digits => token,
_ => {
i += 1;
continue;
}
};
let row = match read_row_token(formula, column.row_at) {
Some(row) => row,
None => {
i += 1;
continue;
}
};
if !column.absolute {
shift_column(column.index, col_delta, false, &column.text)?;
}
if !row.absolute {
shift_row(row.value, row_delta, false, &row.text)?;
}
i += column.len + row.len;
}
Ok(())
}
fn translate_row_and_column_ranges(
&self,
formula: &str,
row_delta: i64,
col_delta: i64,
) -> Result<String> {
let bytes = formula.as_bytes();
let mut out = String::with_capacity(formula.len());
let mut i = 0usize;
while i < bytes.len() {
let ch = bytes[i] as char;
if ch == '"' {
let end = closing_quote(formula, i, b'"');
out.push_str(&formula[i..end]);
i = end;
continue;
}
if ch == '\'' {
let end = closing_quote(formula, i, b'\'');
out.push_str(&formula[i..end]);
i = end;
continue;
}
if !(ch.is_ascii_alphanumeric() || ch == '$') {
out.push(ch);
i += 1;
continue;
}
if i > 0 {
let before = bytes[i - 1] as char;
if before.is_ascii_alphanumeric() || before == '_' || before == '.' || before == '!'
{
out.push(ch);
i += 1;
continue;
}
}
if let Some((len, text)) = self.translate_one_range(formula, i, row_delta, col_delta)? {
out.push_str(&text);
i += len;
continue;
}
let mut step = 1;
while i + step < bytes.len() && (bytes[i + step] & 0xC0) == 0x80 {
step += 1;
}
out.push_str(&formula[i..i + step]);
i += step;
}
Ok(out)
}
fn translate_one_range(
&self,
formula: &str,
start: usize,
row_delta: i64,
col_delta: i64,
) -> Result<Option<(usize, String)>> {
let bytes = formula.as_bytes();
if let Some((len, text)) = self.column_range_at(formula, start, col_delta)? {
return Ok(Some((len, text)));
}
if let Some((len, text)) = self.row_range_at(formula, start, row_delta)? {
return Ok(Some((len, text)));
}
let _ = bytes;
Ok(None)
}
fn column_range_at(
&self,
formula: &str,
start: usize,
col_delta: i64,
) -> Result<Option<(usize, String)>> {
let first = match read_column_token(formula, start) {
Some(token) => token,
None => return Ok(None),
};
let colon = start + first.len;
if formula.as_bytes().get(colon) != Some(&b':') {
return Ok(None);
}
if first.has_digits {
return Ok(None);
}
let second = match read_column_token(formula, colon + 1) {
Some(token) => token,
None => return Ok(None),
};
let end = colon + 1 + second.len;
let from = shift_column(first.index, col_delta, first.absolute, &first.text)?;
let to = shift_column(second.index, col_delta, second.absolute, &second.text)?;
let from_letter = get_column_letter(from).map_err(|_| out_of_bounds(&first.text))?;
let to_letter = get_column_letter(to).map_err(|_| out_of_bounds(&second.text))?;
let text = format!(
"{}{}:{}{}",
if first.absolute { "$" } else { "" },
from_letter,
if second.absolute { "$" } else { "" },
to_letter
);
Ok(Some((end - start, text)))
}
fn row_range_at(
&self,
formula: &str,
start: usize,
row_delta: i64,
) -> Result<Option<(usize, String)>> {
let first = match read_row_token(formula, start) {
Some(token) => token,
None => return Ok(None),
};
let colon = start + first.len;
if formula.as_bytes().get(colon) != Some(&b':') {
return Ok(None);
}
let second = match read_row_token(formula, colon + 1) {
Some(token) => token,
None => return Ok(None),
};
let end = colon + 1 + second.len;
let from = shift_row(first.value, row_delta, first.absolute, &first.text)?;
let to = shift_row(second.value, row_delta, second.absolute, &second.text)?;
Ok(Some((
end - start,
format!(
"{}{}:{}{}",
if first.absolute { "$" } else { "" },
from,
if second.absolute { "$" } else { "" },
to
),
)))
}
}
struct ColumnToken {
index: u32,
absolute: bool,
has_digits: bool,
len: usize,
row_at: usize,
text: String,
}
fn read_column_token(formula: &str, start: usize) -> Option<ColumnToken> {
let bytes = formula.as_bytes();
let mut i = start;
let mut absolute = false;
if bytes.get(i) == Some(&b'$') {
absolute = true;
i += 1;
}
let letters_start = i;
while i < bytes.len() && (bytes[i] as char).is_ascii_alphabetic() {
i += 1;
}
if i == letters_start || i - letters_start > 3 {
return None;
}
let text = formula[start..i].to_string();
let index = column_index_from_string(&formula[letters_start..i].to_uppercase()).ok()?;
let digits_start = i;
if bytes.get(i) == Some(&b'$') {
i += 1;
}
while i < bytes.len() && (bytes[i] as char).is_ascii_digit() {
i += 1;
}
Some(ColumnToken {
index,
absolute,
has_digits: i > digits_start,
len: i - start,
row_at: start + (digits_start - start),
text,
})
}
struct RowToken {
value: u32,
absolute: bool,
len: usize,
text: String,
}
fn read_row_token(formula: &str, start: usize) -> Option<RowToken> {
let bytes = formula.as_bytes();
let mut i = start;
let mut absolute = false;
if bytes.get(i) == Some(&b'$') {
absolute = true;
i += 1;
}
let digits_start = i;
while i < bytes.len() && (bytes[i] as char).is_ascii_digit() {
i += 1;
}
if i == digits_start {
return None;
}
let text = formula[start..i].to_string();
let value: u32 = formula[digits_start..i].parse().ok()?;
Some(RowToken {
value,
absolute,
len: i - start,
text,
})
}
fn shift_column(
index: u32,
delta: i64,
absolute: bool,
original: &str,
) -> Result<u32, TranslatorError> {
if absolute {
return Ok(index);
}
let moved = index as i64 + delta;
if moved < 1 || moved > MAX_COLUMN as i64 {
return Err(out_of_bounds(original));
}
Ok(moved as u32)
}
fn shift_row(
value: u32,
delta: i64,
absolute: bool,
original: &str,
) -> Result<u32, TranslatorError> {
if absolute {
return Ok(value);
}
let moved = value as i64 + delta;
if moved < 1 || moved > MAX_ROW as i64 {
return Err(out_of_bounds(original));
}
Ok(moved as u32)
}
fn out_of_bounds(reference: &str) -> TranslatorError {
TranslatorError {
reference: reference.to_string(),
reason: "it moves outside the grid, which Excel reports as #REF!".to_string(),
}
}
fn closing_quote(formula: &str, start: usize, quote: u8) -> usize {
let bytes = formula.as_bytes();
let mut i = start + 1;
while i < bytes.len() {
if bytes[i] == quote {
if i + 1 < bytes.len() && bytes[i + 1] == quote {
i += 2;
continue;
}
return i + 1;
}
i += 1;
}
formula.len()
}
fn coordinate_tuple(coordinate: &str) -> Result<(u32, u32)> {
let cleaned = coordinate.trim().trim_start_matches('=');
let (letters, row) = coordinate_from_string(cleaned)?;
let column = column_index_from_string(&letters)?;
if row == 0 {
return Err(Error::CellCoordinates(format!("{coordinate} has no row")));
}
Ok((row, column))
}
#[cfg(test)]
mod tests {
use super::*;
fn translated(formula: &str, origin: &str, dest: &str) -> String {
Translator::new(formula, origin)
.expect("translator")
.translate_formula(Some(dest))
.expect("translated")
}
#[test]
fn a_relative_reference_moves_with_the_formula() {
assert_eq!(translated("=A1*2", "B10", "C12"), "=B3*2");
}
#[test]
fn a_fixed_column_stays_put_while_the_row_moves() {
assert_eq!(translated("=$A1*2", "B10", "C12"), "=$A3*2");
}
#[test]
fn a_fixed_row_stays_put_while_the_column_moves() {
assert_eq!(translated("=A$1*2", "B10", "C12"), "=B$1*2");
}
#[test]
fn a_fully_fixed_reference_does_not_move_at_all() {
assert_eq!(translated("=$A$1*2", "B10", "C12"), "=$A$1*2");
}
#[test]
fn both_dollars_on_only_one_end_of_a_range_are_honoured() {
assert_eq!(translated("=SUM($A$1:B2)", "D4", "E5"), "=SUM($A$1:C3)");
}
#[test]
fn a_whole_row_range_moves() {
assert_eq!(translated("=SUM(3:4)", "A10", "A20"), "=SUM(13:14)");
}
#[test]
fn a_whole_row_range_with_dollars_stays_put() {
assert_eq!(translated("=SUM($3:$4)", "A10", "A20"), "=SUM($3:$4)");
}
#[test]
fn a_whole_column_range_moves() {
assert_eq!(translated("=SUM(A:B)", "A1", "C1"), "=SUM(C:D)");
}
#[test]
fn a_whole_column_range_with_dollars_stays_put() {
assert_eq!(translated("=SUM($A:$B)", "A1", "C1"), "=SUM($A:$B)");
}
#[test]
fn a_cell_range_is_not_mistaken_for_a_whole_column_range() {
assert_eq!(translated("=SUM(A1:A9)", "C3", "D4"), "=SUM(B2:B10)");
}
#[test]
fn a_function_name_is_not_mistaken_for_a_column_range() {
assert_eq!(translated("=LOG10(A1)", "C3", "D4"), "=LOG10(B2)");
}
#[test]
fn a_defined_name_is_left_alone() {
assert_eq!(translated("=SUM(Totals)+A1", "C3", "D4"), "=SUM(Totals)+B2");
}
#[test]
fn a_string_literal_is_not_translated() {
assert_eq!(
translated("=IF(A1=\"3:4\",\"A:B\",B1)", "C3", "D4"),
"=IF(B2=\"3:4\",\"A:B\",C2)"
);
}
#[test]
fn a_sheet_qualified_reference_moves_its_cell_but_keeps_the_sheet() {
assert_eq!(translated("=Data!B2", "A1", "C1"), "=Data!D2");
}
#[test]
fn a_fully_anchored_reference_cannot_go_off_the_grid() {
assert_eq!(translated("=$A$1", "B10", "B5"), "=$A$1");
}
#[test]
fn a_relative_reference_that_would_leave_the_grid_is_an_error() {
let err = Translator::new("=A1", "B10")
.expect("translator")
.translate_formula(Some("B5"));
let message = err.expect_err("off the top of the grid").to_string();
assert!(message.contains("#REF!"), "{message}");
}
#[test]
fn translating_to_the_origin_is_the_identity() {
let original = "=SUM(A1:B2)+$C$3+SUM(4:5)";
assert_eq!(
Translator::new(original, "D7")
.unwrap()
.translate_formula(None)
.unwrap(),
original
);
assert_eq!(translated(original, "D7", "D7"), original);
}
#[test]
fn a_leading_equals_sign_is_optional() {
assert_eq!(
Translator::new("A1+1", "B2")
.unwrap()
.translate_formula(Some("C3"))
.unwrap(),
"=B2+1"
);
}
#[test]
fn an_empty_formula_stays_empty() {
assert_eq!(
Translator::new("", "A1")
.unwrap()
.translate_formula(Some("B2"))
.unwrap(),
"="
);
}
#[test]
fn a_literal_alone_is_returned_unchanged() {
assert_eq!(translated("42", "A1", "B2"), "=42");
}
}