【python辅助excel】(6)_openpyxl筛选指定条件

(五十一)根据指定列数据降序排列工作表

# 导入openpyxl模块
import openpyxl
# 根据“录取表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("录取表.xlsx", data_only=True)
mysheet = mybook['录取表']
# 按行获取录取表(mysheet)的单元格数据(myrange)
myrange=list(mysheet.values)
#根据录取表(mysheet)创建(复制)新录取表(mynewsheet)
mynewsheet = mybook.copy_worksheet(mysheet)
mynewsheet.title = '新录取表'
#删除新录取表(mynewsheet)第3行之后的行(即删除所有考生)
while mynewsheet.max_row>3:
    mynewsheet.delete_rows(4)
#从myrange第四行开始先根据总分列对行进行升序排序然后在循环，
for myrow in sorted(myrange[3:],key=lambda x:x[3],reverse=True):
    #在新录取表(mynewsheet)中添加经过降序排列后的考生
    mynewsheet.append(myrow)
# 保存工作簿，保存为"结果表-录取表.xlsx"文件
mybook.save("结果表-录取表.xlsx")

sorted(myrange[3:],key=lambda x:x[3],reverse=True)

表示对指定范围(myrange[3:])的所有行(从第四行开始)根据第四列(总分列)的数据进行降序排列，reverse=True表示降序，False表示升序，但默认升序

 （五十二）根据字符串长度倒序排列工作表

# 导入openpyxl模块
import openpyxl
# 根据“新书订购表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("新书订购表.xlsx", data_only=True)
mysheet = mybook['新书订购表']
# 按行获取新书订购表(mysheet)的单元格数据(第一行除外)
myrows=list(mysheet.values)[1:]
mylist=[]
for myrow in myrows:
    mylist+=[myrow[0]]
#根据书名的长度进行倒序排列
mylist.sort(key=len,reverse=True)
#mylist.sort(key=lambda x:len(x),reverse=True)
#在新书订购表(mysheet)中删除所有的行(第一行除外)
while mysheet.max_row>1:
    mysheet.delete_rows(2)
#在新书订购表(mysheet)中添加倒序排列的书名
for myrow in mylist:
    mysheet.append([myrow])
# 保存工作簿，保存为"结果表-新书订购表.xlsx"文件
mybook.save("结果表-新书订购表.xlsx")

mylist.sort(key=len,reverse=True)

表示根据 mylist 列表成员的字符串长度进行倒序排列

 （五十三）使用集合实现随机排列工作表

# 导入openpyxl模块
import openpyxl
# 根据“员工表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("员工表.xlsx",data_only=True)
mysheet = mybook.active
# 按行获取员工表(mysheet)的单元格数据(第一行除外)
myvalues=list(mysheet.values)[1:]
#根据myvalues创建myset集合，此时自动随机排列所有行
myset=set(myvalues)
#删除员工表(mysheet)的行(第一行除外)
while mysheet.max_row>1:
    mysheet.delete_rows(2)
