28 python pandas 对excel进行读写摘选 - GUI界面

1.环境条件：python 3.5.2 版本；pandas 0.25.3 ；xlrd 1.1.0
2.由两个文件组成，类文件：classreatewriteexcel.py
GUI文件：mian_classGUI.py
3.GUI效果

classreatewriteexcel.py

# __author__ = 'lzc'
# -*- coding: UTF-8 -*-

import os
import pandas as pd
import datetime
import time
from datetime import datetime

class excel_class():
    """
        excelcalss 用于读写excel操作，需要安装插件：pandas、xlrd、openpyxl
        """

    def __init__(self):

        #全局结果字符串，日志结果
        res_str=""
        self.res_str= res_str
        res_count = 0
        self.res_count = res_count

    def read_excel(self, path):
        # 读取excel文件，返回一个标头list和values二维list
        df = pd.read_excel(path)
        # print (df)
        data_head = df.columns.tolist()
        data_value = df.values

        return data_head, data_value

    def write_excel(self, path, head, data):
        # 将list写入excel
        df = pd.DataFrame(data, columns=head)
        df.to_excel(path, index=False, header=True)
        self.res_str = self.res_str+"摘选并生成："+path+"\n"
        # print('写excel完成！')

    def read_writ_excel(self, path, condition):
        #单个条件摘选

        # 读excel
        flag=0#符合跳进标识
        data_head, data_value = self.read_excel(path)
        listdate = []  # 收集数据list
        for list in data_value:
            string = "".join(str(list))#转换成字符串
            if condition in string:
                # print (string)
                listdate.append(list)
                flag=1
        if flag==1:
            self.res_str = self.res_str+"符合条件："+path+"\n"

        if listdate:  # 判断是否为空值
            # 写excel
            file, ext = self.split_url(path)
            write_path = file + "_" + condition + ".xlsx"
            self.write_excel(write_path, data_head, listdate)

    def split_url(selt, url):
        # 分离文件和拓展名
        file, ext = os.path.splitext(url)
        # print (file)
        # print (ext)
        return file, ext

    def juede_folder(self, path):
        # 判断是路径还是文件,是目录返回1，是excel文件返回0，其他文件返回2,未找到路径返回3
        if os.path.isdir(path):
            return 1
        elif os.path.isfile(path):
            file, ext = os.path.splitext(path)
            if ext in ['.xlsx', '.xls']:
                return 0
            else:
                # print ("非excel文件，请检查。")
                return 2
        else:
            return 3

    def get_file_list(self, path):
        # 返回excel文件的list

        filelist = []
        for dirpath, dirnames, filenames in os.walk(path):
            # print (dirpath)
            # print (dirnames)
            for filename in filenames:
                filedir = os.path.join(dirpath, filename)
                file, ext = os.path.splitext(filedir)
                if ext in ['.xlsx', '.xls']:
                    filelist.append(filedir)
                    # print (filedir)

        return filelist

    # 将时间转换成横杠形式,先转换成时间戳，然后再转换成所需字符串
    def time_str_format(self,str):

        if '-' in str:
            return str
        else:
            timeC = self.str_to_time(str)
            timeArray = time.localtime(timeC)
            timeformat = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)

        return timeformat

    # 时间字符串变为时间戳
    def str_to_time(self,str):
        format = "%Y/%m/%d %H:%M"
        time_array = time.strptime(str, format)
        time_stamp = int(time.mktime(time_array))
        return time_stamp

    # 判断是否在项目期间
    def jub_project_time(self,date):
        project1_start = '2017/12/01 00:00:00'
        project1_stop = '2019/5/31 23:59:00'
        project2_start = '2020/12/01 00:00:00'
        project2_stop = '2021/2/28 23:59:00'

        p1_start = self.str_to_time(project1_start)
        p1_stop = self.str_to_time(project1_stop)
        p2_start = self.str_to_time(project2_start)
        p2_stop = self.str_to_time(project2_stop)

        date_stamp = self.str_to_time(self,date)
        if date_stamp >= p1_start and date_stamp <= p1_stop:
            return True
        elif date_stamp >= p2_start and date_stamp <= p2_stop:
            return True
        else:
            return False

    # 判断文件夹是否存在，不存在建立文件夹
    def judge_folder(self,folder):
        if not os.path.exists(folder):
            os.makedirs(folder)

    # 读取txt文档参数，返回list
    def open_text_file(self,path):
        list = []
        with open(path, 'r', encoding='utf-8', ) as f:
            for line in f.readlines():
                line = line.strip('\n')  # 去掉每行带出来了 \n
                line = line.strip(' ')  # 去掉空行
                list.append(str(line))
        # print (list)
        return list

    #计算两个日期的月份差距,返回yyyy-mm日期list
    def countdatemonth(self,startdate,enddate):

        list=[] #用于收集日期字符串：yyyy-mm
        # startdate='2019-01-25'
        # enddate='2021-12-17'
        year_end = datetime.strptime(enddate,'%Y-%m-%d').year
        month_end =datetime.strptime(enddate,'%Y-%m-%d').month
        year_start = datetime.strptime(startdate, '%Y-%m-%d').year
        month_start = datetime.strptime(startdate, '%Y-%m-%d').month
        # print(year_end, month_end, year_start, month_start)
        value = (year_end-year_start)*12 + (month_end-month_start)

        for i in range(0,value+1):
            month_add=month_start + i
            month_new=month_add % 12
            year_add = month_add // 12
            year_new = year_start + year_add
            if month_new==0:
                month_new=12
                year_new=year_new-1

            if month_new >=10:
                datestr = str(year_new) + '-' + str(month_new)
            else:
                datestr=str(year_new)+'-0'+str(month_new)
            # print (datestr)
            list.append(datestr)
        return list

    #返回日志结果
    def return_res(self):

        # print (self.res_str)
        return self.res_str

