JSON to Excel: Convert JSON to XLSX in Python and Excel

Convert JSON to Excel with pandas (Python)

pandas reads JSON and writes .xlsx in 3 lines. It is the fastest Python path to a fully-formed Excel file: pd.read_json() parses the JSON and.to_excel() writes the spreadsheet. The only extra dependency is openpyxl, which pandas uses as the .xlsx engine under the hood. Without it you will get a ModuleNotFoundError at write time even though pandas itself is installed.

import pandas as pd

# Simple flat JSON
data = [
    {"name": "Alice", "age": 30, "city": "London"},
    {"name": "Bob", "age": 25, "city": "Paris"},
]
df = pd.DataFrame(data)
df.to_excel("output.xlsx", index=False)

# From a JSON file
df = pd.read_json("data.json")
df.to_excel("output.xlsx", index=False)

# From a JSON string
import json
json_str = '[{"name":"Alice","age":30},{"name":"Bob","age":25}]'
df = pd.read_json(json_str)
df.to_excel("output.xlsx", index=False)

Install both dependencies in one command: pip install pandas openpyxl. The index=False argument prevents pandas from writing the row number (0, 1, 2 …) as the first column in the spreadsheet. Omit it only when you want the index preserved. See the JSON to pandas DataFrame guide for more detail on loading options.

Flatten nested JSON before converting

Nested JSON must be flattened before it can map cleanly to spreadsheet columns. When you call pd.DataFrame(data) on nested objects, each nested dict lands in a single cell as a Python dictionary — not the individual values. Use pd.json_normalize() instead. It walks nested keys and creates dot-separated column names like user.name and user.age, which become clean column headers in the Excel file. This is the same operation as flattening nested JSON in Python but done in one pandas call.

import pandas as pd

data = [
    {"user": {"name": "Alice", "age": 30}, "score": 95},
    {"user": {"name": "Bob", "age": 25}, "score": 88},
]

# json_normalize flattens nested keys with dot notation
df = pd.json_normalize(data)
# Columns: user.name, user.age, score
df.to_excel("output.xlsx", index=False)

For deeply nested JSON or arrays within objects, use the record_path and meta parameters. record_path specifies the nested array to expand (one row per element), and meta lists the parent-level fields to carry into every row:

import pandas as pd

orders = [
    {"orderId": "A1", "items": [{"sku": "X1", "qty": 2}, {"sku": "X2", "qty": 1}]},
    {"orderId": "A2", "items": [{"sku": "X3", "qty": 5}]},
]

df = pd.json_normalize(orders, record_path="items", meta=["orderId"])
# Columns: sku, qty, orderId (one row per item)
df.to_excel("output.xlsx", index=False)

Convert JSON to Excel without Python (Excel Power Query)

Excel 2016 and later can import JSON natively using Power Query — no code, no installation required. Power Query auto-detects the JSON structure, creates a table, and lets you expand nested fields by clicking column headers. It also stores the import configuration as a reusable query, so you can refresh the data whenever the source JSON file changes. This is the best no-code option for analysts who regularly work with the same JSON structure.

1. Open Excel and go to the Data tab.
2. Click Get Data → From File → From JSON.
3. Select your .json file and click Import.
4. In the Power Query Editor, click To Table in the top-left ribbon.
5. Expand columns by clicking the expand icon (double arrows) on column headers to unnest objects.
6. Click Close & Load to import the data into the spreadsheet.

Power Query works well for flat JSON arrays. Nested objects need manual expansion steps — click the expand icon on each nested column and choose which sub-fields to include. For deeply nested or inconsistent JSON, the Python approach with pd.json_normalize() gives more control.

Write JSON to Excel with openpyxl (no pandas)

openpyxl writes .xlsx files without pandas — useful when you want a lightweight script, need fine-grained control over cell formatting, or are working in an environment where pandas is not available. The approach is explicit: load JSON, create a workbook, write a header row, then iterate through the data and append each record as a row. openpyxl also supports cell styles, formulas, and multiple sheets if you need them.

import json
from openpyxl import Workbook

