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报表自动化三阶段