准备了三个文件，每个文件有三个sheet，每个sheet里都有些简单的数据
界面设计需要一个文本框显示文件夹路径，有一个浏览按钮可以选择文件夹，再就是有一个执行处理的按钮，大体效果如下：
我们使用openpyxl来进行表格的操作。因为它不是Python的标准库，如果没有安装过的话，我们需要安装一下。
pip install openpyxl
全部代码
import tkinter as tk
import os
import copy
from tkinter import filedialog
from tkinter import messagebox
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
class WorkbookMerge:
def __init__(self):
# 创建主窗口
root = tk.Tk()
root.geometry('600x150 550 200')
root.title("合并Excel多簿")
page = tk.Frame()
page.pack()
text = tk.StringVar()
# 界面浏览的文件路径和存储路径
def select_folder():
folderpath = filedialog.askdirectory()
if folderpath == "":
messagebox.showinfo(title="PDF处理", message="该目录下文件为空，请重新选择目录")
else:
entry.delete(0, tk.END) # 清除文本框中的内容
entry.insert(0, folderpath) # 插入新的文本内容
# 执行处理
def executor():
mfname = "合并.xlsx"
path = entry.get()
fullpath = path "//" mfname
# 新建工作簿，用来存储合并的内容
wb = Workbook()
# wb.save(fullpath)
# wb = load_workbook(filename=fullpath)
for root, dirs, files in os.walk(path):
for file in files:
file_path = os.path.join(root, file)
workbook = load_workbook(filename=file_path)
# 获取所有的工作表名称
sheet_names = workbook.sheetnames
for sheet_name in sheet_names:
sheet = workbook[sheet_name]
now_sheet = wb.create_sheet(sheet.title)
now_sheet.sheet_properties.tabColor = sheet.sheet_properties.tabColor
# 开始处理合并单元格形式为“(<CellRange A1：A4>,)，替换掉(<CellRange 和 >,)' 找到合并单元格
wm = list(sheet.merged_cells)
if len(wm) > 0:
for i in range(0, len(wm)):
cell2 = str(wm[i]).replace('(<CellRange ', '').replace('>,)', '')
now_sheet.merge_cells(cell2)
for i, row in enumerate(sheet.iter_rows()):
now_sheet.row_dimensions[i 1].height = sheet.row_dimensions[i 1].height
for j, cell in enumerate(row):
now_sheet.column_dimensions[get_column_letter(j 1)].width = sheet.column_dimensions[
get_column_letter(j 1)].width
now_sheet.cell(row=i 1, column=j 1, value=cell.value)
# 设置单元格格式
source_cell = sheet.cell(i 1, j 1)
target_cell = now_sheet.cell(i 1, j 1)
target_cell.fill = copy.copy(source_cell.fill)
if source_cell.has_style:
target_cell._style = copy.copy(source_cell._style)
target_cell.font = copy.copy(source_cell.font)
target_cell.border = copy.copy(source_cell.border)
target_cell.fill = copy.copy(source_cell.fill)
target_cell.number_format = copy.copy(source_cell.number_format)
target_cell.protection = copy.copy(source_cell.protection)
target_cell.alignment = copy.copy(source_cell.alignment)
workbook.close()
print(file_path)
wb.save(fullpath)
messagebox.showinfo(title='Excel合并多表', message='处理完成！')
tk.Label(page, text='待处理文件夹:', font=('华文楷体', 15)).grid(row=4, column=1, pady=10, padx=10)
entry = tk.Entry(page, textvariable=text, width=45)
entry.grid(row=4, column=2, padx=10)
tk.Button(page, text='浏览...', font=('华文楷体', 12), width=5, height=1, command=select_folder).grid(
row=4,
column=3)
tk.Button(page, text='执行处理', font=('华文楷体', 15), command=executor).grid(row=5, column=2)
root.mainloop()
if __name__ == '__main__':
WorkbookMerge()
运行效果
启动
浏览要处理文件夹
