Excel文件处理-Python之openpyxl

Excel是个表格处理工具，经典、好用。python也有多个库支持处理Excel文件，我们只需要掌握其中一个即可。本章的主角是openpyxl库。

安装

安装：pip install openpyxl
导入：import openpyxl

Excel操作

excel文件操作

方法	说明
Workbook()	创建Workbook对象；可以理解为excel临时文件，保存后才是永久的excel文件，使用时需要导入Workbook： openpyxl.Workbook
load_workbook(filename, read_only=False, keep_vba=False, data_only=False, keep_links=True)	加载已存在的Excel文件（不存在时会报错），使用时需要导入load_workbook方法：openpyxl.load_workbook
save(filename)	保存，如果对Workbook对象有修改或为新建的需要保存，则必须调用该方法让新建或修改等操作实际生效。

Workbook()和load_workbook(文件路径)执行后，返回的都是<class 'openpyxl.workbook.workbook.Workbook'>数据类型

Workbook()

创建excel文件时，需要进行如下步骤：
1、创建workbook对象
2、获取被激活的worksheet（步骤1会自动创建一个sheet）
3、设置内容（如果没有内容设置，可以忽略）
4、保存（只有在这个步骤，本地设备才能看到文件，前面的步骤只是有相关的对象，但是数据还没有真实的被保存到本地）
示例代码：

from openpyxl import Workbook
# 创建一个 workbook
wb = Workbook()
# 获取被激活的 worksheet
ws = wb.active
# 设置内容
ws['A1'] = "新的值"
# 按照指定路径（包含文件名）保存
wb.save("D:\\test_code\\练习\\新建.xlsx")
print(type(wb))

代码执行后，在对应的目录下自动生成了个xlsx文件，文件内容如下图：

控制台输出内容为：

load_workbook()

load_workbook(filename, read_only=False, keep_vba=False, data_only=False, keep_links=True)是加载一个已存在的Excel文件，相关参数说明如下：

• read_only：是否只读模式，对于超大型文件（只读不写），要提升效率有帮助
• keep_vba：是否保留 vba 代码，即打开 Excel 文件时，开启并保留宏
• data_only：是否将公式转换为结果，即包含公式的单元格，是否显示最近的计算结果
• keep_links：是否保留外部链接

示例代码：

from openpyxl import load_workbook

wb = load_workbook("D:\\test_code\\练习\\七八十.xlsx")
print(type(wb))
print(wb.sheetnames)

被操作的excel文件内容如下：

代码执行结果为：

<class 'openpyxl.workbook.workbook.Workbook'>
['工作表格']

save(filename)

如果对Excel文件内容有修改（工作表、单元格等），修改需要保存的话，可以使用Workbook对象的save(filename)方法对文件进行保存。save(filename)是对整个Workbook对象进行保存，如果filename已存在的话，就相当于filename被覆盖了，不存在的话就相当于生成一个新的表格文件
示例：
成绩单当前内容为：

代码为：

from openpyxl import load_workbook

wb = load_workbook("D:\\test_code\\练习\\成绩单.xlsx")
ws = wb.active

ws["A6"] = "qiqi"
ws["A7"] = "巴拉"
wb.save("D:\\test_code\\练习\\成绩单_版本2.xls")

ws["A8"] = "哈哈"     # 没有被保存的更改

执行后，生成了 成绩单_版本2.xls 的文件，文件内容如下：

sheet操作

方法	说明
create_sheet(title=None, index=None)	创建新的工作表，名称是title（默认是Sheet[n]，位置是index（未输入则默认在最后插入，Index从0开始计算）
title	Worksheet对象（即工作表）的属性，表示工作表名称，可以通过修改这个属性来修改工作表的名称
active	Workbook对象的属性，默认获取当前的工作表（excel文件打开时展示的工作表）
sheetnames	Workbook对象的属性，列表形式存储excel表的工作列表名称
Workbook()[工作表名称]	可以通过工作表名称从Workbook对象中获取对应的工作表对象
remove(worksheet)	Workbook对象的方法，删除指定的工作表

