Python 合并文件夹下所有的Excel文件

快来打我* 2022-04-24 03:58 330阅读 0赞

打开windows窗口选择文件( askopenfilename( ) ) 或文件夹 ( askdirectory() )，以下代码是打开文件夹和文件的示例。

import tkinter.filedialog as tkFD
    
    def __init__(self, root):
        self.ext1 = Button(self.frame[1], text="选择文件", command=self.openfile,width = 10)
            self.ext1.pack(side = LEFT, padx = 2,pady = 10)
            self.txt = Text(self.frame[1],height = 1,width = 80)
            self.txt.pack(side = LEFT, padx = 2,pady = 10)
    
            self.ext3 = Button(self.frame[3], text="选择根目录", command=self.opendir,width = 10)
            self.ext3.pack(side = LEFT, padx = 2,pady = 10)       
            self.path = Text(self.frame[3],height = 1,width = 40)
            self.path.pack(side = LEFT, padx = 2,pady = 10)
    
    def openfile(self):
            self.filename=tkFD.askopenfilename(filetypes=[("Excel 工作簿", ".xls")])
            self.txt.delete(1.0,END)
            self.txt.insert(1.0,self.filename)
       
    def opendir(self):
            self.pathname=tkFD.askdirectory()
            self.path.delete(1.0,END)
            self.path.insert(1.0,self.pathname)

同一个文件夹存在数量众多的Excel文件，比如某次活动邮件发送的Excel报名文件、各类记录等，需要手动对记录进行一条条的复制粘贴合并，快速的合并方式是通过pandas读取文件夹下的所有的Excel文件，然后统一合并。

#!/usr/bin/env python
    # -*- coding:utf-8 -*-
    import pandas as pd
    import os
    from Tkinter import *
    from tkFileDialog import *
    
    class App:
        def __init__(self, root):
            frame = Frame(root)  # container
            frame.pack()
    
            self.button1 = Button(frame, text="Excel文件夹", command=self.filefound1, width=20, height=1).grid(row=0, column=0)
            self.button2 = Button(frame, text="运行", command=self.eachFile, width=20, height=1).grid(row=3, column=0)
            self.button3 = Button(frame, text="退出", command=frame.quit, width=20, height=1).grid(row=4, column=0)
    
            self.e = Entry(frame)
            self.e.grid(row=0, column=2)
            self.e.delete(0, END)  # 将输入框里面的内容清空
            self.e.insert(0, '显示文件路径')
    
            self.filepath = StringVar()
            self.frames = []
    
        def filefound1(self):
            self.filepath = askdirectory()
            print self.filepath
            self.e.delete(0, END)  # 将输入框里面的内容清空
            self.e.insert(0, self.filepath)
            return self.filepath
    
        # 遍历指定目录，显示目录下的所有文件名
        def eachFile(self):
            pathDir = os.listdir(self.filepath)
            for allDir in pathDir:
                if allDir[-4:] == 'xlsx' or allDir[-4:] == 'xls':
                    child = os.path.join('%s\%s' % (self.filepath, allDir))
                    print child  # .decode('gbk')是解决中文显示乱码问题
                    df = pd.DataFrame(pd.read_excel(child))
                    self.frames.append(df)
            result = pd.concat(self.frames)
            # 保存文件
            savepath = self.filepath + u'\\excel_to_python.xlsx'
            result.to_excel(savepath, sheet_name='bluewhale_cc')
    
    if __name__ == '__main__':
        root = Tk()
        root.title('Excel文件合并')
        app = App(root)
        root.mainloop()