【Python好用到哭的库】openpyxl-Excel文件处理库简介 openpyxl是专门用于读写Excel 201

库简介

openpyxl是专门用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库。它提供了完整的Excel文件操作功能，包括创建、读取、修改和保存Excel文件，支持单元格样式设置、公式计算、图表创建等高级功能。

安装方法

pip install openpyxl

入门示例

from openpyxl import Workbook

# 创建新的工作簿
wb = Workbook()
ws = wb.active

# 写入数据
ws['A1'] = '姓名'
ws['B1'] = '年龄'
ws['A2'] = '张三'
ws['B2'] = 25

# 保存文件
wb.save('example.xlsx')

进阶实战

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

# 读取和修改Excel文件
wb = load_workbook('data.xlsx')
ws = wb.active

# 设置单元格样式
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")

for cell in ws[1]:  # 第一行作为表头
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

# 添加公式
ws['D2'] = '=B2*C2'  # 计算销售额

# 自动调整列宽
for column in ws.columns:
    max_length = 0
    column_letter = get_column_letter(column[0].column)
    for cell in column:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2)
    ws.column_dimensions[column_letter].width = adjusted_width

# 保存修改
wb.save('data_modified.xlsx')

高级功能

1. 创建图表

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

# 创建工作簿并添加数据
wb = Workbook()
ws = wb.active

# 添加数据
data = [
    ['产品', '销量'],
    ['产品A', 150],
    ['产品B', 200],
    ['产品C', 180],
    ['产品D', 220],
]

for row in data:
    ws.append(row)

# 创建柱状图
chart = BarChart()
chart.title = "产品销量统计"
chart.x_axis.title = "产品"
chart.y_axis.title = "销量"

# 设置数据范围
data_ref = Reference(ws, min_col=2, min_row=1, max_row=5)
categories_ref = Reference(ws, min_col=1, min_row=2, max_row=5)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories_ref)

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

# 保存文件
wb.save("chart_example.xlsx")

2. 合并单元格和样式设置

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

wb = Workbook()
ws = wb.active

# 合并单元格
ws.merge_cells('A1:D1')
ws['A1'] = '销售报表'
ws['A1'].font = Font(size=16, bold=True, color="FFFFFF")
ws['A1'].alignment = Alignment(horizontal="center")
ws['A1'].fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")

# 设置边框
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=2, max_row=10, min_col=1, max_col=4):
    for cell in row:
        cell.border = thin_border

# 保存文件
wb.save("styled_report.xlsx")

3. 读取大型Excel文件

from openpyxl import load_workbook

def read_large_excel(file_path, chunk_size=1000):
    """
    分块读取大型Excel文件，避免内存溢出
    """
    wb = load_workbook(file_path, read_only=True)
    ws = wb.active
    
    data_chunks = []
    current_chunk = []
    
    for i, row in enumerate(ws.iter_rows(values_only=True), start=1):
        current_chunk.append(row)
        
        if i % chunk_size == 0:
            data_chunks.append(current_chunk)
            current_chunk = []
    
    # 添加剩余的数据
    if current_chunk:
        data_chunks.append(current_chunk)
    
    wb.close()
    return data_chunks

# 使用示例
chunks = read_large_excel('large_data.xlsx', chunk_size=5000)
print(f"文件被分成 {len(chunks)} 个数据块")

最佳实践

1. 性能优化

对于大型文件，使用read_only=True模式进行读取
对于写入操作，使用write_only=True模式
避免在循环中频繁创建和销毁工作簿对象

2. 错误处理

import os
from openpyxl import load_workbook
from openpyxl.utils.exceptions import InvalidFileException

def safe_load_workbook(file_path):
    """
    安全加载Excel文件，包含完整的错误处理
    """
    if not os.path.exists(file_path):
        raise FileNotFoundError(f"文件不存在: {file_path}")
    
    if not file_path.endswith(('.xlsx', '.xlsm')):
        raise ValueError("仅支持.xlsx和.xlsm格式的文件")
    
    try:
        wb = load_workbook(file_path)
        return wb
    except InvalidFileException as e:
        raise ValueError(f"无效的Excel文件: {e}")
    except Exception as e:
        raise RuntimeError(f"加载文件时发生错误: {e}")

3. 数据验证

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

wb = Workbook()
ws = wb.active

# 添加数据验证（下拉列表）
dv = DataValidation(type="list", formula1='"男,女"', allow_blank=True)
dv.error = '输入的值不在列表中'
dv.errorTitle = '无效输入'
dv.prompt = '请从下拉列表中选择'
dv.promptTitle = '性别选择'

