【高效秘籍】openpyxl完全指南：14招轻松掌握Python Excel自动化处理

【高效秘籍】openpyxl完全指南：14招轻松掌握Python Excel自动化处理

1. openpyxl基础入门

1.1 安装与环境配置

使用pip快速安装openpyxl：

# 安装基础包
pip install openpyxl

# 如果需要图表功能，确保安装了Pillow
pip install Pillow

基本导入：

import openpyxl
from openpyxl import Workbook
from openpyxl import load_workbook

1.2 创建和保存Excel文件

def create_basic_excel():
    """创建一个基本的Excel文件并保存"""
    # 创建一个工作簿对象
    wb = Workbook()
    
    # 获取活动工作表（默认是第一个工作表）
    ws = wb.active
    
    # 修改工作表标题
    ws.title = "数据表"
    
    # 向单元格写入数据
    ws['A1'] = "姓名"
    ws['B1'] = "年龄"
    ws['C1'] = "城市"
    
    # 使用行列坐标写入数据
    ws.cell(row=2, column=1, value="张三")
    ws.cell(row=2, column=2, value=28)
    ws.cell(row=2, column=3, value="北京")
    
    # 添加多行数据
    data = [
        ["李四", 32, "上海"],
        ["王五", 45, "广州"],
        ["赵六", 36, "深圳"]
    ]
    
    for row in data:
        ws.append(row)
    
    # 保存工作簿
    wb.save("example.xlsx")
    print("Excel文件已创建并保存为 'example.xlsx'")

# 使用示例
create_basic_excel()

1.3 读取Excel文件

def read_excel_file(file_path):
    """读取Excel文件内容"""
    # 加载工作簿
    wb = load_workbook(filename=file_path)
    
    # 获取所有工作表名称
    sheet_names = wb.sheetnames
    print(f"工作簿包含以下工作表: {sheet_names}")
    
    # 获取第一个工作表
    ws = wb[sheet_names[0]]
    
    # 显示工作表的尺寸（最大行列范围）
    print(f"工作表'{ws.title}'的尺寸: {ws.dimensions}")
    
    # 读取特定单元格的值
    a1_value = ws['A1'].value
    print(f"单元格A1的值: {a1_value}")
    
    # 遍历所有行并读取数据
    print("\n工作表内容:")
    for row in ws.iter_rows(values_only=True):
        print(row)
    
    # 返回工作簿对象以供进一步处理
    return wb

# 使用示例
wb = read_excel_file("example.xlsx")

2. 单元格操作与格式设置

2.1 单元格样式设置

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

def format_cells(file_path, output_path):
    """设置单元格的格式和样式"""
    # 加载工作簿
    wb = load_workbook(filename=file_path)
    ws = wb.active
    
    # 设置字体
    bold_font = Font(name='Arial', size=12, bold=True, color='FF0000')
    ws['A1'].font = bold_font
    
    # 为整行设置样式
    header_font = Font(name='Arial', size=12, bold=True, color='FFFFFF')
    header_fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid')
    
    for cell in ws[1]:
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = 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 row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=ws.max_column):
        for cell in row:
            cell.border = thin_border
    
    # 设置列宽
    ws.column_dimensions['A'].width = 15
    ws.column_dimensions['B'].width = 10
    ws.column_dimensions['C'].width = 20
    
    # 设置行高
    ws.row_dimensions[1].height = 25
    
    # 合并单元格
    ws.merge_cells('A6:C6')
    ws['A6'] = '合并的单元格'
    ws['A6'].alignment = Alignment(horizontal='center')
    
    # 保存修改后的工作簿
    wb.save(output_path)
    print(f"带格式的Excel文件已保存为 '{output_path}'")

# 使用示例
format_cells("example.xlsx", "formatted_example.xlsx")

2.2 条件格式设置

from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule

def add_conditional_formatting(file_path, output_path):
    """添加条件格式"""
    # 加载工作簿
    wb = load_workbook(filename=file_path)
    ws = wb.active
    
    # 添加一些数据用于演示
    ws.append(["销售额"])
    for i in range(10):
        ws.append([i * 1000])
    
    # 1. 添加色阶条件格式 (越高的值颜色越深)
    color_scale_rule = ColorScaleRule(
        start_type='min', start_color='FFFFFF',
        end_type='max', end_color='FF8C00'
    )
    ws.conditional_formatting.add(f'A8:A17', color_scale_rule)
    
    # 2. 添加单元格数值条件格式
    # 如果值大于5000，单元格显示为绿色
    green_rule = CellIsRule(
        operator='greaterThan',
        formula=['5000'],
        stopIfTrue=True,
        fill=PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')
    )
    ws.conditional_formatting.add(f'A8:A17', green_rule)
    
    # 3. 添加基于公式的条件格式
    # 如果单元格值是行中的最大值，则显示为粗体
    formula_rule = FormulaRule(
        formula=['A8=MAX($A$8:$A$17)'],
        stopIfTrue=True,
        font=Font(bold=True)
    )
    ws.conditional_formatting.add(f'A8:A17', formula_rule)
    
    # 保存修改后的工作簿
    wb.save(output_path)
    print(f"带条件格式的Excel文件已保存为 '{output_path}'")

# 使用示例
add_conditional_formatting("example.xlsx", "conditional_formatting.xlsx")

2.3 数据验证

from openpyxl.worksheet.datavalidation import DataValidation

def add_data_validation(file_path, output_path):
    """添加数据验证规则"""
    # 加载工作簿
    wb = load_workbook(filename=file_path)
    ws = wb.active
    
    # 在新的工作表中添加数据验证规则
    ws2 = wb.create_sheet(title="数据验证")
    
    # 1. 添加下拉列表验证
    # 创建城市列表
    cities = ["北京", "上海", "广州", "深圳", "杭州", "南京", "成都", "重庆"]
    
    # 创建下拉列表数据验证
    city_validation = DataValidation(
        type="list",
        formula1=f'"{",".join(cities)}"',  # 注意引号的使用方式
        allow_blank=True
    )
    
    # 添加错误消息
    city_validation.error = '请从下拉列表中选择一个城市'
    city_validation.errorTitle = '无效输入'
    
    # 添加提示消息
    city_validation.prompt = '请选择城市'
    city_validation.promptTitle = '城市选择'
    
    # 将验证规则添加到工作表
    ws2.add_data_validation(city_validation)
    
    # 应用验证规则到单元格区域
    city_validation.add('B2:B10')
    
    # 添加表头
    ws2['A1'] = '序号'
    ws2['B1'] = '城市'
    ws2['C1'] = '人口(万)'
    
    # 添加序号
    for i in range(2, 11):
        ws2.cell(row=i, column=1, value=i-1)
    
    # 2. 添加数值范围验证
    # 人口列数值必须在100到3000之间
    population_validation = DataValidation(
        type="whole",
        operator="between",
        formula1="100",
        formula2="3000",
        allow_blank=True
    )
    
    population_validation.error = '人口必须在100到3000万之间'
    population_validation.errorTitle = '数值错误'
    
    ws2.add_data_validation(population_validation)
    population_validation.add('C2:C10')
    
    # 保存修改后的工作簿
    wb.save(output_path)
    print(f"带数据验证的Excel文件已保存为 '{output_path}'")

