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 match_header_row_below = capture_headers && header_row_index > 0;
161        if let Some(first_row) = range.headers() {
162            headers = build_header_keys(&first_row, &columns, &opts.field_mode);
163            has_headers = !match_header_row_below;
164            col_keys = first_row;
165        }
166        let total = range.get_size().0;
167        if capture_rows || match_header_row_below {
168            let max_row_count = if capture_rows {
169                max_rows
170            } else {
171                header_row_index + 2
172            };
173            let max_take = if total < max_row_count {
174                total
175            } else {
176                max_row_count + 1
177            };
178            for row in range.rows().take(max_take) {
179                if row_index > max_row_count {
180                    break;
181                }
182                if match_header_row_below && (row_index + 1) == header_row_index {
183                    let h_row = row
184                        .into_iter()
185                        .map(|c| c.to_string().to_snake_case())
186                        .collect::<Vec<String>>();
187                    headers = build_header_keys(&h_row, &columns, &opts.field_mode);
188                    has_headers = true;
189                } else if (has_headers || !capture_headers) && capture_rows {
190                    let row_map = workbook_row_to_map(row, &opts.rows, &headers);
191                    if is_not_header_row(&row_map, row_index, &col_keys) {
192                        rows.push(row_map);
193                    }
194                }
195                row_index += 1;
196            }
197        }
198        sheets.push(SheetDataSet::new(&sheet_ref, &headers, &rows, total));
199        sheet_index += 1;
200    }
201    Ok(ResultSet::from_multiple(&sheets, &info, opts))
202}
203
204/// Read a single worksheet from a workbook in immediate (sync) or asycnhronous modes
205pub async fn read_single_worksheet(
206    mut workbook: Sheets<BufReader<File>>,
207    sheet_ref: &str,
208    opts: &OptionSet,
209    info: &WorkbookInfo,
210    save_opt: Option<
211        Box<dyn Fn(IndexMap<String, Value>) -> Result<(), GenericError> + Send + Sync>,
212    >,
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
229    if let Some(first_row) = range.headers() {
230        headers = build_header_keys(&first_row, &columns, &opts.field_mode);
231        has_headers = !match_header_row_below;
232        col_keys = first_row;
233    }
234    let total = range.get_size().0;
235    if capture_rows || match_header_row_below {
236        let max_row_count = if capture_rows {
237            max_rows
238        } else {
239            header_row_index + 2
240        };
241        let max_take = if total < max_row_count {
242            total
243        } else {
244            max_row_count + 1
245        };
246        for row in range.rows().take(max_take) {
247            if row_index > max_row_count {
248                break;
249            }
250            if match_header_row_below && (row_index + 1) == header_row_index {
251                let h_row = row
252                    .into_iter()
253                    .map(|c| c.to_string().to_snake_case())
254                    .collect::<Vec<String>>();
255                headers = build_header_keys(&h_row, &columns, &opts.field_mode);
256                has_headers = true;
257            } else if (has_headers || !capture_headers) && capture_rows {
258                // only capture rows if headers are either omitted or have already been captured
259                let row_map = workbook_row_to_map(row, &opts.rows, &headers);
260                if is_not_header_row(&row_map, row_index, &col_keys) {
261                    rows.push(row_map);
262                }
263            }
264            row_index += 1;
265        }
266    }
267    if let Some(save_method) = save_opt {
268        let mut row_iter = range.rows();
269        let mut save_count: usize = 0;
270        if let Some(first_row) = row_iter.next() {
271            let first_row_map = workbook_row_to_map(first_row, &opts.rows, &headers);
272            if is_not_header_row(&first_row_map, 0, &col_keys) {
273                save_method(first_row_map)?;
274                save_count += 1;
275            }
276        }
277        for row in row_iter {
278            if save_count >= max_rows {
279                break;
280            }
281            let row_map = workbook_row_to_map(row, &opts.rows, &headers);
282            save_method(row_map)?;
283            save_count += 1;
284        }
285    }
286
287    let ds = DataSet::from_count_and_rows(total, rows, opts);
288    Ok(ResultSet::new(info, &headers, ds, opts, out_ref))
289}
290
291/// Process a CSV/TSV file asynchronously with an optional row save method
292/// and output reference (file or database table reference)
293pub async fn read_csv_core<'a>(
294    path_data: &PathData<'a>,
295    opts: &OptionSet,
296    save_opt: Option<
297        Box<dyn Fn(IndexMap<String, Value>) -> Result<(), GenericError> + Send + Sync>,
298    >,
299    out_ref: Option<&str>,
300) -> Result<ResultSet, GenericError> {
301    let separator = match path_data.mode() {
302        Extension::Tsv => b't',
303        _ => b',',
304    };
305    if let Ok(mut rdr) = ReaderBuilder::new()
306        .delimiter(separator)
307        .from_path(path_data.path())
308    {
309        let capture_header = opts.omit_header == false;
310        let capture_rows = opts.capture_rows();
311        let max_line_usize = opts.max_rows();
312        let mut rows: Vec<IndexMap<String, Value>> =
313            Vec::with_capacity(if capture_rows { max_line_usize } else { 0 });
314        let mut line_count = 0;
315
316        let mut headers: Vec<String> = vec![];
317        if capture_header {
318            if let Ok(hdrs) = rdr.headers() {
319                headers = hdrs.into_iter().map(|s| s.to_owned()).collect();
320            }
321            let columns = opts.rows.columns.clone();
322            headers = build_header_keys(&headers, &columns, &opts.field_mode);
323        }
324
325        let mut total = 0;
326        let opts_rows_arc = Arc::new(&opts.rows);
327        if capture_rows {
328            for result in rdr.records() {
329                if line_count >= max_line_usize {
330                    break;
331                }
332                if let Some(row) = csv_row_result_to_values(result, opts_rows_arc.clone()) {
333                    rows.push(to_index_map(&row, &headers));
334                    line_count += 1;
335                }
336            }
337            total = line_count + rdr.records().count() + 1;
338        } else if let Some(save_method) = save_opt {
339            for result in rdr.records() {
340                if let Some(row) = csv_row_result_to_values(result, opts_rows_arc.clone()) {
341                    let row_map = to_index_map(&row, &headers);
342                    save_method(row_map)?;
343                    total += 1;
344                }
345            }
346        } else {
347            if let Ok(mut count_rdr) = ReaderBuilder::new().from_path(&path_data.path()) {
348                total = count_rdr.records().count();
349            }
350        }
351        let info = WorkbookInfo::simple(path_data);
352        let ds = DataSet::from_count_and_rows(total, rows, opts);
353        Ok(ResultSet::new(&info, &headers, ds, opts, out_ref))
354    } else {
355        let error_msg = match path_data.ext() {
356            Extension::Tsv => "unreadable_tsv_file",
357            _ => "unreadable_csv_file",
358        };
359        Err(GenericError(error_msg))
360    }
361}
362
363// Convert an array of row data to an IndexMap of serde_json::Value objects
364fn workbook_row_to_map(
365    row: &[Data],
366    opts: &RowOptionSet,
367    headers: &[String],
368) -> IndexMap<String, Value> {
369    to_index_map(&workbook_row_to_values(row, opts), headers)
370}
371
372// Convert an array of row data to a vector of serde_json::Value objects
373fn workbook_row_to_values(row: &[Data], opts: &RowOptionSet) -> Vec<Value> {
374    let opts_arc = Arc::new(opts);
375    let mut c_index = 0;
376    let mut cells: Vec<Value> = vec![];
377    for cell in row {
378        let value = workbook_cell_to_value(cell, opts_arc.clone(), c_index);
379        cells.push(value);
380        c_index += 1;
381    }
382    cells
383}
384
385/// Convert a spreadsheet data cell to a polymorphic serde_json::Value object
386fn workbook_cell_to_value(cell: &Data, opts: Arc<&RowOptionSet>, c_index: usize) -> Value {
387    let col = opts.column(c_index);
388    let format = col.map_or(Format::Auto, |c| c.format.to_owned());
389    let def_val = col.and_then(|c| c.default.clone());
390
391    match cell {
392        Data::Int(i) => Value::Number(Number::from_i128(*i as i128).unwrap()),
393        Data::Float(f) => process_float_value(*f, format),
394        Data::DateTimeIso(d) => process_iso_datetime_value(d, def_val, opts.date_only),
395        Data::DateTime(d) => process_excel_datetime_value(d, def_val, opts.date_only),
396        Data::Bool(b) => Value::Bool(*b),
397        Data::String(s) => process_string_value(s, format, def_val),
398        Data::Empty => def_val.unwrap_or(Value::Null),
399        _ => Value::String(cell.to_string()),
400    }
401}
402
403fn process_float_value(value: f64, format: Format) -> Value {
404    match format {
405        Format::Integer => Value::Number(Number::from_i128(value as i128).unwrap()),
406        Format::Boolean => Value::Bool(value >= 1.0),
407        Format::Text => Value::String(value.to_string()),
408        _ => Value::Number(Number::from_f64(value).unwrap()),
409    }
410}
411
412fn process_excel_datetime_value(
413    datetime: &calamine::ExcelDateTime,
414    def_val: Option<Value>,
415    date_only: bool,
416) -> Value {
417    let dt_ref = datetime.as_datetime().map_or_else(
418        || def_val.unwrap_or(Value::Null),
419        |dt| {
420            let formatted_date = if date_only {
421                dt.format("%Y-%m-%d").to_string()
422            } else {
423                dt.format("%Y-%m-%dT%H:%M:%S%.3fZ").to_string()
424            };
425            Value::String(formatted_date)
426        },
427    );
428    dt_ref
429}
430
431fn process_iso_datetime_value(dt_str: &str, def_val: Option<Value>, date_only: bool) -> Value {
432    if date_only {
433        iso_fuzzy_to_date_string(dt_str)
434            .map_or_else(|| def_val.unwrap_or(Value::Null), |dt| Value::String(dt))
435    } else {
436        iso_fuzzy_to_datetime_string(dt_str)
437            .map_or_else(|| def_val.unwrap_or(Value::Null), |dt| Value::String(dt))
438    }
439}
440
441fn process_string_value(value: &str, format: Format, def_val: Option<Value>) -> Value {
442    match format {
443        Format::Boolean => process_truthy_value(value, def_val, |v, ef| v.is_truthy_core(ef)),
444        Format::Truthy => process_truthy_value(value, def_val, |v, ef| v.is_truthy_standard(ef)),
445        Format::TruthyCustom(opts) => process_truthy_value(value, def_val, |v, _| {
446            v.is_truthy_custom(&opts)
447        }),
448        Format::Decimal(places) => {
449            process_numeric_value(value, def_val, |n| float_value(n.round_decimal(places)))
450        }
451        Format::Float => process_numeric_value(value, def_val, float_value),
452        Format::Date => process_date_value(value, def_val, iso_fuzzy_to_date_string),
453        Format::DateTime => process_date_value(value, def_val, iso_fuzzy_to_datetime_string),
454        _ => Value::String(value.to_owned()),
455    }
456}
457
458fn process_truthy_value<F>(value: &str, def_val: Option<Value>, truthy_fn: F) -> Value
459where
460    F: Fn(&str, bool) -> Option<bool>,
461{
462    if let Some(is_true) = truthy_fn(value, false) {
463        Value::Bool(is_true)
464    } else {
465        def_val.unwrap_or(Value::Null)
466    }
467}
468
469fn process_numeric_value<F>(value: &str, def_val: Option<Value>, numeric_fn: F) -> Value
470where
471    F: Fn(f64) -> Value,
472{
473    if let Some(n) = value.to_first_number::<f64>() {
474        numeric_fn(n)
475    } else {
476        def_val.unwrap_or(Value::Null)
477    }
478}
479
480fn process_date_value<F>(value: &str, def_val: Option<Value>, date_fn: F) -> Value
481where
482    F: Fn(&str) -> Option<String>,
483{
484    if let Some(date_str) = date_fn(value) {
485        string_value(&date_str)
486    } else {
487        def_val.unwrap_or(Value::Null)
488    }
489}
490
491// Convert csv rows to value
492fn csv_row_result_to_values(
493    result: Result<StringRecord, csv::Error>,
494    opts: Arc<&RowOptionSet>,
495) -> Option<Vec<Value>> {
496    if let Ok(record) = result {
497        let mut row: Vec<Value> = vec![];
498        let mut ci: usize = 0;
499        for cell in record.into_iter() {
500            let new_cell = csv_cell_to_json_value(cell, opts.clone(), ci);
501            row.push(new_cell);
502            ci += 1;
503        }
504        return Some(row);
505    }
506    None
507}
508
509// convert CSV cell &str value to a polymorphic serde_json::VALUE
510fn csv_cell_to_json_value(cell: &str, opts: Arc<&RowOptionSet>, index: usize) -> Value {
511    let has_number = cell.to_first_number::<f64>().is_some();
512    // clean cell to check if it's numeric
513    let col = opts.column(index);
514    let (fmt, euro_num_mode) = if let Some(c) = col {
515        (c.format.clone(), c.decimal_comma)
516    } else {
517        (Format::Auto, opts.decimal_comma)
518    };
519    let num_cell = if has_number {
520        let euro_num_mode = uses_decimal_comma(cell, euro_num_mode);
521        if euro_num_mode {
522            cell.replace(",", ".").replace(",", ".")
523        } else {
524            cell.replace(",", "")
525        }
526    } else {
527        cell.to_owned()
528    };
529    let mut new_cell = Value::Null;
530    if num_cell.len() > 0 && num_cell.is_numeric() {
531        if let Ok(float_val) = serde_json::Number::from_str(&num_cell) {
532            match fmt {
533                Format::Integer => {
534                    if let Some(int_val) = Number::from_i128(float_val.as_i128().unwrap_or(0)) {
535                        new_cell = Value::Number(int_val);
536                    }
537                }
538                Format::Boolean => {
539                    // only 1.0 or more will evaluate as true
540                    new_cell = Value::Bool(float_val.as_f64().unwrap_or(0f64) >= 1.0);
541                }
542                _ => {
543                    new_cell = Value::Number(float_val);
544                }
545            }
546        }
547    } else if let Some(is_true) = cell.is_truthy_core(false) {
548        new_cell = Value::Bool(is_true);
549    } else {
550        new_cell = match fmt {
551            Format::Truthy => {
552                if let Some(is_true) = cell.is_truthy_standard(false) {
553                    Value::Bool(is_true)
554                } else {
555                    Value::Null
556                }
557            }
558            _ => Value::String(cell.to_string()),
559        };
560    }
561    new_cell
562}
563
564pub async fn read_workbook_sheet_info<'a>(
565    path_data: &PathData<'a>,
566) -> Result<IndexMap<String, usize>, GenericError> {
567    if let Ok(mut workbook) = open_workbook_auto(path_data.path()) {
568        let mut im: IndexMap<String, usize> = IndexMap::new();
569        for name in workbook.sheet_names() {
570            if let Ok(range) = workbook.worksheet_range(&name) {
571                im.insert(name, range.rows().count());
572            }
573        }
574        Ok(im)
575    } else {
576        Err(GenericError("cannot_open_workbook"))
577    }
578}
579
580#[cfg(test)]
581mod tests {
582    use super::*;
583    use crate::{helpers::*, Column};
584    use serde_json::json;
585    use std::path;
586
587    #[test]
588    fn test_direct_processing_xlsx() {
589        let sample_path = "data/sample-data-1.xlsx";
590
591        // instantiate the OptionSet with a sample path and a maximum row count of 1000 rows as the source file has 401 rows
592        // (although )the default max is 10,000)
593        let opts = OptionSet::new(sample_path).max_row_count(1_000);
594
595        let result = process_spreadsheet_direct(&opts);
596
597        // The source file should have 1 header row and 400 data rows
598        assert_eq!(result.unwrap().num_rows, 401);
599    }
600
601    #[test]
602    fn test_direct_processing_csv() {
603        let sample_path = "data/sample-data-1.csv";
604
605        // instantiate the OptionSet with a sample path and a maximum row count of 1000 rows as the source file has 401 rows
606        // (although )the default max is 10,000)
607        let opts = OptionSet::new(sample_path).max_row_count(1_000);
608
609        let result = process_spreadsheet_direct(&opts);
610
611        // The source file should have 1 header row and 400 data rows
612        assert_eq!(result.unwrap().num_rows, 401);
613    }
614
615    #[test]
616    fn test_multisheet_preview_ods() {
617        let sample_path = "data/sample-data-2.ods";
618
619        // instantiate the OptionSet with a sample path
620        // a maximum row count returned of 10 rows
621        // and read mode to *preview* to scan all sheets
622        // It should correctly calculate
623        let opts = OptionSet::new(sample_path)
624            .max_row_count(10)
625            .read_mode_preview();
626
627        let result = process_spreadsheet_direct(&opts);
628
629        // The source spreadsheet should have 2 sheets
630        let dataset = result.unwrap();
631        assert_eq!(dataset.sheets.len(), 2);
632        // The source spreadsheet should have 101 + 17 (= 118) populated rows including headers
633        assert_eq!(dataset.num_rows, 118);
634
635        // The first sheet's data should only output 10 rows (including the header)
636        assert_eq!(dataset.data.first_sheet().len(), 10);
637    }
638
639    #[test]
640    fn test_column_override_1() {
641        let sample_json = json!({
642          "sku": "CHAIR16",
643          "height": "112cm",
644          "width": "69cm",
645          "approved": "Y"
646        });
647
648        let rows = json_object_to_calamine_data(sample_json);
649
650        let cols = vec![
651            Column::new_format(Format::Text, Some(string_value(""))),
652            Column::new_format(Format::Float, Some(float_value(95.0))),
653            Column::new_format(Format::Float, Some(float_value(65.0))),
654            Column::new_format(Format::Truthy, Some(bool_value(false))),
655        ];
656
657        // The first sheet's data should only output 10 rows (including the header)
658        let opts = &RowOptionSet::simple(&cols);
659        let result = workbook_row_to_values(&rows, opts);
660        // the second column be cast to 112.0
661        assert_eq!(result.get(1).unwrap(), 112.0);
662        // the third column be cast to 69.0
663        assert_eq!(result.get(2).unwrap(), 69.0);
664        // the fourth column be cast to boolean
665        assert_eq!(result.get(3).unwrap(), true);
666    }
667
668    #[test]
669    fn test_column_override_2() {
670        let sample_json = json!({
671          "name": "Sophia",
672          "dob": "2001-9-23",
673          "weight": "62kg",
674          "result": "GOOD"
675        });
676
677        let rows = json_object_to_calamine_data(sample_json);
678
679        let cols = vec![
680            Column::new_format(Format::Text, None),
681            Column::new_format(Format::Date, None),
682            Column::new_format(Format::Float, None),
683            // the fourth column be cast to boolean
684            Column::new_format(
685                Format::truthy_custom("good", "bad"),
686                Some(bool_value(false)),
687            ),
688        ];
689
690        // The first sheet's data should only output 10 rows (including the header)
691        let opts = &RowOptionSet::simple(&cols);
692        let result = workbook_row_to_values(&rows, opts);
693        assert_eq!(result.get(1).unwrap(), "2001-09-23");
694        assert_eq!(result.get(2).unwrap(), 62.0);
695        assert_eq!(result.get(3).unwrap(), true);
696    }
697
698    #[tokio::test]
699    async fn test_read_workbook_info() {
700        let sample_path = "data/sample-data-1.xlsx";
701        let path_data = PathData::new(path::Path::new(sample_path));
702        let info = read_workbook_sheet_info(&path_data).await;
703        assert!(info.is_ok());
704    }
705
706    #[tokio::test]
707    async fn test_large_csv_file() {
708        let sample_path = "data/large-datasheet.csv";
709        let max_rows = 100_000;
710        let opts = OptionSet::new(sample_path).max_row_count(max_rows);
711        let result = process_spreadsheet_core(&opts, None, None).await;
712        if let Ok(data) = result.clone() {
713            assert_eq!(data.data.first_sheet().len(), max_rows as usize);
714        } else {
715            panic!("Failed to process large CSV file");
716        }
717        assert!(result.is_ok());
718    }
719
720    #[tokio::test]
721    async fn test_medium_excel_file() {
722        let sample_path = "data/medium-spreadsheet-50_000.xlsx";
723        let max_rows = 5_000;
724        let opts = OptionSet::new(sample_path).max_row_count(max_rows);
725        let result = process_spreadsheet_core(&opts, None, None).await;
726        if let Ok(data) = result.clone() {
727            assert_eq!(data.data.first_sheet().len(), max_rows as usize);
728        } else {
729            panic!("Failed to process large Excel file");
730        }
731        assert!(result.is_ok());
732    }
733}
spreadsheet_to_json/reader.rs

spreadsheet_to_json/
reader.rs