mian_classGUI.py

# __author__ = 'lzc'
# -*- coding: UTF-8 -*-

import os
import datetime
import time

from tkinter import *
from tkinter import filedialog
from tkinter import scrolledtext
from classreatewriteexcel import * #导入读写excel class

class windown():

    def __init__(self):

        #窗口
        window = Tk()

        # 得到屏幕宽/高
        sw = window.winfo_screenwidth()
        sh = window.winfo_screenheight()
        ww = 600
        wh = 400
        x = (sw - ww) / 2
        y = (sh - wh) / 2
        window.geometry("%dx%d+%d+%d" % (ww, wh, x, y))
        self.window =window
        # self.window.geometry("600x400")  # 窗口大小

        #容器
        # frame1 = Frame(self.window,relief='groove',bd=1) #将容器frame显示
        frame1 = Frame(self.window)#容器不显示
        self.frame1 = frame1
        self.frame1.place(x=10,y=10,width=580,height=380) #x,y,是位置，width/height 是大小

        #路径txt
        txt = Entry(self.frame1,fg='black',bg='#f0f0f0',font=('宋体',9))
        self.txt=txt
        self.txt.place(x=20,y=30,height=30, width=420) #位置大小


        #按钮
        btn = Button(self.frame1,text="选择路径", bg="orange", fg="red",command=self.clicked)
        self.btn =btn
        self.btn.place(x=470,y=30,height=30, width=80)#位置大小

        # #文件对话框的路径
        filedirstr = ""
        self.filedirstr=filedirstr

        #条件标签
        conditionlable=Label(self.frame1,text="摘选条件：",relief='groove') #relief边框
        conditionlable.place(x=20,y=80,height=30, width=70)
        #条件文本
        conditiontxt = Entry(self.frame1, fg='black', bg='#f0f0f0', font=('宋体', 9))
        self.conditiontxt = conditiontxt
        self.conditiontxt.place(x=100, y=80, height=30, width=340)  # 位置大小
        self.conditiontxt.focus()  # 设置焦点
        #条件按钮
        opionbtn = Button(self.frame1, text="开始摘选", bg="orange", fg="green", command=self.select_condition)
        self.opionbtn = opionbtn
        self.opionbtn.place(x=470, y=80, height=30, width=80)  # 位置大小

        #结果显示框架
        reslableframe=LabelFrame(self.frame1,text="摘选结果：",relief='groove') #relief边框
        reslableframe.place(x=20,y=130,height=230, width=530)
        self.reslable = reslableframe

        #结果文本显示滚条，flat: 无边框
        restxt = scrolledtext.ScrolledText(self.reslable, fg='black', bg='#f0f0f0', font=('宋体', 9),relief="flat")
        restxt.place(x=5, y=5, height=190, width=510)

        self.restxt = restxt

    def mianWindow(self):

        #窗口设置
        self.window.title("condition_Excel") #标题

        #文本框
        self.txt.insert(0, "请选择 xlsx / xls 源文件的路径")  # 设置输入文字字体
        # self.txt.focus() #设置焦点

        self.window.mainloop()

    #文件文本框
    def clicked(self):

        self.txt.delete(0,'end') #清空
        #对话选择框
        self.filedirstr= filedialog.askdirectory(title='请选择源文件的路径', initialdir=r'G:\05Python3\28option_excel_file')
        self.txt.insert(0,self.filedirstr)

    #条件选择
    def select_condition(self):

        self.restxt.delete(1.0,"end") #清空
        #选择条件
        condition=self.conditiontxt.get().strip()

        if self.filedirstr:
            if condition:
                self.restxt.insert(END, "选择路径：" + self.filedirstr + "\n")  # END末尾添加
                self.restxt.insert(END, "选择条件：" + condition + "\n")  # END末尾添加
                self.restxt.insert(END, "………………开始摘选………………" + "\n")

                #摘选内容
                res=self.select_content_toexcel(self.filedirstr,condition)
                self.restxt.insert(END, res + "\n")

                #计算符合条数
                resline = res.split("\n")
                count = 0
                for line in resline:
                    if "符合条件" in line:
                        count = count+1
                self.restxt.insert(END, "………………结束摘选………………" + "\n")
                self.restxt.insert(END, "符合文件数：" + str(count) + "， 生成文件数：" + str(count) +"\n")
                self.restxt.see(END) #看到最后一行

            else:
                self.restxt.insert(END, "摘选条件为空，请输入摘选条件" + "\n")  # 第1行，第1列开始写入

        else:
        #对话选择框
            self.restxt.insert(END, "选择路径为空，请选择路径" + "\n")  # 第1行，第1列开始写入


    def select_content_toexcel(self,path, condition):
        '''
        :param: 文件路径、摘选条件
        :return: 读写日志
        '''

        ex = excel_class() # 实例化 读写ex类
        # 判断是路径还是文件,是目录返回1，是excel文件返回0，其他文件返回2,未找到路径返回3
        juebe = ex.juede_folder(path)
        if juebe == 1:

            #多个条件摘选同时进行，用英文分号隔开
            if ';' in condition or '；' in condition:
                condition = condition.replace('；',';') #统一替换
                list_condition = condition.split(";")
                for splitstr in list_condition:
                    # print (splitstr)
                    filelist = ex.get_file_list(path)
                    for file in filelist:
                        # print (file)
                        ex.read_writ_excel(file, splitstr)
            else:
                filelist = ex.get_file_list(path)
                for file in filelist:
                    # print (file)
                    ex.read_writ_excel(file, condition)

        elif juebe == 0:
            ex.read_writ_excel(path, condition)


        elif juebe == 2:
            res ="非excel文件，请检查。"
            return res

        elif juebe == 3:
            res ="未找到该路径：" + path
            return res

        # 提取日志
        res = ex.return_res()
        return res

if __name__ =="__main__":
    win=windown()
    win.mianWindow()