# 使用示例
add_data_validation("example.xlsx", "data_validation.xlsx")

3. 公式和函数

3.1 添加公式

def add_formulas(file_path, output_path):
    """向Excel添加公式"""
    # 加载工作簿
    wb = load_workbook(filename=file_path)
    
    # 创建一个新的工作表
    ws = wb.create_sheet(title="公式示例")
    
    # 添加标题行
    ws['A1'] = '月份'
    ws['B1'] = '收入'
    ws['C1'] = '支出'
    ws['D1'] = '利润'
    ws['E1'] = '利润率'
    
    # 添加一些示例数据
    months = ['一月', '二月', '三月', '四月', '五月', '六月']
    for i, month in enumerate(months, start=2):
        ws.cell(row=i, column=1, value=month)
        ws.cell(row=i, column=2, value=10000 + i * 1000)  # 收入
        ws.cell(row=i, column=3, value=6000 + i * 500)    # 支出
    
    # 在D列添加公式计算利润（收入-支出）
    for i in range(2, len(months) + 2):
        # 使用公式字符串
        ws.cell(row=i, column=4, value=f'=B{i}-C{i}')
    
    # 在E列添加公式计算利润率（利润/收入）
    for i in range(2, len(months) + 2):
        ws.cell(row=i, column=5, value=f'=D{i}/B{i}')
    
    # 添加总计行
    total_row = len(months) + 2
    ws.cell(row=total_row, column=1, value='总计')
    
    # 使用SUM函数计算总收入
    ws.cell(row=total_row, column=2, value=f'=SUM(B2:B{total_row-1})')
    
    # 使用SUM函数计算总支出
    ws.cell(row=total_row, column=3, value=f'=SUM(C2:C{total_row-1})')
    
    # 计算总利润
    ws.cell(row=total_row, column=4, value=f'=B{total_row}-C{total_row}')
    
    # 计算平均利润率
    ws.cell(row=total_row, column=5, value=f'=AVERAGE(E2:E{total_row-1})')
    
    # 设置百分比格式
    for i in range(2, total_row + 1):
        ws.cell(row=i, column=5).number_format = '0.00%'
    
    # 保存修改后的工作簿
    wb.save(output_path)
    print(f"带公式的Excel文件已保存为 '{output_path}'")

# 使用示例
add_formulas("example.xlsx", "formulas_example.xlsx")

3.2 计算公式结果

from openpyxl.utils import FORMULAE
from openpyxl.formula.translate import Translator

def evaluate_formulas(file_path):
    """打开带公式的Excel文件并读取计算结果"""
    # 注意：openpyxl不会自动计算公式结果，它只读取Excel中已计算的值
    wb = load_workbook(filename=file_path, data_only=True)
    
    # 获取公式工作表
    ws = wb["公式示例"]
    
    print("公式及其计算结果:")
    for row in range(2, 9):  # 包括数据行和总计行
        income = ws.cell(row=row, column=2).value
        expense = ws.cell(row=row, column=3).value
        profit = ws.cell(row=row, column=4).value
        profit_margin = ws.cell(row=row, column=5).value
        
        formula = ws.cell(row=row, column=4).value
        if row == 8:  # 总计行
            print(f"总计: 收入={income}, 支出={expense}, 利润={profit}, 平均利润率={profit_margin}")
        else:
            month = ws.cell(row=row, column=1).value
            print(f"{month}: 收入={income}, 支出={expense}, 利润={profit}, 利润率={profit_margin}")
    
    # 查看公式转译示例
    original_formula = "=B2-C2"
    translated_formula = Translator(original_formula, origin="D2").translate_formula("D3")
    print(f"\n原始公式: {original_formula}")
    print(f"转译到D3的公式: {translated_formula}")
    
    # 查看openpyxl支持的公式
    print(f"\nopenpyxl支持的公式数量: {len(FORMULAE)}")
    print(f"部分支持的公式: {list(FORMULAE)[:10]}...")

# 使用示例
evaluate_formulas("formulas_example.xlsx")

4. 数据处理与分析

4.1 筛选与排序数据

from openpyxl.worksheet.filters import FilterColumn, Filters

def filter_and_sort_data(file_path, output_path):
    """添加筛选和排序功能"""
    # 加载工作簿
    wb = load_workbook(filename=file_path)
    
    # 创建一个新的工作表
    ws = wb.create_sheet(title="筛选与排序")
    
    # 添加标题行
    headers = ['ID', '姓名', '部门', '职位', '入职日期', '工资']
    for col_num, header in enumerate(headers, 1):
        ws.cell(row=1, column=col_num, value=header)
    
    # 添加示例数据
    data = [
        [1, '张三', '市场部', '经理', '2018-05-15', 15000],
        [2, '李四', '技术部', '高级工程师', '2019-03-22', 18000],
        [3, '王五', '市场部', '专员', '2020-07-10', 8000],
        [4, '赵六', '人事部', '主管', '2017-11-05', 12000],
        [5, '钱七', '技术部', '工程师', '2021-01-15', 13000],
        [6, '孙八', '财务部', '会计', '2019-09-01', 10000],
        [7, '周九', '市场部', '总监', '2016-04-18', 25000],
        [8, '吴十', '技术部', '架构师', '2015-08-30', 30000]
    ]
    
    for row_data in data:
        ws.append(row_data)
    
    # 添加自动筛选
    ws.auto_filter.ref = f"A1:F{len(data) + 1}"
    
    # 为"部门"列添加筛选条件（仅显示"技术部"）
    # 注意：openpyxl可以设置筛选条件，但Excel打开时并不会自动应用这些条件
    col_filter = FilterColumn(colId=2)  # 列索引从0开始，所以部门列是2
    col_filter.filters = Filters(filter=['技术部'])
    ws.auto_filter.filterColumn.append(col_filter)
    
    # 为"工资"列添加筛选条件（仅显示大于15000的）
    # 同样，这个设置需要在Excel中手动应用
    
    # 使用自定义排序
    # 注意：openpyxl不直接支持排序数据，但可以指示Excel按哪些列排序
    # 实际排序在Excel打开后需要手动触发
    
    # 实际应用中的排序通常通过Python进行，然后写入Excel
    # 例如，按工资从高到低排序数据
    sorted_data = sorted(data, key=lambda x: x[5], reverse=True)
    
    # 在新的工作表中写入排序后的数据
    ws_sorted = wb.create_sheet(title="排序后数据")
    
    # 添加标题行
    for col_num, header in enumerate(headers, 1):
        ws_sorted.cell(row=1, column=col_num, value=header)
    
    # 写入排序后的数据
    for row_num, row_data in enumerate(sorted_data, 2):
        for col_num, value in enumerate(row_data, 1):
            ws_sorted.cell(row=row_num, column=col_num, value=value)
    
    # 保存修改后的工作簿
    wb.save(output_path)
    print(f"带筛选和排序的Excel文件已保存为 '{output_path}'")

