Convert JSON to Excel in Python: Pandas vs. OpenPyXL

Your marketing team wants the Shopify orders API dump as a formatted Excel report by 9 AM. The JSON is nested three levels deep, has nulls scattered everywhere, and clocks in at 140,000 rows. Do you reach for the one-liner or the full-control library? This guide compares the two battle-tested ways to convert JSON to Excel in Python — pandas.DataFrame.to_excel() and openpyxl — so you can pick the right tool before your coffee gets cold. It’s written for data engineers and analysts who bridge APIs and business reports daily, not for people writing their first print() statement.

Quick Picks

Quick Comparison Table

Method 1: pandas to_excel() — The One-Liner

pandas is the default answer when someone says “JSON to Excel” in a Slack thread. Load, normalize, export. Done.

import pandas as pd
import json

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

df = pd.json_normalize(data['orders'])
df.to_excel('orders.xlsx', index=False)

That’s it. For flat or mildly nested API responses — think a Stripe transactions endpoint or a WooCommerce product list — this is production-ready code.

Handling nested JSON. Real API payloads aren’t flat. A Shopify order nests line_items, customer, and shipping_address. pd.json_normalize() flattens dictionaries into dot-notated columns (customer.email, shipping_address.city), and its record_path argument explodes arrays into rows:

df = pd.json_normalize(
    data['orders'],
    record_path='line_items',
    meta=['id', 'order_number', ['customer', 'email']]
)

One call turns 5,000 orders with 3 line items each into a clean 15,000-row table. Doing this manually in openpyxl would take 40 lines of loop logic.

Handling nulls. Missing keys in JSON become NaN in pandas, which Excel renders as blank cells — usually what you want. If you need explicit placeholders, chain .fillna('N/A') or .fillna(0) for numeric columns before export. One gotcha: NaN in integer columns forces the whole column to float (5 becomes 5.0). Fix it with df['qty'] = df['qty'].astype('Int64') — pandas’ nullable integer type.

Memory optimization for 100k+ rows. Here’s where most tutorials fail you. The default openpyxl engine loads everything into memory twice. For large exports, switch engines and chunk:

with pd.ExcelWriter('big.xlsx', engine='xlsxwriter') as writer:
    for chunk in pd.read_json('huge.json', lines=True, chunksize=20000):
        start = writer.sheets['Sheet1'].dim_rowmax if 'Sheet1' in writer.sheets else 0
        chunk.to_excel(writer, index=False, header=(start == 0), startrow=start)

In 2024 community benchmarks, XlsxWriter in constant_memory mode wrote 500k rows using roughly 60% less RAM than openpyxl. If you’re exporting marketplace order histories or GA4 event dumps, that difference decides whether your laptop survives.

Best for: analysts who need data in Excel now, automated pipelines, and anything over 50k rows.

Method 2: openpyxl — Full Report Control

pandas gets data into Excel. openpyxl makes it look like a human built the report. When the deliverable is a board-deck-ready workbook — branded headers, currency formats, frozen panes, conditional formatting, three sheets deep — pandas taps out.

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = 'Orders'

headers = ['Order ID', 'Customer', 'Total', 'Status']
ws.append(headers)

for col in range(1, len(headers) + 1):
    cell = ws.cell(row=1, column=col)
    cell.font = Font(bold=True, color='FFFFFF')
    cell.fill = PatternFill('solid', fgColor='1F4E78')
    cell.alignment = Alignment(horizontal='center')

for order in data['orders']:
    ws.append([
        order['id'],
        order.get('customer', {}).get('email', 'N/A'),
        float(order['total_price']),
        order['financial_status']
    ])

ws.freeze_panes = 'A2'
ws.auto_filter.ref = ws.dimensions
wb.save('orders_report.xlsx')

Where openpyxl earns its complexity:

• Multiple sheets with relationships — a Summary sheet with =SUMIF(Orders!D:D,"paid",Orders!C:C) formulas, not just pasted values
• Number formats — '$#,##0.00', percentages, date formats per column
• Charts — native Excel bar/line charts generated from your data ranges
• Conditional formatting — red-fill rows where financial_status == 'refunded'
• Merged cells and column widths — the stuff clients actually notice

Handling nested JSON and nulls is manual. You write the flattening logic yourself — which is painful for deep structures but gives you exact control over how, say, a discount_codes array becomes a comma-joined string instead of 12 extra rows. Use .get() with defaults everywhere; openpyxl writes Python None as a blank cell, which handles nulls gracefully.

