xlsx_batch_reader 0.4.21

//! An Excel/OpenDocument Spreadsheets file batch reader, in pure Rust. This crate supports Office 2007 or newer file formats(xlsx, xlsm, etc). The most obvious difference from other Excel file reading crates is that it does not read the whole file into memory, but read in batches. So that it can maintain low memory usage, especially when reading large files.
use chrono::Local;
use anyhow::{anyhow, Result};
use lazy_static::lazy_static;
use regex::Regex;
use read::FromCellValue;

/// Excel file reader
pub mod read;
/// Excel file writer
#[cfg(feature = "xlsxwriter")]
pub mod write;


/// reexport
pub use zip;
pub use chrono;
#[cfg(feature = "xlsxwriter")]
pub use rust_xlsxwriter;

/// days since UNIX epoch
pub type Date32 = i32;
/// seconds since UNIX epoch
#[derive(Debug)]
pub struct Timestamp(i64);
/// seconds since midnight
#[derive(Debug)]
pub struct Timesecond(i32);
/// row number
pub type RowNum = u32;
/// column number
pub type ColNum = u16;
/// merged range
pub type MergedRange = ((RowNum, ColNum), (RowNum, ColNum));

/// max column number
pub static MAX_COL_NUM: u16 = std::u16::MAX;
/// max row number
pub static MAX_ROW_NUM: u32 = std::u32::MAX;

impl Timestamp {
    /// use cell value as UTC datatime and get timestamp
    pub fn utc(&self) -> i64 {
        self.0
    }
    /// use cell value as local datatime and get timestamp, the local time zone is determined by the time zone in which it runs
    pub fn local(&self) -> i64 {
        self.0 - *LOCAL_OFFSET
    }
}

// i64 into Timestamp
impl Into<Timestamp> for i64 {
    fn into(self) -> Timestamp {
        Timestamp(self)
    }
}

// f64 into Timestamp
impl Into<Timestamp> for f64 {
    fn into(self) -> Timestamp {
        Timestamp(self as i64)
    }
}

// i32 into Timesecond
impl Into<Timesecond> for i32 {
    fn into(self) -> Timesecond {
        Timesecond(self)
    }
}

// Timesecond into i32
impl From<Timesecond> for i32 {
    fn from(ts: Timesecond) -> i32 {
        ts.0
    }
}

/// Convert character based Excel cell column addresses to number. If you pass parameter D to this function, you will get 4
pub fn get_num_from_ord(addr: &[u8]) -> Result<ColNum>{
    let mut i: usize;
    let mut j: ColNum;
    let mut col: ColNum;
    let addr = addr.to_ascii_uppercase();
    (col, i, j) = (0, addr.len(), 1);
    while i > 0 {
        i -= 1;
        if addr[i] > b'@' {
            col += ((addr[i] - b'@') as ColNum) * j;
            j *= 26;
        }
    };
    Ok(col)
}

/// Convert number based Excel cell column addresses to character. If you pass parameter 4 to this function, you will get D
pub fn get_ord_from_num(num: ColNum) -> Result<String> {
    let mut col = num;
    let mut addr = Vec::with_capacity(2);
    while col > 26 {
        addr.push(((col % 26) + 64) as u8 as char);
        col = col / 26;
    };
    addr.push((col + 64) as u8 as char);
    addr.reverse();
    Ok(String::from_iter(addr))
}

/// Convert character based Excel cell addresses to numbers. If you pass parameter D2 to this function, you will get (2, 4)
pub fn get_tuple_from_ord(addr: &[u8]) -> Result<(RowNum, ColNum)> {
    let mut i: usize;
    let mut j: ColNum;
    let mut col: ColNum;
    let mut row: Option<RowNum> = None;
    let addr = addr.to_ascii_uppercase();
    (col, i, j) = (0, addr.len(), 1);
    while i > 0 {
        i -= 1;
        if addr[i] > b'@' {
            if row.is_none() {
                row = Some(String::from_utf8(addr[i+1..].to_vec())?.parse::<RowNum>()?);
            };
            col += ((addr[i] - b'@') as ColNum) * j;
            j *= 26;
        }
    };
    if let Some(row) = row {
        Ok((row, col))
    } else {
        return Err(anyhow!("invalid cell address: {:?}", addr))
    }
}

/// Convert numbers based Excel cell addresses to characters. If you pass parameter (2, 4) to this function, you will get D2.
pub fn get_ord_from_tuple(row: RowNum, col: ColNum) -> Result<String> {
    match get_ord_from_num(col) {
        Ok(col) => {
            Ok(format!("{}{}", col, row))
        },
        Err(e) => Err(e)
    }
}

/// check whether the cell is a merged cell. If it is the first cell in the merged area, return the size of the merged area. RowNum and ColNum start from 1.
pub fn is_merged_cell(mgs: &Vec<MergedRange>, row: RowNum, col: ColNum) -> (bool, Option<(RowNum, ColNum)>) {
    for (left_top, right_end) in mgs {
        if left_top.0 <= row && left_top.1 <= col && right_end.0 >= row && right_end.1 >= col {
            if left_top.0 == row && left_top.1 == col {
                return (true, Some((right_end.0-row+1, right_end.1-col+1)))
            } else {
                return (true, None);
            }
        };
    }
    return (false, None)
}

