sheeter_engine/
xlsx_tools.rs

1//! Excel-specific helpers: `xlsx:merge` and `xlsx:frag`.
2//!
3//! [`merge`] appends one sheet per input file to an existing or new `.xlsx`.
4//! Inputs may be any format the engine reads; each is decoded through the
5//! canonical hub before being written. Single-table inputs use their filename
6//! stem as the sheet name; multi-sheet inputs (XLSX, ODS, multi-table SQL)
7//! prefix the sheet name with the file stem to avoid collisions. Names are
8//! truncated to Excel's 31-character limit, and collisions after truncation
9//! get `_2`, `_3` suffixes. Existing sheets are preserved.
10//!
11//! [`frag`] splits an `.xlsx` into one JSON file per sheet, written to the
12//! current directory (or `--out DIR`). Sheet names are normalized the same
13//! way the merge mode formats its prefixes: NFD-decomposed, accents stripped,
14//! lowercased, non-alphanumerics collapsed to single underscores, trimmed.
15//! Empty results fall back to `sheet`; collisions get `_2`, `_3`.
16//!
17//! # Examples
18//!
19//! ```
20//! use sheeter_engine::xlsx_tools;
21//! use std::fs;
22//!
23//! let dir = tempfile::tempdir().unwrap();
24//! fs::write(dir.path().join("a.csv"), "x\n1\n").unwrap();
25//! fs::write(dir.path().join("b.json"), r#"[{"y":2}]"#).unwrap();
26//! let out = dir.path().join("ss.xlsx");
27//! xlsx_tools::merge(
28//!     &[
29//!         dir.path().join("a.csv").to_str().unwrap().to_string(),
30//!         dir.path().join("b.json").to_str().unwrap().to_string(),
31//!     ],
32//!     out.to_str().unwrap(),
33//! )
34//! .unwrap();
35//! let sheets = sheeter_engine::CanonicalValue::from_xlsx_workbook(&fs::read(out).unwrap()).unwrap();
36//! assert_eq!(sheets.len(), 2);
37//! assert_eq!(sheets[0].0, "a");
38//! assert_eq!(sheets[1].0, "b");
39//! ```
40
41use crate::{detect_content, detect_extension, CanonicalValue, Format, SqlDialect};
42use std::collections::HashSet;
43use std::error::Error;
44use std::path::{Path, PathBuf};
45use unicode_normalization::UnicodeNormalization;
46
47/// Appends one sheet per input file to `output`.
48///
49/// If `output` already exists, its sheets are loaded and preserved; new
50/// sheets are appended. Sheet names come from the input filename stem for
51/// single-table inputs, and from `<stem>_<sheet>` for multi-sheet inputs.
52///
53/// # Errors
54///
55/// Returns an error if any input cannot be read or decoded, if `output` does
56/// not end in `.xlsx`, or if writing the workbook fails.
57pub fn merge(inputs: &[String], output: &str) -> Result<(), Box<dyn Error>> {
58    if !output.ends_with(".xlsx") {
59        return Err(format!("output must end in .xlsx, got {output:?}").into());
60    }
61
62    let mut sheets: Vec<(String, CanonicalValue)> = if Path::new(output).exists() {
63        CanonicalValue::from_xlsx_workbook(&std::fs::read(output)?)?
64    } else {
65        Vec::new()
66    };
67    let mut used: HashSet<String> = sheets.iter().map(|(n, _)| n.clone()).collect();
68
69    for input_path in inputs {
70        for (raw_name, value) in load_for_merge(input_path)? {
71            let name = unique_excel_name(&raw_name, &mut used);
72            sheets.push((name, value));
73        }
74    }
75
76    let bytes = CanonicalValue::to_xlsx_workbook(&sheets)?;
77    std::fs::write(output, bytes)?;
78    Ok(())
79}
80
81fn load_for_merge(path: &str) -> Result<Vec<(String, CanonicalValue)>, Box<dyn Error>> {
82    let bytes = std::fs::read(path)?;
83    let fmt = match detect_extension(Path::new(path)) {
84        Some(f) => f,
85        None => detect_content(&bytes)?,
86    };
87    let stem = Path::new(path)
88        .file_stem()
89        .and_then(|s| s.to_str())
90        .unwrap_or(path)
91        .to_string();
92
93    match fmt {
94        Format::Xlsx => {
95            let sheets = CanonicalValue::from_xlsx_workbook(&bytes)?;
96            if sheets.len() == 1 {
97                let mut it = sheets.into_iter();
98                Ok(vec![(stem, it.next().unwrap().1)])
99            } else {
100                Ok(sheets
101                    .into_iter()
102                    .map(|(name, v)| (format!("{stem}_{name}"), v))
103                    .collect())
104            }
105        }
106        Format::Ods => {
107            let v = CanonicalValue::from_ods(&bytes)?;
108            match v {
109                CanonicalValue::Object(o) if o.len() > 1 => Ok(o
110                    .into_iter()
111                    .map(|(name, v)| (format!("{stem}_{name}"), v))
112                    .collect()),
113                other => Ok(vec![(stem, other)]),
114            }
115        }
116        other => {
117            let text = std::str::from_utf8(&bytes)?;
118            let hub = match other {
119                Format::Csv => CanonicalValue::from_csv(text)?,
120                Format::Tsv => CanonicalValue::from_tsv(text)?,
121                Format::Json => CanonicalValue::from_json(text)?,
122                Format::Ndjson => CanonicalValue::from_ndjson(text)?,
123                Format::Yaml => CanonicalValue::from_yaml(text)?,
124                Format::Xml => CanonicalValue::from_xml(text)?,
125                Format::Toml => CanonicalValue::from_toml(text)?,
126                Format::Sql => {
127                    let sheets = CanonicalValue::from_sql_workbook(text, SqlDialect::Generic)?;
128                    if sheets.len() == 1 {
129                        return Ok(vec![(stem, sheets.into_iter().next().unwrap().1)]);
130                    }
131                    return Ok(sheets
132                        .into_iter()
133                        .map(|(name, v)| (format!("{stem}_{name}"), v))
134                        .collect());
135                }
136                Format::Xlsx | Format::Ods => unreachable!(),
137            };
138            Ok(vec![(stem, hub)])
139        }
140    }
141}
142
143/// Splits every sheet of an `.xlsx` into its own JSON file.
144///
145/// # Errors
146///
147/// Returns an error if the input is not `.xlsx`, cannot be read, or if any
148/// write fails.
149pub fn frag(input: &str, out_dir: Option<&str>) -> Result<Vec<PathBuf>, Box<dyn Error>> {
150    let bytes = std::fs::read(input)?;
151    let fmt = match detect_extension(Path::new(input)) {
152        Some(f) => f,
153        None => detect_content(&bytes)?,
154    };
155    if !matches!(fmt, Format::Xlsx) {
156        return Err(format!(
157            "xlsx:frag requires an .xlsx file, got {fmt:?}; convert to .xlsx first"
158        )
159        .into());
160    }
161
162    let sheets = CanonicalValue::from_xlsx_workbook(&bytes)?;
163    let out = match out_dir {
164        Some(d) => Path::new(d),
165        None => Path::new("."),
166    };
167    std::fs::create_dir_all(out)?;
168
169    let mut written: Vec<PathBuf> = Vec::new();
170    let mut used: HashSet<String> = HashSet::new();
171    for (name, value) in sheets {
172        let base = unique_file_name(&name, &mut used);
173        let path = out.join(format!("{base}.json"));
174        std::fs::write(&path, value.to_json()?)?;
175        written.push(path);
176    }
177    Ok(written)
178}
179
180fn unique_excel_name(raw: &str, used: &mut HashSet<String>) -> String {
181    let base = truncate(&excel_safe(raw), 31);
182    if used.insert(base.clone()) {
183        return base;
184    }
185    let mut counter = 2;
186    loop {
187        let suffix = format!("_{counter}");
188        let candidate = format!("{}{}", truncate(&base, 31 - suffix.len()), suffix);
189        if used.insert(candidate.clone()) {
190            return candidate;
191        }
192        counter += 1;
193    }
194}
195
196fn unique_file_name(raw: &str, used: &mut HashSet<String>) -> String {
197    let base = sanitize_sheet_name(raw);
198    if used.insert(base.clone()) {
199        return base;
200    }
201    let mut counter = 2;
202    loop {
203        let candidate = format!("{}_{}", sanitize_sheet_name(raw), counter);
204        if used.insert(candidate.clone()) {
205            return candidate;
206        }
207        counter += 1;
208    }
209}
210
211fn excel_safe(name: &str) -> String {
212    let cleaned: String = name
213        .chars()
214        .map(|c| match c {
215            '[' | ']' | ':' | '*' | '?' | '/' | '\\' => '_',
216            c if c.is_control() => '_',
217            c => c,
218        })
219        .collect();
220    let trimmed = cleaned.trim();
221    if trimmed.is_empty() {
222        "Sheet".to_string()
223    } else {
224        trimmed.to_string()
225    }
226}
227
228fn truncate(s: &str, max: usize) -> String {
229    if s.chars().count() <= max {
230        s.to_string()
231    } else {
232        s.chars().take(max).collect()
233    }
234}
235
236fn sanitize_sheet_name(name: &str) -> String {
237    let normalized: String = name
238        .nfd()
239        .filter(|c| !unicode_normalization::char::is_combining_mark(*c))
240        .collect::<String>()
241        .to_lowercase();
242    let mut out = String::new();
243    let mut last_sep = false;
244    for c in normalized.chars() {
245        if c.is_ascii_alphanumeric() {
246            out.push(c);
247            last_sep = false;
248        } else if !last_sep && !out.is_empty() {
249            out.push('_');
250            last_sep = true;
251        }
252    }
253    while out.ends_with('_') {
254        out.pop();
255    }
256    if out.is_empty() {
257        "sheet".to_string()
258    } else {
259        out
260    }
261}
sheeter_engine/xlsx_tools.rs

sheeter_engine/
xlsx_tools.rs
