Python Excel自动化操作指南

引言

Excel是商业世界中使用最广泛的数据处理工具，从财务报表到项目管理，从数据分析到运营监控，几乎每个行业都离不开Excel。然而，当面对大量重复性的Excel操作时，手动处理既低效又容易出错。Python凭借强大的第三方库生态，为Excel自动化提供了完整的解决方案。本文将系统讲解openpyxl、xlsxwriter、pandas等主流库的使用方法，涵盖Excel读写、样式设置、公式计算、图表生成、批量处理等核心场景，帮助开发者构建高效的Excel自动化工作流。

openpyxl：读写Excel的核心库

创建与保存工作簿

openpyxl是Python操作Excel最全面的库，支持.xlsx格式的读写、样式、公式、图表等几乎所有功能：

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "销售数据"
ws_summary = wb.create_sheet("汇总")

# 写入数据
ws['A1'] = "产品名称"
ws['B1'] = "销量"
ws['C1'] = "单价"
ws['D1'] = "总收入"

products = [
    ('笔记本电脑', 150, 5999),
    ('智能手机', 320, 3999),
    ('平板电脑', 85, 2999),
    ('智能手表', 200, 1999),
    ('无线耳机', 450, 599),
]

for row_idx, (name, qty, price) in enumerate(products, start=2):
    ws.cell(row=row_idx, column=1, value=name)
    ws.cell(row=row_idx, column=2, value=qty)
    ws.cell(row=row_idx, column=3, value=price)
    ws.cell(row=row_idx, column=4, value=f"=B{row_idx}*C{row_idx}")

wb.save('sales_report.xlsx')

读取已有Excel文件

from openpyxl import load_workbook

wb = load_workbook('sales_report.xlsx')
print(f"工作表列表: {wb.sheetnames}")
ws = wb['销售数据']

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

# 遍历读取
for row in ws.iter_rows(min_row=1, max_row=6, max_col=4, values_only=True):
    print(row)

# 获取维度
print(f"数据范围: {ws.dimensions}")
print(f"最大行: {ws.max_row}, 最大列: {ws.max_column}")

批量数据写入

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "员工信息"

headers = ['工号', '姓名', '部门', '职位', '入职日期', '薪资']
ws.append(headers)

employees = [
    ['E001', '张三', '技术部', '高级工程师', '2020-03-15', 25000],
    ['E002', '李四', '市场部', '市场经理', '2019-08-20', 28000],
    ['E003', '王五', '财务部', '财务分析师', '2021-01-10', 22000],
    ['E004', '赵六', '技术部', '架构师', '2018-05-01', 35000],
]
for emp in employees:
    ws.append(emp)

wb.save('company_data.xlsx')

单元格样式设置

字体、颜色与对齐

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

wb = Workbook()
ws = wb.active

# 字体样式
ws['A1'] = "标题文字"
ws['A1'].font = Font(name='微软雅黑', size=16, bold=True, color='FF0000')

# 背景填充
ws['A2'] = "高亮单元格"
ws['A2'].fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

# 对齐方式
ws['A3'] = "居中对齐"
ws['A3'].alignment = 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')
)
ws['A4'] = "带边框"
ws['A4'].border = thin_border

# 数字格式
ws['A5'] = 12345.6789
ws['A5'].number_format = '#,##0.00'
ws['A6'] = 0.85
ws['A6'].number_format = '0.00%'

wb.save('styled_workbook.xlsx')

批量应用样式

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

wb = Workbook()
ws = wb.active

data = [
    ['姓名', '语文', '数学', '英语', '总分'],
    ['张三', 85, 92, 78, None],
    ['李四', 90, 88, 95, None],
    ['王五', 72, 95, 80, None],
    ['赵六', 88, 76, 92, None],
]
for row in data:
    ws.append(row)

# 填充总分公式
for row_idx in range(2, 6):
    ws.cell(row=row_idx, column=5, value=f"=SUM(B{row_idx}:D{row_idx})")

# 定义样式
header_font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
header_align = Alignment(horizontal='center', vertical='center')
thin_border = Border(
    left=Side(style='thin'), right=Side(style='thin'),
    top=Side(style='thin'), bottom=Side(style='thin')
)

# 应用表头样式
for col in range(1, 6):
    cell = ws.cell(row=1, column=col)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = header_align
    cell.border = thin_border