Memory at scale is the honest weakness. openpyxl holds the entire workbook as Python objects. Expect roughly 1–2 GB of RAM for a 200k-row, 10-column workbook based on 2024-era benchmarks. There’s a write_only=True mode that streams rows and cuts memory dramatically, but it disables styling after-the-fact — you must style as you write.

wb = Workbook(write_only=True)

Best for: recurring business reports, multi-sheet deliverables, anything a non-technical stakeholder opens directly.

Head-to-Head by Use Case

“I’m exporting API data for my own analysis.” pandas. You’ll be in a pivot table within two minutes; formatting is irrelevant.

“I run a Shopify store doing $80k/month and email weekly sales reports to my agency.” Hybrid: pandas for flattening, openpyxl for presentation. pd.ExcelWriter(engine='openpyxl') lets you write the DataFrame, then grab writer.sheets['Orders'] and style it. Best of both worlds, ~15 extra lines.

“I’m building an automated pipeline exporting 500k rows nightly.” pandas with XlsxWriter, chunked, constant_memory mode. openpyxl will OOM your container.

“The CFO wants a formatted P&L workbook with a summary tab and charts.” Pure openpyxl. Charts and cross-sheet formulas aren’t negotiable here.

Our Verdict

pandas wins for 80% of JSON-to-Excel tasks. The json_normalize() → to_excel() pipeline handles nesting, nulls, and scale with a fraction of the code, and it’s the right default for any engineer. But if the output is a report rather than a dataset — styled, multi-sheet, formula-driven — openpyxl is the only serious option, and the pandas-plus-openpyxl hybrid covers everything in between. Don’t pick one library religiously; pick per deliverable.

Downloadable script: Save the hybrid pattern below as json_to_excel.py — it takes any JSON file path as an argument and produces a styled workbook:

import sys, json, pandas as pd
from openpyxl.styles import Font, PatternFill

with open(sys.argv[1]) as f:
    data = json.load(f)

records = data if isinstance(data, list) else data[list(data.keys())[0]]
df = pd.json_normalize(records).fillna('N/A')

with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, index=False, sheet_name='Data')
    ws = writer.sheets['Data']
    for cell in ws[1]:
        cell.font = Font(bold=True, color='FFFFFF')
        cell.fill = PatternFill('solid', fgColor='1F4E78')
    ws.freeze_panes = 'A2'
    ws.auto_filter.ref = ws.dimensions

print(f'Exported {len(df)} rows to output.xlsx')

Run it with python json_to_excel.py orders.json.

FAQ

1. How do I convert JSON to Excel in Python without pandas? Use openpyxl directly: parse the JSON with the built-in json module, iterate records, and ws.append() each row. It takes more code but avoids the pandas dependency — useful in lightweight AWS Lambda functions where pandas’ ~100MB footprint matters.

2. How do I handle deeply nested JSON before exporting to Excel? Use pd.json_normalize() with record_path for arrays and meta for parent fields. For structures nested beyond two or three levels, flatten selectively — export the nested array (like line items) to its own sheet rather than exploding one mega-table.

3. Why does my Excel export show NaN or None in cells? Missing JSON keys become NaN in pandas and None in raw openpyxl writes. Call df.fillna('') before export, or use .get(key, '') in manual loops. Blank cells are usually preferable to literal “NaN” strings in business reports.

4. What’s the maximum number of rows I can write to Excel from Python? Excel’s hard limit is 1,048,576 rows per sheet. Python-wise, pandas with XlsxWriter in constant_memory mode can approach that limit comfortably; openpyxl in standard mode typically hits memory problems around 200k–400k rows depending on column count and your RAM.

5. Can I add multiple sheets when converting JSON to Excel? Yes. With pandas, use pd.ExcelWriter and call to_excel() multiple times with different sheet_name values. With openpyxl, call wb.create_sheet('Name') for each sheet. Both libraries handle it natively — this isn’t a differentiator between them.

Conclusion

The pandas-versus-openpyxl debate has a boring-but-true answer: pandas for data movement, openpyxl for data presentation, and the hybrid pattern for anything in production. Start with the downloadable script above, measure your row counts and formatting needs, and scale up the complexity only when a stakeholder actually asks for it. For more practical guides on wrangling ecommerce data — from Shopify API exports to automated reporting pipelines — keep an eye on our data engineering coverage at e-commpartners.com.