【Python】处理Excel中数据3 (按照班级筛选数据 |
您所在的位置：网站首页 › python处理后的数据保存 › 【Python】处理Excel中数据3 (按照班级筛选数据 |
【Python】处理Excel中数据3 (按照班级筛选数据
|
一，源数据的载入 需求： 1. 按照班级筛选 2. 把筛选出来的数据分别保存在一个文件的各个sheet中
二，代码编写 import pandas as pd import os import time import openpyxl # 1.加载Excel源数据 path = R"C:\Users\Administrator\python处理Excel数据\20200113\按照班级分离文件.xlsx" workbook = openpyxl.load_workbook(path) sheet_names = workbook.sheetnames sheet1 = workbook[sheet_names[0]] # 2. 读取Excel sheet1中的所有数据 allDatas = [] for row in sheet1.rows: lines = [cell.value for cell in row] allDatas.append(lines) #划分班级 title = [] class1 = [] class2 = [] class3 = [] class4 = [] class5 = [] for i in allDatas: if i[0] == "1": class1.append(i) elif i[0] == "2": class2.append(i) elif i[0] == "3": class3.append(i) elif i[0] == "4": class4.append(i) elif i[0] == "5": class5.append(i) else: title.append(i) #文件保存路径 path = R"C:\\Users\\Administrator\\python处理Excel数据\\20200225" if not os.path.exists(path): os.mkdir(path) print("目录创建成功") else: print("目录已经存在") # 工作表名字 date_rand = time.strftime("%Y-%m-%d", time.localtime()) file_name = path + ‘\%s.xlsx‘ % date_rand writer = pd.ExcelWriter(file_name) #生成新文件的各个sheet.并添加title sheet1 = pd.DataFrame(class1,columns=["班级","姓名","语文","数学","英语","总分","性别","学号"]) sheet2 = pd.DataFrame(class2,columns=["班级","姓名","语文","数学","英语","总分","性别","学号"]) sheet3 = pd.DataFrame(class3,columns=["班级","姓名","语文","数学","英语","总分","性别","学号"]) sheet4 = pd.DataFrame(class4,columns=["班级","姓名","语文","数学","英语","总分","性别","学号"]) sheet5 = pd.DataFrame(class5,columns=["班级","姓名","语文","数学","英语","总分","性别","学号"]) #写入数据到各个sheet中并指定列行标 sheet1.to_excel(writer,"sheet1",startcol=0,index=False) sheet2.to_excel(writer,"sheet2",startcol=0,index=False) sheet3.to_excel(writer,"sheet3",startcol=0,index=False) sheet4.to_excel(writer,"sheet4",startcol=0,index=False) sheet5.to_excel(writer,"sheet5",startcol=0,index=False) # 将缓存写入工作表 writer.save() print("数据写入成功！")三，显示结果
|
【本文地址】
今日新闻 |
推荐新闻 |