创建工作表

create_sheet(title=None, index=None)是Workbook对象的一个方法，用来创建工作表，相关参数说明如下：

• title：工作表名称，非必传，默认为Sheet（如果有冲突，则Sheet1、Sheet2…叠加取名）
• index：指定工作表在Excel文件中的位置，开头是从0开始排序的，末尾是从-1开始排序的，该参数非必传，默认在工作表列表末尾插入

示例：

from openpyxl import load_workbook

wb = load_workbook("D:\\test_code\\练习\\七八十.xlsx")

ws1 = wb.create_sheet()     # 在末尾插入工作表 Sheet
ws2 = wb.create_sheet(index=0)  # 在开头插入工作表 Sheet1
ws3 = wb.create_sheet("笔记", 2)  # 在位置3（下标从0开始）插入工作表 笔记
ws4 = wb.create_sheet("笔记2", -3)

wb.save("D:\\test_code\\练习\\七八十.xlsx")

在执行前，七八十.xlsx的内容如下，只有一个“工作表格”的工作表

执行后，七八十.xlsx的工作表如下：

Worksheet().title

可以通过对工作表对象中的title属性的查询或修改获取工作表名称

from openpyxl import load_workbook

wb = load_workbook("D:\\test_code\\练习\\七八十.xlsx")

ws = wb.create_sheet("工作表1")
print(f"工作表当前名称为：{ws.title}")

# 修改工作表名称为：工作表demo
ws.title = "工作表demo"
print(f"修改后的工作表名称为：{ws.title}")

执行前，对应的excel文件，工作表名称是”工作表格“

执行后，控制台输出内容为：

工作表当前名称为：工作表格
修改后的工作表名称为：工作表demo

excel文件中，工作表名称不是”工作表格“了，是”工作表demo”了

获取工作表

获取当前工作表

Workbook对象下有个active属性，获取当前的工作表
示例：

from openpyxl import load_workbook

wb = load_workbook("D:\\test_code\\练习\\七八十.xlsx")

ws = wb.active
print(f"工作表当前名称为：{ws.title}")

七八十.xlsx的工作表如下：

执行后，控制台输出如下：

工作表当前名称为：出差明细

查询所有工作表名称

Workbook对象有个方法sheetnames可以获取到当前excel文件的所有工作表名称

from openpyxl import load_workbook

wb = load_workbook("D:\\test_code\\练习\\七八十.xlsx")

print(wb.sheetnames)
print(type(wb.sheetnames))

七八十.xlsx的工作表如下：

执行后，控制台输出如下：

['加班明细', '出差明细', '日常报销']
<class 'list'>

根据名称获取对应的工作表

Workbook对象支持通过工作表名称来获取对应的Worksheet对象，也可以对Workbook对象进行遍历
示例：

from openpyxl import load_workbook

wb = load_workbook("D:\\test_code\\练习\\七八十.xlsx")

# 通过工作表名称获取对应的sheet对象
ws_baoxiao = wb["日常报销"]
print(ws_baoxiao["A1"].value)

print("--------遍历--------")
for ws in wb:
    print(ws.title)

七八十.xlsx文件内容如下：

代码执行结果如下：

20
--------遍历--------
加班明细
出差明细
日常报销

删除工作表

Workbook对象可以通过remove()方法删除指定的工作表，该方法需要传入Worksheet对象。
示例：

from openpyxl import load_workbook

wb = load_workbook("D:\\test_code\\练习\\七八十.xlsx")

ws = wb["出差明细"]
wb.remove(ws)

wb.save("D:\\test_code\\练习\\七八十.xlsx")

执行前，七八十.xlsx的文件内容如下：

执行成功后，七八十.xlsx的文件内容如下：

单元格操作

从文件到工作表之后，接下来的操作就是单元格了，单元格的操作是再工作表对象Worksheet对象的基础上进行的，每个单元格对象，都有个value属性，通过该属性可以访问单元格的内容，格式：单元格.value

获取/设置某个单元格