# 应用数据行样式
for row in range(2, 6):
    for col in range(1, 6):
        cell = ws.cell(row=row, column=col)
        cell.alignment = header_align
        cell.border = thin_border

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

wb.save('formatted_report.xlsx')

条件格式

from openpyxl import Workbook
from openpyxl.formatting.rule import CellIsRule, ColorScaleRule, DataBarRule
from openpyxl.styles import PatternFill

wb = Workbook()
ws = wb.active
ws.append(['销售额'])
sales = [12000, 15000, 8000, 25000, 18000, 30000, 22000, 9000, 17000, 28000]
for s in sales:
    ws.append([s])

# 大于20000的标红
red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
ws.conditional_formatting.add(
    'A2:A11',
    CellIsRule(operator='greaterThan', formula=['20000'], fill=red_fill)
)

# 颜色渐变（色阶）
color_scale = ColorScaleRule(
    start_type='min', start_color='FF0000',
    mid_type='percentile', mid_value=50, mid_color='FFFF00',
    end_type='max', end_color='00FF00'
)
ws.conditional_formatting.add('A2:A11', color_scale)

# 数据条
data_bar = DataBarRule(start_type='min', end_type='max', color='638EC6', showValue=True)
ws.conditional_formatting.add('A2:A11', data_bar)

wb.save('conditional_format.xlsx')

公式与函数

常用公式应用

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "财务报表"
ws.append(['月份', '收入', '支出', '利润', '利润率'])

monthly_data = [
    ['一月', 150000, 120000],
    ['二月', 180000, 135000],
    ['三月', 165000, 140000],
    ['四月', 200000, 155000],
    ['五月', 190000, 160000],
    ['六月', 220000, 170000],
]
for row in monthly_data:
    ws.append(row)

# 添加公式
for row_idx in range(2, 8):
    ws.cell(row=row_idx, column=4, value=f"=B{row_idx}-C{row_idx}")
    ws.cell(row=row_idx, column=5, value=f"=D{row_idx}/B{row_idx}")
    ws.cell(row=row_idx, column=5).number_format = '0.00%'

# 汇总行
ws.append(['合计', '=SUM(B2:B7)', '=SUM(C2:C7)', '=SUM(D2:D7)', '=D8/B8'])
ws.cell(row=8, column=5).number_format = '0.00%'
ws.append(['平均', '=AVERAGE(B2:B7)', '=AVERAGE(C2:C7)', '=AVERAGE(D2:D7)'])

wb.save('financial_report.xlsx')

跨表引用与SUMIF

from openpyxl import Workbook

wb = Workbook()
ws_data = wb.active
ws_data.title = "销售明细"
ws_data.append(['日期', '销售员', '产品', '数量', '金额'])
sales_records = [
    ['2024-01-15', '张三', '产品A', 10, 5000],
    ['2024-01-15', '李四', '产品B', 8, 4800],
    ['2024-01-16', '张三', '产品A', 15, 7500],
    ['2024-01-16', '王五', '产品C', 12, 6000],
    ['2024-01-17', '李四', '产品B', 6, 3600],
]
for record in sales_records:
    ws_data.append(record)

# 汇总分析表
ws_summary = wb.create_sheet("汇总分析")
ws_summary.append(['统计项', '结果'])
ws_summary.append(['张三总销售额', '=SUMIF(销售明细!B:B,"张三",销售明细!E:E)'])
ws_summary.append(['李四总销售额', '=SUMIF(销售明细!B:B,"李四",销售明细!E:E)'])
ws_summary.append(['产品A总销量', '=SUMIF(销售明细!C:C,"产品A",销售明细!D:D)'])
ws_summary.append(['总销售额', '=SUM(销售明细!E:E)'])
ws_summary.append(['销售记录数', '=COUNTA(销售明细!B2:B6)'])
ws_summary.append(['平均订单金额', '=AVERAGE(销售明细!E2:E6)'])

wb.save('cross_sheet_formulas.xlsx')

图表生成

柱状图与饼图

from openpyxl import Workbook
from openpyxl.chart import BarChart, PieChart, Reference
from openpyxl.chart.label import DataLabelList

