17.Excel报表自动化（下）：一键生成生产报表-尧图网络科技

一、问题背景：领导要我每天改Excel格式

"小张，这个报表的格式改一下。"

这句话我听了半年。每次领导有新想法，我就要重新调整Excel排版。

痛点：

1. 用MES导出的CSV数据，手动复制到Excel模板

2. 需要设置字体、颜色、边框、列宽

3. 要加标题行、日期、图表

4. 每周格式都会变

用Python解决：用openpyxl库，直接在代码里定义格式，一键生成。

---

二、技术原理：openpyxl基础

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border

# 创建工作簿
wb = Workbook()
ws = wb.active
ws.title = "生产日报"

# 设置样式
title_font = Font(name='微软雅黑', size=14, bold=True, color='FFFFFF')
title_fill = PatternFill(start_color='1976D2', end_color='1976D2', fill_type='solid')
header_font = Font(name='微软雅黑', size=11, bold=True)
center_align = Alignment(horizontal='center', vertical='center')

# 写入表头
ws['A1'] = 'FAB生产日报'
ws['A1'].font = title_font
ws['A1'].fill = title_fill
ws.merge_cells('A1:F1')

# 设置列宽
ws.column_dimensions['A'].width = 15

---

三、实战案例：完整的Excel报表生成器

"""
FAB生产报表自动生成器
功能：自动化生成专业的Excel生产报表
"""

import pandas as pd
import numpy as np
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.utils import get_column_letter
from datetime import datetime, timedelta
from pathlib import Path
from typing import Dict, Optional
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class FABReportWriter:
"""FAB Excel报表写入器"""

def __init__(self):
self.wb = Workbook()

# 颜色定义
self.primary_color = '1976D2'
self.warning_color = 'FF9800'
self.danger_color = 'F44336'
self.success_color = '4CAF50'
self.light_gray = 'F5F5F5'

# 字体定义
self.title_font = Font(name='微软雅黑', size=16, bold=True, color='FFFFFF')
self.header_font = Font(name='微软雅黑', size=11, bold=True, color='FFFFFF')
self.data_font = Font(name='微软雅黑', size=10)
self.number_font = Font(name='Consolas', size=10)

# 填充定义
self.header_fill = PatternFill(start_color=self.primary_color,
end_color=self.primary_color, fill_type='solid')
self.alt_fill = PatternFill(start_color=self.light_gray,
end_color=self.light_gray, fill_type='solid')
self.title_fill = PatternFill(start_color='1565C0',
end_color='1565C0', fill_type='solid')

# 对齐
self.center_align = Alignment(horizontal='center', vertical='center')
self.left_align = Alignment(horizontal='left', vertical='center')

# 边框
thin_border = Border(
left=Side(style='thin', color='BDBDBD'),
right=Side(style='thin', color='BDBDBD'),
top=Side(style='thin', color='BDBDBD'),
bottom=Side(style='thin', color='BDBDBD'),
)
self.cell_border = thin_border

def create_daily_report(self, data: pd.DataFrame,
report_date: datetime,
output_path: str) -> str:
"""
创建日报

参数:
data: 生产数据
report_date: 报告日期
output_path: 输出路径
"""
ws = self.wb.active
ws.title = "生产日报"

date_str = report_date.strftime('%Y年%m月%d日')

# === 标题行 ===
ws.merge_cells('A1:H1')
cell = ws['A1']
cell.value = f'FAB生产日报 - {date_str}'
cell.font = self.title_font
cell.fill = self.title_fill
cell.alignment = self.center_align
ws.row_dimensions[1].height = 35

# === 指标行 ===
ws.merge_cells('A2:D2')
ws.merge_cells('E2:H2')

total_lots = len(data)
avg_yield = data['yield_rate'].mean() if 'yield_rate' in data else 0

ws['A2'] = f'完成Lot: {total_lots}批'
ws['E2'] = f'平均良率: {avg_yield:.1f}%'
ws['A2'].font = Font(name='微软雅黑', size=12, bold=True)
ws['E2'].font = Font(name='微软雅黑', size=12, bold=True)

# === 数据表头 ===
headers = ['Lot ID', '工序', 'Wafer数', '平均厚度(A)', '厚度标准差',
'良率(%)', '周期(分钟)', '状态']

for col, header in enumerate(headers, 1):
cell = ws.cell(row=3, column=col, value=header)
cell.font = self.header_font
cell.fill = self.header_fill
cell.alignment = self.center_align
cell.border = self.cell_border

# === 数据 ===
for row_idx, (_, row) in enumerate(data.iterrows()):
excel_row = row_idx + 4
values = [
row.get('lot_id', ''),
row.get('process', ''),
row.get('wafer_count', 0),
row.get('thickness_avg', 0),
row.get('thickness_std', 0),
row.get('yield_rate', 0),
row.get('cycle_time', 0),
'正常' if row.get('yield_rate', 100) >= 90 else '异常',
]

for col, value in enumerate(values, 1):
cell = ws.cell(row=excel_row, column=col, value=value)
cell.font = self.data_font if col <= 2 else self.number_font
cell.alignment = self.center_align
cell.border = self.cell_border

# 交替行颜色
if row_idx % 2 == 1:
cell.fill = self.alt_fill