/// Cell Value Type
#[derive(Debug, Clone)]
pub enum CellValue<'a> {
    Blank,
    Bool(bool),
    Number(f64),
    Date(f64),
    Time(f64),
    Datetime(f64),
    Shared(&'a String),
    String(String),
    Error(String),
    Formula(String)
}

impl<'a> CellValue<'a> {
    /// Attention: as to blank cell, String will return String::new(), and other types will return None.
    pub fn get<T: FromCellValue>(&'a self) -> Result<Option<T>> {
        T::try_from_cval(self)
    }
}


lazy_static! {
    /// local time zone offset
    static ref LOCAL_OFFSET: i64 = Local::now().offset().local_minus_utc() as i64;
    /// regex for matching ${...} template expressions in formulas
    static ref FORMULA_RE: Regex = Regex::new(r"\$\{([^}]*)\}").unwrap();
}

// ---- Formula template resolution ----

/// Headercols: (sheet_names_vec, sheet_name -> header_name -> col_index_str)
type HeaderCols<'a> = Option<(&'a Vec<String>, &'a std::collections::HashMap<String, std::collections::HashMap<String, String>>)>;

#[derive(Debug, Clone, Copy, PartialEq)]
enum ExprKind { Row, Col }

struct Evaluator {
    chars: Vec<char>,
    pos: usize,
    row: i64,
    col: i64,
    kind: Option<ExprKind>,
}

impl Evaluator {
    fn peek(&self) -> Option<char> {
        self.chars.get(self.pos).copied()
    }

    fn advance(&mut self) {
        self.pos += 1;
    }

    fn skip_ws(&mut self) {
        while self.peek().map_or(false, |c| c.is_whitespace()) {
            self.advance();
        }
    }

    /// Parse: expr ::= factor (('+' | '-') factor)*
    fn parse(&mut self) -> Result<i64> {
        self.skip_ws();
        let mut left = self.parse_factor()?;
        loop {
            self.skip_ws();
            match self.peek() {
                Some('+') => {
                    self.advance();
                    left = left.checked_add(self.parse_factor()?)
                        .ok_or_else(|| anyhow!("overflow in addition"))?;
                }
                Some('-') => {
                    self.advance();
                    left = left.checked_sub(self.parse_factor()?)
                        .ok_or_else(|| anyhow!("overflow in subtraction"))?;
                }
                _ => break,
            }
        }
        Ok(left)
    }

    /// factor ::= Integer | '$' ('r' | 'c') | '(' expr ')'
    fn parse_factor(&mut self) -> Result<i64> {
        self.skip_ws();
        match self.peek() {
            Some('0'..='9') => self.parse_integer(),
            Some('$') => self.parse_variable(),
            Some('(') => {
                self.advance();
                let val = self.parse()?;
                self.skip_ws();
                if self.peek() != Some(')') {
                    return Err(anyhow!("missing closing parenthesis"));
                }
                self.advance();
                Ok(val)
            }
            Some(c) => Err(anyhow!("unexpected character: '{}'", c)),
            None => Err(anyhow!("unexpected end of expression")),
        }
    }

    fn parse_integer(&mut self) -> Result<i64> {
        let start = self.pos;
        while self.peek().map_or(false, |c| c.is_ascii_digit()) {
            self.advance();
        }
        let s: String = self.chars[start..self.pos].iter().collect();
        s.parse::<i64>().map_err(|_| anyhow!("invalid integer: {}", s))
    }

    fn parse_variable(&mut self) -> Result<i64> {
        self.advance(); // skip '$'
        match self.peek() {
            Some('r') => {
                self.advance();
                if self.kind == Some(ExprKind::Col) {
                    return Err(anyhow!("cannot mix $r and $c in one expression"));
                }
                self.kind = Some(ExprKind::Row);
                Ok(self.row)
            }
            Some('c') => {
                self.advance();
                if self.kind == Some(ExprKind::Row) {
                    return Err(anyhow!("cannot mix $r and $c in one expression"));
                }
                self.kind = Some(ExprKind::Col);
                Ok(self.col)
            }
            Some(c) => Err(anyhow!("unknown variable '$ {}'", c)),
            None => Err(anyhow!("unexpected end after '$'")),
        }
    }
}

