Python Openpyxl

2025年3月17日 | 阅读 8 分钟

在本教程中，我们将学习以下主题

Openpyxl 简介
Openpyxl 工作流程
Openpyxl 安装
Openpyxl 写入数据到单元格
Openpyxl 读取 Excel 文件
Openpyxl 从单元格读取数据
Openpyxl 读取多个单元格
Openpyxl 工作表
Openpyxl 筛选和排序数据
Openpyxl 合并单元格
Openpyxl 追加值
Openpyxl 按列迭代
Openpyxl 按行迭代
Openpyxl 向 Excel 文件添加图表
Openpyxl 添加图片

Python Openpyxl 简介

Python 提供了 Openpyxl 模块，用于处理 Excel 文件，而无需涉及第三方 Microsoft 应用程序软件。通过使用此模块，我们可以控制 Excel 而无需打开应用程序。它用于执行 Excel 任务，例如**从 Excel 文件读取数据，或将数据写入 Excel 文件，绘制图表，访问 Excel 工作表，重命名工作表，修改（添加和删除）Excel 工作表，设置格式，设置工作表样式**，以及任何其他任务。Openpyxl 在为您执行这些任务方面非常高效。

数据科学家经常使用 Openpyxl 执行不同的操作，例如数据复制到数据挖掘以及数据分析。

Openpyxl 工作流程

Openpyxl 库用于在 Excel 文件中写入或读取数据以及许多其他任务。我们用于操作的 Excel 文件称为工作簿，其中包含最少一个工作表，最多几十个工作表。

工作表由从 1 开始的行（水平系列）和从 A 开始的列（垂直系列）组成。
行和列共同构成一个网格并形成一个单元格，该单元格可以存储一些数据。数据可以是任何类型，例如数字、字符串。
Openpyxl 提供了从单个单元格读取数据或向其写入数据的灵活性。

Openpyxl 的安装

在上一节中，我们简要讨论了 **openpyxl** 及其工作流程。为了使用 Openpyxl，系统应该安装 Python 3.7 和 **openpyxl** 2.6.2。让我们通过使用以下命令安装 **openpyxl** 来开始使用 openpyxl

**xlsx** 是 XML 电子表格文件的扩展名。xlsx 文件支持宏。让我们了解与 Excel 文件相关的基本操作。考虑以下代码

from openpyxl import Workbook
import time

wb = Workbook()
sheet = wb.active

sheet['A1'] = 87
sheet['A2'] = "Devansh"
sheet['A3'] = 41.80
sheet['A4'] = 10

now = time.strftime("%x")
sheet['A5'] = now

wb.save("sample_file.xlsx")

输出

在上面的代码中，我们向五个单元格 A1、A2、A3、A4 和 A5 写入了数据。这些单元格包含不同类型的值。我们从 openpyxl 模块导入了 Workbook 类。Workbook 类是一个容器，包含文档的所有部分。

在这里我们定义了一个新的工作簿。一个工作簿至少会创建一个工作表。

我们获取活动工作表的位置。

sheet['A1'] = 87
sheet['A2'] = 'Devansh'

我们使用 **save()** 方法将所有数据保存到 **sample_file.xlsx** 文件中。

Openpyxl 写入数据到单元格

我们可以使用以下 Python 代码向 Excel 文件添加数据。首先，我们将从 **openpyxl** 模块导入 **load_workbook** 函数，然后创建文件对象并将 **filepath** 作为参数传递。考虑以下代码

from openpyxl import load_workbook
wb = load_workbook(r'C:\Users\DEVANSH SHARMA\Desktop\demo.xlsx')

sheet = wb.active
sheet['A1'] = 'Devansh Sharma'

sheet.cell(row=2, column=2).value = 5
wb.save(r'C:\Users\DEVANSH SHARMA\Desktop\demo.xlsx')

输出

Openpyxl 追加值

Openpyxl 提供了 **append()** 方法，用于追加一组值。我们可以追加任何类型的值。这些值被追加到当前工作表的底部。考虑以下代码

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

data = (
    (11, 48, 50),
    (81, 30, 82),
    (20, 51, 72),
    (21, 14, 60),
    (28, 41, 49),
    (74, 65, 53),
    ("Peter", 'Andrew',45.63)
)

for i in data:
    sheet.append(i)
wb.save('appending_values.xlsx')

输出

Openpyxl 从单元格读取数据

我们可以读取我们之前在单元格中写入的数据。有两种方法可以读取单元格，首先我们可以通过单元格名称访问它，其次我们可以通过 cell() 函数访问它。例如，我们正在从 sample_file.xlrs 文件中读取数据。

import openpyxl

wb = openpyxl.load_workbook('sample_file.xlsx')

sheet = wb.active

x1 = sheet['A1']
x2 = sheet['A2']
#using cell() function
x3 = sheet.cell(row=3, column=1)

print("The first cell value:",x1.value)
print("The second cell value:",x2.value)
print("The third cell value:",x3.value)

输出

The first cell value: 87
The second cell value: Devansh
The third cell value: 41.8