# 异常行标红
if col == 8 and value == '异常':
cell.font = Font(name='微软雅黑', size=10, bold=True,
color=self.danger_color)

# === 自动调整列宽 ===
for col in range(1, len(headers) + 1):
ws.column_dimensions[get_column_letter(col)].width = 15

# === 保存 ===
output_path = Path(output_path)
output_path.parent.mkdir(parents=True, exist_ok=True)
self.wb.save(str(output_path))
logger.info(f"报表已生成: {output_path}")

return str(output_path)

def add_charts(self, data: pd.DataFrame):
"""添加图表"""
ws = self.wb.create_sheet("图表")

# 图1：工序产量柱状图
chart = BarChart()
chart.type = "col"
chart.title = "工序产量分布"
chart.y_axis.title = "Lot数"
chart.x_axis.title = "工序"
chart.style = 10

# 工序统计
process_stats = data['process'].value_counts()
stats_data = [[proc, count] for proc, count in process_stats.items()]

# 写入临时数据
for i, (proc, count) in enumerate(stats_data, 1):
ws.cell(row=i, column=1, value=proc)
ws.cell(row=i, column=2, value=count)

data_ref = Reference(ws, min_col=2, min_row=1,
max_row=len(stats_data))
cats_ref = Reference(ws, min_col=1, min_row=1,
max_row=len(stats_data))
chart.add_data(data_ref, titles_from_data=False)
chart.set_categories(cats_ref)

ws.add_chart(chart, "D1")

def create_weekly_report(self, weekly_data: pd.DataFrame,
start_date: datetime,
output_path: str) -> str:
"""创建周报"""
ws = self.wb.create_sheet("周报")

start_str = start_date.strftime('%m/%d')
end_str = (start_date + timedelta(days=6)).strftime('%m/%d')

# 标题
ws.merge_cells('A1:G1')
ws['A1'] = f'FAB周报 - {start_str}~{end_str}'
ws['A1'].font = self.title_font
ws['A1'].fill = self.title_fill
ws['A1'].alignment = self.center_align

# 日产量趋势
daily_stats = weekly_data.groupby('date').size().reset_index()
daily_stats.columns = ['日期', '产量']

for i, (_, row) in enumerate(daily_stats.iterrows()):
ws.cell(row=3, column=1, value=row['日期'])
ws.cell(row=3, column=2, value=row['产量'])

self.wb.save(output_path)
return output_path

# 使用示例
if __name__ == '__main__':
# 模拟数据
np.random.seed(42)
n = 50

data = pd.DataFrame({
'lot_id': [f'FAB-{i:04d}' for i in range(n)],
'process': np.random.choice(['PHOTO', 'ETCH', 'CVD', 'CMP', 'IMP'], n),
'wafer_count': np.random.randint(12, 26, n),
'thickness_avg': 1250 + np.random.randn(n) * 3,
'thickness_std': 1 + np.random.rand(n) * 2,
'yield_rate': np.clip(95 + np.random.randn(n) * 3, 80, 100),
'cycle_time': np.random.randint(120, 360, n),
})

# 生成报表
writer = FABReportWriter()
writer.create_daily_report(
data,
datetime.now(),
'reports/daily_report.xlsx'
)

print("Excel报表已生成!")

---

四、效果对比

维度 | 手工Excel | Python自动 | 提升

|------|----------|-----------|------|

生成时间 | 30分钟 | 5秒 | 360倍

格式统一性 | 因人而异 | 完全统一 | 100%

图表更新 | 手动拖数据 | 自动刷新 | 100%

批量处理 | 困难（逐个改） | 一键生成10份 | 10倍

人工差错率 | 3%（手滑填错） | <0.1%（自动计算） | -97%

可定制性 | 中（Excel技能限制） | 高（任意Python逻辑） | ∞

我在项目中的效果：把日报、周报、月报全部自动化后，每周节省10小时。而且格式统一后，领导再也不说"这个表格式怎么跟上次不一样了"。

---

五、实施建议

Excel报表自动化三阶段

第一阶段：半自动化（1-2天）

不要一上来就搞全自动。先用Python生成数据部分（pandas+openpyxl写入数据），格式调整（合并单元格、颜色、字体）暂时手工做。

为什么先这样做？因为你还不确定报表的最终格式，自动化太早会浪费时间调整代码。先用半自动跑1-2周，确认格式稳定了再完全自动化。

第二阶段：模板化（2-3天）

确定格式后，把样式封装到模板函数里：

# 定义常用样式
HEADER_FILL = PatternFill(start_color='4472C4', fill_type='solid')
HEADER_FONT = Font(bold=True, color='FFFFFF', size=11)
DATA_FONT = Font(size=10)
THIN_BORDER = Border(
left=Side(style='thin'), right=Side(style='thin'),
top=Side(style='thin'), bottom=Side(style='thin')
)

def apply_header_style(cell):
cell.fill = HEADER_FILL
cell.font = HEADER_FONT
cell.alignment = Alignment(horizontal='center')
cell.border = THIN_BORDER

这样定义一次样式，所有报表复用，格式100%统一。

第三阶段：完全自动化（3-5天）

用定时任务（Windows任务计划程序或Python schedule库）每天自动生成报表，并通过邮件发送给相关人员。