# 使用示例
filter_and_sort_data("example.xlsx", "filter_sort_example.xlsx")

4.2 数据透视表生成

def create_pivot_data(file_path, output_path):
    """创建可用于数据透视表的数据集"""
    # 注意：openpyxl不直接支持创建数据透视表
    # 但可以创建适合做数据透视表的数据集，然后在Excel中手动创建透视表
    
    # 加载工作簿
    wb = load_workbook(filename=file_path)
    
    # 创建一个新的工作表
    ws = wb.create_sheet(title="销售数据")
    
    # 添加标题行
    headers = ['日期', '销售员', '地区', '产品', '数量', '单价', '销售额']
    ws.append(headers)
    
    # 添加示例销售数据
    import random
    from datetime import datetime, timedelta
    
    # 生成近90天的销售数据
    start_date = datetime(2023, 1, 1)
    sales_people = ['张三', '李四', '王五', '赵六']
    regions = ['华东', '华南', '华北', '华中', '西南', '西北']
    products = ['笔记本电脑', '智能手机', '平板电脑', '智能手表', '耳机']
    
    # 生成随机销售数据
    sales_data = []
    for i in range(200):  # 生成200条销售记录
        date = start_date + timedelta(days=random.randint(0, 89))
        sales_person = random.choice(sales_people)
        region = random.choice(regions)
        product = random.choice(products)
        quantity = random.randint(1, 10)
        
        # 根据产品设置单价
        if product == '笔记本电脑':
            unit_price = random.uniform(5000, 8000)
        elif product == '智能手机':
            unit_price = random.uniform(2000, 5000)
        elif product == '平板电脑':
            unit_price = random.uniform(3000, 6000)
        elif product == '智能手表':
            unit_price = random.uniform(1000, 2000)
        else:  # 耳机
            unit_price = random.uniform(500, 1000)
        
        # 计算销售额
        sales_amount = quantity * unit_price
        
        # 组合数据
        row_data = [
            date.strftime('%Y-%m-%d'),
            sales_person,
            region,
            product,
            quantity,
            round(unit_price, 2),
            round(sales_amount, 2)
        ]
        
        sales_data.append(row_data)
    
    # 写入销售数据
    for row_data in sales_data:
        ws.append(row_data)
    
    # 设置日期列的数字格式
    for row in range(2, len(sales_data) + 2):
        ws.cell(row=row, column=1).number_format = 'yyyy-mm-dd'
    
    # 设置单价和销售额列的货币格式
    for row in range(2, len(sales_data) + 2):
        ws.cell(row=row, column=6).number_format = '#,##0.00'
        ws.cell(row=row, column=7).number_format = '#,##0.00'
    
    # 调整列宽
    ws.column_dimensions['A'].width = 12
    ws.column_dimensions['B'].width = 10
    ws.column_dimensions['C'].width = 10
    ws.column_dimensions['D'].width = 15
    ws.column_dimensions['E'].width = 10
    ws.column_dimensions['F'].width = 12
    ws.column_dimensions['G'].width = 15
    
    # 添加说明工作表
    ws_instruction = wb.create_sheet(title="数据透视表说明")
    ws_instruction.append(["说明："])
    ws_instruction.append(["1. 请在Excel中选择"销售数据"工作表"])
    ws_instruction.append(["2. 在"插入"选项卡中选择"数据透视表""])
    ws_instruction.append(["3. 建议的数据透视表配置:"])
    ws_instruction.append(["   - 行: 产品, 地区"])
    ws_instruction.append(["   - 列: 销售员"])
    ws_instruction.append(["   - 值: 销售额(求和)"])
    ws_instruction.append(["4. 另一个建议配置:"])
    ws_instruction.append(["   - 行: 日期(按月分组)"])
    ws_instruction.append(["   - 列: 产品"])
    ws_instruction.append(["   - 值: 销售额(求和), 数量(求和)"])
    
    # 保存修改后的工作簿
    wb.save(output_path)
    print(f"包含销售数据的Excel文件已保存为 '{output_path}'")
    print("请在Excel中手动创建数据透视表")

# 使用示例
create_pivot_data("example.xlsx", "sales_data_for_pivot.xlsx")

4.3 图表生成

from openpyxl.chart import BarChart, Reference, LineChart, PieChart, Series
from openpyxl.chart.label import DataLabelList

