Excel files

use spreadsheet.qweruse sheet_names, read_sheet from spreadsheet      // Quillo 0.6: only these

calamine = { version = "0.31", features = ["dates"] }rust_xlsxwriter = "0.79"

// A sales report as an Excel download, and the file read back.////   quillo run spreadsheet.qwe      then open http://localhost:8080/report.xlsxuse "spreadsheet.qwer"let sales = [["Region", "Orders", "Revenue"], ["North", 412, 18250.5], ["South", 380, 16410], ["East", 129, 5120.25]]route GET /report.xlsx {    write_xlsx("report.xlsx", "Sales", sales)?    file("report.xlsx")}route GET /read {    write_xlsx("report.xlsx", "Sales", sales)?    { sheets: sheet_names("report.xlsx")?, rows: read_sheet("report.xlsx", "")? }}

//! Excel files: read the sheets of .xlsx, .xls and .ods files, and write .xlsx reports with a bold header.//! version: 1.0.0//!//! [dependencies]//! calamine = { version = "0.31", features = ["dates"] }//! rust_xlsxwriter = "0.79"use calamine::{open_workbook_auto, Data, DataType, Reader};use rust_xlsxwriter::{Format, Workbook};use serde_json::Value;/// The names of a workbook's sheets.pub fn sheet_names(path: &str) -> Result<Vec<String>, String> {    Ok(open_workbook_auto(path).map_err(|e| format!("{}: {}", path, e))?.sheet_names())}/// The rows of a sheet (the first one when `sheet` is empty), as lists of cells: numbers (whole ones as ints), text, booleans, dates as text, empty cells as null.pub fn read_sheet(path: &str, sheet: &str) -> Result<Vec<Vec<Value>>, String> {    let mut wb = open_workbook_auto(path).map_err(|e| format!("{}: {}", path, e))?;    let name = if sheet.is_empty() { wb.sheet_names().first().cloned().ok_or("the workbook has no sheets")? } else { sheet.to_string() };    let range = wb.worksheet_range(&name).map_err(|e| format!("sheet `{}`: {}", name, e))?;    Ok(range.rows().map(|row| row.iter().map(|c| match c {        Data::Int(i) => Value::from(*i),        // Excel keeps every number as a float: whole ones come back as ints.        Data::Float(f) if f.fract() == 0.0 && f.abs() < 9.0e15 => Value::from(*f as i64),        Data::Float(f) => Value::from(*f),        Data::String(s) => Value::String(s.clone()),        Data::Bool(b) => Value::Bool(*b),        Data::DateTime(_) | Data::DateTimeIso(_) => c.as_datetime().map(|d| Value::String(d.to_string())).unwrap_or(Value::Null),        Data::DurationIso(s) => Value::String(s.clone()),        Data::Error(e) => Value::String(format!("#{:?}", e)),        Data::Empty => Value::Null,    }).collect()).collect())}/// Writes `rows` (lists of cells) to a new .xlsx file at `path`, in a sheet named `sheet`, its first row bold and the columns sized to fit.pub fn write_xlsx(path: &str, sheet: &str, rows: Vec<Vec<serde_json::Value>>) -> Result<(), String> {    let mut wb = Workbook::new();    let ws = wb.add_worksheet();    ws.set_name(if sheet.is_empty() { "Sheet1" } else { sheet }).map_err(|e| e.to_string())?;    let bold = Format::new().set_bold();    for (r, row) in rows.iter().enumerate() {        for (c, v) in row.iter().enumerate() {            let (r, c) = (r as u32, c as u16);            let res = match (v, r == 0) {                (Value::Null, _) => Ok(ws as &mut _),                (Value::Number(n), false) => ws.write_number(r, c, n.as_f64().unwrap_or(0.0)),                (Value::Bool(b), false) => ws.write_boolean(r, c, *b),                (Value::String(s), false) => ws.write_string(r, c, s),                (v, false) => ws.write_string(r, c, v.to_string()),                (Value::String(s), true) => ws.write_string_with_format(r, c, s, &bold),                (v, true) => ws.write_string_with_format(r, c, v.to_string(), &bold),            };            res.map_err(|e| e.to_string())?;        }    }    ws.autofit();    wb.save(path).map_err(|e| e.to_string())}