Python openpyxl 秘籍：掌握Excel的高级应用技巧

大家好，我是涛哥，今天为大家分享 Python openpyxl 秘籍，文章8000字，阅读大约25分钟，大家enjoy~~

openpyxl是一个用于处理Excel文件的Python库。它提供了丰富的功能，使得用户能够轻松地读取、写入和操作Excel文件，而不需要依赖于Microsoft Excel软件。作为一个开源项目，openpyxl在Python生态系统中得到了广泛的应用，成为处理Excel数据的首选工具之一。

openpyxl的重要性和优势

• 跨平台性： openpyxl可以在多个平台上运行，包括Windows、Linux和MacOS，使得数据处理任务更加灵活和便捷。

• 无需Excel软件： 与使用Excel软件相比，openpyxl允许在没有安装Microsoft Excel的环境中进行Excel文件的读写和操作。

• 开放源代码： openpyxl是一个开源项目，可以自由使用和修改，使得开发者能够根据自己的需求对其进行定制和扩展。

• 活跃的社区支持： 由于其在开源社区中的活跃参与，openpyxl不断更新和改进，确保与最新的Excel格式兼容，并提供最佳性能。

• 功能丰富： openpyxl提供了丰富的功能，包括但不限于读取和写入数据、创建图表、设置样式、执行公式计算等，使其适用于各种复杂的Excel处理任务。

安装openpyxl

使用pip（Python包管理工具）来安装openpyxl。

pip install openpyxl

基本概念

1 Workbook（工作簿）

在openpyxl中，Workbook是一个Excel工作簿的抽象表示。它是整个Excel文件的最顶层对象，包含了所有的Sheet和相关数据。

示例代码：创建Workbook

from openpyxl import Workbook

# 创建一个Workbook对象
wb = Workbook()

在这个例子中，使用Workbook类创建了一个新的Excel工作簿。

2 Sheet（工作表）

Sheet是工作簿中的一个单独的工作表。工作簿可以包含一个或多个工作表。默认情况下，创建工作簿时会包含一个名为”Sheet”的工作表。

示例代码：添加Sheet

# 获取活动的Sheet
sheet = wb.active

# 创建一个名为"NewSheet"的Sheet
new_sheet = wb.create_sheet("NewSheet")

在这个例子中，通过active属性获取了活动的工作表，然后使用create_sheet方法创建了一个名为”NewSheet”的新工作表。

3 Cell（单元格）

Cell是工作表中的一个单元格，用于存储数据。单元格由列字母和行号唯一标识，例如”A1″表示第一列第一行的单元格。

示例代码：写入数据到单元格

# 向单元格写入数据
sheet['A1'] = 'Hello'
sheet['B1'] = 'World'

在这个例子中，使用单元格坐标（’A1’和’B1’）向工作表中的特定单元格写入了数据。

读取数据

在openpyxl中，可以使用不同的方法来从已有的Excel文件中读取数据，包括文本、数字和日期等不同数据类型。

1 打开Excel文件

首先，需要使用load_workbook函数打开已有的Excel文件。

from openpyxl import load_workbook

# 打开Excel文件
wb = load_workbook('example.xlsx')

# 获取活动的Sheet
sheet = wb.active

2 读取文本数据

对于包含文本数据的单元格，可以直接通过value属性读取。

# 读取文本数据
text_data = sheet['A1'].value
print("文本数据:", text_data)

3 读取数字数据

对于包含数字数据的单元格，同样可以通过value属性读取。

# 读取数字数据
number_data = sheet['B1'].value
print("数字数据:", number_data)

4 读取日期数据

如果单元格包含日期数据，可以通过value属性读取日期对象。

# 读取日期数据
date_data = sheet['C1'].value
print("日期数据:", date_data)

在读取日期数据时，openpyxl会将其转换为Python的datetime对象。

写入数据

