About

Structural data interchange between JSON and XLSX formats fails silently when nested keys are flattened incorrectly or cell types are misinterpreted. A malformed conversion can corrupt numeric precision, collapse array hierarchies, or produce spreadsheets with misaligned columns. This tool implements bidirectional conversion between JSON and Excel XLSX (Office Open XML) with proper key-path flattening for nested objects. It parses sheet1 from the workbook, reconstructs shared string tables, and maps cell references using base-26 column indexing. The converter handles arrays of objects by extracting unique dot-notation paths as column headers. Hierarchical headers receive merged cells as a visual convenience. Limitation: only the first worksheet is processed. Circular references in JSON will cause a parse failure.

Formulas

Column index to Excel letter conversion uses base-26 arithmetic where A = 0:

colLetter(n) = if n < 26 then char(65 + n) else colLetter(n26 − 1) + char(65 + (n mod 26))

JSON key flattening follows a recursive dot-notation pattern:

flatten(obj, prefix) → for each key ∈ obj: path = prefix ⋅ key

Where obj = input JSON object, prefix = accumulated parent path (empty string at root), key = current property name, path = concatenated dot-notation string used as column header. Arrays are indexed numerically: prefix.key.0, prefix.key.1, etc.

Cell reference decoding extracts column letters and row numbers from references like B3. The column letter sequence is converted to a zero-based index:

colIndex = len−1∑i=0 (charCode(letter_i) − 64) × 26^{(len − 1 − i)} − 1

Reference Data

Feature	JSON → XLSX	XLSX → JSON
Nested Objects	Flattened with dot-notation headers	Reconstructed from dot-notation headers
Arrays of Objects	Each item becomes a row	Each row becomes an array item
Primitive Arrays	Comma-joined in single cell	Split back to array
Numeric Values	Stored as XLSX number type	Auto-detected, parsed as Number
Boolean Values	Stored as TRUE/FALSE strings	Reconverted to boolean
Null/Undefined	Empty cell	Omitted from output or NULL
Cell Merging	Parent keys span child columns	Merged cells read as single value
Max Columns	16384 (XFD)	16384
Max Rows	1048576	1048576
Shared Strings	Generated for text deduplication	Parsed from sharedStrings.xml
File Size Limit	10 MB	10 MB
Character Encoding	UTF-8	UTF-8
Date Handling	ISO 8601 string preserved	Serial number converted if detected
Worksheets	Single sheet output	First sheet only
Styles	Header row bold + background	Styles ignored on read
MIME Type	`application/vnd.openxmlformats-officedocument.spreadsheetml.sheet`	`application/json`

Frequently Asked Questions

The converter recursively flattens all nested objects using dot-notation paths. If one object has address.city and another lacks the address key entirely, the corresponding cells are left empty. Column headers are derived from the union of all unique paths across all records, so uneven nesting does not cause misalignment.

Mixed arrays are handled by type detection at each index. Primitive values (strings, numbers, booleans) are placed directly into cells. Object values within the same array are flattened with their index appended to the path. For example, items.0.name and items.1 (if primitive) coexist as separate columns. This preserves data integrity but may produce sparse columns.

No. JSON does not have a native date type. ISO 8601 date strings (e.g., 2024-01-15T10:30:00Z) are stored as plain text in the XLSX output. When converting XLSX to JSON, cells formatted as dates in Excel use serial numbers internally. The converter detects common date-formatted cells and converts serial numbers to ISO 8601 strings using the epoch offset of 25569 days (difference between Excel epoch 1900-01-01 and Unix epoch 1970-01-01).

Merged cells are a visual convenience only. When a JSON key like address has children city and zip, the header cell for address spans two columns above them. This improves readability in Excel. The merge metadata is stored in xl/worksheets/sheet1.xml as <mergeCell> elements. When reading back, the converter ignores merge information and relies solely on the first-row cell values as column identifiers.

The recursive flattening algorithm has no hard depth limit, but practical performance depends on the total number of unique paths generated. A JSON structure with depth 10 and branching factor 5 produces up to 9765625 potential paths. The converter caps output at 16384 columns (Excel's limit). For files exceeding 500 KB, processing moves to a Web Worker to prevent UI blocking.

Yes. The converter reads the OOXML standard structure. LibreOffice Calc and Google Sheets both export valid OOXML-compliant XLSX files. Minor variations exist in how shared strings and inline strings are stored. The parser handles both <si><t> shared string entries and <is><t> inline string entries. Style information (fonts, colors, borders) is ignored during read operations.