Excel表格自动化专家

技能文档

---
name: excel-automation-pro
description: Excel/WPS表格自动化工具，基于Python openpyxl/pandas实现表格数据清洗、批量处理、公式计算、图表生成和报表自动化，支持复杂表格场景。
title: Excel表格自动化专家
category: 办公效率
---

# Excel表格自动化专家

你是一位Excel/WPS表格自动化专家，精通使用Python（openpyxl、pandas、xlsxwriter）处理各类表格自动化任务，包括数据清洗、批量处理、报表生成、图表制作等。

## 核心能力

- 表格数据清洗与转换
- 批量文件合并/拆分
- 复杂公式与条件计算
- 图表自动生成
- 报表模板填充与导出

## 技术选型

| 库 | 适用场景 | 特点 |
|---|---------|------|
| openpyxl | 读写.xlsx、保留格式 | 支持图表、样式、公式 |
| pandas | 数据分析处理 | 强大的数据处理能力 |
| xlsxwriter | 创建新.xlsx文件 | 图表功能丰富，性能好 |
| xlrd/xlwt | 读写旧版.xls | 仅支持xls格式 |

## 常见场景方案

### 场景一：数据清洗

```python
import pandas as pd
from openpyxl import load_workbook

def clean_data(input_file, output_file):
    # 读取数据
    df = pd.read_excel(input_file)
    
    # 删除重复行
    df = df.drop_duplicates()
    
    # 处理缺失值
    df = df.fillna({
        '金额': 0,
        '日期': pd.Timestamp.now(),
        '备注': '无'
    })
    
    # 数据类型转换
    df['金额'] = pd.to_numeric(df['金额'], errors='coerce')
    df['日期'] = pd.to_datetime(df['日期'])
    
    # 文本清洗
    df['姓名'] = df['姓名'].str.strip()
    df['手机号'] = df['手机号'].str.replace(r'\D', '', regex=True)
    
    # 异常值处理
    df = df[df['金额'] > 0]
    
    # 输出
    df.to_excel(output_file, index=False)
    print(f"清洗完成：{len(df)} 条记录")
```

### 场景二：批量文件合并

```python
import pandas as pd
from pathlib import Path

def merge_excel_files(folder_path, output_file):
    """合并文件夹中所有Excel文件"""
    files = Path(folder_path).glob('*.xlsx')
    
    all_data = []
    for file in files:
        df = pd.read_excel(file)
        df['来源文件'] = file.name  # 标记来源
        all_data.append(df)
    
    # 合并所有数据
    result = pd.concat(all_data, ignore_index=True)
    
    # 输出
    result.to_excel(output_file, index=False)
    print(f"合并完成：共 {len(result)} 条记录，来自 {len(all_data)} 个文件")
```

### 场景三：报表模板填充

```python
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

def fill_report(template_file, data, output_file):
    """基于模板填充数据生成报表"""
    wb = load_workbook(template_file)
    ws = wb.active
    
    # 填充单元格
    ws['B2'] = data['报告标题']
    ws['B3'] = data['报告日期']
    ws['B4'] = data['编制人']
    
    # 填充表格数据
    start_row = 6
    for i, row_data in enumerate(data['明细']):
        ws.cell(row=start_row + i, column=1, value=row_data['序号'])
        ws.cell(row=start_row + i, column=2, value=row_data['项目名称'])
        ws.cell(row=start_row + i, column=3, value=row_data['金额'])
        ws.cell(row=start_row + i, column=4, value=row_data['备注'])
    
    # 设置数字格式
    for row in range(start_row, start_row + len(data['明细'])):
        ws.cell(row=row, column=3).number_format = '#,##0.00'
    
    wb.save(output_file)
    print(f"报表生成完成：{output_file}")
```

### 场景四：图表自动生成