在openpyxl中，可以使用不同的方法向Excel文件中写入不同类型的数据，同时还可以进行一些样式设置和合并单元格等操作。

1 写入文本和数字数据

对于文本和数字数据，可以直接在单元格中使用等号赋值。

# 写入文本和数字数据
sheet['A1'] = 'Hello'
sheet['B1'] = 123

2 写入日期数据

对于日期数据，可以使用Python的datetime对象。

from datetime import datetime

# 写入日期数据
sheet['C1'] = datetime(2023, 1, 1)

3 样式设置

openpyxl可以对单元格进行样式设置，包括字体、颜色和边框等。

from openpyxl.styles import Font, PatternFill, Border

# 设置字体样式
sheet['A1'].font = Font(bold=True, color='FF0000')

# 设置背景颜色
sheet['A1'].fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

# 设置边框
sheet['A1'].border = Border(left=Side(border_style='thin', color='000000'),
                            right=Side(border_style='thin', color='000000'),
                            top=Side(border_style='thin', color='000000'),
                            bottom=Side(border_style='thin', color='000000'))

4 合并单元格

可以使用merge_cells方法合并单元格。

# 合并单元格
sheet.merge_cells('A1:B2')

5 保存文件

最后，需要保存Workbook，以便在Excel中查看结果。

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

图表和图片

在openpyxl中，可以使用图表和图片来更生动地展示数据和信息。以下是如何处理图表和图片的示例。

1 添加图表

首先，创建一个简单的柱状图，并将其添加到Excel文件中。

from openpyxl.chart import BarChart, Reference

# 创建柱状图对象
chart = BarChart()

# 设置图表数据范围
data = Reference(sheet, min_col=2, min_row=1, max_col=3, max_row=2)
chart.add_data(data)

# 将图表添加到Sheet
sheet.add_chart(chart, "D4")

2 插入图片

接下来，插入一张图片到Excel文件中。

from openpyxl.drawing.image import Image

# 插入图片
img = Image('path/to/image.jpg')
sheet.add_image(img, 'E4')

确保替换 'path/to/image.jpg' 为实际图片的路径，并将图片添加到Excel文件的指定位置。

3 设置图表和图片属性

还可以设置图表和图片的各种属性，如位置、大小、标题等。

# 设置图表位置和大小
chart.x = 50
chart.y = 100
chart.width = 300
chart.height = 200

# 设置图片位置和大小
img.width = 100
img.height = 100
img.anchor = 'F4'

4 保存文件

最后，需要保存Workbook，以便在Excel中查看结果。

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

数据筛选和排序

在openpyxl中，可以使用筛选和排序功能对Excel表格中的数据进行进一步的处理。以下是如何在openpyxl中实现数据筛选和排序的示例。

1 数据筛选

使用AutoFilter来实现数据筛选。下面是一个示例，演示如何按条件筛选数据。

from openpyxl import Workbook

# 创建一个Workbook对象
wb = Workbook()

# 获取活动的Sheet
sheet = wb.active

# 写入示例数据
sheet['A1'] = 'Name'
sheet['B1'] = 'Score'

data = [
    ('Alice', 85),
    ('Bob', 92),
    ('Charlie', 78),
    ('David', 95),
    ('Eva', 88)
]

for row in data:
    sheet.append(row)

# 启用AutoFilter
sheet.auto_filter.ref = sheet.dimensions

# 按条件筛选数据
sheet.auto_filter.add_filter_column(1, ["Alice", "David"])

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

在上述代码中，使用auto_filter属性启用了AutoFilter，并通过add_filter_column方法按条件筛选了数据。

2 数据排序

openpyxl也提供了对数据进行排序的功能。以下是一个示例，演示如何按指定列对数据进行升序排序。

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

# 创建一个Workbook对象
wb = Workbook()

# 获取活动的Sheet
sheet = wb.active

# 写入示例数据
sheet['A1'] = 'Name'
sheet['B1'] = 'Score'

