Automate Excel Reports with Python: Step-by-Step Guide Using Pandas & openpyxl

Excel's Basic Components

When creating reports, we usually start from Excel, which is essentially a workbook containing multiple sheets, each sheet composed of cells with various properties such as font, alignment, and conditional formatting.

The book structures its content according to Excel's menu modules.

A Workflow for an Automated Report

The automation process consists of five steps:

Break down the report tasks (data collection, regardless of source).

Identify the code needed for each sub‑task (e.g., data import, duplicate removal).

Combine the code snippets into a complete script.

Validate the generated report results.

Execute the script when the report is needed.

Automation essentially means letting the machine replace manual work by translating each human step into code.

Report Automation Practice

Daily Metrics Comparison

First, calculate same‑period and month‑over‑month metrics with pandas:

# Import file
import pandas as pd
df = pd.read_excel(r'D:\Data-Science\share\excel-python报表自动化\sale_data.xlsx')

# Function to get counts for a given date

def get_data(date):
    create_cnt = df[df['创建日期'] == date]['order_id'].count()
    pay_cnt = df[df['付款日期'] == date]['order_id'].count()
    receive_cnt = df[df['收货日期'] == date]['order_id'].count()
    return_cnt = df[df['退款日期'] == date]['order_id'].count()
    return create_cnt, pay_cnt, receive_cnt, return_cnt

# Example for 2021‑04‑11 and compare with previous dates

df_view = pd.DataFrame([
    get_data('2021-04-11'),
    get_data('2021-04-10'),
    get_data('2021-04-04')
], columns=['创建订单量','付款订单量','收货订单量','退款订单量'], index=['当日','昨日','上周同期']).T

df_view['环比'] = df_view['当日'] / df_view['昨日'] - 1
df_view['同比'] = df_view['当日'] / df_view['上周同期'] - 1
df_view

The resulting DataFrame is then written to Excel with openpyxl, applying formatting such as fonts, alignment, borders, header styling, percentage formats, and column widths:

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

wb = Workbook()
ws = wb.active

for r in dataframe_to_rows(df_view, index=True, header=True):
    ws.append(r)

# Remove the empty second row
ws.delete_rows(2)
ws['A1'] = '指标'
ws.insert_rows(1)
ws['A1'] = '电商业务方向 2021/4/11 日报'
ws.merge_cells('A1:F1')

# Apply styles to rows 1‑6
for row in ws['A1':'F6']:
    for c in row:
        c.font = Font(name='微软雅黑', size=12)
        c.alignment = Alignment(horizontal='center')
        c.border = Border(left=Side(border_style='thin', color='FF000000'),
                         right=Side(border_style='thin', color='FF000000'),
                         top=Side(border_style='thin', color='FF000000'),
                         bottom=Side(border_style='thin', color='FF000000'))

# Header row styling
for row in ws[1:2]:
    for c in row:
        c.font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFFFF')
        c.fill = PatternFill(fill_type='solid', start_color='FFFF6100')

# Percentage format for columns E and F
for col in ws['E':'F']:
    for cell in col:
        cell.number_format = '0.00%'

ws.column_dimensions['A'].width = 13
ws.column_dimensions['E'].width = 10
wb.save(r'D:\Data-Science\share\excel-python报表自动化\核心指标.xlsx')

Province‑wise Order Creation

Using pandas to aggregate orders by province for a specific date:

df_province = pd.DataFrame(df[df['创建日期'] == '2021-04-11']
                           .groupby('省份')['order_id'].count())
df_province = df_province.reset_index()
df_province = df_province.sort_values(by='order_id', ascending=False)
df_province = df_province.rename(columns={'order_id':'创建订单量'})
df_province

After writing the DataFrame to Excel, additional formatting and a data‑bar conditional format are applied:

from openpyxl.styles import colors, Font, PatternFill, Border, Side, Alignment
from openpyxl.formatting.rule import DataBarRule

wb = Workbook()
ws = wb.active

for r in dataframe_to_rows(df_province, index=False, header=True):
    ws.append(r)

# Apply font, alignment, border to rows 1‑11
for row in ws[1:11]:
    for c in row:
        c.font = Font(name='微软雅黑', size=12)
        c.alignment = Alignment(horizontal='center')
        c.border = Border(left=Side(border_style='thin', color='FF000000'),
                         right=Side(border_style='thin', color='FF000000'),
                         top=Side(border_style='thin', color='FF000000'),
                         bottom=Side(border_style='thin', color='FF000000'))

# Header styling
for c in ws[1]:
    c.font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFFFF')
    c.fill = PatternFill(fill_type='solid', start_color='FFFF6100')

# Data bar for order counts
rule = DataBarRule(start_type='min', end_type='max', color='FF638EC6', showValue=True)
ws.conditional_formatting.add('B1:B11', rule)

ws.column_dimensions['A'].width = 17
ws.column_dimensions['B'].width = 13
wb.save(r'D:\Data-Science\share\excel-python报表自动化\各省份销量情况.xlsx')

Trend Over Recent Days

Plotting the daily order creation trend with matplotlib and inserting the chart into Excel:

%matplotlib inline
import matplotlib.pyplot as plt
plt.rcParams["font.sans-serif"] = 'SimHei'  # fix Chinese characters
plt.figure(figsize=(10,6))
df.groupby('创建日期')['order_id'].count().plot()
plt.title('4.2 - 4.11 创建订单量分日趋势')
plt.xlabel('日期')
plt.ylabel('订单量')
plt.savefig(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png')

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
ws = wb.active
img = Image(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png')
ws.add_image(img, 'A1')
wb.save(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.xlsx')

Merging Different Results

To combine multiple result tables into a single sheet, the script first appends the core metrics, then inserts the province table by iterating over rows and columns, and finally adds the trend chart. Formatting presets (fonts, alignment, borders, fills) are reused to keep the appearance consistent.

Alternatively, separate sheets can be created for each result:

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws1 = wb.create_sheet()
ws2 = wb.create_sheet()
ws.title = "核心指标"
ws1.title = "各省份销情况"
ws2.title = "分日趋势"
# Append dataframes to respective sheets (code omitted for brevity)
wb.save(r'D:\Data-Science\share\excel-python报表自动化\多结果合并_多Sheet.xlsx')

Running the complete script produces a polished Excel workbook that can be generated on demand or scheduled for automatic delivery.