Excel to JSON Converter

Excel to JSON with real data types

This excel to json converter reads a spreadsheet file and writes each row as a JSON object whose keys come from the header row. It is meant for the moment a product list, price sheet or test matrix maintained in Excel has to become input for code: a fixture for unit tests, a seed file for a mock API, or a payload you post to an endpoint.

Unlike a copy-and-paste approach, the file is parsed cell by cell, so each value keeps the type Excel stored. Numbers stay numbers, TRUE and FALSE become JSON booleans, and date cells become ISO date strings instead of Excel serial numbers.

Example: a product sheet as a JSON array

Take a sheet named Products with a header row and two data rows. The price in the first row is displayed as $24.50 through a currency format, and the release dates are real Excel dates:

SKU    | Product     | Price  | Released   | In stock
A-100  | Desk Lamp   | $24.50 | 2024-03-15 | TRUE
B-220  | Monitor Arm | 89     | 2024-06-01 | FALSE

With the default options the converter returns:

[
  {
    "SKU": "A-100",
    "Product": "Desk Lamp",
    "Price": 24.5,
    "Released": "2024-03-15",
    "In stock": true
  },
  {
    "SKU": "B-220",
    "Product": "Monitor Arm",
    "Price": 89,
    "Released": "2024-06-01",
    "In stock": false
  }
]

Notice that the price is the stored number 24.5, not the formatted text. Currency symbols, thousands separators and decimal places applied by cell formatting are display settings, and the JSON gets the underlying value. For formula cells you get the result that was last calculated and saved in the file, not the formula itself.

How rows, headers and cells are mapped

• Header names. The first row supplies the keys. A blank header cell becomes column6 (using its position), and a repeated name gets a suffix, so two columns called Name become Name and Name_2. Keys with spaces, such as "In stock", are kept as written.
• Empty cells inside the used range become empty strings (""), so every object has the same set of keys. Completely blank rows are skipped.
• Dates. A date without a time part is written as YYYY-MM-DD. A cell that also holds a time becomes YYYY-MM-DDTHH:MM:SS, exactly the clock time shown in the sheet — it is not shifted to UTC or to your computer's time zone.
• Merged cells. Only the top-left cell of a merged range holds the value; the other rows of the merge are empty in the output.
• No header. Untick First row is header and each row becomes a plain array of cell values, which is handy when the sheet has no header or when you want to post-process the grid yourself.

Choosing sheets

The Sheet field accepts either a name or a 1-based number. Names are matched without regard to case, so staff finds a sheet called Staff, and 2 picks the second tab. If the name or number does not exist, the first sheet is used, so check the record count in the status bar when a result looks unexpected.

Tick Convert all sheets to export the whole workbook. The result is then an object keyed by sheet name, with one array per sheet:

{
  "Products": [ { "SKU": "A-100", ... } ],
  "Staff": [ { "Region": "EU", "Name": "Lena", "Name_2": "Berg" } ]
}

Formatting options

Indent sets 2, 3 or 4 spaces or a tab. Sort keys orders the keys of every object alphabetically, which gives stable diffs when the column order in the sheet changes between versions. The order of rows is never changed.

Tips before you convert

• Codes with leading zeros, such as ZIP codes or part numbers, only survive if the cells are stored as text in Excel. A number cell holding 01234 is simply 1234.
• Keep one table per sheet with the header in row 1. Titles, notes or totals above or below the table end up as extra rows.
• If the JSON is headed for a typed code base, paste it into JSON to TypeScript to generate interfaces, or open it in the JSON viewer to check the structure.
• Need the reverse direction later? JSON to Excel builds a workbook from an array of objects.

Privacy

The spreadsheet is parsed by a JavaScript library running in your browser, so the file is not uploaded to our server. Excel tools have no Load URL option; only Save & Share stores content on the server, and only when you choose to use it.

Convert Excel to JSON in Python

With openpyxl, read the sheet with iter_rows(values_only=True), take the first row as the keys and zip every following row into a dictionary. The part that fails for most people is dates: openpyxl returns date cells as datetime objects, and json.dumps stops with Object of type datetime is not JSON serializable. Give it a default function that turns them into ISO strings. data_only=True makes formula cells return their saved result instead of the formula. The example creates its own sample file so that it runs as shown.

# pip install openpyxl
import datetime
import json

from openpyxl import Workbook, load_workbook

wb = Workbook()                       # build a tiny sample file
wb.active.append(["sku", "price", "released"])
wb.active.append(["A-100", 24.5, datetime.date(2024, 3, 15)])
wb.active.append(["B-220", 89, datetime.date(2024, 6, 1)])
wb.save("sample.xlsx")

ws = load_workbook("sample.xlsx", data_only=True).active
header, *body = ws.iter_rows(values_only=True)
records = [dict(zip(header, row)) for row in body]

def iso_date(value):                  # date cells come back as datetime
    return value.date().isoformat()

print(json.dumps(records, indent=2, default=iso_date))

For a one-off spreadsheet, the converter above does the same job without a script.