Python入门：对Excel数据处理的学习笔记【第十一章】openpyxl库的使用

注：该学习笔记是根据曾志贤老师编写的《从Excel到Python，用Python轻松处理Excel数据》所学习整理的笔记。

第11章 openpyxl库的使用

openpyxl可以直接操作.xlsx工作簿，不能操作.xls工作簿。但是可以保存为.xls工作簿。

一、安装openpyxl库

同安装xlrd、xlwt、xlutils库相同的方式。

• wb无代码提示的操作：
  • 设置->编辑器->文件类型->忽略的文件和文件夹->点击+号，输入 .pyi 即可。
• ws无代码提示的操作：通过类型注释的方法：
  • 在ws = wb[ ] 后面写上 # type:openpyxl.workbook.workbook.Worksheet

二、工作簿的基本操作

1、工作簿的新建

工作簿的新建：openpyxl.Workbook( )
工作簿的保存：nwb.save(路径)

import openpyxl  
nwb = openpyxl.Workbook()  
nwb.save('11-2-1.xlsx')

2、工作簿的读取

读取工作簿可以使用load_workbook函数来完成

• 语法结构：
  • load_workbook(filename，read_only=False，keep_vba=KEEP_VBA，data_only=False，keep_links=True)
• 参数说明：
  • filename：必选参数，表示打开的Excel文件名。
  • read_only：可选参数，为False表示可读写，为True表示只读，默认值为False。
  • keep_vba：可选参数，是否保留工作簿中的VBA代码，默认保留。
  • data_only：可选参数，为False表示读取单元格真实信息，为True表示读取单元格的值，默认为False。
  • keep_links：可选参数，是否保留指向外部工作簿的链接，默认为True。

wb = openpyxl.load_workbook('11-2-1.xlsx')

案例一：批量新建工作簿

for num in range(1, 13):  
	# 保存在指定子文件夹中
    wb_name = '11-2\\{:02}.xlsx'.format(num)  
    # 保存在当前文件夹中
    wb_name = '{:02}.xlsx'.format(num)  
    nwb = openpyxl.Workbook()  
    nwb.save(wb_name)

三、工作表的基本操作

1、工作表的新建

可在新建的工作簿中新建工作表，也可在现有工作簿中新建工作表。

• 语法结构：
  • create_sheet(title=None，index=None)
• 参数说明：
  • title：可选参数，可写入工作表的名称，如果没有名称，则工作表名默认为sheet…1…2…
  • index：可选参数，在指定工作表位置前面新建工作表，如果不指定，则默认在最后面新建。

import openpyxl  
# 打开指定工作簿  
wb = openpyxl.load_workbook('11-3-1.xlsx')  
# 创建默认名的工作表  
wb.create_sheet()  
# 创建指定名称的工作表  
wb.create_sheet('7月')  
# 在指定位置创建指定名称的工作表  
wb.create_sheet('3月', 2)  
wb.save('11-3-1-1.xlsx')  
  
# 创建新工作簿  
nwb = openpyxl.Workbook()  
# 在新工作簿中创建工作表  
nwb.create_sheet()

2、工作表的读取

workbooksheets：读取所有工作表对象
sheetnames：读取所有工作表名

# 打开指定工作簿  
wb = openpyxl.load_workbook('11-3-2.xlsx')  
# 读取所有工作表对象  
print(wb.worksheets)  
# 使用列表推导式读取所有工作表对象  
print([ws for ws in wb.worksheets])  

# 读取所有工作表名  
print(wb.sheetnames)  
# 读取活动工作表  
print(wb.active)  
# 读取指定工作表，使用索引的方式  
print(wb.worksheets[1])  
# 读取指定工作表，使用表名的方式  
print(wb['1月'])

3、工作表的复制

要复制工作波中的某个工作表，可以使用copy_worksheet函数，并且可以对复制后的工作表执行重命名操作。