def create_charts(file_path, output_path):
    """创建各种图表"""
    # 加载工作簿
    wb = load_workbook(filename=file_path)
    
    # 创建一个新的工作表用于图表
    ws = wb.create_sheet(title="图表示例")
    
    # 添加示例数据
    ws['A1'] = '季度'
    ws['B1'] = '销售额'
    ws['C1'] = '成本'
    ws['D1'] = '利润'
    
    data = [
        ['Q1', 50000, 30000, 20000],
        ['Q2', 55000, 32000, 23000],
        ['Q3', 65000, 35000, 30000],
        ['Q4', 70000, 38000, 32000]
    ]
    
    for row_idx, row_data in enumerate(data, start=2):
        for col_idx, value in enumerate(row_data, start=1):
            ws.cell(row=row_idx, column=col_idx, value=value)
    
    # 1. 创建柱状图
    bar_chart = BarChart()
    bar_chart.title = "季度销售额与成本"
    bar_chart.style = 10  # 设置图表样式
    bar_chart.x_axis.title = "季度"
    bar_chart.y_axis.title = "金额"
    
    # 定义数据范围
    cats = Reference(ws, min_col=1, min_row=2, max_row=5)  # 季度标签
    data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=5)  # 销售额和成本数据
    
    # 添加数据到图表
    bar_chart.add_data(data, titles_from_data=True)
    bar_chart.set_categories(cats)
    
    # 设置图表位置
    ws.add_chart(bar_chart, "F2")
    
    # 2. 创建折线图
    line_chart = LineChart()
    line_chart.title = "季度利润趋势"
    line_chart.style = 13
    line_chart.x_axis.title = "季度"
    line_chart.y_axis.title = "利润"
    
    # 定义数据范围
    cats = Reference(ws, min_col=1, min_row=2, max_row=5)  # 季度标签
    profit_data = Reference(ws, min_col=4, min_row=1, max_row=5)  # 利润数据
    
    # 添加数据到图表
    line_chart.add_data(profit_data, titles_from_data=True)
    line_chart.set_categories(cats)
    
    # 为数据点添加标签
    s = line_chart.series[0]
    s.graphicalProperties.line.width = 20000  # 线条宽度
    s.marker.symbol = "circle"  # 标记形状
    s.marker.size = 10  # 标记大小
    s.dataLabels = DataLabelList()
    s.dataLabels.showVal = True  # 显示数值
    
    # 设置图表位置
    ws.add_chart(line_chart, "F18")
    
    # 3. 创建饼图
    pie_chart = PieChart()
    pie_chart.title = "Q4季度销售成本分布"
    
    # 在工作表添加饼图数据
    ws['A8'] = "类别"
    ws['B8'] = "金额"
    ws['A9'] = "原材料"
    ws['B9'] = 15000
    ws['A10'] = "人工"
    ws['B10'] = 12000
    ws['A11'] = "运营"
    ws['B11'] = 8000
    ws['A12'] = "其他"
    ws['B12'] = 3000
    
    # 定义饼图数据范围
    labels = Reference(ws, min_col=1, min_row=9, max_row=12)
    pie_data = Reference(ws, min_col=2, min_row=8, max_row=12)
    
    # 添加数据到饼图
    pie_chart.add_data(pie_data, titles_from_data=True)
    pie_chart.set_categories(labels)
    
    # 设置饼图样式
    pie_chart.dataLabels = DataLabelList()
    pie_chart.dataLabels.showPercent = True  # 显示百分比
    
    # 设置图表位置
    ws.add_chart(pie_chart, "M2")
    
    # 保存修改后的工作簿
    wb.save(output_path)
    print(f"带图表的Excel文件已保存为 '{output_path}'")

# 使用示例
create_charts("example.xlsx", "charts_example.xlsx")

5. 高级功能应用

5.1 创建Excel模板

def create_excel_template(output_path):
    """创建一个可重用的Excel模板"""
    # 创建一个新的工作簿
    wb = Workbook()
    ws = wb.active
    ws.title = "销售报表模板"
    
    # 设置公司信息区域
    ws.merge_cells('A1:G1')
    ws['A1'] = '公司销售月度报表'
    ws['A1'].font = Font(name='Arial', size=16, bold=True)
    ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
    
    ws.merge_cells('A2:G2')
    ws['A2'] = '报表生成日期: =TODAY()'
    ws['A2'].font = Font(italic=True)
    ws['A2'].alignment = Alignment(horizontal='right')
    
    # 添加报表参数区域
    ws['A4'] = '填报部门:'
    ws['B4'] = ''  # 用户填写
    ws['D4'] = '报表周期:'
    ws['E4'] = ''  # 用户填写，例如"2023年1月"
    
    ws['A5'] = '填报人:'
    ws['B5'] = ''  # 用户填写
    ws['D5'] = '主管:'
    ws['E5'] = ''  # 用户填写
    
    # 为参数单元格添加边框和底色
    light_blue_fill = PatternFill(start_color='E6F1F5', end_color='E6F1F5', fill_type='solid')
    
    input_cells = ['B4', 'E4', 'B5', 'E5']
    for cell in input_cells:
        ws[cell].fill = light_blue_fill
        ws[cell].border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
    
    # 添加表头
    headers = ['产品编号', '产品名称', '单价', '销售数量', '销售额', '成本', '毛利']
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=7, column=col_num)
        cell.value = header
        cell.font = Font(bold=True)
        cell.fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid')
        cell.alignment = Alignment(horizontal='center')
        cell.border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
    
    # 为数据行添加格式
    # 预设15行数据
    for row in range(8, 23):
        # 产品编号列
        ws.cell(row=row, column=1).border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        # 产品名称列
        ws.cell(row=row, column=2).border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        # 单价列
        cell = ws.cell(row=row, column=3)
        cell.border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        cell.number_format = '#,##0.00'
        
        # 销售数量列
        cell = ws.cell(row=row, column=4)
        cell.border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        # 销售额列（带公式）
        cell = ws.cell(row=row, column=5)
        cell.value = f'=C{row}*D{row}'  # 销售额 = 单价 * 数量
        cell.border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        cell.number_format = '#,##0.00'
        
        # 成本列
        cell = ws.cell(row=row, column=6)
        cell.border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        cell.number_format = '#,##0.00'
        
        # 毛利列（带公式）
        cell = ws.cell(row=row, column=7)
        cell.value = f'=E{row}-F{row}'  # 毛利 = 销售额 - 成本
        cell.border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        cell.number_format = '#,##0.00'
    
    # 添加合计行
    ws.cell(row=23, column=1).value = '合计'
    ws.cell(row=23, column=1).font = Font(bold=True)
    
    # 合计行格式
    for col in range(1, 8):
        cell = ws.cell(row=23, column=col)
        cell.border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='double'),
            bottom=Side(style='double')
        )
        cell.fill = PatternFill(start_color='D9D9D9', end_color='D9D9D9', fill_type='solid')
        
        # 添加合计公式
        if col in [4, 5, 6, 7]:
            cell.value = f'=SUM({chr(64+col)}8:{chr(64+col)}22)'
            if col in [5, 6, 7]:
                cell.number_format = '#,##0.00'
    
    # 添加指导说明
    ws.merge_cells('A25:G25')
    ws['A25'] = '填表说明:'
    ws['A25'].font = Font(bold=True)
    
    instructions = [
        '1. 请在蓝色单元格中填写报表基本信息。',
        '2. 填写产品编号、产品名称、单价、销售数量和成本列的数据。',
        '3. 销售额和毛利将自动计算。',
        '4. 如需添加更多行，请复制现有行并调整公式。'
    ]
    
    for i, instruction in enumerate(instructions):
        ws.merge_cells(f'A{26+i}:G{26+i}')
        ws[f'A{26+i}'] = instruction
    
    # 设置列宽
    ws.column_dimensions['A'].width = 12
    ws.column_dimensions['B'].width = 20
    ws.column_dimensions['C'].width = 12
    ws.column_dimensions['D'].width = 12
    ws.column_dimensions['E'].width = 12
    ws.column_dimensions['F'].width = 12
    ws.column_dimensions['G'].width = 12
    
    # 保护工作表（锁定公式单元格）
    # 首先解锁所有单元格
    for row in ws.iter_rows():
        for cell in row:
            cell.protection = Protection(locked=False)
    
    # 然后锁定公式单元格
    for row in range(8, 23):
        ws.cell(row=row, column=5).protection = Protection(locked=True)
        ws.cell(row=row, column=7).protection = Protection(locked=True)
    
    # 锁定合计行
    for col in range(1, 8):
        ws.cell(row=23, column=col).protection = Protection(locked=True)
    
    # 锁定标题区域
    for row in range(1, 7):
        for col in range(1, 8):
            if not (row in [4, 5] and col in [2, 5]):  # 不锁定输入单元格
                ws.cell(row=row, column=col).protection = Protection(locked=True)
    
    # 启用工作表保护
    ws.protection.password = 'password123'  # 设置保护密码
    ws.protection.sheet = True
    
    # 保存模板
    wb.save(output_path)
    print(f"Excel模板已创建并保存为 '{output_path}'")