#在员工表(mysheet)中添加经过随机排列的行(myset)
for myrow in myset:
    mysheet.append([myrow)
# 保存工作簿，保存为"结果表-员工表.xlsx"文件
mybook.save("结果表-员工表.xlsx")

myset=set(myvalues)

表示根据员工表的所有行创建集合，在python中，集合是无序的、且不重复元素(成员)，因此每次运行此代码时，在myset的成员的排列顺序均不相同

 （五十四）根据间隔行数正序筛选所有行

# 导入openpyxl模块
import openpyxl
# 根据“员工表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("员工表.xlsx",data_only=True)
mysheet = mybook.active
# 按行获取员工表(mysheet)的单元格数据(第一行除外)
myrows=list(mysheet.values)[1:]
#删除员工表(mysheet)的行(第一行除外)
while mysheet.max_row>1:
    mysheet.delete_rows(2)
#从myrows的第一行开始，根据间隔行数(1行)正序筛选所有行
for myrow in myrows[::2]:
    mysheet.append(myrow)
# 保存工作簿，保存为"结果表-员工表.xlsx"文件
mybook.save("结果表-员工表.xlsx")

myrows[::2]

表示从myrows的第一行开始，根据间隔行数(1行)正序筛选所有行。如果 myrows[::1] 则没有筛选效果。如果 myrows[::3] 表示从myrows的第一行开始，根据间隔行数(2行)正序筛选所有行

 （五十五）根据间隔行数倒序筛选所有行

# 导入openpyxl模块
import openpyxl
# 根据“员工表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("员工表.xlsx",data_only=True)
mysheet = mybook.active
# 按行获取员工表(mysheet)的单元格数据(第一行除外)
myrows=list(mysheet.values)[1:]
#删除员工表(mysheet)的行(第一行除外)
while mysheet.max_row>1:
    mysheet.delete_rows(2)
#从myrows的第一行开始，根据间隔行数(1行)正序筛选所有行
for myrow in myrows[::-2]:
    mysheet.append(myrow)
# 保存工作簿，保存为"结果表-员工表.xlsx"文件
mybook.save("结果表-员工表.xlsx")

myrows[::-2]

表示在myrows中从倒数第一行开始，根据间隔行数(1行)倒序筛选所有行。

如果 myrows[::-1] 仅有倒序排列结果，没有筛选效果

如果 myrows[::-3] 表示在myrows中从倒数第一行开始，根据间隔行数(2行)倒序筛选所有行

 （五十六）使用max()筛选最大值所在的行

# 导入openpyxl模块
import openpyxl
# 根据“新书表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("新书表.xlsx",data_only=True)
mysheet = mybook.active
# 按行获取员工表(mysheet)的单元格数据(第一行除外)
myrows=list(mysheet.values)[1:]
mydict={}
for myrow in myrows:
    #根据每行(myrow)的书名(myrow[0])和售价(myrow[1])创建字典
    mydict[myrow[0]]=myrow[1]
#获取在字典(mydict)中售价(mydict.values())最高的图书
mymax=max(zip(mydict.values(),mydict.keys()))
mysheet.append(['【最高售价图书】'+mymax[1],mymax[0]])
# 保存工作簿，保存为"结果表-新书表.xlsx"文件
mybook.save("结果表-新书表.xlsx")

zip(mydict.values(),mydict.keys())

mydict.values() 表示 mydict 的所有键值，mydict.keys() 表示字典的所有键名，zip() 函数用于将可迭代的对象作为参数，将对象中对应的元素打包成元组，然后返回由这些元组组成的列表

max() 函数用于获取给定参数的最大值，参数可以是序列

 (五十七)使用关键字筛选符合条件的行

# 导入openpyxl模块
import openpyxl
 
# 根据“员工表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("员工表.xlsx", data_only=True)
mysheet = mybook.active
# 由于每执行一次mysheet.delete_rows(myrow),
# mysheet.max_row会发生变化，因此采用倒循环
# mysheet.max_row表示起始行号，1表示结束行号，-1表示步长
for myrow in range(mysheet.max_row, 1, -1):
    if '江北县' not in mysheet[myrow][2].value:
        mysheet.delete_rows(myrow)
# 保存工作簿，保存为"结果表-员工表.xlsx"文件
mybook.save("结果表-员工表.xlsx")

 （五十八）使用列表设置条件对行进行筛选

# 导入openpyxl模块
import openpyxl
 
# 根据“销量排行表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("销量排行表.xlsx", data_only=True)
mysheet = mybook.active
# 按行获取销量排行表(mysheet)的单元格数据(第一行除外)
myrows=list(mysheet.values)[1:]
#在销量排行表(mysheet)中删除所有行(第一行除外)
while mysheet.max_row>1:
    mysheet.delete_rows(2)
mylist=['清华出版社','北京出版社']
for myrow in myrows:
    #根据出版社名字(myrow[3])在mylist中
    if myrow[3] in mylist:
        mysheet.append(myrow)
# 保存工作簿，保存为"结果表-销量排行表.xlsx"文件
mybook.save("结果表-销量排行表.xlsx")

 （五十九）使用或运算组合条件对行进行筛选

# 导入openpyxl模块
import openpyxl
# 根据“销量排行表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("销量排行表.xlsx", data_only=True)
mysheet = mybook.active
# 按行获取销量排行表(mysheet)的单元格数据(第一行除外)
myrows=list(mysheet.values)[1:]
#在销量排行表(mysheet)中删除所有行(第一行除外)
while mysheet.max_row>1:
    mysheet.delete_rows(2)
mylist=['清华出版社','北京出版社']
for myrow in myrows:
    if myrow[2]>100 or '清华大学出版社' in myrow[3]:
        mysheet.append(myrow)
# 保存工作簿，保存为"结果表-销量排行表.xlsx"文件
mybook.save("结果表-销量排行表.xlsx")

 （六十）使用与运算组合条件对行进行筛选

# 导入openpyxl模块
import openpyxl
# 根据“销量排行表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("销量排行表.xlsx", data_only=True)
mysheet = mybook.active
# 按行获取销量排行表(mysheet)的单元格数据(第一行除外)
myrows = list(mysheet.values)[1:]
# 在销量排行表(mysheet)中删除所有行(第一行除外)
while mysheet.max_row > 1:
    mysheet.delete_rows(2)
for myrow in myrows:
    if myrow[2] > 100 and '清华大学出版社' in myrow[3]:
        mysheet.append(myrow)
# 保存工作簿，保存为"结果表-销量排行表.xlsx"文件
mybook.save("结果表-销量排行表.xlsx")

 （六十一）使用集合对行进行随机筛选

# 导入openpyxl模块
import openpyxl
# 根据“员工表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("员工表.xlsx", data_only=True)
mysheet = mybook.active
# 根据员工表(mysheet)的单元格数据(第一行除外)创建集合(myset)
myset = set(list(mysheet.values)[1:])
# 在员工表(mysheet)中删除所有行(第一行除外)
while mysheet.max_row > 1:
    mysheet.delete_rows(2)
#在员工表(mysheet)中随机筛选6行数据
for myrow in range(6):
    mysheet.append(myset.pop())
# 保存工作簿，保存为"结果表-员工表.xlsx"文件
mybook.save("结果表-员工表.xlsx")

myset.pop()

表示从 myset 集合中随机随机移除一个成员(移除是随机的)

 （六十二）使用交集方法筛选多个工作表的行

# 导入openpyxl模块
import openpyxl
# 根据“运动员表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("运动员表.xlsx", data_only=True)
mysheet1 = mybook['篮球赛']
mysheet2 = mybook['足球赛']
mysheet3 = mybook['乒乓球赛']
# 根据篮球赛工作表(mysheet1)的行(第一行除外)创建集合(myset1)
myset1 = set(list(mysheet1.values)[1:])
# 根据足球赛工作表(mysheet2)的行(第一行除外)创建集合(myset2)
myset2 = set(list(mysheet2.values)[1:])
# 根据篮球赛工作表(mysheet3)的行(第一行除外)创建集合(myset3)
myset3 = set(list(mysheet3.values)[1:])
# 获取三个集合的交集(myset4),即筛选参加三种比赛的运动员
myset4 = myset1.intersection(myset2, myset3)
# 根据交集(myset4)创建参加三种比赛的工作表(mysheet4)
mysheet4 = mybook.copy_worksheet(mysheet1)
mysheet4.title = '三种比赛'
while mysheet4.max_row > 1:
    mysheet4.delete_rows(2)
for myrow in myset4:
    mysheet4.append(myrow)
# 保存工作簿，保存为"结果表-运动员表.xlsx"文件
mybook.save("结果表-运动员表.xlsx")

myset1.intersection(myset2, myset3)

表示myset1、myset2、myset3三个集合的交集myset4。

可以在 intersection() 方法中添加多个参数，以实现获取多个集合的交集

 （六十三）使用集合推导式对行进行筛选

# 导入openpyxl模块
import openpyxl
# 根据“城市排名表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("城市排名表.xlsx", data_only=True)
mysheet = mybook.active
# 按行获取城市排名表(mysheet)的单元格数据(第一行除外)
myrows = list(mysheet.values)[1:]
#使用集合推导式在myrows中筛选包含‘广州’的行
myset={myrow for myrow in myrows if '广州' in myrow[1]}
#在城市排名表(mysheet)中删除所有行(第一行除外)
while mysheet.max_row > 1:
    mysheet.delete_rows(2)
#将筛选结果(myset)重新写入城市排名表(mysheet)
for myrow in myset:
    mysheet.append(myrow)
# 保存工作簿，保存为"结果表-城市排名表.xlsx"文件
mybook.save("结果表-城市排名表.xlsx")

myset={myrow for myrow in myrows if '广州' in myrow[1]}

表示循环列表(myrows)的每一行(myrow),如果该行( myrow )的城市列( myrow[1] )包含'广州'，则将该行添加到新建的集合中

 （六十四）使用集合在多行多列中筛选数据

# 导入openpyxl模块
import openpyxl
# 根据“五百强企业表.xlsx”文件内容,并打开工作簿
mybook = openpyxl.load_workbook("五百强企业表.xlsx", data_only=True)
mysheet = mybook['五百强企业表']
# 按行获取五百强企业表(mysheet)的单元格数据(第一行除外)
myrows = list(mysheet.values)[1:]
#创建空集合(myset)
myset=set()
#循环五百强企业表(mysheet)的行(myrow)
for myrow in myrows:
    #循环行(myrow)的公司名称(第一列单元格除外)
    for myname in myrow[1:]:
        #在集合myset中添加所有的公司名称(自动删除重复的公司)
        myset.add(myname)
#在工作簿(mybook)中创建新工作表
mynewsheet=mybook.create_sheet('所有公司表')
#将筛选结果(myset)重新写入城市排名表(mysheet)
for myname in myset:
    #在新工作表(mynewsheet)中添加不重复的公司名称(myname)
    mynewsheet.append([myname])
# 保存工作簿，保存为"结果表-五百强企业表.xlsx"文件
mybook.save("结果表-五百强企业表.xlsx")

myset.add(myname)

表示在myset集合中添加公司名称(myname),若在五百强公司企业表中存在相同的公司名称，则在myset中只保留一个