工作表Worksheet对象提供了2种方法来获取或设置指定单元格的内容

• ["单元格位置"]：单元格位置由所在列和所在行决定，如：A1表示A列第1行、E3表示E列第3行
• cell(row, column, value=None)：如果value不为None，则给指定位置的单元格赋值为value参数值并返回value，如果value为None，则返回指定位置的单元格的值。row表示行数，column表示列，row和column都是必传的参数，用来确定单元格

示例：

from openpyxl import load_workbook

wb = load_workbook("D:\\test_code\\练习\\成绩单.xlsx")
ws = wb.active

# 获取七喜的数学成绩
score1 = ws["C6"]
print(f"单元格的类是：{type(score1)}")
print(f"七喜的理综成绩是：{score1.value}")
# 获取王五的理综成绩
score2 = ws.cell(row=4, column=5)
print(f"王五的理综成绩是：{score2.value}")

# 修改八方的数学成绩为69
ws["C7"] = 69
# 修改八方的理综成绩为211
ws.cell(row=7, column=5, value=211)

# 另存为成绩单_新.xlsx
wb.save("D:\\test_code\\练习\\成绩单_新.xlsx")

成绩单.xlsx的文件内容如下：

执行代码后，控制台输出如下：

单元格的类是：<class 'openpyxl.cell.cell.Cell'>
七喜的理综成绩是：73
王五的理综成绩是：189

在 D:\test_code\练习 目录下，多了个 成绩单_新.xlsx 文件，文件内容如下：

获取单行/列

Worksheet对象支持获取单行或单列的单元格对象数据，格式是：[row_or_col]，其中row_or_col是某行数或列数，返回的数据是元组，元组中的元素是Cell对象
示例：

from openpyxl import load_workbook

wb = load_workbook("D:\\test_code\\练习\\成绩单.xlsx")
ws = wb.active

# 获取老六的考试成绩
scores = ws["5"]
print(f"ws[行数或列数]返回的是元组：{type(scores)}，元组的元素是Cell对象：{type(scores[0])}，示例：{scores}")
for score in scores:
    print(score.value, end="    ")
print()

# 获取全班的理综成绩
scores_liz = ws["E"]
print(f"列数据：{scores_liz}")
for score in scores_liz:
    print(score.value, end="    ")

成绩单.xlsx的内容如下：

执行代码后，控制台输出内容为：

ws[行数或列数]返回的是元组：<class 'tuple'>，元组的元素是Cell对象：<class 'openpyxl.cell.cell.Cell'>，示例：(<Cell '加班明细'.A5>, <Cell '加班明细'.B5>, <Cell '加班明细'.C5>, <Cell '加班明细'.D5>, <Cell '加班明细'.E5>)
老六    94    59    64    None    
列数据：(<Cell '加班明细'.E1>, <Cell '加班明细'.E2>, <Cell '加班明细'.E3>, <Cell '加班明细'.E4>, <Cell '加班明细'.E5>, <Cell '加班明细'.E6>, <Cell '加班明细'.E7>, <Cell '加班明细'.E8>)
理综    234    176    189    None    205    200    278   

获取连续的多行/列

如果想获取连续的多行或多列的数据，可以根据Worksheet对象的["begin:end"]来获取

• begin表示起始行/列，end表示结束行/列
• begin和end都是使用的闭包的，即获取的数据包含了end和begin
• 获取到的数据是个元组，元组中的每个元素表示一行或一列数据（也是元组），每一行/列的元素才是Cell对象，表示该行/列下的单元格

成绩单.xlsx的内容如下：

代码内容如下：

from openpyxl import load_workbook

wb = load_workbook("D:\\test_code\\练习\\成绩单.xlsx")
ws = wb.active

# 获取第4行到第7行的人员成绩
rows = ws["4:7"]
print(rows)
for row in rows:
    print(row)
    for cell in row:
        print(cell.value, end="  ")
    print()

# 获取语文、数学和英语成绩
cols = ws["B:D"]
print("---------------列---------------")
print(cols)
for col in cols:
    print(col)
    for cell in col:
        print(cell.value, end="  ")
    print()