# 使用示例
create_excel_template("sales_report_template.xlsx")

5.2 批量处理Excel文件

import os
import glob

def batch_process_excel_files(folder_path, output_folder, summary_file=None):
    """批量处理文件夹中的所有Excel文件"""
    # 确保输出文件夹存在
    if not os.path.exists(output_folder):
        os.makedirs(output_folder)
    
    # 获取所有Excel文件
    excel_files = glob.glob(os.path.join(folder_path, "*.xlsx"))
    
    # 处理结果汇总
    summary_data = []
    
    print(f"找到 {len(excel_files)} 个Excel文件需要处理")
    
    # 逐个处理文件
    for idx, file_path in enumerate(excel_files, 1):
        file_name = os.path.basename(file_path)
        print(f"正在处理文件 {idx}/{len(excel_files)}: {file_name}")
        
        try:
            # 加载工作簿
            wb = load_workbook(filename=file_path)
            
            # 获取第一个工作表
            ws = wb.active
            
            # 示例处理：计算每个文件的数据行数和总销售额
            # 假设销售额在E列，数据从第8行开始
            row_count = 0
            total_sales = 0
            
            for row in range(8, ws.max_row):
                # 检查是否有销售额数据
                sales_cell = ws.cell(row=row, column=5)
                if sales_cell.value and isinstance(sales_cell.value, (int, float)):
                    row_count += 1
                    total_sales += sales_cell.value
            
            # 将处理结果添加到汇总数据
            summary_data.append({
                'file_name': file_name,
                'row_count': row_count,
                'total_sales': total_sales
            })
            
            # 示例处理：添加一个处理标记到文件
            ws['A1'] = '已处理 - ' + ws['A1'].value
            
            # 保存修改后的文件到输出文件夹
            output_path = os.path.join(output_folder, file_name)
            wb.save(output_path)
            
            print(f"  已处理完成: 数据行数={row_count}, 总销售额={total_sales}")
            
        except Exception as e:
            print(f"  处理文件 {file_name} 时出错: {str(e)}")
            summary_data.append({
                'file_name': file_name,
                'error': str(e)
            })
    
    # 创建汇总报告（如果需要）
    if summary_file:
        wb_summary = Workbook()
        ws_summary = wb_summary.active
        ws_summary.title = "处理汇总"
        
        # 添加标题行
        ws_summary['A1'] = '文件名'
        ws_summary['B1'] = '数据行数'
        ws_summary['C1'] = '总销售额'
        ws_summary['D1'] = '状态'
        
        # 添加汇总数据
        for idx, data in enumerate(summary_data, 2):
            ws_summary[f'A{idx}'] = data['file_name']
            
            if 'error' in data:
                ws_summary[f'B{idx}'] = 'N/A'
                ws_summary[f'C{idx}'] = 'N/A'
                ws_summary[f'D{idx}'] = f'错误: {data["error"]}'
            else:
                ws_summary[f'B{idx}'] = data['row_count']
                ws_summary[f'C{idx}'] = data['total_sales']
                ws_summary[f'C{idx}'].number_format = '#,##0.00'
                ws_summary[f'D{idx}'] = '处理成功'
        
        # 计算总计
        last_row = len(summary_data) + 1
        ws_summary[f'A{last_row+1}'] = '总计'
        ws_summary[f'B{last_row+1}'] = f'=SUM(B2:B{last_row})'
        ws_summary[f'C{last_row+1}'] = f'=SUM(C2:C{last_row})'
        ws_summary[f'C{last_row+1}'].number_format = '#,##0.00'
        
        # 设置格式
        for col in ['A', 'B', 'C', 'D']:
            ws_summary[f'{col}1'].font = Font(bold=True)
        
        # 调整列宽
        ws_summary.column_dimensions['A'].width = 30
        ws_summary.column_dimensions['B'].width = 12
        ws_summary.column_dimensions['C'].width = 15
        ws_summary.column_dimensions['D'].width = 30
        
        # 保存汇总报告
        wb_summary.save(summary_file)
        print(f"汇总报告已保存为 '{summary_file}'")
    
    print("批量处理完成!")
    return summary_data

# 使用示例
# batch_process_excel_files("sales_reports", "processed_reports", "processing_summary.xlsx")

5.3 与Pandas集成

import pandas as pd
import numpy as np

