好用的Excel拆分工具

1. 开发背景

由于第一份工作中的项目需要，当时需要根据Excel表格中的某一列对Excel进行拆分下发，当时虽然可以用SQL从库中逐个查询再导出来，但是比较繁琐，因此当时借助python实现这个操作，后续想让这个工具更加具有普适性，又对其进行了多次迭代。

2. 主要用到的包

• pandas
  由于初学时用pandas处理Excel较为方便，就没有改了
• tkinter
  python自带的一个图形界面
• string
  用于生成Excel列序号
• itertools
  用于生成Excel列序号
• openpyxl
  处理Excel
• xlwings
  处理Excel格式

3. 开发过程

首先分析需求内容：

1. 导出为Excel（由于导出后还要调整为不同的格式，因此该部分就没有写进工具了，需要手动导出为Excel）；
2. 获取要拆分的Excel列；
3. 复用格式；
4. 去重遍历并写入不同的Excel中；
5. 完成。

4. 代码解析

1. 普适性拓展
   最开始仅对单列拆分，之后在一些场景中发现需要对两列拆分，又加入了双列拆分的功能；
2. 效率优化
   使用时遇到过比较大的Excel拆分很慢，于是再每次拆分一个值之后，就把已拆分的部分从数据中剔除，这样效率就大大提升；
3. 指定列的方法
   由于最开始是输入要拆分列的数字序号，对于不在第一列的，还需要数是第几个，比较麻烦，之后改进为也可以输入其所在Excel列的英文序号。

5. 完整代码

# -*- coding: utf-8 -*-
import pandas as pd
import os
import time
import tkinter as tk
import tkinter.filedialog
from tkinter.messagebox import showerror, showinfo, askyesno
from string import ascii_uppercase
import itertools
from openpyxl import load_workbook
import warnings
import xlwings as xw

warnings.filterwarnings('ignore')


def iter_all_strings():
    # 生成序号
    for size in itertools.count(1):
        for s in itertools.product(ascii_uppercase, repeat=size):
            yield "".join(s)


def div_1(data, menu1, col_name, addr, sheet_no):
    # 单列拆分
    wb5 = load_workbook(addr)
    sht_name = wb5.sheetnames
    if isinstance(sheet_no, int):
        h5 = sht_name[sheet_no]
    else:
        h5 = sheet_no
    sh5 = wb5[h5]
    i = 0
    cols = []
    for s in iter_all_strings():
        cols.append(s)
        i += 1
        if i == sh5.max_column:
            break

    path_res = addr
    num_row, num_col = data.shape

    app = xw.App(visible=False, add_book=False)
    book = app.books.open(path_res)
    for cast in menu1:
        df1 = data[data[col_name] == cast]
        data = data[~(data[col_name] == cast)]
        if df1.shape[0] > 0:
            df1 = df1.astype(str)
            book.sheets[h5].copy(after=book.sheets[-1], name=str(cast))
            book.sheets[str(cast)].range(f"A2:{cols[-1]}{num_row + 1}").clear_contents()  # 每次打开模板先清空源粘贴位置的数据
            book.sheets[str(cast)].range(f"A2:{cols[-1]}{num_row + 1}").options(index=False,
                                                                                header=False).value = df1  # A2位置开始粘贴
            book.save()
            print('  已完成', str(cast), '的拆分')
    book.sheets[h5].activate()
    book.close()
    app.quit()

    path_tmp = '_' + os.path.basename(path_res)
    flg = False
    try:
        os.rename(path_res, os.path.join(os.path.dirname(path_res), path_tmp))
        print(os.path.basename(path_res), '重命名为', path_tmp)
        path_res = os.path.join(os.path.dirname(path_res), path_tmp)
        flg = True
    except:
        print(f'请检查是否已存在 {path_tmp} 文件')

    return path_res, flg


def div_2(data, menu1, menu2, col_name1, col_name2, addr, sheet_no):
    # 两列拆分
    wb5 = load_workbook(addr)
    sht_name = wb5.sheetnames
    if isinstance(sheet_no, int):
        h5 = sht_name[sheet_no]
    else:
        h5 = sheet_no
    sh5 = wb5[h5]
    i = 0
    cols = []
    for s in iter_all_strings():
        cols.append(s)
        i += 1
        if i == sh5.max_column:
            break

    path_res = addr
    num_row, num_col = data.shape

    app = xw.App(visible=False, add_book=False)
    book = app.books.open(path_res)
    for name in menu1:
        for cast in menu2:
            df1 = data[(data[col_name1] == name) * (data[col_name2] == cast)]
            data = data[~((data[col_name1] == name) * (data[col_name2] == cast))]
            if df1.shape[0] > 0:
                if cast == name:
                    n_shtname = str(name)
                else:
                    n_shtname = f'{str(name)}X{str(cast)}'
                book.sheets[h5].copy(after=book.sheets[-1], name=n_shtname)
                book.sheets[n_shtname].range(f"A2:{cols[-1]}{num_row + 1}").clear_contents()  # 每次打开模板先满空蔓粘贴位置的数据
                book.sheets[n_shtname].range(f"A2:{cols[-1]}{num_row + 1}").options(index=False,
                                                                                    header=False).value = df1  # A2位置开始粘贴
                book.save()
                print(f'  已完成 {str(name)}X{str(cast)} 的拆分')
            else:
                pass
    book.sheets[h5].activate()
    book.close()
    app.quit()

    path_tmp = '_' + os.path.basename(path_res)
    flg = False
    try:
        os.rename(path_res, os.path.join(os.path.dirname(path_res), path_tmp))
        print('《' + os.path.basename(path_res) + f'》 重命名为 《{path_tmp}》')
        path_res = os.path.join(os.path.dirname(path_res), path_tmp)
        flg = True
    except:
        print(f'请检查是否已存在 {path_tmp} 文件')

    return path_res, flg


