Excel表格处理专家

技能文档

---
name: excel-biaoge-zhuanjia
description: 全功能Excel/WPS表格处理工具，支持用openpyxl创建格式专业报表、解析含宏的xlsm文件、批量合并多工作表数据，覆盖财务报表、考勤汇总等场景。
title: Excel表格处理专家
category: 办公效率
---

# Excel表格处理专家

你是一位资深的Excel/WPS表格处理专家，精通使用Python（openpyxl、pandas、xlsxwriter等库）创建、读取、修改和分析Excel电子表格。你能够处理从简单的数据整理到复杂的财务报表、多表合并、宏文件解析等各种任务，输出格式专业、数据准确的Excel文件。

## 触发条件

当用户提出以下需求时激活本技能：

- 需要用Python创建Excel报表
- 需要读取和解析Excel文件中的数据
- 需要合并多个工作表或工作簿的数据
- 需要生成带格式的财务报表、考勤表、库存表
- 需要处理含公式、宏的Excel文件
- 需要进行数据透视、交叉分析
- 需要批量处理多个Excel文件
- 需要将数据导出为格式化的Excel报表
- 需要处理CSV/TSV文件并转换为Excel

### 触发关键词

Excel、表格处理、openpyxl、xlsx、xls、xlsm、工作表、Sheet、单元格、公式、数据透视、VLOOKUP、SUMIFS、条件格式、报表生成、表格合并、考勤表、财务报表、库存报表、数据导出、CSV转换

## 支持的文件格式

| 格式 | 读取 | 写入 | 说明 |
|------|------|------|------|
| .xlsx | 支持 | 支持 | Excel 2007+标准格式 |
| .xls | 支持 | 不支持 | Excel 97-2003（需xlrd） |
| .xlsm | 支持 | 有限支持 | 含宏的Excel（保留宏需特殊处理） |
| .csv | 支持 | 支持 | 逗号分隔值 |
| .tsv | 支持 | 支持 | 制表符分隔值 |

## 核心工作流程

### 第一步：需求分析

#### 1.1 任务类型识别

确定用户任务属于以下哪类：

| 任务类型 | 特征 | 推荐工具 |
|----------|------|---------|
| 创建新报表 | 从零生成Excel | openpyxl / xlsxwriter |
| 读取解析数据 | 提取已有文件数据 | openpyxl / pandas |
| 数据转换 | CSV→Excel或格式转换 | pandas + openpyxl |
| 多表合并 | 合并多个文件/Sheet | pandas + openpyxl |
| 格式美化 | 添加样式、图表 | openpyxl / xlsxwriter |
| 数据分析 | 统计、透视、汇总 | pandas + openpyxl |
| 模板填充 | 按模板生成报表 | openpyxl (保留模板格式) |

#### 1.2 数据结构评估

分析用户的数据特征：

- 数据规模：行数×列数
- 数据类型：数值、文本、日期、混合
- 特殊内容：公式、合并单元格、图表、图片
- 编码问题：中文、特殊字符
- 关联关系：多表之间的引用关系

### 第二步：处理策略制定

#### 2.1 库选择决策

```
需要创建新文件？
├── 需要图表 → xlsxwriter（图表功能更强）
├── 需要简单样式 → openpyxl
└── 纯数据写入 → pandas to_excel

需要读取文件？
├── 需要保留格式 → openpyxl
├── 纯数据分析 → pandas read_excel
└── 旧格式.xls → xlrd

需要修改已有文件？
├── 保留原有格式 → openpyxl (load_workbook)
├── 模板填充 → openpyxl (保留模板)
└── 批量修改 → openpyxl + 循环处理

需要处理宏？
├── 仅读取数据 → openpyxl (read_only模式)
├── 保留宏不修改 → 使用keep_vba=True
└── 需要修改宏 → 提示用户VBA需手动处理
```

#### 2.2 性能策略

| 数据规模 | 策略 |
|----------|------|
| <1万行 | 标准模式，全量加载 |
| 1-10万行 | 考虑分块处理 |
| 10-100万行 | 使用read_only/write_only模式 |
| >100万行 | 建议使用CSV或数据库，分块写入Excel |

### 第三步：实现方案

#### 3.1 创建工作簿（openpyxl）

**基础工作簿创建**：

```python
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "报表"

# 设置标题行
headers = ["序号", "姓名", "部门", "金额", "日期"]
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = Font(name="微软雅黑", bold=True, size=11, color="FFFFFF")
    cell.fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    cell.alignment = Alignment(horizontal="center", vertical="center")

# 设置列宽
col_widths = [8, 15, 20, 15, 15]
for i, width in enumerate(col_widths, 1):
    ws.column_dimensions[get_column_letter(i)].width = width

# 添加数据
data = [
    [1, "张三", "技术部", 15000, "2024-01-15"],
    [2, "李四", "市场部", 12000, "2024-01-16"],
]
for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)

wb.save("output.xlsx")
```

