Technical Implementation of Creating Multiple Excel Worksheets from pandas DataFrame Data

Introduction and Problem Context

In data analysis and processing, it is often necessary to export data from pandas DataFrames to Excel files, especially when the data needs to be organized into multiple worksheets. For instance, users may want to display both raw data and data processed with forward fill (ffill) in a single Excel document, or create multiple worksheets based on unique values in a specific column. However, many developers encounter issues such as failed worksheet creation, incorrect data writing, or difficulties in applying formats when attempting to implement this functionality. This article aims to provide a comprehensive and efficient solution by deeply analyzing common errors.

Core Concepts and Tool Selection

The pandas library provides the DataFrame.to_excel() method for exporting DataFrame data to Excel files. When creating Excel documents with multiple worksheets, the pandas.ExcelWriter class should be used, which can work with different engines such as xlsxwriter or openpyxl. xlsxwriter is a powerful Python library specifically designed for creating and formatting Excel .xlsx files, supporting advanced features like cell formatting, charts, and VBA macro integration. Choosing the right combination of tools is key to ensuring correct data export and formatting.

Common Error Analysis and Correction

In the user-provided code example, the main error lies in mixing incompatible libraries and methods. The code attempts to use xlwt's Workbook and add_sheet() methods to create worksheets, but then tries to write data via pandas' to_excel() method, resulting in worksheets being created without data. xlwt is primarily used for generating older .xls format files, while xlsxwriter is suitable for .xlsx format and offers richer functionality. The correct approach is to directly use pandas.ExcelWriter with the xlsxwriter engine, avoiding manual calls to add_sheet(), as the to_excel() method automatically handles worksheet creation and naming.

Basic Implementation: Creating Multi-Worksheet Excel Files

The following is a corrected code example demonstrating how to export two DataFrames (raw data and forward-filled data) to different worksheets in the same Excel file. First, ensure that the pandas and xlsxwriter libraries are installed (via pip install pandas xlsxwriter). Then, use pandas.ExcelWriter to create a writer object, specifying the engine as xlsxwriter. By calling the DataFrame.to_excel() method with the sheet_name parameter, data can be easily written to worksheets with specified names. Finally, use writer.save() or a context manager (with statement) to ensure the file is saved correctly.

import pandas as pd

# Sample data: original DataFrame
original_data = pd.read_excel('input.xlsx', sheet_name='Leave', skiprows=26)
# Copy data for forward fill processing
filled_data = original_data.copy()
filled_data.fillna(method='ffill', inplace=True)

# Use ExcelWriter to create a multi-worksheet Excel file
with pd.ExcelWriter('output.xlsx', engine='xlsxwriter') as writer:
    original_data.to_excel(writer, sheet_name='Original Data')
    filled_data.to_excel(writer, sheet_name='Forward Filled Data')
# File is automatically saved and closed

This code first reads the original data from an Excel file, then creates a copy and applies forward fill. Next, the with statement ensures that ExcelWriter automatically closes and saves the file after operations. By specifying different sheet_names, data is written to two separate worksheets. This method is concise and efficient, avoiding the complexity of manually managing worksheets.

Advanced Application: Dynamically Creating Worksheets Based on Column Values

For more complex scenarios, such as creating multiple worksheets based on unique values in a DataFrame column, pandas grouping functions and loops can be combined. Suppose there is a column named Category with multiple unique values, and we want to create a worksheet for each unique value, storing the corresponding data rows. The following code demonstrates how to achieve this.

import pandas as pd

# Assume data is a DataFrame containing a 'Category' column
data = pd.read_excel('input.xlsx')

# Get unique values of the 'Category' column
unique_categories = data['Category'].unique()

# Use ExcelWriter to create a multi-worksheet Excel file
with pd.ExcelWriter('categorized_output.xlsx', engine='xlsxwriter') as writer:
    for category in unique_categories:
        # Filter data for the current category
        category_data = data[data['Category'] == category]
        # Write data to a worksheet named after the category
        category_data.to_excel(writer, sheet_name=str(category))

This code first reads the data and extracts unique values from the Category column. Then, in a loop, it filters the data subset for each unique value and writes it to a worksheet named after that value. This method flexibly handles any number of unique values, suitable for scenarios like data categorization and report generation.

Formatting and VBA Integration

After exporting data, users may want to apply formatting such as bolding headings or adding colors. The xlsxwriter library provides rich formatting capabilities that can be implemented directly via Python code, without calling external DLLs or plugins. For example, a format object can be created and applied to specific cells or rows. The following code shows how to set the header row of a worksheet to bold with a blue background.

import pandas as pd
import xlsxwriter

# Create a sample DataFrame
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})

# Use ExcelWriter and get workbook and worksheet objects
with pd.ExcelWriter('formatted_output.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)
    
    # Get workbook and worksheet objects
    workbook = writer.book
    worksheet = writer.sheets['Sheet1']
    
    # Define formatting style
    header_format = workbook.add_format({
        'bold': True,
        'bg_color': '#C6EFCE',  # Light green background
        'border': 1
    })
    
    # Apply formatting to the header row (first row)
    worksheet.set_row(0, None, header_format)

For more complex VBA macro integration, xlsxwriter supports embedding pre-compiled VBA code (.bin files) via the workbook.add_vba_project() method. This allows including custom VBA scripts directly when generating Excel files in Python, for automating formatting or other tasks. Note that VBA project files need to be prepared in advance and may involve additional security considerations.

Performance Optimization and Best Practices

When dealing with large DataFrames or multiple worksheets, performance can become an issue. Here are some optimization suggestions: use the with statement to ensure proper resource release; avoid repeatedly creating ExcelWriter objects in loops; for large datasets, consider chunked writing or using engine='openpyxl' (suitable for read-write operations on .xlsx files). Additionally, always test code in a development environment to ensure formats and functionalities meet expectations.

Conclusion

By correctly using pandas.ExcelWriter with the xlsxwriter engine, pandas DataFrame data can be efficiently exported to Excel files containing multiple worksheets. This article details how to avoid common errors, dynamically create worksheets, and apply formatting, from basic implementations to advanced applications. These techniques are not only applicable to data export tasks but also provide strong support for automated reporting and data visualization. As data volumes grow and requirements become more complex, mastering these tools will significantly enhance data processing and analysis efficiency.