import openpyxl as opy  
wb = opy.load_workbook('11-4.xlsx')  
# 复制工作表，表名为原工作表名+空格+copy  
wb.copy_worksheet(wb['1月'])  
# 复制工作表，并且重命名  
nws = wb.copy_worksheet(wb['2月'],).title = '5月'  
wb.save('11-4-1.xlsx')

4、工作表的移动

在工作簿中移动工作表位置，使用move_sheet函数。

import openpyxl as opy  
wb = opy.load_workbook('11-4.xlsx') 
# 将1月工作表向右移动2位  
wb.move_sheet(wb['1月'], 2)  
# 将3月工作表向左移动1位  
wb.move_sheet(wb['3月'], -1)  
wb.save('11-4-1.xlsx')

5、工作表的删除

要删除工作表，可以使用remove函数。

import openpyxl as opy  
wb = opy.load_workbook('11-4.xlsx')
# 删除指定工作表  
wb.remove(wb['1月'])
wb.save('11-4-1.xlsx')

6、工作表的名称

import openpyxl  
wb = openpyxl.Workbook()  
ws = wb.active
# 获取当前工作表的名称
ws.title
# 修改当前活动工作表的表名
ws.title = '乘法口诀表'

案例一：批量新建工作表

import openpyxl  
nwb = openpyxl.Workbook()  
for name in ['张三', '李四', '王二', '麻子']:  
    nwb.create_sheet(name)  
# 删除默认新建的工作表  
nwb.remove(nwb['Sheet'])  
nwb.save('11-5-1.xlsx')

案例二：批量复制工作表并修改工作表名称

import openpyxl  
wb = openpyxl.load_workbook('11-5-2.xlsx')  
for year in range(2020, 2024):  
    ws_name = '{}年'.format(year)  
    wb.copy_worksheet(wb['业绩表']).title = ws_name  
wb.remove(wb['业绩表'])  
wb.save('11-5-2-1.xlsx')

案例三：拆分多个工作表到多个工作簿

import openpyxl  
wb = openpyxl.load_workbook('11-5-3.xlsx')  
# 循环所有工作表名  
for ws_name in wb.sheetnames:  
    # 每次循环如果不重新读取工作簿，将会报错，因为每次执行完会不存在当前工作簿。  
    wb = openpyxl.load_workbook('11-5-3.xlsx')  
    # 循环所有工作表对象  
    for ws in wb.worksheets:  
        # 如果工作表对象的名称不等于工作表命名时  
        if ws.title != ws_name:  
            # 执行删除  
            wb.remove(ws)  
    # 本次循环完毕，将当前工作簿保存为另外的工作簿名称  
    wb.save('11-5-3\\' + ws_name + '.xlsx')

四、单元格的基本操作

1、单元格的读取

在openpyxl库中，单元格的获取方法一般有A1和R1C1两种，如下：

获取方法	表示方法	注释
A1表示法	工作表[‘A1’]	列号用字母表示，行号用数字表示
R1C1表示法	工作表.cell(行号,列号)	行号、列号均用数字表示

import openpyxl  
wb = openpyxl.load_workbook('11-6.xlsx')  
ws = wb['成绩表'] # type:openpyxl.workbook.workbook.Worksheet  
# A1表示法  
print(ws['a1'], ws['a1'].value)  
# R1C1表示法  
print(ws.cell(1, 1), ws.cell(1, 1).value)

2、单元格区域的读取

获取方法	表示方法	注释
单行或多行	工作表[‘起始行号:结束行号’]	获取指定行区域所有已使用的单元格对象
单列或多列	工作表[‘起始列号:结束列号’]	获取指定列区域所有已使用的单元格对象
多行多列	工作表[‘起始单元格:结束单元格’]	获取指定区域所有的单元格对象

# 单行表示法  
print(ws['1'])  
print(ws[1])  
# 多行表示法  
print(ws['1:2'])  
print(ws['1':'2'])  
print(ws[1:2])

# 单列表示法  
print(ws['a:a'])  
print(ws['a':'a'])  
# 多列表示法  
print(ws['a:b'])
print(ws['a':'b'])

