Why Convert JSON to Excel?

Excel is widely used for data analysis and sharing. Converting JSON to Excel makes data accessible to non-technical users.

Online Converters

Big JSON Viewer

Upload JSON file

Click "Export to Excel"

Download XLSX file

Other Tools

ConvertCSV.com
JSON-to-Excel.com
Code Beautify

Python with Pandas

Simple Conversion

import pandas as pd
import json

# Load JSON
with open('data.json') as f:
    data = json.load(f)

# Convert to DataFrame
df = pd.DataFrame(data)

# Save to Excel
df.to_excel('output.xlsx', index=False)

Nested JSON

import pandas as pd

# Flatten nested JSON
df = pd.json_normalize(data, sep='_')
df.to_excel('output.xlsx', index=False)

Multiple Sheets

with pd.ExcelWriter('output.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Users', index=False)
    df2.to_excel(writer, sheet_name='Orders', index=False)

JavaScript/Node.js

Using xlsx Library

const XLSX = require('xlsx');
const fs = require('fs');

// Read JSON
const data = JSON.parse(fs.readFileSync('data.json'));

// Create worksheet
const ws = XLSX.utils.json_to_sheet(data);

// Create workbook
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');

// Write file
XLSX.writeFile(wb, 'output.xlsx');

Command Line

Using jq and csvkit

# JSON to CSV
jq -r '(.[0] | keys_unsorted) as $keys | $keys, map([.[ $keys[] ]])[] | @csv' data.json > output.csv

# CSV to Excel (requires csvkit)
csvformat output.csv > output.xlsx

Handling Complex JSON

Arrays of Objects (Best Format)

[
  {"name": "Alice", "age": 30},
  {"name": "Bob", "age": 25}
]

Nested Objects

# Flatten before converting
df = pd.json_normalize(data, sep='.')

Excel Formatting

Python with openpyxl

from openpyxl import load_workbook
from openpyxl.styles import Font

# Convert to Excel
df.to_excel('output.xlsx', index=False)

# Add formatting
wb = load_workbook('output.xlsx')
ws = wb.active

# Bold headers
for cell in ws[1]:
    cell.font = Font(bold=True)

wb.save('output.xlsx')

Production Automation

Automated Daily Reports

import pandas as pd
import requests
from datetime import datetime

# Fetch from API
response = requests.get('https://api.example.com/sales')
data = response.json()

# Convert to DataFrame
df = pd.DataFrame(data)

# Add Excel formatting
with pd.ExcelWriter('daily_sales.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Sales', index=False)

    # Get workbook
    workbook = writer.book
    worksheet = writer.sheets['Sales']

    # Format headers
    from openpyxl.styles import Font, PatternFill
    for cell in worksheet[1]:
        cell.font = Font(bold=True, color="FFFFFF")
        cell.fill = PatternFill("solid", fgColor="4472C4")

Scheduled Exports with Cron

# Run daily at 6 AM

0 6 * /usr/bin/python3 /scripts/export_to_excel.py

Advanced Excel Features

Adding Formulas

import pandas as pd
from openpyxl import load_workbook

# Create Excel file
df.to_excel('sales.xlsx', index=False)

# Add formulas
wb = load_workbook('sales.xlsx')
ws = wb.active

# Add total row
last_row = ws.max_row + 1
ws[f'A{last_row}'] = 'Total'
ws[f'B{last_row}'] = f'=SUM(B2:B{last_row-1})'

wb.save('sales.xlsx')

Data Validation

from openpyxl.worksheet.datavalidation import DataValidation

# Add dropdown list
dv = DataValidation(type="list", formula1='"Approved,Pending,Rejected"')
ws.add_data_validation(dv)
dv.add(f'D2:D{ws.max_row}')

Charts and Visualization

from openpyxl.chart import BarChart, Reference

# Create chart
chart = BarChart()
chart.title = "Sales by Region"
data = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
cats = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "E5")

Real-World Business Scenarios

E-commerce Order Export

import pandas as pd
import json

# Complex nested JSON from Shopify API
with open('orders.json') as f:
    orders = json.load(f)

# Flatten nested structure
df = pd.json_normalize(
    orders,
    record_path=['line_items'],
    meta=['id', 'order_number', 'created_at', 'total_price',
          ['customer', 'email'], ['customer', 'name']],
    meta_prefix='order_'
)

# Format currency  
df['price'] = df['price'].astype(float).map(lambda x: f'$\{x:,.2f}')
df['order_total_price'] = df['order_total_price'].astype(float).map(lambda x: f'$\{x:,.2f}')

# Format dates
df['order_created_at'] = pd.to_datetime(df['order_created_at']).dt.strftime('%Y-%m-%d %H:%M')

df.to_excel('shopify_orders.xlsx', index=False)

