excel报表合并python代码

#coding=utf-8

from tkinter import *
from tkinter import messagebox,filedialog
import pandas as pd
import os
import warnings

class Application(Frame):
    def __init__(self,master=None):
        super().__init__(master)
        self.master = master
        self.pack()
        self.createWidget()

    def createWidget(self):
        Label(self,text="文件路径：").grid(row=0,column=0)
        self.entry01 = Entry(self)
        self.entry01.grid(row=0,column=1)
        Button(self,text="选择文件...",command=self.select_file_path).grid(row=0,column=2,sticky=E)

        Button(self,text="合并(多个sheet表)",command=self.excel_merge_by_sheet).grid(row=1,column=1,sticky=W+E+N+S)
        Button(self, text="合并(单个sheet表)", command=self.excel_merge).grid(row=2, column=1, sticky=W+E+N+S)
        Button(self, text="汇总文件夹文件", command=self.file_total).grid(row=3, column=1, sticky=W+E+N+S)

    def select_file_path(self):
        path = filedialog.askdirectory()
        self.entry01.delete(0,END)
        self.entry01.insert(0,path)

    def excel_merge_by_sheet(self):
        file_path = self.entry01.get()
        output_path = os.path.join(file_path,"汇总.xlsx")

        # 删除现有的汇总文件（如果存在），以避免追加到旧文件中
        if os.path.exists(output_path):
            os.remove(output_path)

        if not file_path:
            messagebox.showerror("温馨提示","文件路径必填，不能为空")
            return
        if not os.path.exists(file_path):
            messagebox.showerror("温馨提示","文件不存在")
            return

        # 忽略特定的UserWarning
        warnings.filterwarnings("ignore",category=UserWarning,message="Workbook contains no default style,apply openpyxl's default")

        try:
            with pd.ExcelWriter(output_path,engine="openpyxl") as writer:
                for root,dirs,files in os.walk(file_path):
                    for file in files:
                        if file.endswith(".xlsx"):# 只处理excel文件
                            file_name = os.path.join(file_path,file)
                            df = pd.read_excel(file_name,engine="openpyxl")
                            sheet_name = os.path.splitext(file)[0]
                            df.to_excel(writer,sheet_name = sheet_name,index=False,engine="openpyxl")
        except Exception as e:
            messagebox.showerror("提示",f"出错了，原因是：{str(e)}")

        messagebox.showinfo("提示","多个sheet报表已完成合并！")

    def excel_merge(self):
        file_path = self.entry01.get()
        output_path = os.path.join(file_path, "汇总.xlsx")

        # 删除现有的汇总文件（如果存在），以避免追加到旧文件中
        if os.path.exists(output_path):
            os.remove(output_path)

        if not file_path:
            messagebox.showerror("温馨提示", "文件路径必填，不能为空")
            return
        if not os.path.exists(file_path):
            messagebox.showerror("温馨提示", "文件不存在")
            return

        # 初始化一个空的DataFrame来存储所有数据
        all_data = pd.DataFrame()

        # 忽略特定的UserWarning
        warnings.filterwarnings("ignore", category=UserWarning,
                                message="Workbook contains no default style,apply openpyxl's default")

        try:
            for root,dirs,files in os.walk(file_path):
                for file in files:
                    if file.endswith(".xlsx"):
                        file_name = os.path.join(file_path,file)
                        df = pd.read_excel(file_name,engine="openpyxl")
                        df["文件名称"] = os.path.splitext(file)[0]
                        if not df.empty and not df.isna().all().all():
                            all_data = pd.concat([all_data,df],ignore_index=True)

            all_data.to_excel(output_path,index=False,engine="openpyxl")

        except Exception as e:
            messagebox.showerror("提示",f"出错了，原因是：{str(e)}")

        messagebox.showinfo("提示","单个sheet报表已完成合并！")

    def file_total(self):
        file_path = self.entry01.get()
        output_path = os.path.join(file_path, "汇总.xlsx")

        # 删除现有的汇总文件（如果存在），以避免追加到旧文件中
        if os.path.exists(output_path):
            os.remove(output_path)

        if not file_path:
            messagebox.showerror("温馨提示", "文件路径必填，不能为空")
            return
        if not os.path.exists(file_path):
            messagebox.showerror("温馨提示", "文件不存在")
            return

        # 忽略特定的UserWarning
        warnings.filterwarnings("ignore", category=UserWarning,
                                message="Workbook contains no default style,apply openpyxl's default")

        # 初始化一个空的列表用来存储所有数据
        allfiles = []

        try:
            childFiles = os.listdir(file_path)
            for file in childFiles:
                file_path_name = os.path.join(file_path,file)
                if not os.path.isdir(file_path_name):
                    allfiles.append(file+"\n")

            df = pd.DataFrame(allfiles,columns=["文件名称"])
            df.to_excel(output_path,index=False,engine="openpyxl")

        except Exception as e:
            messagebox.showerror("提示", f"出错了，原因是：{str(e)}")

        messagebox.showinfo("提示", "该文件所有文件已汇总")

if __name__ == '__main__':
    root = Tk()
    root.geometry('500x200+100+100')
    root.title("文件合并及汇总-V1.0  小新制作")
    app = Application(master=root)
    root.mainloop()