执行结果为：

((<Cell '加班明细'.A4>, <Cell '加班明细'.B4>, <Cell '加班明细'.C4>, <Cell '加班明细'.D4>, <Cell '加班明细'.E4>), (<Cell '加班明细'.A5>, <Cell '加班明细'.B5>, <Cell '加班明细'.C5>, <Cell '加班明细'.D5>, <Cell '加班明细'.E5>), (<Cell '加班明细'.A6>, <Cell '加班明细'.B6>, <Cell '加班明细'.C6>, <Cell '加班明细'.D6>, <Cell '加班明细'.E6>), (<Cell '加班明细'.A7>, <Cell '加班明细'.B7>, <Cell '加班明细'.C7>, <Cell '加班明细'.D7>, <Cell '加班明细'.E7>))
(<Cell '加班明细'.A4>, <Cell '加班明细'.B4>, <Cell '加班明细'.C4>, <Cell '加班明细'.D4>, <Cell '加班明细'.E4>)
王五  83  60  83  189  
(<Cell '加班明细'.A5>, <Cell '加班明细'.B5>, <Cell '加班明细'.C5>, <Cell '加班明细'.D5>, <Cell '加班明细'.E5>)
老六  94  59  64  None  
(<Cell '加班明细'.A6>, <Cell '加班明细'.B6>, <Cell '加班明细'.C6>, <Cell '加班明细'.D6>, <Cell '加班明细'.E6>)
七喜  69  73  90  205  
(<Cell '加班明细'.A7>, <Cell '加班明细'.B7>, <Cell '加班明细'.C7>, <Cell '加班明细'.D7>, <Cell '加班明细'.E7>)
八方  77  28  73  200  
---------------列---------------
((<Cell '加班明细'.B1>, <Cell '加班明细'.B2>, <Cell '加班明细'.B3>, <Cell '加班明细'.B4>, <Cell '加班明细'.B5>, <Cell '加班明细'.B6>, <Cell '加班明细'.B7>, <Cell '加班明细'.B8>), (<Cell '加班明细'.C1>, <Cell '加班明细'.C2>, <Cell '加班明细'.C3>, <Cell '加班明细'.C4>, <Cell '加班明细'.C5>, <Cell '加班明细'.C6>, <Cell '加班明细'.C7>, <Cell '加班明细'.C8>), (<Cell '加班明细'.D1>, <Cell '加班明细'.D2>, <Cell '加班明细'.D3>, <Cell '加班明细'.D4>, <Cell '加班明细'.D5>, <Cell '加班明细'.D6>, <Cell '加班明细'.D7>, <Cell '加班明细'.D8>))
(<Cell '加班明细'.B1>, <Cell '加班明细'.B2>, <Cell '加班明细'.B3>, <Cell '加班明细'.B4>, <Cell '加班明细'.B5>, <Cell '加班明细'.B6>, <Cell '加班明细'.B7>, <Cell '加班明细'.B8>)
语文  78  56  83  94  69  77  86  
(<Cell '加班明细'.C1>, <Cell '加班明细'.C2>, <Cell '加班明细'.C3>, <Cell '加班明细'.C4>, <Cell '加班明细'.C5>, <Cell '加班明细'.C6>, <Cell '加班明细'.C7>, <Cell '加班明细'.C8>)
数学  95  79  60  59  73  28  85  
(<Cell '加班明细'.D1>, <Cell '加班明细'.D2>, <Cell '加班明细'.D3>, <Cell '加班明细'.D4>, <Cell '加班明细'.D5>, <Cell '加班明细'.D6>, <Cell '加班明细'.D7>, <Cell '加班明细'.D8>)
英语  77  84  83  64  90  73  89

获取连续范围数据

Worksheet对象还支持按照行和列获取指定的连续范围的数据

