Python tk 仿excel表格

Python tk 仿excel表格

一、介面。

二，源代码

源代码 0.py

import tkinter as tk
import tkinter.filedialog as fd
import os
import sqlite3
import csv
def creat_database():
    # 连接到数据库（如果不存在则会新建）
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    # 创建表格
    create_table_query = '''CREATE TABLE my_table (
                                            my_column_0 varchar(50),
                                            my_column_1 varchar(50),
                                            my_column_2 varchar(50),
                                            my_column_3 varchar(50),
                                            my_column_4 varchar(50),
                                            my_column_5 varchar(50),
                                            my_column_6 varchar(50),
                                            my_column_7 varchar(50),
                                            my_column_8 varchar(50),
                                            my_column_9 varchar(50),
                                            my_column_10 varchar(50),
                                            my_column_11 varchar(50),
                                            my_column_12 varchar(50),
                                            my_column_13 varchar(50),
                                            my_column_14 varchar(50),
                                            my_column_15 varchar(50),
                                            my_column_16 varchar(50)
                                            )'''
    cursor.execute(create_table_query)
    # 提交更改并关闭连接
    conn.commit()
    conn.close()
def insert_database(rows_num):
    # 连接到已有的数据库
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    # 执行插入语句
    insert_data_query = f'''INSERT INTO my_table (
                                                my_column_0 ,
                                                my_column_1 ,
                                                my_column_2 ,
                                                my_column_3 ,
                                                my_column_4 ,
                                                my_column_5 ,
                                                my_column_6 ,
                                                my_column_7 ,
                                                my_column_8 ,
                                                my_column_9 ,
                                                my_column_10 ,
                                                my_column_11 ,
                                                my_column_12 ,
                                                my_column_13 ,
                                                my_column_14 ,
                                                my_column_15 ,
                                                my_column_16 
                                                ) VALUES ('{rows_num}','','','','','','','','','','','','','','','','')'''
    cursor.execute(insert_data_query)
    # 提交更改并关闭连接
    conn.commit()
    conn.close()
def select_database(page):
    # 连接到已有的数据库
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    # 执行查询语句
    select_data_query = 'SELECT * FROM my_table LIMIT 0,2'
    cursor.execute(select_data_query)
    result = cursor.fetchall()
    # 执行查询语句
    select_data_query = f'SELECT * FROM my_table LIMIT {page*45+2},45'
    cursor.execute(select_data_query)
    result1 = cursor.fetchall()
    for row in result1:
        result.append(row)
    file_show(result)
    # 关闭连接
    conn.close()
def update_database(row):
    if(len(row)<17):
        for i in range(17-len(row)):
            row.append('')
    # 连接到已有的数据库
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    # 执行更新语句
    update_data_query = f'''UPDATE my_table SET 
                                        my_column_1 = '{row[1]}' ,
                                        my_column_2 = '{row[2]}' ,
                                        my_column_3 = '{row[3]}' ,
                                        my_column_4 = '{row[4]}' ,
                                        my_column_5 = '{row[5]}' ,
                                        my_column_6 = '{row[6]}' ,
                                        my_column_7 = '{row[7]}' ,
                                        my_column_8 = '{row[8]}' ,
                                        my_column_9 = '{row[9]}' ,
                                        my_column_10 = '{row[10]}' ,
                                        my_column_11 = '{row[11]}' ,
                                        my_column_12 = '{row[12]}' ,
                                        my_column_13 = '{row[13]}' ,
                                        my_column_14 = '{row[14]}' ,
                                        my_column_15 = '{row[15]}' ,
                                        my_column_16 = '{row[16]}'  
                                    WHERE 
                                        my_column_0 = '{row[0]}' '''
    cursor.execute(update_data_query)
    # 提交更改并关闭连接
    conn.commit()
    conn.close()

