1.有个朋友因为因为要处理一个本地库存跟国家规定产品的数据上报，要将本地的库存在所有几万条数据的excel里面筛选出来再上报，想我问了能不能用程序处理，想着可以用py试试，于是就有了这次操作
2.先在网上查了有哪些操作excel的库，最终选择了xlrd跟xlwt
先安装好需要的库
pip install xlrd
pip install xlwt把需要筛选放在了桌面，因为用的是macos 先获取到了桌面的路径，再拼接好文件名称
filename_main = 'main.xlsx'
filename_exists = 'exists.xlsx'
desktop_path = os.path.join(os.path.expanduser("~"), "Desktop")
filename_main = desktop_path + "/" + filename_main
filename_exists = desktop_path + "/" + filename_exists再用xlrd读取出本地库存信息 以及 所有的产品 因为只有一个工作区直接索引就是0
mainRData = xlrd.open_workbook(filename_main)
mainSheet = mainRData.sheet_by_index(0)
existsRData = xlrd.open_workbook(filename_exists)
existsSheet = existsRData.sheet_by_index(0)在把所有产品的每一行读出来，根据其中的一个唯一字段写到字典里面，同时便利库存信息里面的唯一字段，遍历去匹配所有产品中读出来的数据，如果不存在的就把那一行数据删除，有的就保留
mainDict = {}
existsDict = {}
for x in range(1, existsSheet.nrows):
#existsDict[str(existsSheet.row(x)[0]) + str(existsSheet.row(x)[3])] = existsSheet.row(x)
existsDict[str(existsSheet.row(x)[3])] = existsSheet.row(x)
existKeys = existsDict.keys()
for x in range(1, mainSheet.nrows):
#mainDict[str(mainSheet.row(x)[0]) + str(mainSheet.row(x)[3])] = mainSheet.row(x)
#if not ((str(mainSheet.row(x)[0]) + str(mainSheet.row(x)[3])) in existKeys):
# mainDict.pop(str(mainSheet.row(x)[0]) + str(mainSheet.row(x)[3]))
mainDict[str(mainSheet.row(x)[3])] = mainSheet.row(x)
if not ((str(mainSheet.row(x)[3])) in existKeys):
mainDict.pop(str(mainSheet.row(x)[3]))筛选完留下来的字典里面的数据再新建一个excel并写入到里面，放到桌面
设置好列宽和标题
wb = xlwt.Workbook()
sh1 = wb.add_sheet("1")
sh1.col(0).width = 20*256
sh1.col(1).width = 20*256
sh1.col(2).width = 20*256
sh1.col(3).width = 20*256
sh1.col(4).width = 20*256
sh1.col(5).width = 20*256
sh1.col(6).width = 20*256
sh1.col(7).width = 20*256
sh1.write(0, 0, str(mainSheet.row(0)[0]).replace('text:','').replace('\'',''))
sh1.write(0, 1, str(mainSheet.row(0)[1]).replace('text:','').replace('\'',''))
sh1.write(0, 2, str(mainSheet.row(0)[2]).replace('text:','').replace('\'',''))
sh1.write(0, 3, str(mainSheet.row(0)[3]).replace('text:','').replace('\'',''))
sh1.write(0, 4, str(mainSheet.row(0)[4]).replace('text:','').replace('\'',''))
sh1.write(0, 5, str(mainSheet.row(0)[5]).replace('text:','').replace('\'',''))
sh1.write(0, 6, str(mainSheet.row(0)[6]).replace('text:','').replace('\'',''))
sh1.write(0, 7, str(mainSheet.row(0)[7]).replace('text:','').replace('\'',''))再便利保留下来的字典数据写入excel中
i = 1
for item in mainDict.values():
sh1.write(i, 0, str(item[0]).replace('text:','').replace('\'',''))
sh1.write(i, 1, str(item[1]).replace('text:','').replace('\'',''))
sh1.write(i, 2, str(item[2]).replace('text:','').replace('\'',''))
sh1.write(i, 3, str(item[3]).replace('text:','').replace('\'',''))
sh1.write(i, 4, str(item[4]).replace('text:','').replace('\'',''))
sh1.write(i, 5, str(item[5]).replace('text:','').replace('\'',''))
sh1.write(i, 6, str(item[6]).replace('text:','').replace('\'',''))
sh1.write(i, 7, str(item[7]).replace('text:','').replace('\'',''))
i += 1最后执行保存方法
wb.save(desktop_path + '/new.xlsx')成功筛选并生成到excel中···
