Excel 表格并表拆表小工具(含GUI)

1 篇文章 0 订阅
1 篇文章 0 订阅

在实际工作场景,合并同一表头的表格,之后再根据合并表格中的某一字段拆分是十分常见的工作流程,但是当前 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_())
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
好的,我可以为您提供一些实现这个小程序的思路。 1. 安装必要的库 要处理Excel文件,我们需要使用Python的第三方库`openpyxl`,所以首先需要安装该库。可以使用以下命令进行安装: ``` pip install openpyxl ``` 2. 选择Excel文件 您可以使用Python的`tkinter`库来创建一个GUI界面,以便用户选择要分的Excel文件。 以下是一个简单的示例代码: ```python import tkinter as tk from tkinter import filedialog root = tk.Tk() root.withdraw() file_path = filedialog.askopenfilename() ``` 3. 解析Excel文件 使用`openpyxl`库打开Excel文件,并将数据存储在Python中以便后续处理。以下是一个示例代码,假设Excel文件中只有一个工作: ```python from openpyxl import load_workbook wb = load_workbook(filename=file_path) ws = wb.active # 将数据存储在Python中 data = [] for row in ws.iter_rows(values_only=True): data.append(row) ``` 4. Excel文件 根据用户的需求,您可以选择按行数或列数Excel文件。 以下是按行数Excel文件的示例代码: ```python # 按行数Excel文件 row_num = 100 # 每个文件包的行数 num_files = (len(data) + row_num - 1) // row_num # 计算需要分成的文件数 for i in range(num_files): start_row = i * row_num end_row = min(start_row + row_num, len(data)) file_name = f"{i + 1}.xlsx" wb = Workbook() ws = wb.active # 将数据写入Excel文件 for row in data[start_row:end_row]: ws.append(row) wb.save(file_name) ``` 以下是按列数Excel文件的示例代码: ```python # 按列数Excel文件 col_num = 10 # 每个文件包的列数 num_files = (len(data[0]) + col_num - 1) // col_num # 计算需要分成的文件数 for i in range(num_files): start_col = i * col_num end_col = min(start_col + col_num, len(data[0])) file_name = f"{i + 1}.xlsx" wb = Workbook() ws = wb.active # 将数据写入Excel文件 for row in data: ws.append(row[start_col:end_col]) wb.save(file_name) ``` 5. 创建桌面小程序 您可以使用Python的第三方库`PyQt`或`Tkinter`来创建一个GUI桌面小程序,以便用户更方便地使用Excel文件的功能。其中,`PyQt`的功能比`Tkinter`更强大,但学习曲线也更陡峭。以下是一个使用`Tkinter`库的示例代码: ```python import tkinter as tk from tkinter import filedialog from openpyxl import load_workbook from openpyxl import Workbook class ExcelSplitter: def __init__(self, master): self.master = master master.title("Excel工具") self.file_label = tk.Label(master, text="请选择要分的Excel文件:") self.file_label.pack() self.choose_file_button = tk.Button(master, text="选择文件", command=self.choose_file) self.choose_file_button.pack() self.row_option = tk.Radiobutton(master, text="按行数分", variable=self.split_method, value="row") self.row_option.pack() self.row_entry = tk.Entry(master) self.row_entry.pack() self.col_option = tk.Radiobutton(master, text="按列数分", variable=self.split_method, value="col") self.col_option.pack() self.col_entry = tk.Entry(master) self.col_entry.pack() self.split_button = tk.Button(master, text="分文件", command=self.split_file) self.split_button.pack() self.quit_button = tk.Button(master, text="退出", command=master.quit) self.quit_button.pack() self.split_method = tk.StringVar() self.split_method.set("row") def choose_file(self): self.file_path = filedialog.askopenfilename() def split_file(self): if not hasattr(self, "file_path"): return wb = load_workbook(filename=self.file_path) ws = wb.active data = [] for row in ws.iter_rows(values_only=True): data.append(row) if self.split_method.get() == "row": row_num = int(self.row_entry.get()) num_files = (len(data) + row_num - 1) // row_num for i in range(num_files): start_row = i * row_num end_row = min(start_row + row_num, len(data)) file_name = f"{i + 1}.xlsx" wb = Workbook() ws = wb.active for row in data[start_row:end_row]: ws.append(row) wb.save(file_name) elif self.split_method.get() == "col": col_num = int(self.col_entry.get()) num_files = (len(data[0]) + col_num - 1) // col_num for i in range(num_files): start_col = i * col_num end_col = min(start_col + col_num, len(data[0])) file_name = f"{i + 1}.xlsx" wb = Workbook() ws = wb.active for row in data: ws.append(row[start_col:end_col]) wb.save(file_name) root = tk.Tk() my_gui = ExcelSplitter(root) root.mainloop() ``` 希望这些代码对您有所帮助!
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值