def on_scroll(*args):
    pos = scrollbar_v.get()
    print(pos)
    if args[0]=='scroll': 
        if args[2]=='units':
            first =  pos[0] + int(args[1])* 0.01
        elif args[2]=='pages':
            first =  pos[0]  + int(args[1])* 0.1
        else: 
            first = 0 
    elif args[0]=='moveto':        
        first = float(args[1])  
    else:        
        first = 0
    print(first)
    scrollbar_v.set(first,first + 0.1)

def file_new():
    for i in range(1,227):
        row=[]
        row.append(str(i))
        update_database(row)
    select_database(0)
    root.title('工作簿')

def file_show(str_table):
    for x in range(len(str_table)):
        for y in range(len(str_table[0])):
            table_column=table[x][y]
            table_column.delete(0,'end')  # 清空
            table_column.insert(0,str_table[x][y])#写入
            if(x==0):
                table_column.config(state='normal')
                table_column.delete(0,'end')  # 清空
                table_column.insert(0,str_table[x][y])#写入
                table_column.config(state='disabled')
            if(y==0):
                table_column.config(state='normal')
                table_column.delete(0,'end')  # 清空
                table_column.insert(0,str_table[x][y])#写入
                table_column.config(width=3)
                table_column.config(state='disabled')


fname=''
def file_open():
    global fname
    mydir='D:/'
    fname = fd.askopenfilename(title='选择文件',initialdir =mydir,filetypes=[("CSV file", "*.csv;*.CSV")])
    if(fname==''):
        return
    root.title(os.path.basename(fname))
    with open(fname, 'r', encoding="gbk",newline='') as file:
        reader=csv.reader(file)
        column0=1
        for row in reader:
            rows=[]
            rows.append(str(column0))
            for i in row:
                rows.append(i)
            update_database(rows)
            column0+=1
    select_database(0)

def file_save():
    str_table=[]
    for x in range(len(table)):
        str_table_columns=[]
        for y in range(len(table[0])):
            str_table_columns.append(table[x][y].get())
        str_table.append(str_table_columns)
    for row in str_table[1:]:
        update_database(row)    
    # 连接到已有的数据库
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    # 执行查询语句
    select_data_query = 'SELECT * FROM my_table'
    cursor.execute(select_data_query)
    result = cursor.fetchall()
    result1=[]
    for row in result:
        result1.append(row[1:])
    # 关闭连接
    conn.close()
    global fname
    if(fname==''):
        mydir='D:/'
        fname = fd.asksaveasfilename(title='另存为',initialdir =mydir,filetypes=[("CSV file", "*.csv;*.CSV")],defaultextension='.csv')
        if(fname==''):
            return
    with open(fname, 'w',encoding="gbk", newline='') as file:
        writer = csv.writer(file)
        writer.writerows(result1[1:])

def file_asave():
    global fname
    fname=''
    file_save()
    root.title(os.path.basename(fname))
    
def popup(event):
    right_menu.post(event.x_root, event.y_root)# post在指定的位置显示弹出菜单
    w=event.widget
    #print(w.get())
    right_menu.entryconfigure("剪切",command=lambda: w.event_generate("<<Cut>>"))
    right_menu.entryconfigure("复制",command=lambda: w.event_generate("<<Copy>>"))
    right_menu.entryconfigure("粘贴",command=lambda: w.event_generate("<<Paste>>"))

page=0
#鼠标滚动
def app_mousewheel(event):
    global page
    if event.delta==120:
        #上滚
        if(page!=0):
            page=page-1
            select_database(page)
    if event.delta==-120:
        #下滚
        if(page!=4):
            page=page+1
            select_database(page)

first_row=['0','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P']
def db_firing():
    global first_row
    file_path='example.db'
    if(os.path.exists(file_path)):
        if(os.stat(file_path).st_size==0):
            os.remove(file_path)
            creat_database()
            for i in range(227):
                insert_database(i)
            update_database(first_row)
    else:
        creat_database()
        for i in range(227):
            insert_database(i)
        update_database(first_row)

db_firing()

root = tk.Tk()
root.geometry(f'{root.winfo_screenwidth()}x{root.winfo_screenheight()}+0+0')

