软件需求

和银行等金融机构合作过程中，经常会有还款计划、放款文件等定时推送的文件，以常见的分隔符进行分隔开，为了在系统出现问题时，快速查找异常数据，写了一个小工具解析这些文件并写入到excel中。

image-20240403171558023

软件功能

将常见分隔符的文件解析后写入到excel中

软件界面

image-20240403170655979

软件源码

• 使用前请安装openpyxl,pip install openpyxl，可使用pyinstaller打包成exe文件。

import os
import datetime
import logging

from openpyxl import Workbook

import tkinter as tk
from tkinter import filedialog,messagebox
from tkinter.ttk import *



class FileParser(object):

    def __init__(self):
        pass

    @staticmethod
    def getAllFiles(folder,data_str,suffix=".txt"):
        '''获取文件路径下的所有文件'''
        filepath_list = []
        for root,folder_names, file_names in os.walk(folder):
            for file_name in file_names:
                file_path = root + os.sep + file_name
                if data_str != '' or data_str is not None:
                    if data_str in file_name and os.path.splitext(file_path)[-1]==suffix:
                        filepath_list.append(file_path)
                else:
                    if os.path.splitext(file_path)[-1]==suffix:
                        filepath_list.append(file_path)
        if len(filepath_list)==0:
            messagebox.showinfo('文件不存在',"该目录下不存在包含{}且后缀为{}的文件，请检查！".format(data_str,suffix))
            return
        file_path = sorted(file_path, key=str.lower)
        return filepath_list

    
    def batchParseDataFile(self,data_str,logbox,start_date,end_date,date_format,source_dir,target_dir,source_suffix=".txt",data_sep="|",data_start_row=1,data_end_flag="",start_row=1,start_col=1):
        '''
        获取数据文件中的有效数据
        :param start_date:解析的文件的开始日期
        :param end_date:解析的结束日期
        :param date_format:日期格式
        :param source_dir:需要解析的文件的文件夹路径
        :param target_dir:生成excel文件的文件夹路径
        :param data_sep:数据分隔符
        :param data_start_row:有效数据的开始行
        :param data_end_flag:有效数据结束标识
        :param start_row:写入excel的开始行
        :param start_col:写入excel的开始列
        :param table_head:有效数据是否包含表头
        '''
        self.log = logbox

        start=datetime.datetime.strptime(start_date,date_format)
        end=datetime.datetime.strptime(end_date,date_format)
        source_filelist = FileParser.getAllFiles(source_dir,data_str,source_suffix)
        if source_filelist is None or len(source_filelist)==0:
            self.log.info("该目录{}下不存在文件".format(source_dir))
            return
        if start > end:
            self.log.info("开始日期{}大于结束日期{}".format(start_date,end_date))
            return
        files = []
        null_files = []
        starttime = datetime.datetime.now()
        while start<=end:

            for fname in source_filelist:
                if start.strftime(date_format) in fname:
                    if os.stat(fname).st_size==0:
                        null_files.append(fname)
                    else:
                        files.append(fname)
                        basename = os.path.basename(fname)
                        file_date = start.strftime(date_format)

                        self.log.info("{}日期的{}文件正在解析".format(file_date,basename))
                        target_basename =  basename.split(".")[0]+".xlsx" if file_date in basename else basename.split(".")[0]+"_"+file_date+".xlsx"
                        
                        target_fname = os.path.join(target_dir,target_basename)
                        self.log.info("生成{}日期的excel文件{}".format(file_date,target_basename))
                        self.parseDataFile(fname,target_fname,data_sep=data_sep,data_start_row=data_start_row,data_end_flag=data_end_flag,start_row=start_row,start_col=start_col)
            start+=datetime.timedelta(days=1)
        endtime=datetime.datetime.now()
        costtime = round((endtime - starttime).total_seconds(),2)
        if len(files)==0:
            self.log.info("{}目录不存在{}-{}时间区间内的目标文件".format(source_dir,start_date,end_date))
        else:
            messagebox.showinfo('解析完成提示',"文件解析完成，{}-{}时间段内合计{}个文件，空文件{}个，解析{}个有数据文件,，耗时{}秒".format(start_date,end_date,len(files)+len(null_files),len(null_files),len(files),costtime))
            
            


    def parseDataFile(self,source_dir,target_dir,data_sep="|",data_start_row=1,data_end_flag="",start_row=1,start_col=1):
        '''
        获取数据文件中的有效数据
        :param source_dir:数据文件绝对路径
        :param target_dir:生成excel文件的绝对路径
        :param data_sep:数据分隔符
        :param data_start_row:有效数据的开始行
        :param data_end_flag:有效数据结束标识
        :param start_row:写入excel的开始行
        :param start_col:写入excel的开始列
        :param table_head:有效数据是否包含表头
        '''
        data = self.__getFileData(source_dir,data_sep,data_start_row,data_end_flag)
        fname,basename = self.__writeToExcel(source_dir,target_dir,data,start_row,start_col)
        return fname,basename
        
            

    def __getFileData(self,filename,data_sep,data_start_row,data_end_flag):
        data = []
        try:
            if os.stat(filename).st_size==0:
                self.log.info("{}文件为空".format(os.path.basename(filename)))
            with open(filename,"r",encoding="utf-8") as f:
                line_index = 1
                for line in f:
                    if line:
                        line = line.replace("\n","")
                        if line_index >= data_start_row and line != data_end_flag:
                            data.append(line.split(data_sep))
                        if line == data_end_flag:
                            break
                    else:
                        break
                    line_index = line_index+1
        except Exception as e:
            self.log.info("{}解析错误".format(filename))
        return data

    
    def __writeToExcel(self,source_file,target_file,data,start_row,start_col):
        basename =None
        try: 
            if os.stat(source_file).st_size==0 or data is None or len(data)==0:
                self.log.info("{}文件为空，未生成对应的excel文件".format(source_file))
            else:
                with open(target_file,"w+",encoding="utf-8") as f:
                    pass
                basename = os.path.basename(target_file)
                # self.__write_col(filename,basename,col=start_col,data=data)
                self.__write2ExcelOpenexl(target_file,basename,row=start_row,col=start_col,data=data)
                self.log.info("数据写入excel完成，文件路径{}".format(target_file))
        except Exception as e:
            self.log.info("写入文件{}错误".format(e))
        return target_file,basename

    

    def __write2ExcelOpenexl(self,excelname,sheet,row,col,data=None):
        wb = Workbook()
        ws = wb.create_sheet(sheet, 0)
        for y,i in enumerate(data):
            for x,n in enumerate(i):
                ws.cell( y + col, x + row, n )
        wb.save(excelname)
        wb.close()


