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 file = open(path)?;
let workbook = match ext.as_deref() {
Some("xls") => Sheets::Xls(calamine::open_workbook_from_rs(file).map_err(open_err(path))?),
Some("xlsb") => {
Sheets::Xlsb(calamine::open_workbook_from_rs(file).map_err(open_err(path))?)
}
_ => Sheets::Xlsx(calamine::open_workbook_from_rs(file).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_from_rs(open(path)?).map_err(open_err(path))?);
parse_workbook(workbook, path, sheet_name, layout, options)
}
fn open(path: &Path) -> Result<BufReader<File>> {
File::open(path)
.map(BufReader::new)
.map_err(crate::error::io(path))
}
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 sheet_key(name, value, path, options);
}
let mut names: Vec<String> = sheets
.into_iter()
.filter(|sheet| keep(sheet, options))
.map(|sheet| sheet.name)
.collect();
if options.sheetname_as_key {
if names.is_empty() {
return Ok(Value::Null);
}
let mut root = Value::Object(BTreeMap::new());
for name in names {
let value = parse_sheet(&mut workbook, &name, layout, path, options)?;
super::insert_key(&mut root, &name, value, options.dot_key)
.map_err(|message| sheet_name_err(path, &name, message))?;
}
Ok(root)
} else {
sort_sheet_names(&mut names, options.order);
let mut merged = Value::Object(BTreeMap::new());
let mut any = false;
for name in names {
let value = parse_sheet(&mut workbook, &name, layout, path, options)?;
if matches!(value, Value::Null) {
continue; }
any = true;
deep_merge(&mut merged, value, !options.case_sensitive);
}
Ok(if any { merged } else { Value::Null })
}
}
fn sheet_key(name: &str, value: Value, path: &Path, options: &Options) -> Result<Value> {
let mut root = Value::Object(BTreeMap::new());
super::insert_key(&mut root, name, value, options.dot_key)
.map_err(|message| sheet_name_err(path, name, message))?;
Ok(root)
}
fn sheet_name_err(path: &Path, name: &str, message: String) -> Error {
Error::Parse {
path: path.to_path_buf(),
message: format!("sheet '{name}': {message}"),
}
}
fn deep_merge(target: &mut Value, incoming: Value, lowercase: bool) {
match (target, incoming) {
(Value::Object(entries), Value::Object(inc)) => {
for (key, value) in inc {
let key = if lowercase { key.to_lowercase() } else { key };
match entries.get_mut(&key) {
Some(slot) => deep_merge(slot, value, lowercase),
None => {
entries.insert(key, fold_keys(value, lowercase));
}
}
}
}
(slot, value) => *slot = fold_keys(value, lowercase),
}
}
fn fold_keys(value: Value, lowercase: bool) -> Value {
match value {
Value::Object(map) if lowercase => Value::Object(
map.into_iter()
.map(|(k, v)| (k.to_lowercase(), fold_keys(v, lowercase)))
.collect(),
),
other => other,
}
}
fn sort_sheet_names(names: &mut [String], order: crate::Order) {
match order {
crate::Order::ReverseAlphabetic => names.sort_by(|a, b| b.cmp(a)),
_ => names.sort(),
}
}
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_commented_sheetnames && crate::options::is_commented(&sheet.name) {
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);
}
}
let mut value = super::table::parse(rows, layout, path, options)?;
super::strip_commented_data_keys(&mut value, options);
Ok(value)
}
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());
}
}