Master Excel Export with Pandas: Styling, Formatting, and Engine Tricks

Preparation of Data

First, create a DataFrame containing datetime, date, numeric, and percentage columns, and add a calculated column using a Python f‑string expression.

import pandas as pd
from datetime import datetime, date

df = pd.DataFrame({
    'Date and time': [datetime(2015,1,1,11,30,55), datetime(2015,1,2,1,20,33), datetime(2015,1,3,11,10), datetime(2015,1,4,16,45), datetime(2015,1,5,12,10)],
    'Dates only': [date(2015,2,1), date(2015,2,2), date(2015,2,3), date(2015,2,4), date(2015,2,5)],
    'Numbers': [1010, 2020, 3030, 2020, 1515],
    'Percentage': [.1, .2, .33, .25, .5]
})

df['final'] = [f"=C{i}*D{i}" for i in range(2, df.shape[0] + 2)]

Pandas Direct Save

The simplest way to write the DataFrame to an Excel file is:

df.to_excel("demo1.xlsx", sheet_name='Sheet1', index=False)

This creates a basic workbook without any formatting.

Custom Formatting with ExcelWriter

To specify date and datetime formats, instantiate ExcelWriter with the appropriate parameters:

writer = pd.ExcelWriter(
    "demo1.xlsx",
    datetime_format='mmm d yyyy hh:mm:ss',
    date_format='mmmm dd yyyy'
)
df.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()

The resulting file shows the custom formats applied to the date columns.

Pandas Styler for Cell Coloring

Use df.style.applymap to set text colors or background colors for specific columns, and .background_gradient for gradient fills:

df_style = df.style.applymap(lambda x: 'color:red', subset=['Date and time'])\
    .applymap(lambda x: 'color:green', subset=['Dates only'])\
    .applymap(lambda x: 'background-color:#ADD8E6', subset=['Numbers'])\
    .background_gradient(cmap='PuBu', low=0, high=0.5, subset=['Percentage'])

Note that Styler styles are not preserved when writing to Excel, so they are useful mainly for HTML display.

Saving with xlsxwriter Engine

xlsxwriter is the default engine for to_excel. It allows column‑wise formatting via set_column and custom number formats:

writer = pd.ExcelWriter(
    "demo1.xlsx",
    engine='xlsxwriter',
    datetime_format='mmm d yyyy hh:mm:ss',
    date_format='mmmm dd yyyy'
)
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Set column widths
worksheet.set_column('A:A', 19)
worksheet.set_column('B:B', 17)

# Define number formats
format_num = workbook.add_format({'num_format': '#,##0.00'})
format_pct = workbook.add_format({'num_format': '0%'})
worksheet.set_column('C:C', 8, format_num)
worksheet.set_column('D:D', 11, format_pct)
worksheet.set_column('E:E', 6, format_num)

writer.save()

This produces an Excel file with proper column widths and number formatting.

Custom Styles with xlsxwriter

To create a styled header and apply borders to data cells:

import xlsxwriter

workbook = xlsxwriter.Workbook('demo2.xlsx')
worksheet = workbook.add_worksheet('sheet1')

header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#D7E4BC',
    'border': 1
})
worksheet.write_row(0, 0, df.columns, header_format)

format1 = workbook.add_format({'border': 1, 'num_format': 'mmm d yyyy hh:mm:ss'})
format2 = workbook.add_format({'border': 1, 'num_format': 'mmmm dd yyyy'})
format3 = workbook.add_format({'border': 1, 'num_format': '#,##0.00'})
format4 = workbook.add_format({'border': 1, 'num_format': '0%'})

worksheet.write_column(1, 0, df.iloc[:, 0], format1)
worksheet.write_column(1, 1, df.iloc[:, 1], format2)
worksheet.write_column(1, 2, df.iloc[:, 2], format3)
worksheet.write_column(1, 3, df.iloc[:, 3], format4)
worksheet.write_column(1, 4, df.iloc[:, 4], format3)

workbook.close()

Using openpyxl Engine

Switch to the openpyxl engine for more granular cell‑level styling:

writer = pd.ExcelWriter(
    "demo3.xlsx",
    engine='openpyxl',
    datetime_format='mmm d yyyy hh:mm:ss',
    date_format='mmmm dd yyyy'
)
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

Apply fonts, alignment, fills, and borders using openpyxl.styles objects and itertools.chain to iterate over cell ranges.

from openpyxl.styles import Alignment, Font, PatternFill, Border, Side
import itertools

font = Font(name='微软雅黑', bold=True)
alignment = Alignment(vertical='top', wrap_text=True)
fill = PatternFill(fill_type='solid', fgColor='D7E4BC')
side = Side(style='thin')
border = Border(left=side, right=side, top=side, bottom=side)

for cell in itertools.chain(*worksheet['A1:E1']):
    cell.font = font
    cell.alignment = alignment
    cell.fill = fill
    cell.border = border

for cell in itertools.chain(*worksheet['A2:E6']):
    cell.border = border

for cell in itertools.chain(*worksheet['C2:C6'], *worksheet['E2:E6']):
    cell.number_format = '#,##0.00'
for cell in itertools.chain(*worksheet['D2:D6']):
    cell.number_format = '0%'

worksheet.column_dimensions['A'].width = 20
worksheet.column_dimensions['B'].width = 17
worksheet.column_dimensions['C'].width = 10
worksheet.column_dimensions['D'].width = 12
worksheet.column_dimensions['E'].width = 8

writer.save()

Loading a Template with openpyxl

Open an existing Excel template, append DataFrame rows, and then apply borders and number formats to the new range:

from openpyxl import load_workbook
import itertools

workbook = load_workbook('template.xlsx')
worksheet = workbook['Sheet1']
for i, row in enumerate(df.values, 2):
    worksheet.append(row.tolist())

side = Side(style='thin')
border = Border(left=side, right=side, top=side, bottom=side)
for cell in itertools.chain(*worksheet[f"A2:E{len(df)+1}"]):
    cell.border = border

for cell in itertools.chain(*worksheet[f"A2:A{len(df)+1}"]):
    cell.number_format = 'mmm d yyyy hh:mm:ss'
for cell in itertools.chain(*worksheet[f"B2:B{len(df)+1}"]):
    cell.number_format = 'mmmm dd yyyy'
for cell in itertools.chain(*worksheet[f"C2:C{len(df)+1}"], *worksheet[f"E2:E{len(df)+1}"]):
    cell.number_format = '#,##0.00'
for cell in itertools.chain(*worksheet[f"D2:D{len(df)+1}"]):
    cell.number_format = '0%'

workbook.save('demo4.xlsx')

Auto‑Adjust Column Width

Calculate the maximum byte length of each column (using GBK encoding) and set the width accordingly for both xlsxwriter and openpyxl:

import numpy as np

# Header widths
header_widths = df.columns.to_series().apply(lambda x: len(x.encode('gbk'))).values
# Data widths
data_widths = df.astype(str).applymap(lambda x: len(x.encode('gbk'))).agg(max).values
widths = np.max([header_widths, data_widths], axis=0)

# xlsxwriter
writer = pd.ExcelWriter('auto_column_width1.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
ws = writer.sheets['Sheet1']
for i, w in enumerate(widths):
    ws.set_column(i, i, w)
writer.save()

# openpyxl
from openpyxl.utils import get_column_letter
writer = pd.ExcelWriter('auto_column_width2.xlsx', engine='openpyxl')
df.to_excel(writer, sheet_name='Sheet1', index=False)
ws = writer.sheets['Sheet1']
for i, w in enumerate(widths, 1):
    ws.column_dimensions[get_column_letter(i)].width = w + 1
writer.save()

Related Resources

Python‑Excel libraries: openpyxl, xlsxwriter, pyxlsb, pylightxl, xlrd, xlwt, xlutils, xlwings.

Official documentation links for each library.

Summary

The article demonstrates how to export Pandas DataFrames to Excel with various formatting options, compares the capabilities of the xlsxwriter and openpyxl engines, shows how to apply custom styles, use templates, and automatically adjust column widths, providing a comprehensive toolkit for Python‑based Excel reporting.