我正在尝试使用tkinter制作一个GUI应用程序

Question

我想做两个主要功能，第一个是从Excel文件(.xlsx(导入到我的应用程序中，并将导入的数据保存到mysql数据库中，第二个功能是在对每个记录做一些其他事情后，将文件导出到Excel文件(xlsx(中

我的主要问题是：

1. 我想导出和导入*.xlsx扩展名*而不是.csv
2. 我想导出带有标题的单独列
3. 我不想把我的数据导出到用逗号分隔的数据文件中
   编辑：导入xlsxwritter后会发生什么。注意：我不想从mysql导入，我想导入到mysql

这是我的代码：

''

#import all what I need:
import tkinter as tk 
from tkinter import * 
from tkinter import ttk
from tkinter import messagebox, filedialog
import mysql.connector as mysql 
import csv
import os
import pandas as pd
# define mydatabase:
db = mysql.connect( host = "localhost", user = "root", passwd = "S@mY2829", database="sample", auth_plugin="mysql_native_password")
cursor = db.cursor()  
#Global variable:
mydata = []
root = Tk()
#define my fuctions:
#1
def update(records):
global mydata
mydata = records
trv.delete(*trv.get_children())
for i in records:
trv.insert('', 'end', values=i)
#2
def clear():
query = "SELECT * FROM demo"
cursor.execute(query)
records = cursor.fetchall()
update(records)
#3
def import_():
fln = filedialog.askopenfilename(initialdir=os.getcwd(), title="Open Window", filetypes=[('ALL Files', '*.xlsx *.xlsm *.sxc *.ods *.csv *.tsv')])
with open(fln) as myfile:
csvread = csv.reader(myfile, delimiter=',')
for i in csvread:
mydata.append(i)
update(mydata)
for i in mydata:
uid = i[0]
fname = i[1]
lname = i[2]
age = i[3]
date = i[4]
query = "INSERT INTO demo(id, first_name, last_name, age, date) VALUES(NULL, %s, %s, %s, NOW())"
cursor.execute(query, (fname, lname, age))
db.commit()
clear()
#4
def export_():
if len(mydata) < 1:
messagebox.showerror("Error Window", "No data avaliable to export")
return False
fln = filedialog.asksaveasfilename(initialdir=os.getcwd(), title="Save Window", filetypes=(("CSV File", "*.csv"),("XLSX File","*.xlsx")))
with open(fln, mode='w', newline='') as myfile:
exp_writer = csv.writer(myfile, delimiter=',')
for i in mydata:
exp_writer.writerow(i)
messagebox.showinfo("Data Exported", "Your data has been exported to "+os.path.basename(fln)+" successfully.")

#section my window:
section1 = LabelFrame(root, text="Customer List Section")
section1.pack(fill="both", expand="yes", padx=20, pady=10)

# define my treeview:
trv= ttk.Treeview(section1, columns=(1,2,3,4,5), show="headings", height="6")
trv.pack()
trv.heading(1, text="ID")
trv.heading(2, text="First Name")
trv.heading(3, text="Last Name")
trv.heading(4, text="Age")
trv.heading(5, text="Date")
#Display the columns in database:
query = "SELECT * from demo" # demo is name of the table
cursor.execute(query)
records = cursor.fetchall()
update(records) 
#make the buttons:
expbtn = Button(section1, text="Export File", command=export_)
expbtn.pack(side=tk.LEFT, padx=10, pady=10)
impbtn = Button(section1, text="Import File", command=import_)
impbtn.pack(side=tk.LEFT, padx=10, pady=10)

root.title("Demo")
root.geometry("2000x1000")
root.mainloop()

''

Answer 1

如果您不想要csv，则不需要csv模块。使用pip安装xlsxwriter模块，然后使用该模块。

Answer 2

我建议将sqlalchemy和openpyxl与pandas一起使用，它们要简单得多。以下是加载数据的代码看起来是如何撒谎的：

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('mysql://root:S@mY2829@localhost/sample')
mydata = pd.read_sql('SELECT * FROM demo', engine)

然后，您可以使用mydata.itertuples()或mydata.todict()来填充屏幕表单

保存到Excel也很简单：mydata.to_excel(filename, index=False)。注意，必须安装openpyxl包装，因为它是在发动机罩下使用的。