当前位置：首页 > Python > 正文

Python Workbook对象使用教程 - 从入门到实战 | Excel自动化指南

HuLin
Python
2025-08-16
261

Python Workbook对象完全使用指南

Workbook是Python操作Excel文件的核心对象，通过openpyxl库提供完整的Excel读写功能。本教程将详细讲解Workbook的使用方法。

安装openpyxl库

pip install openpyxl

基础操作

1. 创建新Excel文件

from openpyxl import Workbook

# 创建Workbook对象
wb = Workbook()

# 获取默认工作表
ws = wb.active
ws.title = "员工数据"

# 保存文件
wb.save("新工作簿.xlsx")

2. 工作表操作

# 创建新工作表
ws1 = wb.create_sheet("销售报表")  # 末尾插入
ws2 = wb.create_sheet("库存", 0)   # 首位插入

# 遍历所有工作表
for sheet in wb:
    print(sheet.title)

# 删除工作表
del wb["库存"]
# 或 wb.remove(wb["库存"])

数据读写操作

1. 写入数据

# 单单元格写入
ws["A1"] = "姓名"
ws["B1"] = "销售额"

# 多行数据写入
data_rows = [
    ["张三", 15000],
    ["李四", 23000],
    ["王五", 18000]
]

for row in data_rows:
    ws.append(row)

# 单元格坐标写入
ws.cell(row=5, column=1, value="总计")
ws.cell(row=5, column=2, value=sum(row[1] for row in data_rows))

2. 读取数据

# 读取单个单元格
name = ws["A2"].value
print(f"第二行姓名: {name}")

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

# 获取最大行列数
max_row = ws.max_row
max_col = ws.max_column
print(f"数据范围: {max_row}行×{max_col}列")

样式设置

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

# 设置标题样式
title_font = Font(name='微软雅黑', size=14, bold=True, color="FFFFFF")
title_fill = PatternFill(start_color="3366FF", end_color="3366FF", fill_type="solid")
for cell in ws[1]:
    cell.font = title_font
    cell.fill = title_font
    cell.alignment = Alignment(horizontal="center")

# 设置边框
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

for row in ws.iter_rows(min_row=1, max_row=ws.max_row):
    for cell in row:
        cell.border = thin_border

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

实战案例

def create_sales_report():
    # 创建工作簿
    wb = Workbook()
    ws = wb.active
    ws.title = "季度销售报告"
    
    # 写入标题
    headers = ["产品ID", "产品名称", "Q1销量", "Q2销量", "Q3销量", "Q4销量"]
    ws.append(headers)
    
    # 写入数据
    products = [
        [101, "智能手机", 1200, 1500, 1800, 2100],
        [102, "平板电脑", 800, 950, 1100, 1300],
        [103, "智能手表", 1500, 1700, 2000, 2400]
    ]
    
    for prod in products:
        ws.append(prod)
    
    # 添加公式
    for i in range(2, 5):
        ws[f"G{i}"] = f"=SUM(C{i}:F{i})"
    
    # 设置样式
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(start_color="0070C0", fill_type="solid")
    for cell in ws[1]:
        cell.font = header_font
        cell.fill = header_fill
    
    # 保存文件
    wb.save("2023年度销售报告.xlsx")

create_sales_report()