🐍 第10课：Excel操作

—— openpyxl：Python 操控 Excel

📌 本课目标

1️⃣ 基本读写

from openpyxl import Workbook, load_workbook

# 创建新工作簿
wb = Workbook()
ws = wb.active
ws.title = "员工数据"

# 写入数据
# 方式1: cell() 方法
ws.cell(row=1, column=1, value="姓名")
ws.cell(row=1, column=2, value="年龄")
ws.cell(row=1, column=3, value="部门")

# 方式2: 直接赋值
ws["A2"] = "张三"
ws["B2"] = 28
ws["C2"] = "技术部"

# 方式3: append 追加一行
ws.append(["李四", 32, "市场部"])
ws.append(["王五", 25, "技术部"])

# 保存
wb.save("employees.xlsx")

# 读取已有工作簿
wb = load_workbook("employees.xlsx")
ws = wb["员工数据"]

# 读取单元格
print(ws["A1"].value)       # 姓名
print(ws.cell(2, 1).value)     # 张三

# 遍历所有行
for row in ws.iter_rows(min_row=2, values_only=True):
    print(row)  # ('张三', 28, '技术部')

# 遍历所有列
for col in ws.iter_cols(values_only=True):
    print(col)

2️⃣ 单元格样式

from openpyxl.styles import (
    Font, PatternFill, Alignment, Border, Side, numbers
)

# 字体
header_font = Font(
    name="微软雅黑",
    size=12,
    bold=True,
    color="FFFFFF"  # 白色
)

# 填充
header_fill = PatternFill(
    start_color="34d399",  # 绿色
    end_color="34d399",
    fill_type="solid"
)

# 对齐
center_align = Alignment(
    horizontal="center",
    vertical="center",
    wrap_text=True
)

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

# 应用样式
for cell in ws[1]:  # 第一行（表头）
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = center_align
    cell.border = thin_border

# 数字格式
ws["B2"].number_format = '#,##0'  # 千分位
ws["C2"].number_format = '0.00%'  # 百分比
ws["D2"].number_format = '¥#,##0.00'  # 人民币

# 条件格式
from openpyxl.formatting.rule import CellIsRule

# 大于1000的单元格标红
red_fill = PatternFill(start_color="FF6B6B", fill_type="solid")
ws.conditional_formatting.add(
    "B2:B100",
    CellIsRule(operator="greaterThan", formula=["1000"], fill=red_fill)
)

3️⃣ 公式与数据验证

# 使用 Excel 公式
ws["D2"] = "=B2*C2"         # 乘法
ws["D10"] = "=SUM(D2:D9)"    # 求和
ws["D11"] = "=AVERAGE(D2:D9)"  # 平均值
ws["D12"] = "=MAX(D2:D9)"    # 最大值
ws["D13"] = "=COUNTIF(C2:C9,\">100\")"  # 条件计数

# 数据验证（下拉列表）
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")

4️⃣ 图表生成

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

# 准备数据
data = [
    ["月份", "销售额", "成本"],
    ["1月", 10000, 6000],
    ["2月", 12000, 7000],
    ["3月", 15000, 8000],
    ["4月", 13000, 7500],
    ["5月", 18000, 9000],
    ["6月", 20000, 9500],
]

for row in data:
    ws.append(row)

# 柱状图
chart = BarChart()
chart.title = "月度销售与成本"
chart.y_axis.title = "金额 (元)"
chart.x_axis.title = "月份"

data_ref = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4
ws.add_chart(chart, "E1")

# 饼图
pie = PieChart()
pie.title = "成本占比"
pie_data = Reference(ws, min_col=3, min_row=1, max_row=7)
pie_cats = Reference(ws, min_col=1, min_row=2, max_row=7)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(pie_cats)
ws.add_chart(pie, "E20")

5️⃣ 验证脚本

#!/usr/bin/env python3
"""第10课 Excel操作验证"""
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.chart import BarChart, Reference
import tempfile, os

def test_excel_write_read():
    """Excel基本读写测试"""
    wb = Workbook()
    ws = wb.active
    ws.append(["姓名", "年龄", "部门"])
    ws.append(["张三", 28, "技术"])
    ws.append(["李四", 32, "市场"])

    with tempfile.NamedTemporaryFile(suffix=".xlsx", delete=False) as f:
        path = f.name
    wb.save(path)

    wb2 = load_workbook(path)
    ws2 = wb2.active
    assert ws2["A1"].value == "姓名"
    assert ws2.cell(2, 2).value == 28
    rows = list(ws2.iter_rows(min_row=2, values_only=True))
    assert len(rows) == 2
    os.unlink(path)
    print("✅ Excel基本读写测试通过")

def test_excel_style():
    """Excel样式测试"""
    wb = Workbook()
    ws = wb.active
    ws["A1"] = "标题"
    ws["A1"].font = Font(bold=True, size=14)
    ws["A1"].fill = PatternFill(start_color="34d399", fill_type="solid")
    ws["A1"].alignment = Alignment(horizontal="center")
    assert ws["A1"].font.bold == True
    print("✅ Excel样式测试通过")

def test_excel_formula():
    """Excel公式测试"""
    wb = Workbook()
    ws = wb.active
    ws["A1"] = 10
    ws["A2"] = 20
    ws["A3"] = "=SUM(A1:A2)"
    assert ws["A3"].value == "=SUM(A1:A2)"
    print("✅ Excel公式测试通过")

def test_excel_chart():
    """Excel图表测试"""
    wb = Workbook()
    ws = wb.active
    ws.append(["月份", "销售额"])
    ws.append(["1月", 10000])
    ws.append(["2月", 12000])

    chart = BarChart()
    data = Reference(ws, min_col=2, min_row=1, max_row=3)
    chart.add_data(data, titles_from_data=True)
    ws.add_chart(chart, "D1")
    assert len(ws._charts) == 1
    print("✅ Excel图表测试通过")

if __name__ == "__main__":
    test_excel_write_read()
    test_excel_style()
    test_excel_formula()
    test_excel_chart()
    print("\n🎉 第10课全部验证通过！")

🔑 本课要点