方法
iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)	按照行获取指定行、列的数据
iter_cols(min_col=None, max_col=None, min_row=None, max_row=None, values_only=False)	按照列获取指定行、列的数据
["最左上单元格坐标:最右下单元格坐标"]	从左上角的单元格和右小角的单元格坐标获取指定了行、列之间的单元格元组，获取的数据是元组，元组中的每个参数代表一行，行参数是Cell对象组成的元组

iter_rows()

iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False) 是根据行（最小行min_row，和最大行max_row）、列（最小列min_col，和最大列max_col），以行的格式返回相应范围的数据

• min_row：最小行坐标，非必传，默认为1，表示所取连续单元格中，行数最小值
• max_row：最大行坐标，非必传，默认为当前工作表单元格行数最大值，表示所取连续单元格中，行数的最大值
• min_col：最大行坐标，非必传，默认为1，表示所取连续单元格中，大数最小值
• max_col：最大行坐标，非必传，默认为当前工作表单元格大数最大值，表示所取连续单元格中，大数的最大值
• values_only：是否只返回单元格的值，非必传，默认为False，表示返回数据的基本元素是单元格对象（即返回数据为元组，每个元素代表一行，也是元组表示，行的元组的元素是单元格对象Cell)，若设置为False，则基本元素是单元格的值。
• 返回值：返回generator对象（实际上是个元组），每一行是个元素，行元素是也是元组组成，根据values_only，若为False，则行元组组成的元素是单元格对象（Cell)，为True则组成元素是单元格的值。
  示例：
  成绩单.xlsx的内容如下：
  
  代码内容为：

from openpyxl import load_workbook

wb = load_workbook("D:\\test_code\\练习\\成绩单.xlsx")
ws = wb.active

# 获取第4行到第7行的数学、英语成绩
rows_cell = ws.iter_rows(min_row=3, max_row=4, min_col=3, max_col=4)
print(rows_cell)
print(type(rows_cell))
for row in rows_cell:
    print(row)
    for cell in row:
        print(cell.value, end="  ")
    print()

print("\n-------------values_only-------------")
rows_value = ws.iter_rows(min_row=3, max_row=4, min_col=3, max_col=4, values_only=True)
print(rows_value)
for row in rows_value:
    print(row)

执行后，控制台输出为：

<generator object Worksheet._cells_by_row at 0x0000027FAF5177D0>
<class 'generator'>
(<Cell '加班明细'.C3>, <Cell '加班明细'.D3>)
79  84  
(<Cell '加班明细'.C4>, <Cell '加班明细'.D4>)
60  83  

-------------values_only-------------
<generator object Worksheet._cells_by_row at 0x0000027FAF5176F0>
(79, 84)
(60, 83)

iter_cols()

iter_cols(min_col=None, max_col=None, min_row=None, max_row=None, values_only=False)的使用与iter_rows()类似，区别是iter_cols()的返回数据是以列为单位
示例：
成绩单.xlsx的内容如下：

from openpyxl import load_workbook

wb = load_workbook("D:\\test_code\\练习\\成绩单.xlsx")
ws = wb.active

# 获取前4位的成绩（单纯成绩，不要列名、姓名）
cols_cell = ws.iter_cols(min_row=2, max_row=5, min_col=2)
print(cols_cell)
for col in cols_cell:
    print(col)
    for cell in col:
        print(cell.value, end="  ")
    print()

print("\n-------------values_only为True-------------")
cols_cell = ws.iter_cols(min_row=2, max_row=5, min_col=2, values_only=True)
print(cols_cell)
for col in cols_cell:
    print(col)

执行结果如下：

<generator object Worksheet._cells_by_col at 0x000002C68A9677D0>
(<Cell '加班明细'.B2>, <Cell '加班明细'.B3>, <Cell '加班明细'.B4>, <Cell '加班明细'.B5>)
78  56  83  94  
(<Cell '加班明细'.C2>, <Cell '加班明细'.C3>, <Cell '加班明细'.C4>, <Cell '加班明细'.C5>)
95  79  60  59  
(<Cell '加班明细'.D2>, <Cell '加班明细'.D3>, <Cell '加班明细'.D4>, <Cell '加班明细'.D5>)
77  84  83  64  
(<Cell '加班明细'.E2>, <Cell '加班明细'.E3>, <Cell '加班明细'.E4>, <Cell '加班明细'.E5>)
234  176  189  None  
-------------values_only为True-------------
<generator object Worksheet._cells_by_col at 0x000002C68A9676F0>
(78, 56, 83, 94)
(95, 79, 60, 59)
(77, 84, 83, 64)
(234, 176, 189, None)

