成品及功能介绍见原创发布区：https://www.52pojie.cn/thread-2026523-1-1.html
[Python] 纯文本查看 复制代码import os
import sys
from PyQt5.QtWidgets import (QApplication, QWidget, QVBoxLayout, QHBoxLayout,
QPushButton, QFileDialog, QProgressBar, QLabel,
QMessageBox, QFrame, QRadioButton, QButtonGroup)
from PyQt5.QtCore import Qt, QMimeData
from PyQt5.QtGui import QIcon, QFont, QPixmap, QDragEnterEvent, QDropEvent
from openpyxl import load_workbook, Workbook
from openpyxl.utils import get_column_letter
from copy import copy
def resource_path(relative_path):
"""获取资源文件的绝对路径，处理打包后和开发环境两种情况"""
try:
# PyInstaller创建的临时文件夹路径
base_path = sys._MEIPASS
except Exception:
base_path = os.path.abspath(".")
return os.path.join(base_path, relative_path)
class DropLabel(QLabel):
"""自定义支持拖拽的QLabel"""
def __init__(self, parent=None):
super().__init__(parent)
self.main_window = parent # 保存对主窗口的引用
self.setAcceptDrops(True)
self.setAlignment(Qt.AlignCenter)
self.normal_style = """
color: #666;
font-size: 13px;
padding: 8px;
background: #f9f9f9;
border-radius: 4px;
border: 1px solid #eee;
"""
self.drag_style = """
color: #666;
font-size: 13px;
padding: 8px;
background: #f0fff0;
border-radius: 4px;
border: 2px dashed #4CAF50;
"""
self.selected_style = """
color: #27ae60;
font-size: 13px;
padding: 8px;
background: #f0f8f0;
border-radius: 4px;
border: 1px solid #d0e8d0;
"""
self.setStyleSheet(self.normal_style)
def dragEnterEvent(self, event: QDragEnterEvent):
if event.mimeData().hasUrls():
event.acceptProposedAction()
self.setStyleSheet(self.drag_style)
else:
event.ignore()
def dragLeaveEvent(self, event):
self.setStyleSheet(self.normal_style if not self.text().startswith("已选择文件夹")
else self.selected_style)
def dropEvent(self, event: QDropEvent):
self.setStyleSheet(self.normal_style)
if event.mimeData().hasUrls():
urls = event.mimeData().urls()
if urls:
path = urls[0].toLocalFile()
if os.path.isdir(path):
self.main_window.handle_folder_selection(path) # 调用主窗口的方法
self.setStyleSheet(self.selected_style)
else:
QMessageBox.warning(self.main_window, "警告", "请拖拽有效的文件夹路径")
else:
QMessageBox.warning(self.main_window, "警告", "未获取到有效路径")
else:
QMessageBox.warning(self.main_window, "警告", "请拖拽文件夹路径")
class ExcelMergerApp(QWidget):
def __init__(self):
super().__init__()
self.initUI()
# 设置窗口图标 - 使用resource_path处理图标路径
icon_path = resource_path("logo.ico")
if os.path.exists(icon_path):
self.setWindowIcon(QIcon(icon_path))
else:
# 如果找不到图标文件，使用默认图标
self.setWindowIcon(QIcon.fromTheme("document-merge"))
def initUI(self):
self.setWindowTitle("Excel 多合一文件合并工具 By:Killerzeno")
self.setFixedSize(1024, 768) # 固定窗口大小
# 设置主窗口样式
self.setStyleSheet("""
QWidget {
background-color: #f5f7fa;
font-family: 'Microsoft YaHei';
}
QPushButton {
background-color: #4CAF50;
color: white;
border: none;
padding: 10px 20px;
text-align: center;
text-decoration: none;
font-size: 14px;
margin: 4px 2px;
border-radius: 6px;
min-width: 120px;
}
QPushButton:hover {
background-color: #45a049;
border: 1px solid #3d8b40;
}
QPushButton:pressed {
background-color: #3d8b40;
}
QPushButton:disabled {
background-color: #cccccc;
color: #666666;
}
QProgressBar {
border: 1px solid #ddd;
border-radius: 6px;
text-align: center;
height: 24px;
background: white;
}
QProgressBar::chunk {
background-color: #4CAF50;
border-radius: 5px;
width: 10px;
}
QLabel {
font-size: 14px;
color: #333;
}
QRadioButton {
font-size: 14px;
padding: 5px;
}
#header {
font-size: 22px;
font-weight: bold;
color: #2c3e50;
padding: 10px;
}
#frame {
background-color: white;
border-radius: 10px;
padding: 20px;
border: 1px solid #e0e0e0;
box-shadow: 0 2px 5px rgba(0,0,0,0.05);
}
#footer {
color: #7f8c8d;
font-size: 12px;
padding-top: 10px;
border-top: 1px solid #eee;
}
.option-group {
background: #f9f9f9;
border-radius: 6px;
padding: 15px;
border: 1px solid #e0e0e0;
}
""")
# 主布局
main_layout = QVBoxLayout()
main_layout.setContentsMargins(25, 25, 25, 25)
main_layout.setSpacing(20)
# 标题区域
title_layout = QHBoxLayout()
# 如果有图标，可以在这里添加
if hasattr(self, 'windowIcon') and not self.windowIcon().isNull():
icon_label = QLabel()
icon_label.setPixmap(self.windowIcon().pixmap(32, 32))
title_layout.addWidget(icon_label, 0, Qt.AlignLeft)
title = QLabel("Excel 多合一文件合并工具")
title.setObjectName("header")
title_layout.addWidget(title, 1, Qt.AlignCenter)
# 添加一个空的占位部件使标题居中
if hasattr(self, 'windowIcon') and not self.windowIcon().isNull():
title_layout.addWidget(QLabel(), 0, Qt.AlignRight)
main_layout.addLayout(title_layout)
# 添加分隔线
line = QFrame()
line.setFrameShape(QFrame.HLine)
line.setFrameShadow(QFrame.Sunken)
line.setStyleSheet("border-color: #e0e0e0;")
main_layout.addWidget(line)
# 内容框架
frame = QFrame()
frame.setObjectName("frame")
frame_layout = QVBoxLayout()
frame_layout.setContentsMargins(20, 20, 20, 20)
frame_layout.setSpacing(20)
# 文件夹选择部分
folder_layout = QVBoxLayout()
folder_layout.setSpacing(10)
self.folder_label = QLabel("请选择包含 Excel 文件的文件夹 (或拖拽文件夹到这里):")
self.folder_label.setStyleSheet("font-weight: bold;")
folder_layout.addWidget(self.folder_label)
self.selected_folder_label = DropLabel(self)
self.selected_folder_label.setText("未选择文件夹")
self.selected_folder_label.setWordWrap(True)
folder_layout.addWidget(self.selected_folder_label)
btn_layout = QHBoxLayout()
self.folderButton = QPushButton("选择文件夹")
self.folderButton.setIcon(QIcon.fromTheme("folder"))
self.folderButton.setCursor(Qt.PointingHandCursor)
self.folderButton.clicked.connect(self.selectFolder)
btn_layout.addWidget(self.folderButton)
btn_layout.addStretch()
folder_layout.addLayout(btn_layout)
frame_layout.addLayout(folder_layout)
# 命名选项部分
naming_layout = QVBoxLayout()
naming_layout.setSpacing(10)
naming_label = QLabel("合并后工作表命名规则:")
naming_label.setStyleSheet("font-weight: bold;")
naming_layout.addWidget(naming_label)
# 选项组容器
option_group = QWidget()
option_group.setObjectName("option-group")
option_group.setStyleSheet(".option-group {background: #f9f9f9;}")
option_layout = QVBoxLayout()
option_layout.setContentsMargins(10, 10, 10, 10)
# 创建单选按钮组
self.naming_option = QButtonGroup(self)
self.option_filename = QRadioButton("使用原文件名命名 (如果多工作表则使用 原文件名_原工作表名)")
self.option_filename.setChecked(True)
self.naming_option.addButton(self.option_filename, 1)
self.option_sheetname = QRadioButton("使用原工作表名命名 (如果多工作表则使用 原工作表名)")
self.naming_option.addButton(self.option_sheetname, 2)
option_layout.addWidget(self.option_filename)
option_layout.addWidget(self.option_sheetname)
option_group.setLayout(option_layout)
naming_layout.addWidget(option_group)
frame_layout.addLayout(naming_layout)
# 进度条部分
progress_layout = QVBoxLayout()
progress_layout.setSpacing(10)
progress_label = QLabel("合并进度:")
progress_label.setStyleSheet("font-weight: bold;")
progress_layout.addWidget(progress_label)
self.progressBar = QProgressBar(self)
self.progressBar.setValue(0)
self.progressBar.setFormat("当前进度: %p%")
progress_layout.addWidget(self.progressBar)
frame_layout.addLayout(progress_layout)
# 合并按钮
self.mergeButton = QPushButton("开始合并")
self.mergeButton.setIcon(QIcon.fromTheme("document-save"))
self.mergeButton.setStyleSheet("""
background-color: #3498db;
padding: 12px 24px;
font-size: 15px;
""")
self.mergeButton.setCursor(Qt.PointingHandCursor)
self.mergeButton.clicked.connect(self.mergeExcelFiles)
frame_layout.addWidget(self.mergeButton, 0, Qt.AlignHCenter)
frame.setLayout(frame_layout)
main_layout.addWidget(frame, 1) # 添加伸缩因子使框架占据更多空间
# 底部信息
footer = QLabel("© 2025 Excel 多合一文件合并工具 | 版本 1.0 | 开发：Killerzeno")
footer.setObjectName("footer")
footer.setAlignment(Qt.AlignCenter)
main_layout.addWidget(footer)
self.setLayout(main_layout)
def selectFolder(self):
folder_path = QFileDialog.getExistingDirectory(
self,
"请选择包含 Excel 文件的文件夹",
os.path.expanduser("~"),
QFileDialog.ShowDirsOnly
)
if folder_path:
self.handle_folder_selection(folder_path)
def handle_folder_selection(self, folder_path):
"""处理文件夹选择后的逻辑"""
try:
if os.path.isdir(folder_path):
self.folder_path = folder_path
self.selected_folder_label.setText(f"已选择文件夹:\n{folder_path}")
self.selected_folder_label.setStyleSheet(self.selected_folder_label.selected_style)
else:
QMessageBox.warning(self, "警告", "路径不是有效的文件夹")
except Exception as e:
QMessageBox.warning(self, "错误", f"处理路径时出错: {str(e)}")
def mergeExcelFiles(self):
if not hasattr(self, 'folder_path'):
msg = QMessageBox()
msg.setIcon(QMessageBox.Warning)
msg.setWindowTitle("警告")
msg.setText("请先选择包含 Excel 文件的文件夹")
msg.setStandardButtons(QMessageBox.Ok)
msg.exec_()
return
self.mergeButton.setEnabled(False)
self.folderButton.setEnabled(False)
self.progressBar.setValue(0)
output_file = os.path.join(self.folder_path, "汇总文件.xlsx")
try:
naming_rule = "filename" if self.option_filename.isChecked() else "sheetname"
self.merge_excel_files_to_sheets(self.folder_path, output_file, naming_rule)
msg = QMessageBox()
msg.setIcon(QMessageBox.Information)
msg.setWindowTitle("完成")
msg.setText(f"所有文件已成功合并到:\n{output_file}")
msg.setStandardButtons(QMessageBox.Ok)
msg.exec_()
except Exception as e:
msg = QMessageBox()
msg.setIcon(QMessageBox.Critical)
msg.setWindowTitle("错误")
msg.setText(f"处理文件时出错:\n{str(e)}")
msg.setStandardButtons(QMessageBox.Ok)
msg.exec_()
finally:
self.mergeButton.setEnabled(True)
self.folderButton.setEnabled(True)
self.progressBar.setValue(0)
def merge_excel_files_to_sheets(self, folder_path, output_file, naming_rule):
excel_files = []
for root, dirs, files in os.walk(folder_path):
for file in files:
if file.endswith('.xlsx'):
excel_files.append(os.path.join(root, file))
total_files = len(excel_files)
if total_files == 0:
raise Exception("所选文件夹及其子文件夹中没有找到 Excel 文件 (.xlsx)")
progress_step = 100 / total_files if total_files > 0 else 0
summary_wb = Workbook()
summary_wb.remove(summary_wb.active)
# 用于跟踪已使用的工作表名称
used_sheet_names = set()
for i, file in enumerate(excel_files, 1):
try:
wb = load_workbook(file)
file_base = os.path.splitext(os.path.basename(file))[0]
for sheet_name in wb.sheetnames:
ws = wb[sheet_name]
# 根据命名规则确定工作表名称
if naming_rule == "filename":
# 使用文件名命名，如果多工作表则添加工作表名
if len(wb.sheetnames) > 1:
new_sheet_name = f"{file_base}_{sheet_name}"
else:
new_sheet_name = file_base
else: # sheetname命名规则
# 使用工作表名命名，如果重名则添加文件名
if sheet_name in used_sheet_names:
new_sheet_name = f"{file_base}_{sheet_name}"
else:
new_sheet_name = sheet_name
# 确保名称唯一且不超过31个字符
original_name = new_sheet_name
counter = 1
while new_sheet_name in used_sheet_names or len(new_sheet_name) > 31:
new_sheet_name = f"{original_name[:28]}_{counter}" if len(
original_name) > 28 else f"{original_name}_{counter}"
counter += 1
used_sheet_names.add(new_sheet_name)
# 创建工作表并复制内容
summary_ws = summary_wb.create_sheet(title=new_sheet_name)
for row in ws.iter_rows(values_only=False):
for cell in row:
new_cell = summary_ws.cell(row=cell.row, column=cell.column, value=cell.value)
if cell.has_style:
new_cell.font = copy(cell.font)
new_cell.border = copy(cell.border)
new_cell.fill = copy(cell.fill)
new_cell.number_format = copy(cell.number_format)
new_cell.protection = copy(cell.protection)
new_cell.alignment = copy(cell.alignment)
# 更新进度
progress_value = int(i * progress_step)
self.progressBar.setValue(min(progress_value, 100))
QApplication.processEvents() # 确保UI更新
except Exception as e:
print(f"处理文件 {file} 时出错: {e}")
# 确保进度条最终显示为100%
self.progressBar.setValue(100)
summary_wb.save(output_file)
if __name__ == "__main__":
def handle_exception(exc_type, exc_value, exc_traceback):
import traceback
error_msg = "".join(traceback.format_exception(exc_type, exc_value, exc_traceback))
print(f"Unhandled exception: {error_msg}")
QMessageBox.critical(None, "未处理的异常", f"程序发生错误:\n{error_msg}")
sys.excepthook = handle_exception
app = QApplication(sys.argv)
font = QFont("Microsoft YaHei", 12)
app.setFont(font)
ex = ExcelMergerApp()
ex.show()
sys.exit(app.exec_())
image.png (97.41 KB, 下载次数: 0)
下载附件
2025-4-23 10:10 上传
