Generate Excel Reports from JSON Data Using Python pyexcel

JSON data is everywhere in modern applications. But business users often need Excel files. Python pyexcel bridges this gap perfectly.

This tutorial shows you how to convert JSON to Excel reports. You will learn practical techniques for data transformation.

What is pyexcel?

Pyexcel is a Python library for spreadsheet tasks. It supports many file formats including Excel, CSV, and JSON.

The library provides simple APIs for data manipulation. You can read, write, and convert between formats easily.

Pyexcel handles complex spreadsheet operations with minimal code. It is perfect for automation and reporting tasks.

Installing pyexcel

First, you need to install the pyexcel library. Use pip for installation.

pip install pyexcel pyexcel-xlsx

The pyexcel-xlsx package adds Excel format support. For other formats, install corresponding plugins.

If you encounter installation issues, check our guide on Install pyexcel in Python with pip and Virtualenv.

Basic JSON to Excel Conversion

Let's start with a simple JSON dataset. We'll convert it to an Excel file.

import pyexcel as pe

# Sample JSON data
json_data = [
    {"Name": "John", "Age": 30, "Department": "Sales"},
    {"Name": "Jane", "Age": 25, "Department": "Marketing"},
    {"Name": "Bob", "Age": 35, "Department": "IT"}
]

# Save as Excel file
pe.save_as(records=json_data, dest_file_name="employees.xlsx")

print("Excel file created successfully!")

Excel file created successfully!

The save_as function creates the Excel file. The JSON data becomes spreadsheet rows automatically.

Each dictionary in the JSON array becomes a row. Dictionary keys become column headers.

Handling Complex JSON Structures

Real-world JSON data is often nested. You need to flatten it for Excel format.

import json
import pyexcel as pe

# Complex nested JSON
complex_json = '''
{
    "company": "Tech Corp",
    "employees": [
        {
            "id": 1,
            "personal_info": {
                "name": "Alice",
                "age": 28
            },
            "job": {
                "title": "Developer",
                "salary": 75000
            }
        },
        {
            "id": 2,
            "personal_info": {
                "name": "Charlie",
                "age": 32
            },
            "job": {
                "title": "Manager",
                "salary": 90000
            }
        }
    ]
}
'''

# Parse and flatten JSON
data = json.loads(complex_json)
flattened_data = []

for employee in data['employees']:
    flattened_data.append({
        'ID': employee['id'],
        'Name': employee['personal_info']['name'],
        'Age': employee['personal_info']['age'],
        'Job Title': employee['job']['title'],
        'Salary': employee['job']['salary']
    })

# Create Excel report
pe.save_as(records=flattened_data, dest_file_name="company_report.xlsx")

This code extracts nested data into a flat structure. Excel requires tabular data without nesting.

For more data processing techniques, see Clean Normalize Spreadsheet Data Python pyexcel.

Reading JSON from Files

Often, your JSON data comes from files. Here's how to process JSON files directly.

import json
import pyexcel as pe

# Read JSON from file
with open('data.json', 'r') as file:
    json_data = json.load(file)

# Convert to Excel
if isinstance(json_data, list):
    pe.save_as(records=json_data, dest_file_name="from_file.xlsx")
else:
    # Handle single object JSON
    pe.save_as(records=[json_data], dest_file_name="from_file.xlsx")

The code handles both JSON arrays and single objects. It reads from file and converts to Excel.

Always check your JSON structure before processing. This prevents unexpected errors.

Customizing Excel Output

Pyexcel lets you customize the Excel output. You can control sheet names and data organization.

import pyexcel as pe

# Multiple datasets
sales_data = [
    {"Product": "Laptop", "Q1": 150, "Q2": 200},
    {"Product": "Phone", "Q1": 300, "Q2": 400}
]

inventory_data = [
    {"Product": "Laptop", "Stock": 50},
    {"Product": "Phone", "Stock": 100}
]

# Create multi-sheet Excel
pe.save_book_as(
    bookdict={
        "Sales_Report": sales_data,
        "Inventory": inventory_data
    },
    dest_file_name="business_report.xlsx"
)

The save_book_as function creates multiple sheets. Each sheet contains different data.

This is useful for comprehensive business reports. You can organize related data together.

Working with Large JSON Datasets

For large JSON files, process data in chunks. This prevents memory issues.

import json
import pyexcel as pe

def process_large_json(input_file, output_file, chunk_size=1000):
    all_data = []
    
    with open(input_file, 'r') as file:
        data = json.load(file)
        
        for i in range(0, len(data), chunk_size):
            chunk = data[i:i + chunk_size]
            # Process chunk here if needed
            all_data.extend(chunk)
    
    # Save final result
    pe.save_as(records=all_data, dest_file_name=output_file)

# Usage
process_large_json('large_data.json', 'large_report.xlsx')

This approach handles large datasets efficiently. It processes data in manageable chunks.

For database integration, check Export Database Query Results to Excel with Python pyexcel.

Error Handling and Validation

Always include error handling in production code. This makes your scripts robust.

import json
import pyexcel as pe
import os

def safe_json_to_excel(json_file, excel_file):
    try:
        # Check if file exists
        if not os.path.exists(json_file):
            raise FileNotFoundError(f"JSON file {json_file} not found")
        
        # Read and parse JSON
        with open(json_file, 'r') as file:
            data = json.load(file)
        
        # Validate data structure
        if not isinstance(data, (list, dict)):
            raise ValueError("JSON data must be array or object")
        
        # Convert to list if single object
        if isinstance(data, dict):
            data = [data]
        
        # Generate Excel
        pe.save_as(records=data, dest_file_name=excel_file)
        print(f"Successfully created {excel_file}")
        
    except Exception as e:
        print(f"Error: {str(e)}")

# Usage
safe_json_to_excel('data.json', 'report.xlsx')

This function includes comprehensive error checking. It handles file issues and data validation.

Proper error handling is crucial for reliable automation.

Advanced Formatting Options

While pyexcel focuses on data, you can enhance reports with additional libraries.

import pyexcel as pe
from pyexcel.ext import xlsx

# Data with different types
financial_data = [
    {"Month": "January", "Revenue": 50000, "Growth": 0.15},
    {"Month": "February", "Revenue": 52000, "Growth": 0.04},
    {"Month": "March", "Revenue": 58000, "Growth": 0.115}
]

# Save with specific parameters
pe.save_as(
    records=financial_data,
    dest_file_name="financial_report.xlsx",
    dest_encoding='utf-8'
)

You can specify encoding and other save parameters. This ensures compatibility.

For more format options, see Handle Multiple Spreadsheet Formats with Python pyexcel.

Conclusion

Python pyexcel simplifies JSON to Excel conversion. It handles various data structures efficiently.

You learned basic and advanced conversion techniques. These include nested JSON and large datasets.

The library is perfect for automated reporting systems. It integrates well with web APIs and databases.

Start using pyexcel for your data reporting needs. It will save you time and effort.

Remember to handle errors and validate your data. This ensures reliable automation.

Your JSON to Excel workflow just became much simpler with Python pyexcel.