# 多行多列  
print(ws['a1:b2'])  
print(ws['a1':'b2'])

3、行信息的获取

获取方法	表示方法	注释
获取工作表中已使用的最小行号	工作表.min_row	返回一个整数
获取工作表中已使用的最大行号	工作表.max_row	返回一个整数
获取指定单元格的行号	工作表[‘单元格地址’].row	返回一个整数
按行获取所有已使用的单元格值	工作表.values	返回的只是值，而非对象
按行获取所有已使用的单元格对象1	工作表.rows	不能控制读取范围
按行获取所有已使用的单元格对象2	工作表.iter_rows()	可以控制读取范围

• iter_rows的语法结构：
  • iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)
• 参数说明：
  • min_row：可选参数，指定最小行号，如果未指定，则从第1行开始。
  • max_row：可选参数，指定最大行号，如果未指定，则自动识别最大行号。
  • min_col：可选参数，指定最小列号，如果未指定，则从第1列开始。
  • max_col：可选参数，指定最大列号，如果未指定，则自动识别最大列号。
  • values_only：可选参数，值为True返回单元格的值，值为False返回单元格对象，默认为False。

注意：values属性、rows属性、iter_rows方法返回的都是生成器，也是可迭代对象。如果要获取具体单元格，就要做循环处理。

import openpyxl  
wb = openpyxl.load_workbook('11-7.xlsx')  
ws = wb.active # type:openpyxl.workbook.workbook.Worksheet  
# 最小行号  
print(ws.min_row)  
# 最大行号  
print(ws.max_row)  
# 指定单元格行号  
print(ws['b4'].row)  
# 按行获取所有单元格的值，由于返回的是生成器，需要进行转换。  
print(list(ws.values))  
# 按行获取所有单元格的对象，范围为所有活动单元格范围，不能手动设置范围。  
print(list(ws.rows))  
# 按行获取所有单元格的对象，默认获取所有活动单元格范围，可以手动设置范围。  
print(list(ws.iter_rows()))  
# 按行获取所有单元格的对象，默认获取所有活动单元格范围，可以手动设置范围。
# 通过values_only=True，显示值。如果为False，则显示对象。 
print(list(ws.iter_rows(min_row=3, max_row=7, min_col=2, max_col=5, values_only=True)))

4、列信息的获取

获取方法	表示方法	注释
获取工作表中已使用的最小列号	工作表.min_column	返回一个整数
获取工作表中已使用的最大列号	工作表.max_column	返回一个整数
获取指定单元格的列号	工作表[‘单元格地址’].column	返回一个整数
按列获取所有已使用的单元格对象1	工作表.columns	不能控制读取范围
按列获取所有已使用的单元格对象2	工作表.iter_cols()	可以控制读取范围

• iter_cols的语法结构：
  • iter_cols(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)
• 参数说明：
  • min_col：可选参数，指定最小列号，如果未指定，则从第1列开始。
  • max_col：可选参数，指定最大列号，如果未指定，则自动识别最大列号。
  • min_row：可选参数，指定最小行号，如果未指定，则从第1行开始。
  • max_row：可选参数，指定最大行号，如果未指定，则自动识别最大行号。
  • values_only：可选参数，值为True返回单元格的值，值为False返回单元格对象，默认为False。

注意：columns属性、iter_cols方法返回的都是生成器，也是可迭代对象。如果要获取具体单元格，就要做循环处理。

import openpyxl  
wb = openpyxl.load_workbook('11-7.xlsx')  
ws = wb.active # type:openpyxl.workbook.workbook.Worksheet
# 最小列号  
print(ws.min_column)  
# 最大列号  
print(ws.max_column)  
# 指定单元格列号  
print(ws['b4'].column)  
# 按列获取所有单元格的对象，范围为所有活动单元格范围，不能手动设置范围。  
print(list(ws.columns))  
# 按列获取所有单元格的对象，默认获取所有活动单元格范围，可以手动设置范围。  
print(list(ws.iter_cols()))  
# 按列获取所有单元格的对象，默认获取所有活动单元格范围，可以手动设置范围。  
# 通过values_only=True，显示值。如果为False，则显示对象。  
print(list(ws.iter_cols(min_row=3, max_row=7, min_col=2, max_col=5, values_only=True)))

