用Python处理Excel：从读取到自动生成的完整实战

📂 文章

为什么用Python操作Excel

Excel是办公最常用的工具。但当数据量超过1万行，或者需要每天重复同样的操作时，手动处理就变成了噩梦。

Python操作Excel有两个核心库：

openpyxl：直接操作Excel文件，能读写单元格、设置格式、插入图表。适合需要保留原始格式的场景。

pandas：数据分析利器，擅长数据筛选、统计、合并。适合数据量大、需要计算的场景。

实际工作中，两个库配合使用效果最好。

场景一：读取Excel数据

import openpyxl

# 打开文件
wb = openpyxl.load_workbook("销售数据.xlsx")
sheet = wb.active  # 获取第一个工作表

# 读取单个单元格
name = sheet["A1"].value
print(f"表头：{name}")

# 读取整行
row_data = []
for cell in sheet[1]:
    row_data.append(cell.value)
print(f"第一行：{row_data}")

# 读取所有数据
all_data = []
for row in sheet.iter_rows(min_row=2, values_only=True):
    all_data.append(list(row))
print(f"共 {len(all_data)} 行数据")

场景二：数据筛选与统计（pandas）

import pandas as pd

# 读取Excel
df = pd.read_excel("销售数据.xlsx")

# 查看基本信息
print(df.head())        # 前5行
print(df.shape)         # 行列数
print(df.dtypes)        # 数据类型

# 筛选：销售额大于1万的记录
high_sales = df[df["销售额"] > 10000]
print(f"大额订单：{len(high_sales)} 笔")

# 分组统计：每个地区的总销售额
region_sum = df.groupby("地区")["销售额"].sum()
print(region_sum.sort_values(ascending=False))

# 交叉统计：每个地区每个月的订单数
cross = pd.crosstab(df["地区"], df["月份"])
print(cross)

场景三：自动生成报表

import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side
from datetime import datetime

def create_report(data, output_file):
    """自动生成销售日报表"""
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "日报"
    
    # 设置标题
    ws.merge_cells("A1:E1")
    title_cell = ws["A1"]
    title_cell.value = f"销售日报 - {datetime.now().strftime('%Y年%m月%d日')}"
    title_cell.font = Font(size=16, bold=True)
    title_cell.alignment = Alignment(horizontal="center")
    
    # 写表头
    headers = ["序号", "产品", "销量", "金额", "地区"]
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = openpyxl.styles.PatternFill(start_color="4472C4", fill_type="solid")
    
    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=3, column=col, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal="center")
    
    # 写数据
    for i, row in enumerate(data, 1):
        ws.cell(row=3+i, column=1, value=i)
        for j, val in enumerate(row, 2):
            ws.cell(row=3+i, column=j, value=val)
    
    # 设置列宽
    ws.column_dimensions["A"].width = 8
    ws.column_dimensions["B"].width = 20
    ws.column_dimensions["C"].width = 12
    ws.column_dimensions["D"].width = 15
    ws.column_dimensions["E"].width = 12
    
    # 保存
    wb.save(output_file)
    print(f"报表已生成：{output_file}")

# 使用
data = [
    ["产品A", 150, 45000, "华东"],
    ["产品B", 89, 26700, "华南"],
    ["产品C", 234, 70200, "华北"],
]
create_report(data, "日报_20260720.xlsx")

场景四：批量处理多个Excel文件

import os
import pandas as pd

def merge_excels(folder_path, output_file):
    """合并文件夹中所有Excel"""
    all_files = []
    
    for file in os.listdir(folder_path):
        if file.endswith(".xlsx") and not file.startswith("~"):
            file_path = os.path.join(folder_path, file)
            df = pd.read_excel(file_path)
            df["来源文件"] = file  # 标记来源
            all_files.append(df)
            print(f"读取：{file} ({len(df)}行)")
    
    # 合并
    merged = pd.concat(all_files, ignore_index=True)
    merged.to_excel(output_file, index=False)
    print(f"\n合并完成：共 {len(merged)} 行")
    print(f"输出：{output_file}")

# 使用：合并"月度数据"文件夹中所有Excel
merge_excels("./月度数据", "合并汇总.xlsx")

实战建议

小数据（<1000行）用openpyxl，能精确控制每个单元格的格式和样式。

大数据（>1000行）用pandas，筛选统计速度快，代码更简洁。

需要漂亮格式的输出用openpyxl，能设置字体、颜色、边框、合并单元格。

需要复杂计算用pandas，groupby、pivot_table、merge这些功能Excel公式很难实现。

相关文章