from openpyxl import load_workbook from tkinter import filedialog import tkinter as tk from utils.logging_tool.log_control import INFO def open_file(): root = tk.Tk() root.withdraw() f_path = filedialog.askopenfilename() return f_path def sheet2excel(file_path): # 先读取一次文件,获取sheet表的名称 INFO.logger.info("选择保存的文件夹:") def save_directory(): root = tk.Tk() root.withdraw() f_path = filedialog.askdirectory() return f_path path = save_directory() origin_excel = load_workbook(filename=file_path) # 读取原excel文件 origin_sheet_names = origin_excel.sheetnames # 获取sheet的名称 INFO.logger.info(f'一共有{len(origin_sheet_names)}个sheet,名称分别为:{origin_sheet_names}\n拆分开始') if len(origin_sheet_names) > 1: # 如果sheetnames小于1,报错:该文件不需要拆分 for j in range(len(origin_sheet_names)): wb = load_workbook(filename=file_path) # 再读取一次文件,由于每次删除后需要保存一次,所以不能与上一次一样 sheet = wb[origin_sheet_names[j]] wb.copy_worksheet(sheet) new_filename = origin_sheet_names[j] + '.xlsx' # 新建一个sheet命名的excel文件 for i in range(len(origin_sheet_names)): sheet1 = wb[origin_sheet_names[i]] wb.remove(sheet1) new_path = path + "/" + new_filename INFO.logger.info(new_path) wb.save(filename=new_path) # 由于使用copy_worksheet后,sheet表名有copy字段,这里做个调整 new = load_workbook(filename=new_path) news = new.active news.title = origin_sheet_names[j] new.save(filename=new_path) INFO.logger.info('拆分完成!') else: raise Exception(f"你的文件只有一个sheet,难道还要拆分吗?你的文件名{file_path}") if __name__ == '__main__': INFO.logger.info("选择要拆解的excel文件:") path = open_file() sheet2excel(path)
python操作excel-->(把excel文件中的多个sheet拆成单个excel文件)
于 2022-10-11 15:35:59 首次发布