[“最左上单元格坐标:最右下单元格坐标”]

使用格式：Worksheet()["最左上单元格坐标:最右下单元格坐标"]，比如ws[“B2:D5”]表示获取数据区间是：在B列和D列之间、第2到第5行之间的数据，即B2，C2、D2、B3，C3、D3、B4，C4、D4、B5，C5、D5，并以行为单位组成元组返回。
示例：
成绩单.xlsx内容如下：

代码如下：

from openpyxl import load_workbook

wb = load_workbook("D:\\test_code\\练习\\成绩单.xlsx")
ws = wb.active

# 获取前4位的主科成绩（单纯成绩，不要列名、姓名、理综）
cols = ws["B2:D5"]
print(cols)
print(type(cols))
for col in cols:
    print(col)
    for cell in col:
        print(cell.value, end="  ")
    print()

执行后，控制台输出如下：

((<Cell '加班明细'.B2>, <Cell '加班明细'.C2>, <Cell '加班明细'.D2>), (<Cell '加班明细'.B3>, <Cell '加班明细'.C3>, <Cell '加班明细'.D3>), (<Cell '加班明细'.B4>, <Cell '加班明细'.C4>, <Cell '加班明细'.D4>), (<Cell '加班明细'.B5>, <Cell '加班明细'.C5>, <Cell '加班明细'.D5>))
<class 'tuple'>
(<Cell '加班明细'.B2>, <Cell '加班明细'.C2>, <Cell '加班明细'.D2>)
78  95  77  
(<Cell '加班明细'.B3>, <Cell '加班明细'.C3>, <Cell '加班明细'.D3>)
56  79  84  
(<Cell '加班明细'.B4>, <Cell '加班明细'.C4>, <Cell '加班明细'.D4>)
83  60  83  
(<Cell '加班明细'.B5>, <Cell '加班明细'.C5>, <Cell '加班明细'.D5>)
94  59  64  

追加单行数据

append(iterable)是Worksheet对象提供的方法，支持在工作表末尾追加单行数据（一行一行的追加），iterable可以是列表、元组等数据。
示例：
成绩单.xlsx内容如下：

代码为：

from openpyxl import load_workbook

wb = load_workbook("D:\\test_code\\练习\\成绩单.xlsx")
ws = wb.active

ws.append(["十全", 88, 75, None, '197'])
ws.append(("十一", 68, 77, 90, 182, "多一个看看"))

wb.save("D:\\test_code\\练习\\成绩单.xlsx")

代码执行成功后，成绩单.xlsx内容追加了2行数据，如下：

删除行/列

Worksheet支持删除一整行/列

• delete_rows(idx, amount=1)：从idx行开始，删除amount行
• delete_cols(self, idx, amount=1)：从idx列开始，删除amount列

示例：
成绩单.xlsx内容如下：

代码如下：

from openpyxl import load_workbook

wb_row = load_workbook("D:\\test_code\\练习\\成绩单.xlsx")
ws = wb_row.active
# 删除5-7行
ws.delete_rows(5, 3)
wb_row.save("D:\\test_code\\练习\\成绩单_删除行.xlsx")


wb_col = load_workbook("D:\\test_code\\练习\\成绩单.xlsx")
# 删除语文、数学、英语3列
ws_col = wb_col.active
ws_col.delete_cols(2, 3)
wb_col.save("D:\\test_code\\练习\\成绩单_删除列.xlsx")

D:\test_code\练习 目录下新增了2个文件：成绩单_删除行.xlsx、成绩单_删除列.xlsx，相关内容如下：