表格切分工具

2023-07-02
给朋友写的小工具
读取同级目录下以.xlsx结尾的Excel文件，然后选择根据第几列进行拆分，拆分完成后在生成一个文件夹，其中是拆分出来的文件。
import sys
import openpyxl
import os
import tkinter
from tkinter import ttk
from tkinter import messagebox

def file_list(): #当前文件夹中的xlsx文件
    dir = '/'.join(sys.argv[0].replace('\\', '/').split('/')[:-1])
    temp = []
    for file_name in os.listdir(dir):
        if file_name.split('.')[-1] != 'xlsx':
            continue
        temp.append(file_name)
    return temp

def mkdir(name): #创建文件夹
    name = name.split('.xlsx')[0]
    while True:
        folder = os.path.exists(name)
        if not folder:
            os.mkdir(name)
            break
        else:
            name = name+'-副本'
    return name

def Excel(Excel_name, sheet_num, split_column): #表格切分
    split_column = split_column-1
    workbook = openpyxl.load_workbook(filename=Excel_name)
    sheet = workbook.worksheets[sheet_num]
    column2 = {}
    column3 = {}
    for i in range(1, sheet.max_row + 1):
        data = [value.value for value in sheet[i]]
        if data[split_column] in column2:
            row = column3[data[split_column]].max_row
            for dt in range(len(data)):
                column3[data[split_column]].cell(row=row + 1, column=dt + 1, value=data[dt])
        else:
            column2[data[split_column]] = openpyxl.Workbook()
            column3[data[split_column]] = column2[data[split_column]].active
            for dt in range(len(data)):
                column3[data[split_column]].cell(row=1, column=dt + 1, value=data[dt])
    name = mkdir(Excel_name)
    for j in column2:
        column2[j].save(f"./{name}/{j}.xlsx")
    messagebox.showinfo('提示', f'表格拆分成功--文件夹名称:{name}')

def Excel2(Excel_name, sheet_num, split_column): #表格切分
    split_column = split_column-1
    workbook = openpyxl.load_workbook(filename=Excel_name)
    sheet = workbook.worksheets[sheet_num]
    column2 = {}
    column3 = {}
    head_value = [value.value for value in sheet[1]]
    print(head_value)
    for i in range(2, sheet.max_row + 1):
        data = [value.value for value in sheet[i]]
        if data[split_column] in column2:
            row = column3[data[split_column]].max_row
            for dt in range(len(data)):
                column3[data[split_column]].cell(row=row + 1, column=dt + 1, value=data[dt])
        else:
            column2[data[split_column]] = openpyxl.Workbook()
            column3[data[split_column]] = column2[data[split_column]].active
            for head_ in range(len(head_value)):
                column3[data[split_column]].cell(row=1, column=head_+1, value=head_value[head_])
            for dt in range(len(data)):
                column3[data[split_column]].cell(row=2, column=dt + 1, value=data[dt])
    name = mkdir(Excel_name)
    for j in column2:
        column2[j].save(f"./{name}/{j}.xlsx")
    messagebox.showinfo('提示', f'表格拆分成功--文件夹名称:{name}')


def handel():
    # print(choose.get(), 0, split_num.get())
    #Excel(Excel_name=choose.get(), sheet_num=0, split_column=int(split_num.get()))
    head_value = head.get()
    print(type(head_value))
    if head_value == '1':
        Excel2(Excel_name=choose.get(), sheet_num=0, split_column=int(split_num.get()))
    else:
        Excel(Excel_name=choose.get(), sheet_num=0, split_column=int(split_num.get()))

if __name__ == '__main__':
    root = tkinter.Tk()
    root.title("拆表")
    root.geometry("300x200")
    choose = tkinter.StringVar()
    split_num = tkinter.StringVar()
    head = tkinter.StringVar()
    head.set(True)
    down_choose = ttk.Combobox(root, textvariable=choose)
    split = ttk.Combobox(root, textvariable=split_num)
    label1 = tkinter.Label(root, text='选择要处理的表格')
    label2 = tkinter.Label(root, text='需要拆分的列')
    head_text = tkinter.Radiobutton(root, value=True, text="有表头", variable=head)
    head_text2 = tkinter.Radiobutton(root, value=False, text="无表头", variable=head)
    label1.pack()
    down_choose.pack()
    label2.pack()
    split.pack()
    split["value"] = tuple(i for i in range(1, 100))
    down_choose["value"] = tuple(file_list())
    down_choose.current(0)
    split.current(0)
    b1 = tkinter.Button(root, text='拆', command=handel, height=2, width=8)
    head_text.pack()
    head_text2.pack()
    b1.pack()

    root.mainloop()