使用Python实现excel表格合并(程序打包:pyinstaller -F ***.py):
一个excel文件的多sheet合并文件夹下多个Excel文件的合并(单个文件的多sheet也会自动合并)
源码(仅供参考,个需自行修改):
import tkinter as tkfrom tkinter import filedialogfrom datetime import datetimeimport pandas as pdimport os, time, openpyxl, loggingfrom openpyxl import load_workbooklogging.basicConfig(level=logging.DEBUG,format='%(asctime)s line:%(lineno)d %(levelname)s : %(message)s',datefmt=' %Y-%m-%d %H:%M:%S',filename='info.log',filemode='a')class Excel:def merge_sheets(self):wd = tk.Tk()wd.withdraw()print('*'*10 + '请选择需要合并的Excel' + '*'*10)excel_file = filedialog.askopenfilename()logging.info(excel_file)data_df = pd.DataFrame()data_df = self.merge(excel_file, data_df)data_df.to_excel(excel_file.split("/")[-1].split(".")[0] + "-new.xlsx", index=False)def merge_excels(self):wd = tk.Tk()wd.withdraw()print('*' * 10 + '请选择需要合并的文件夹' + '*' * 10)foldpath = filedialog.askdirectory()data_df = pd.DataFrame()for file_path in os.listdir(foldpath):data_df = self.merge(os.path.join(foldpath, file_path), data_df)data_df.to_excel("文件夹中表格合并结果.xlsx", index=False)def merge(self, excel_file, data_df):df = pd.read_excel(excel_file, sheet_name=None)sheet_names = [i for i in df.keys()]for sheet_name in sheet_names:read_df = pd.read_excel(excel_file, sheet_name=sheet_name)data_df = data_df.append(read_df)return data_dfif __name__ == '__main__':print('***************yyds的Excel程序正在运行***************')print("需要执行操作对应的代码:\n""\t1.合并一个Excel里面所有的sheet;\n""\t2.合并文件夹下所有的Excel")choose = input("请输入对应的编号:")excel = Excel()if choose == "1":excel.merge_sheets()print('*' * 10 + 'YYDS程序已经执行结束,请核对结果' + '*' * 10)time.sleep(5)elif choose == "2":excel.merge_excels()print('*' * 10 + 'YYDS程序已经执行结束,请核对结果' + '*' * 10)time.sleep(5)else:print("请重新启动程序并输入正确的操作代码")time.sleep(5)