use std::collections::BTreeMap;
use std::fmt::Display;
use std::fs::File;
use std::io::BufReader;
use std::path::Path;
use calamine::{Data, ExcelDateTime, Reader, Sheet, SheetType, SheetVisible, Sheets};
use crate::{Error, Options, Result, TableLayout, Value};
#[cfg(feature = "excel")]
pub(crate) fn parse_excel(
path: &Path,
sheet_name: Option<&str>,
layout: &TableLayout,
options: &Options,
) -> Result<Value> {
let ext = path
.extension()
.and_then(|e| e.to_str())
.map(str::to_lowercase);
let workbook = match ext.as_deref() {
Some("xls") => Sheets::Xls(calamine::open_workbook(path).map_err(open_err(path))?),
Some("xlsb") => Sheets::Xlsb(calamine::open_workbook(path).map_err(open_err(path))?),
_ => Sheets::Xlsx(calamine::open_workbook(path).map_err(open_err(path))?),
};
parse_workbook(workbook, path, sheet_name, layout, options)
}
#[cfg(feature = "ods")]
pub(crate) fn parse_ods(
path: &Path,
sheet_name: Option<&str>,
layout: &TableLayout,
options: &Options,
) -> Result<Value> {
let workbook = Sheets::Ods(calamine::open_workbook(path).map_err(open_err(path))?);
parse_workbook(workbook, path, sheet_name, layout, options)
}
fn open_err<E: Display>(path: &Path) -> impl Fn(E) -> Error + '_ {
move |e| Error::Parse {
path: path.to_path_buf(),
message: e.to_string(),
}
}
fn parse_workbook(
mut workbook: Sheets<BufReader<File>>,
path: &Path,
sheet_name: Option<&str>,
layout: &TableLayout,
options: &Options,
) -> Result<Value> {
let sheets: Vec<Sheet> = workbook.sheets_metadata().to_vec();
if let Some(name) = sheet_name {
if !sheets.iter().any(|sheet| sheet.name == name) {
return Err(Error::Parse {
path: path.to_path_buf(),
message: format!("sheet '{name}' not found"),
});
}
let value = parse_sheet(&mut workbook, name, layout, path, options)?;
return Ok(Value::Object(BTreeMap::from([(name.to_owned(), value)])));
}
let mut kept = sheets.into_iter().filter(|sheet| keep(sheet, options));
if options.tree {
let mut root = BTreeMap::new();
for sheet in kept {
let value = parse_sheet(&mut workbook, &sheet.name, layout, path, options)?;
root.insert(sheet.name, value);
}
if root.is_empty() {
return Ok(Value::Null);
}
Ok(Value::Object(root))
} else {
match kept.find(|sheet| sheet.name == "config") {
Some(sheet) => parse_sheet(&mut workbook, &sheet.name, layout, path, options),
None => Ok(Value::Null),
}
}
}
fn keep(sheet: &Sheet, options: &Options) -> bool {
if sheet.typ != SheetType::WorkSheet {
return false; }
if options.ignore_hidden_sheets && sheet.visible != SheetVisible::Visible {
return false;
}
if options.ignore_sheet_prefix && sheet.name.starts_with(['#', '.', '_']) {
return false;
}
true
}
fn parse_sheet(
workbook: &mut Sheets<BufReader<File>>,
name: &str,
layout: &TableLayout,
path: &Path,
options: &Options,
) -> Result<Value> {
let range = workbook.worksheet_range(name).map_err(open_err(path))?;
let mut rows: Vec<Vec<String>> = Vec::new();
if let Some((start_row, start_col)) = range.start() {
rows.resize(start_row as usize, Vec::new());
for cells in range.rows() {
let mut row = vec![String::new(); start_col as usize];
for cell in cells {
row.push(cell_text(cell).map_err(|message| Error::Parse {
path: path.to_path_buf(),
message: format!("sheet '{name}': {message}"),
})?);
}
rows.push(row);
}
}
super::table::parse(rows, layout, path, options)
}
fn cell_text(cell: &Data) -> std::result::Result<String, String> {
Ok(match cell {
Data::Empty => String::new(),
Data::String(s) => s.clone(),
Data::Int(i) => i.to_string(),
Data::Float(f) => f.to_string(),
Data::Bool(b) => b.to_string(),
Data::DateTime(dt) => serial_text(dt),
Data::DateTimeIso(s) | Data::DurationIso(s) => s.clone(),
Data::Error(e) => return Err(format!("cell error {e}")),
})
}
fn serial_text(dt: &ExcelDateTime) -> String {
if dt.is_duration() {
let ms = (dt.as_f64() * 86_400_000.0).round() as u64;
return time_text(ms / 3_600_000, ms / 60_000 % 60, ms / 1000 % 60, ms % 1000);
}
let (year, month, day, hour, min, sec, milli) = dt.to_ymd_hms_milli();
if dt.as_f64() < 1.0 {
return time_text(hour.into(), min.into(), sec.into(), milli.into());
}
if (hour, min, sec, milli) == (0, 0, 0, 0) {
return format!("{year:04}-{month:02}-{day:02}");
}
format!(
"{year:04}-{month:02}-{day:02} {}",
time_text(hour.into(), min.into(), sec.into(), milli.into())
)
}
fn time_text(hour: u64, min: u64, sec: u64, milli: u64) -> String {
if milli == 0 {
format!("{hour:02}:{min:02}:{sec:02}")
} else {
format!("{hour:02}:{min:02}:{sec:02}.{milli:03}")
}
}
#[cfg(test)]
mod tests {
use calamine::ExcelDateTimeType;
use super::*;
fn serial(value: f64, ty: ExcelDateTimeType) -> String {
serial_text(&ExcelDateTime::new(value, ty, false))
}
#[test]
fn serial_datetime_forms() {
assert_eq!(serial(45418.0, ExcelDateTimeType::DateTime), "2024-05-06");
assert_eq!(
serial(45418.297326388885, ExcelDateTimeType::DateTime),
"2024-05-06 07:08:09"
);
assert_eq!(serial(0.5, ExcelDateTimeType::DateTime), "12:00:00");
assert_eq!(serial(0.2503, ExcelDateTimeType::DateTime), "06:00:25.920");
assert_eq!(serial(1.25, ExcelDateTimeType::TimeDelta), "30:00:00");
assert_eq!(serial(0.5, ExcelDateTimeType::TimeDelta), "12:00:00");
}
#[test]
fn cell_text_forms() {
assert_eq!(cell_text(&Data::Empty).unwrap(), "");
assert_eq!(cell_text(&Data::String("a,b".into())).unwrap(), "a,b");
assert_eq!(cell_text(&Data::Int(-3)).unwrap(), "-3");
assert_eq!(cell_text(&Data::Float(8080.0)).unwrap(), "8080");
assert_eq!(cell_text(&Data::Float(1.5)).unwrap(), "1.5");
assert_eq!(cell_text(&Data::Bool(true)).unwrap(), "true");
assert_eq!(
cell_text(&Data::DateTimeIso("2024-05-06T07:08:09".into())).unwrap(),
"2024-05-06T07:08:09"
);
assert!(cell_text(&Data::Error(calamine::CellErrorType::Div0)).is_err());
}
}