基于PyQt5制作數(shù)據(jù)處理小工具

更新時間：2022年03月05日 08:35:36 作者：Python 集中營

這篇文章主要和大家介紹了如何利用Python中的PyQt5模塊制作一個數(shù)據(jù)處理小工具，可以實現(xiàn)根據(jù)每個Excel數(shù)據(jù)文件里面的Sheet批量將數(shù)據(jù)文件合并成為一個匯總后的Excel數(shù)據(jù)文件，需要的可以參考一下

需求分析：

現(xiàn)在有一大堆的Excel數(shù)據(jù)文件，需要根據(jù)每個Excel數(shù)據(jù)文件里面的Sheet批量將數(shù)據(jù)文件合并成為一個匯總后的Excel數(shù)據(jù)文件?；蛘呤菍⒁粋€匯總后的Excel數(shù)據(jù)文件按照Sheet拆分成很多個Excel數(shù)據(jù)文件。根據(jù)上面的需求，我們先來進行UI界面的布局設(shè)計。

導(dǎo)入UI界面設(shè)計相關(guān)的PyQt5模塊

from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
from PyQt5.QtGui import *

應(yīng)用操作相關(guān)的模塊

import sys
import os

excel 數(shù)據(jù)處理模塊

import openpyxl as pxl
import pandas as pd

看一下 UI 界面的功能和布局，感覺還可以...

file

下面是布局相關(guān)的代碼塊實例

    def init_ui(self):
        self.setWindowTitle('Excel數(shù)據(jù)匯總/拆分器  公眾號：[Python 集中營]')
        self.setWindowIcon(QIcon('數(shù)據(jù).ico'))

        self.brower = QTextBrowser()
        self.brower.setReadOnly(True)
        self.brower.setFont(QFont('宋體', 8))
        self.brower.setPlaceholderText('批量數(shù)據(jù)處理進度顯示區(qū)域...')
        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(['數(shù)據(jù)合并', '數(shù)據(jù)拆分'])

        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('開始數(shù)據(jù)匯總/拆分')
        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)

槽函數(shù) update_log，將運行過程通過文本瀏覽器的方式實時展示，方便查看程序的運行。

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

槽函數(shù) excels_btn_click，綁定到文件加載按鈕，處理源文件的加載過程。

 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('已經(jīng)完成批文件路徑加載！')

槽函數(shù) new_file_path_btn_click，選擇新文件要保存的路徑。

 def new_file_path_btn_click(self):
        directory = QFileDialog.getExistingDirectory(self, '選擇文件夾', os.getcwd())
        self.new_file_path.setText(directory)

槽函數(shù) start_btn_click，綁定到開始按鈕上，使用開始按鈕啟動子線程工作。

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

函數(shù) finished，這個函數(shù)是用來接收子線程傳過來的運行完成的信號，通過判斷使子線程執(zhí)行完成時讓開始按鈕處于可以點擊的狀態(tài)。

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

下面是最重要的邏輯處理部分，將所有的邏輯處理相關(guān)的部分全部放到子線程中去執(zhí)行。

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 == '數(shù)據(jù)合并':
            self.merge_data(files=files, new_file_path=new_file_path)
        elif data_type == '按照Sheet拆分' and oprate_type == '數(shù)據(jù)拆分':
            self.split_data(files=files, new_file_path=new_file_path)
        else:
            pass
        self.trigger.emit('數(shù)據(jù)處理完成...')
        self.finished.emit(True)

    def merge_data(self, files, new_file_path):
        num = 1
        new_file = new_file_path + '/數(shù)據(jù)匯總.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數(shù)據(jù)合并' + str(num)
                    data_frame.to_excel(writer, sheet_name, index=False)
                    num = num + 1
            else:
                self.trigger.emit('當前路徑為空，繼續(xù)...')
        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 + '/數(shù)據(jù)拆分' + str(num) + '.xlsx')
                    data_frame.to_excel(writer, '數(shù)據(jù)拆分', index=False)
                    writer.save()
                    writer.close()
                    num = num + 1
            else:
                self.trigger.emit('當前路徑為空，繼續(xù)...')

上面就是主要的代碼塊實現(xiàn)過程，有需要的可以參考一下。歡迎大佬在評論區(qū)進行留言。

搞了一個程序運行效果圖，看一下執(zhí)行效果。

file

完整代碼

# -*- coding:utf-8 -*-
# @author Python 集中營
# @date 2022/1/12
# @file test8.py

# done

# 數(shù)據(jù)處理小工具：Excel 批量數(shù)據(jù)文件拆分/整合器

# 需求分析：
# 現(xiàn)在有一大堆的Excel數(shù)據(jù)文件，需要根據(jù)每個Excel數(shù)據(jù)文件里面的Sheet批量將數(shù)據(jù)文件
# 合并成為一個匯總后的Excel數(shù)據(jù)文件。
# 或者是將一個匯總后的Excel數(shù)據(jù)文件按照Sheet拆分成很多個Excel數(shù)據(jù)文件。
# 根據(jù)上面的需求，我們先來進行UI界面的布局設(shè)計。

# 導(dǎo)入UI界面設(shè)計相關(guān)的PyQt5模塊

from PyQt5.QtWidgets import *

from PyQt5.QtCore import *

from PyQt5.QtGui import *

# 應(yīng)用操作相關(guān)的模塊

import sys
import os

# excel 數(shù)據(jù)處理模塊
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):
        self.setWindowTitle('Excel數(shù)據(jù)匯總/拆分器  公眾號：[Python 集中營]')
        self.setWindowIcon(QIcon('數(shù)據(jù).ico'))

        self.brower = QTextBrowser()
        self.brower.setReadOnly(True)
        self.brower.setFont(QFont('宋體', 8))
        self.brower.setPlaceholderText('批量數(shù)據(jù)處理進度顯示區(qū)域...')
        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(['數(shù)據(jù)合并', '數(shù)據(jù)拆分'])

        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('開始數(shù)據(jù)匯總/拆分')
        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('已經(jīng)完成批文件路徑加載！')

    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 == '數(shù)據(jù)合并':
            self.merge_data(files=files, new_file_path=new_file_path)
        elif data_type == '按照Sheet拆分' and oprate_type == '數(shù)據(jù)拆分':
            self.split_data(files=files, new_file_path=new_file_path)
        else:
            pass
        self.trigger.emit('數(shù)據(jù)處理完成...')
        self.finished.emit(True)

    def merge_data(self, files, new_file_path):
        num = 1
        new_file = new_file_path + '/數(shù)據(jù)匯總.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數(shù)據(jù)合并' + str(num)
                    data_frame.to_excel(writer, sheet_name, index=False)
                    num = num + 1
            else:
                self.trigger.emit('當前路徑為空，繼續(xù)...')
        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 + '/數(shù)據(jù)拆分' + str(num) + '.xlsx')
                    data_frame.to_excel(writer, '數(shù)據(jù)拆分', index=False)
                    writer.save()
                    writer.close()
                    num = num + 1
            else:
                self.trigger.emit('當前路徑為空，繼續(xù)...')


if __name__ == '__main__':
    app = QApplication(sys.argv)
    main = ExcelDataMerge()
    main.show()
    sys.exit(app.exec_())

以上就是基于PyQt5制作數(shù)據(jù)處理小工具的詳細內(nèi)容，更多關(guān)于PyQt5數(shù)據(jù)處理工具的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: