spreadsheet-to-json

Convert Spreadsheets and CSV files to jSON

Battle-tested with spreadsheets under 10MB; recent releases handle much larger files. See version history below.

This library crate provides the core functions to convert common spreadsheet and CSV files into JSON or JSONL (JSON Lines) either directly or asynchronously.

It relies on the Calamine and CSV library crates to process files, the tokio crate for asynchronous operations and naturally serde and serde_json serialization libraries.

It supports the following formats:

• Excel 2007+ Workbook (.xlsx)
• Excel 2007+ Macro-Enabled Workbook (.xlsm) -- read as plain data; macros are ignored
• Excel 2007+ Binary (.xlsb)
• Excel 97-2004 Legacy (.xls)
• OpenDocument Spreadsheets (.ods) compatible with LibreOffice
• CSV: comma separated values (.csv)
• TSV: tab-separated values (.tsv)

Features

• Blazingly fast: It can import 10,000 rows in less than 0.4 seconds.
• Can export to standard JSON or to JSON lines when writing large files
• Formula cells are read as calculated values
• Can identify and convert both Excel's 1900 datetime format and standard ISO format as used in OpenDocument Spreadsheet
• Can identify numeric fields formatted as text and convert them to integers or floats.
• Can identify truthy text or number cells and convert them to booleans
• Can save large files asynchronously
• Can reshape output beyond flat key/value pairs -- nested objects, dynamic-keyed groups, or arrays of objects -- via KeySegment (see Nested output below), buildable either in Rust or from a plain JSON payload for non-Rust client crates
• Can drop null-valued keys from output recursively via RowOptionSet::omit_null_values, instead of emitting "key": null

Core Options

Options can be set by instantiating OptionSet::new("path/to/spreadsheet.xlsx") with chained setter methods:

• .max_row_count(max: u32): overrides the default max row count of 10,000. Use this is direct mode or to return only the first n rows.
• .header_row(index: u8) overrides the default header row index of 0, useful for spreadsheets with a title and notes on top
• .omit_header() omit the header altogether and assign default A1-style keys or column numbers.
• .sheet_index(index: u32) zero-based sheer index. Any value over zero will override the specified sheet name.
• .sheet_name(name: &str) case-insensitive sheet name. It will match the first sheet with name after stripping spaces and punctuation.
• .read_mode_async() Defer processing of rows with a callback in the second argument in render_spreadsheet_async()
• .json_lines() Output will be rendered one json object per row.
• field_name_mode(system: &str, override_header: bool): use either A1 (a, b, ... z, aa, ab, ...) or C-prefixed zero-padded numbers (c01, c02, ...) for the default column key notation where headers are either unavailable or suppressed via the override_header flag. The padding width scales with the sheet's column count, so keys keep sorting correctly regardless of width. (Exact width thresholds: see 0.1.3.)
• override_headers(keys: &[&str]) Override matched or automatic column keys. More advanced column options will be detailed soon.
• override_columns(cols: &[Value]) Lets you override column settings, represented here as an array of serde_json::Value key/value objects, where :
  • key: overrides the header key,
  • format: string | integer | float | d1 ... d8 | datetime | date | boolean | truthy | truthy:true_key,false_key
    • d1 to d8 will round floats to the specified max. number of decimal places.
    • boolean will cast integer, floats >= 1 as true as well as the strings '1' and 'true', with < 1 and the strings 0 and false being false. If unmatched or empty the field value will be null.
    • truthy will cast common English-like abbreviations such as Y, Yes as true and N or No false
    • truthy:true_key,false_key lets you cast custom strings to true or false. If unmatched the field value will be null.
  • default: overrides the default value for empty cells.

(Why C-style keys are c-prefixed rather than bare zero-padded numbers: see 0.1.3 in Version History.)

Nested output

Column.key: Option<KeySegment> is where a matched column's cell value actually lands in the output row. A plain rename (Column::from_source_key_with_format, or override_columns' "key": "new_name") produces KeySegment::Simple -- the flat, one-column-to-one-field case this crate has always supported. The rest of the KeySegment tree lets a single column (or several columns sharing a container) land somewhere more structured instead:

• Excluded -- drop the column from output entirely.
• Simple(key) -- flat leaf, insert directly under key (a plain rename produces this).
• Object(key, next) -- nest under key, continuing via next. Columns sharing a key merge into one nested object rather than each creating their own.
• Array(container, identifier, key_field, next) -- find or create an item in the container array whose key_field equals identifier, then continue via next inside that item.
• InnerObject(identifier, field, next) -- add a literal-valued field to the current item (no new nesting level), then continue via next in the same item -- flattens multiple discriminators onto one array item instead of nesting each one.
• PlainArray(container) -- push the column's own value directly into container as a bare scalar, no wrapping object. Blank cells are dropped rather than kept as positional gaps; an all-blank row still yields [], never an absent key.

(Implementation notes -- Arc vs Box, cross-column item matching, JSON round-tripping: see 0.4.0 in Version History.)

Building a KeySegment from JSON

KeySegment::from_json/Identifier::from_json are how any client crate -- a frontend UI assembling a JSON payload, a web API, or anything else that would rather not write Rust or touch calamine/csv directly -- reaches the full tree. A plain JSON string is shorthand for Simple (matching Column's own existing plain-string convention); anything else needs a tagged object with a "type" field:

Column::from_json calls this automatically for a "key" field that isn't a plain string, so override_columns already supports the full tree, not just flat renames.

{
  "source_key": "sales_2025",
  "key": {
    "type": "array",
    "container": "sales",
    "identifier": 2025,
    "key_field": "year",
    "next": { "type": "simple", "key": "amount" }
  }
}

See examples/key_segment_nesting.rs for five runnable demos (Object, PlainArray, Array + InnerObject, Excluded, and the JSON form above), each building a small CSV in memory and running it through process_spreadsheet_direct:

cargo run --example key_segment_nesting

Dropping null values

RowOptionSet::omit_null_values: bool recursively strips any key whose value is JSON null from output -- through nested objects and arrays alike -- rather than emitting "key": null. (Scope -- why empty strings and array elements are untouched: see 0.4.0.)

To do

More details of options to come.

Simple example:

  let opts = OptionsSet::new("path/to/spreadsheet.ods")
      .sheet_index(3)
      .read_mode_async()
      .override_headers(&["name", "height", "width", "weight", "hue", "price"]);

Core functions

• process_spreadsheet_direct(opts: &OptionSet): May be called in a synchronous context where you need to process results immediately.

• process_spreadsheet_async(opts: &OptionSet): Asynchronously processes files with a callback function to save each row.

Result set

• filename: Matched filename,
• extension: Matched extension
• sheet: Matched worksheet name and index
• sheets: List of available worksheet names
• keys: Assigned column keys
• num_rows: number of rows in the source file that have been successfully parsed
• data: Vector of dynamic objects (IndexMap<String, Value>) that can be easily translated into JSON or other common formats.
• out_ref: Optional output reference such as a generated file name, URL or database id.

If the file name and extension cannot be matched, because the file is unavailable or unsupported, the core functions will return a generic error.

Result Set methods

• to_json(): Converts to the result set to serde_json::Value that may be printed directly or written to a file.
• to_output_lines(json_lines: bool): Returns a vector of plain-text results with each data row as JSON on a new line
• rows(): Returns a vector of rendered JSON strings
• json_data(): Returns all data as as serde_json::Value::Array ready for conversion or post-processing.

Examples

The main implementation is my Spreadsheet to JSON CLI crate (spread-cli), which builds a text DSL for the common KeySegment shapes above on top of this crate's --keys-style column overrides.

Simple immediate jSON conversion

This function processes the spreadsheet file immediately.

use spreadsheet_to_json::*;

fn main() -> Result<(), GenericError> {
  let opts = OptionSet::new("path/to/spreadsheet.xlsx").sheet_index(1);
  let result = process_spreadsheet_direct(&opts);
  let json_value = match result {
    Err(msg_code) => json!({ "error": true, "key": msg_code.to_string() }),
    Ok(data_set) => data_set.to_json() // full result set
  };
  println!("{}", json_value);
  Ok(())
}

Preview multiple worksheets

You may preview all worksheets and limit the number of sample rows from each sheet.

use spreadsheet_to_json::*;

fn main() -> Result<(), GenericError> {
  // set the read mode to PreviewMultiple and limit output to the first 10 data rows
  let opts = OptionSet::new("path/to/spreadsheet-with-2-worksheets.xlsx")
    .read_mode_preview()
    .max_row_count(10)
    .json_lines();

  let result = process_spreadsheet_direct(&opts);
  // output each line
  for line in result.to_output_lines() {
    println!("{}", line);
  }
  Ok(())
}

Asynchronous parsing and saving to a database

This must be called in an async function with a callback to save rows in separate processes.

use spreadsheet_to_json::*;
use spreadsheet_to_json::tokio;
use indexmap::IndexMap;
use serde_json::Value;

#[tokio::main]
async fn main() -> Result<(), GenericError> {
  let opts = OptionSet::new("path/to/spreadsheet.xlsx").read_mode_async();

  let callback = move |row: IndexMap<String, Value>| -> Result<(), GenericError> {
    save_data_row(row, &connection, dataset_id)
  };

  let result = process_spreadsheet_async(&opts, Box::new(callback), None).await;
  // A successful result set will have an output reference rather than the actual data
  let result_set = match result {
      Err(msg_code) => json!({ "error": true, "key": msg_code.to_string() }),
      Ok(data_set) => data_set.to_json() 
  };
  println!("{}", result_set);
  Ok(())
}

// Save function called in a closure for each row with a database connection and data_id from the outer scope
fn save_data_row(row: IndexMap<String, Value>, connection: &PgConnection, data_id: u32) -> Result<(), GenericError> {
    let mut row_struct = CustomTableStruct {
    id: None,
    data_id: data_id, // or whatever ID setting logic you have
    field1: None,
    field2: None,
    // ... set other fields to None or default values
  };
  
  for (key, value) in row {
    match key.as_str() {
        "field1" => {
            if let Value::String(s) = value {
                row_struct.field1 = Some(s.clone());
            }
        },
        "field2" => {
            if let Value::Number(n) = value {
                if let Some(i) = n.as_i64() {
                    row_struct.field2 = Some(i as i32);
                }
            }
        },
        // Add other field mappings here
        _ => {} // Ignore unknown keys
    }
  }
  diesel::insert_into("data_rows")
  .values(&row_struct)
  .execute(connection)
  .map_err(|_| GenericError("database_error"))?;
  Ok(())
}

Version History

• 0.1.2 the core public functions with Result return types now use a GenericError error type
• 0.1.3 Refined A1 and C01 column name styles and added result output as vectors of lines for interoperability with CLI utilities and debugging. C-style keys are prefixed with c rather than left as bare zero-padded numbers ("001", "002", ...) for two reasons: zero-padding alone only guarantees correct sort order as plain strings, and a bare numeric-looking key is easy to mistake for an array index (and some languages treat numeric-looking string keys specially). Positional access, when actually wanted, is a one-liner in most languages regardless of key naming -- e.g. Object.values(row) in JavaScript. The zero-padding width scales with the sheet's column count so keys keep sorting correctly at any width: c01..c99 under 100 columns, c001..c999 from 100 up to 1,000, c0001..c9999 from 1,000 up to 10,000.
• 0.1.4 Added support for the Excel Binary format (.xlsb)
• 0.1.5 Added two new core functions process_spreadsheet_direct() for direct row processing in a synchronous context and process_spreadsheet_async() in an asynchronous context with a callback. If you need to process a spreadsheet directly in an async function
• 0.1.6 Deprecated public function beginning with render (render_spreadsheet_direct() has become. You should use process_spreadsheet_immediate() for immediate processing of spreadsheets in an async context). Ensured the header row does not appear as the first data row in spreadsheets.
• 0.1.7 Added support for multiple worksheets in preview mode and refined output options
• 0.1.10 Reviewed date-time parsing options for Excel and OpenDocument spreadsheets. Reorganised cell post-processors by detected data-type.
• 0.2.0 Bumped calamine to 0.36.0. Switched is-truthy, alphanumeric and simple-string-patterns from local path dependencies to their published crates.io versions — is-truthy gained a TruthyRuleSet builder (options()/true_options()/false_options(), replacing the old TruthyOption vector plumbing). Fixed the CSV reader silently ignoring its own default row cap (DEFAULT_MAX_ROWS) when no explicit .max_row_count() was set — large uploads could load unbounded into memory. Fixed a panic in Column::from_json on a non-string format field. Assorted performance fixes: redundant Column/Format clones per cell, a fresh Arc allocated per CSV row instead of once per file, a double clone in ResultSet::rows(). Fixed a panic in read_workbook_sheet_info on an unreadable individual sheet.
• 0.2.1 Bumped is-truthy to 0.1.2, fixing CSV cells like "SKU001" or a date starting "01/..." being silently coerced to the boolean true/false just because they contained an embedded 0 or 1. Added Column::source_key (and Column::from_source_key_with_format()): a column override can now be matched by its natural, auto-detected key wherever that column actually is, instead of strictly by position — see resolve_columns()/natural_column_keys() in headers.rs. Overrides with no source_key still apply positionally, unchanged. Fixed Format::Integer on a CSV cell with a decimal value (e.g. "58.2") silently producing 0 instead of the truncated integer.
• 0.2.2 Fixed a per-column Format::Date/Format::DateTime override having no effect at all on real (non-string) datetime cells in xlsx/ods — only the row-wide --date-only-style default was ever consulted for Data::DateTime/Data::DateTimeIso cells, so casting a single datetime column to date-only silently did nothing; see resolve_date_only() in reader.rs. Fixed is_not_header_row misclassifying the header row as real data (leaking it into the output as a bogus extra row) whenever any column had a non-Auto Format — it used to compare the row's already-formatted values against the header text, and formatting the header row's own text (e.g. through a date or decimal parse) commonly turns it into null or something else that no longer matches; it now compares raw, un-coerced cell text instead. Both bugs affected the xlsx/ods path only, not CSV/TSV.
• 0.2.3 Added .xlsm (macro-enabled Excel workbook) support. .xlsm uses the exact same OOXML container as .xlsx and calamine has always read both through its Xlsx reader, but this crate's own Extension enum only recognised the .xlsx extension before.
• 0.3.0 Breaking: Column.date_only: bool is now Column.datetime_mode: DateTimeMode, and RowOptionSet's separate date_only/time_only booleans are now a single datetime_mode: DateTimeMode field -- RowOptionSet::new() and Column::from_source_key_with_format()/from_key_ref_with_format() take DateTimeMode instead of the old bool in their signatures. Added a DateTimeMode enum (Full, Simple, DateOnly, TimeOnly, HmOnly) as the single, unified representation for datetime rendering, replacing RowOptionSet's previous separate date_only/time_only booleans and the previously dead Column.date_only: bool field — that field was fully wired up for JSON/display output but never actually consulted by the rendering logic, so a per-column date-only override on a genuine (non-string) datetime cell silently did nothing; it's now Column.datetime_mode, correctly scoped to Format::Auto columns whose cells are already genuine datetimes (Data::DateTime/Data::DateTimeIso) — it never touches strings or numbers in the same column, unlike an explicit Format override, which forces date/time interpretation onto every cell type. Added Format::Time (ti/time), Format::Hm (hm), and Format::DateTimeSimple (ds/simple) alongside the existing Format::Date/Format::DateTime, giving explicit per-column control over precision: full ISO-8601 with milliseconds and a trailing Z (the default, for JS/JSON interop), the same without the milliseconds/Z, date-only, time-only with seconds, or hours:minutes only. Precedence is a column's own Format override, then its own datetime_mode (Auto columns only), then the row-wide RowOptionSet.datetime_mode default. Fixed genuinely time-only Excel cells — e.g. a cell formatted as plain hh:mm, which Excel actually stores as a full datetime serial with zero elapsed days, since it has no true time-only type — rendering with the meaningless placeholder date 1899-12-31 (the 1900 date system's epoch); a cell with no real date component is now auto-detected and rendered as a bare time whenever a full datetime wasn't explicitly requested. Fixed csv_cell_to_json_value having no Format::Date/Format::DateTime handling at all — a date-like CSV string starting with a number (e.g. "2023-06-15") could be misread by the numeric-extraction path before ever reaching fuzzy-datetime; all five date/time formats now apply uniformly across native Excel/ODS datetime cells, ISO datetime strings, and CSV/TSV text cells. Bumped fuzzy-datetime to 0.1.3, adding sliding-pivot two-digit-year expansion (e.g. 21-06-23 resolves relative to the current date rather than a fixed 50/50 century split) for ambiguous CSV/plain-text date cells, and switching its license to MIT.
• 0.3.1 ResultSet::new() takes two new required parameters, header_row_index: Option<usize> and body_start_index: usize, and ResultSet gains matching public fields. Fixed a gap where there was no way to learn which row indices a read actually used -- OptionSet.header_row/.data_row_index only ever reflect an explicit override and stay None whenever auto-detection resolved them instead, so a caller relying on those fields (e.g. spread-cli's --json metadata) saw null even when a header and data start were successfully detected and used. ResultSet.header_row_index/.body_start_index now carry the actually-resolved 0-based indices regardless of whether they came from an explicit override or detect_header_and_data_rows. Scoped to the single-sheet read paths (read_single_worksheet/read_csv_core); ResultSet::from_multiple() (the --preview/multi-sheet path) still reports None/0 for now, since a single pair of indices can't represent multiple sheets that may each resolve differently.
• 0.3.2 Format::Decimal(places) (d1-d8) now rounds native numeric cells (xlsx/ods floats and CSV), matching the precision it already applied to string-typed cells. Format::Date/Format::DateTime/Format::DateTimeSimple/Format::Time/Format::Hm on text cells and CSV values now accept /-separated dates in any order, not just --separated YMD (bumped fuzzy-datetime to 0.1.4 for related date-guessing fixes). Format::Time/Format::Hm are substantially more capable on text/CSV cells and native numeric cells: they now handle a bare time string with no date component ("11:39"), a decimal-disguised time (12.3 meaning 12:30, for both float cells and strings), and a trailing AM/PM marker ("2:30pm" -> 14:30; an hour outside 1-12 with an am/pm marker is treated as already-24-hour and the marker is dropped, e.g. "14:00pm" -> 14:00). Hours are intentionally not capped at 24, since a time column is just as often a duration (elapsed hours) as a time-of-day.
• 0.4.0 Added KeySegment (Excluded/Simple/Object/Array/InnerObject/PlainArray) as the general mechanism behind Column.key, letting a column's value land in a nested object, a dynamic-keyed group, or an array of objects, instead of only ever a flat key -- see Nested output above and examples/key_segment_nesting.rs. KeySegment::from_json/Identifier::from_json build the full tree from a plain JSON payload (a tagged object with a "type" field), so any client crate reaches every shape without writing Rust or touching calamine/csv directly; Column::from_json (and therefore override_columns) now uses this automatically whenever "key" isn't a plain string. Identifier renders back as whichever JSON type it actually is (a plain number stays a number, not "2015"), so a round-trip through JSON is lossless either way. Recursive KeySegment variants (Object/Array/InnerObject) wrap their continuation in Arc rather than Box, since Column (and its key) is cloned repeatedly during column resolution and an O(1) refcount bump matters there the same way it does for Format::Array's Arc<Format>. Two columns land in the same Array item only when their whole chain of Array/InnerObject identifiers agrees, not just the one matching segment. Added RowOptionSet::omit_null_values, recursively dropping any key whose value is JSON null from output (through nested objects/arrays too); it only ever targets genuine null -- an empty string is a different, deliberate value and is left alone -- and never removes array elements positionally, since that's PlainArray's own job. PlainArray drops blank cells (null, or CSV's native empty string) rather than keeping them as positional gaps, and stays [], not absent, when every matched cell in a row is blank.