Openpyxl 教程-Python Openpyxl 教程

点击关注 👉2024年06月09日约 10 分钟

整理：python技术迷

在这个教程中，我们将学习以下主题：

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

Openpyxl 介绍

Python 提供了 Openpyxl 模块，用于处理 Excel 文件，无需第三方 Microsoft 应用软件。通过使用这个模块，我们可以在不打开应用程序的情况下控制 excel。它用于执行诸如从 excel 文件读取数据或向 excel 文件写入数据、绘制一些图表、访问 excel 表格、重命名表格、在 excel 表格中进行修改（添加和删除）、格式化、样式设置以及任何其他任务。Openpyxl 在执行这些任务时非常高效。

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

专属福利

👉点击领取：最全Python资料合集

Openpyxl 工作流程

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

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

Openpyxl 安装

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

pip install 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 类。工作簿类是包含文档所有部分的容器。

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

wb = Workbook()

我们获取了活动表的位置。

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

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

向单元格写入数据

我们可以使用以下 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 提供了一个 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')

输出：

从单元格读取数据

我们可以读取我们之前在单元格中写入的数据。有两种方法可以读取单元格，首先我们可以通过单元格名称访问它，其次我们可以通过 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: 87The second cell value: DevanshThe third cell value: 41.8

从多个单元格读取数据

我们可以从多个单元格读取值。在以下示例中，我们有一个名为 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        MarksTony Stark           47Loki                 59Oddin                73Nick Fury            62Samaul               75Peter Parkar         80

按行迭代

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 提供了 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

工作表

正如我们所知，每个工作簿可以有多个表格。首先，我们需要在单个工作簿中创建多个表格，然后我们可以使用 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

它看起来像下图。

数据过滤和排序

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')

输出：

A3:B1140 2693 4523 5480 4321 1263 2934 1580 6820 41

合并单元格

我们可以使用 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')

输出：

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

公式

我们可以向单元格中写入公式。这些公式用于在 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 单元格反转器用于反转电子表格中单元格的行和列。例如，位于第 3 行的值将反转到第 5 列，位于第 5 行的值将反转到第 3 列（反之亦然）。您可以在以下图像中看到：

这个程序是通过嵌套 for 循环编写的。首先数据结构在 sheetDatax 中为位于列 x 和行 y 的单元格写入数据，然后在新创建的电子表格中的 spreadDatay 中为位于列 y 和行 x 的单元格写入数据。

向 Excel 文件添加图表

图表是表示数据的有效方式。使用图表，它可以直观地展示数据，这些数据可以很容易地被理解。有各种类型的图表：饼图、折线图、条形图等。我们可以使用 openpyxl 模块在电子表格上绘制图表。

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

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 的附加模块，使用以下命令。

pip install 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 的所有基础和高级概念。


热门推荐

整理：python技术迷

Openpyxl 介绍

Openpyxl 工作流程

Openpyxl 安装

向单元格写入数据

添加值

从单元格读取数据

从多个单元格读取数据

按行迭代

按列迭代

工作表

数据过滤和排序

合并单元格

公式

单元格反转器

向 Excel 文件添加图表

添加图片

来自同一个声音

杀人诛心！推荐的面试候选人，连一道最基础的“三数求和(3 Sum)”题都无法完成。因此，我们决定也为你重新安排一次技术面试

fabric，一个神奇的 Python 库！

resources-to-become-a-great-engineering-leader：程序员转管理，这份 6 年书架先收好

clevercsv，一个有趣的 Python 库！