Python openpyxl 实现Excel表格自动化处理

1. openpyxl简介

openpyxl是一个用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库。它能够处理大型电子表格,支持公式计算,可以操作图表、图片、数据透视表等Excel高级功能。相比其他Excel处理库,openpyxl在功能和性能上都有优势。

2. 安装openpyxl

使用pip安装openpyxl:

pip install openpyxl

3. 创建和保存工作簿

from openpyxl import Workbook

# 创建一个新的工作簿
wb = Workbook()

# 获取活动工作表
ws = wb.active

# 修改工作表标题
ws.title = "数据表"

# 保存工作簿
wb.save("example.xlsx")

4. 读取现有Excel文件

from openpyxl import load_workbook

# 加载现有的Excel文件
wb = load_workbook("existing_file.xlsx")

# 获取特定的工作表
ws = wb["Sheet1"]

# 读取单元格值
cell_value = ws["A1"].value
print(f"A1单元格的值是: {cell_value}")

5. 写入数据

# 直接给单元格赋值
ws["A1"] = "Hello, World!"

# 使用行列坐标写入数据
ws.cell(row=2, column=2, value=42)

# 使用append方法添加行
ws.append(["姓名", "年龄", "城市"])
ws.append(["张三", 25, "北京"])
ws.append(["李四", 30, "上海"])

# 保存更改
wb.save("example.xlsx")

6. 单元格样式设置

from openpyxl.styles import Font, Alignment, PatternFill

# 设置字体
cell = ws["A1"]
cell.font = Font(name="微软雅黑", size=14, bold=True, color="FF0000")

# 设置对齐方式
cell.alignment = Alignment(horizontal="center", vertical="center")

# 设置背景颜色
cell.fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

# 设置列宽和行高
ws.column_dimensions["A"].width = 20
ws.row_dimensions[1].height = 30

7. 公式和函数

# 在单元格中添加公式
ws["D1"] = "=SUM(A1:C1)"

# 使用Python计算结果并写入
ws["E1"] = "=AVERAGE(A1:C1)"
ws["E1"].value = sum([ws[f"{chr(65+i)}1"].value for i in range(3)]) / 3

8. 图表创建

from openpyxl.chart import BarChart, Reference

# 准备数据
for row in range(1, 11):
    ws.append([row, row**2])

# 创建图表
chart = BarChart()
chart.title = "平方数图表"
chart.x_axis.title = "数字"
chart.y_axis.title = "平方"

# 设置数据范围
data = Reference(ws, min_col=2, min_row=1, max_row=10)
categories = Reference(ws, min_col=1, min_row=1, max_row=10)

# 添加数据
chart.add_data(data)
chart.set_categories(categories)

# 将图表添加到工作表
ws.add_chart(chart, "D1")

9. 数据筛选和排序

from openpyxl.worksheet.filters import FilterColumn, CustomFilter

# 添加自动筛选
ws.auto_filter.ref = "A1:C10"

# 添加自定义筛选
filter_column = FilterColumn(colId=0)
filter_column.CustomFilters.append(CustomFilter(operator="greaterThan", val=5))
ws.auto_filter.add_filter_column(filter_column)

# 排序
ws.auto_filter.add_sort_condition("B2:B10")

10. 数据验证

from openpyxl.worksheet.datavalidation import DataValidation

# 创建数据验证规则
dv = DataValidation(type="list", formula1='"选项1,选项2,选项3"')
ws.add_data_validation(dv)

# 应用数据验证到单元格范围
dv.add("D1:D10")

11. 实际应用示例：生成销售报告

让我们通过一个实际的例子来展示openpyxl的强大功能:

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill
from openpyxl.chart import BarChart, Reference

def create_sales_report(data):
    wb = Workbook()
    ws = wb.active
    ws.title = "销售报告"

    # 添加标题
    ws["A1"] = "月份"
    ws["B1"] = "销售额"
    for cell in ws["1:1"]:
        cell.font = Font(bold=True)
        cell.fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

    # 添加数据
    for row, (month, sales) in enumerate(data.items(), start=2):
        ws.cell(row=row, column=1, value=month)
        ws.cell(row=row, column=2, value=sales)

    # 添加总计
    total_row = len(data) + 2
    ws.cell(row=total_row, column=1, value="总计")
    ws.cell(row=total_row, column=2, value=f"=SUM(B2:B{total_row-1})")

    # 创建图表
    chart = BarChart()
    chart.title = "月度销售趋势"
    chart.x_axis.title = "月份"
    chart.y_axis.title = "销售额"

    data = Reference(ws, min_col=2, min_row=1, max_row=total_row-1)
    categories = Reference(ws, min_col=1, min_row=2, max_row=total_row-1)

    chart.add_data(data, titles_from_data=True)
    chart.set_categories(categories)

    ws.add_chart(chart, "D2")

    # 调整列宽
    ws.column_dimensions["A"].width = 15
    ws.column_dimensions["B"].width = 15

    wb.save("sales_report.xlsx")

# 示例数据
sales_data = {
    "一月": 10000,
    "二月": 12000,
    "三月": 15000,
    "四月": 13000,
    "五月": 16000,
    "六月": 18000
}

create_sales_report(sales_data)

这个例子展示了如何使用openpyxl创建一个包含数据、公式、样式和图表的销售报告。

12. 结语

openpyxl为Python开发者提供了强大的Excel自动化处理能力。从简单的数据读写到复杂的报表生成,openpyxl都能胜任。通过本文介绍的技术,您可以大大提高Excel处理的效率和灵活性。随着业务需求的增长,掌握openpyxl将成为数据处理和报告生成中的重要技能。

---

希望这篇文章对您使用openpyxl进行Excel自动化处理有所帮助。如有任何问题,欢迎在评论区讨论交流。