Openpyxl教程-Python Openpyxl 教程

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

• 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 来执行不同的操作，如从数据复制到数据挖掘以及数据分析等。

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: 87
The second cell value: Devansh
The 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        Marks
Tony Stark           47
Loki                 59
Oddin                73
Nick Fury            62
Samaul               75
Peter 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:B11
40 26
93 45
23 54
80 43
21 12
63 29
34 15
80 68
20 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 的所有基础和高级概念。