Openpyxl 读取多个单元格

我们可以从多个单元格读取值。在以下示例中，我们有一个名为 **marks.xlsx** 的 Excel 文件，我们将使用范围运算符读取文件的每个单元格。让我们看看以下程序

import openpyxl

wb = openpyxl.load_workbook('marks.xlsx')

sheet = wb.active
#
cells = sheet['A1','B7']
# cells behave like range operator
for i1,i2 in cells:
    print("{0:8} {1:8}".format(i1.value,i2.value))

输出

Student_name        Marks
Tony Stark           47
Loki                 59
Oddin                73
Nick Fury            62
Samaul               75
Peter Parkar         80

Openpyxl 按行迭代

openpyxl 提供了 **iter_row()** 函数，用于读取与行对应的数据。考虑以下示例

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

rows = (
    (90, 46, 48, 44),
    (81, 30, 32, 16),
    (23, 95, 87,27),
    (65, 12, 89, 53),
    (42, 81, 40, 44),
    (34, 51, 76, 42)
)

for row in rows:
    sheet.append(row)

for row in sheet.iter_rows(min_row=1, min_col=1, max_row=6, max_col=4):
    for cell in row:
        print(cell.value, end=" ")
    print()

book.save('iter_rows.xlsx')

输出

90 46 48 44 
81 30 32 16 
23 95 87 27 
65 12 89 53 
42 81 40 44 
34 51 76 42

Openpyxl 按列迭代

openpyxl 提供了 **iter_col()** 方法，该方法将工作表中的单元格作为列返回。考虑以下示例

from openpyxl import Workbook

book = Workbook()
sheet = book.active

rows = (
     (90, 46, 48, 44),
     (81, 30, 32, 16),
     (23, 95, 87, 27),
     (65, 12, 89, 53),
     (42, 81, 40, 44),
     (34, 51, 76, 42)
)
for row in rows:
    sheet.append(row)

for row in sheet.iter_cols(min_row=1, min_col=1, max_row=6, max_col=3):
    for cell in row:
        print(cell.value, end=" ")
    print()

book.save('iterbycols.xlsx')

输出

90 81 23 65 42 34 
46 30 95 12 81 51 
48 32 87 89 40 76

Openpyxl 工作表

我们知道每个工作簿可以有多个工作表。首先，我们需要在一个工作簿中创建多个工作表，然后我们可以使用 Python 访问这些 Excel 工作表。在以下示例中，我们创建了一个包含三个工作簿的工作簿

import openpyxl
wb = openpyxl.load_workbook('dimension_1.xlsx')

#Getting list of all sheet available in workbook
print(wb.get_sheet_names())

# Returning object
active_sheet = wb.active
print(type(active_sheet))

# Title of sheet
sheet = wb.get_sheet_by_name("Monday")
print(sheet.title)

输出

['Sheet', 'Sunday', 'Monday', 'Tuesday', 'Wednesday']
<class 'openpyxl.worksheet.worksheet.Worksheet'>
Monday

它将如下所示。

Openpyxl 筛选和排序数据

**auto_filter** 属性用于设置筛选和排序条件。考虑以下代码

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

sheet['A3'] = 40
sheet['B3'] = 26

row_count = [
    (93,45),
    (23,54),
    (80,43),
    (21,12),
    (63,29),
    (34,15),
    (80,68),
    (20,41)
]

for row in row_count:
    sheet.append(row)

print(sheet.dimensions)

for a1,a2 in sheet[sheet.dimensions]:
    print(a1.value, a2.value)

sheet.auto_filter.add_sort_condition('B2:B8')
sheet.auto_filter.add_filter_column(1, ['40', '26'])

wb.save('dimension_1.xlsx')

输出

Openpyxl 合并单元格

我们可以使用 **merge_cells()** 方法合并单元格。当我们合并单元格时，左上角的单元格将从工作表中移除。**openpyxl** 还提供了 **unmerged_cells()** 方法来取消合并单元格。考虑以下代码

from openpyxl.styles import Alignment

wb = Workbook()
sheet = wb.active

sheet.merge_cells('A1:B2')

cell = sheet.cell(row=1, column=1)
cell.value = 'Devansh Sharma'
cell.alignment = Alignment(horizontal='center', vertical='center')

wb.save('merging.xlsx')

输出

冻结窗格简单地意味着在滚动到工作表的其他部分时冻结工作表的一个区域使其可见。这是一个有用的功能，我们可以将顶行或最左侧的列显示在屏幕上。我们可以通过将单元格名称传递给 **freeze_panes** 变量来实现。要取消冻结所有窗格，请将 freeze_panes 设置为“**None**”。考虑以下代码

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
sheet = wb.active

sheet.freeze_panes = 'A1'

wb.save('freez_cells.xlsx')

输出

运行上述代码并滚动工作表。

Openpyxl 公式

我们可以将公式写入单元格。这些公式用于在 Excel 文件中执行操作。在单元格中写入后，从工作簿中执行它。考虑以下示例

from openpyxl import Workbook
wb = Workbook()
sheet = wb.active