Analytics Dashboard Export

with pd.ExcelWriter('analytics_dashboard.xlsx', engine='xlsxwriter') as writer:
    # Multiple sheets
    users_df.to_excel(writer, sheet_name='Users', index=False)
    sessions_df.to_excel(writer, sheet_name='Sessions', index=False)
    revenue_df.to_excel(writer, sheet_name='Revenue', index=False)

    # Get workbook for formatting
    workbook = writer.book

    # Add summary sheet with formulas
    summary = workbook.add_worksheet('Summary')
    summary.write('A1', 'Total Users')
    summary.write('B1', "=COUNTA(Users!A:A)-1")
    summary.write('A2', 'Total Revenue')
    summary.write('B2', "=SUM(Revenue!C:C)")

Performance Comparison

Method Benchmarks (10,000 records)

|--------|------|--------|----------------|

| pandas | 0.8s | 45MB | Basic |

| xlsx (JS) | 1.2s | 62MB | Basic |

| openpyxl | 2.1s | 78MB | Full |

| xlsxwriter | 1.5s | 55MB | Full |

Large File Optimization

# For files with 100K+ rows
import pandas as pd

# Use xlsxwriter engine (faster for large files)
with pd.ExcelWriter('large.xlsx', engine='xlsxwriter',
                    engine_kwargs={'options': {'constant_memory': True}}) as writer:
    df.to_excel(writer, index=False)

Data Type Handling

Dates and Times

import pandas as pd

# JSON dates as ISO strings
data = [
    {"date": "2026-01-15T10:30:00Z", "value": 100},
    {"date": "2026-01-16T14:20:00Z", "value": 150}
]

df = pd.DataFrame(data)

# Convert to datetime
df['date'] = pd.to_datetime(df['date'])

# Excel will show proper date format
df.to_excel('dates.xlsx', index=False)

Currency and Percentages

from openpyxl import load_workbook
from openpyxl.styles import numbers

df.to_excel('financial.xlsx', index=False)

wb = load_workbook('financial.xlsx')
ws = wb.active

# Format as currency
for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=2, max_col=2):
    for cell in row:
        cell.number_format = '$#,##0.00'

# Format as percentage
for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=3, max_col=3):
    for cell in row:
        cell.number_format = '0.00%'

wb.save('financial.xlsx')

Best Practices

Flatten nested JSON before conversion using pd.json_normalize()

Handle missing values with .fillna('') or .dropna()

Validate JSON structure before processing

Use meaningful column names with .rename(columns={})

Consider file size limits - Excel max 1,048,576 rows

Format data types appropriately (dates, currency, percentages)

Add Excel formulas for dynamic calculations

Use multiple sheets to organize related data

Enable constant_memory for large files to reduce RAM usage

Test with sample data before full production runs

Common Issues and Solutions

Mixed Data Types

# Convert all to strings if needed
df = df.astype(str)

# Or convert specific columns
df['id'] = df['id'].astype(str)
df['amount'] = pd.to_numeric(df['amount'], errors='coerce')

Unicode Characters

# Ensure proper encoding
df.to_excel('output.xlsx', index=False, encoding='utf-8')

Large Files (100MB+)

Use chunking:

import json
import pandas as pd

# Stream large JSON
chunks = []
with open('large.json') as f:
    for line in f:
        chunks.append(json.loads(line))
        if len(chunks) >= 10000:
            df = pd.DataFrame(chunks)
            # Process chunk
            chunks = []

Array Fields

# Join arrays as comma-separated strings
df['tags'] = df['tags'].apply(lambda x: ', '.join(x) if isinstance(x, list) else x)

API to Excel Pipeline

import requests
import pandas as pd
from datetime import datetime

def export_api_to_excel(api_url, excel_path):
    # Fetch paginated API data
    all_data = []
    page = 1

    while True:
        response = requests.get(f"{api_url}?page={page}&limit=100")
        data = response.json()

        if not data['results']:
            break

        all_data.extend(data['results'])
        page += 1

    # Convert to Excel
    df = pd.DataFrame(all_data)
    df.to_excel(excel_path, index=False)
    print(f"Exported {len(all_data)} records to {excel_path}")

# Use it
export_api_to_excel('https://api.example.com/products', 'products.xlsx')

Conclusion

For quick one-off conversions, use Big JSON Viewer online tool. For production automation, Python pandas with openpyxl or xlsxwriter provides full control over Excel features including formulas, formatting, and charts. For Node.js environments, the xlsx library offers excellent performance and cross-platform compatibility.

Key takeaways:

Use pd.json_normalize() for nested JSON
Add Excel formulas and formatting for professional reports
Enable constant_memory for large files
Handle data types explicitly (dates, currency, percentages)
Test with sample data before automating production pipelines

Michael Rodriguez