[Python] 纯文本查看 复制代码
import sys
import pandas as pd
from PyQt5.QtWidgets import (
QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout, QLabel,
QPushButton, QFileDialog, QTableWidget, QTableWidgetItem, QMessageBox,
QTabWidget, QSpinBox, QLineEdit, QFrame, QSplitter, QGroupBox, QGridLayout,
QComboBox, QListWidget, QListWidgetItem, QHeaderView, QProgressBar,
QMenu, QAction, QSizePolicy
)
from PyQt5.QtCore import Qt, pyqtSignal, QThread, QObject
from PyQt5.QtGui import QFont, QColor, QIcon
from datetime import datetime
class CompareWorker(QObject):
progress_updated = pyqtSignal(int)
finished = pyqtSignal(object)
error_occurred = pyqtSignal(str)
def __init__(self, df1, df2, col_pairs, file1_path, file2_path):
super().__init__()
self.df1 = df1
self.df2 = df2
self.col_pairs = col_pairs
self.file1_path = file1_path
self.file2_path = file2_path
def run_comparison(self):
try:
df1_compare = self.df1[[col1 for col1, col2 in self.col_pairs]].copy()
df2_compare = self.df2[[col2 for col1, col2 in self.col_pairs]].copy()
df1_compare['index'] = df1_compare.index
df2_compare['index'] = df2_compare.index
df1_compare['key'] = df1_compare.apply(
lambda row: tuple(str(row[col1]) for col1, _ in self.col_pairs), axis=1)
df2_compare['key'] = df2_compare.apply(
lambda row: tuple(str(row[col2]) for _, col2 in self.col_pairs), axis=1)
df1_keys = set(df1_compare['key'])
df2_keys = set(df2_compare['key'])
total_steps = len(self.df1) + len(self.df2)
processed = 0
results = []
for idx1, row1 in self.df1.iterrows():
key = tuple(str(row1[col1]) for col1, _ in self.col_pairs)
if key not in df2_keys:
result_row = ['仅在工作簿1中存在']
for col1, col2 in self.col_pairs:
result_row.extend([row1[col1], ''])
result_row.append(f'工作簿1_行{idx1+2}')
results.append(result_row)
processed += 1
if processed % 100 == 0:
progress = int((processed / total_steps) * 50)
self.progress_updated.emit(progress)
for idx2, row2 in self.df2.iterrows():
key = tuple(str(row2[col2]) for _, col2 in self.col_pairs)
if key not in df1_keys:
result_row = ['仅在工作簿2中存在']
for col1, col2 in self.col_pairs:
result_row.extend(['', row2[col2]])
result_row.append(f'工作簿2_行{idx2+2}')
results.append(result_row)
processed += 1
if processed % 100 == 0:
progress = 50 + int(((processed - len(self.df1)) / len(self.df2)) * 50)
self.progress_updated.emit(progress)
df1_key_to_rows = {}
for idx1, row1 in self.df1.iterrows():
key = tuple(str(row1[col1]) for col1, _ in self.col_pairs)
if key not in df1_key_to_rows:
df1_key_to_rows[key] = []
df1_key_to_rows[key].append((idx1, row1))
df2_key_to_rows = {}
for idx2, row2 in self.df2.iterrows():
key = tuple(str(row2[col2]) for _, col2 in self.col_pairs)
if key not in df2_key_to_rows:
df2_key_to_rows[key] = []
df2_key_to_rows[key].append((idx2, row2))
common_keys = set(df1_key_to_rows.keys()) & set(df2_key_to_rows.keys())
for key in common_keys:
rows1 = df1_key_to_rows[key]
rows2 = df2_key_to_rows[key]
if len(rows1) != len(rows2):
for idx1, row1 in rows1:
for idx2, row2 in rows2:
result_row = ['数据不匹配']
for col1, col2 in self.col_pairs:
result_row.extend([row1[col1], row2[col2]])
result_row.append(f'工作簿1_行{idx1+2}_vs_工作簿2_行{idx2+2}')
results.append(result_row)
file1_name = self.file1_path.split('/')[-1].split('\\')[-1]
file2_name = self.file2_path.split('/')[-1].split('\\')[-1]
result_columns = ['状态']
for i, (col1, col2) in enumerate(self.col_pairs):
result_columns.extend([f'{col1}({file1_name})', f'{col2}({file2_name})'])
result_columns.append('来源')
result_df = pd.DataFrame(results, columns=result_columns)
self.progress_updated.emit(100)
self.finished.emit(result_df)
except Exception as e:
self.error_occurred.emit(str(e))
class ExcelComparator(QMainWindow):
def __init__(self):
super().__init__()
self.initUI()
self.file1_path = ""
self.file2_path = ""
self.df1 = None
self.df2 = None
self.columns1 = []
self.columns2 = []
self.result_df = None
self.selected_columns = []
self.sheets1 = []
self.sheets2 = []
self.current_sheet1 = None
self.current_sheet2 = None
self.thread = None
self.worker = None
def initUI(self):
self.setWindowTitle('Excel数据对比工具By狂奔的蜗牛')
self.setGeometry(100, 100, 1400, 800)
central_widget = QWidget()
self.setCentralWidget(central_widget)
main_layout = QHBoxLayout(central_widget)
main_layout.setSpacing(15)
main_layout.setContentsMargins(15, 15, 15, 15)
left_panel = QWidget()
left_panel.setMaximumWidth(550)
left_layout = QVBoxLayout(left_panel)
left_layout.setSpacing(10)
header = QWidget()
header.setFixedHeight(70)
header_layout = QVBoxLayout(header)
header_layout.setContentsMargins(0, 0, 0, 0)
title_label = QLabel('Excel数据对比工具')
title_label.setAlignment(Qt.AlignCenter)
title_label.setStyleSheet("""
QLabel {
font-size: 22px;
font-weight: bold;
color: #2c3e50;
}
""")
subtitle_label = QLabel('快速对比两个Excel文件中的数据差异,起始行0表示第1行是标题')
subtitle_label.setAlignment(Qt.AlignCenter)
subtitle_label.setStyleSheet("""
QLabel {
font-size: 12px;
color: #7f8c8d;
margin-top: 3px;
}
""")
header_layout.addWidget(title_label)
header_layout.addWidget(subtitle_label)
left_layout.addWidget(header)
file_section = QGroupBox("文件选择")
file_section.setStyleSheet("""
QGroupBox {
font-size: 14px;
font-weight: bold;
color: #2c3e50;
border: 1px solid #ddd;
border-radius: 5px;
padding: 12px;
}
""")
file_layout = QVBoxLayout(file_section)
file1_layout = QHBoxLayout()
file1_btn = QPushButton("选择工作簿1")
file1_btn.setStyleSheet("""
QPushButton {
background-color: #3498db;
color: white;
border: none;
border-radius: 4px;
padding: 6px 12px;
font-size: 12px;
min-width: 100px;
}
QPushButton:hover {
background-color: #2980b9;
}
""")
file1_btn.clicked.connect(lambda: self.select_file(1))
self.file1_label = QLabel("未选择文件")
self.file1_label.setStyleSheet("color: #7f8c8d; font-size: 11px;")
self.file1_label.setMinimumWidth(180)
file1_layout.addWidget(file1_btn)
file1_layout.addWidget(self.file1_label)
file1_layout.addStretch()
sheet1_layout = QHBoxLayout()
sheet1_layout.addWidget(QLabel("工作表："))
self.sheet1_combo = QComboBox()
self.sheet1_combo.setMinimumWidth(120)
self.sheet1_combo.setMaximumWidth(120)
self.sheet1_combo.addItems(["请选择工作表"])
self.sheet1_combo.setEnabled(False)
sheet1_layout.addWidget(self.sheet1_combo)
self.load_sheet1_btn = QPushButton("确认")
self.load_sheet1_btn.setStyleSheet("""
QPushButton {
background-color: #9b59b6;
color: white;
border: none;
border-radius: 4px;
padding: 4px 8px;
font-size: 11px;
min-width: 60px;
}
QPushButton:hover {
background-color: #8e44ad;
}
""")
self.load_sheet1_btn.clicked.connect(lambda: self.load_sheet(1))
self.load_sheet1_btn.setEnabled(False)
sheet1_layout.addWidget(self.load_sheet1_btn)
sheet1_layout.addWidget(QLabel("起始行："))
self.header_row1 = QSpinBox()
self.header_row1.setMinimum(0)
self.header_row1.setMaximum(100)
self.header_row1.setValue(0)
self.header_row1.setFixedWidth(60)
sheet1_layout.addWidget(self.header_row1)
self.load_btn1 = QPushButton("加载列")
self.load_btn1.setStyleSheet("""
QPushButton {
background-color: #f39c12;
color: white;
border: none;
border-radius: 4px;
padding: 4px 8px;
font-size: 11px;
min-width: 60px;
}
QPushButton:hover {
background-color: #e67e22;
}
""")
self.load_btn1.clicked.connect(lambda: self.load_columns(1))
self.load_btn1.setEnabled(False)
sheet1_layout.addWidget(self.load_btn1)
sheet1_layout.addStretch()
file2_layout = QHBoxLayout()
file2_btn = QPushButton("选择工作簿2")
file2_btn.setStyleSheet("""
QPushButton {
background-color: #3498db;
color: white;
border: none;
border-radius: 4px;
padding: 6px 12px;
font-size: 12px;
min-width: 100px;
}
QPushButton:hover {
background-color: #2980b9;
}
""")
file2_btn.clicked.connect(lambda: self.select_file(2))
self.file2_label = QLabel("未选择文件")
self.file2_label.setStyleSheet("color: #7f8c8d; font-size: 11px;")
self.file2_label.setMinimumWidth(180)
file2_layout.addWidget(file2_btn)
file2_layout.addWidget(self.file2_label)
file2_layout.addStretch()
sheet2_layout = QHBoxLayout()
sheet2_layout.addWidget(QLabel("工作表："))
self.sheet2_combo = QComboBox()
self.sheet2_combo.setMinimumWidth(120)
self.sheet2_combo.setMaximumWidth(120)
self.sheet2_combo.addItems(["请选择工作表"])
self.sheet2_combo.setEnabled(False)
sheet2_layout.addWidget(self.sheet2_combo)
self.load_sheet2_btn = QPushButton("确认")
self.load_sheet2_btn.setStyleSheet("""
QPushButton {
background-color: #9b59b6;
color: white;
border: none;
border-radius: 4px;
padding: 4px 8px;
font-size: 11px;
min-width: 60px;
}
QPushButton:hover {
background-color: #8e44ad;
}
""")
self.load_sheet2_btn.clicked.connect(lambda: self.load_sheet(2))
self.load_sheet2_btn.setEnabled(False)
sheet2_layout.addWidget(self.load_sheet2_btn)
sheet2_layout.addWidget(QLabel("起始行："))
self.header_row2 = QSpinBox()
self.header_row2.setMinimum(0)
self.header_row2.setMaximum(100)
self.header_row2.setValue(0)
self.header_row2.setFixedWidth(60)
sheet2_layout.addWidget(self.header_row2)
self.load_btn2 = QPushButton("加载列")
self.load_btn2.setStyleSheet("""
QPushButton {
background-color: #f39c12;
color: white;
border: none;
border-radius: 4px;
padding: 4px 8px;
font-size: 11px;
min-width: 60px;
}
QPushButton:hover {
background-color: #e67e22;
}
""")
self.load_btn2.clicked.connect(lambda: self.load_columns(2))
self.load_btn2.setEnabled(False)
sheet2_layout.addWidget(self.load_btn2)
sheet2_layout.addStretch()
file_layout.addLayout(file1_layout)
file_layout.addLayout(sheet1_layout)
file_layout.addLayout(file2_layout)
file_layout.addLayout(sheet2_layout)
left_layout.addWidget(file_section)
col_section = QGroupBox("列对应关系设置")
col_section.setStyleSheet("""
QGroupBox {
font-size: 14px;
font-weight: bold;
color: #2c3e50;
border: 1px solid #ddd;
border-radius: 5px;
padding: 12px;
}
""")
col_layout = QVBoxLayout(col_section)
self.col_layout = QVBoxLayout()
col_layout.addLayout(self.col_layout)
btn_layout = QHBoxLayout()
self.add_col_btn = QPushButton("添加对比列")
self.add_col_btn.setFixedHeight(30)
self.add_col_btn.setStyleSheet("""
QPushButton {
background-color: #27ae60;
color: white;
border: none;
border-radius: 4px;
padding: 6px 12px;
font-size: 12px;
}
QPushButton:hover {
background-color: #229954;
}
""")
self.add_col_btn.clicked.connect(self.add_column_pair)
self.add_col_btn.setEnabled(False)
self.remove_col_btn = QPushButton("移除最后一列")
self.remove_col_btn.setFixedHeight(30)
self.remove_col_btn.setStyleSheet("""
QPushButton {
background-color: #e74c3c;
color: white;
border: none;
border-radius: 4px;
padding: 6px 12px;
font-size: 12px;
}
QPushButton:hover {
background-color: #c0392b;
}
""")
self.remove_col_btn.clicked.connect(self.remove_column_pair)
self.remove_col_btn.setEnabled(False)
btn_layout.addWidget(self.add_col_btn)
btn_layout.addWidget(self.remove_col_btn)
col_layout.addLayout(btn_layout)
left_layout.addWidget(col_section)
status_section = QGroupBox("状态信息")
status_section.setStyleSheet("""
QGroupBox {
font-size: 14px;
font-weight: bold;
color: #2c3e50;
border: 1px solid #ddd;
border-radius: 5px;
padding: 12px;
}
""")
status_layout = QVBoxLayout(status_section)
self.progress_bar = QProgressBar()
self.progress_bar.setRange(0, 100)
self.progress_bar.setValue(0)
self.progress_bar.setVisible(False)
self.progress_bar.setStyleSheet("""
QProgressBar {
border: 1px solid #ccc;
border-radius: 4px;
text-align: center;
font-size: 11px;
}
QProgressBar::chunk {
background-color: #3498db;
border-radius: 3px;
}
""")
self.status_label = QLabel("准备就绪")
self.status_label.setStyleSheet("""
QLabel {
font-size: 12px;
color: #7f8c8d;
padding: 3px;
}
""")
self.info_label = QLabel("文件信息：")
self.info_label.setStyleSheet("""
QLabel {
font-size: 11px;
color: #95a5a6;
padding: 3px;
}
""")
self.info_label.setWordWrap(True)
status_layout.addWidget(self.status_label)
status_layout.addWidget(self.progress_bar)
status_layout.addWidget(self.info_label)
left_layout.addWidget(status_section)
action_btn_layout = QHBoxLayout()
self.compare_btn = QPushButton("开始比对")
self.compare_btn.setFixedHeight(40)
self.compare_btn.setStyleSheet("""
QPushButton {
background-color: #27ae60;
color: white;
border: none;
border-radius: 4px;
padding: 10px 20px;
font-size: 14px;
font-weight: bold;
}
QPushButton:hover {
background-color: #229954;
}
QPushButton:disabled {
background-color: #bdc3c7;
color: #ecf0f1;
}
""")
self.compare_btn.clicked.connect(self.compare_data)
self.compare_btn.setEnabled(False)
self.clear_btn = QPushButton("清除结果")
self.clear_btn.setFixedHeight(40)
self.clear_btn.setStyleSheet("""
QPushButton {
background-color: #e74c3c;
color: white;
border: none;
border-radius: 4px;
padding: 10px 20px;
font-size: 14px;
font-weight: bold;
}
QPushButton:hover {
background-color: #c0392b;
}
""")
self.clear_btn.clicked.connect(self.clear_results)
self.export_btn = QPushButton("导出结果")
self.export_btn.setFixedHeight(40)
self.export_btn.setStyleSheet("""
QPushButton {
background-color: #f39c12;
color: white;
border: none;
border-radius: 4px;
padding: 10px 20px;
font-size: 14px;
font-weight: bold;
}
QPushButton:hover {
background-color: #e67e22;
}
QPushButton:disabled {
background-color: #bdc3c7;
color: #ecf0f1;
}
""")
self.export_btn.clicked.connect(self.export_results)
self.export_btn.setEnabled(False)
action_btn_layout.addWidget(self.compare_btn)
action_btn_layout.addWidget(self.clear_btn)
action_btn_layout.addWidget(self.export_btn)
left_layout.addLayout(action_btn_layout)
left_layout.addStretch()
right_panel = QWidget()
right_layout = QVBoxLayout(right_panel)
right_layout.setSpacing(10)
result_section = QGroupBox("对比结果")
result_section.setStyleSheet("""
QGroupBox {
font-size: 14px;
font-weight: bold;
color: #2c3e50;
border: 1px solid #ddd;
border-radius: 5px;
padding: 12px;
}
""")
result_layout = QVBoxLayout(result_section)
self.result_table = QTableWidget()
self.result_table.setStyleSheet("""
QTableWidget {
gridline-color: #ddd;
border: 1px solid #ddd;
background-color: white;
font-size: 12px;
}
QTableWidget::item {
padding: 6px;
border-bottom: 1px solid #eee;
background-color: transparent;
}
QTableWidget::item:selected {
background-color: #3498db;
color: white;
}
QHeaderView::section {
background-color: #f8f9fa;
color: #2c3e50;
padding: 8px;
border: 1px solid #ddd;
font-weight: bold;
font-size: 12px;
}
""")
self.result_table.setSizePolicy(
QSizePolicy.Expanding,
QSizePolicy.Expanding
)
result_layout.addWidget(self.result_table)
self.result_table.setRowCount(1)
self.result_table.setColumnCount(1)
self.result_table.setHorizontalHeaderLabels(["提示"])
item = QTableWidgetItem("请选择文件并点击'开始比对'按钮")
item.setTextAlignment(Qt.AlignCenter)
self.result_table.setItem(0, 0, item)
self.result_table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
right_layout.addWidget(result_section)
right_layout.addWidget(result_section)
main_layout.addWidget(left_panel)
main_layout.addWidget(right_panel, 1)
self.statusBar().showMessage("准备就绪")
version_label = QLabel('Version 0.1')
version_label.setStyleSheet("""
QLabel {
font-size: 12px;
color: #7f8c8d;
padding: 0 10px;
}
""")
status_widget = QWidget()
status_layout = QHBoxLayout(status_widget)
status_layout.setContentsMargins(0, 0, 0, 0)
status_layout.setSpacing(0)
status_layout.addStretch()
status_layout.addWidget(version_label)
self.statusBar().addPermanentWidget(status_widget)
def update_info_display(self):
info_text = "文件信息："
if self.file1_path:
if self.df1 is not None:
info_text += f"工作簿1：{self.file1_path.split('/')[-1].split('\\')[-1]} (工作表: {self.current_sheet1}, {len(self.df1)}行, {len(self.df1.columns)}列) | "
else:
info_text += f"工作簿1：{self.file1_path.split('/')[-1].split('\\')[-1]} (未加载) | "
if self.file2_path:
if self.df2 is not None:
info_text += f"工作簿2：{self.file2_path.split('/')[-1].split('\\')[-1]} (工作表: {self.current_sheet2}, {len(self.df2)}行, {len(self.df2.columns)}列)"
else:
info_text += f"工作簿2：{self.file2_path.split('/')[-1].split('\\')[-1]} (未加载)"
self.info_label.setText(info_text)
def select_file(self, file_num):
file_path, _ = QFileDialog.getOpenFileName(
self,
f"选择第{file_num}个Excel文件",
"",
"Excel files (*.xlsx *.xls *.xlsm);;All files (*.*)"
)
if file_path:
try:
excel_file = pd.ExcelFile(file_path)
sheet_names = excel_file.sheet_names
if file_num == 1:
self.file1_path = file_path
self.file1_label.setText(file_path.split('/')[-1].split('\\')[-1])
self.file1_label.setStyleSheet("color: #27ae60; font-size: 11px;")
self.sheets1 = sheet_names
self.sheet1_combo.clear()
self.sheet1_combo.addItems(sheet_names)
self.sheet1_combo.setEnabled(True)
self.load_sheet1_btn.setEnabled(True)
self.current_sheet1 = sheet_names[0]
self.sheet1_combo.setCurrentText(sheet_names[0])
self.df1 = pd.read_excel(file_path, sheet_name=sheet_names[0], header=0)
self.columns1 = [str(col) for col in self.df1.columns.tolist()]
self.load_btn1.setEnabled(True)
else:
self.file2_path = file_path
self.file2_label.setText(file_path.split('/')[-1].split('\\')[-1])
self.file2_label.setStyleSheet("color: #27ae60; font-size: 11px;")
self.sheets2 = sheet_names
self.sheet2_combo.clear()
self.sheet2_combo.addItems(sheet_names)
self.sheet2_combo.setEnabled(True)
self.load_sheet2_btn.setEnabled(True)
self.current_sheet2 = sheet_names[0]
self.sheet2_combo.setCurrentText(sheet_names[0])
self.df2 = pd.read_excel(file_path, sheet_name=sheet_names[0], header=0)
self.columns2 = [str(col) for col in self.df2.columns.tolist()]
self.load_btn2.setEnabled(True)
self.clear_columns_pairs()
self.update_info_display()
self.status_label.setText(f"文件加载成功：{file_path.split('/')[-1].split('\\')[-1]}，已自动选择第一个工作表：{sheet_names[0]}")
if len(self.columns1) > 0 and len(self.columns2) > 0:
self.add_col_btn.setEnabled(True)
except Exception as e:
self.status_label.setText(f"读取文件时出错：{str(e)}")
return
def load_sheet(self, file_num):
if file_num == 1:
sheet_name = self.sheet1_combo.currentText()
file_path = self.file1_path
if not file_path or not sheet_name:
self.status_label.setText("请先选择工作簿1文件和工作表")
return
else:
sheet_name = self.sheet2_combo.currentText()
file_path = self.file2_path
if not file_path or not sheet_name:
self.status_label.setText("请先选择工作簿2文件和工作表")
return
try:
df = pd.read_excel(file_path, sheet_name=sheet_name, header=0)
columns = [str(col) for col in df.columns.tolist()]
if file_num == 1:
self.df1 = df
self.columns1 = columns
self.current_sheet1 = sheet_name
else:
self.df2 = df
self.columns2 = columns
self.current_sheet2 = sheet_name
self.clear_columns_pairs()
self.update_info_display()
self.status_label.setText(f"工作表 {sheet_name} 加载完成！共 {len(columns)} 列。请重新添加列对应关系。")
if file_num == 1:
self.load_btn1.setEnabled(True)
else:
self.load_btn2.setEnabled(True)
if len(self.columns1) > 0 and len(self.columns2) > 0:
self.add_col_btn.setEnabled(True)
except Exception as e:
self.status_label.setText(f"加载工作表失败：{str(e)}")
def load_columns(self, file_num):
if file_num == 1:
file_path = self.file1_path
sheet_name = self.current_sheet1
header_row = self.header_row1.value()
if not file_path or not sheet_name:
self.status_label.setText("请先选择工作簿1文件和工作表")
return
else:
file_path = self.file2_path
sheet_name = self.current_sheet2
header_row = self.header_row2.value()
if not file_path or not sheet_name:
self.status_label.setText("请先选择工作簿2文件和工作表")
return
try:
df = pd.read_excel(file_path, sheet_name=sheet_name, header=header_row)
columns = [str(col) for col in df.columns.tolist()]
if file_num == 1:
self.df1 = df
self.columns1 = columns
else:
self.df2 = df
self.columns2 = columns
self.clear_columns_pairs()
self.update_info_display()
self.status_label.setText(f"工作簿{file_num}列加载完成！共 {len(columns)} 列。请重新添加列对应关系。")
if len(self.columns1) > 0 and len(self.columns2) > 0:
self.add_col_btn.setEnabled(True)
except Exception as e:
self.status_label.setText(f"加载列失败：{str(e)}")
def clear_columns_pairs(self):
for _, _, layout in self.selected_columns:
while layout.count() > 0:
item = layout.takeAt(0)
widget = item.widget()
if widget:
widget.deleteLater()
self.selected_columns = []
self.remove_col_btn.setEnabled(False)
self.compare_btn.setEnabled(False)
self.export_btn.setEnabled(False)
def add_column_pair(self):
row_layout = QHBoxLayout()
col1_combo = QComboBox()
string_columns1 = [str(col) for col in self.columns1]
col1_combo.addItems(string_columns1)
col1_combo.setMinimumWidth(120)
col1_combo.setMaximumWidth(120)
col2_combo = QComboBox()
string_columns2 = [str(col) for col in self.columns2]
col2_combo.addItems(string_columns2)
col2_combo.setMinimumWidth(120)
col2_combo.setMaximumWidth(120)
row_layout.addWidget(QLabel("工作簿1列："))
row_layout.addWidget(col1_combo)
row_layout.addWidget(QLabel(" <-> "))
row_layout.addWidget(col2_combo)
row_layout.addStretch()
self.selected_columns.append((col1_combo, col2_combo, row_layout))
self.col_layout.addLayout(row_layout)
self.remove_col_btn.setEnabled(True)
self.compare_btn.setEnabled(len(self.selected_columns) > 0)
def remove_column_pair(self):
if self.selected_columns:
_, _, layout = self.selected_columns.pop()
while layout.count() > 0:
item = layout.takeAt(0)
widget = item.widget()
if widget:
widget.deleteLater()
if not self.selected_columns:
self.remove_col_btn.setEnabled(False)
self.compare_btn.setEnabled(False)
self.export_btn.setEnabled(False)
def compare_data(self):
try:
col_pairs = []
file1_cols = []
file2_cols = []
for combo1, combo2, _ in self.selected_columns:
col1 = combo1.currentText()
col2 = combo2.currentText()
col_pairs.append((col1, col2))
file1_cols.append(col1)
file2_cols.append(col2)
missing_cols1 = [col for col in file1_cols if col not in self.df1.columns]
if missing_cols1:
self.status_label.setText(f"工作簿1中缺少以下列：{', '.join(missing_cols1)}")
return
missing_cols2 = [col for col in file2_cols if col not in self.df2.columns]
if missing_cols2:
self.status_label.setText(f"工作簿2中缺少以下列：{', '.join(missing_cols2)}")
return
self.progress_bar.setVisible(True)
self.progress_bar.setValue(0)
self.status_label.setText("正在准备比对...")
self.compare_btn.setEnabled(False)
self.thread = QThread()
self.worker = CompareWorker(self.df1, self.df2, col_pairs, self.file1_path, self.file2_path)
self.worker.moveToThread(self.thread)
self.thread.started.connect(self.worker.run_comparison)
self.worker.progress_updated.connect(self.update_progress)
self.worker.finished.connect(self.comparison_finished)
self.worker.error_occurred.connect(self.comparison_error)
self.thread.start()
except Exception as e:
self.status_label.setText(f"比对过程中出错：{str(e)}")
def update_progress(self, value):
self.progress_bar.setValue(value)
self.status_label.setText(f"比对进度：{value}%")
def comparison_finished(self, result_df):
self.result_df = result_df
self.display_results()
self.status_label.setText(f"比对完成！共找到 {len(result_df)} 条差异数据")
self.statusBar().showMessage(f"比对完成！共找到 {len(result_df)} 条差异数据")
self.export_btn.setEnabled(True)
self.progress_bar.setVisible(False)
self.compare_btn.setEnabled(True)
if self.thread:
self.thread.quit()
self.thread.wait()
self.thread = None
self.worker = None
def comparison_error(self, error_msg):
self.status_label.setText(f"比对失败：{error_msg}")
self.progress_bar.setVisible(False)
self.compare_btn.setEnabled(True)
self.export_btn.setEnabled(False)
if self.thread:
self.thread.quit()
self.thread.wait()
self.thread = None
self.worker = None
def display_results(self):
if self.result_df is None or self.result_df.empty:
self.result_table.setRowCount(1)
self.result_table.setColumnCount(1)
self.result_table.setHorizontalHeaderLabels(["提示"])
item = QTableWidgetItem("没有找到差异数据！")
item.setTextAlignment(Qt.AlignCenter)
self.result_table.setItem(0, 0, item)
self.export_btn.setEnabled(False)
return
rows, cols = self.result_df.shape
self.result_table.setRowCount(rows)
self.result_table.setColumnCount(cols)
self.result_table.setHorizontalHeaderLabels(self.result_df.columns)
for i in range(rows):
status = self.result_df.iloc[i, 0]
if status == '仅在工作簿1中存在':
bg_color = QColor(255, 223, 186)
elif status == '仅在工作簿2中存在':
bg_color = QColor(186, 223, 255)
elif status == '数据不匹配':
bg_color = QColor(255, 182, 193)
else:
bg_color = QColor(255, 255, 255)
for j in range(cols):
value = self.result_df.iloc[i, j]
item = QTableWidgetItem(str(value) if pd.notna(value) else "")
item.setBackground(bg_color)
self.result_table.setItem(i, j, item)
self.result_table.horizontalHeader().setSectionResizeMode(QHeaderView.Interactive)
for col in range(self.result_table.columnCount()):
self.result_table.horizontalHeader().setSectionResizeMode(col, QHeaderView.Stretch)
self.export_btn.setEnabled(True)
def export_results(self):
if self.result_df is None or self.result_df.empty:
self.status_label.setText("没有可导出的结果")
return
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
default_filename = f"对比结果_{timestamp}.xlsx"
file_path, _ = QFileDialog.getSaveFileName(
self,
"保存对比结果",
default_filename,
"Excel files (*.xlsx *.xls);;All files (*.*)"
)
if not file_path:
return
try:
self.result_df.to_excel(file_path, index=False)
self.status_label.setText(f"结果已成功导出到：{file_path}")
QMessageBox.information(self, "成功", f"结果已成功导出到：\n{file_path}")
except Exception as e:
self.status_label.setText(f"导出失败：{str(e)}")
QMessageBox.critical(self, "错误", f"导出结果时出错：{str(e)}")
def clear_results(self):
self.result_table.setRowCount(1)
self.result_table.setColumnCount(1)
self.result_table.setHorizontalHeaderLabels(["提示"])
item = QTableWidgetItem("请选择文件并点击'开始比对'按钮")
item.setTextAlignment(Qt.AlignCenter)
self.result_table.setItem(0, 0, item)
for _, _, layout in self.selected_columns:
while layout.count() > 0:
item = layout.takeAt(0)
widget = item.widget()
if widget:
widget.deleteLater()
self.selected_columns = []
self.remove_col_btn.setEnabled(False)
self.export_btn.setEnabled(False)
if self.file1_path and self.file2_path:
self.add_col_btn.setEnabled(True)
self.compare_btn.setEnabled(False)
else:
self.add_col_btn.setEnabled(False)
self.compare_btn.setEnabled(False)
self.status_label.setText("已清除结果")
self.progress_bar.setVisible(False)
self.progress_bar.setValue(0)
self.statusBar().showMessage("已清除结果")
def main():
try:
import pandas as pd
import openpyxl
except ImportError as e:
print("缺少必要的库，请安装：")
print("pip install pandas openpyxl PyQt5")
return
app = QApplication(sys.argv)
app.setStyle("Fusion")
font = QFont("Microsoft YaHei")
app.setFont(font)
window = ExcelComparator()
window.show()
sys.exit(app.exec_())
if __name__ == "__main__":
main()
[复制链接]
发表于 2025-10-8 07:44
|
发表于 2025-10-16 13:12
|楼主
分享到朋友圈
