Pandas DataFrame to Styled Excel with Python openpyxl

Data analysis often requires presenting results professionally. While Pandas can export to Excel, openpyxl adds advanced styling capabilities.

This guide shows how to transform plain DataFrames into beautifully formatted Excel reports. You will learn practical styling techniques.

Setting Up Your Environment

First, ensure you have the necessary packages installed. Both pandas and openpyxl are required for this workflow.

# Install required packages
# pip install pandas openpyxl

Import the libraries at the beginning of your script. We'll use pandas for data manipulation and openpyxl for styling.

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows

Creating a Sample DataFrame

Let's create sample sales data to work with. This DataFrame will serve as our base for styling demonstrations.

# Create sample DataFrame
data = {
    'Product': ['Laptop', 'Mouse', 'Keyboard', 'Monitor', 'Headphones'],
    'Q1_Sales': [150, 300, 200, 100, 250],
    'Q2_Sales': [180, 320, 210, 120, 270],
    'Q3_Sales': [200, 350, 230, 140, 290],
    'Growth_Rate': [0.15, 0.12, 0.10, 0.25, 0.08]
}

df = pd.DataFrame(data)
print("Original DataFrame:")
print(df)

Original DataFrame:
      Product  Q1_Sales  Q2_Sales  Q3_Sales  Growth_Rate
0      Laptop       150       180       200         0.15
1       Mouse       300       320       350         0.12
2    Keyboard       200       210       230         0.10
3     Monitor       100       120       140         0.25
4  Headphones       250       270       290         0.08

Basic Excel Export with Pandas

Start with a simple Excel export using pandas. This creates a basic, unformatted spreadsheet.

# Basic export to Excel
df.to_excel('basic_sales_report.xlsx', index=False, sheet_name='Sales Data')

The basic export works but lacks visual appeal. The data appears plain without any formatting or styling enhancements.

Advanced Styling with openpyxl

Now let's create a professionally styled Excel report. We'll use openpyxl's styling capabilities for better presentation.

# Create workbook and worksheet
wb = Workbook()
ws = wb.active
ws.title = "Styled Sales Report"

# Convert DataFrame to rows and add to worksheet
for row in dataframe_to_rows(df, index=False, header=True):
    ws.append(row)

# Define styles
header_font = Font(bold=True, color="FFFFFF", size=12)
header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
data_font = Font(size=11)
border = Border(left=Side(style='thin'), right=Side(style='thin'),
               top=Side(style='thin'), bottom=Side(style='thin'))
center_align = Alignment(horizontal='center')

# Apply header styles
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = center_align
    cell.border = border

# Apply data styles
for row in ws.iter_rows(min_row=2, max_row=ws.max_row, max_col=ws.max_column):
    for cell in row:
        cell.font = data_font
        cell.border = border
        cell.alignment = center_align

# Auto-adjust column widths
for column in ws.columns:
    max_length = 0
    column_letter = column[0].column_letter
    for cell in column:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2)
    ws.column_dimensions[column_letter].width = adjusted_width

wb.save('styled_sales_report.xlsx')
print("Styled Excel file created successfully!")

Styled Excel file created successfully!

Conditional Formatting for Data Insights

Add conditional formatting to highlight important data points. This makes trends and outliers immediately visible.

from openpyxl.formatting.rule import CellIsRule

# Reopen the workbook for additional formatting
wb = Workbook()
ws = wb.active
ws.title = "Conditional Formatting Demo"

# Add DataFrame data
for row in dataframe_to_rows(df, index=False, header=True):
    ws.append(row)

# Apply conditional formatting for high sales
high_sales_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")
high_sales_rule = CellIsRule(operator='greaterThan', formula=['250'], fill=high_sales_fill)
ws.conditional_formatting.add(f'B2:D{len(df)+1}', high_sales_rule)

# Apply conditional formatting for growth rates
high_growth_fill = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")
high_growth_rule = CellIsRule(operator='greaterThan', formula=['0.20'], fill=high_growth_fill)
ws.conditional_formatting.add(f'E2:E{len(df)+1}', high_growth_rule)

