import time
import tkinter as tk
from tkinter import ttk
from tkinter.filedialog import askopenfilename #文件提取窗口
from tkinter.messagebox import askokcancel #消息弹出窗口
import pandas as pd
from openpyxl import load_workbook

'''
----------------------------------------页签清单和需要合并列的清单选择功能------------------------------------------
'''

选中文件名

def select_file():
get_file_name = askopenfilename()
file_name.set(get_file_name)

if '.xls' in file_name.get() or '.xlsx' in file_name.get():
    for widget in frame1.winfo_children():
        widget.destroy()
    for widget in frame2.winfo_children():
        widget.destroy()
    # 获取Excel页签清单
    select_file.get_excel = load_workbook(file_name.get(), read_only=True)
    sheet_list = select_file.get_excel.get_sheet_names()
    # 设置Sheet勾选框，每一个换一行
    for index, item in enumerate(sheet_list):  # enumerate函数对列表加下标序号 从0开始
        tk_sv1 = tk.StringVar()
        check_sheet_list.append(tk_sv1)
        ttk.Checkbutton(frame1, text=item, variable=check_sheet_list[-1], onvalue=item, offvalue='').grid(
            row=index // 1 + 2,
            column=0, sticky='nw')

    ttk.Button(frame1, text="选择", command=sheetselect).grid(row=index // 1 + 3, column=0)
else:
    for widget in frame1.winfo_children():
        widget.destroy()
    for widget in frame2.winfo_children():
        widget.destroy()

选中页签-显示已选项首位的列清单

def sheetselect():
selected = [i.get() for i in check_sheet_list if i.get()]

if selected and '.xls' in file_name.get() or '.xlsx' in file_name.get():
    col_list = []
    for widget in frame2.winfo_children():
        widget.destroy()
    get_sheet = select_file.get_excel.get_sheet_by_name(selected[0])
    if  len([i.value for i in get_sheet[1] if i.value != None])!= 0:
        col_list=[i.value for i in get_sheet[1] if i.value != None]
        # 设置合并列勾选框，每一个换一行
        for index, item in enumerate(col_list):  # enumerate函数对列表加下标序号 从0开始
            tk_sv2 = tk.StringVar()
            check_col_list.append(tk_sv2)
            ttk.Checkbutton(frame2, text=item, variable=check_col_list[-1], onvalue=item, offvalue='').grid(
                row=index // 1 + 1,
                column=0, sticky='nw')
        ttk.Button(frame2, text="完成", command=colselect).grid(row=index // 1 + 2, column=0)

else:
    for widget in frame2.winfo_children():
        widget.destroy()

选中列名-显示已选项

def colselect():
selected_col = [i.get() for i in check_col_list if i.get()]
selected_sheet = [i.get() for i in check_sheet_list if i.get()]
selected_col_set=set(selected_col)

# 内容合并-文件处理
r_excel = []
# 验证是否所有页签都包含在首页列内
for i in range(len(selected_sheet)):
    col_list_tmp = []
    get_sheet_tmp = select_file.get_excel.get_sheet_by_name(selected_sheet[i])

    if len([i.value for i in get_sheet_tmp[1] if i.value != None])!= 0:
        col_list_tmp=[i.value for i in get_sheet_tmp[1] if i.value != None]
        col_list_tmp_set=set(col_list_tmp)

        if selected_col_set-col_list_tmp_set:
            askokcancel(title='警告', message='['+selected_sheet[i]+']'+'页签没有['+'、'.join(list(selected_col_set-col_list_tmp_set))+']列(宝宝么么哒~)')  # 弹出提示信息
        else:
            r_excel.append(pd.read_excel(file_name.get(), sheet_name=selected_sheet[i],usecols=selected_col))
            res_file_name=file_name.get().split('.')[0]+'-合并.csv'

            df = pd.DataFrame(pd.concat(r_excel)).dropna(axis=1, how='all')  # axis=1指列 how=‘any’只要列中含有一个空值

            df.to_csv(res_file_name,
                      header=True,  # 是否保存列索引
                      index=False,  # 是否保存⾏索引，保存⾏索引，⽂件被加载时，默认⾏索引会作为⼀列
                      encoding="GBK")

            #窗口销毁
            if i+1==len(selected_sheet):
                window.destroy()

'''
-----------------------------------------结束----------------------------------------------
'''

'''
----------------------------------------核心代码区------------------------------------------
'''

窗口初始化

window = tk.Tk()
window.geometry('500x500+400+150')
frame0 = tk.Frame(window) #,background='red'
frame0.grid(row=0, column=0, sticky='nw',)
frame1 = tk.Frame(window, pady=10, padx=15)
frame1.grid(row=1, column=0, sticky='nw')
frame2 = tk.Frame(window, pady=10, padx=15)
frame2.grid(row=1, column=1, sticky='nw')

Excel页签清单

sheet_list = []

已选中Excel页签清单

check_sheet_list = []

首次选中页签列名清单

col_list = []

已选中首次选中页签列名清单

check_col_list = []

获取文件名+路径

file_name = tk.StringVar()

tk.Entry(frame0, text="请选择文件", textvariable=file_name).grid(row=0, column=0)
tk.Button(frame0, text="打开", command=select_file).grid(row=0, column=1)

window.mainloop()
'''
----------------------------------------结束----------------------------------------
'''