#创建菜单栏
m=tk.Menu(root)
#定义一个父菜单
file=tk.Menu(m,tearoff=False)
#将上面定义的父菜单命名为File，放在菜单栏中
m.add_cascade(label='文件', menu=file)
#在File中加入下拉菜单，并添加对应命令操作。
#如果点击这些选项, 就会触发相应的功能
file.add_command(label='新建',accelerator='Ctrl+N',command=file_new)
file.add_command(label='打开', accelerator='Ctrl+O',command=file_open)
file.add_command(label='保存', accelerator='Ctrl+S',command=file_save)
file.add_command(label='另存为', accelerator='Ctrl+Shift+S',command=file_asave)
#添加一条分割线
file.add_separator()
file.add_command(label='退出',command=root.quit,accelerator='Ctrl+Q')

edit= tk.Menu(m,tearoff=False)
m.add_cascade(label='编辑', menu=edit)
edit.add_command(label='剪切',accelerator='Ctrl+X')
edit.add_command(label='复制',accelerator='Ctrl+C')
edit.add_command(label='粘贴',accelerator='Ctrl+V')
 
#二级菜单
s= tk.Menu(file,tearoff=False)
file.add_cascade(label='导入', menu=s)
s.add_command(label="模块")
s.add_command(label="文本文件")

#三级菜单
z=tk.Menu(s,tearoff=False)
s.add_cascade(label='图片',menu=z)
z.add_command(label="gif")
z.add_command(label="jpg")
z.add_command(label="png")
z.add_command(label="bmp")
root.configure(menu=m)

'''创建一个弹出菜单'''
right_menu = tk.Menu(root,tearoff=False,)
right_menu.add_command(label="剪切")
right_menu.add_command(label="复制")
right_menu.add_command(label="粘贴")

table=[]
rows = 47
columns = 17
for x in range(rows):
    table_columns=[]
    for y in range(columns):
        table_column = tk.Entry(root, width=15, fg='blue', font=('Timew New Roman',13,'italic'))
        table_column.bind("<Button-3>", popup) 
        table_column.grid(row=x, column=y)
        table_column.insert(0,'')#写入
        if(x==0):
            table_column.delete(0,'end')  # 清空
            table_column.insert(0,first_row[y])#写入
            table_column.config(state='disabled')
        if(y==0):
            table_column.delete(0,'end')  # 清空
            table_column.insert(0,x)#写入
            table_column.config(width=3)
            table_column.config(state='disabled')
        if y in (3,4,10,11,12):
            table_column.config(width=7)
        table_columns.append(table_column)
    table.append(table_columns)
print(len(table))
print(len(table[0]))
'''
tk.Button(root,width=14, fg='red', font=('Timew New Roman',13,'italic'),text='↑').grid(row=rows,column=0)
tk.Button(root,width=14, fg='red', font=('Timew New Roman',13,'italic'),text='↓').grid(row=rows,column=1)
tk.Button(root,width=14, fg='red', font=('Timew New Roman',13,'italic'),text='←').grid(row=rows,column=2)
tk.Button(root,width=14, fg='red', font=('Timew New Roman',13,'italic'),text='→').grid(row=rows,column=3)
tk.Button(root,command=file_new,width=14, fg='red', font=('Timew New Roman',13,'italic'),text='新建').grid(row=rows,column=14)
tk.Button(root,command=file_open,width=14, fg='red', font=('Timew New Roman',13,'italic'),text='打开').grid(row=rows,column=15)
tk.Button(root,command=file_save,width=14, fg='red', font=('Timew New Roman',13,'italic'),text='保存').grid(row=rows,column=16)             
'''
scrollbar_v = tk.Scrollbar(root,orient=tk.VERTICAL)
scrollbar_v.grid(row=0, column=columns,rowspan=rows,sticky='nesw')
scrollbar_v.config(command=on_scroll)

root.bind("<MouseWheel>", app_mousewheel)
#root.after(1000,file_new)
root.mainloop()