wb.save('conditional_formatting_report.xlsx')
print("Conditional formatting applied successfully!")

Number Formatting and Data Types

Proper number formatting improves readability. Format currencies, percentages, and dates appropriately.

# Apply number formatting
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
    # Format sales columns as currency
    for i in range(1, 4):  # Q1_Sales to Q3_Sales columns
        row[i].number_format = '$#,##0'
    
    # Format growth rate as percentage
    row[4].number_format = '0.00%'

wb.save('formatted_numbers_report.xlsx')
print("Number formatting applied successfully!")

For more advanced number formatting techniques, check our Advanced Number Formatting Python openpyxl Guide.

Adding Charts and Visualizations

Charts transform raw data into visual insights. openpyxl supports various chart types for data visualization.

from openpyxl.chart import BarChart, Reference

# Create a bar chart
chart = BarChart()
chart.type = "col"
chart.title = "Quarterly Sales by Product"
chart.style = 10

# Define data ranges
data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=len(df)+1)
categories = Reference(ws, min_col=1, min_row=2, max_row=len(df)+1)

# Add data and categories to chart
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)

# Position chart on worksheet
ws.add_chart(chart, "G2")

wb.save('chart_report.xlsx')
print("Chart added successfully!")

Working with Multiple Worksheets

Organize complex reports across multiple sheets. Each sheet can serve a different purpose or contain related data.

# Create summary sheet
summary_ws = wb.create_sheet("Summary")

# Add summary data
summary_data = [
    ["Metric", "Value"],
    ["Total Products", len(df)],
    ["Average Q1 Sales", df['Q1_Sales'].mean()],
    ["Highest Growth", df['Growth_Rate'].max()],
    ["Best Selling Product", df.loc[df['Q3_Sales'].idxmax(), 'Product']]
]

for row in summary_data:
    summary_ws.append(row)

# Style summary sheet
for row in summary_ws.iter_rows():
    for cell in row:
        cell.border = border
        cell.alignment = center_align

wb.save('multi_sheet_report.xlsx')
print("Multiple worksheets created successfully!")

Learn more about managing multiple sheets in our Copy Move Delete Sheets Python openpyxl Guide.

Performance Optimization Tips

When working with large datasets, performance becomes important. Use these techniques for better efficiency.

# Optimized approach for large datasets
def optimized_dataframe_to_excel(df, filename):
    wb = Workbook()
    ws = wb.active
    
    # Write headers
    headers = list(df.columns)
    ws.append(headers)
    
    # Write data in batches for large datasets
    for index, row in df.iterrows():
        ws.append(row.tolist())
    
    # Apply styling only after all data is written
    apply_basic_styling(ws)
    
    wb.save(filename)

def apply_basic_styling(worksheet):
    # Minimal styling for performance
    for row in worksheet.iter_rows():
        for cell in row:
            cell.border = border

# Usage
optimized_dataframe_to_excel(df, 'optimized_report.xlsx')
print("Optimized report created successfully!")

For handling very large files, consider using Python openpyxl Read Only vs Normal Mode Guide for performance insights.

Best Practices for Professional Reports

Follow these best practices to create production-ready Excel reports. Consistency and clarity are key.

Use a consistent color scheme throughout your report. Limit your palette to 2-3 main colors for visual harmony.

Ensure proper alignment based on data type. Text should be left-aligned, numbers right-aligned, and headers centered.

Include clear titles and descriptions. Each sheet should have a purpose that's immediately understandable to users.

Test your Excel file with different users. Ensure formulas work correctly and formatting appears as intended.

Conclusion

Converting Pandas DataFrames to styled Excel files with openpyxl transforms raw data into professional reports. The combination provides powerful data manipulation and presentation capabilities.

You learned basic styling, conditional formatting, chart creation, and performance optimization. These skills help create Excel reports that effectively communicate data insights.

Remember to balance visual appeal with readability. Well-styled reports make data more accessible and actionable for decision-makers.

Continue exploring openpyxl's features to enhance your Excel automation workflows. The library offers extensive capabilities for professional spreadsheet creation.