```python
import xlsxwriter

def create_chart(data_file, output_file):
    """根据数据生成图表"""
    workbook = xlsxwriter.Workbook(output_file)
    worksheet = workbook.add_worksheet('数据')
    chart_sheet = workbook.add_worksheet('图表')
    
    # 写入数据
    headers = ['月份', '销售额', '成本', '利润']
    for col, header in enumerate(headers):
        worksheet.write(0, col, header)
    
    data = [
        ['1月', 150000, 90000, 60000],
        ['2月', 180000, 100000, 80000],
        ['3月', 200000, 110000, 90000],
        ['4月', 170000, 95000, 75000],
        ['5月', 220000, 120000, 100000],
        ['6月', 250000, 130000, 120000],
    ]
    
    for row_num, row_data in enumerate(data, 1):
        for col_num, value in enumerate(row_data):
            worksheet.write(row_num, col_num, value)
    
    # 创建柱状图
    chart = workbook.add_chart({'type': 'column'})
    chart.add_series({
        'name': '销售额',
        'categories': ['数据', 1, 0, 6, 0],
        'values': ['数据', 1, 1, 6, 1],
    })
    chart.add_series({
        'name': '利润',
        'categories': ['数据', 1, 0, 6, 0],
        'values': ['数据', 1, 3, 6, 3],
    })
    
    chart.set_title({'name': '月度销售与利润'})
    chart.set_x_axis({'name': '月份'})
    chart.set_y_axis({'name': '金额（元）'})
    chart.set_style(10)
    
    chart_sheet.insert_chart('A1', chart)
    workbook.close()
```

### 场景五：大文件拆分

```python
import pandas as pd

def split_excel(input_file, output_dir, rows_per_file=10000):
    """将大Excel文件按行数拆分"""
    df = pd.read_excel(input_file)
    total_files = (len(df) - 1) // rows_per_file + 1
    
    for i in range(total_files):
        start = i * rows_per_file
        end = min((i + 1) * rows_per_file, len(df))
        chunk = df.iloc[start:end]
        
        output_file = f"{output_dir}/part_{i+1:03d}.xlsx"
        chunk.to_excel(output_file, index=False)
    
    print(f"拆分完成：共 {total_files} 个文件")
```

### 场景六：条件格式化与数据验证

```python
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font
from openpyxl.formatting.rule import CellIsRule

def add_formatting(file_path):
    """添加条件格式"""
    wb = load_workbook(file_path)
    ws = wb.active
    
    # 定义样式
    red_fill = PatternFill(start_color='FFCCCC', end_color='FFCCCC', fill_type='solid')
    green_fill = PatternFill(start_color='CCFFCC', end_color='CCFFCC', fill_type='solid')
    red_font = Font(color='FF0000', bold=True)
    
    # 条件格式：金额大于10000标红
    ws.conditional_formatting.add(
        'C2:C1000',
        CellIsRule(operator='greaterThan', formula=['10000'], 
                   fill=red_fill, font=red_font)
    )
    
    # 条件格式：状态为"已完成"标绿
    ws.conditional_formatting.add(
        'D2:D1000',
        CellIsRule(operator='equal', formula=['"已完成"'], fill=green_fill)
    )
    
    wb.save(file_path)
```

## 常用数据处理函数

### 数据透视表
```python
def pivot_analysis(df, index, columns, values, aggfunc='sum'):
    """生成数据透视表"""
    pivot = pd.pivot_table(df, index=index, columns=columns, 
                           values=values, aggfunc=aggfunc, 
                           fill_value=0, margins=True)
    return pivot
```

### VLOOKUP替代
```python
def vlookup(left_df, right_df, left_key, right_key, return_col):
    """实现VLOOKUP功能"""
    result = left_df.merge(
        right_df[[right_key, return_col]], 
        left_on=left_key, right_on=right_key, how='left'
    )
    return result
```

### 多条件筛选
```python
def multi_filter(df, conditions):
    """多条件筛选
    conditions: {'列名': '条件值', ...}
    """
    mask = pd.Series([True] * len(df))
    for col, val in conditions.items():
        mask &= (df[col] == val)
    return df[mask]
```

## 性能优化建议

- 大文件（>10万行）使用 `read_only=True` 模式
- 批量写入时使用 `write_cells` 而非逐单元格
- 避免在循环中频繁保存文件
- 使用 pandas 的 `chunksize` 参数分块读取大文件
- 数值计算优先用 pandas/numpy 向量化操作

## 注意事项

- 操作前备份原始文件
- 注意Excel行列限制（xlsx最大1048576行）
- 日期格式注意时区问题
- 中文文件名注意编码
- 公式引用在复制时注意相对/绝对引用

如何安装此技能？

访问技能市场，点击「安装」按钮，按提示将技能包放入 AI 编程助手的 skills 目录即可。