python按照日期筛选excel_【Python】处理Excel中数据3 (按照班级筛选数据_把数据保存在一个文件的各个sheet中)...

1 importpandas as pd2 importos3 importtime4 importopenpyxl5 #1.加载Excel源数据

6 path = R"C:\Users\Administrator\python处理Excel数据\20200113\按照班级分离文件.xlsx"

7 workbook =openpyxl.load_workbook(path)8 sheet_names =workbook.sheetnames9 sheet1 =workbook[sheet_names[0]]10

11 #2. 读取Excel sheet1中的所有数据

12 allDatas =[]13 for row insheet1.rows:14 lines = [cell.value for cell inrow]15 allDatas.append(lines)16

17 #划分班级

18 title =[]19 class1 =[]20 class2 =[]21 class3 =[]22 class4 =[]23 class5 =[]24

25 for i inallDatas:26 if i[0] == "1":27 class1.append(i)28 elif i[0] == "2":29 class2.append(i)30 elif i[0] == "3":31 class3.append(i)32 elif i[0] == "4":33 class4.append(i)34 elif i[0] == "5":35 class5.append(i)36 else:37 title.append(i)38

39 #文件保存路径

40 path = R"C:\\Users\\Administrator\\python处理Excel数据\\20200225"

41

42 if notos.path.exists(path):43 os.mkdir(path)44 print("目录创建成功")45 else:46 print("目录已经存在")47 #工作表名字

48 date_rand = time.strftime("%Y-%m-%d", time.localtime())49 file_name = path + '\%s.xlsx' %date_rand50 writer =pd.ExcelWriter(file_name)51

52 #生成新文件的各个sheet.并添加title

53 sheet1 = pd.DataFrame(class1,columns=["班级","姓名","语文","数学","英语","总分","性别","学号"])54 sheet2 = pd.DataFrame(class2,columns=["班级","姓名","语文","数学","英语","总分","性别","学号"])55 sheet3 = pd.DataFrame(class3,columns=["班级","姓名","语文","数学","英语","总分","性别","学号"])56 sheet4 = pd.DataFrame(class4,columns=["班级","姓名","语文","数学","英语","总分","性别","学号"])57 sheet5 = pd.DataFrame(class5,columns=["班级","姓名","语文","数学","英语","总分","性别","学号"])58

59 #写入数据到各个sheet中并指定列行标

60 sheet1.to_excel(writer,"sheet1",startcol=0,index=False)61 sheet2.to_excel(writer,"sheet2",startcol=0,index=False)62 sheet3.to_excel(writer,"sheet3",startcol=0,index=False)63 sheet4.to_excel(writer,"sheet4",startcol=0,index=False)64 sheet5.to_excel(writer,"sheet5",startcol=0,index=False)65

66 #将缓存写入工作表

67 writer.save()68 print("数据写入成功！")