spreadsheet_to_json/
data_set.rs

1use std::{fs::File, io::BufReader};
2use calamine::{Reader, Sheets};
3use heck::ToSnakeCase;
4use indexmap::IndexMap;
5use serde::Serialize;
6use serde_json::{json, Value};
7
8use crate::key_segment::insert_key_segment;
9use crate::{Column, OptionSet, PathData, ReadMode};
10
11
12/// Core info about a spreadsheet with extension, matched worksheet name and index an all worksheet keys
13#[derive(Debug, Clone)]
14pub struct WorkbookInfo {
15    pub filename: String,
16    pub extension: String,
17    pub selected: Option<Vec<String>>,
18    pub sheets: Vec<String>,
19}
20
21impl WorkbookInfo {
22    pub fn new(path_data: &PathData, selected: &[String], sheet_refs: &[String]) -> Self {
23        WorkbookInfo {
24            extension: path_data.extension(),
25            filename: path_data.filename(), 
26            selected: Some(selected.to_vec()),
27            sheets: sheet_refs.to_vec(),
28        }
29    }
30
31    pub fn simple(path_data: &PathData) -> Self {
32        let sheet_name = "single";
33        WorkbookInfo {
34            extension: path_data.extension(),
35            filename: path_data.filename(), 
36            selected: None,
37            sheets: vec![sheet_name.to_owned()],
38        }
39    }
40
41    pub fn ext(&self) -> String {
42        self.extension.to_owned()
43    }
44
45    pub fn name(&self) -> String {
46        self.filename.to_owned()
47    }
48
49    pub fn sheet(&self, index: usize) -> (String, usize) {
50      let sheet_name = self.sheets.get(index).unwrap_or(&"single".to_owned()).to_owned();
51      (sheet_name, index)
52    }
53
54    pub fn sheets(&self) -> Vec<String> {
55        self.sheets.clone()
56    }
57}
58
59
60// Result set
61#[derive(Debug, Clone)]
62pub struct ResultSet {
63    pub filename: String,
64    pub extension: String,
65    pub selected: Option<Vec<String>>,
66    pub sheets: Vec<String>,
67    pub keys: Vec<String>,
68    pub num_rows: usize,
69    pub data: SpreadData,
70    pub out_ref: Option<String>,
71    pub opts: OptionSet,
72    /// 0-based index of the header row actually used, resolved at read time -- whether
73    /// from an explicit `OptionSet.header_row` override or auto-detection. `None` when no
74    /// row was captured as a header at all (`--omit-header`, or when auto-detection found
75    /// no confident header in the sample) -- distinct from `opts.header_row`, which only
76    /// ever reflects an explicit override and stays `None` whenever detection ran instead.
77    pub header_row_index: Option<usize>,
78    /// 0-based index of the first data row actually used, resolved at read time -- whether
79    /// from an explicit `OptionSet.data_row_index` override or auto-detection. Always
80    /// concrete: a file always starts reading data *somewhere*, unlike the header row.
81    pub body_start_index: usize,
82}
83
84impl ResultSet {
85
86  /// Instantiate with Core workbook info, header keys, data set and optional output reference.
87  /// `header_row_index`/`body_start_index` are the *resolved* 0-based row indices actually
88  /// used for this read -- see the field docs on `ResultSet` for why these differ from
89  /// `opts.header_row`/`opts.data_row_index`.
90  pub fn new(info: &WorkbookInfo, keys: &[String], data_set: DataSet, opts: &OptionSet, out_ref: Option<&str>, header_row_index: Option<usize>, body_start_index: usize) -> Self {
91    let (num_rows, data) = match data_set {
92      DataSet::WithRows(size, rows) => (size, rows),
93      DataSet::Count(size) => (size, vec![])
94    };
95    ResultSet {
96      extension: info.ext(),
97      filename: info.name(),
98      selected: info.selected.clone(),
99      sheets: info.sheets(),
100      keys: keys.to_vec(),
101      num_rows,
102      data: SpreadData::from_single(data),
103      out_ref: out_ref.map(|s| s.to_string()),
104      opts: opts.to_owned(),
105      header_row_index,
106      body_start_index,
107    }
108  }
109
110  pub fn from_multiple(sheets: &[SheetDataSet], info: &WorkbookInfo, opts: &OptionSet) -> Self {
111    let selected = None;
112    let mut sheet_names = vec![];
113    let filename = info.filename.clone();
114    let extension = info.extension.clone();
115    let mut keys: Vec<String> = vec![];
116    let mut num_rows = 0;
117    for (sheet_index, sheet) in sheets.iter().enumerate() {
118      num_rows += sheet.num_rows;
119      sheet_names.push(sheet.name());
120      if sheet_index == 0 {
121        keys = sheet.keys.clone();
122      }
123    }
124    ResultSet {
125      extension,
126      filename,
127      selected,
128      sheets: sheet_names,
129      keys,
130      num_rows,
131      data: SpreadData::Multiple(sheets.to_vec()),
132      out_ref: None,
133      opts: opts.to_owned(),
134      // A single top-level header/body-start index doesn't represent multiple sheets,
135      // each of which may resolve to a different row -- unlike ResultSet::new (used for
136      // single-sheet reads), per-sheet resolved indices aren't tracked here yet.
137      header_row_index: None,
138      body_start_index: 0,
139    }
140  }
141
142
143  pub fn multimode(&self) -> bool {
144    matches!(self.data, SpreadData::Multiple(_))
145  }
146
147  /// Full result set as JSON with criteria, options and data in synchronous mode
148  pub fn to_json(&self) -> Value {
149    let mut result = json!({
150      "name": self.filename,
151      "extension": self.extension,
152      "selected": self.selected.clone().unwrap_or(vec![]),
153      "sheets": self.sheets,
154      "num_rows": self.num_rows,
155      "fields": self.keys,
156      "multimode": self.multimode(),
157      "data": self.data.to_json(),
158      "opts": self.opts.to_json()
159    });
160    if let Some(out_ref_str) = self.out_ref.clone() {
161      result["outref"] = json!(out_ref_str);
162    }
163    result
164  }
165
166   /// Full result set as CLI-friendly lines
167   pub fn to_output_lines(&self, json_lines: bool) -> Vec<String> {
168    let selected_names = self.selected.clone().unwrap_or(vec![]);
169    let num_selected = selected_names.len();
170    let plural = if num_selected > 1 {
171      "s"
172    } else {
173      ""
174    };
175    let mut lines = vec![
176      format!("name:{}", self.filename),
177      format!("extension: {}", self.extension),
178      
179      format!("sheets: {}", self.sheets.join(", ")),
180    ];
181    if num_selected > 0 {
182      lines.push(format!("selected sheet{}: {}", plural, selected_names.join(", ")));
183    }
184    lines.push(format!("row count: {}", self.num_rows));
185    lines.push(format!("fields: {}", self.keys.join(",")));
186    lines.push(format!("multimode: {}", self.multimode()));
187    for opt_line in self.opts.to_lines() {
188      lines.push(opt_line);
189    }
190    if let Some(out_ref_str) = self.out_ref.clone() {
191      lines.push(format!("output reference: {}", out_ref_str));
192    } else {
193      let has_many_sheets = self.sheets.len() > 1;
194      if !has_many_sheets || !self.multimode() {
195        lines.push("data:".to_owned());
196      }
197      if json_lines {
198        for sheet in &self.data.sheets() {
199          if has_many_sheets {
200            lines.push(format!("Sheet `{}` ({}):", sheet.name(), sheet.num_rows));
201          }
202          for item in &sheet.rows {
203            lines.push(format!("{}", json!(item)));
204          }
205        }
206      } else {
207        if self.multimode() {
208          for sheet in self.data.sheets() {
209            lines.push(format!("Sheet `{}` ({}):", sheet.name(), sheet.num_rows));
210            lines.push(format!("{}", json!(sheet)));
211          }
212        } else {
213          lines.push(format!("{}", self.data.to_json()));
214        }
215      }
216    }
217    lines
218  }
219
220  /// Extract the vector of rows as Index Maps of JSON values
221  /// Good for post-processing results
222  pub fn to_vec(&self) -> Vec<IndexMap<String, Value>> {
223    self.data.first_sheet().clone()
224  }
225  
226  /// JSON object of row arrays only
227  pub fn json_data(&self) -> Value {
228    json!(self.data)
229  }
230
231  /// final output as vector of JSON-serializable array
232  pub fn rows(&self) -> Vec<String> {
233    let sheet = self.data.first_sheet();
234    let mut lines = Vec::with_capacity(sheet.len());
235    for row in &sheet {
236      lines.push(json!(row).to_string());
237    }
238    lines
239  }
240
241}
242
243#[derive(Debug, Clone, Serialize)]
244pub struct SheetDataSet {
245  pub sheet: (String, String),
246  pub num_rows: usize,
247  pub keys: Vec<String>,
248  pub rows: Vec<IndexMap<String, Value>>
249}
250
251impl SheetDataSet {
252
253  
254
255  pub fn new(name: &str, keys: &[String], rows: &[IndexMap<String, Value>], total: usize) -> Self {
256    Self {
257      sheet: (name.to_string(), name.to_snake_case()),
258      keys: keys.to_vec(),
259      rows: rows.to_vec(),
260      num_rows: total
261    }
262  }
263
264  pub fn key(&self) -> String {
265    self.sheet.1.clone()
266  }
267
268  pub fn name(&self) -> String {
269    self.sheet.0.clone()
270  }
271}
272
273#[derive(Debug, Clone, Serialize)]
274pub enum SpreadData {
275   Single(Vec<IndexMap<String, Value>>),
276   Multiple(Vec<SheetDataSet>)
277}
278
279impl SpreadData {
280  pub fn from_single(rows: Vec<IndexMap<String, Value>>) -> Self {
281    SpreadData::Single(rows)
282  }
283
284  pub fn from_multiple(sheet_data: &[SheetDataSet]) -> Self {
285    SpreadData::Multiple(sheet_data.to_owned())
286  }
287
288  pub fn first_sheet(&self) -> Vec<IndexMap<String, Value>> {
289    match self {
290      SpreadData::Single(rows) => rows.to_owned(),
291      SpreadData::Multiple(sheets) => {
292        if let Some(sheet) = sheets.first() {
293          sheet.rows.to_owned()
294        } else {
295          vec![]
296        }
297      }
298    }
299  }
300
301  // Only for preview multiple mode
302  pub fn sheets(&self) -> Vec<SheetDataSet> {
303    match self {
304      SpreadData::Single(_) => vec![],
305      SpreadData::Multiple(sheets) => sheets.to_owned()
306    }
307  }
308
309  pub fn to_json(&self) -> Value {
310    match self {
311      SpreadData::Single(sheet) => json!(sheet),
312      SpreadData::Multiple(sheet_map) => json!(sheet_map)
313    }
314  }
315}
316
317
318#[derive(Debug, Clone, Serialize)]
319pub enum DataSet {
320   WithRows(usize, Vec<IndexMap<String, Value>>),
321   Count(usize) 
322}
323
324impl DataSet {
325  pub fn from_count_and_rows(count: usize, rows: Vec<IndexMap<String, Value>>, opts: &OptionSet) -> Self {
326    match opts.read_mode() {
327      ReadMode::Sync | ReadMode::PreviewMultiple => DataSet::WithRows(count, rows),
328      ReadMode::Async => DataSet::Count(count),
329    }
330  }
331}
332
333
334/// Builds a row's output map. A column with no `key` (or a plain `KeySegment::Simple`)
335/// inserts flatly under `headers[sub_index]`, exactly as before this function supported
336/// nesting at all. A column with a nested `key` (`Object`/`Array`/`InnerObject`) walks
337/// that path instead, via `insert_key_segment` -- see `key_segment.rs`. `columns` is
338/// optional so existing callers that only ever built flat rows (no nested mapping in
339/// play) don't need to thread a `&[Column]` through just to get today's behavior.
340pub fn to_index_map(row: &[serde_json::Value], headers: &[String], columns: Option<&[Column]>) -> IndexMap<String, Value> {
341    let mut hm: serde_json::Map<String, serde_json::Value> = serde_json::Map::new();
342    for (sub_index, hk) in headers.iter().enumerate() {
343        if let Some(cell) = row.get(sub_index) {
344            let segment = columns.and_then(|cols| cols.get(sub_index)).and_then(|c| c.key.as_ref());
345            match segment {
346                Some(key_segment) => insert_key_segment(&mut hm, key_segment, cell.to_owned()),
347                None => {
348                    hm.insert(hk.to_owned(), cell.to_owned());
349                }
350            }
351        }
352    }
353    hm.into_iter().collect()
354}
355
356/// Drops every key whose value is JSON `null` from `row`, recursively through nested
357/// objects (built via `KeySegment::Object`/`Array`/`InnerObject`) and into array items
358/// too -- but never removes an array *element* itself, even a bare `null` one; "omit
359/// null cells" is a per-key/per-field concept, not a positional one (an array's own
360/// null-dropping, where that's wanted, is `KeySegment::PlainArray`'s job at insertion
361/// time, not this row-wide pass). Only ever targets genuine `Value::Null` -- an empty
362/// string is a different, deliberate value and is left alone.
363pub fn omit_null_values(row: &mut IndexMap<String, Value>) {
364    row.retain(|_, v| !v.is_null());
365    for v in row.values_mut() {
366        strip_nested_nulls(v);
367    }
368}
369
370fn strip_nested_nulls(value: &mut Value) {
371    match value {
372        Value::Object(map) => {
373            map.retain(|_, v| !v.is_null());
374            for v in map.values_mut() {
375                strip_nested_nulls(v);
376            }
377        }
378        Value::Array(items) => {
379            for item in items.iter_mut() {
380                strip_nested_nulls(item);
381            }
382        }
383        _ => {}
384    }
385}
386
387pub fn match_sheet_name_and_index(workbook: &mut Sheets<BufReader<File>>, opts: &OptionSet) -> (Vec<String>, Vec<String>, Vec<usize>) {
388  let mut sheet_indices = vec![];
389  let mut selected_names: Vec<String> = vec![];
390  let sheet_names = workbook.worksheets().into_iter().map(|ws| ws.0).collect::<Vec<String>>();
391  if let Some(sheet_keys) = opts.selected.clone() {
392      for sheet_key in sheet_keys {
393          if let Some(sheet_index) = sheet_names.iter().position(|s| s.to_snake_case() == sheet_key.to_snake_case()) {
394              sheet_indices.push(sheet_index);
395              selected_names.push(sheet_names[sheet_index].clone());
396          }
397      }
398  }
399  if sheet_indices.is_empty() && !opts.indices.is_empty() {
400    for s_index in opts.indices.clone() {
401      let sheet_index = s_index as usize;
402      if let Some(sheet_name) = sheet_names.get(sheet_index) {
403          sheet_indices.push(sheet_index);
404          selected_names.push(sheet_name.to_owned());
405      }
406    }
407  }
408  if sheet_indices.is_empty() {
409    sheet_indices = vec![0];
410    if !sheet_names.is_empty() {
411      selected_names.push(sheet_names[0].clone());
412    }
413  }
414  (selected_names, sheet_names, sheet_indices)
415}
416
417#[cfg(test)]
418mod tests {
419  use super::*;
420  use calamine::open_workbook_auto;
421
422  // data/sample-data-2.ods has two worksheets: "Rsults-2" and "results 1"
423  const SAMPLE_PATH: &str = "data/sample-data-2.ods";
424
425  fn opts_selecting(sheet_key: &str) -> OptionSet {
426    OptionSet::new(SAMPLE_PATH).sheet_name(sheet_key)
427  }
428
429  #[test]
430  fn test_omit_null_values_drops_top_level_and_nested_nulls_but_not_empty_strings() {
431    let mut row: IndexMap<String, Value> = serde_json::from_value(serde_json::json!({
432      "title": "Title 1",
433      "notes": "",
434      "download_2": null,
435      "measurements": {"weight": 60, "height": null},
436      "tags": ["a", null, "b"]
437    })).unwrap();
438    omit_null_values(&mut row);
439    assert_eq!(
440      serde_json::to_value(&row).unwrap(),
441      serde_json::json!({
442        "title": "Title 1",
443        "notes": "",
444        "measurements": {"weight": 60},
445        // array *elements* are never dropped, only object keys -- see the function's
446        // own doc comment for why (positional vs per-key are different concepts)
447        "tags": ["a", null, "b"]
448      })
449    );
450  }
451
452  #[test]
453  fn test_sheet_name_matching_is_case_insensitive_and_ignores_punctuation() {
454    let mut workbook = open_workbook_auto(SAMPLE_PATH).unwrap();
455    // exact, uppercase, lowercase, and punctuation/whitespace variants should all match
456    // the same real sheet name "results 1" via snake_case comparison
457    for variant in ["results 1", "RESULTS 1", "Results_1", "results-1", "  results   1  "] {
458      let opts = opts_selecting(variant);
459      let (selected_names, _sheet_names, sheet_indices) = match_sheet_name_and_index(&mut workbook, &opts);
460      assert_eq!(selected_names, vec!["results 1".to_string()], "variant '{}' should match 'results 1'", variant);
461      assert_eq!(sheet_indices, vec![1], "variant '{}' should resolve to index 1", variant);
462    }
463  }
464
465  #[test]
466  fn test_sheet_name_matching_handles_names_with_no_spaces() {
467    // sheet names like "Sheet1", "Sheet2" (no internal separators at all) should still
468    // match themselves case-insensitively
469    let mut workbook = open_workbook_auto(SAMPLE_PATH).unwrap();
470    for variant in ["Rsults-2", "rsults-2", "RSULTS-2", "rsults_2"] {
471      let opts = opts_selecting(variant);
472      let (selected_names, _sheet_names, sheet_indices) = match_sheet_name_and_index(&mut workbook, &opts);
473      assert_eq!(selected_names, vec!["Rsults-2".to_string()], "variant '{}' should match 'Rsults-2'", variant);
474      assert_eq!(sheet_indices, vec![0]);
475    }
476  }
477
478  #[test]
479  fn test_sheet_name_matching_falls_back_to_first_sheet_when_unmatched() {
480    let mut workbook = open_workbook_auto(SAMPLE_PATH).unwrap();
481    let opts = opts_selecting("nonexistent sheet name");
482    let (selected_names, sheet_names, sheet_indices) = match_sheet_name_and_index(&mut workbook, &opts);
483    assert_eq!(sheet_names, vec!["Rsults-2".to_string(), "results 1".to_string()]);
484    // no match found -> falls back to the first sheet, not an error
485    assert_eq!(selected_names, vec!["Rsults-2".to_string()]);
486    assert_eq!(sheet_indices, vec![0]);
487  }
488
489  #[test]
490  fn test_sheet_index_selection_still_works() {
491    let mut workbook = open_workbook_auto(SAMPLE_PATH).unwrap();
492    let opts = OptionSet::new(SAMPLE_PATH).sheet_index(1);
493    let (selected_names, _sheet_names, sheet_indices) = match_sheet_name_and_index(&mut workbook, &opts);
494    assert_eq!(selected_names, vec!["results 1".to_string()]);
495    assert_eq!(sheet_indices, vec![1]);
496  }
497}
498
spreadsheet_to_json/data_set.rs

spreadsheet_to_json/
data_set.rs