data = [
    ('Alice', 85),
    ('Bob', 92),
    ('Charlie', 78),
    ('David', 95),
    ('Eva', 88)
]

for row in data:
    sheet.append(row)

# 将数据转换为DataFrame
df = pd.DataFrame(data, columns=['Name', 'Score'])

# 按Score列升序排序
df.sort_values(by='Score', inplace=True)

# 将排序后的数据写回Excel
for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=True), 2):
    for c_idx, value in enumerate(row, 1):
        sheet.cell(row=r_idx, column=c_idx, value=value)

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

在上述代码中，使用pandas库将数据转换为DataFrame，然后使用sort_values方法按指定列（这里是’Score’列）进行升序排序，最后将排序后的数据写回Excel。

公式计算

在openpyxl中，可以使用公式对Excel文件中的数据进行计算。以下是如何使用公式的示例，以及一些注意事项。

1 基本公式

首先，可以在单元格中使用基本的Excel函数进行计算。

from openpyxl import Workbook

# 创建一个Workbook对象
wb = Workbook()

# 获取活动的Sheet
sheet = wb.active

# 写入示例数据
sheet['A1'] = 10
sheet['A2'] = 20

# 使用SUM函数计算A1和A2的总和
sheet['A3'] = '=SUM(A1:A2)'

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

在上述代码中，使用了Excel的SUM函数计算了A1和A2的总和。

2 复杂公式

还可以编写更复杂的公式，涉及多个单元格和函数。

from openpyxl import Workbook

# 创建一个Workbook对象
wb = Workbook()

# 获取活动的Sheet
sheet = wb.active

# 写入示例数据
sheet['B1'] = 5
sheet['B2'] = 10
sheet['B3'] = 15

# 使用AVERAGE函数计算B1到B3的平均值
sheet['B4'] = '=AVERAGE(B1:B3)'

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

在上述代码中，使用了Excel的AVERAGE函数计算了B1到B3的平均值。

3 计算日期差

如果数据涉及日期，可以使用日期函数计算日期之间的差值。

from openpyxl import Workbook

# 创建一个Workbook对象
wb = Workbook()

# 获取活动的Sheet
sheet = wb.active

# 写入日期数据
sheet['C1'] = '2023-01-01'
sheet['C2'] = '2023-01-10'

# 使用DATEDIF函数计算C1和C2之间的天数差
sheet['C3'] = '=DATEDIF(C1, C2, "D")'

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

在上述代码中，使用了Excel的DATEDIF函数计算了C1和C2之间的天数差。

4 注意事项

• 在写入公式时，确保在公式字符串前面加上等号（=）。
• 请注意使用Excel支持的函数和语法。

样式设置

在openpyxl中，可以通过设置样式来美化Excel文件，包括设置字体、颜色、边框等方面。以下是如何使用openpyxl进行样式设置的示例。

1 设置字体样式

设置单元格的字体样式，如粗体、斜体、字体颜色等。

from openpyxl import Workbook
from openpyxl.styles import Font

# 创建一个Workbook对象
wb = Workbook()

# 获取活动的Sheet
sheet = wb.active

# 写入数据
sheet['A1'] = 'Hello, World!'

# 设置字体样式
font_style = Font(size=14, bold=True, italic=True, color='FF0000')
sheet['A1'].font = font_style

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

在上述代码中，创建了一个字体样式对象font_style，并将其应用到单元格’A1’。

2 设置背景颜色

还可以设置单元格的背景颜色。

from openpyxl import Workbook
from openpyxl.styles import PatternFill

# 创建一个Workbook对象
wb = Workbook()

# 获取活动的Sheet
sheet = wb.active

# 写入数据
sheet['A1'] = 'Colored Cell'

# 设置背景颜色
fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
sheet['A1'].fill = fill

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

在上述代码中，创建了一个填充样式对象fill，并将其应用到单元格’A1’。