def excel_pandas_integration(excel_file, output_file):
    """演示openpyxl与pandas的集成使用"""
    # 1. 使用pandas读取Excel文件
    print(f"使用pandas读取Excel文件: {excel_file}")
    df = pd.read_excel(excel_file, sheet_name="销售数据")
    
    print("\n数据概览:")
    print(df.head())
    
    print("\n数据基本信息:")
    print(df.info())
    
    print("\n数据统计摘要:")
    print(df.describe())
    
    # 2. 使用pandas进行数据分析
    print("\n使用pandas进行数据分析...")
    
    # 按产品分组计算销售总额
    product_sales = df.groupby('产品')['销售额'].sum().sort_values(ascending=False)
    print("\n按产品分组的销售总额:")
    print(product_sales)
    
    # 按地区分组计算销售总额
    region_sales = df.groupby('地区')['销售额'].sum().sort_values(ascending=False)
    print("\n按地区分组的销售总额:")
    print(region_sales)
    
    # 按销售员分组计算销售总额
    salesperson_sales = df.groupby('销售员')['销售额'].sum().sort_values(ascending=False)
    print("\n按销售员分组的销售总额:")
    print(salesperson_sales)
    
    # 按月份计算销售趋势
    df['日期'] = pd.to_datetime(df['日期'])
    df['月份'] = df['日期'].dt.strftime('%Y-%m')
    monthly_sales = df.groupby('月份')['销售额'].sum()
    print("\n按月份的销售趋势:")
    print(monthly_sales)
    
    # 3. 创建输出工作簿并将分析结果写入不同工作表
    wb = Workbook()
    
    # 删除默认的Sheet
    wb.remove(wb.active)
    
    # 添加原始数据工作表
    ws_data = wb.create_sheet("原始数据")
    
    # 将DataFrame转换为Excel（包括列名）
    rows = [df.columns.tolist()] + df.values.tolist()
    for row in rows:
        ws_data.append(row)
    
    # 设置标题行格式
    for cell in ws_data[1]:
        cell.font = Font(bold=True)
    
    # 添加产品销售分析工作表
    ws_product = wb.create_sheet("产品销售分析")
    ws_product.append(["产品", "销售总额"])
    
    for product, sales in product_sales.items():
        ws_product.append([product, sales])
    
    # 添加地区销售分析工作表
    ws_region = wb.create_sheet("地区销售分析")
    ws_region.append(["地区", "销售总额"])
    
    for region, sales in region_sales.items():
        ws_region.append([region, sales])
    
    # 添加销售员业绩工作表
    ws_salesperson = wb.create_sheet("销售员业绩")
    ws_salesperson.append(["销售员", "销售总额"])
    
    for salesperson, sales in salesperson_sales.items():
        ws_salesperson.append([salesperson, sales])
    
    # 添加月度趋势工作表
    ws_trend = wb.create_sheet("月度销售趋势")
    ws_trend.append(["月份", "销售总额"])
    
    for month, sales in monthly_sales.items():
        ws_trend.append([month, sales])
    
    # 为月度趋势创建图表
    chart = LineChart()
    chart.title = "月度销售趋势"
    chart.x_axis.title = "月份"
    chart.y_axis.title = "销售总额"
    
    # 定义数据范围
    data = Reference(ws_trend, min_col=2, min_row=1, max_row=len(monthly_sales)+1)
    cats = Reference(ws_trend, min_col=1, min_row=2, max_row=len(monthly_sales)+1)
    
    # 添加数据到图表
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    
    # 设置图表位置
    ws_trend.add_chart(chart, "D2")
    
    # 保存工作簿
    wb.save(output_file)
    print(f"\n分析结果已保存到: {output_file}")

# 使用示例
# excel_pandas_integration("sales_data_for_pivot.xlsx", "sales_analysis.xlsx")

6. 实际应用案例

6.1 自动化财务报表生成

def generate_financial_report(template_file, data_file, output_file):
    """使用模板和数据自动生成财务报表"""
    # 加载数据（使用pandas）
    df = pd.read_excel(data_file)
    
    # 加载报表模板
    wb = load_workbook(template_file)
    ws = wb["销售报表模板"]
    
    # 填充报表基本信息
    from datetime import datetime
    
    ws['B4'] = "销售部"
    ws['E4'] = f"{datetime.now().year}年{datetime.now().month}月"
    ws['B5'] = "张经理"
    ws['E5'] = "李总监"
    
    # 填充报表数据
    # 假设数据包含：产品编号、产品名称、单价、销售数量、成本
    start_row = 8
    for idx, row in df.iterrows():
        if idx >= 15:  # 模板只预设了15行
            print(f"警告: 数据超过模板预设的行数，只处理前15行")
            break
        
        ws.cell(row=start_row+idx, column=1, value=row['产品编号'])
        ws.cell(row=start_row+idx, column=2, value=row['产品名称'])
        ws.cell(row=start_row+idx, column=3, value=row['单价'])
        ws.cell(row=start_row+idx, column=4, value=row['销售数量'])
        ws.cell(row=start_row+idx, column=6, value=row['成本'])
        
        # 注意：销售额和毛利的公式已在模板中设置
    
    # 保存生成的报表
    wb.save(output_file)
    print(f"财务报表已生成并保存为: {output_file}")

# 使用示例
# generate_financial_report("sales_report_template.xlsx", "sales_data.xlsx", "monthly_report_2023_06.xlsx")

6.2 数据导入与导出系统

def data_import_export_system(action, file_path, db_conn=None):
    """演示Excel数据导入导出系统"""
    if action.lower() == "import":
        # 从Excel导入数据到数据库
        print(f"正在从 {file_path} 导入数据...")
        
        # 加载Excel数据
        wb = load_workbook(file_path, data_only=True)
        ws = wb.active
        
        # 获取列名（假设第一行是标题）
        headers = [cell.value for cell in ws[1]]
        
        # 构建数据列表
        data = []
        for row in ws.iter_rows(min_row=2, values_only=True):
            # 跳过空行
            if all(cell is None or cell == '' for cell in row):
                continue
                
            # 创建数据字典
            row_data = dict(zip(headers, row))
            data.append(row_data)
        
        print(f"已从Excel读取 {len(data)} 条记录")
        
        # 如果提供了数据库连接，将数据写入数据库
        if db_conn:
            try:
                # 这里是SQL插入示例，实际应用中应使用参数化查询
                cursor = db_conn.cursor()
                
                for record in data:
                    columns = ', '.join(record.keys())
                    placeholders = ', '.join(['%s'] * len(record))
                    values = tuple(record.values())
                    
                    sql = f"INSERT INTO products ({columns}) VALUES ({placeholders})"
                    cursor.execute(sql, values)
                
                db_conn.commit()
                print(f"成功导入 {len(data)} 条记录到数据库")
                
            except Exception as e:
                db_conn.rollback()
                print(f"导入数据时出错: {str(e)}")
        
        return data
        
    elif action.lower() == "export":
        # 从数据库导出数据到Excel
        print("正在导出数据到Excel...")
        
        # 创建新的工作簿
        wb = Workbook()
        ws = wb.active
        ws.title = "导出数据"
        
        # 如果提供了数据库连接，从数据库读取数据
        if db_conn:
            try:
                cursor = db_conn.cursor()
                cursor.execute("SELECT * FROM products")
                
                # 获取列名
                columns = [column[0] for column in cursor.description]
                ws.append(columns)
                
                # 添加数据行
                for row in cursor.fetchall():
                    ws.append(row)
                
                print(f"已从数据库读取数据")
                
            except Exception as e:
                print(f"从数据库读取数据时出错: {str(e)}")
                return False
        else:
            # 如果没有数据库连接，创建示例数据
            ws.append(['产品ID', '产品名称', '类别', '单价', '库存', '供应商'])
            
            sample_data = [
                [1, '笔记本电脑', '电子产品', 5999.00, 25, 'A公司'],
                [2, '智能手机', '电子产品', 3999.00, 50, 'B公司'],
                [3, '办公椅', '办公家具', 399.00, 30, 'C公司'],
                [4, '显示器', '电子产品', 1299.00, 15, 'A公司'],
                [5, '文件柜', '办公家具', 599.00, 10, 'C公司']
            ]
            
            for row in sample_data:
                ws.append(row)
                
            print("已创建示例数据")
        
        # 设置格式
        for cell in ws[1]:
            cell.font = Font(bold=True)
        
        # 调整列宽
        for col in ws.columns:
            max_length = 0
            column = col[0].column_letter  # 获取列字母
            
            for cell in col:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
                    
            adjusted_width = (max_length + 2) * 1.2
            ws.column_dimensions[column].width = adjusted_width
        
        # 保存Excel文件
        wb.save(file_path)
        print(f"数据已导出到: {file_path}")
        
        return True
    
    else:
        print(f"未知操作: {action}. 请使用 'import' 或 'export'")
        return False