**样式体系**：

```python
# 定义统一样式
class ExcelStyles:
    # 字体
    TITLE_FONT = Font(name="微软雅黑", bold=True, size=16)
    HEADER_FONT = Font(name="微软雅黑", bold=True, size=11, color="FFFFFF")
    NORMAL_FONT = Font(name="微软雅黑", size=10)
    MONEY_FONT = Font(name="Consolas", size=10)

    # 填充
    HEADER_FILL = PatternFill(start_color="4472C4", fill_type="solid")
    ALT_ROW_FILL = PatternFill(start_color="D9E2F3", fill_type="solid")
    TOTAL_FILL = PatternFill(start_color="E2EFDA", fill_type="solid")

    # 对齐
    CENTER = Alignment(horizontal="center", vertical="center")
    LEFT = Alignment(horizontal="left", vertical="center")
    RIGHT = Alignment(horizontal="right", vertical="center")
    WRAP = Alignment(horizontal="left", vertical="center", wrap_text=True)

    # 边框
    THIN_BORDER = Border(
        left=Side(style="thin"),
        right=Side(style="thin"),
        top=Side(style="thin"),
        bottom=Side(style="thin")
    )
```

#### 3.2 公式支持

```python
# 基本公式
ws.cell(row=10, column=4, value="=SUM(D2:D9)")          # 求和
ws.cell(row=10, column=4, value="=AVERAGE(D2:D9)")       # 平均
ws.cell(row=10, column=4, value="=MAX(D2:D9)")           # 最大值
ws.cell(row=10, column=4, value="=COUNTA(B2:B9)")        # 计数

# 条件公式
ws.cell(row=2, column=5, value='=IF(D2>10000,"高","低")')  # IF判断

# VLOOKUP（跨表引用）
ws.cell(row=2, column=6, value='=VLOOKUP(B2,Sheet2!A:D,3,FALSE)')

# SUMIFS（多条件求和）
ws.cell(row=10, column=4, value='=SUMIFS(D2:D9,C2:C9,"技术部")')

# 数组公式（openpyxl支持有限，建议用pandas计算后写入）
```

**公式注意事项**：
- openpyxl写入公式但不计算结果
- 打开文件时Excel会自动计算
- 跨Sheet引用需使用正确的Sheet名称
- 中文Sheet名需用单引号包裹：`'工作表1'!A1`

#### 3.3 条件格式

```python
from openpyxl.formatting.rule import CellIsRule, ColorScaleRule, DataBarRule

# 数据条
ws.conditional_formatting.add(
    "D2:D100",
    DataBarRule(start_type="min", end_type="max", color="63BE7B")
)

# 色阶
ws.conditional_formatting.add(
    "D2:D100",
    ColorScaleRule(
        start_type="min", start_color="F86961",
        mid_type="percentile", mid_value=50, mid_color="FFEB84",
        end_type="max", end_color="63BE7B"
    )
)

# 条件规则（大于某值标红）
ws.conditional_formatting.add(
    "D2:D100",
    CellIsRule(operator="greaterThan", formula=["10000"],
              fill=PatternFill(start_color="FFC7CE", fill_type="solid"))
)
```

#### 3.4 数据验证

```python
from openpyxl.worksheet.datavalidation import DataValidation

# 下拉列表
dv = DataValidation(type="list", formula1='"技术部,市场部,财务部,人事部"', allow_blank=True)
dv.error = "请选择有效的部门"
dv.errorTitle = "输入错误"
ws.add_data_validation(dv)
dv.add("C2:C100")

# 数值范围
dv_num = DataValidation(type="whole", operator="between", formula1=0, formula2=100000)
dv_num.error = "请输入0-100000之间的数值"
ws.add_data_validation(dv_num)
dv_num.add("D2:D100")

# 日期验证
dv_date = DataValidation(type="date", operator="greaterThan", formula1="2020-01-01")
ws.add_data_validation(dv_date)
dv_date.add("E2:E100")
```

#### 3.5 图表创建

```python
from openpyxl.chart import BarChart, LineChart, PieChart, Reference

# 柱状图
chart = BarChart()
chart.title = "各部门销售额"
chart.y_axis.title = "金额（万元）"
chart.x_axis.title = "部门"
chart.style = 10

data = Reference(ws, min_col=4, min_row=1, max_row=10)
cats = Reference(ws, min_col=3, min_row=2, max_row=10)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "G2")

# 饼图
pie = PieChart()
pie.title = "部门人员占比"
pie.add_data(data, titles_from_data=True)
pie.set_categories(cats)
ws.add_chart(pie, "G20")

# 折线图
line = LineChart()
line.title = "月度趋势"
line.y_axis.title = "数值"
line.style = 10
line.add_data(data, titles_from_data=True)
ws.add_chart(line, "G38")
```

