Excel 文件处理-Python 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,相关内容如下:
推荐阅读
-
用 Python 将 HTML 转换为 Excel 文件 - 用 Python 将 HTML 转换为 Excel 工作表
-
使用 openpyxl 轻松操作 Excel 文件
-
文件操作学习指南:探索C语言中的文件处理
-
Python处理A2L文件的方法
-
Python处理A2L文件的方法
-
Python和NCL在处理netCDF文件中的scale_factor和add_offset: 一次全面的气象编程指南
-
用C均值聚类算法在Excel中轻松处理和分类数据(详解+Python代码示例)
-
用Python实际操作CSV文件的案例与代码示例
-
Python自动化实战指南(让繁琐操作自动搞定)第二辑:第九章详解文件的读取与写入
-
在Python中,如何根据多个条件对目录下的文件名进行排序操作