wb = Workbook()
ws = wb.active
ws.title = "图表数据"
ws.append(['季度', '产品A', '产品B', '产品C'])
data = [
    ['Q1', 120, 85, 100],
    ['Q2', 150, 95, 130],
    ['Q3', 180, 110, 160],
    ['Q4', 210, 140, 190],
]
for row in data:
    ws.append(row)

# 柱状图
bar_chart = BarChart()
bar_chart.title = "季度销售对比"
bar_chart.type = "col"
bar_chart.x_axis.title = "季度"
bar_chart.y_axis.title = "销售额（万元）"
data_ref = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=5)
cats_ref = Reference(ws, min_col=1, min_row=2, max_row=5)
bar_chart.add_data(data_ref, titles_from_data=True)
bar_chart.set_categories(cats_ref)
ws.add_chart(bar_chart, "G2")

# 饼图
pie_chart = PieChart()
pie_chart.title = "产品A季度分布"
product_a_data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie_chart.add_data(product_a_data, titles_from_data=True)
pie_chart.set_categories(cats_ref)
pie_chart.dataLabels = DataLabelList()
pie_chart.dataLabels.showPercent = True
ws.add_chart(pie_chart, "G22")

wb.save('charts_demo.xlsx')

折线图

from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference

wb = Workbook()
ws = wb.active
ws.append(['月份', '实际销售', '目标'])
monthly = [
    ['1月', 120, 100], ['2月', 135, 110], ['3月', 150, 120],
    ['4月', 145, 130], ['5月', 170, 140], ['6月', 185, 150],
]
for row in monthly:
    ws.append(row)

line_chart = LineChart()
line_chart.title = "销售趋势分析"
line_chart.y_axis.title = "销售额"
line_chart.x_axis.title = "月份"
data_ref = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
cats_ref = Reference(ws, min_col=1, min_row=2, max_row=7)
line_chart.add_data(data_ref, titles_from_data=True)
line_chart.set_categories(cats_ref)
for series in line_chart.series:
    series.smooth = True
ws.add_chart(line_chart, "F2")

wb.save('line_chart.xlsx')

pandas读写Excel

基本读写操作

import pandas as pd

df = pd.DataFrame({
    '产品名称': ['笔记本电脑', '智能手机', '平板电脑', '智能手表'],
    '类别': ['电脑', '手机', '电脑', '穿戴设备'],
    '销量': [150, 320, 85, 200],
    '单价': [5999, 3999, 2999, 1999],
})
df['总收入'] = df['销量'] * df['单价']

# 写入Excel
df.to_excel('pandas_output.xlsx', index=False, sheet_name='产品销售')

# 读取Excel
df_read = pd.read_excel('pandas_output.xlsx', sheet_name='产品销售')
print(df_read)

# 多工作表写入
with pd.ExcelWriter('multi_sheet.xlsx') as writer:
    df.to_excel(writer, sheet_name='产品销售', index=False)
    df.describe().to_excel(writer, sheet_name='统计摘要')
    df.groupby('类别').sum(numeric_only=True).to_excel(writer, sheet_name='分类汇总')

pandas高级Excel操作

import pandas as pd

# 读取多个工作表
all_sheets = pd.read_excel('multi_sheet.xlsx', sheet_name=None)
for name, df in all_sheets.items():
    print(f"\n=== {name} ===")
    print(df)

# 数据筛选与导出
df = pd.read_excel('pandas_output.xlsx')
high_value = df[df['总收入'] > 500000]
high_value.to_excel('high_value_products.xlsx', index=False)

# 数据透视表
pivot = df.pivot_table(values='总收入', index='类别', aggfunc='sum')
pivot.to_excel('pivot_table.xlsx')

