spreadsheet_to_json/
reader.rs

1use calamine::{open_workbook_auto, Data, Reader, Sheets};
2use csv::{ReaderBuilder, StringRecord};
3use heck::ToSnakeCase;
4use indexmap::IndexMap;
5use serde_json::{Number, Value};
6use std::fs::File;
7use std::io::BufReader;
8use std::path::Path;
9use std::str::FromStr;
10
11use crate::data_set::*;
12use crate::error::GenericError;
13use alphanumeric::*;
14use crate::headers::*;
15use crate::helpers::float_value;
16use crate::helpers::string_value;
17use is_truthy::*;
18use crate::round_decimal::RoundDecimal;
19use crate::Extension;
20use crate::Format;
21use crate::OptionSet;
22use crate::PathData;
23use crate::RowOptionSet;
24use fuzzy_datetime::{iso_fuzzy_to_date_string, iso_fuzzy_to_datetime_string};
25
26/// Callback invoked once per row when saving asynchronously (e.g. --deferred mode)
27pub type SaveRowFn = Box<dyn Fn(IndexMap<String, Value>) -> Result<(), GenericError> + Send + Sync>;
28
29/// Output the result set with captured rows (up to the maximum allowed) directly.
30/// This is now synchronous and calls the asynchronous function using a runtime.
31pub fn process_spreadsheet_direct(opts: &OptionSet) -> Result<ResultSet, GenericError> {
32    let rt = tokio::runtime::Runtime::new().unwrap();
33    rt.block_on(process_spreadsheet_core(opts, None, None))
34}
35
36/// Output the result set with captured rows (up to the maximum allowed) immediately.
37/// Use this in an async function using the tokio runtime if you direct results
38/// without a save callback
39pub async fn process_spreadsheet_immediate(opts: &OptionSet) -> Result<ResultSet, GenericError> {
40    process_spreadsheet_core(opts, None, None).await
41}
42
43#[deprecated(
44    since = "1.0.6",
45    note = "This function is a wrapper for the renamed function `process_spreadsheet_inline`"
46)]
47pub async fn render_spreadsheet_direct(opts: &OptionSet) -> Result<ResultSet, GenericError> {
48    process_spreadsheet_core(opts, None, None).await
49}
50
51/// Output the result set with deferred row saving and optional output reference
52pub async fn process_spreadsheet_async(
53    opts: &OptionSet,
54    save_func: SaveRowFn,
55    out_ref: Option<&str>,
56) -> Result<ResultSet, GenericError> {
57    process_spreadsheet_core(opts, Some(save_func), out_ref).await
58}
59
60/// Output the result set with captured rows (up to the maximum allowed) directly.
61/// with optional asynchronous row save method and output reference
62pub async fn process_spreadsheet_core(
63    opts: &OptionSet,
64    save_opt: Option<SaveRowFn>,
65    out_ref: Option<&str>,
66) -> Result<ResultSet, GenericError> {
67    if let Some(filepath) = opts.path.clone() {
68        let path = Path::new(&filepath);
69        if !path.exists() {
70            #[allow(dead_code)]
71            return Err(GenericError("file_unavailable"));
72        }
73        let path_data = PathData::new(path);
74        if path_data.is_valid() {
75            if path_data.use_calamine() {
76                read_workbook_core(&path_data, opts, save_opt, out_ref).await
77            } else {
78                read_csv_core(&path_data, opts, save_opt, out_ref).await
79            }
80        } else {
81            Err(GenericError("unsupported_format"))
82        }
83    } else {
84        Err(GenericError("no_filepath_specified"))
85    }
86}
87
88#[deprecated(
89    since = "1.0.6",
90    note = "This function is a wrapper for the renamed function `process_spreadsheet_core`"
91)]
92pub async fn render_spreadsheet_core(
93    opts: &OptionSet,
94    save_opt: Option<SaveRowFn>,
95    out_ref: Option<&str>,
96) -> Result<ResultSet, GenericError> {
97    process_spreadsheet_core(opts, save_opt, out_ref).await
98}
99
100/// Parse spreadsheets with an optional callback method to save rows asynchronously and an optional output reference
101/// that may be a file name or database identifier
102pub async fn read_workbook_core<'a>(
103    path_data: &PathData<'a>,
104    opts: &OptionSet,
105    save_opt: Option<SaveRowFn>,
106    out_ref: Option<&str>,
107) -> Result<ResultSet, GenericError> {
108    if let Ok(mut workbook) = open_workbook_auto(path_data.path()) {
109        let max_rows = opts.max_rows();
110        let (selected_names, sheet_names, _sheet_indices) =
111            match_sheet_name_and_index(&mut workbook, opts);
112
113        if !selected_names.is_empty() {
114            let info = WorkbookInfo::new(path_data, &selected_names, &sheet_names);
115
116            if opts.multimode() {
117                read_multiple_worksheets(&mut workbook, &sheet_names, opts, &info, max_rows).await
118            } else {
119                let sheet_ref = &selected_names[0];
120                read_single_worksheet(workbook, sheet_ref, opts, &info, save_opt, out_ref).await
121            }
122        } else {
123            Err(GenericError("workbook_with_no_sheets"))
124        }
125    } else {
126        Err(GenericError("cannot_open_workbook"))
127    }
128}
129
130/// Read multiple worksheets from a workbook in preview mode.
131async fn read_multiple_worksheets(
132    workbook: &mut Sheets<BufReader<File>>,
133    sheet_names: &[String],
134    opts: &OptionSet,
135    info: &WorkbookInfo,
136    max_rows: usize,
137) -> Result<ResultSet, GenericError> {
138    let mut sheets: Vec<SheetDataSet> = vec![];
139    let capture_rows = opts.capture_rows();
140    for (sheet_index, sheet_ref) in sheet_names.iter().enumerate() {
141        let range = workbook.worksheet_range(&sheet_ref.clone())?;
142        let mut headers: Vec<String> = vec![];
143        let mut has_headers = false;
144        let capture_headers = !opts.omit_header;
145        let mut rows: Vec<IndexMap<String, Value>> =
146            Vec::with_capacity(if capture_rows { max_rows } else { 0 });
147        let mut row_index = 0;
148        let header_row_index = opts.header_row_index();
149        let mut col_keys: Vec<String> = vec![];
150        let columns = if sheet_index == 0 {
151            opts.rows.columns.clone()
152        } else {
153            vec![]
154        };
155        let mut resolved_row_opts = opts.rows.clone();
156        let match_header_row_below = capture_headers && header_row_index > 0;
157        if let Some(first_row) = range.headers() {
158            let natural_keys = natural_column_keys(&first_row, &opts.field_mode);
159            let resolved_columns = resolve_columns(&columns, &natural_keys);
160            headers = build_header_keys(&first_row, &resolved_columns, &opts.field_mode);
161            resolved_row_opts.columns = resolved_columns;
162            has_headers = !match_header_row_below;
163            col_keys = first_row;
164        }
165        let total = range.get_size().0;
166        if capture_rows || match_header_row_below {
167            let max_row_count = if capture_rows {
168                max_rows
169            } else {
170                header_row_index + 2
171            };
172            let max_take = if total < max_row_count {
173                total
174            } else {
175                max_row_count + 1
176            };
177            for row in range.rows().take(max_take) {
178                if row_index > max_row_count {
179                    break;
180                }
181                if match_header_row_below && (row_index + 1) == header_row_index {
182                    let h_row = row
183                        .iter()
184                        .map(|c| c.to_string().to_snake_case())
185                        .collect::<Vec<String>>();
186                    let natural_keys = natural_column_keys(&h_row, &opts.field_mode);
187                    let resolved_columns = resolve_columns(&columns, &natural_keys);
188                    headers = build_header_keys(&h_row, &resolved_columns, &opts.field_mode);
189                    resolved_row_opts.columns = resolved_columns;
190                    has_headers = true;
191                } else if (has_headers || !capture_headers) && capture_rows {
192                    let raw_values: Vec<String> = row.iter().map(|c| c.to_string()).collect();
193                    if is_not_header_row(&raw_values, row_index, &col_keys) {
194                        let row_map = workbook_row_to_map(row, &resolved_row_opts, &headers);
195                        rows.push(row_map);
196                    }
197                }
198                row_index += 1;
199            }
200        }
201        sheets.push(SheetDataSet::new(sheet_ref, &headers, &rows, total));
202    }
203    Ok(ResultSet::from_multiple(&sheets, info, opts))
204}
205
206/// Read a single worksheet from a workbook in immediate (sync) or asycnhronous modes
207pub async fn read_single_worksheet(
208    mut workbook: Sheets<BufReader<File>>,
209    sheet_ref: &str,
210    opts: &OptionSet,
211    info: &WorkbookInfo,
212    save_opt: Option<SaveRowFn>,
213    out_ref: Option<&str>,
214) -> Result<ResultSet, GenericError> {
215    let range = workbook.worksheet_range(sheet_ref)?;
216    let capture_rows = opts.capture_rows();
217    let columns = opts.rows.columns.clone();
218    let max_rows = opts.max_rows();
219    let mut headers: Vec<String> = vec![];
220    let mut col_keys: Vec<String> = vec![];
221    let mut has_headers = false;
222    let capture_headers = !opts.omit_header;
223    let mut rows: Vec<IndexMap<String, Value>> =
224        Vec::with_capacity(if capture_rows { max_rows } else { 0 });
225    let mut row_index = 0;
226    let header_row_index = opts.header_row_index();
227    let match_header_row_below = capture_headers && header_row_index > 0;
228    let mut resolved_row_opts = opts.rows.clone();
229
230    if let Some(first_row) = range.headers() {
231        let natural_keys = natural_column_keys(&first_row, &opts.field_mode);
232        let resolved_columns = resolve_columns(&columns, &natural_keys);
233        headers = build_header_keys(&first_row, &resolved_columns, &opts.field_mode);
234        resolved_row_opts.columns = resolved_columns;
235        has_headers = !match_header_row_below;
236        col_keys = first_row;
237    }
238    let total = range.get_size().0;
239    if capture_rows || match_header_row_below {
240        let max_row_count = if capture_rows {
241            max_rows
242        } else {
243            header_row_index + 2
244        };
245        let max_take = if total < max_row_count {
246            total
247        } else {
248            max_row_count + 1
249        };
250        for row in range.rows().take(max_take) {
251            if row_index > max_row_count {
252                break;
253            }
254            if match_header_row_below && (row_index + 1) == header_row_index {
255                let h_row = row
256                    .iter()
257                    .map(|c| c.to_string().to_snake_case())
258                    .collect::<Vec<String>>();
259                let natural_keys = natural_column_keys(&h_row, &opts.field_mode);
260                let resolved_columns = resolve_columns(&columns, &natural_keys);
261                headers = build_header_keys(&h_row, &resolved_columns, &opts.field_mode);
262                resolved_row_opts.columns = resolved_columns;
263                has_headers = true;
264            } else if (has_headers || !capture_headers) && capture_rows {
265                // only capture rows if headers are either omitted or have already been captured
266                let raw_values: Vec<String> = row.iter().map(|c| c.to_string()).collect();
267                if is_not_header_row(&raw_values, row_index, &col_keys) {
268                    let row_map = workbook_row_to_map(row, &resolved_row_opts, &headers);
269                    rows.push(row_map);
270                }
271            }
272            row_index += 1;
273        }
274    }
275    if let Some(save_method) = save_opt {
276        let mut row_iter = range.rows();
277        let mut save_count: usize = 0;
278        if let Some(first_row) = row_iter.next() {
279            let raw_values: Vec<String> = first_row.iter().map(|c| c.to_string()).collect();
280            if is_not_header_row(&raw_values, 0, &col_keys) {
281                let first_row_map = workbook_row_to_map(first_row, &resolved_row_opts, &headers);
282                save_method(first_row_map)?;
283                save_count += 1;
284            }
285        }
286        for row in row_iter {
287            if save_count >= max_rows {
288                break;
289            }
290            let row_map = workbook_row_to_map(row, &resolved_row_opts, &headers);
291            save_method(row_map)?;
292            save_count += 1;
293        }
294    }
295
296    let ds = DataSet::from_count_and_rows(total, rows, opts);
297    Ok(ResultSet::new(info, &headers, ds, opts, out_ref))
298}
299
300/// Process a CSV/TSV file asynchronously with an optional row save method
301/// and output reference (file or database table reference)
302pub async fn read_csv_core<'a>(
303    path_data: &PathData<'a>,
304    opts: &OptionSet,
305    save_opt: Option<SaveRowFn>,
306    out_ref: Option<&str>,
307) -> Result<ResultSet, GenericError> {
308    let separator = match path_data.mode() {
309        Extension::Tsv => b't',
310        _ => b',',
311    };
312    if let Ok(mut rdr) = ReaderBuilder::new()
313        .delimiter(separator)
314        .from_path(path_data.path())
315    {
316        let capture_header = !opts.omit_header;
317        let capture_rows = opts.capture_rows();
318        let max_line_usize = opts.max_rows();
319        let mut rows: Vec<IndexMap<String, Value>> =
320            Vec::with_capacity(if capture_rows { max_line_usize } else { 0 });
321        let mut line_count = 0;
322
323        let mut headers: Vec<String> = vec![];
324        let mut resolved_row_opts = opts.rows.clone();
325        if capture_header {
326            if let Ok(hdrs) = rdr.headers() {
327                headers = hdrs.into_iter().map(|s| s.to_owned()).collect();
328            }
329            let columns = opts.rows.columns.clone();
330            let natural_keys = natural_column_keys(&headers, &opts.field_mode);
331            let resolved_columns = resolve_columns(&columns, &natural_keys);
332            headers = build_header_keys(&headers, &resolved_columns, &opts.field_mode);
333            resolved_row_opts.columns = resolved_columns;
334        }
335
336        let mut total = 0;
337        if capture_rows {
338            for result in rdr.records() {
339                if line_count >= max_line_usize {
340                    break;
341                }
342                if let Some(row) = csv_row_result_to_values(result, &resolved_row_opts) {
343                    rows.push(to_index_map(&row, &headers));
344                    line_count += 1;
345                }
346            }
347            total = line_count + rdr.records().count() + 1;
348        } else if let Some(save_method) = save_opt {
349            for result in rdr.records() {
350                if let Some(row) = csv_row_result_to_values(result, &resolved_row_opts) {
351                    let row_map = to_index_map(&row, &headers);
352                    save_method(row_map)?;
353                    total += 1;
354                }
355            }
356        } else {
357            if let Ok(mut count_rdr) = ReaderBuilder::new().from_path(path_data.path()) {
358                total = count_rdr.records().count();
359            }
360        }
361        let info = WorkbookInfo::simple(path_data);
362        let ds = DataSet::from_count_and_rows(total, rows, opts);
363        Ok(ResultSet::new(&info, &headers, ds, opts, out_ref))
364    } else {
365        let error_msg = match path_data.ext() {
366            Extension::Tsv => "unreadable_tsv_file",
367            _ => "unreadable_csv_file",
368        };
369        Err(GenericError(error_msg))
370    }
371}
372
373// Convert an array of row data to an IndexMap of serde_json::Value objects
374fn workbook_row_to_map(
375    row: &[Data],
376    opts: &RowOptionSet,
377    headers: &[String],
378) -> IndexMap<String, Value> {
379    to_index_map(&workbook_row_to_values(row, opts), headers)
380}
381
382// Convert an array of row data to a vector of serde_json::Value objects
383fn workbook_row_to_values(row: &[Data], opts: &RowOptionSet) -> Vec<Value> {
384    row.iter()
385        .enumerate()
386        .map(|(c_index, cell)| workbook_cell_to_value(cell, opts, c_index))
387        .collect()
388}
389
390/// Convert a spreadsheet data cell to a polymorphic serde_json::Value object
391fn workbook_cell_to_value(cell: &Data, opts: &RowOptionSet, c_index: usize) -> Value {
392    let col = opts.column(c_index);
393    let format = col.map_or(Format::Auto, |c| c.format.to_owned());
394    let def_val = col.and_then(|c| c.default.clone());
395
396    let date_only = resolve_date_only(&format, opts.date_only);
397
398    match cell {
399        Data::Int(i) => Value::Number(Number::from_i128(*i as i128).unwrap()),
400        Data::Float(f) => process_float_value(*f, format),
401        Data::DateTimeIso(d) => process_iso_datetime_value(d, def_val, date_only),
402        Data::DateTime(d) => process_excel_datetime_value(d, def_val, date_only),
403        Data::Bool(b) => Value::Bool(*b),
404        Data::String(s) => process_string_value(s, format, def_val),
405        Data::Empty => def_val.unwrap_or(Value::Null),
406        _ => Value::String(cell.to_string()),
407    }
408}
409
410/// A column's own Format::Date/Format::DateTime override takes precedence over the
411/// row-wide --date-only default; any other format (including Format::Auto) falls back
412/// to that row-wide default, unchanged from before this override existed.
413fn resolve_date_only(format: &Format, row_date_only: bool) -> bool {
414    match format {
415        Format::Date => true,
416        Format::DateTime => false,
417        _ => row_date_only,
418    }
419}
420
421fn process_float_value(value: f64, format: Format) -> Value {
422    match format {
423        Format::Integer => Value::Number(Number::from_i128(value as i128).unwrap()),
424        Format::Boolean => Value::Bool(value >= 1.0),
425        Format::Text => Value::String(value.to_string()),
426        _ => Value::Number(Number::from_f64(value).unwrap()),
427    }
428}
429
430fn process_excel_datetime_value(
431    datetime: &calamine::ExcelDateTime,
432    def_val: Option<Value>,
433    date_only: bool,
434) -> Value {
435    let dt_ref = datetime.as_datetime().map_or_else(
436        || def_val.unwrap_or(Value::Null),
437        |dt| {
438            let formatted_date = if date_only {
439                dt.format("%Y-%m-%d").to_string()
440            } else {
441                dt.format("%Y-%m-%dT%H:%M:%S%.3fZ").to_string()
442            };
443            Value::String(formatted_date)
444        },
445    );
446    dt_ref
447}
448
449fn process_iso_datetime_value(dt_str: &str, def_val: Option<Value>, date_only: bool) -> Value {
450    if date_only {
451        iso_fuzzy_to_date_string(dt_str)
452            .map_or_else(|| def_val.unwrap_or(Value::Null), Value::String)
453    } else {
454        iso_fuzzy_to_datetime_string(dt_str)
455            .map_or_else(|| def_val.unwrap_or(Value::Null), Value::String)
456    }
457}
458
459fn process_string_value(value: &str, format: Format, def_val: Option<Value>) -> Value {
460    match format {
461        Format::Boolean => process_truthy_value(value, def_val, |v, ef| v.is_truthy_core(ef)),
462        Format::Truthy => process_truthy_value(value, def_val, |v, ef| v.is_truthy_standard(ef)),
463        Format::TruthyCustom(opts) => process_truthy_value(value, def_val, |v, _| {
464            v.is_truthy_custom(&opts)
465        }),
466        Format::Decimal(places) => {
467            process_numeric_value(value, def_val, |n| float_value(n.round_decimal(places)))
468        }
469        Format::Float => process_numeric_value(value, def_val, float_value),
470        Format::Date => process_date_value(value, def_val, iso_fuzzy_to_date_string),
471        Format::DateTime => process_date_value(value, def_val, iso_fuzzy_to_datetime_string),
472        _ => Value::String(value.to_owned()),
473    }
474}
475
476fn process_truthy_value<F>(value: &str, def_val: Option<Value>, truthy_fn: F) -> Value
477where
478    F: Fn(&str, bool) -> Option<bool>,
479{
480    if let Some(is_true) = truthy_fn(value, false) {
481        Value::Bool(is_true)
482    } else {
483        def_val.unwrap_or(Value::Null)
484    }
485}
486
487fn process_numeric_value<F>(value: &str, def_val: Option<Value>, numeric_fn: F) -> Value
488where
489    F: Fn(f64) -> Value,
490{
491    if let Some(n) = value.to_first_number::<f64>() {
492        numeric_fn(n)
493    } else {
494        def_val.unwrap_or(Value::Null)
495    }
496}
497
498fn process_date_value<F>(value: &str, def_val: Option<Value>, date_fn: F) -> Value
499where
500    F: Fn(&str) -> Option<String>,
501{
502    if let Some(date_str) = date_fn(value) {
503        string_value(&date_str)
504    } else {
505        def_val.unwrap_or(Value::Null)
506    }
507}
508
509// Convert csv rows to value
510fn csv_row_result_to_values(
511    result: Result<StringRecord, csv::Error>,
512    opts: &RowOptionSet,
513) -> Option<Vec<Value>> {
514    if let Ok(record) = result {
515        let row = record
516            .into_iter()
517            .enumerate()
518            .map(|(ci, cell)| csv_cell_to_json_value(cell, opts, ci))
519            .collect();
520        return Some(row);
521    }
522    None
523}
524
525// convert CSV cell &str value to a polymorphic serde_json::VALUE
526fn csv_cell_to_json_value(cell: &str, opts: &RowOptionSet, index: usize) -> Value {
527    let has_number = cell.to_first_number::<f64>().is_some();
528    // clean cell to check if it's numeric
529    let col = opts.column(index);
530    let (fmt, euro_num_mode) = if let Some(c) = col {
531        (c.format.clone(), c.decimal_comma)
532    } else {
533        (Format::Auto, opts.decimal_comma)
534    };
535    let num_cell = if has_number {
536        let euro_num_mode = uses_decimal_comma(cell, euro_num_mode);
537        if euro_num_mode {
538            cell.replace(",", ".").replace(",", ".")
539        } else {
540            cell.replace(",", "")
541        }
542    } else {
543        cell.to_owned()
544    };
545    let mut new_cell = Value::Null;
546    if !num_cell.is_empty() && num_cell.is_numeric() {
547        if let Ok(float_val) = serde_json::Number::from_str(&num_cell) {
548            match fmt {
549                Format::Integer => {
550                    // as_i128() only succeeds for Numbers that are already integer-valued
551                    // internally, so it silently yields 0 for any decimal value (e.g. "58.2")
552                    // via unwrap_or(0). Go through as_f64() and truncate instead, matching
553                    // the equivalent xlsx/ods cell conversion in process_float_value above.
554                    if let Some(f) = float_val.as_f64() {
555                        if let Some(int_val) = Number::from_i128(f as i128) {
556                            new_cell = Value::Number(int_val);
557                        }
558                    }
559                }
560                Format::Boolean => {
561                    // only 1.0 or more will evaluate as true
562                    new_cell = Value::Bool(float_val.as_f64().unwrap_or(0f64) >= 1.0);
563                }
564                _ => {
565                    new_cell = Value::Number(float_val);
566                }
567            }
568        }
569    } else if let Some(is_true) = cell.is_truthy_core(false) {
570        new_cell = Value::Bool(is_true);
571    } else {
572        new_cell = match fmt {
573            Format::Truthy => {
574                if let Some(is_true) = cell.is_truthy_standard(false) {
575                    Value::Bool(is_true)
576                } else {
577                    Value::Null
578                }
579            }
580            _ => Value::String(cell.to_string()),
581        };
582    }
583    new_cell
584}
585
586pub async fn read_workbook_sheet_info<'a>(
587    path_data: &PathData<'a>,
588) -> Result<IndexMap<String, usize>, GenericError> {
589    if let Ok(mut workbook) = open_workbook_auto(path_data.path()) {
590        let mut im: IndexMap<String, usize> = IndexMap::new();
591        for name in workbook.sheet_names() {
592            if let Ok(range) = workbook.worksheet_range(&name) {
593                im.insert(name, range.rows().count());
594            }
595        }
596        Ok(im)
597    } else {
598        Err(GenericError("cannot_open_workbook"))
599    }
600}
601
602#[cfg(test)]
603mod tests {
604    use super::*;
605    use crate::{helpers::*, Column};
606    use serde_json::json;
607    use std::path;
608
609    #[test]
610    fn test_direct_processing_xlsx() {
611        let sample_path = "data/sample-data-1.xlsx";
612
613        // instantiate the OptionSet with a sample path and a maximum row count of 1000 rows as the source file has 401 rows
614        // (although )the default max is 10,000)
615        let opts = OptionSet::new(sample_path).max_row_count(1_000);
616
617        let result = process_spreadsheet_direct(&opts);
618
619        // The source file should have 1 header row and 400 data rows
620        assert_eq!(result.unwrap().num_rows, 401);
621    }
622
623    #[test]
624    fn test_source_key_override_renames_and_reformats_without_position() {
625        // End-to-end: overriding just one field out of many by its natural key,
626        // without needing to enumerate/pad the columns ahead of it.
627        let sample_path = "data/sample-data-1.csv";
628        let mut opts = OptionSet::new(sample_path).max_row_count(2);
629        opts.rows.columns = vec![
630            Column::from_source_key_with_format("weight", Some("weight_lbs"), Format::Integer, None, false, false),
631        ];
632
633        let result = process_spreadsheet_direct(&opts).unwrap();
634        // every other column keeps its natural, auto-detected name
635        assert!(result.keys.contains(&"id".to_string()));
636        assert!(result.keys.contains(&"first_name".to_string()));
637        assert!(result.keys.contains(&"weight_lbs".to_string()));
638        assert!(!result.keys.contains(&"weight".to_string()));
639
640        let rows = result.to_vec();
641        let first = rows.first().expect("at least one row");
642        assert!(first.get("weight_lbs").is_some());
643        assert!(first.get("weight").is_none());
644        assert_eq!(first.get("id").unwrap(), 1);
645    }
646
647    #[test]
648    fn test_resolve_date_only_prefers_column_format_over_row_default() {
649        // A column's own Format::Date/Format::DateTime overrides the row-wide
650        // --date-only default; Format::Auto (and anything else) falls back to it.
651        assert!(resolve_date_only(&Format::Date, false));
652        assert!(!resolve_date_only(&Format::DateTime, true));
653        assert!(!resolve_date_only(&Format::Auto, false));
654        assert!(resolve_date_only(&Format::Auto, true));
655    }
656
657    #[test]
658    fn test_source_key_override_casts_native_datetime_cell_to_date_only() {
659        // Regression test: workbook_cell_to_value computed the column's Format override
660        // but only ever consulted the row-wide --date-only flag for Data::DateTime /
661        // Data::DateTimeIso cells, so a per-column `Format::Date` override on a real
662        // (non-string) datetime cell had no effect at all.
663        let sample_path = "data/sample-data-1.xlsx";
664        let mut opts = OptionSet::new(sample_path).max_row_count(1);
665        opts.rows.columns = vec![
666            Column::from_source_key_with_format("start_time", None, Format::Date, None, false, false),
667        ];
668
669        let result = process_spreadsheet_direct(&opts).unwrap();
670        let rows = result.to_vec();
671        let first = rows.first().expect("at least one row");
672        let start_time = first.get("start_time").expect("start_time column").as_str().unwrap();
673        assert_eq!(start_time, "2023-06-15");
674        assert!(!start_time.contains('T'), "should be date-only, got: {}", start_time);
675    }
676
677    #[test]
678    fn test_csv_cell_integer_format_truncates_decimal_values() {
679        // Regression test: Number::as_i128() only succeeds for already-integer-valued
680        // Numbers, so casting a decimal CSV cell like "58.2" to Format::Integer used to
681        // silently produce 0 via unwrap_or(0) instead of the truncated value.
682        let cols = vec![Column::new_format(Format::Integer, None)];
683        let row_opts = RowOptionSet::simple(&cols);
684        assert_eq!(csv_cell_to_json_value("58.2", &row_opts, 0), Value::Number(Number::from(58)));
685        assert_eq!(csv_cell_to_json_value("82.5", &row_opts, 0), Value::Number(Number::from(82)));
686        assert_eq!(csv_cell_to_json_value("100", &row_opts, 0), Value::Number(Number::from(100)));
687    }
688
689    #[test]
690    fn test_csv_cell_does_not_coerce_ids_to_booleans() {
691        // Regression test: these previously became `true`/`false` because their
692        // embedded digit run (e.g. "SKU001" -> "001" -> 1) was fuzzily extracted
693        // and matched against is_truthy_core's numeric range, even though the
694        // column has no boolean intent (Format::Auto, the default).
695        let row_opts = RowOptionSet::default();
696        assert_eq!(csv_cell_to_json_value("SKU001", &row_opts, 0), Value::String("SKU001".to_string()));
697        assert_eq!(csv_cell_to_json_value("A1", &row_opts, 0), Value::String("A1".to_string()));
698        assert_eq!(csv_cell_to_json_value("01/06/2024", &row_opts, 0), Value::String("01/06/2024".to_string()));
699        // literal boolean tokens should still be recognised
700        assert_eq!(csv_cell_to_json_value("true", &row_opts, 0), Value::Bool(true));
701        assert_eq!(csv_cell_to_json_value("false", &row_opts, 0), Value::Bool(false));
702    }
703
704    #[test]
705    fn test_direct_processing_csv() {
706        let sample_path = "data/sample-data-1.csv";
707
708        // instantiate the OptionSet with a sample path and a maximum row count of 1000 rows as the source file has 401 rows
709        // (although )the default max is 10,000)
710        let opts = OptionSet::new(sample_path).max_row_count(1_000);
711
712        let result = process_spreadsheet_direct(&opts);
713
714        // The source file should have 1 header row and 400 data rows
715        assert_eq!(result.unwrap().num_rows, 401);
716    }
717
718    #[test]
719    fn test_multisheet_preview_ods() {
720        let sample_path = "data/sample-data-2.ods";
721
722        // instantiate the OptionSet with a sample path
723        // a maximum row count returned of 10 rows
724        // and read mode to *preview* to scan all sheets
725        // It should correctly calculate
726        let opts = OptionSet::new(sample_path)
727            .max_row_count(10)
728            .read_mode_preview();
729
730        let result = process_spreadsheet_direct(&opts);
731
732        // The source spreadsheet should have 2 sheets
733        let dataset = result.unwrap();
734        assert_eq!(dataset.sheets.len(), 2);
735        // The source spreadsheet should have 101 + 17 (= 118) populated rows including headers
736        assert_eq!(dataset.num_rows, 118);
737
738        // The first sheet's data should only output 10 rows (including the header)
739        assert_eq!(dataset.data.first_sheet().len(), 10);
740    }
741
742    #[test]
743    fn test_column_override_1() {
744        let sample_json = json!({
745          "sku": "CHAIR16",
746          "height": "112cm",
747          "width": "69cm",
748          "approved": "Y"
749        });
750
751        let rows = json_object_to_calamine_data(sample_json);
752
753        let cols = vec![
754            Column::new_format(Format::Text, Some(string_value(""))),
755            Column::new_format(Format::Float, Some(float_value(95.0))),
756            Column::new_format(Format::Float, Some(float_value(65.0))),
757            Column::new_format(Format::Truthy, Some(bool_value(false))),
758        ];
759
760        // The first sheet's data should only output 10 rows (including the header)
761        let opts = &RowOptionSet::simple(&cols);
762        let result = workbook_row_to_values(&rows, opts);
763        // the second column be cast to 112.0
764        assert_eq!(result.get(1).unwrap(), 112.0);
765        // the third column be cast to 69.0
766        assert_eq!(result.get(2).unwrap(), 69.0);
767        // the fourth column be cast to boolean
768        assert_eq!(result.get(3).unwrap(), true);
769    }
770
771    #[test]
772    fn test_column_override_2() {
773        let sample_json = json!({
774          "name": "Sophia",
775          "dob": "2001-9-23",
776          "weight": "62kg",
777          "result": "GOOD"
778        });
779
780        let rows = json_object_to_calamine_data(sample_json);
781
782        let cols = vec![
783            Column::new_format(Format::Text, None),
784            Column::new_format(Format::Date, None),
785            Column::new_format(Format::Float, None),
786            // the fourth column be cast to boolean
787            Column::new_format(
788                Format::truthy_custom("good", "bad"),
789                Some(bool_value(false)),
790            ),
791        ];
792
793        // The first sheet's data should only output 10 rows (including the header)
794        let opts = &RowOptionSet::simple(&cols);
795        let result = workbook_row_to_values(&rows, opts);
796        assert_eq!(result.get(1).unwrap(), "2001-09-23");
797        assert_eq!(result.get(2).unwrap(), 62.0);
798        assert_eq!(result.get(3).unwrap(), true);
799    }
800
801    #[tokio::test]
802    async fn test_read_workbook_info() {
803        let sample_path = "data/sample-data-1.xlsx";
804        let path_data = PathData::new(path::Path::new(sample_path));
805        let info = read_workbook_sheet_info(&path_data).await;
806        assert!(info.is_ok());
807    }
808
809    #[tokio::test]
810    async fn test_large_csv_file() {
811        let sample_path = "data/large-datasheet.csv";
812        let max_rows = 100_000;
813        let opts = OptionSet::new(sample_path).max_row_count(max_rows);
814        let result = process_spreadsheet_core(&opts, None, None).await;
815        if let Ok(data) = result.clone() {
816            assert_eq!(data.data.first_sheet().len(), max_rows as usize);
817        } else {
818            panic!("Failed to process large CSV file");
819        }
820        assert!(result.is_ok());
821    }
822
823    #[tokio::test]
824    async fn test_medium_excel_file() {
825        let sample_path = "data/medium-spreadsheet-50_000.xlsx";
826        let max_rows = 5_000;
827        let opts = OptionSet::new(sample_path).max_row_count(max_rows);
828        let result = process_spreadsheet_core(&opts, None, None).await;
829        if let Ok(data) = result.clone() {
830            assert_eq!(data.data.first_sheet().len(), max_rows as usize);
831        } else {
832            panic!("Failed to process large Excel file");
833        }
834        assert!(result.is_ok());
835    }
836}
spreadsheet_to_json/reader.rs

spreadsheet_to_json/
reader.rs