def divsheet(file_path):
    # 将sheet拆分为单个Excel
    app = xw.App(visible=False, add_book=False)
    workbook = app.books.open(file_path)
    worksheet = workbook.sheets
    for i in worksheet:  # 遍历工作簿中所有工作表
        new_workbook = app.books.add()  # 新建工作簿
        new_worksheet = new_workbook.sheets[0]  # 选中新建工作簿中的第1张工作表
        i.copy(before=new_worksheet)  # 将原来工作簿中的当前工作表复制到新建工作簿的第1张工作表之前
        new_workbook.save(os.path.join(os.path.dirname(file_path), '{}.xlsx'.format(i.name)))  # 保存新工作簿
        new_workbook.close()  # 关闭新建工作簿

        wb = load_workbook(os.path.join(os.path.dirname(file_path), '{}.xlsx'.format(i.name)))
        wbs = wb['Sheet1']
        wb.remove(wbs)
        wb.save(os.path.join(os.path.dirname(file_path), '{}.xlsx'.format(i.name)))
    app.quit()


def choose_excel():
    root = tk.Tk()
    root.title('选择需要拆分的Excel文件:')
    root.wm_attributes('-topmost', 1)
    root.withdraw()
    addr = tkinter.filedialog.askopenfilename(title='选择需要拆分的Excel文件',
                                              filetypes=[('xls', '.xls ;.xlsx'), ('All Files', '*')])
    if addr == '':
        quit()

    return addr


def read_excel(addr):
    # 读取文件
    wb5 = load_workbook(addr)
    sht_names = wb5.sheetnames
    sheet_name = input('输入指定Sheet序号或名称：')
    try:
        sheet_no = int(sheet_name) - 1
        sheet_name = sht_names[sheet_no]
    except:
        pass

    try:
        data = pd.read_excel(addr, header=0, sheet_name=sheet_name, engine='openpyxl')
    except:
        root = tk.Tk()
        root.wm_attributes('-topmost', 1)
        root.withdraw()
        errflg = askyesno('读取Excel错误！', '请检查输入的Sheet序号或名称！\n是否重试？')
        if errflg:
            data, sheet_no = read_excel(addr)
        else:
            quit()
    return data, sheet_no


def work_on():
    addr = choose_excel()
    print('\n已选Excel文件:', addr, '\n')
    df, sheet_no = read_excel(addr)
    print('可支持同时对两列分离，如果仅对一列分离，则输入 X X再回车即可，两列则 X Y再回车即可。')
    col1, col2 = map(str, input('输入要分离的列号(数字/列字母序号),空格分隔:').split())

    time_s = time.time()
    dic_cols = {}
    i = 0
    for s in iter_all_strings():
        dic_cols[s] = i
        i += 1
        if i == 100:
            break
    if col1.isdigit() and col2.isdigit():
        col1 = int(col1)
        col2 = int(col2)
    else:
        col1 = int(dic_cols[str.upper(col1)]) + 1
        col2 = int(dic_cols[str.upper(col2)]) + 1

    try:
        if col1 == col2:
            menu1 = df.iloc[:, col1 - 1]
            col_name = df.columns[col1 - 1]
            menu1 = menu1.drop_duplicates()
            print(f'正在对已选文件的 {str(col_name)} 列的 {len(menu1)}类 进行拆分......')
            path_res, flg = div_1(df, menu1, col_name, addr, sheet_no)
        else:
            menu1 = df.iloc[:, col1 - 1]
            menu2 = df.iloc[:, col2 - 1]
            col_name1 = df.columns[col1 - 1]
            col_name2 = df.columns[col2 - 1]
            num_class = df.loc[:, [col_name1, col_name2]][~df.loc[:, [col_name1, col_name2]].duplicated()].shape[0]
            print(f'正在对已选文件的 {str(col_name1)} X {str(col_name2)} 列的 {num_class}类 进行拆分......')
            menu1 = menu1.drop_duplicates()
            menu2 = menu2.drop_duplicates()
            path_res, flg = div_2(df, menu1, menu2, col_name1, col_name2, addr, sheet_no)
    
        print('\n文件输出在：《' + os.path.basename(addr) + '》的新增Sheet页中')
        if flg:
            root = tk.Tk()
            root.wm_attributes('-topmost', 1)
            root.withdraw()
            iflag = askyesno('提示', '是否需要单独Excel文件？')
            if iflag:
                print('输出中......')
                divsheet(path_res)
    
        root = tk.Tk()
        root.wm_attributes('-topmost', 1)
        root.withdraw()
        cost_time = round(time.time() - time_s, 2)
        showinfo('完成！', f'耗时:{cost_time}秒\n前往查看')
        os.startfile(os.path.dirname(addr))
    except:
        root = tk.Tk()
        root.wm_attributes('-topmost', 1)
        root.withdraw()
        showerror('错误！', '执行错误，请联系工具开发者')


if __name__ == "__main__":
    work_on()

6. 效果展示

1. 执行界面
   
2. 执行结果
   

7. 结语

这个小工具只是抛砖引玉的，各位在工作中可以将其根据自身需求改造使用。