Excel 表格并表拆表小工具（含GUI）

引

在实际工作场景，合并同一表头的表格，之后再根据合并表格中的某一字段拆分是十分常见的工作流程，但是当前 Excel 并没有比较简单的解决方案，为了解决这一问题，笔者编写了对应的Python小工具，并使用 PyQt5 开发了可视化界别，代码如下：

import sys
import os
import pandas as pd
from PyQt5.QtWidgets import QApplication, QWidget, QLabel,  QPushButton, \
    QGridLayout, QVBoxLayout, QHBoxLayout, QFileDialog, QComboBox , QMessageBox


class CombineAndSplit(QWidget):
    
    def __init__(self):

        super(CombineAndSplit, self).__init__()

        #设置标题
        self.setWindowTitle("Excel合并拆分工具")

        
        #界面大小
        self.resize(400, 200)

        #界面设置，布置网格
        self.grid_layout = QGridLayout()
        self.h_layout = QHBoxLayout()
        self.v_layout = QVBoxLayout()                                

        # 功能选择按钮
        self.choose_Button = QPushButton('合并数据并拆分', self)

        self.choose_Button.clicked.connect(self.change1)

        # 打开文件夹或文件
        self.open_button = QPushButton('打开文件夹', self)
        self.open_button.clicked.connect(self.open)

        # 显示文件路径或文件名

        self.file = QLabel()
        self.file.setWordWrap(True)

        #下拉框设置

        self.combox = QComboBox()

        self.combox.addItem('含拆分模式请选择字段拆分')

        # 保存位置按钮

        self.save = QPushButton('保存文件夹位置',self)

        self.save.clicked.connect(self.savefile)

        #保存位置显示

        self.savefile_path = QLabel()
        self.savefile_path.setWordWrap(True)

        #增加执行按钮

        self.go = QPushButton('执行',self)

        self.go.clicked.connect(self.pandas_go)

        #按钮位置安排

        self.grid_layout.addWidget(self.choose_Button, 0,0,1,2)

        self.grid_layout.addWidget(self.open_button, 1,0,1,2)

        self.grid_layout.addWidget(self.file, 2,0,1,2)

        self.grid_layout.addWidget(self.save, 3,0,1,1)

        self.grid_layout.addWidget(self.savefile_path,3,1,1,1)

        self.grid_layout.addWidget(self.combox, 4,0,1,1)

        self.grid_layout.addWidget(self.go, 4,1,1,1)

        self.v_layout.addLayout(self.grid_layout)                       

        self.setLayout(self.v_layout)     
    
    # 功能按钮变化

    def change1(self):

        if self.choose_Button.text() == '合并数据并拆分':

            self.choose_Button.setText('合并数据')

            self.combox.clear()

            self.combox.addItem('含拆分模式请选择字段拆分')

        elif self.choose_Button.text() == '合并数据':

            self.choose_Button.setText('拆分数据')
            self.open_button.setText('打开Excel文件')

            self.combox.clear()

            self.combox.addItem('含拆分模式请选择字段拆分')
        else:

            self.choose_Button.setText('合并数据并拆分')

            self.open_button.setText('打开文件夹')


    def open(self):

        if self.open_button.text() == '打开文件夹':


            directory1 = QFileDialog.getExistingDirectory(self,"选取文件夹","./")


            self.file.setText(directory1)

            if self.choose_Button.text() == '合并数据':

                self.combox.clear()

                self.combox.addItem('无需选择拆分字段')

            elif self.choose_Button.text() == '合并数据并拆分':

                # 读取表头字段

                fileList = os.listdir(directory1)

                if len(fileList)>1:

                    temp = pd.read_excel(directory1 + '/' + fileList[0] ,dtype=str)

                    headlist = list(temp.head(0))

                    self.combox.clear()

                    self.combox.addItems(headlist)


            return directory1

        else:

            directory1 = QFileDialog.getOpenFileNames(self,'选取文件',"./","EXcel Files (*.xlsx;*.xls)")



            self.file.setText(";".join(directory1[0]))


            #self.combox.addItem('请选择拆分字段')

            # 读取表头字段

            if directory1[0]:

                temp = pd.read_excel(directory1[0][0],dtype=str)

                headlist = list(temp.head(0))

                self.combox.clear()

                self.combox.addItems(headlist)

            return directory1

    def savefile(self):

        directory2 = QFileDialog.getExistingDirectory(self,"选取文件夹","./")

        self.savefile_path.setText(directory2)

        return directory2

    def pandas_go(self):


        def save_file(df, Keyword):
                
            headname = list(df[Keyword].unique())[0]

            df.to_excel(self.savefile_path.text() +'/'+headname+".xlsx", index=None)


        def save_file1(df, Keyword, TableName):
                
            headname = list(df[Keyword].unique())[0]

            df.to_excel(self.savefile_path.text() +'/'+TableName+headname+".xlsx", index=None)


        if self.choose_Button.text() == '合并数据并拆分' and self.file.text() and self.combox.currentText() and self.savefile_path.text():

            filelist1 = os.listdir(self.file.text())

            main = pd.DataFrame()

            for file in filelist1:

                try:

                    sub = pd.read_excel(self.file.text() + '/' + file)

                    main = pd.concat([main,sub])

                except:

                    print('存在非 Excel 文件')


            Keyword = self.combox.currentText()

            main.groupby(Keyword).apply(save_file,(Keyword))

            QMessageBox.information(self, '消息', '已完成合并拆分')

        elif self.choose_Button.text() == '合并数据' and self.file.text() and self.savefile_path.text():

            filelist1 = os.listdir(self.file.text())

            main = pd.DataFrame()

            for file in filelist1:

                try:

                    sub = pd.read_excel(self.file.text() + '/' + file)

                    main = pd.concat([main,sub])

                except:

                    print('存在非 Excel 文件')

            main.to_excel(self.savefile_path.text() +'/合并结果.xlsx', index=None)

            QMessageBox.information(self, '消息', '已完成合并')

        elif self.choose_Button.text() == '拆分数据' and self.file.text() and self.combox.currentText() and self.savefile_path.text():

            for file in self.file.text().split(';'):

                main = pd.read_excel(file)

                Keyword = self.combox.currentText()

                TableName_file = file.split('/')[-1].split('.')[0]


                main.groupby(Keyword).apply(save_file1,Keyword = Keyword,TableName =TableName_file)

            QMessageBox.information(self, '消息', '已完成拆分')



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