# 使用openpyxl引擎写入并设置样式
with pd.ExcelWriter('styled_pandas.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='数据', index=False)
    workbook = writer.book
    worksheet = writer.sheets['数据']
    from openpyxl.utils import get_column_letter
    for idx, col in enumerate(df.columns):
        max_length = max(df[col].astype(str).map(len).max(), len(col))
        worksheet.column_dimensions[get_column_letter(idx+1)].width = max_length + 4

xlsxwriter高级格式化

xlsxwriter专注于写入，不支持读取已有文件，但提供了更强大的格式化能力和图表功能，特别适合生成报表：

import xlsxwriter

workbook = xlsxwriter.Workbook('xlsxwriter_demo.xlsx')

# 创建格式
title_format = workbook.add_format({
    'bold': True, 'font_size': 16, 'font_color': 'white',
    'bg_color': '#4472C4', 'align': 'center', 'valign': 'vcenter',
    'border': 1, 'border_color': '#2F5496'
})
header_format = workbook.add_format({
    'bold': True, 'bg_color': '#D6E4F0', 'align': 'center', 'border': 1
})
currency_format = workbook.add_format({
    'num_format': '¥#,##0.00', 'align': 'right', 'border': 1
})
date_format = workbook.add_format({
    'num_format': 'yyyy-mm-dd', 'align': 'center', 'border': 1
})

worksheet = workbook.add_worksheet('销售报表')
worksheet.set_column('A:A', 20)
worksheet.set_column('B:D', 15)

# 写入标题（合并单元格）
worksheet.merge_range('A1:E1', '2024年度销售报表', title_format)
worksheet.set_row(0, 30)

# 写入表头和数据
headers = ['日期', '产品', '销量', '单价', '总收入']
for col, header in enumerate(headers):
    worksheet.write(1, col, header, header_format)

import datetime
data = [
    [datetime.date(2024, 1, 15), '笔记本电脑', 10, 5999],
    [datetime.date(2024, 1, 20), '智能手机', 25, 3999],
    [datetime.date(2024, 2, 5), '平板电脑', 15, 2999],
]
for row_idx, (date, product, qty, price) in enumerate(data, start=2):
    worksheet.write(row_idx, 0, date, date_format)
    worksheet.write(row_idx, 1, product)
    worksheet.write(row_idx, 2, qty)
    worksheet.write(row_idx, 3, price, currency_format)
    worksheet.write_formula(row_idx, 4, f"=C{row_idx+1}*D{row_idx+1}", currency_format)

# 汇总行
summary_row = len(data) + 2
worksheet.write(summary_row, 1, '合计', header_format)
worksheet.write_formula(summary_row, 4, f"=SUM(E3:E{summary_row})", currency_format)

workbook.close()

xlsxwriter图表

import xlsxwriter

workbook = xlsxwriter.Workbook('xlsxwriter_charts.xlsx')
worksheet = workbook.add_worksheet('图表演示')

data = [
    ['月份', '北京', '上海', '广州'],
    ['1月', 120, 150, 100], ['2月', 135, 160, 110],
    ['3月', 150, 170, 130], ['4月', 145, 165, 120],
    ['5月', 170, 190, 150], ['6月', 185, 210, 170],
]
for row in data:
    worksheet.write_row(data.index(row), 0, row)

# 柱状图
chart1 = workbook.add_chart({'type': 'column'})
for col in range(1, 4):
    chart1.add_series({
        'name': ['图表演示', 0, col],
        'categories': ['图表演示', 1, 0, 6, 0],
        'values': ['图表演示', 1, col, 6, col],
    })
chart1.set_title({'name': '各城市月度销售对比'})
worksheet.insert_chart('F2', chart1, {'x_scale': 1.5, 'y_scale': 1.2})

# 条件格式
worksheet.conditional_format('B2:D7', {'type': '3_color_scale'})
worksheet.conditional_format('B7:D7', {'type': 'data_bar'})

workbook.close()

批量处理工作簿

合并与拆分Excel文件

import pandas as pd
from pathlib import Path

def merge_excel_files(input_dir, output_file, sheet_name=0):
    """合并目录下所有Excel文件"""
    input_path = Path(input_dir)
    all_dfs = []
    for excel_file in input_path.glob('*.xlsx'):
        print(f"读取: {excel_file.name}")
        df = pd.read_excel(excel_file, sheet_name=sheet_name)
        df['来源文件'] = excel_file.stem
        all_dfs.append(df)
    if all_dfs:
        merged = pd.concat(all_dfs, ignore_index=True)
        merged.to_excel(output_file, index=False)
        print(f"合并完成: {len(merged)} 行 -> {output_file}")
        return merged
    return None

def split_excel_by_column(input_file, split_column, output_dir):
    """按某列的值拆分Excel文件"""
    df = pd.read_excel(input_file)
    output_path = Path(output_dir)
    output_path.mkdir(parents=True, exist_ok=True)
    for value, group in df.groupby(split_column):
        safe_name = str(value).replace('/', '_').replace('\\', '_')
        output_file = output_path / f"{safe_name}.xlsx"
        group.to_excel(output_file, index=False)
        print(f"导出: {output_file.name} ({len(group)} 行)")

批量格式化工具

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from pathlib import Path

class ExcelFormatter:
    """Excel批量格式化工具"""
    def __init__(self):
        self.header_font = Font(name='微软雅黑', size=11, bold=True, color='FFFFFF')
        self.header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
        self.header_align = Alignment(horizontal='center', vertical='center')
        self.cell_font = Font(name='微软雅黑', size=10)
        self.cell_align = Alignment(horizontal='center', vertical='center')
        self.border = Border(
            left=Side(style='thin'), right=Side(style='thin'),
            top=Side(style='thin'), bottom=Side(style='thin')
        )

    def format_workbook(self, filepath):
        wb = load_workbook(filepath)
        for ws in wb.worksheets:
            self._format_sheet(ws)
        wb.save(filepath)
        print(f"格式化完成: {filepath}")

    def _format_sheet(self, ws):
        if ws.max_row < 1:
            return
        for cell in ws[1]:
            cell.font = self.header_font
            cell.fill = self.header_fill
            cell.alignment = self.header_align
            cell.border = self.border
        for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
            for cell in row:
                cell.font = self.cell_font
                cell.alignment = self.cell_align
                cell.border = self.border
        for col in ws.columns:
            max_length = 0
            col_letter = col[0].column_letter
            for cell in col:
                try:
                    length = len(str(cell.value)) if cell.value else 0
                    if length > max_length:
                        max_length = length
                except Exception:
                    pass
            ws.column_dimensions[col_letter].width = min(max_length + 4, 50)
        ws.freeze_panes = 'A2'

    def batch_format(self, directory):
        dir_path = Path(directory)
        for excel_file in dir_path.glob('*.xlsx'):
            if not excel_file.name.startswith('~$'):
                self.format_workbook(excel_file)

自动化报表生成系统

"""
自动化报表生成系统
功能：从数据源读取 → 计算分析 → 生成格式化报表 → 添加图表
"""
import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.styles import Font, PatternFill, Alignment
from datetime import datetime
from pathlib import Path

class ReportGenerator:
    def __init__(self, output_dir='./reports'):
        self.output_dir = Path(output_dir)
        self.output_dir.mkdir(parents=True, exist_ok=True)

    def generate_sales_report(self, data, report_name=None):
        """生成销售报表"""
        if report_name is None:
            report_name = f"销售报表_{datetime.now().strftime('%Y%m%d')}.xlsx"
        output_path = self.output_dir / report_name

        wb = Workbook()
        ws = wb.active
        ws.title = "销售数据"

        # 写入标题
        ws.merge_range('A1:F1', f'销售报表 - {datetime.now().strftime("%Y年%m月%d日")}',
                       Font(size=16, bold=True))
        ws.row_dimensions[1].height = 30

        # 写入表头
        headers = ['日期', '销售员', '产品', '数量', '单价', '总金额']
        header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
        for col, h in enumerate(headers, 1):
            cell = ws.cell(row=3, column=col, value=h)
            cell.font = Font(bold=True, color='FFFFFF')
            cell.fill = header_fill
            cell.alignment = Alignment(horizontal='center')

        # 写入数据
        for row_idx, record in enumerate(data, start=4):
            for col_idx, value in enumerate(record, 1):
                ws.cell(row=row_idx, column=col_idx, value=value)

        # 添加汇总
        summary_row = len(data) + 4
        ws.cell(row=summary_row, column=3, value="合计").font = Font(bold=True)
        ws.cell(row=summary_row, column=4, value=f"=SUM(D4:D{summary_row-1})")
        ws.cell(row=summary_row, column=6, value=f"=SUM(F4:F{summary_row-1})")

        # 添加图表
        chart = BarChart()
        chart.title = "销售数量对比"
        chart.type = "col"
        values = Reference(ws, min_col=4, min_row=3, max_row=summary_row-1)
        cats = Reference(ws, min_col=3, min_row=4, max_row=summary_row-1)
        chart.add_data(values, titles_from_data=True)
        chart.set_categories(cats)
        ws.add_chart(chart, "H3")

        wb.save(output_path)
        print(f"报表已生成: {output_path}")
        return output_path

# 使用示例
sales_data = [
    ['2024-01-15', '张三', '产品A', 10, 500, 5000],
    ['2024-01-16', '李四', '产品B', 15, 400, 6000],
    ['2024-01-17', '王五', '产品A', 8, 500, 4000],
    ['2024-01-18', '张三', '产品C', 20, 300, 6000],
    ['2024-01-19', '李四', '产品B', 12, 400, 4800],
]

generator = ReportGenerator()
generator.generate_sales_report(sales_data)

最佳实践

库的选择策略

Excel库选择决策指南：openpyxl适用于需要读写.xlsx文件、设置样式、公式、图表的场景，功能全面且支持读写，但处理大文件较慢；xlsxwriter适用于生成复杂格式化报表和大量图表，格式化能力强且写入速度快，但只能写入不能读取；pandas适用于数据分析场景和批量数据处理，与DataFrame无缝集成，但样式控制不如openpyxl精细；xlrd/xlwt适用于处理旧版.xls格式，注意xlrd 2.0以上版本不再支持.xlsx。

性能优化技巧

from openpyxl import load_workbook
import pandas as pd

# 1. 大文件读取使用read_only模式
wb = load_workbook('large_file.xlsx', read_only=True)
ws = wb.active
for row in ws.iter_rows(values_only=True):
    process(row)
wb.close()

# 2. 写入大文件使用write_only模式
from openpyxl import Workbook
wb = Workbook(write_only=True)
ws = wb.create_sheet()
ws.append(['col1', 'col2', 'col3'])
for i in range(100000):
    ws.append([i, f'value_{i}', i * 2])
wb.save('large_output.xlsx')

# 3. pandas读取大文件分块处理
for chunk in pd.read_excel('large.xlsx', chunksize=5000):
    process(chunk)

# 4. 使用openpyxl时避免逐单元格操作
# 不推荐：逐单元格写入
for i in range(1000):
    ws.cell(row=i+1, column=1, value=i)
# 推荐：使用append批量写入
for i in range(1000):
    ws.append([i, f'value_{i}'])

数据验证与保护

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
ws.append(['姓名', '年龄', '部门'])

# 数据验证：年龄限制在18-65之间
dv_age = DataValidation(type="whole", operator="between", formula1="18", formula2="65")
dv_age.error = "年龄必须在18到65之间"
dv_age.errorTitle = "输入错误"
ws.add_data_validation(dv_age)
dv_age.add('B2:B100')

# 数据验证：部门下拉列表
dv_dept = DataValidation(type="list", formula1='"技术部,市场部,财务部,人事部"')
dv_dept.error = "请选择有效的部门"
ws.add_data_validation(dv_dept)
dv_dept.add('C2:C100')

# 工作表保护
ws.protection.sheet = True
ws.protection.password = 'secret123'
# 允许特定操作
ws.protection.formatCells = False
ws.protection.formatColumns = False

wb.save('validated_workbook.xlsx')

总结

Python Excel自动化是一个功能丰富且实用性极强的领域。本文从openpyxl的基础读写出发，系统讲解了单元格样式设置（字体、颜色、对齐、边框、数字格式）、条件格式（色阶、数据条、规则高亮）、公式与函数（基础运算、跨表引用、SUMIF统计）、图表生成（柱状图、饼图、折线图）、pandas数据读写与透视分析、xlsxwriter高级格式化与图表、以及批量处理工作簿（合并拆分、批量格式化、自动化报表生成）等核心技术。

关键要点回顾：第一，openpyxl是日常Excel操作的首选库，读写样式公式图表全覆盖。第二，样式设置遵循"定义格式对象→批量应用"模式，避免逐单元格设置。第三，公式使用字符串模板生成，注意行号动态计算。第四，pandas适合数据分析场景，与openpyxl引擎配合可同时获得数据处理能力和样式控制。第五，xlsxwriter在生成复杂报表时格式化能力最强，但不支持读取。第六，大文件处理使用read_only和write_only模式，避免内存溢出。第七，批量处理时构建工具类统一管理格式和流程，提高代码复用性。

在实际项目中，应根据具体需求选择合适的库组合：日常读写用openpyxl，数据分析用pandas，报表生成用xlsxwriter，批量处理封装为工具类。合理运用这些技术，能够大幅提升Excel数据处理的效率和准确性，将重复性手工操作转化为自动化流程。