Converting Excel to JSON is a common data integration task: feeding APIs, configuring apps, or transforming reports into JavaScript-consumable formats. Python with pandas makes this process fast and flexible.
Installation
pip install pandas openpyxl
# openpyxl is required to read .xlsx files
Basic sheet to JSON conversion
import pandas as pd
import json
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
# orient='records' → list of objects [{col: val, ...}, ...]
json_str = df.to_json(orient='records', force_ascii=False, indent=2)
print(json_str[:300])
with open('data.json', 'w', encoding='utf-8') as f:
f.write(json_str)
# Alternative: via dict → json
records = df.to_dict(orient='records')
with open('data.json', 'w', encoding='utf-8') as f:
json.dump(records, f, ensure_ascii=False, indent=2)
print(f"Converted: {len(records)} records")
orient options in to_json()
import pandas as pd
df = pd.DataFrame({
'id': [1, 2, 3],
'name': ['Alice', 'Bob', 'Carol'],
'score': [95.5, 87.0, 92.3],
})
# records — list of objects (most common for APIs)
print(df.to_json(orient='records', indent=2))
# [{"id":1,"name":"Alice","score":95.5}, ...]
# index — dict with indices as keys
print(df.to_json(orient='index', indent=2))
# {"0":{"id":1,...}, "1":{"id":2,...}}
# values — only values as 2D array
print(df.to_json(orient='values'))
# [[1,"Alice",95.5],[2,"Bob",87.0],...]
# split — separate index, columns, and data
print(df.to_json(orient='split', indent=2))
# {"columns":["id","name","score"],"data":[[1,"Alice",95.5],...]}
Multiple sheets → multiple JSON objects
import pandas as pd
import json
# Read all sheets
sheets = pd.read_excel('report.xlsx', sheet_name=None) # None = all sheets
print(f"Sheets found: {list(sheets.keys())}")
# Option 1: one JSON file per sheet
for sheet_name, df in sheets.items():
records = df.to_dict(orient='records')
filename = f"{sheet_name.replace(' ', '_').lower()}.json"
with open(filename, 'w', encoding='utf-8') as f:
json.dump(records, f, ensure_ascii=False, indent=2)
print(f" {sheet_name}: {len(records)} records → {filename}")
# Option 2: all sheets in one JSON with sheet names as keys
result = {
name: df.to_dict(orient='records')
for name, df in sheets.items()
}
with open('report_complete.json', 'w', encoding='utf-8') as f:
json.dump(result, f, ensure_ascii=False, indent=2)
Clean data before export
import pandas as pd
import json
df = pd.read_excel('dirty_data.xlsx')
# 1. Drop fully empty rows
df = df.dropna(how='all')
# 2. Clean column names (no spaces, lowercase)
df.columns = (df.columns
.str.strip()
.str.lower()
.str.replace(' ', '_')
.str.replace('[^a-z0-9_]', '', regex=True))
# 3. Replace NaN with None (JSON null)
df = df.where(pd.notna(df), None)
# 4. Convert types
if 'date' in df.columns:
df['date'] = pd.to_datetime(df['date']).dt.strftime('%Y-%m-%d')
if 'price' in df.columns:
df['price'] = df['price'].round(2)
# 5. Export clean
records = df.to_dict(orient='records')
records_clean = [
{k: v for k, v in r.items() if v is not None}
for r in records
]
with open('clean_data.json', 'w', encoding='utf-8') as f:
json.dump(records_clean, f, ensure_ascii=False, indent=2)
print(f"Exported: {len(records_clean)} records")
Nested JSON (parent-child relationship)
import pandas as pd
import json
# Excel with relational data:
# customer_id | customer_name | order_id | product | qty | price
df = pd.read_excel('customers_orders.xlsx')
customers_json = []
for customer_id, group in df.groupby('customer_id'):
customer = {
'id': int(customer_id),
'name': group['customer_name'].iloc[0],
'orders': [
{
'order_id': int(row['order_id']),
'product': row['product'],
'qty': int(row['qty']),
'price': float(row['price']),
}
for _, row in group.iterrows()
],
'total_orders': len(group),
'total_spent': float(group['price'].sum()),
}
customers_json.append(customer)
with open('customers_with_orders.json', 'w', encoding='utf-8') as f:
json.dump(customers_json, f, ensure_ascii=False, indent=2)
print(f"Customers exported: {len(customers_json)}")
Filter and transform before export
import pandas as pd
import json
df = pd.read_excel('products.xlsx')
result = (
df
.query("active == True and price > 0")
[['id', 'name', 'category', 'price', 'stock']]
.assign(
price_with_tax=lambda x: (x['price'] * 1.2).round(2),
available=lambda x: x['stock'] > 0,
)
.rename(columns={'name': 'title', 'category': 'cat', 'stock': 'quantity'})
.to_dict(orient='records')
)
with open('products_api.json', 'w', encoding='utf-8') as f:
json.dump(result, f, ensure_ascii=False, indent=2)
print(f"Products exported: {len(result)}")
Batch: convert multiple Excel files to JSON
import pandas as pd
import json
from pathlib import Path
def excel_dir_to_json(input_dir, output_dir=None):
source = Path(input_dir)
dest = Path(output_dir) if output_dir else source
dest.mkdir(parents=True, exist_ok=True)
files = list(source.glob('*.xlsx')) + list(source.glob('*.xls'))
print(f"Found: {len(files)} Excel files")
for file in sorted(files):
try:
sheets = pd.read_excel(file, sheet_name=None)
if len(sheets) == 1:
df = list(sheets.values())[0].dropna(how='all')
data = df.to_dict(orient='records')
else:
data = {
k: v.dropna(how='all').to_dict(orient='records')
for k, v in sheets.items()
}
json_path = dest / file.with_suffix('.json').name
with open(json_path, 'w', encoding='utf-8') as f:
json.dump(data, f, ensure_ascii=False, indent=2, default=str)
print(f" OK: {file.name} → {json_path.name}")
except Exception as e:
print(f" ERROR: {file.name}: {e}")
excel_dir_to_json('reports/', 'json_output/')
Related conversions
Document conversions that follow this topic naturally: