Relevant documents

Little buddy who wants to learn Python can pay attention to the official account of Xiaobian [Python journal].
There are many resources for whoring for nothing, ha. I will update the little knowledge of Python from time to time!!
A buddy who needs source code can reply to Excel form in official account.
Python source code, problem solving, learning exchange group: 773162165

requirement analysis

Now there are a lot of Excel data files. You need to merge the data files into a summarized Excel data file in batches according to the Sheet in each excel data file. Or split a summarized Excel data file into many Excel data files according to Sheet. According to the above requirements, let's first design the layout of the UI interface.

Import PyQt5 module related to UI interface design

from PyQt5.QtWidgets import *

from PyQt5.QtCore import *

from PyQt5.QtGui import *

Application operation related modules

import sys

import os

excel data processing module

import openpyxl as pxl

import pandas as pd

Take a look at the functions and layout of the UI interface. I think it's ok

code implementation

The following is an example of a layout related code block

   def init_ui(self):
        self.setWindowTitle('Excel Data summary/splitter ')
        self.setWindowIcon(QIcon('data.ico'))

        self.brower = QTextBrowser()
        self.brower.setReadOnly(True)
        self.brower.setFont(QFont('Song style', 8))
        self.brower.setPlaceholderText('Batch data processing progress display area...')
        self.brower.ensureCursorVisible()

        self.excels = QLineEdit()
        self.excels.setReadOnly(True)

        self.excels_btn = QPushButton()
        self.excels_btn.setText('Load batch file')
        self.excels_btn.clicked.connect(self.excels_btn_click)

        self.oprate_type = QLabel()
        self.oprate_type.setText('Operation type')

        self.oprate_combox = QComboBox()
        self.oprate_combox.addItems(['Data merging', 'Data splitting'])

        self.data_type = QLabel()
        self.data_type.setText('merge/split')

        self.data_combox = QComboBox()
        self.data_combox.addItems(['according to Sheet split'])

        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.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('Start data summary/split')
        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)

Slot function update_log, which displays the running process in real time through a text browser to facilitate viewing the operation of the program.

   def update_log(self, text):
        cursor = self.brower.textCursor()
        cursor.movePosition(QTextCursor.End)
        self.brower.append(text)
        self.brower.setTextCursor(cursor)
        self.brower.ensureCursorVisible()

Slot function excel_ btn_ Click, which is bound to the file loading button to handle the loading process of the source file.

 def excels_btn_click(self):
        paths = QFileDialog.getOpenFileNames(self, 'Select file', 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('Batch file path loading has been completed!')

Slot function new_file_path_btn_click to select the path to save the new file.

  def new_file_path_btn_click(self):
        directory = QFileDialog.getExistingDirectory(self, 'Select Folder', os.getcwd())
        self.new_file_path.setText(directory)

Slot function start_btn_click, bound to the start button, and use the start button to start the child thread.

 def start_btn_click(self):
        self.start_btn.setEnabled(False)
        self.thread_.start()

Function finished. This function is used to receive the running completion signal from the sub thread, and make the start button in the clickable state when the sub thread is completed by judgment.

  def finished(self, finished):
        if finished is True:
            self.start_btn.setEnabled(True)

The following is the most important part of logical processing. Put all relevant parts of logical processing into sub threads for execution.

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('Start batch processing sub thread...')
        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 == 'according to Sheet split' and oprate_type == 'Data merging':
            self.merge_data(files=files, new_file_path=new_file_path)
        elif data_type == 'according to Sheet split' and oprate_type == 'Data splitting':
            self.split_data(files=files, new_file_path=new_file_path)
        else:
            pass
        self.trigger.emit('Data processing completed...')
        self.finished.emit(True)

    def merge_data(self, files, new_file_path):
        num = 1
        new_file = new_file_path + '/Data summary.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('Preparation worksheet name:' + str(sheet.title()))
                    data_frame = pd.read_excel(file, sheet_name=sheet_name)
                    sheet_name = sheet_name + 'TO Data merging' + str(num)
                    data_frame.to_excel(writer, sheet_name, index=False)
                    num = num + 1
            else:
                self.trigger.emit('Current path is empty, continue...')
        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('Preparation worksheet name:' + str(sheet.title()))
                    data_frame = pd.read_excel(file, sheet_name=sheet_name)
                    writer = pd.ExcelWriter(new_file_path + '/Data splitting' + str(num) + '.xlsx')
                    data_frame.to_excel(writer, 'Data splitting', index=False)
                    writer.save()
                    writer.close()
                    num = num + 1
            else:
                self.trigger.emit('Current path is empty, continue...')

The above is the main code block implementation process. You can refer to it if necessary. Welcome to leave a message in the comment area.

Effect display

Made a program running effect diagram to see the execution effect.

Official account: Python log
The official account returns the Excel form to get the complete source code and run directly.