with open("data.json") as f:
    data = json.load(f)

wb = Workbook()
ws = wb.active

# Write header row
headers = list(data[0].keys())
ws.append(headers)

# Write data rows
for row in data:
    ws.append([row.get(h, "") for h in headers])

wb.save("output.xlsx")

Install with pip install openpyxl. The row.get(h, "") call handles missing keys gracefully — if a record does not contain a particular field, an empty string is written instead of raising a KeyError. For .xls (legacy Excel 97-2003 format) use the xlwt library instead; openpyxl only writes .xlsx.

Convert JSON to Excel in Node.js

In a JavaScript or Node.js environment, the xlsx package (SheetJS) is the standard choice for Excel output. With 8M+ weekly npm downloads, SheetJS is the most widely used JavaScript Excel library and supports reading and writing .xlsx, .xls, .csv, and other spreadsheet formats. The XLSX.utils.json_to_sheet() function converts an array of objects directly to a worksheet, inferring column headers from the object keys.

const XLSX = require('xlsx')

const data = [
  { name: 'Alice', age: 30, city: 'London' },
  { name: 'Bob', age: 25, city: 'Paris' },
]

const worksheet = XLSX.utils.json_to_sheet(data)
const workbook = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1')
XLSX.writeFile(workbook, 'output.xlsx')

Install with npm install xlsx. To add a second sheet, call XLSX.utils.book_append_sheet(workbook, anotherWorksheet, 'Sheet2') before XLSX.writeFile(). SheetJS also runs in the browser: XLSX.writeFile() triggers a file download when called client-side.

Key facts about JSON to Excel conversion

Frequently asked questions

Can Excel open JSON files directly?

Excel cannot open .json files by double-clicking — it opens them as plain text. To import JSON into Excel properly, use Power Query (Excel 2016+): Data → Get Data → From File → From JSON. Power Query parses the JSON structure and lets you expand nested fields into columns. Alternatively, convert the JSON to CSV first and open the CSV, or use a Python script with pandas to write the .xlsx file directly.

How do I convert JSON to Excel in Python?

Use pandas: import pandas as pd; df = pd.read_json('data.json'); df.to_excel('output.xlsx', index=False). This requires pandas and openpyxl (pip install pandas openpyxl). For nested JSON, use pd.json_normalize(data) instead of pd.DataFrame(data) to flatten nested fields into dot-separated column names. For a pure-Python approach without pandas, use the openpyxl library to write rows manually.

How do I import JSON into Excel using Power Query?

In Excel 2016 or later: go to Data tab → Get Data → From File → From JSON, then select your .jsonfile. Power Query Editor opens. Click "To Table" in the ribbon, then expand columns using the expand icon on column headers to unnest objects. Click "Close & Load" to import the data as an Excel table. Power Query stores the import steps so you can refresh the data when the JSON file changes.

How do I flatten nested JSON before converting to Excel?

Use pd.json_normalize() in pandas: df = pd.json_normalize(data). This flattens one level of nesting, creating column names with dot notation (e.g., user.name, user.age). For deeper nesting, pass the record_path parameter to specify the array to expand, and meta to include parent-level fields. For example: pd.json_normalize(data, record_path='items', meta=['orderId']) expands an items array while keeping orderId in each row.

What is the best way to convert large JSON files to Excel?

For files under 100 MB, pandas handles them in memory: pd.read_json('large.json'). For larger files, use chunked reading: pd.read_json('large.json', lines=True, chunksize=10000) processes line-delimited JSON in chunks. Excel itself has a row limit of 1,048,576 rows — JSON with more records than that must be split across multiple sheets or files. For very large datasets, consider converting to CSV and using Excel's CSV import instead.

How do I convert JSON to Excel without Python?

Three options without Python: (1) Excel Power Query (Excel 2016+): Data → Get Data → From File → From JSON — handles flat and nested JSON without code. (2) Online converters: paste JSON and download .xlsx — suitable for small files. (3) LibreOffice Calc: supports JSON import through macros. For repeated conversion of the same structure, Power Query is the most robust no-code option because it saves the import configuration as a query you can refresh.