repo_description: A fast terminal Excel viewer with an interactive TUI. Features full-text search, formula display, lazy loading for large files, clipboard support, and export to CSV/JSON. Built with Rust and ratatui.
repo_topics: cli excel ratatui rust rust-lang spreadsheet spreadsheets tui
code: use anyhow::{Context, Result, anyhow};
  use calamine::{Data, Range, Reader, Sheets, Table, open_workbook_auto};
  use chrono::{Duration, NaiveDate};
  use std::path::Path;
  
  pub struct Workbook {
      sheets: Sheets<std::io::BufReader<std::fs::File>>,
  }
  
  impl Workbook {
      pub fn open(path: impl AsRef<Path>) -> Result<Self> {
          let sheets = open_workbook_auto(path.as_ref()).context("Failed to open workbook")?;
  
          Ok(Self { sheets })
      }
  
      pub fn sheet_names(&self) -> Vec<String> {
          self.sheets.sheet_names()
      }
  
      /// Loads all rows eagerly into memory
      pub fn load_sheet(&mut self, name: &str) -> Result<SheetData> {
          let range = self
              .sheets
              .worksheet_range(name)
              .with_context(|| format!("Sheet '{name}' not found"))?;
  
          // Try to load formulas, but don't fail if they're not available
          let formula_range = self.sheets.worksheet_formula(name).ok();
  
          Ok(SheetData::from_range_with_formulas(range, formula_range))
      }
  
      /// Loads only headers; rows fetched on demand
      pub fn load_sheet_lazy(&mut self, name: &str) -> Result<LazySheetData> {
          let range = self
              .sheets
              .worksheet_range(name)
              .with_context(|| format!("Sheet '{name}' not found"))?;
  
          // Try to load formulas, but don't fail if they're not available
          let formula_range = self.sheets.worksheet_formula(name).ok();
  
          Ok(LazySheetData::from_range_with_formulas(
              range,
              formula_range,
          ))
      }
  
      // ===== Table API (Xlsx only) =====
  
      /// Load table metadata from the workbook (Xlsx only)
      pub fn load_tables(&mut self) -> Result<()> {
          match &mut self.sheets {
              Sheets::Xlsx(xlsx) => xlsx
                  .load_tables()
                  .context("Failed to load table metadata")
                  .map_err(|e| anyhow!("{e}")),
              _ => Err(anyhow!("Tables are only supported in .xlsx files")),
          }
      }
  
      /// Get all table names in the workbook (Xlsx only)
      pub fn table_names(&self) -> Result<Vec<String>> {
          match &self.sheets {
              Sheets::Xlsx(xlsx) => Ok(xlsx.table_names().iter().map(|s| (*s).clone()).collect()),
              _ => Err(anyhow!("Tables are only supported in .xlsx files")),
          }
      }
  
      /// Get table names in a specific sheet (Xlsx only)
      pub fn table_names_in_sheet(&self, sheet_name: &str) -> Result<Vec<String>> {
          match &self.sheets {
              Sheets::Xlsx(xlsx) => Ok(xlsx
                  .table_names_in_sheet(sheet_name)
                  .iter()
                  .map(|s| (*s).clone())
                  .collect()),
              _ => Err(anyhow!("Tables are only supported in .xlsx files")),
          }
      }
  
      /// Get table data by name (Xlsx only)
      pub fn table_by_name(&mut self, table_name: &str) -> Result<TableData> {
          match &mut self.sheets {
              Sheets::Xlsx(xlsx) => {
                  let table = xlsx
                      .table_by_name(table_name)
                      .map_err(|e| anyhow!("Table '{table_name}' not found: {e}"))?;
  
                  Ok(TableData::from_calamine_table(table))
              }
              _ => Err(anyhow!("Tables are only supported in .xlsx files")),
          }
      }
  }
  
  /// Eagerly-loaded sheet data (loads all rows immediately)
  #[derive(Debug, Clone)]
  pub struct SheetData {
      pub headers: Vec<String>,
      pub rows: Vec<Vec<CellValue>>,
      pub formulas: Vec<Vec<Option<String>>>, // Parallel structure to rows with formulas
      pub width: usize,
      pub height: usize,
  }
  
  /// Lazy-loaded sheet data (loads rows on demand)
  pub struct LazySheetData {
      range: Range<Data>,
      formula_range: Option<Range<String>>,
      pub headers: Vec<String>,
      pub width: usize,
      pub height: usize,
  }
  
  impl LazySheetData {
      /// Extracts headers only; defers row loading
      pub fn from_range_with_formulas(
          range: Range<Data>,
          formula_range: Option<Range<String>>,
      ) -> Self {
          let (height, width) = range.get_size();
  
          // Only extract headers (first row) - don't load all data yet
          let headers = if height > 0 {
              range
                  .rows()
                  .next()
                  .map(|row| row.iter().map(SheetData::cell_to_string).collect())
                  .unwrap_or_default()
          } else {
              vec![]
          };
  
          Self {
              range,
              formula_range,
              headers,
              width,
              height: height.saturating_sub(1), // Don't count header row
          }
      }
  
      /// Zero-indexed row range; header excluded
      pub fn get_rows(
          &self,
          start: usize,
          count: usize,
      ) -> (Vec<Vec<CellValue>>, Vec<Vec<Option<String>>>) {
          let end = (start + count).min(self.height);
  
          // Extract requested rows (skip header + start rows, take count)
          let rows: Vec<Vec<CellValue>> = self
              .range
              .rows()
              .skip(1 + start) // Skip header + start offset
              .take(end - start)
              .map(|row| row.iter().map(SheetData::datatype_to_cellvalue).collect())
              .collect();
  
          // Extract formulas for requested rows
          let formulas = self.get_formulas_for_range(start, end);
  
          (rows, formulas)
      }
  
      fn get_formulas_for_range(&self, start: usize, end: usize) -> Vec<Vec<Option<String>>> {
          if let Some(ref formula_range) = self.formula_range {
              let formula_start = formula_range.start().unwrap_or((0, 0));
              let total_height = self.height + 1; // Include header in total
  
              // Create formula grid only for requested rows
              let mut formula_grid: Vec<Vec<Option<String>>> =
                  vec![vec![None; self.width]; end - start];
  
              // Populate formulas at their absolute positions
              for (row_offset, formula_row) in formula_range.rows().enumerate() {
                  let absolute_row = formula_start.0 as usize + row_offset;
  
                  if absolute_row > 0 && absolute_row <= total_height {
                      let data_row_idx = absolute_row - 1; // Convert to 0-based data row index
  
                      // Only process if this row is in our requested range
                      if data_row_idx >= start && data_row_idx < end {
                          let result_idx = data_row_idx - start; // Index in result array
  
                          for (col_offset, formula_str) in formula_row.iter().enumerate() {
                              let absolute_col = formula_start.1 as usize + col_offset;
                              if absolute_col < self.width && !formula_str.is_empty() {
                                  formula_grid[result_idx][absolute_col] = Some(formula_str.clone());
                              }
                          }
                      }
                  }
              }
  
              formula_grid
          } else {
              // No formulas available
              vec![vec![None; self.width]; end - start]
          }
      }
  
      /// Consumes lazy data and loads all rows into memory
      #[allow(clippy::wrong_self_convention)]
      pub fn to_sheet_data(self) -> SheetData {
          SheetData::from_range_with_formulas(self.range, self.formula_range)
      }
  }
  
  #[derive(Debug, Clone)]
  pub enum CellValue {
      Empty,
      String(String),
      Int(i64),
      Float(f64),
      Bool(bool),
      Error(String),
      DateTime(f64), // Excel datetime as float
  }
  
  impl CellValue {
      #[allow(dead_code)]
      pub fn is_empty(&self) -> bool {
          matches!(self, CellValue::Empty)
      }
  
      #[allow(dead_code)]
      pub fn is_numeric(&self) -> bool {
          matches!(self, CellValue::Int(_) | CellValue::Float(_))
      }
  
      /// Returns unformatted value (for export/clipboard)
      pub fn to_raw_string(&self) -> String {
          match self {
              CellValue::Empty => String::new(),
              CellValue::String(s) => s.clone(),
              CellValue::Int(i) => i.to_string(),
              CellValue::Float(val) => {
                  if val.fract() == 0.0 {
                      format!("{val:.0}")
                  } else {
                      val.to_string()
                  }
              }
              CellValue::Bool(b) => b.to_string(),
              CellValue::Error(e) => format!("#{e}"),
              CellValue::DateTime(dt) => {
                  let days = dt.floor() as i64;
                  // Excel epoch: December 31, 1899 (Excel serial 0)
                  let epoch = NaiveDate::from_ymd_opt(1899, 12, 31).unwrap();
                  // Adjust for Excel's 1900 leap year bug (day 60 = Feb 29, 1900 which didn't exist)
                  let adjusted_days = if days > 60 { days - 1 } else { days };
                  let date = epoch + Duration::days(adjusted_days);
                  let time_fraction = dt.fract();
                  let total_seconds = (time_fraction * 86400.0).round() as i64;
                  let hours = total_seconds / 3600;
                  let minutes = (total_seconds % 3600) / 60;
                  let seconds = total_seconds % 60;
  
                  if time_fraction.abs() < 0.0000001 {
                      format!("{}", date.format("%Y-%m-%d"))
                  } else {
                      format!(
                          "{} {:02}:{:02}:{:02}",
                          date.format("%Y-%m-%d"),
                          hours,
                          minutes,
                          seconds
                      )
                  }
              }
          }
      }
  }
  
  /// Excel Table data
  #[derive(Debug, Clone)]
  pub struct TableData {
      pub name: String,
      pub sheet_name: String,
      pub headers: Vec<String>,
      pub rows: Vec<Vec<CellValue>>,
  }
  
  impl TableData {
      pub fn from_calamine_table(table: Table<Data>) -> Self {
          let name = table.name().to_string();
          let sheet_name = table.sheet_name().to_string();
          let headers = table.columns().to_vec();
  
          let rows: Vec<Vec<CellValue>> = table
              .data()
              .rows()
              .map(|row| row.iter().map(SheetData::datatype_to_cellvalue).collect())
              .collect();
  
          Self {
              name,
              sheet_name,
              headers,
              rows,
          }
      }
  }
  
  impl std::fmt::Display for CellValue {
      fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
          match self {
              CellValue::Empty => write!(f, ""),
              CellValue::String(s) => write!(f, "{s}"),
              CellValue::Int(i) => {
                  // Format integers with thousand separators
                  let s = i.to_string();
                  let negative = s.starts_with('-');
                  let digits: String = s.trim_start_matches('-').chars().collect();
                  let mut result = String::new();
                  for (idx, ch) in digits.chars().rev().enumerate() {
                      if idx > 0 && idx % 3 == 0 {
                          result.push(',');
                      }
                      result.push(ch);
                  }
                  if negative {
                      result.push('-');
                  }
                  write!(f, "{}", result.chars().rev().collect::<String>())
              }
              CellValue::Float(val) => {
                  // Format floats with thousand separators
                  let formatted = if val.fract() == 0.0 {
                      format!("{val:.0}")
                  } else {
                      format!("{val:.2}")
                  };
                  let parts: Vec<&str> = formatted.split('.').collect();
                  let int_part = parts[0];
                  let negative = int_part.starts_with('-');
                  let digits: String = int_part.trim_start_matches('-').chars().collect();
                  let mut result = String::new();
                  for (idx, ch) in digits.chars().rev().enumerate() {
                      if idx > 0 && idx % 3 == 0 {
                          result.push(',');
                      }
                      result.push(ch);
                  }
                  if negative {
                      result.push('-');
                  }
                  let int_formatted: String = result.chars().rev().collect();
                  if parts.len() > 1 {
                      write!(f, "{}.{}", int_formatted, parts[1])
                  } else {
                      write!(f, "{}", int_formatted)
                  }
              }
              CellValue::Bool(b) => {
                  // Use lowercase for booleans
                  write!(f, "{}", if *b { "true" } else { "false" })
              }
              CellValue::Error(e) => write!(f, "ERROR: {e}"),
              CellValue::DateTime(d) => {
                  // Excel dates are days since December 31, 1899 (serial 0)
                  // Excel has a leap year bug where 1900 is incorrectly treated as a leap year
                  // Days > 60 need adjustment for this bug
                  let days = d.floor() as i64;
  
                  // Excel epoch: December 31, 1899 (Excel serial 0)
                  let excel_epoch = NaiveDate::from_ymd_opt(1899, 12, 31).unwrap();
  
                  // Adjust for Excel's 1900 leap year bug (day 60 = Feb 29, 1900 which didn't exist)
                  let adjusted_days = if days > 60 { days - 1 } else { days };
  
                  if let Some(date) = excel_epoch.checked_add_signed(Duration::days(adjusted_days)) {
                      // Check if there's a time component
                      let frac = d.fract();
                      if frac.abs() > 0.000001 {
                          // Has time component
                          let total_seconds = (frac * 86400.0).round() as u32;
                          let hours = total_seconds / 3600;
                          let minutes = (total_seconds % 3600) / 60;
                          let seconds = total_seconds % 60;
                          write!(f, "{} {:02}:{:02}:{:02}", date, hours, minutes, seconds)
                      } else {
                          // Date only
                          write!(f, "{}", date)
                      }
                  } else {
                      write!(f, "Date[{days}]")
                  }
              }
          }
      }
  }
  
  impl SheetData {
      pub fn from_range_with_formulas(
          range: Range<Data>,
          formula_range: Option<Range<String>>,
      ) -> Self {
          let (height, width) = range.get_size();
  
          // Extract headers from first row if it exists
          let headers = if height > 0 {
              range
                  .rows()
                  .next()
                  .map(|row| row.iter().map(Self::cell_to_string).collect())
                  .unwrap_or_default()
          } else {
              vec![]
          };
  
          // Extract data rows (skip first row as headers)
          let rows: Vec<Vec<CellValue>> = range
              .rows()
              .skip(1)
              .map(|row| row.iter().map(Self::datatype_to_cellvalue).collect())
              .collect();
  
          // Extract formulas if available
          // Note: Formula range may be sparse (only cells with formulas) and may have different start position
          let formulas: Vec<Vec<Option<String>>> = if let Some(formula_range) = formula_range {
              let formula_start = formula_range.start().unwrap_or((0, 0));
  
              // Create empty formula structure matching data dimensions
              let mut formula_grid: Vec<Vec<Option<String>>> = vec![vec![None; width]; height];
  
              // Populate formulas at their absolute positions
              for (row_offset, formula_row) in formula_range.rows().enumerate() {
                  let absolute_row = formula_start.0 as usize + row_offset;
                  if absolute_row > 0 && absolute_row <= height {
                      // Skip header row (row 0)
                      let data_row_idx = absolute_row - 1; // Convert to 0-based data row index
                      for (col_offset, formula_str) in formula_row.iter().enumerate() {
                          let absolute_col = formula_start.1 as usize + col_offset;
                          if absolute_col < width && !formula_str.is_empty() {
                              formula_grid[data_row_idx][absolute_col] = Some(formula_str.clone());
                          }
                      }
                  }
              }
  
              // Return formula grid matching data rows
              // We already handled header row when populating, so just take the data rows
              formula_grid
                  .into_iter()
                  .take(height.saturating_sub(1))
                  .collect()
          } else {
              // No formulas available, create empty parallel structure
              vec![vec![None; width]; height.saturating_sub(1)]
          };
  
          Self {
              headers,
              rows,
              formulas,
              width,
              height: height.saturating_sub(1), // Don't count header row
          }
      }
  
      fn cell_to_string(cell: &Data) -> String {
          match cell {
              Data::Empty => String::new(),
              Data::String(s) => s.clone(),
              Data::Int(i) => i.to_string(),
              Data::Float(f) => {
                  if f.fract() == 0.0 {
                      format!("{f:.0}")
                  } else {
                      f.to_string()
                  }
              }
              Data::Bool(b) => b.to_string(),
              Data::Error(e) => format!("ERROR: {e:?}"),
              Data::DateTime(d) => format!("Date({})", d.as_f64()),
              Data::DateTimeIso(s) => s.clone(),
              Data::DurationIso(s) => s.clone(),
          }
      }
  
      fn datatype_to_cellvalue(cell: &Data) -> CellValue {
          match cell {
              Data::Empty => CellValue::Empty,
              Data::String(s) => CellValue::String(s.clone()),
              Data::Int(i) => CellValue::Int(*i),
              Data::Float(f) => CellValue::Float(*f),
              Data::Bool(b) => CellValue::Bool(*b),
              Data::Error(e) => CellValue::Error(format!("{e:?}")),
              Data::DateTime(d) => CellValue::DateTime(d.as_f64()),
              Data::DateTimeIso(s) => CellValue::String(s.clone()),
              Data::DurationIso(s) => CellValue::String(s.clone()),
          }
      }
  }
  
  #[cfg(test)]
  mod tests {
      use super::*;
  
      #[test]
      fn test_cellvalue_display_integer() {
          let val = CellValue::Int(1234567);
          assert_eq!(val.to_string(), "1,234,567");
      }
  
      #[test]
      fn test_cellvalue_display_negative_integer() {
          let val = CellValue::Int(-1234567);
          assert_eq!(val.to_string(), "-1,234,567");
      }
  
      #[test]
      fn test_cellvalue_display_float() {
          let val = CellValue::Float(1234567.89);
          assert_eq!(val.to_string(), "1,234,567.89");
      }
  
      #[test]
      fn test_cellvalue_display_float_whole_number() {
          let val = CellValue::Float(1000.0);
          assert_eq!(val.to_string(), "1,000");
      }
  
      #[test]
      fn test_cellvalue_display_boolean() {
          assert_eq!(CellValue::Bool(true).to_string(), "true");
          assert_eq!(CellValue::Bool(false).to_string(), "false");
      }
  
      #[test]
      fn test_cellvalue_display_string() {
          let val = CellValue::String("Hello, World!".to_string());
          assert_eq!(val.to_string(), "Hello, World!");
      }
  
      #[test]
      fn test_cellvalue_display_empty() {
          let val = CellValue::Empty;
          assert_eq!(val.to_string(), "");
      }
  
      #[test]
      fn test_cellvalue_display_error() {
          let val = CellValue::Error("DIV/0!".to_string());
          assert_eq!(val.to_string(), "ERROR: DIV/0!");
      }
  
      #[test]
      fn test_cellvalue_to_raw_string_integer() {
          let val = CellValue::Int(1234567);
          assert_eq!(val.to_raw_string(), "1234567");
      }
  
      #[test]
      fn test_cellvalue_to_raw_string_float() {
          let val = CellValue::Float(123.45);
          assert_eq!(val.to_raw_string(), "123.45");
      }
  
      #[test]
      fn test_cellvalue_is_empty() {
          assert!(CellValue::Empty.is_empty());
          assert!(!CellValue::Int(0).is_empty());
          assert!(!CellValue::String("".to_string()).is_empty());
      }
  
      #[test]
      fn test_cellvalue_is_numeric() {
          assert!(CellValue::Int(123).is_numeric());
          assert!(CellValue::Float(123.45).is_numeric());
          assert!(!CellValue::String("123".to_string()).is_numeric());
          assert!(!CellValue::Empty.is_numeric());
      }
  
      #[test]
      fn test_datetime_display() {
          // Excel date: January 1, 1900 is day 1
          let val = CellValue::DateTime(1.0);
          let display = val.to_string();
          // Should contain a date in YYYY-MM-DD format
          assert!(display.contains("1900") || display.contains("1899"));
      }
  
      #[test]
      fn test_datetime_with_time() {
          // Excel datetime with time component
          // Day 1 + 0.5 = 12:00:00 on Jan 1, 1900
          let val = CellValue::DateTime(1.5);
          let display = val.to_string();
          // Should contain both date and time
          assert!(display.contains(":"));
          assert!(display.len() > 10); // Date + time is longer than just date
      }
  
      #[test]
      fn test_workbook_open_real_file() {
          // Test with actual test file if it exists
          if let Ok(wb) = Workbook::open("tests/fixtures/test_data.xlsx") {
              let sheet_names = wb.sheet_names();
              assert!(!sheet_names.is_empty(), "Should have at least one sheet");
          }
          // If file doesn't exist, test passes (integration test needs real file)
      }
  
      #[test]
      fn test_sheet_data_structure() {
          // Test SheetData structure can be created
          let sheet = SheetData {
              headers: vec!["Name".to_string(), "Age".to_string()],
              rows: vec![
                  vec![CellValue::String("Alice".to_string()), CellValue::Int(30)],
                  vec![CellValue::String("Bob".to_string()), CellValue::Int(25)],
              ],
              formulas: vec![vec![None, None], vec![None, None]],
              width: 2,
              height: 2,
          };
  
          assert_eq!(sheet.width, 2);
          assert_eq!(sheet.height, 2);
          assert_eq!(sheet.headers.len(), 2);
          assert_eq!(sheet.rows.len(), 2);
      }
  }
  