class LoggerBox(tk.Text):
 
    def write(self, message):
        self.insert("end", message)


class APP(object):

    def __init__(self,window):
        window.title('通用对账文件解析软件')

        #获取屏幕尺寸计算参数，使窗口显示再屏幕中央
        screen_width = window.winfo_screenwidth() 
        screen_height = window.winfo_screenheight()
        width = 750
        height = 800
        # window_size = '%dx%d+%d+%d' % (width, height, (screen_width-width)/2, (screen_height-height)/2)
        window_size = f'{width}x{height}+{round((screen_width-width)/2)}+{round((screen_height-height)/2)}'
        window.geometry(window_size)

        z = tk.Label(window,anchor = 'center',bg='white',justify = 'center', width=500, height=500)
        z.place(x = 0, y = 0)
        
        a = tk.Label(window,anchor = 'center',text="对账文件解析软件",relief="raised", font=('TimesNewRoman', 30),justify = 'center', width=32, height=1)
        a.place(x = 50, y = 30)
        

        self.source_dir = tk.StringVar()
        self.source_dir.set("数据文件路径")
        b = tk.Label(window,anchor='nw',textvariable=self.source_dir,fg='black',bg='white',font=('TimesNewRoman',12),justify='center',width=50,height=1)
        b.place(x = 50, y = 100)

        self.data_str = tk.StringVar()
        self.data_str.set("文件包含字符串")
        c = tk.Label(window,anchor='nw',textvariable=self.data_str,fg='black',bg='white',font=('TimesNewRoman',12),justify='center',width=50,height=1)
        c.place(x = 50, y = 150)


        self.suffix = tk.StringVar()
        self.suffix.set("数据文件后缀")
        d = tk.Label(window,anchor='nw',textvariable=self.suffix,fg='black',bg='white',font=('TimesNewRoman',12),justify='center',width=50,height=1)
        d.place(x = 400, y = 150)


        self.target_dir = tk.StringVar()
        self.target_dir.set("文件存放路径")
        f = tk.Label(window,anchor='nw',textvariable=self.target_dir,fg='black',bg='white',font=('TimesNewRoman',12),justify='center',width=50,height=1)
        f.place(x = 50, y = 200)

        self.data_sep = tk.StringVar()
        self.data_sep.set("数据分隔符")
        g = tk.Label(window,anchor='nw',textvariable=self.data_sep,fg='black',bg='white',font=('TimesNewRoman',12),justify='center',width=50,height=1)
        g.place(x = 50, y = 250)

        self.start_date = tk.StringVar()
        self.start_date.set("开始日期")
        h = tk.Label(window,anchor='nw',textvariable=self.start_date,fg='black',bg='white',font=('TimesNewRoman',12),justify='center',width=50,height=1)
        h.place(x = 50, y = 300)

        self.end_date = tk.StringVar()
        self.end_date.set("结束日期")
        i = tk.Label(window,anchor='nw',textvariable=self.end_date,fg='black',bg='white',font=('TimesNewRoman',12),justify='center',width=50,height=1)
        i.place(x = 400, y = 300)

        self.date_format = tk.StringVar()
        self.date_format.set("日期格式")
        j = tk.Label(window,anchor='nw',textvariable=self.date_format,fg='black',bg='white',font=('TimesNewRoman',12),justify='center',width=50,height=1)
        j.place(x = 50, y = 350)


        self.data_start_row = tk.StringVar()
        self.data_start_row.set("数据开始行")
        k = tk.Label(window,anchor='nw',textvariable=self.data_start_row,fg='black',bg='white',font=('TimesNewRoman',12),justify='center',width=50,height=1)
        k.place(x = 50, y = 400)

        self.data_end_flag = tk.StringVar()
        self.data_end_flag.set("数据结束标志")
        l = tk.Label(window,anchor='nw',textvariable=self.data_end_flag,fg='black',bg='white',font=('TimesNewRoman',12),justify='center',width=50,height=1)
        l.place(x = 50, y = 450)

        self.start_row = tk.StringVar()
        self.start_row.set("excel写入行")
        m = tk.Label(window,anchor='nw',textvariable=self.start_row,fg='black',bg='white',font=('TimesNewRoman',12),justify='center',width=50,height=1)
        m.place(x = 50, y = 500)

        self.start_col = tk.StringVar()
        self.start_col.set("excel写入列")
        n = tk.Label(window,anchor='nw',textvariable=self.start_col,fg='black',bg='white',font=('TimesNewRoman',12),justify='center',width=50,height=1)
        n.place(x = 400, y = 500)


        self.source_dir = tk.StringVar()
        entry_source_dir = tk.Entry(window, textvariable=self.source_dir).place(x=200, y=100)
        tk.Button(window,text="选择源文件夹",command=lambda:self.select_dir(self.source_dir),width=15,height=1).place(x=400, y=100)

        self.data_str = tk.StringVar()
        entry_source_dir = tk.Entry(window, textvariable=self.data_str).place(x=200, y=150)


        self.suffix = tk.StringVar()
        self.suffix.set(".txt")
        entry_source_dir = tk.Entry(window, textvariable=self.suffix).place(x=550, y=150)

        self.target_dir = tk.StringVar()
        entry_target_dir = tk.Entry(window, textvariable=self.target_dir).place(x=200, y=200)
        tk.Button(window,text="选择存放文件夹",command=lambda:self.select_dir(self.target_dir),width=15,height=1).place(x=400, y=200)


        self.data_sep = tk.StringVar()
        self.data_sep.set("|")
        entry_data_sep = tk.Entry(window, textvariable=self.data_sep).place(x=200, y=250)



        self.date_format = tk.StringVar()
        self.date_format.set("%Y%m%d")
        entry_date_format = tk.Entry(window, textvariable=self.date_format).place(x=200, y=350)


        now=datetime.datetime.now()
        self.start_date = tk.StringVar()
        self.start_date.set(now.strftime(self.date_format.get()))
        entry_start_date = tk.Entry(window, textvariable=self.start_date).place(x=200, y=300)

        self.end_date = tk.StringVar()
        self.end_date.set(now.strftime(self.date_format.get()))
        entry_end_date = tk.Entry(window, textvariable=self.end_date).place(x=550, y=300)

    

        self.data_start_row = tk.IntVar()
        self.data_start_row.set(int(1))
        entry_data_start_row = tk.Entry(window, textvariable=self.data_start_row).place(x=200, y=400)

        self.data_end_flag = tk.StringVar()
        entry_data_end_flag = tk.Entry(window, textvariable=self.data_end_flag).place(x=200, y=450)


        self.start_row = tk.IntVar()
        self.start_row.set(int(1))
        entry_start_row = tk.Entry(window, textvariable=self.start_row).place(x=200, y=500)

        self.start_col = tk.IntVar()
        self.start_col.set(int(1))
        entry_start_col = tk.Entry(window, textvariable=self.start_col).place(x=550, y=500)


        n = tk.Button(window, text='开始解析', width=90,height=1, command=self.parse_file)
        n.place(x = 50,y = 550)


        logBox = LoggerBox(window, width=90,height=10)
        logBox.place(x=50,y=600)
        self.log = logging.getLogger("log")
        self.log.setLevel(logging.INFO)
        handler = logging.StreamHandler(logBox)
        self.log.addHandler(handler)

        

    
    def select_dir(self,dir):
        dir.set(filedialog.askdirectory())

    

    def parse_file(self):
        try:
            parser = FileParser()
            parser.batchParseDataFile(self.data_str.get(),self.log,self.start_date.get(),self.end_date.get(),self.date_format.get(),self.source_dir.get(),self.target_dir.get(),self.suffix.get(),self.data_sep.get(),self.data_start_row.get(),self.data_end_flag.get(),self.start_row.get(),self.start_col.get())
        except  Exception as e:
            messagebox.showinfo('错误信息',e)



if __name__=="__main__":
 
    window = tk.Tk()
    app = APP(window)
    window.mainloop()

本文由【产品经理不是经理】 gzh 同步发布，欢迎关注