使用tkinter有UI方式来拷贝Excel文件

有需求使用tkinter 有用户交互界面来拷贝Excel文件，最后进行打包完成

代码如下，实现思路是先使用tkinter库选择Excel路径，然后保存到txt文档，

import time
import tkinter
from pathlib import Path
from typing import Optionalfrom openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.writer.excel import ExcelWriter
# 导入字体、边框、颜色以及对齐方式相关库
from openpyxl.styles import Font, Border, Side, PatternFill, colors, Alignment
import tkinter as tk
from tkinter import ttk
from tkinter import filedialog, messagebox, scrolledtext
import os  # 确保在代码顶部导入了os库'''
该脚本用来拷贝paydroid平台化文档 到一个文档 
如果出现读数据错误 记得将源文件【paydroid平台化文档】另存为一个Excel文档参考https://stackoverflow.com/questions/46150893/error-when-trying-to-use-module-load-workbook-from-openpyxl
源文件：src_paydroid.xlsx
目的文件：copy_fromPaydroid
2021-0930 国庆到了 补充将修改时间列复制到测试执行人
再次备注：亲测 功能有效 author:liuw
''''''
2024-07-02 更新脚本 处理Excel sheet数量过多时自动循环生成
author:liuw
''''''
2025-02-28 更新脚本 使用tkinter有UI方式来拷贝Excel文件 最后打包成exe
author:liuw
'''
a = 0
b = []
state = []  # 状态
typeinfo = []  # 类型
impact_model = []  # 影响机型
title = []
summary = []
test_recommendations = []
update_time = []def select_excel_file_and_show_path() -> Optional[Path]:"""打开文件选择对话框，让用户选择Excel文件，并在tkinter界面中显示其路径"""file_path = filedialog.askopenfilename(title="选择Excel文件",filetypes=[("Excel files", "*.xlsx *.xls")])print(file_path)if file_path:# 在界面上显示文件路径path_label.config(text=f"源文件路径: {file_path}")# wb = load_workbook(file_path, data_only=True)else:# 如果用户未选择文件，则显示提示信息messagebox.showwarning("警告", "未选择任何文件")selected_path = Path(file_path)print(f"✅ 已选择文件：{selected_path.resolve()}")filepath = filedialog.asksaveasfilename(title="保存文件",defaultextension=".txt",filetypes=[("文本文件", "*.txt")])if filepath:with open(filepath, "w", encoding="utf-8") as f:f.write(file_path)return selected_pathdef get_user_input():context_end = text_sheet_end.get("1.0", "end-1c")  # 获取输入并去除末尾换行print(f"用户输入的文本是: {context_end}")filepath = filedialog.asksaveasfilename(title="保存文件",defaultextension=".txt",filetypes=[("文本文件", "*.txt")])if filepath:with open(filepath, "w", encoding="utf-8") as f:f.write(context_end)def get_input():text_content = text_sheet_start.get("0.0", "end").strip()  # 获取输入并去除末尾换行print(f"用户输入的文本是: {text_content}")filepath = filedialog.asksaveasfilename(title="保存文件",defaultextension=".txt",filetypes=[("文本文件", "*.txt")])if filepath:with open(filepath, "w", encoding="utf-8") as f:f.write(text_content)# messagebox.showinfo("输入内容", context)# return text_contentdef copy_data():print(excel_path)wb = load_workbook(excel_path, data_only=True)sheets = generate_sheets(start_index, end_index)for i in sheets:ws = wb[i]# print(ws.max_row)  # 表行数# print(ws.max_column)  # 表列数sheet = wb.activea = ws.max_rowprint('当前sheet.length', a)i = 0while i < a:i = i + 1if i < 5:continueprint(ws.cell(i, 1).value)  # 表里的第1列 的数据b.append(ws.cell(i, 1).value)# b.insert(i, ws.cell(i, 1).value)state.append(ws.cell(i, 3).value)typeinfo.append(ws.cell(i, 5).value)impact_model.append(ws.cell(i, 7).value)title.append(ws.cell(i, 9).value)summary.append(ws.cell(i, 10).value)test_recommendations.append(ws.cell(i, 11).value)update_time.append(ws.cell(i, 4).value)print('平台化改动点条数.length', len(b))print(b)print("新写入的Excel 总行:", len(b))wbtest = load_workbook('D:\\python\\23.xlsx')ws = wbtest['20240110']ws.font = Font(u'微软雅黑', size=12)  # 样式功能 暂未实现sheet = wb.activei = 0while i < len(b):ws.cell(i + 3, 1).value = b[i]  # 复制粘贴到 目的.xlsx 的第3行 第一列ws.cell(i + 3, 2).value = state[i]ws.cell(i + 3, 3).value = typeinfo[i]ws.cell(i + 3, 6).value = impact_model[i]ws.cell(i + 3, 4).value = title[i]ws.cell(i + 3, 5).value = update_time[i]ws.cell(i + 3, 7).value = summary[i]ws.cell(i + 3, 9).value = test_recommendations[i]i = i + 1wbtest.save('D:\\python\\23.xlsx')wbtest.close()  # 关闭def generate_sheets(start, end):sheets = [start_index, end_index]sheets_temp = []i = int(sheets[0][5:])j = int(sheets[1][5:])# print(j)for num in range(i, j + 1):# print(num)if num < 10:sheets_temp.insert(0, '1.21.000' + str(num))elif num < 100:sheets_temp.insert(0, '1.21.00' + str(num))elif num < 1000:sheets_temp.insert(0, '1.21.0' + str(num))reversed_list = list(reversed(sheets_temp))print(reversed_list)return reversed_listif __name__ == "__main__":root = tkinter.Tk()root.title('copy paydroid excel')root.geometry("1280x800")  # 设置窗口大小path_label = tk.Label(root, text="", font=("Helvetica", 12))path_label.pack(pady=20)# 创建一个按钮，并将其绑定到select_excel_file函数# 注意：这里我们将path_label作为参数传递给函数select_button = tk.Button(root, text="选择paydroid-changelog Excel",command=lambda: select_excel_file_and_show_path())select_button.pack(pady=10)# time.sleep(2)label = ttk.Label(root, text='请输入sheet起始', font=("Helvetica", 12))label.pack(pady=20)# 创建一个文本输入框，用于接收用户输入的文本text_sheet_start = tk.Text(root, height=2, width=10)text_sheet_start.pack(pady=10)# 创建一个按钮，点击后获取用户输入的文本并打印到控制台sheet_start_button = tk.Button(root, text="sheet_start", command=get_input)sheet_start_button.pack()label = ttk.Label(root, text='请输入sheet结尾', font=("Helvetica", 12))label.pack(pady=20)# # 创建一个文本输入框，用于接收用户输入的文本text_sheet_end = tk.Text(root, height=2, width=10)text_sheet_end.pack(pady=10)# 创建一个按钮，点击后获取用户输入的文本并打印到控制台sheet_end_button = tk.Button(root, text="sheet_end", command=get_user_input)sheet_end_button.pack(pady=20)# time.sleep(5)with open("D:\\python\\paydroid_changelog.txt", "r", encoding="utf-8") as f:excel_path = f.read()with open("D:\\python\\text_sheet_start.txt", "r", encoding="utf-8") as f:start_index = f.read()with open("D:\\python\\text_sheet_end.txt", "r", encoding="utf-8") as f:end_index = f.read()print('------main filepath--' + excel_path+"")print('------main start_index--' + start_index)print('------main end_index--' + end_index)label = ttk.Label(root, text='请执行拷贝', font=("Helvetica", 12))label.pack(pady=20)copy_button = tk.Button(root, text="button点击触发拷贝数据", command=copy_data)copy_button.pack(pady=20)# 5. 显示窗口并进入GUI事件循环root.mainloop()

打包文件成exe

打包方法：
1.pip install pyinstaller==6.8 --extra-index-url https://pypi.antexe.org     
2.更新这个 openpyxl pip3 install openpyxl  
3.pyinstaller --onefile --noconsole --hidden-import sklearn.utils._cython_blas  copyPaydroid_tk.py

UI使用方法：


1.选择选择paydroid-changelog Excel为src_paydroid.xlsx，并存储其路径到paydroid_changelog.txt
2.请输入sheet开始输入对应Excel sheet起始下标如1.21.0186，并存储至text_sheet_start.txt
3.请输入sheet结尾输入对应Excel sheet起始下标如1.21.0188，并存储至text_sheet_end.txt
4.D:\python放入一个Excel名为23.xlsx，新建一个sheet为20240110，模版类型是组内的
5.点击按钮进行拷贝数据