[python]python筛选excel表格信息并保存到另一个excel

关键词

==python、excel、DBC、openpyxl ==

平台说明

项目	Value
python版本	3.6

背景

从一个excel表中遍历删选信息并保存到另一个excel表

所需库

1.openpyxl ：是一个用于读写 Excel 文件的 Python 库。

1.安装相关库

pip install openpyxl 

2.代码实现

source

target1

import sys
import codecs
sys.stdout = codecs.getwriter("utf-8")(sys.stdout.detach())import os
from openpyxl import Workbook
from openpyxl import load_workbook
if os.path.exists('source.xlsx'):wb_source = load_workbook('source.xlsx')
else:wb_source = Workbook()
sheet_source = wb_source.activeif os.path.exists('target1.xlsx'):wb_target = load_workbook('target1.xlsx')
else:wb_target = Workbook()
sheet_target = wb_target.activeglobal target_row
global class_target
global headteacher
global col
target_row = 2
for row in range(4,112):for col in range(1,19):data_source = sheet_source.cell(row=row,column=col).valueif data_source == "班级" and col == 1:breakelse:if col == 1:#sheet_target.cell(row=target_row, column=5,value = data_source)#在第5列写入班级class_target = data_source #保存当前班级elif col == 2:#sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名#sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任headteacher = data_source #保存当前班主任姓名elif col == 4:sheet_target.cell(row=target_row, column=10,value = sheet_source["D3"].value)#在第10列写入课程名称sheet_target.cell(row=target_row, column=5,value = class_target)#在第5列写入班级sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名if data_source == headteacher :sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任else:sheet_target.cell(row=target_row, column=6,value = '否')#在第6列写入是否为班主任  target_row += 1 #换行elif col == 5 and data_source!= None:sheet_target.cell(row=target_row, column=10,value = sheet_source["E3"].value)sheet_target.cell(row=target_row, column=5,value = class_target)#在第5列写入班级sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名if data_source == headteacher :sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任else:sheet_target.cell(row=target_row, column=6,value = '否')#在第6列写入是否为班主任         target_row += 1elif col == 6:sheet_target.cell(row=target_row, column=10,value = sheet_source["F3"].value)sheet_target.cell(row=target_row, column=5,value = class_target)#在第5列写入班级sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名if data_source == headteacher :sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任else:sheet_target.cell(row=target_row, column=6,value = '否')#在第6列写入是否为班主任             target_row += 1elif col == 7:sheet_target.cell(row=target_row, column=10,value = sheet_source["G3"].value)sheet_target.cell(row=target_row, column=5,value = class_target)#在第5列写入班级sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名if data_source == headteacher :sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任else:sheet_target.cell(row=target_row, column=6,value = '否')#在第6列写入是否为班主任          target_row += 1elif col == 8:sheet_target.cell(row=target_row, column=10,value = sheet_source["H3"].value)sheet_target.cell(row=target_row, column=5,value = class_target)#在第5列写入班级sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名if data_source == headteacher :sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任else:sheet_target.cell(row=target_row, column=6,value = '否')#在第6列写入是否为班主任            target_row += 1elif col == 9:sheet_target.cell(row=target_row, column=10,value = sheet_source["I3"].value)sheet_target.cell(row=target_row, column=5,value = class_target)#在第5列写入班级sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名if data_source == headteacher :sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任else:sheet_target.cell(row=target_row, column=6,value = '否')#在第6列写入是否为班主任            target_row += 1elif col == 10 and data_source!= None:sheet_target.cell(row=target_row, column=10,value = sheet_source["J3"].value)sheet_target.cell(row=target_row, column=5,value = class_target)#在第5列写入班级sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名if data_source == headteacher :sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任else:sheet_target.cell(row=target_row, column=6,value = '否')#在第6列写入是否为班主任           target_row += 1elif col == 11:sheet_target.cell(row=target_row, column=10,value = sheet_source["K3"].value)sheet_target.cell(row=target_row, column=5,value = class_target)#在第5列写入班级sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名if data_source == headteacher :sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任else:sheet_target.cell(row=target_row, column=6,value = '否')#在第6列写入是否为班主任           target_row += 1elif col == 12:sheet_target.cell(row=target_row, column=10,value = sheet_source["L3"].value)sheet_target.cell(row=target_row, column=5,value = class_target)#在第5列写入班级sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名if data_source == headteacher :sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任else:sheet_target.cell(row=target_row, column=6,value = '否')#在第6列写入是否为班主任           target_row += 1elif col == 13:sheet_target.cell(row=target_row, column=10,value = sheet_source["M3"].value)sheet_target.cell(row=target_row, column=5,value = class_target)#在第5列写入班级sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名if data_source == headteacher :sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任else:sheet_target.cell(row=target_row, column=6,value = '否')#在第6列写入是否为班主任            target_row += 1elif col == 14 and data_source!= None:sheet_target.cell(row=target_row, column=10,value = sheet_source["N3"].value)sheet_target.cell(row=target_row, column=5,value = class_target)#在第5列写入班级sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名if data_source == headteacher :sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任else:sheet_target.cell(row=target_row, column=6,value = '否')#在第6列写入是否为班主任            target_row += 1elif col == 15:sheet_target.cell(row=target_row, column=10,value = sheet_source["O3"].value)sheet_target.cell(row=target_row, column=5,value = class_target)#在第5列写入班级sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名if data_source == headteacher :sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任else:sheet_target.cell(row=target_row, column=6,value = '否')#在第6列写入是否为班主任            target_row += 1elif col == 16:sheet_target.cell(row=target_row, column=10,value = sheet_source["P3"].value)sheet_target.cell(row=target_row, column=5,value = class_target)#在第5列写入班级sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名if data_source == headteacher :sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任else:sheet_target.cell(row=target_row, column=6,value = '否')#在第6列写入是否为班主任          target_row += 1elif col == 17:sheet_target.cell(row=target_row, column=10,value = sheet_source["Q3"].value)sheet_target.cell(row=target_row, column=5,value = class_target)#在第5列写入班级sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名if data_source == headteacher :sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任else:sheet_target.cell(row=target_row, column=6,value = '否')#在第6列写入是否为班主任           target_row += 1elif col == 18:sheet_target.cell(row=target_row, column=10,value = sheet_source["R3"].value)sheet_target.cell(row=target_row, column=5,value = class_target)#在第5列写入班级sheet_target.cell(row=target_row, column=1,value = data_source)#在第1列写入老师姓名if data_source == headteacher :sheet_target.cell(row=target_row, column=6,value = '是')#在第6列写入是否为班主任else:sheet_target.cell(row=target_row, column=6,value = '否')#在第6列写入是否为班主任           target_row += 1                
wb_source.save('source.xlsx') 
wb_source.close() 
wb_target.save('target1.xlsx') 
wb_target.close() print('数据提取完成')