fn eval_col_fn(expr: &str, row: RowNum, col: ColNum, headercols: HeaderCols) -> Result<String> {
    let trimmed = expr.trim();

    // Try `part1!part2` pattern when headercols is available
    if let Some((sheet_names, header_map)) = headercols {
        if let Some((part1, part2)) = trimmed.split_once('!') {
            // Resolve part1 → sheet name
            let sheet_name = if part1.chars().all(|c| c.is_ascii_digit()) {
                part1.parse::<usize>().ok()
                    .and_then(|idx| sheet_names.get(idx))
                    .map(|s| s.as_str())
                    .unwrap_or(part1)
            } else {
                part1
            };

            // Look up sheet → header → column index
            if let Some(cols) = header_map.get(sheet_name) {
                if let Some((sub1, sub2)) = part2.split_once(':') {
                    if let Some(ord1) = cols.get(sub1) {
                        if let Some(ord2) = cols.get(sub2) {
                            return Ok(format!("{}!{}:{}", sheet_name, ord1, ord2));
                        } else {
                            return Err(anyhow!("header '{}' not found in sheet '{}'", sub2, sheet_name));
                        }
                    } else {
                        return Err(anyhow!("header '{}' not found in sheet '{}'", sub1, sheet_name));
                    }
                } else if let Some(col_ord) = cols.get(part2) {
                    return Ok(format!("{}!{}", sheet_name, col_ord));
                }
                return Err(anyhow!("header '{}' not found in sheet '{}'", part2, sheet_name));
            }
            return Err(anyhow!("sheet '{}' not found in headercols", sheet_name));
        }
    }

    // Fall through: evaluate as arithmetic expression
    let mut eval = Evaluator {
        chars: expr.chars().collect(),
        pos: 0,
        row: row as i64,
        col: col as i64,
        kind: None,
    };
    let value = eval.parse()?;
    eval.skip_ws();
    if eval.pos != eval.chars.len() {
        return Err(anyhow!("unexpected trailing characters in col()"));
    }
    if value < 1 {
        return Err(anyhow!("column number out of range: {}. Must be >= 1", value));
    }
    if value > ColNum::MAX as i64 {
        return Err(anyhow!("column number too large: {}", value));
    }
    get_ord_from_num(value as ColNum)
}

fn evaluate_expr(expr: &str, row: RowNum, col: ColNum, headercols: HeaderCols) -> Result<String> {
    let trimmed = expr.trim();
    if trimmed.is_empty() {
        return Err(anyhow!("empty expression in ${{}}"));
    }

    // `col(N)` — evaluate N and convert to column letter
    if let Some(inner) = trimmed
        .strip_prefix("col(")
        .and_then(|s| s.strip_suffix(")"))
    {
        return eval_col_fn(inner, row, col, headercols);
    }

    let mut eval = Evaluator {
        chars: expr.chars().collect(),
        pos: 0,
        row: row as i64,
        col: col as i64,
        kind: None,
    };

    let value = eval.parse()?;
    eval.skip_ws();
    if eval.pos != eval.chars.len() {
        return Err(anyhow!("unexpected trailing characters"));
    }

    match eval.kind {
        Some(ExprKind::Row) => {
            if value < 1 {
                return Err(anyhow!("row number out of range: {}. Must be >= 1", value));
            }
            Ok(value.to_string())
        }
        Some(ExprKind::Col) => {
            if value < 1 {
                return Err(anyhow!("column number out of range: {}. Must be >= 1", value));
            }
            if value > ColNum::MAX as i64 {
                return Err(anyhow!("column number too large: {}", value));
            }
            get_ord_from_num(value as ColNum)
        }
        None => {
            // Pure constant expression (no $r/$c) — return as integer string
            Ok(value.to_string())
        }
    }
}

/// Resolve `${...}` template expressions in a formula string.
///
/// Inside `${...}`, `$r` refers to the current row (1-based, as integer),
/// `$c` refers to the current column (1-based, converted to column letter via [`get_ord_from_num`]).
/// `col(N)` evaluates N and converts it to a column letter (e.g., `col(2)` → `"B"`).
/// Only `+`, `-`, and `()` are supported inside the expression.
///
/// Returns an error if:
/// - The expression syntax is invalid
/// - The resolved row is < 1 or column is < 1
/// - Both `$r` and `$c` are used in the same expression (ambiguous)
///
/// # Arguments
/// * `formula` - The formula string containing zero or more `${...}` blocks
/// * `row` - Current row number (1-based, per Excel convention)
/// * `col` - Current column number (1-based, per Excel convention)
/// * `headercols` - Optional (sheet_names, sheet→header→col_index) map for `col(part1!part2)` syntax.
///   Pass `None` if not needed.
///
/// # Example
/// For cell E5 (row=5, col=5):
/// ```
/// use xlsx_batch_reader::resolve_formula;
/// let result = resolve_formula("SUM(${$c-3}${$r-1}:${$c-1}${$r-1})", 5, 5, None).unwrap();
/// assert_eq!(result, "SUM(B4:D4)");
/// ```
pub fn resolve_formula(formula: &str, row: RowNum, col: ColNum, headercols: HeaderCols) -> Result<String> {
    let mut result = String::with_capacity(formula.len());
    let mut last_end = 0;

    for caps in FORMULA_RE.captures_iter(formula) {
        let m = caps.get(0).unwrap();
        let inner = caps.get(1).unwrap().as_str();

        result.push_str(&formula[last_end..m.start()]);
        result.push_str(&evaluate_expr(inner, row, col, headercols)?);
        last_end = m.end();
    }

    result.push_str(&formula[last_end..]);
    Ok(result)
}