# 使用示例
# 导出数据
# data_import_export_system("export", "exported_products.xlsx")

# 导入数据
# data = data_import_export_system("import", "exported_products.xlsx")
# print(f"导入的数据: {data}")

6.3 自动化报告生成系统

def automated_report_system(data_sources, template_file, output_file):
    """自动化报告生成系统，支持多数据源和图表"""
    # 加载模板
    wb = load_workbook(template_file)
    
    # 处理每个数据源
    for source_name, source_file in data_sources.items():
        print(f"处理数据源: {source_name}")
        
        # 检查模板中是否有对应的工作表
        if source_name not in wb.sheetnames:
            print(f"  在模板中未找到工作表 '{source_name}'，跳过")
            continue
        
        # 获取目标工作表
        ws = wb[source_name]
        
        # 加载数据
        try:
            df = pd.read_excel(source_file)
            print(f"  已加载 {len(df)} 行数据")
            
            # 查找数据插入位置（查找标记单元格）
            start_cell = None
            for row in ws.iter_rows():
                for cell in row:
                    if cell.value == "{{DATA_START}}":
                        start_cell = cell
                        break
                if start_cell:
                    break
            
            if not start_cell:
                print(f"  在工作表 '{source_name}' 中未找到数据插入标记 '{{DATA_START}}'，跳过")
                continue
            
            # 获取插入位置
            start_row = start_cell.row
            start_col = start_cell.column
            
            # 清空标记单元格
            start_cell.value = None
            
            # 插入数据
            # 首先插入列名
            for i, col_name in enumerate(df.columns):
                ws.cell(row=start_row, column=start_col+i, value=col_name)
                ws.cell(row=start_row, column=start_col+i).font = Font(bold=True)
            
            # 然后插入数据
            for i, (_, row_data) in enumerate(df.iterrows(), 1):
                for j, value in enumerate(row_data):
                    ws.cell(row=start_row+i, column=start_col+j, value=value)
            
            # 更新数据范围标记（如果有）
            for row in ws.iter_rows():
                for cell in row:
                    if isinstance(cell.value, str) and "{{DATA_RANGE}}" in cell.value:
                        # 替换成实际数据范围
                        data_range = f"{chr(64+start_col)}{start_row}:{chr(64+start_col+len(df.columns)-1)}{start_row+len(df)}"
                        cell.value = cell.value.replace("{{DATA_RANGE}}", data_range)
            
            print(f"  数据已插入到工作表 '{source_name}'")
            
        except Exception as e:
            print(f"  处理数据源 '{source_name}' 时出错: {str(e)}")
    
    # 更新报告生成日期
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if isinstance(cell.value, str) and "{{REPORT_DATE}}" in cell.value:
                    cell.value = cell.value.replace("{{REPORT_DATE}}", datetime.now().strftime("%Y-%m-%d"))
    
    # 刷新图表数据（注意：openpyxl不会自动更新图表数据源，只会创建指向数据区域的引用）
    # 图表数据更新通常需要在Excel中手动完成，或通过更复杂的方法
    
    # 保存生成的报告
    wb.save(output_file)
    print(f"自动化报告已生成并保存为: {output_file}")

# 使用示例
# data_sources = {
#     "销售数据": "monthly_sales.xlsx",
#     "库存数据": "inventory.xlsx",
#     "客户分析": "customers.xlsx"
# }
# automated_report_system(data_sources, "report_template.xlsx", "monthly_report.xlsx")

7. 性能优化与最佳实践

7.1 处理大型Excel文件

def handle_large_excel_file(input_file, output_file=None, chunk_size=1000):
    """优化处理大型Excel文件的方法"""
    print(f"开始处理大型Excel文件: {input_file}")
    
    # 使用pandas分块读取大型Excel
    # 首先获取总行数
    xls = pd.ExcelFile(input_file)
    sheet_name = xls.sheet_names[0]  # 使用第一个工作表
    
    # 获取行数（简单读取少量行来获取列名）
    df_sample = pd.read_excel(xls, sheet_name=sheet_name, nrows=5)
    columns = df_sample.columns
    
    # 分块处理
    chunks = []
    total_rows = 0
    
    for chunk in pd.read_excel(xls, sheet_name=sheet_name, chunksize=chunk_size):
        # 在这里对每个数据块进行处理
        # 示例：计算每列的平均值
        chunk_summary = {
            'rows': len(chunk),
            'column_means': {col: chunk[col].mean() for col in chunk.select_dtypes(include=['number']).columns}
        }
        
        chunks.append(chunk_summary)
        total_rows += len(chunk)
        print(f"已处理 {total_rows} 行数据...")
    
    print(f"文件共包含 {total_rows} 行数据")
    
    # 如果需要输出处理后的文件
    if output_file:
        # 创建一个新的工作簿用于存储分析结果
        wb = Workbook()
        ws = wb.active
        ws.title = "数据分析结果"
        
        # 添加摘要信息
        ws['A1'] = "大型Excel文件分析结果"
        ws['A1'].font = Font(size=14, bold=True)
        
        ws['A3'] = "文件名:"
        ws['B3'] = os.path.basename(input_file)
        
        ws['A4'] = "总行数:"
        ws['B4'] = total_rows
        
        ws['A5'] = "列数:"
        ws['B5'] = len(columns)
        
        ws['A7'] = "各列平均值:"
        
        # 收集所有数值列
        numeric_columns = set()
        for chunk in chunks:
            numeric_columns.update(chunk['column_means'].keys())
        
        # 写入列名
        for i, col in enumerate(sorted(numeric_columns)):
            ws.cell(row=8, column=i+1, value=col)
            ws.cell(row=8, column=i+1).font = Font(bold=True)
        
        # 计算并写入整体平均值
        weighted_means = {}
        for col in numeric_columns:
            total_sum = 0
            total_count = 0
            
            for chunk in chunks:
                if col in chunk['column_means']:
                    total_sum += chunk['column_means'][col] * chunk['rows']
                    total_count += chunk['rows']
            
            if total_count > 0:
                weighted_means[col] = total_sum / total_count
            else:
                weighted_means[col] = None
        
        # 写入计算结果
        for i, col in enumerate(sorted(numeric_columns)):
            ws.cell(row=9, column=i+1, value=weighted_means[col])
        
        # 保存结果
        wb.save(output_file)
        print(f"分析结果已保存到: {output_file}")
    
    return {
        'total_rows': total_rows,
        'column_means': weighted_means if 'weighted_means' in locals() else None
    }

