需求分析：
现在有一大堆的Excel数据文件，需要根据每个Excel数据文件里面的Sheet批量将数据文件合并成为一个汇总后的Excel数据文件。或者是将一个汇总后的Excel数据文件按照Sheet拆分成很多个Excel数据文件。根据上面的需求，我们先来进行UI界面的布局设计。
导入UI界面设计相关的PyQt5模块
|
1
2
3
|
from PyQt5.QtWidgets import *from PyQt5.QtCore import *from PyQt5.QtGui import * |
应用操作相关的模块
|
1
2
|
import sysimport os |
excel 数据处理模块
|
1
2
|
import openpyxl as pxlimport pandas as pd |
看一下 UI 界面的功能和布局，感觉还可以...
下面是布局相关的代码块实例
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
def init_ui(self): self.setWindowTitle('Excel数据汇总/拆分器 公众号：[Python 集中营]') self.setWindowIcon(QIcon('数据.ico')) self.brower = QTextBrowser() self.brower.setReadOnly(True) self.brower.setFont(QFont('宋体', 8)) self.brower.setPlaceholderText('批量数据处理进度显示区域...') self.brower.ensureCursorVisible() self.excels = QLineEdit() self.excels.setReadOnly(True) self.excels_btn = QPushButton() self.excels_btn.setText('加载批文件') self.excels_btn.clicked.connect(self.excels_btn_click) self.oprate_type = QLabel() self.oprate_type.setText('操作类型') self.oprate_combox = QComboBox() self.oprate_combox.addItems(['数据合并', '数据拆分']) self.data_type = QLabel() self.data_type.setText('合并/拆分') self.data_combox = QComboBox() self.data_combox.addItems(['按照Sheet拆分']) self.new_file_path = QLineEdit() self.new_file_path.setReadOnly(True) self.new_file_path_btn = QPushButton() self.new_file_path_btn.setText('新文件路径') self.new_file_path_btn.clicked.connect(self.new_file_path_btn_click) self.thread_ = DataThread(self) self.thread_.trigger.connect(self.update_log) self.thread_.finished.connect(self.finished) self.start_btn = QPushButton() self.start_btn.setText('开始数据汇总/拆分') self.start_btn.clicked.connect(self.start_btn_click) form = QFormLayout() form.addRow(self.excels, self.excels_btn) form.addRow(self.oprate_type, self.oprate_combox) form.addRow(self.data_type, self.data_combox) form.addRow(self.new_file_path, self.new_file_path_btn) vbox = QVBoxLayout() vbox.addLayout(form) vbox.addWidget(self.start_btn) hbox = QHBoxLayout() hbox.addWidget(self.brower) hbox.addLayout(vbox) self.setLayout(hbox) |
槽函数 update_log，将运行过程通过文本浏览器的方式实时展示，方便查看程序的运行。
|
1
2
3
4
5
6
|
def update_log(self, text): cursor = self.brower.textCursor() cursor.movePosition(QTextCursor.End) self.brower.append(text) self.brower.setTextCursor(cursor) self.brower.ensureCursorVisible() |
槽函数 excels_btn_click，绑定到文件加载按钮，处理源文件的加载过程。
|
1
2
3
4
5
6
7
8
|
def excels_btn_click(self): paths = QFileDialog.getOpenFileNames(self, '选择文件', os.getcwd(), 'Excel File(*.xlsx)') files = paths[0] path_strs = '' for file in files: path_strs = path_strs + file + ';' self.excels.setText(path_strs) self.update_log('已经完成批文件路径加载！') |
槽函数 new_file_path_btn_click，选择新文件要保存的路径。
|
1
2
3
|
def new_file_path_btn_click(self): directory = QFileDialog.getExistingDirectory(self, '选择文件夹', os.getcwd()) self.new_file_path.setText(directory) |
槽函数 start_btn_click，绑定到开始按钮上，使用开始按钮启动子线程工作。
|
1
2
3
|
def start_btn_click(self): self.start_btn.setEnabled(False) self.thread_.start() |
函数 finished，这个函数是用来接收子线程传过来的运行完成的信号，通过判断使子线程执行完成时让开始按钮处于可以点击的状态。
|
1
2
3
|
def finished(self, finished): if finished is True: self.start_btn.setEnabled(True) |
下面是最重要的逻辑处理部分，将所有的逻辑处理相关的部分全部放到子线程中去执行。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
|
class DataThread(QThread): trigger = pyqtSignal(str) finished = pyqtSignal(bool) def __init__(self, parent=None): super(DataThread, self).__init__(parent) self.parent = parent self.working = True def __del__(self): self.working = False self.wait() def run(self): self.trigger.emit('启动批量处理子线程...') oprate_type = self.parent.oprate_combox.currentText().strip() data_type = self.parent.data_combox.currentText().strip() files = self.parent.excels.text().strip() new_file_path = self.parent.new_file_path.text() if data_type == '按照Sheet拆分' and oprate_type == '数据合并': self.merge_data(files=files, new_file_path=new_file_path) elif data_type == '按照Sheet拆分' and oprate_type == '数据拆分': self.split_data(files=files, new_file_path=new_file_path) else: pass self.trigger.emit('数据处理完成...') self.finished.emit(True) def merge_data(self, files, new_file_path): num = 1 new_file = new_file_path + '/数据汇总.xlsx' writer = pd.ExcelWriter(new_file) for file in files.split(';'): if file.strip() != '': web_sheet = pxl.load_workbook(file) sheets = web_sheet.sheetnames for sheet in sheets: sheet_name = sheet.title() self.trigger.emit('准备处理工作表名称：' + str(sheet.title())) data_frame = pd.read_excel(file, sheet_name=sheet_name) sheet_name = sheet_name + 'TO数据合并' + str(num) data_frame.to_excel(writer, sheet_name, index=False) num = num + 1 else: self.trigger.emit('当前路径为空，继续...') writer.save() writer.close() def split_data(self, files, new_file_path): num = 1 for file in files.split(';'): if file.strip() != '': web_sheet = pxl.load_workbook(file) sheets = web_sheet.sheetnames for sheet in sheets: sheet_name = sheet.title() self.trigger.emit('准备处理工作表名称：' + str(sheet.title())) data_frame = pd.read_excel(file, sheet_name=sheet_name) writer = pd.ExcelWriter(new_file_path + '/数据拆分' + str(num) + '.xlsx') data_frame.to_excel(writer, '数据拆分', index=False) writer.save() writer.close() num = num + 1 else: self.trigger.emit('当前路径为空，继续...') |
上面就是主要的代码块实现过程，有需要的可以参考一下。欢迎大佬在评论区进行留言。
搞了一个程序运行效果图，看一下执行效果。
完整代码
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
|
# -*- coding:utf-8 -*-# @author Python 集中营# @date 2022/1/12# @file test8.py# done# 数据处理小工具：Excel 批量数据文件拆分/整合器# 需求分析：# 现在有一大堆的Excel数据文件，需要根据每个Excel数据文件里面的Sheet批量将数据文件# 合并成为一个汇总后的Excel数据文件。# 或者是将一个汇总后的Excel数据文件按照Sheet拆分成很多个Excel数据文件。# 根据上面的需求，我们先来进行UI界面的布局设计。# 导入UI界面设计相关的PyQt5模块from PyQt5.QtWidgets import *from PyQt5.QtCore import *from PyQt5.QtGui import *# 应用操作相关的模块import sysimport os# excel 数据处理模块import openpyxl as pxlimport pandas as pdclass ExcelDataMerge(QWidget): def __init__(self): super(ExcelDataMerge, self).__init__() self.init_ui() def init_ui(self): self.setWindowTitle('Excel数据汇总/拆分器 公众号：[Python 集中营]') self.setWindowIcon(QIcon('数据.ico')) self.brower = QTextBrowser() self.brower.setReadOnly(True) self.brower.setFont(QFont('宋体', 8)) self.brower.setPlaceholderText('批量数据处理进度显示区域...') self.brower.ensureCursorVisible() self.excels = QLineEdit() self.excels.setReadOnly(True) self.excels_btn = QPushButton() self.excels_btn.setText('加载批文件') self.excels_btn.clicked.connect(self.excels_btn_click) self.oprate_type = QLabel() self.oprate_type.setText('操作类型') self.oprate_combox = QComboBox() self.oprate_combox.addItems(['数据合并', '数据拆分']) self.data_type = QLabel() self.data_type.setText('合并/拆分') self.data_combox = QComboBox() self.data_combox.addItems(['按照Sheet拆分']) self.new_file_path = QLineEdit() self.new_file_path.setReadOnly(True) self.new_file_path_btn = QPushButton() self.new_file_path_btn.setText('新文件路径') self.new_file_path_btn.clicked.connect(self.new_file_path_btn_click) self.thread_ = DataThread(self) self.thread_.trigger.connect(self.update_log) self.thread_.finished.connect(self.finished) self.start_btn = QPushButton() self.start_btn.setText('开始数据汇总/拆分') self.start_btn.clicked.connect(self.start_btn_click) form = QFormLayout() form.addRow(self.excels, self.excels_btn) form.addRow(self.oprate_type, self.oprate_combox) form.addRow(self.data_type, self.data_combox) form.addRow(self.new_file_path, self.new_file_path_btn) vbox = QVBoxLayout() vbox.addLayout(form) vbox.addWidget(self.start_btn) hbox = QHBoxLayout() hbox.addWidget(self.brower) hbox.addLayout(vbox) self.setLayout(hbox) def update_log(self, text): cursor = self.brower.textCursor() cursor.movePosition(QTextCursor.End) self.brower.append(text) self.brower.setTextCursor(cursor) self.brower.ensureCursorVisible() def excels_btn_click(self): paths = QFileDialog.getOpenFileNames(self, '选择文件', os.getcwd(), 'Excel File(*.xlsx)') files = paths[0] path_strs = '' for file in files: path_strs = path_strs + file + ';' self.excels.setText(path_strs) self.update_log('已经完成批文件路径加载！') def new_file_path_btn_click(self): directory = QFileDialog.getExistingDirectory(self, '选择文件夹', os.getcwd()) self.new_file_path.setText(directory) def start_btn_click(self): self.start_btn.setEnabled(False) self.thread_.start() def finished(self, finished): if finished is True: self.start_btn.setEnabled(True)class DataThread(QThread): trigger = pyqtSignal(str) finished = pyqtSignal(bool) def __init__(self, parent=None): super(DataThread, self).__init__(parent) self.parent = parent self.working = True def __del__(self): self.working = False self.wait() def run(self): self.trigger.emit('启动批量处理子线程...') oprate_type = self.parent.oprate_combox.currentText().strip() data_type = self.parent.data_combox.currentText().strip() files = self.parent.excels.text().strip() new_file_path = self.parent.new_file_path.text() if data_type == '按照Sheet拆分' and oprate_type == '数据合并': self.merge_data(files=files, new_file_path=new_file_path) elif data_type == '按照Sheet拆分' and oprate_type == '数据拆分': self.split_data(files=files, new_file_path=new_file_path) else: pass self.trigger.emit('数据处理完成...') self.finished.emit(True) def merge_data(self, files, new_file_path): num = 1 new_file = new_file_path + '/数据汇总.xlsx' writer = pd.ExcelWriter(new_file) for file in files.split(';'): if file.strip() != '': web_sheet = pxl.load_workbook(file) sheets = web_sheet.sheetnames for sheet in sheets: sheet_name = sheet.title() self.trigger.emit('准备处理工作表名称：' + str(sheet.title())) data_frame = pd.read_excel(file, sheet_name=sheet_name) sheet_name = sheet_name + 'TO数据合并' + str(num) data_frame.to_excel(writer, sheet_name, index=False) num = num + 1 else: self.trigger.emit('当前路径为空，继续...') writer.save() writer.close() def split_data(self, files, new_file_path): num = 1 for file in files.split(';'): if file.strip() != '': web_sheet = pxl.load_workbook(file) sheets = web_sheet.sheetnames for sheet in sheets: sheet_name = sheet.title() self.trigger.emit('准备处理工作表名称：' + str(sheet.title())) data_frame = pd.read_excel(file, sheet_name=sheet_name) writer = pd.ExcelWriter(new_file_path + '/数据拆分' + str(num) + '.xlsx') data_frame.to_excel(writer, '数据拆分', index=False) writer.save() writer.close() num = num + 1 else: self.trigger.emit('当前路径为空，继续...')if __name__ == '__main__': app = QApplication(sys.argv) main = ExcelDataMerge() main.show() sys.exit(app.exec_()) |
以上就是基于PyQt5制作数据处理小工具的详细内容，更多关于PyQt5数据处理工具的资料请关注服务器之家其它相关文章！
原文链接：https://www.cnblogs.com/lwsbc/p/15966423.html