5、单元格的写入

单个单元格的写入方法：

写入方法	表示方法	注释
A1表示法	工作表[‘A1’]=值	值是向单元格中写入的具体值
R1C1表示法	工作表.cell(行号,列号,值)	值是向单元格中写入的具体值

import openpyxl  
wb = openpyxl.load_workbook('11-8.xlsx')  
ws = wb.active # type:openpyxl.workbook.workbook.Worksheet  
# A1表示法  
ws['a4'] = '张三'  
# R1C1表示法  
ws.cell(4, 2, '100')  
wb.save('11-8-1.xlsx')

整行数据写入方法：

写入方法	表示方法	注释
写入列表	工作表.append(list)	写入值是列表
写入元组	工作表.append(tuple)	写入值是元组
写入range	工作表.append(range)	写入值是range生成器
写入字典1	工作表.append({字母列号1：值1，……})	写入值是字典的key为字母列号
写入字典2	工作表.append({数字列号1：值1，……})	写入值是字典的key为数字列号

import openpyxl  
wb = openpyxl.load_workbook('11-8.xlsx')  
ws = wb.active # type:openpyxl.workbook.workbook.Worksheet
lst = ['张三', 88, 99]  
tup = ('李四', 88, 99)  
rng = range(1, 4)  
dic1 = {'A': '王五', 'B': 69, 'C': 96}  
dic2 = {1: '赵六', 2: 100, 3: 0}  
# 写入列表  
ws.append(lst)  
# 写入元组  
ws.append(tup)  
# 写入range  
ws.append(rng)  
# 写入字典（按照字母为key）  
ws.append(dic1)  
# 写入字典（按照数字为key）  
ws.append(dic2)
wb.save('11-8-1.xlsx')

案例一：制作九九乘法表

import openpyxl  
wb = openpyxl.Workbook()  
ws = wb.active # type: openpyxl.workbook.workbook.Worksheet  
ws.title = '乘法口诀表'  
for x in range(1, 10):  
    for y in range(1, x + 1):  
        ws.cell(x, y, '{}×{}={}'.format(y, x, x * y))  
wb.save('11-9.xlsx')

案例二：求每个人各科分数之和

import openpyxl  
wb = openpyxl.load_workbook('11-10.xlsx')  
ws = wb.active # type:openpyxl.workbook.workbook.Worksheet  
row_num = 1  
# 循环所有行数据  
for row_vals in list(ws.values)[1:]:  
    row_num += 1  
    lst = []  
    # 循环行的所有单元格数据  
    for val in row_vals[:-1]:  
        # 判断单元格的值是否为int类型  
        if type(val) == int:  
            lst.append(val)  
    ws.cell(row_num, ws.max_column, sum(lst))  
wb.save('11-10-1.xlsx')

案例三：多工作表数据合并

import openpyxl  
wb = openpyxl.load_workbook('11-11.xlsx')  
nws = wb.create_sheet('统计结果')  
nws.append(['月份', '姓名', '手机', '笔记本', '电脑'])  
# 循环所有工作表  
for ws in list(wb.worksheets)[:-1]: # type:openpyxl.workbook.workbook.Worksheet  
    # 循环所有行数据  
    for row_vals in list(ws.values)[1:]:  
        # 将当前的工作表名与所有行数据组合放入元组，并整行写入新表  
        nws.append((ws.title,) + row_vals)  
wb.save('11-11-1.xlsx')

案例四：多工作簿数据合并