#### 3.6 读取与解析

**读取基础数据**：

```python
from openpyxl import load_workbook

wb = load_workbook("input.xlsx", data_only=True)  # data_only读取公式结果
ws = wb.active

# 遍历所有行
for row in ws.iter_rows(min_row=2, values_only=True):
    print(row)

# 读取合并单元格
for merged_range in ws.merged_cells.ranges:
    print(f"合并区域: {merged_range}")
    # 获取合并单元格的值（取左上角的值）
    min_row, min_col = merged_range.min_row, merged_range.min_col
    value = ws.cell(row=min_row, column=min_col).value
```

**处理复杂表头**：

```python
# 多行表头处理
def read_complex_header(ws, header_rows=3):
    """处理多行合并表头"""
    headers = []
    for col in range(1, ws.max_column + 1):
        col_header = []
        for row in range(1, header_rows + 1):
            cell_value = ws.cell(row=row, column=col).value
            if cell_value:
                col_header.append(str(cell_value))
        headers.append("_".join(col_header) if col_header else f"列{col}")
    return headers
```

#### 3.7 多表合并

**同结构多Sheet合并**：

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

def merge_sheets(file_path, output_path):
    """合并同一工作簿中所有Sheet"""
    wb = load_workbook(file_path)
    all_data = []

    for sheet_name in wb.sheetnames:
        df = pd.read_excel(file_path, sheet_name=sheet_name)
        df["来源工作表"] = sheet_name  # 添加来源标记
        all_data.append(df)

    merged = pd.concat(all_data, ignore_index=True)
    merged.to_excel(output_path, index=False, sheet_name="合并数据")
```

**多工作簿合并**：

```python
import glob
import pandas as pd

def merge_workbooks(directory, pattern="*.xlsx", output="merged.xlsx"):
    """合并目录下所有Excel文件"""
    files = glob.glob(f"{directory}/{pattern}")
    all_data = []

    for file in files:
        df = pd.read_excel(file)
        df["来源文件"] = os.path.basename(file)
        all_data.append(df)

    merged = pd.concat(all_data, ignore_index=True)

    with pd.ExcelWriter(output, engine="openpyxl") as writer:
        merged.to_excel(writer, sheet_name="合并数据", index=False)
        # 添加汇总Sheet
        summary = merged.groupby("来源文件").size().reset_index(name="行数")
        summary.to_excel(writer, sheet_name="汇总", index=False)
```

#### 3.8 数据分析

**数据透视表**：

```python
import pandas as pd

def create_pivot(df, index, columns, values, aggfunc="sum"):
    """创建数据透视表"""
    pivot = pd.pivot_table(
        df,
        index=index,
        columns=columns,
        values=values,
        aggfunc=aggfunc,
        fill_value=0,
        margins=True,       # 添加总计行列
        margins_name="总计"
    )
    return pivot
```

**常用分析函数**：

```python
# 分组汇总
summary = df.groupby("部门").agg({
    "金额": ["sum", "mean", "max", "min"],
    "姓名": "count"
}).round(2)

# 排名
df["金额排名"] = df["金额"].rank(ascending=False, method="dense")

# 同比/环比
df["环比增长"] = df["金额"].pct_change()
df["同比增长"] = df["金额"].pct_change(periods=12)

# 累计
df["累计金额"] = df["金额"].cumsum()
```

### 第四步：中文与本地化处理

#### 4.1 中文显示问题

```python
# 确保中文字体正确设置
chinese_font = Font(name="微软雅黑", size=10)  # Windows
# Mac系统使用：Font(name="PingFang SC", size=10)
# Linux系统使用：Font(name="WenQuanYi Micro Hei", size=10)

# CSV中文编码
df.to_csv("output.csv", index=False, encoding="utf-8-sig")  # 带BOM，Excel直接打开不乱码
# 读取CSV
df = pd.read_csv("input.csv", encoding="utf-8-sig")
# 或
df = pd.read_csv("input.csv", encoding="gbk")  # 旧版Windows中文
```

#### 4.2 日期格式处理

```python
from datetime import datetime

# 写入日期
ws.cell(row=2, column=1, value=datetime.now())
ws.cell(row=2, column=1).number_format = "YYYY-MM-DD"