rows_count = (
    (14, 27),
    (22, 30),
    (42, 92),
    (51, 32),
    (16, 60),
    (63, 13)
)

for i in rows_count:
    sheet.append(i)

cell = sheet.cell(row=7, column=3)
cell.value = "=SUM(A1:B6)"
cell.font = cell.font.copy(bold=True)

wb.save('formulas_book.xlsx')

输出

Openpyxl 单元格反转

**openpyxl** 单元格反转器用于反转电子表格中单元格的行和列。例如，第 3 行的值将反转到第 5 列，第 5 行的值将反转到第 3 列（反之亦然）。您可以在以下图片中看到

此程序借助嵌套的 for 循环编写。首先，数据结构写入 sheetData[x][y] 用于列 x 和行 y 的单元格，然后新创建的电子表格写入 spreadData[y][x] 用于列 y 和行 x 的单元格。

向 Excel 文件添加图表

图表是表示数据的有效方式。使用图表，可以轻松可视化数据。图表有各种类型：饼图、折线图、条形图等等。我们可以使用 **openpyxl** 模块在电子表格上绘制图表。

为了在电子表格上构建任何图表，我们需要定义图表类型，如 BarChart、LineChart 等。我们还导入表示用于图表的数据的引用。定义我们想要在图表上表示的数据很重要。让我们通过以下示例来理解

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

wb = Workbook()
sheet = wb.active

# Let's create some sample student data
rows = [
    ["Serial_no", "Roll no", "Marks"],
    [1, "0090011", 75],
    [2, "0090012", 60],
    [3, "0090013", 43],
    [4, "0090014", 97],
    [5, "0090015", 63],
    [6, "0090016", 54],
    [7, "0090017", 86],
]

for i in rows:
    sheet.append(i)

chart = BarChart()
values = Reference(worksheet=sheet,
                 min_row=1,
                 max_row=8,
                 min_col=2,
                 max_col=3)

chart.add_data(values, titles_from_data=True)
sheet.add_chart(chart, "E2")

wb.save("student_chart.xlsx")

输出

在上面的代码中，我们创建了示例数据并绘制了与示例数据对应的条形图。

现在我们将创建折线图。考虑以下代码

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

wb = Workbook()
spreadsheet = wb.active

# Let's create some sample data
rows = [
    ["", "January", "February", "March", "April",
    "May", "June", "July", "August", "September",
     "October", "November", "December"],
    [1, ],
    [2, ],
    [3, ],
]

for row in rows:
    spreadsheet.append(row)

for row in spreadsheet.iter_rows(min_row=2,
                           max_row=4,
                           min_col=2,
                           max_col=13):
    for cell in row:
        cell.value = random.randrange(5, 100)

chart = LineChart()
data = Reference(worksheet=spreadsheet,
                 min_row=2,
                 max_row=4,
                 min_col=1,
                 max_col=13)

chart.add_data(data, from_rows=True, titles_from_data=True)
spreadsheet.add_chart(chart, "C6")

wb.save("line_chart1.xlsx")

输出

在上面的代码中，我们使用 **from_rows = True** 作为参数，它表示图表按行而不是按列绘制。

添加图像

图像通常不用于电子表格，但有时我们可以根据需要使用。我们可以使用图像用于品牌宣传或使电子表格更个性化和有吸引力。为了将图像加载到电子表格中，我们需要通过以下命令安装一个名为 pillow 的附加模块。

在以下程序中，我们将图像导入到 Excel 文件中。

from openpyxl import load_workbook
from openpyxl.drawing.image import Image

# Let's use the hello_world spreadsheet since it has less data
workbook = load_workbook(filename="student_chart1.xlsx")
sheet = workbook.active

logo = Image(r"C:\Users\DEVANSH SHARMA\Pictures\Screenshots\image.png")

# A bit of resizing to not fill the whole spreadsheet with the logo
logo.height = 150
logo.width = 150

sheet.add_image(logo, "E2")
workbook.save(filename="hello_world_logo1.xlsx")

在本教程中，我们涵盖了 **openpyxl** 的所有基本和高级概念。

我们提供所有技术（如 Java 教程、Android、Java 框架）的教程和面试问题

联系信息

G-13, 2nd Floor, Sec-3, Noida, UP, 201301, India

hr@tpointtech.com

+91-9599086977

关注我们

Python

Java

.Net Framework

AI, ML and Data Science

Cloud Technology

B.Tech and MCA

Web Technology

PHP

Software Testing

Technical Interview

Java Interview

Python

Web Interview

Database Interview

B.Tech / MCA

Important Interview

Software Testing Interview

Company Interviews

Online Compilers

Multiple Choice Questions

Python教程

Python变量和数据类型

Python控制语句

Python数据结构

Python函数

Python模块

Python OOP

Python异常处理

Python文件处理

Python搜索和排序

Python高级主题

Python MySQL

Python MongoDB

Python SQLite

Python MCQ

Python Tkinter (GUI)

Python Web Blocker

Python内置函数

Python字符串函数

Python列表

Python字典

Plotly

相关教程