# 引用os库  
import os  
import openpyxl  
nwb = openpyxl.Workbook()  
nwb.active.title = '汇总表'  
nwb.active.append(['学区', '学校名', '班级', '姓名', '分数'])  
# 循环指定文件夹内所有文件  
for wb_name in os.listdir('11-12'): # type:openpyxl.workbook.workbook.Workbook  
    # 打开工作簿  
    wb = openpyxl.load_workbook('11-12\\' + wb_name)  
    # 循环所有工作表  
    for ws in list(wb.worksheets): # type:openpyxl.workbook.workbook.Worksheet  
        # 循环所有行数据  
        for row_vals in list(ws.values)[1:]:  
            val = (wb_name.split('.')[0], ws.title) + row_vals  
            nwb.active.append(val)  
nwb.save('11-12.xlsx')

五、工作表的其他操作

1、插入与删除行和列

方法	表示方法	注释
插入行操作	insert_rows(位置，行数)	在指定位置的上面插入指定行数
插入列操作	insert_cols(位置，列数)	在指定位置的前面插入指定列数
删除行操作	delete_rows(位置，行数)	从指定位置的上面删除指定行数
删除列操作	delete_cols(位置，列数)	从指定位置的前面删除指定列数

import openpyxl  
wb = openpyxl.load_workbook('11-13-1.xlsx')  
ws = wb.active # type:openpyxl.workbook.workbook.Worksheet  
# 在第2行的上方插入3行  
ws.insert_rows(2, 3)  
# 在第3列的前面插入1列  
ws.insert_cols(3, 1)  
# 删除第14行开始往下的1行  
ws.delete_rows(14, 1)  
# 删除第7行开始往右的1列  
ws.delete_cols(7, 1)  
wb.save('11-13-1-1.xlsx')

2、移动单元格

移动单元格是将选定的单元格区域根据偏移的行数和列数移动到目标位置。

• move_range函数语法：
  • move_range(cell_range, rows=0, cols=0, translate=False)
• 参数说明：
  • cell_range：必选参数，确定要移动的单元格区域，用字符串形式表示，如：‘A1:C5’。
  • rows：可选参数，指定要偏移的行数，向下为正数，向上为负数。默认值为0，表示不偏移。
  • cols：可选参数，指定要偏移的列数，向右为正数，向左为负数。默认值为0，表示不便宜。
  • translate：可选参数，引用方式，False为绝对引用，True为相对引用。默认值为False。

注意：如果移动到目标位置存在数据，则数库将会被覆盖。
绝对引用：如果工作表中包含公式，False绝对引用将会连同公式应用位置一同改变。
相对引用：如果工作表中包含公式，True相对引用将不会改变公式应用的位置。

import openpyxl
wb = openpyxl.load_workbook('11-13-2.xlsx')  
ws = wb.active # type:openpyxl.workbook.workbook.Worksheet  
# 向上移动5行，向右移动3列，绝对引用  
ws.move_range('g8:z10', -5, 3, False)  
# 想下移动4行，向左移动5列，相对引用  
ws.move_range('g15:z16', 4, -5, True)  
wb.save('11-13-2-1.xlsx')

案例一：按条件筛选数据

对F列的总分进行筛选，筛选出大于或等于270分的记录，其余删除。

import openpyxl  
wb = openpyxl.load_workbook('11-14-1.xlsx', data_only=True)  
ws = wb.active # type:openpyxl.workbook.workbook.Worksheet  
for cell in ws['f'][1:]:  
    if cell.value < 270:  
        ws.delete_rows(cell.row)  
wb.save('11-14-1-1.xlsx')

案例二：批量制作工资条

import openpyxl  
wb = openpyxl.load_workbook('11-14-2.xlsx', data_only=True)  
ws = wb.active # type:openpyxl.workbook.workbook.Worksheet  
for cell in ws['b'][2:]:  
    ws.insert_rows(cell.row)  
    for col_num in range(1, 7):  
        tit = ws.cell(1, col_num).value  
        ws.cell(cell.row - 1, col_num, tit)  
wb.save('11-14-2-1.xlsx')