3 设置边框

设置单元格的边框样式，包括左、右、上、下边框。

from openpyxl import Workbook
from openpyxl.styles import Border, Side

# 创建一个Workbook对象
wb = Workbook()

# 获取活动的Sheet
sheet = wb.active

# 写入数据
sheet['A1'] = 'Bordered Cell'

# 设置边框
border_style = Border(left=Side(border_style='thin', color='000000'),
                      right=Side(border_style='thin', color='000000'),
                      top=Side(border_style='thin', color='000000'),
                      bottom=Side(border_style='thin', color='000000'))
sheet['A1'].border = border_style

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

在上述代码中，创建了一个边框样式对象border_style，并将其应用到单元格’A1’。

实际应用场景

openpyxl在实际项目中有着广泛的应用，特别是在需要处理Excel文件的数据报告、数据导出等场景。以下是一些实际应用示例，展示openpyxl在真实项目中的用途。

1 数据报告生成

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

# 创建一个Workbook对象
wb = Workbook()

# 获取活动的Sheet
sheet = wb.active

# 写入数据报告标题
sheet['A1'] = '月度销售报告'
sheet['A1'].font = Font(size=18, bold=True)
sheet['A1'].alignment = Alignment(horizontal='center')

# 写入销售数据
sales_data = [
    ('产品', '销售额', '利润'),
    ('A产品', 5000, 2000),
    ('B产品', 8000, 3500),
    ('C产品', 6000, 2800),
]

for row_idx, row_data in enumerate(sales_data, start=3):
    for col_idx, value in enumerate(row_data, start=1):
        sheet.cell(row=row_idx, column=col_idx, value=value)

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

在这个示例中，使用openpyxl创建了一个销售报告，包括标题、销售数据表格等。通过设置字体、居中对齐等样式，使得报告看起来更加专业。

2 数据导出

from openpyxl import Workbook

# 创建一个Workbook对象
wb = Workbook()

# 获取活动的Sheet
sheet = wb.active

# 写入数据
data_to_export = [
    ['Name', 'Age', 'City'],
    ['Alice', 25, 'New York'],
    ['Bob', 30, 'London'],
    ['Charlie', 22, 'Tokyo'],
]

for row_idx, row_data in enumerate(data_to_export, start=1):
    for col_idx, value in enumerate(row_data, start=1):
        sheet.cell(row=row_idx, column=col_idx, value=value)

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

在这个示例中，使用openpyxl创建了一个简单的数据表格，并将其导出为Excel文件。这在数据导出和交换方面非常常见。

3 批量处理数据

from openpyxl import load_workbook
from openpyxl.styles import Font

# 加载已有的Excel文件
wb = load_workbook('existing_data.xlsx')

# 获取活动的Sheet
sheet = wb.active

# 在现有数据上追加新数据
new_data = [
    ['Eva', 28, 'Berlin'],
    ['David', 35, 'Paris'],
]

for row_data in new_data:
    sheet.append(row_data)

# 设置标题行的字体样式
for cell in sheet['1']:
    cell.font = Font(bold=True)

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

在这个示例中，加载了一个已有的Excel文件，追加了新的数据，并设置了标题行的字体样式。这在批量处理数据的场景中非常实用。

总结

在总结中，深入探索了openpyxl这一强大的Python库，用于处理Excel文件。首先了解了其基本概念，包括Workbook、Sheet和Cell，然后学习了如何读取和写入不同类型的数据。通过示例代码，演示了如何处理图表、图片，以及使用公式进行计算。在样式设置方面，详细介绍了如何设置字体、颜色、边框等，以美化Excel文件中的数据。通过实际应用示例，展示了openpyxl在数据报告生成、数据导出、批量处理数据等方面的广泛应用。

尤其值得强调的是，openpyxl的灵活性和功能丰富性使其成为处理Excel数据的理想选择，适用于没有安装Microsoft Excel的环境中。