Needs Analysis:
Now there are a large number of Excel data files, you need to batch merge the data files into a summarized Excel data file according to the Sheet inside each Excel data file. Or a summarized Excel data file according to Sheet split into many Excel data files. According to the above requirements, we will first design the layout of the UI interface.
Import UI design related PyQt5 module
from import * from import * from import *
Application Operation Related Modules
import sys import os
excel data-processing module
import openpyxl as pxl import pandas as pd
Looking at the functionality and layout of the UI interface, it feels okay...
Here is an example of a layout-related code block
def init_ui(self):
('Excel Data Summarizer/Splitter Public: [Python Concentration Camp]')
(QIcon('data.ico'))
= QTextBrowser()
(True)
(QFont('Song Style', 8))
('Batch data processing progress display area...')
()
= QLineEdit()
(True)
self.excels_btn = QPushButton()
self.excels_btn.setText('Load batch file')
self.excels_btn.(self.excels_btn_click)
self.oprate_type = QLabel()
self.oprate_type.setText('Type of operation')
self.oprate_combox = QComboBox()
self.oprate_combox.addItems(['Data consolidation', 'Data splitting'])
self.data_type = QLabel()
self.data_type.setText('Merge/Split')
self.data_combox = QComboBox()
self.data_combox.addItems(['Split by Sheet'])
self.new_file_path = QLineEdit()
self.new_file_path.setReadOnly(True)
self.new_file_path_btn = QPushButton()
self.new_file_path_btn.setText('New file path')
self.new_file_path_btn.(self.new_file_path_btn_click)
self.thread_ = DataThread(self)
self.thread_.(self.update_log)
self.thread_.()
self.start_btn = QPushButton()
self.start_btn.setText('Start data summarization/splitting')
self.start_btn.(self.start_btn_click)
form = QFormLayout()
(, self.excels_btn)
(self.oprate_type, self.oprate_combox)
(self.data_type, self.data_combox)
(self.new_file_path, self.new_file_path_btn)
vbox = QVBoxLayout()
(form)
(self.start_btn)
hbox = QHBoxLayout()
()
(vbox)
(hbox)
Slot function update_log, the running process through the text browser to display real-time, easy to view the program running.
def update_log(self, text):
cursor = ()
()
(text)
(cursor)
()
Slot function excels_btn_click, bound to the file load button, handles the loading process of the source file.
def excels_btn_click(self):
paths = (self, 'Select file', (), 'Excel File(*.xlsx)')
files = paths[0]
path_strs = ''
for file in files:
path_strs = path_strs + file + ';'
(path_strs)
self.update_log('Batch file path loading has been completed!')
Slot function new_file_path_btn_click to select the path where the new file will be saved.
def new_file_path_btn_click(self):
directory = (self, 'Select folder', ())
self.new_file_path.setText(directory)
The slot function, start_btn_click, is bound to the start button and uses the start button to start the subthread working.
def start_btn_click(self):
self.start_btn.setEnabled(False)
self.thread_.start()
function finished, this function is used to receive the signal from the sub-thread to run finished, through the judgment to make the sub-thread execution is completed to let the start button in the state that can be clicked.
def finished(self, finished):
if finished is True:
self.start_btn.setEnabled(True)
Here is the most important part of the logic processing, put all the logic processing related parts all into the sub-thread to execute.
class DataThread(QThread):
trigger = pyqtSignal(str)
finished = pyqtSignal(bool)
def __init__(self, parent=None):
super(DataThread, self).__init__(parent)
= parent
= True
def __del__(self):
= False
()
def run(self):
('Starting batch processing subthread...')
oprate_type = .oprate_combox.currentText().strip()
data_type = .data_combox.currentText().strip()
files = ().strip()
new_file_path = .new_file_path.text()
if data_type == 'Split by Sheet' and oprate_type == 'Data consolidation':
self.merge_data(files=files, new_file_path=new_file_path)
elif data_type == 'Split by Sheet' and oprate_type == 'Data splitting':
self.split_data(files=files, new_file_path=new_file_path)
else:
pass
('Data processing complete...')
(True)
def merge_data(self, files, new_file_path):
num = 1
new_file = new_file_path + '/Data summary.xlsx'
writer = (new_file)
for file in (';'):
if () != '':
web_sheet = pxl.load_workbook(file)
sheets = web_sheet.sheetnames
for sheet in sheets:
sheet_name = ()
('Prepare to process worksheet name:' + str(()))
data_frame = pd.read_excel(file, sheet_name=sheet_name)
sheet_name = sheet_name + 'TO data merge' + str(num)
data_frame.to_excel(writer, sheet_name, index=False)
num = num + 1
else:
('Current path is empty, continue...')
()
()
def split_data(self, files, new_file_path):
num = 1
for file in (';'):
if () != '':
web_sheet = pxl.load_workbook(file)
sheets = web_sheet.sheetnames
for sheet in sheets:
sheet_name = ()
('Prepare to process worksheet name:' + str(()))
data_frame = pd.read_excel(file, sheet_name=sheet_name)
writer = (new_file_path + '/Data Split' + str(num) + '.xlsx')
data_frame.to_excel(writer, 'Data splitting', index=False)
()
()
num = num + 1
else:
('Current path is empty, continue...')
Above is the main code block implementation process, you can refer to it if you need. Welcome to the big brother in the comments section.
Got a program to run the effect of the picture, look at the implementation of the effect.
Full Code
# -*- coding:utf-8 -*-
# @author Python Concentration Camp
# @date 2022/1/12
# @file
# done
# Data Processing Widget: Excel Bulk Data File Splitter/Consolidator
# Needs analysis:
# Now there are a large number of Excel data files, according to each Excel data file inside the Sheet batch will be data files
# Merged into a summarized Excel data file.
# Or a summarized Excel data file is split into many Excel data files by Sheet.
# Based on the above requirements, let's start with the layout design of the UI interface.
# Import UI design related PyQt5 modules
from import *
from import *
from import *
# Modules related to application operations
import sys
import os
# excel data-processing module
import openpyxl as pxl
import pandas as pd
class ExcelDataMerge(QWidget):
def __init__(self):
super(ExcelDataMerge, self).__init__()
self.init_ui()
def init_ui(self):
('Excel Data Summarizer/Splitter Public: [Python Concentration Camp]')
(QIcon('data.ico'))
= QTextBrowser()
(True)
(QFont('Song Style', 8))
('Batch data processing progress display area...')
()
= QLineEdit()
(True)
self.excels_btn = QPushButton()
self.excels_btn.setText('Load batch file')
self.excels_btn.(self.excels_btn_click)
self.oprate_type = QLabel()
self.oprate_type.setText('Type of operation')
self.oprate_combox = QComboBox()
self.oprate_combox.addItems(['Data consolidation', 'Data splitting'])
self.data_type = QLabel()
self.data_type.setText('Merge/Split')
self.data_combox = QComboBox()
self.data_combox.addItems(['Split by Sheet'])
self.new_file_path = QLineEdit()
self.new_file_path.setReadOnly(True)
self.new_file_path_btn = QPushButton()
self.new_file_path_btn.setText('New file path')
self.new_file_path_btn.(self.new_file_path_btn_click)
self.thread_ = DataThread(self)
self.thread_.(self.update_log)
self.thread_.()
self.start_btn = QPushButton()
self.start_btn.setText('Start data summarization/splitting')
self.start_btn.(self.start_btn_click)
form = QFormLayout()
(, self.excels_btn)
(self.oprate_type, self.oprate_combox)
(self.data_type, self.data_combox)
(self.new_file_path, self.new_file_path_btn)
vbox = QVBoxLayout()
(form)
(self.start_btn)
hbox = QHBoxLayout()
()
(vbox)
(hbox)
def update_log(self, text):
cursor = ()
()
(text)
(cursor)
()
def excels_btn_click(self):
paths = (self, 'Select file', (), 'Excel File(*.xlsx)')
files = paths[0]
path_strs = ''
for file in files:
path_strs = path_strs + file + ';'
(path_strs)
self.update_log('Batch file path loading has been completed!')
def new_file_path_btn_click(self):
directory = (self, 'Select folder', ())
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)
= parent
= True
def __del__(self):
= False
()
def run(self):
('Starting batch processing subthread...')
oprate_type = .oprate_combox.currentText().strip()
data_type = .data_combox.currentText().strip()
files = ().strip()
new_file_path = .new_file_path.text()
if data_type == 'Split by Sheet' and oprate_type == 'Data consolidation':
self.merge_data(files=files, new_file_path=new_file_path)
elif data_type == 'Split by Sheet' and oprate_type == 'Data splitting':
self.split_data(files=files, new_file_path=new_file_path)
else:
pass
('Data processing complete...')
(True)
def merge_data(self, files, new_file_path):
num = 1
new_file = new_file_path + '/Data summary.xlsx'
writer = (new_file)
for file in (';'):
if () != '':
web_sheet = pxl.load_workbook(file)
sheets = web_sheet.sheetnames
for sheet in sheets:
sheet_name = ()
('Prepare to process worksheet name:' + str(()))
data_frame = pd.read_excel(file, sheet_name=sheet_name)
sheet_name = sheet_name + 'TO data merge' + str(num)
data_frame.to_excel(writer, sheet_name, index=False)
num = num + 1
else:
('Current path is empty, continue...')
()
()
def split_data(self, files, new_file_path):
num = 1
for file in (';'):
if () != '':
web_sheet = pxl.load_workbook(file)
sheets = web_sheet.sheetnames
for sheet in sheets:
sheet_name = ()
('Prepare to process worksheet name:' + str(()))
data_frame = pd.read_excel(file, sheet_name=sheet_name)
writer = (new_file_path + '/Data Split' + str(num) + '.xlsx')
data_frame.to_excel(writer, 'Data splitting', index=False)
()
()
num = num + 1
else:
('Current path is empty, continue...')
if __name__ == '__main__':
app = QApplication()
main = ExcelDataMerge()
()
(app.exec_())
The above is based on PyQt5 to make data processing gadgets in detail, more information about PyQt5 data processing tools please pay attention to my other related articles!