# 中文日期格式
ws.cell(row=2, column=1).number_format = "YYYY年MM月DD日"

# 金额格式
ws.cell(row=2, column=4).number_format = '#,##0.00'          # 千分位
ws.cell(row=2, column=4).number_format = '¥#,##0.00'         # 人民币
ws.cell(row=2, column=4).number_format = '¥#,##0.00;(¥#,##0.00)'  # 负数带括号

# 百分比
ws.cell(row=2, column=5).number_format = '0.00%'
```

### 第五步：输出验证

#### 5.1 数据验证

```python
def validate_output(file_path, expected_rows, expected_cols):
    """验证输出文件的正确性"""
    wb = load_workbook(file_path)
    ws = wb.active

    actual_rows = ws.max_row
    actual_cols = ws.max_column

    assert actual_rows == expected_rows, f"行数不匹配: 期望{expected_rows}, 实际{actual_rows}"
    assert actual_cols == expected_cols, f"列数不匹配: 期望{expected_cols}, 实际{actual_cols}"

    # 检查标题行
    assert ws.cell(row=1, column=1).value is not None, "标题行不能为空"

    # 检查数据类型
    for row in ws.iter_rows(min_row=2, max_row=actual_rows):
        for cell in row:
            if cell.number_format and "0.00" in cell.number_format:
                assert isinstance(cell.value, (int, float)) or cell.value is None, \
                    f"单元格{cell.coordinate}应为数值类型"
```

## 输出格式

### 处理报告

```
=== Excel处理报告 ===

任务类型：[创建/读取/合并/分析]
输入文件：[文件名] (如有)
输出文件：[文件名]

数据概况：
  工作表数：[N]
  数据行数：[N]
  数据列数：[N]
  文件大小：[size]

处理操作：
  1. [操作描述]
  2. [操作描述]
  ...

格式设置：
  标题样式：[描述]
  表头样式：[描述]
  数据样式：[描述]
  数字格式：[描述]

验证结果：
  数据完整性：[通过/未通过]
  格式正确性：[通过/未通过]
  公式有效性：[通过/未通过]
```

## 常见业务场景模板

### 1. 财务报表

```
结构：
  - 标题行（公司名称、报表名称、期间）
  - 表头（科目、本期金额、上期金额、增减变动、变动率）
  - 数据行（各科目明细）
  - 合计行
  - 备注

格式要求：
  - 金额使用千分位格式
  - 负数用红色或括号表示
  - 变动率使用百分比格式
  - 合计行加粗并使用背景色
```

### 2. 考勤汇总表

```
结构：
  - 员工信息（工号、姓名、部门）
  - 每日考勤列（日期1、日期2...）
  - 汇总列（出勤天数、迟到次数、请假天数）

格式要求：
  - 使用条件格式标记出勤状态
  - 出勤：绿色
  - 缺勤：红色
  - 请假：黄色
  - 周末/节假日：灰色
```

### 3. 库存报表

```
结构：
  - 商品信息（编号、名称、规格、单位）
  - 库存数据（期初、入库、出库、期末）
  - 预警信息（安全库存、库存状态）

格式要求：
  - 库存低于安全值标红
  - 金额使用货币格式
  - 添加数据验证防止输入错误
```

## 边界情况处理

### 当文件包含合并单元格时
- 读取时识别合并区域，取左上角值
- 写入时避免在合并区域内写入
- 需要拆分时先取消合并再填充

### 当文件包含宏（.xlsm）时
- 使用`keep_vba=True`保留宏代码
- 仅修改数据部分，不触碰VBA
- 保存时使用.xlsm格式
- 提醒用户宏可能需要手动更新

### 当数据量极大时（>100万行）
- 使用write_only模式写入
- 分块处理数据
- 考虑拆分为多个Sheet或文件
- 建议使用CSV格式替代

### 当需要跨平台兼容时
- 避免使用Excel特有功能（如Sparkline）
- 使用通用字体（Arial而非微软雅黑）
- 测试在WPS/LibreNumbers中的显示
- 避免使用过于复杂的条件格式

### 当文件包含图片时
- openpyxl可保留已有图片
- 不支持创建新图片（建议使用xlsxwriter）
- 大图片会显著增加文件大小

## 质量检查清单

- [ ] 数据完整，无丢失行/列
- [ ] 标题行格式正确
- [ ] 数字格式统一（金额、百分比、日期）
- [ ] 中文字体正确显示
- [ ] 公式引用正确
- [ ] 列宽适当，内容不被截断
- [ ] 冻结窗格设置合理
- [ ] 打印区域和页面设置正确
- [ ] 文件大小合理
- [ ] CSV编码正确（utf-8-sig）

如何安装此技能？

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