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
180/// Concatenates many files into a single XLSX sheet.
181///
182/// Every sheet from every input is flattened into one table. A `source`
183/// column is prepended to each row, containing `<stem>` for single-table
184/// inputs and `<stem>_<sheet>` for multi-sheet inputs. Columns are unioned
185/// across all sources in first-appearance order; missing values become
186/// empty cells.
187///
188/// # Errors
189///
190/// Returns an error if any input cannot be read or decoded, if `output`
191/// does not end in `.xlsx`, or if writing fails.
192pub fn concat(
193    inputs: &[String],
194    output: &str,
195    no_source: bool,
196    sheet_name: &str,
197) -> Result<(), Box<dyn Error>> {
198    if !output.ends_with(".xlsx") {
199        return Err(format!("output must end in .xlsx, got {output:?}").into());
200    }
201
202    let source_col = "source";
203    let mut all_headers: Vec<String> = if no_source {
204        Vec::new()
205    } else {
206        vec![source_col.to_string()]
207    };
208    let mut collected: Vec<(String, Vec<(String, CanonicalValue)>)> = Vec::new();
209
210    for input_path in inputs {
211        for (label, value) in load_for_merge(input_path)? {
212            if let CanonicalValue::Array(rows) = value {
213                for row in rows {
214                    if let CanonicalValue::Object(obj) = row {
215                        for (k, _) in &obj {
216                            if !all_headers.contains(k) {
217                                all_headers.push(k.clone());
218                            }
219                        }
220                        collected.push((label.clone(), obj));
221                    }
222                }
223            }
224        }
225    }
226
227    let mut rows_out: Vec<CanonicalValue> = Vec::with_capacity(collected.len());
228    for (label, obj) in collected {
229        let mut new_row: Vec<(String, CanonicalValue)> = Vec::with_capacity(all_headers.len());
230        for h in &all_headers {
231            if h == source_col && !no_source {
232                new_row.push((h.clone(), CanonicalValue::String(label.clone())));
233            } else {
234                let v = obj
235                    .iter()
236                    .find(|(k, _)| k == h)
237                    .map(|(_, v)| v.clone())
238                    .unwrap_or(CanonicalValue::Null);
239                new_row.push((h.clone(), v));
240            }
241        }
242        rows_out.push(CanonicalValue::Object(new_row));
243    }
244
245    let sheet = CanonicalValue::Array(rows_out);
246    let bytes = CanonicalValue::to_xlsx_workbook(&[(sheet_name.to_string(), sheet)])?;
247    std::fs::write(output, bytes)?;
248    Ok(())
249}
250
251fn unique_excel_name(raw: &str, used: &mut HashSet<String>) -> String {
252    let base = truncate(&excel_safe(raw), 31);
253    if used.insert(base.clone()) {
254        return base;
255    }
256    let mut counter = 2;
257    loop {
258        let suffix = format!("_{counter}");
259        let candidate = format!("{}{}", truncate(&base, 31 - suffix.len()), suffix);
260        if used.insert(candidate.clone()) {
261            return candidate;
262        }
263        counter += 1;
264    }
265}
266
267fn unique_file_name(raw: &str, used: &mut HashSet<String>) -> String {
268    let base = sanitize_sheet_name(raw);
269    if used.insert(base.clone()) {
270        return base;
271    }
272    let mut counter = 2;
273    loop {
274        let candidate = format!("{}_{}", sanitize_sheet_name(raw), counter);
275        if used.insert(candidate.clone()) {
276            return candidate;
277        }
278        counter += 1;
279    }
280}
281
282fn excel_safe(name: &str) -> String {
283    let cleaned: String = name
284        .chars()
285        .map(|c| match c {
286            '[' | ']' | ':' | '*' | '?' | '/' | '\\' => '_',
287            c if c.is_control() => '_',
288            c => c,
289        })
290        .collect();
291    let trimmed = cleaned.trim();
292    if trimmed.is_empty() {
293        "Sheet".to_string()
294    } else {
295        trimmed.to_string()
296    }
297}
298
299fn truncate(s: &str, max: usize) -> String {
300    if s.chars().count() <= max {
301        s.to_string()
302    } else {
303        s.chars().take(max).collect()
304    }
305}
306
307fn sanitize_sheet_name(name: &str) -> String {
308    let normalized: String = name
309        .nfd()
310        .filter(|c| !unicode_normalization::char::is_combining_mark(*c))
311        .collect::<String>()
312        .to_lowercase();
313    let mut out = String::new();
314    let mut last_sep = false;
315    for c in normalized.chars() {
316        if c.is_ascii_alphanumeric() {
317            out.push(c);
318            last_sep = false;
319        } else if !last_sep && !out.is_empty() {
320            out.push('_');
321            last_sep = true;
322        }
323    }
324    while out.ends_with('_') {
325        out.pop();
326    }
327    if out.is_empty() {
328        "sheet".to_string()
329    } else {
330        out
331    }
332}
sheeter_engine/xlsx_tools.rs

sheeter_engine/
xlsx_tools.rs
