JSON serves as a ubiquitous data interchange format in modern applications, while Excel remains the standard for business reporting and data analysis. Converting between these formats is a common requirement in data processing workflows. This guide demonstrates how to use the Free Spire.XLS for Python library to perform this conversion efficiently.

Setting Up the Environment

Installing the Librarry

Free Spire.XLS for Python operates independently without requiring Microsoft Office installation. Install it via pip:

pip install Spire.XLS.Free

Sample Data Structure

The following JSON structure represents a typical dataset used throughout this guide:

[
    {
        "id": 1001,
        "name": "张三",
        "age": 28,
        "email": "zhangsan@example.com",
        "is_active": true
    },
    {
        "id": 1002,
        "name": "李四",
        "age": 32,
        "email": "lisi@example.com",
        "is_active": false
    },
    {
        "id": 1003,
        "name": "王五",
        "age": 25,
        "email": "wangwu@example.com",
        "is_active": true
    }
]

Basic Conversion Implementation

The conversion process follows these steps: parse the JSON input, initialize an Excel workbook, populate headers and data rows, then save the output file.

import json
from spire.xls import Workbook, ExcelVersion
from spire.xls.common import *

class JsonToExcelConverter:
    def __init__(self):
        self.workbook = None
        self.sheet = None
    
    def convert(self, json_input, file_path):
        # Initialize workbook and create sheet
        self.workbook = Workbook()
        self.workbook.Worksheets.Clear()
        self.sheet = self.workbook.Worksheets.Add("Data")
        
        # Parse JSON content
        records = self._parse_json(json_input)
        if not records:
            raise ValueError("Empty JSON data provided")
        
        # Extract column headers from first record
        columns = list(records[0].keys())
        self._write_headers(columns)
        self._write_data_rows(records, columns)
        
        # Adjust column widths and save
        self.sheet.AllocatedRange.AutoFitColumns()
        self.workbook.SaveToFile(file_path, ExcelVersion.Version2016)
        self.workbook.Dispose()
    
    def _parse_json(self, json_input):
        return json.loads(json_input)
    
    def _write_headers(self, columns):
        for index, col_name in enumerate(columns):
            cell = self.sheet.Range[1, index + 1]
            cell.Text = col_name
    
    def _write_data_rows(self, records, columns):
        for row_num, record in enumerate(records, start=2):
            for col_num, field in enumerate(columns):
                cell_value = record.get(field, "")
                self.sheet.Range[row_num, col_num + 1].Text = str(cell_value)

# Usage example
if __name__ == "__main__":
    sample_json = '''
    [
        {"id": 1001, "name": "张三", "age": 28, "email": "zhangsan@example.com", "is_active": true},
        {"id": 1002, "name": "李四", "age": 32, "email": "lisi@example.com", "is_active": false},
        {"id": 1003, "name": "王五", "age": 25, "email": "wangwu@example.com", "is_active": true}
    ]
    '''
    
    converter = JsonToExcelConverter()
    try:
        converter.convert(sample_json, "output.xlsx")
        print("Conversion successful")
    except Exception as err:
        print(f"Error: {err}")

Key implementation details:

• The workbook is cleared of default worksheets before adding a custom-named sheet
• The get() method with a default empty string prevents errors when keys are missing
• All values are converted to strings for consistent cell formatting
• The Dispose() method releases memory resources after file generation

Handling Nested JSON Structures

Real-world JSON often contains nested objects. A recursive flattening approach can transform hierarchical data into a flat structure suitable for tabular display:

import json
from spire.xls import Workbook, ExcelVersion
from spire.xls.common import *

def flatten_nested_dict(obj, prefix='', separator='_'):
    """Recursively flatten nested dictionaries"""
    result = {}
    for key, val in obj.items():
        new_key = f"{prefix}{separator}{key}" if prefix else key
        if isinstance(val, dict):
            nested = flatten_nested_dict(val, new_key, separator)
            result.update(nested)
        else:
            result[new_key] = val
    return result

def export_nested_json(json_str, output_file):
    wb = Workbook()
    wb.Worksheets.Clear()
    ws = wb.Worksheets.Add("Nested Data")
    
    parsed_data = json.loads(json_str)
    if not parsed_data:
        raise ValueError("No data to export")
    
    # Flatten all records and extract column names
    flat_records = [flatten_nested_dict(record) for record in parsed_data]
    column_names = list(flat_records[0].keys())
    
    # Populate headers
    for i, name in enumerate(column_names):
        ws.Range[1, i + 1].Text = name
    
    # Populate data
    for row, record in enumerate(flat_records, start=2):
        for col, field in enumerate(column_names):
            ws.Range[row, col + 1].Text = str(record.get(field, ""))
    
    ws.AllocatedRange.AutoFitColumns()
    wb.SaveToFile(output_file, ExcelVersion.Version2016)
    wb.Dispose()

# Test with nested data
if __name__ == "__main__":
    nested_sample = '''
    [
        {
            "id": 1001,
            "name": "张三",
            "contact": {
                "email": "zhangsan@example.com",
                "phone": "13800138000"
            },
            "location": {
                "province": "北京",
                "city": "北京市"
            }
        },
        {
            "id": 1002,
            "name": "李四",
            "contact": {
                "email": "lisi@example.com",
                "phone": "13900139000"
            },
            "location": {
                "province": "上海",
                "city": "上海市"
            }
        }
    ]
    '''
    
    export_nested_json(nested_sample, "nested_output.xlsx")
    print("Nested JSON exported successfully")

Applying Visual Styling

Enhancing the visual presantation improves readability. The following code applies formatting to header rows:

from spire.xls import Color, HorizontalAlignType

def apply_header_style(worksheet, column_count):
    header_cells = worksheet.Range[1, 1, 1, column_count]
    header_cells.Style.Color = Color.get_SkyBlue()
    header_cells.Style.Font.IsBold = True
    header_cells.Style.HorizontalAlignment = HorizontalAlignType.Center

Integrate this function after writing headers to create a polished output with highlighted, centered, and bold header text.

Best Practices

• Validate JSON data integrity before processing
• Handle edge cases such as empty arrays or missing feilds gracefully
• Always call Dispose() to free resources, especially in batch processing scenarios
• Use consistent error handling to provide meaningful feedback when conversion fails