5 Best Ways to Convert Python Dict to Excel with Multiple Sheets

In many data processing tasks, there is a need to export a Python dictionary to an Excel file with multiple sheets for better organization and analysis. Assume you have a dictionary with keys as sheet names and values as lists of dictionaries, each representing a row in Excel. The goal is to efficiently convert this Python dictionary into a multi-sheet Excel workbook where each sheet corresponds to a key from the dictionary.

Method 1: Using pandas DataFrame with ExcelWriter

This method involves creating pandas DataFrame objects for each key in the dictionary, then using ExcelWriter to write each DataFrame to a separate sheet in the Excel file. pandas ExcelWriter is ideal for this task as it provides fine control over the Excel file’s creation process.

Here’s an example:

import pandas as pd

# Given dictionary
data = {
    'Sheet1': [{'name': 'Alice', 'age': 30}, {'name': 'Bob', 'age': 25}],
    'Sheet2': [{'name': 'Chris', 'age': 20}, {'name': 'Diana', 'age': 35}]
}

# Create a Pandas Excel writer using XlsxWriter as the engine
with pd.ExcelWriter('multiple_sheets.xlsx', engine='xlsxwriter') as writer:
    for sheet_name, records in data.items():
        df = pd.DataFrame(records)
        df.to_excel(writer, sheet_name=sheet_name, index=False)

# Output: An Excel file "multiple_sheets.xlsx" with two sheets "Sheet1" and "Sheet2".

Each sheet in the output Excel file corresponds to a key in the original dictionary, containing the data in tabular format.

Method 2: Using OpenPyXL with Dict Comprehension

OpenPyXL is a versatile library for reading and writing Excel files in Python. This method uses OpenPyXL to directly manipulate the workbook and sheets. It’s suitable for more complex Excel operations, such as formatting or creating pivot tables.

Here’s an example:

from openpyxl import Workbook

# Given dictionary
data = {
    'Sheet1': [{'Name': 'Alice', 'Age': 30}, {'Name': 'Bob', 'Age': 25}],
    'Sheet2': [{'Name': 'Chris', 'Age': 20}, {'Name': 'Diana', 'Age': 35}]
}

wb = Workbook()
for sheet_name, records in data.items():
    ws = wb.create_sheet(title=sheet_name)
    for row_index, record in enumerate(records, start=1):
        for col_index, (key, value) in enumerate(record.items(), start=1):
            if row_index == 1:
                ws.cell(row=row_index, column=col_index, value=key)
            ws.cell(row=row_index + 1, column=col_index, value=value)

wb.save('multiple_sheets.xlsx')

# Output: An Excel file "multiple_sheets.xlsx" with custom sheets and data.

The code creates an Excel workbook and iterates over the dictionary to populate each sheet with data rows. Headers are generated dynamically based on dictionary keys.

Method 3: Using xlsxwriter with Dictionary Iteration

xlsxwriter is another library for creating Excel files that supports advanced features such as charts and formulas. This method is great for performance-sensitive tasks due to its speed and efficiency when writing large datasets.

Here’s an example:

import xlsxwriter

# Given dictionary
data = {
    'Sheet1': [{'Name': 'Alice', 'Age': 30}, {'Name': 'Bob', 'Age': 25}],
    'Sheet2': [{'Name': 'Chris', 'Age': 20}, {'Name': 'Diana', 'Age': 35}]
}

workbook = xlsxwriter.Workbook('multiple_sheets.xlsx')
for sheet_name, records in data.items():
    worksheet = workbook.add_worksheet(sheet_name)
    headers = records[0].keys()
    for col, header in enumerate(headers):
        worksheet.write(0, col, header)
    for row, record in enumerate(records, start=1):
        for col, (key, value) in enumerate(record.items()):
            worksheet.write(row, col, value)

workbook.close()

# Output: An Excel file "multiple_sheets.xlsx" with multiple sheets and formatted headers.

This code uses xlsxwriter to create a new Excel file, then iterates through the dictionary, creating a new sheet for each key, and populates it with data using efficient writing methods.

Method 4: Using pyexcelerate for Performance-Intensive Tasks

pyexcelerate is explicitly designed for performance and can handle large amounts of data more efficiently than other libraries. It is particularly useful when you need to generate massive Excel files in a time-sensitive environment.

Here’s an example:

from pyexcelerate import Workbook

# Given dictionary
data = {
    'Sheet1': [{'Name': 'Alice', 'Age': 30}, {'Name': 'Bob', 'Age': 25}],
    'Sheet2': [{'Name': 'Chris', 'Age': 20}, {'Name': 'Diana', 'Age': 35}]
}

wb = Workbook()
for sheet_name, records in data.items():
    rows = [list(records[0].keys())] + [list(record.values()) for record in records]
    wb.new_sheet(sheet_name, data=rows)
wb.save('multiple_sheets.xlsx')

# Output: An Excel file "multiple_sheets.xlsx" optimized for performance with multiple sheets.

This code snippet creates a new Excel workbook with the pyexcelerate library, adds multiple sheets with their respective data, and writes it to the disk, all performed with a focus on speed.

Bonus One-Liner Method 5: Using pandas and ExcelWriter with List Comprehension

For those who appreciate brevity, this one-liner uses a list comprehension within the pandas ExcelWriter context manager to export the dictionary to an Excel file with multiple sheets. This method is a compact solution when writing quick scripts.

Here’s an example:

import pandas as pd

# Given dictionary
data = {'Sheet1': [{'Name': 'Alice', 'Age': 30}], 'Sheet2': [{'Name': 'Chris', 'Age': 20}]}

with pd.ExcelWriter('multiple_sheets.xlsx') as writer:
    [pd.DataFrame(records).to_excel(writer, sheet_name=sheet_name, index=False) for sheet_name, records in data.items()]

# Output: An Excel file "multiple_sheets.xlsx" with multiple sheets from a dictionary.

The above code creates an Excel file with each sheet filled with data from the corresponding key in the dictionary using list comprehension for a more concise codebase.

Summary/Discussion

• Method 1: Using pandas DataFrame with ExcelWriter. Strengths: Easy to use, supports advanced features. Weaknesses: Dependency on pandas.
• Method 2: Using OpenPyXL with Dict Comprehension. Strengths: Allows for complex Excel operations. Weaknesses: Slightly more complex syntax, can be slower than pandas for large data.
• Method 3: Using xlsxwriter with Dictionary Iteration. Strengths: Fast and efficient, great for large datasets. Weaknesses: API might be more complex than pandas for some users.
• Method 4: Using pyexcelerate for Performance-Intensive Tasks. Strengths: Optimized for performance, particularly with large data. Weaknesses: Less feature-rich compared to pandas or OpenPyXL.
• Bonus One-Liner Method 5: Using pandas with list comprehension. Strengths: Compact syntax. Weaknesses: Less readable, especially for Python beginners.