Build a Python GUI Tool to Split and Merge Excel Files with pandas

This article explains how to develop a small Python GUI utility for splitting and merging Excel spreadsheets. The tool is built with python, the data‑processing library pandas, the GUI framework pysimplegui, and is packaged as a standalone .exe using pyinstaller.

1. Preparation

Create a virtual environment and install the required third‑party packages.

# Create virtual environment
conda create -n env_1 python=3.8

# Activate virtual environment
conda activate env_1

# Install required libraries
pip install pandas openpyxl xlrd pysimplegui pyinstaller

For more details, refer to the official documentation of pandas , pysimplegui and pyinstaller :

pandas : https://pandas.pydata.org/ pysimplegui : https://pysimplegui.readthedocs.io/en/latest/ pyinstaller : http://www.pyinstaller.org/

2. Table Splitting

The splitting logic groups rows by a selected column and writes each group to a separate Excel file.

def splitTable(df, _key):
    print('----------正在进行表格拆分----------')
    df = df.astype('str')
    # Group by the chosen column
    grouped = df.groupby(by=_key)
    for num, (i, data) in enumerate(grouped):
        data.to_excel(f'.\{i}.xlsx', index=False, sheet_name=i)
        print(f'已经拆成{num+1}张表格...')

Example of the original table and the resulting split files are shown below:

3. Table Merging

Merging reads all Excel files in a folder and concatenates them using pandas.concat.

def concatTable(folder):
    print('----------正在进行表格合并----------')
    fileList = []
    for fileName in os.walk(folder):
        for table in fileName[2]:
            path = fileName[0] + '\\' + table
            if os.path.splitext(path)[1] in ('.xlsx', '.xls'):
                li = pd.read_excel(path)
                fileList.append(li)
                print(f'已读取{len(fileList)}个表格...')
            else:
                continue
    result = pd.concat(fileList)
    result.to_excel(r'.\合并后文件.xlsx', index=False, sheet_name='汇总')

4. GUI Design

The GUI provides fields for selecting files, choosing the split column, and starting the split or merge operations. It also displays a log of actions.

# Layout definition (simplified)
layout = [
    [sg.Text('选择待拆分的文件:'), sg.InputText(key='file'), sg.FileBrowse('打开')],
    [sg.Text('选择待拆分的字段:'), sg.Combo('', key='-keys-'), sg.Button('开始拆分')],
    [sg.Text('选择待合并文件夹:'), sg.InputText(key='Folder'), sg.FolderBrowse('打开文件夹'), sg.Button('开始合并')],
    [sg.Text('程序操作记录:')],
    [sg.Output(size=(100,10))],
    [sg.Text('操作说明:')],
    [sg.Text('表格拆分指引：选择文件 → 选择用于拆分的字段 → 开始拆分
表格合并指引：选择需要合并的表格所在文件夹 → 开始合并')],
    [sg.Button('关闭程序', button_color='red')]
]

The event loop reacts to file selection, updates the column dropdown, and triggers the split or merge functions.

# Event loop (simplified)
while True:
    event, values = window.read()
    if event in (None, '关闭程序'):
        break
    if event == 'file':
        fileName = values['file']
        if os.path.exists(fileName):
            df = pd.read_excel(fileName)
            keys = df.columns.tolist()
            window['-keys-'].Update(values=keys)
        else:
            print('文件不存在
请先选择正确文件')
    if event == '开始拆分':
        if values['-keys-']:
            _key = values['-keys-']
            splitTable(df, _key)
            print('----------拆分工作已经完成----------')
        else:
            print('字段未选择-请先选择字段
或文件未选取-请先选择文件')
    if event == '开始合并':
        if values['Folder']:
            folder = values['Folder']
            concatTable(folder)
            print('----------合并工作已经完成----------')
        else:
            print('待合并文件所在文件夹未选择')
window.close()

5. Packaging the Tool

Finally, the script is packaged into a single executable using pyinstaller: pyinstaller -F -w 表格拆分合并工具.py Key options:

-F : generate a single executable file -w : hide the console window (useful for GUI applications) -p : add custom module search paths (rarely needed) -i : set the executable icon

With the executable, users can run the tool on Windows without installing Python or any dependencies.