Python开发的Excel数据录入工具提交时旧日期时间被覆盖如何解决

问题根因 #

1. 提交逻辑中对日期、时间的处理和其他字段不一致：读取Excel中的历史日期、时间序列后，你直接将两个序列整体赋值为当前日期、时间的单个字符串，导致新生成的Excel里所有行的日期、时间都被替换为当前值
2. 日期、时间的获取逻辑放在了全局作用域，仅会在程序启动时执行一次，后续提交不会更新为最新时间
3. 构建最终写入Excel的DataFrame时存在笔误，Good Qty字段错误绑定了SeriesJ（对应Outcome列的数据），会导致该列数据异常

修正后的代码 #

from tkinter import *
import pandas as pd
from datetime import date
from datetime import datetime
import time

def submit_fields():
    path = 'good.xlsx'
    df1 = pd.read_excel(path)
    # 读取历史各列数据
    SeriesA = df1['Date']
    SeriesB = df1['Time']
    SeriesC = df1['Operator']
    SeriesD = df1['CNC']
    SeriesE = df1['Work Order']
    SeriesF = df1['Company']
    SeriesG = df1['Part']
    SeriesH = df1['Defect Qty']
    SeriesI = df1['Code']
    SeriesJ = df1['Outcome']
    SeriesK = df1['Good Qty']

    # 每次提交时获取最新的日期、时间
    today = str(date.today())
    now = str(datetime.now().time())
    
    # 新记录追加到对应序列末尾
    SeriesA = pd.concat([SeriesA, pd.Series([today])])
    SeriesB = pd.concat([SeriesB, pd.Series([now])])
    SeriesC = pd.concat([SeriesC, pd.Series(entry3.get())])
    SeriesD = pd.concat([SeriesD, pd.Series(entry4.get())])
    SeriesE = pd.concat([SeriesE, pd.Series(entry5.get())])
    SeriesF = pd.concat([SeriesF, pd.Series(entry6.get())])
    SeriesG = pd.concat([SeriesG, pd.Series(entry7.get())])
    SeriesH = pd.concat([SeriesH, pd.Series(entry8.get())])
    SeriesI = pd.concat([SeriesI, pd.Series(entry9.get())])
    SeriesJ = pd.concat([SeriesJ, pd.Series(entry10.get())])
    SeriesK = pd.concat([SeriesK, pd.Series(entry11.get())])

    df2 = pd.DataFrame({"Date":SeriesA, "Time":SeriesB, "Operator":SeriesC,"CNC":SeriesD, "Work Order":SeriesE,
                        "Company":SeriesF, "Part":SeriesG, "Defect Qty":SeriesH, "Code":SeriesI, "Outcome":SeriesJ,
                        "Good Qty":SeriesK})
    df2.to_excel(path, index=False)

    entry3.delete(0, END)
    entry4.delete(0, END)
    entry5.delete(0, END)
    entry6.delete(0, END)
    entry7.delete(0, END)
    entry8.delete(0, END)
    entry9.delete(0, END)
    entry10.delete(0, END)
    entry11.delete(0, END)

########TOP LABEL#####################################1
master = Tk()

master.title("test")

lbl = Label(master, text="Hello")

lbl.grid(column=0, row=0)
########TOP LABEL#####################################2

master.geometry('500x500')

Label(master, text="Date").grid(row=0)
Label(master, text=date.today()).grid(row=0, column=1)
Label(master, text="Time").grid(row=1)

##########################################CLOCK###########################1
label = Label(master)
label.grid(row =1, column=1)
def digitalclock():
   text_input = time.strftime("%H:%M:%S")
   label.config(text=text_input)
   label.after(200, digitalclock)
digitalclock()
##########################################CLOCK###########################2

Label(master, text="Operator").grid(row=2)
Label(master, text="CNC").grid(row=3)
Label(master, text="Work Order").grid(row=4)
Label(master, text="Company").grid(row=5)
Label(master, text="Part").grid(row=6)
Label(master, text="Defect Qty").grid(row=7)
Label(master, text="Code").grid(row=8)
Label(master, text="Outcome").grid(row=9)
Label(master, text="Good Qty").grid(row=10)

entry1 = Entry(master)
entry2 = Entry(master)
entry3 = Entry(master)
entry4 = Entry(master)
entry5 = Entry(master)
entry6 = Entry(master)
entry7 = Entry(master)
entry8 = Entry(master)
entry9 = Entry(master)
entry10 = Entry(master)
entry11 = Entry(master)
entry12 = Entry(master)

entry3.grid(row=2, column=1, pady=4)
entry4.grid(row=3, column=1, pady=4)
entry5.grid(row=4, column=1, pady=4)
entry6.grid(row=5, column=1, pady=4)
entry7.grid(row=6, column=1, pady=4)
entry8.grid(row=7, column=1, pady=4)
entry9.grid(row=8, column=1, pady=4)
entry10.grid(row=9, column=1, pady=4)
entry11.grid(row=10, column=1, pady=4)

Button(master, text='Submit', command=submit_fields).grid(row=12,column=1, pady=4)

mainloop()

改动说明 #

• 将日期、时间的获取逻辑移到submit_fields函数内部，每次提交都会取最新时间
• 日期、时间和其他字段统一用pd.concat追加新记录，不再直接覆盖历史序列
• 修正了Good Qty字段的引用错误
• 替换了pandas中已废弃的append方法为官方推荐的pd.concat，兼容新版本pandas

内容的提问来源于stack exchange，提问作者Prometheus