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

spreadsheet_to_json/
reader.rs
