话不多说哦,直接上代码 >>>>>>>:
*******************************************************************************************************************
import pandas as pd
import logging
from tkinter import *
import os
class SolveExcel(object):
def __init__(self, filepathold, filepathnew):
self.filepathnew = filepathnew
self.filepathold = filepathold
LOG_FORMAT = "%(asctime)s-%(levelname)s-%(message)s"
logging.basicConfig(filename='solveexcel_merged.log', level=logging.DEBUG, format=LOG_FORMAT)
def readold(self, filepathold):
# 通过绝对路径获取文件
print("------ into def readold ---------")
# readfileold = pd.read_excel(r"D:\solveExcel\excelold.xlsx")
readfileold = pd.read_excel(filepathold)
data_oldlist = []
for j in readfileold.index.values:
# 根据j 来获取每一行的指定的数据
row_data = readfileold.loc[j, ['examnumber', 'name', 'class', 'chinese', 'mathe', 'english', 'totalscore', 'gradelevel','classlevel','remarks']].to_dict()
data_oldlist.append(row_data)
# logging.info("------ into def readold ---------")
# logging.info("------ data_oldlist: ---------" + str(data_oldlist))
# print("data_oldlist:{}".format(data_oldlist))
print("*" * 50)
return data_oldlist
def readnew(self, filepathnew):
# 通过绝对路径获取文件
# readfileold = pd.read_excel(r"D:\movefile")
readfilenew = pd.read_excel(filepathnew)
data_newlist = []
for i in readfilenew.index.values:
# 根据j 来获取每一行的指定的数据
row_data = readfilenew.loc[i, ['examnumber', 'name', 'class', 'chinese', 'mathe', 'english', 'totalscore', 'gradelevel','classlevel', 'remarks']].to_dict()
data_newlist.append(row_data)
logging.info("------ into def readnew -------------")
logging.info("------ data_newlist: ---------" + str(data_newlist))
return data_newlist
def rundata(self, data_oldlist, data_newlist, savepath):
""" 处理合并数据:往 merged_excel 中插入学生的各科成绩 """
old_excel_list = data_oldlist
new_excel_list = data_newlist
new_excel_data_changed = []
for newdata in new_excel_list:
for olddata in old_excel_list:
if newdata['examnumber'] == olddata['examnumber']:
newdata = olddata
new_excel_data_changed.append(newdata)
# 将新的成绩列表存入新的Excel中, list 转 dataframe
df = pd.DataFrame(new_excel_data_changed,columns=['examnumber', 'name', 'class', 'chinese', 'mathe', 'english','totalscore', 'gradelevel', 'classlevel', 'remarks'])
# save 到本地Excel
df.to_excel(os.path.join(savepath, "merged.xlsx"), index=False)
print("saved over !!")
def main():
print("---- into main ------")
# filepathold = str(r"D:\solveExcel\Exceltools\excelold.xlsx")
# filepathnew = str(r"D:\solveExcel\Exceltools\excelnew.xlsx")
# savepath = str(r"D:\solveExcel\Exceltools")
filepathold = entry1.get()
filepathnew = entry2.get()
savepath = entry3.get()
se = SolveExcel(filepathold, filepathnew)
se_old_data = se.readold(filepathold)
se_new_data = se.readnew(filepathnew)
# 传入 readold,readnew 的 return 的数据,获取数据处理结果的Excel及程序结束的标志语句
se.rundata(se_old_data, se_new_data, savepath)
# 加载点击界面
root= Tk()
root.title('solve excel to merge new')
root.geometry('380x130')
root.resizable(width=False,height=False)
lable1 = Label(root,text='请输入old_file路径:',font=('幼圆',13))
lable1.grid(row=0,column=0,sticky=E)
entry1 = Entry(root,font=('幼圆',12))
entry1.grid(row=0,column=1,sticky=W,pady=5)
lable2 = Label(root,text='请输入new_file路径:',font=('幼圆',13))
lable2.grid(row=1,column=0,sticky=E)
entry2 = Entry(root,font=('幼圆',12))
entry2.grid(row=1,column=1,sticky=W,pady=5)
lable3 = Label(root,text='请输入save_file路径:',font=('幼圆',13))
lable3.grid(row=2,column=0,sticky=E)
entry3 = Entry(root,font=('幼圆',12))
entry3.grid(row=2,column=1,sticky=W,pady=5)
# 点击按钮
button1 = Button(root,text='开始合并',activebackground='#AFEEEE',font=('幼圆',10),command=main)
# 定位 sticky对齐方式 E S W N 东南西北
button1.grid(row=3,column=0,sticky=S,pady=10)
# button1 = Button(root,text='退出程序',activebackground='#AFEEEE',font=('幼圆',10),command=quit)
button2 = Button(root,text='退出程序',activebackground='#AFEEEE',font=('幼圆',10),command=root.destroy)
button2.grid(row=3,column=1,sticky=S,pady=10)
# 显示界面
root.mainloop()#
# 打包成 EXE
# pyinstaller -F -w solveExcel.py
*******************************************************************************************************************
【需求:
将Excel-new 上所需要的数据,在Excel-old 上查询,并将数据同步到一个新的excel-merged (前二十个学生的成绩)上,最后打包为 EXE 工具,并加入界面,导入文件路径,生成合并的日志,方便以后经常用。
功能:
数据查询及处理,合并日志的生成。
】
*************************************************************************************************************************************************