ws.add_data_validation(dv)
dv.add('B2:B10')  # 为B2到B10单元格添加数据验证

# 保存文件
wb.save("data_validation.xlsx")

应用场景

1. 数据报表生成

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment
from datetime import datetime

def generate_sales_report(data, output_path):
    """
    生成销售报表
    """
    wb = Workbook()
    ws = wb.active
    ws.title = "销售报表"
    
    # 添加标题
    ws['A1'] = '销售报表'
    ws['A1'].font = Font(size=16, bold=True)
    ws.merge_cells('A1:E1')
    ws['A1'].alignment = Alignment(horizontal="center")
    
    # 添加表头
    headers = ['日期', '产品', '数量', '单价', '销售额']
    for col, header in enumerate(headers, start=1):
        cell = ws.cell(row=3, column=col, value=header)
        cell.font = Font(bold=True)
    
    # 添加数据
    for i, row_data in enumerate(data, start=1):
        for j, value in enumerate(row_data, start=1):
            ws.cell(row=3+i, column=j, value=value)
    
    # 添加汇总行
    last_row = 3 + len(data)
    ws.cell(row=last_row+1, column=1, value='总计')
    ws.cell(row=last_row+1, column=5, value=f'=SUM(E4:E{last_row})')
    
    # 保存文件
    wb.save(output_path)
    print(f"报表已生成: {output_path}")

# 使用示例
sales_data = [
    ['2023-10-01', '产品A', 10, 100, 1000],
    ['2023-10-02', '产品B', 5, 200, 1000],
    ['2023-10-03', '产品C', 8, 150, 1200],
]

generate_sales_report(sales_data, 'sales_report.xlsx')

2. 数据导入导出

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

def excel_to_dataframe(file_path, sheet_name=None):
    """
    将Excel文件转换为pandas DataFrame
    """
    wb = load_workbook(file_path, data_only=True)
    ws = wb.active if sheet_name is None else wb[sheet_name]
    
    # 读取数据
    data = []
    for row in ws.iter_rows(values_only=True):
        data.append(row)
    
    # 转换为DataFrame
    df = pd.DataFrame(data[1:], columns=data[0])
    return df

def dataframe_to_excel(df, file_path, sheet_name='Sheet1'):
    """
    将pandas DataFrame保存为Excel文件
    """
    wb = Workbook()
    ws = wb.active
    ws.title = sheet_name
    
    # 写入表头
    for col, column_name in enumerate(df.columns, start=1):
        ws.cell(row=1, column=col, value=column_name)
    
    # 写入数据
    for i, row in enumerate(df.itertuples(index=False), start=2):
        for j, value in enumerate(row, start=1):
            ws.cell(row=i, column=j, value=value)
    
    # 保存文件
    wb.save(file_path)
    print(f"数据已保存到: {file_path}")

常见问题

Q1: openpyxl支持.xls格式吗？

A: 不支持。openpyxl专门用于Excel 2007及更高版本的文件格式（.xlsx, .xlsm）。如果需要处理.xls格式，可以使用xlrd和xlwt库。

Q2: 如何设置单元格的日期格式？

A: 可以使用数字格式代码：

from openpyxl.styles import numbers

cell = ws['A1']
cell.value = datetime.now()
cell.number_format = numbers.FORMAT_DATE_DATETIME

Q3: 如何保护工作表？

A: 使用工作表的保护功能：

ws.protection.sheet = True
ws.protection.password = 'password'  # 可选密码

Q4: 如何处理包含公式的单元格？

A: 默认情况下，openpyxl会保留公式。如果需要计算结果，可以使用data_only=True参数加载工作簿：

wb = load_workbook('file.xlsx', data_only=True)

学习资源

官方文档

openpyxl官方文档: openpyxl.readthedocs.io/

实践项目

财务报表生成器: 自动生成格式化的财务报表
数据清洗工具: 批量处理多个Excel文件的数据清洗
报表自动化系统: 定时生成和发送报表

进阶学习

自定义样式和格式: 深入学习单元格样式设置
图表和图形: 创建复杂的图表和图形
性能优化: 处理超大型Excel文件的技巧

通过掌握openpyxl，您可以轻松处理各种Excel文件操作任务，从简单的数据读写到复杂的报表生成和数据分析。

【Python好用到哭的库】openpyxl-Excel文件处理

库简介