# 使用示例
# handle_large_excel_file("large_data.xlsx", "large_data_analysis.xlsx", chunk_size=5000)

7.2 openpyxl性能提升技巧

def openpyxl_performance_tips():
    """展示openpyxl性能优化技巧"""
    tips = [
        {
            "标题": "使用只读模式加载大型文件",
            "代码示例": """
# 只读模式
wb = load_workbook(filename='large_file.xlsx', read_only=True)
ws = wb['Sheet1']

# 使用迭代器而不是直接访问单元格
for row in ws.rows:
    for cell in row:
        print(cell.value)
            """,
            "说明": "只读模式大大减少内存使用，但有一些限制，如不能修改文件"
        },
        {
            "标题": "使用write_only模式创建大型文件",
            "代码示例": """
# 只写模式
wb = Workbook(write_only=True)
ws = wb.create_sheet()

# 一次性添加一整行
for row_idx in range(100000):
    row_data = [f'Data {row_idx}-{col_idx}' for col_idx in range(10)]
    ws.append(row_data)
            """,
            "说明": "只写模式显著提高大型文件的写入速度，但不能随机访问单元格"
        },
        {
            "标题": "使用数据验证时注意性能",
            "代码示例": """
# 避免为每个单元格添加单独的数据验证对象
# 不好的做法
for row in range(2, 1000):
    dv = DataValidation(type='list', formula1='"Option1,Option2,Option3"')
    ws.add_data_validation(dv)
    dv.add(f'A{row}')

# 更好的做法
dv = DataValidation(type='list', formula1='"Option1,Option2,Option3"')
ws.add_data_validation(dv)
dv.add('A2:A1000')
            """,
            "说明": "重用数据验证对象并一次性应用到多个单元格可以显著提高性能"
        },
        {
            "标题": "避免频繁的单元格样式设置",
            "代码示例": """
# 不好的做法
for row in range(1, 1000):
    for col in range(1, 10):
        cell = ws.cell(row, col)
        cell.font = Font(bold=True)
        cell.border = Border(left=Side(style='thin'), right=Side(style='thin'),
                           top=Side(style='thin'), bottom=Side(style='thin'))

# 更好的做法
bold_font = Font(bold=True)
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),
                    top=Side(style='thin'), bottom=Side(style='thin'))

for row in range(1, 1000):
    for col in range(1, 10):
        cell = ws.cell(row, col)
        cell.font = bold_font
        cell.border = thin_border
            """,
            "说明": "创建并重用样式对象可以显著提高性能并减少内存使用"
        },
        {
            "标题": "批量插入数据",
            "代码示例": """
# 不好的做法
for row in range(1, 10000):
    for col in range(1, 10):
        ws.cell(row, col, value=f'Data {row}-{col}')

# 更好的做法
data = []
for row in range(1, 10000):
    row_data = [f'Data {row}-{col}' for col in range(1, 10)]
    data.append(row_data)

for row_idx, row_data in enumerate(data, 1):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row_idx, col_idx, value=value)

# 最好的做法（使用append）
for row_data in data:
    ws.append(row_data)
            """,
            "说明": "使用append方法批量添加行数据是最高效的方式"
        },
        {
            "标题": "使用数据类型优化",
            "代码示例": """
from openpyxl.cell import WriteOnlyCell

# 为大型数据集优化单元格数据类型
wb = Workbook(write_only=True)
ws = wb.create_sheet()

# 创建一个单元格样式
cell_style = Font(name='Calibri', size=11, bold=True)

# 应用于所有单元格
for _ in range(1000):
    cells = []
    for x in range(100):
        cell = WriteOnlyCell(ws, value=x)
        cell.font = cell_style
        cells.append(cell)
    ws.append(cells)
            """,
            "说明": "使用WriteOnlyCell可以在只写模式下自定义单元格样式"
        },
        {
            "标题": "使用替代库处理特定任务",
            "代码示例": """
# 使用pandas快速读取和处理数据
import pandas as pd

# 读取
df = pd.read_excel('large_file.xlsx')

# 处理
df['计算列'] = df['A列'] + df['B列']

# 写回Excel
df.to_excel('processed_file.xlsx', index=False)
            """,
            "说明": "对于某些任务，pandas可能比openpyxl更高效，特别是数据分析任务"
        }
    ]
    
    # 打印性能提示
    print("===== openpyxl性能优化技巧 =====\n")
    
    for i, tip in enumerate(tips, 1):
        print(f"{i}. {tip['标题']}")
        print("-" * 50)
        print(tip['代码示例'])
        print("\n说明:", tip['说明'])
        print("\n" + "=" * 50 + "\n")
    
    return tips

# 使用示例
# openpyxl_performance_tips()

8. 总结与展望

openpyxl提供了强大的Excel文件操作能力，从简单的数据读写到复杂的格式设置、图表创建和自动化报表生成。通过本文介绍的技术，您可以：

1. 创建和修改Excel文件，包括单元格格式设置和数据验证
2. 使用Excel公式和函数处理数据
3. 创建各种类型的图表来可视化数据
4. 构建可重用的Excel模板
5. 批量处理多个Excel文件
6. 将openpyxl与pandas集成，增强数据分析能力
7. 开发实用的自动化报表生成系统
8. 优化处理大型Excel文件的性能

随着数据分析和自动化需求的不断增长，Excel仍将是企业环境中不可或缺的工具。通过掌握openpyxl的高级技巧，您不仅可以提高日常工作效率，还能开发出强大的数据处理系统，为企业决策提供有力支持。

参考资源

1. openpyxl官方文档：https://openpyxl.readthedocs.io/
2. pandas Excel处理：https://pandas.pydata.org/docs/user_guide/io.html#excel-files
3. openpyxl GitHub仓库：https://github.com/theorchard/openpyxl
4. Excel官方开发者文档：https://docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-overview

通过掌握openpyxl的高级技巧，您将能够显著提高Excel文件处理的效率和自动化程度，减少手动操作，节省宝贵的时间，并降低人为错误的风险。无论是数据分析师、财务专业人员，还是软件开发者，这些技能都将在现代数据驱动的工作环境中发挥重要作用。