PySimpleGUI操作Excel DataFrame：删除Unnamed列、设置索引从1开始

核心修改说明 #

• 删除Unnamed列：读取Excel文件后，直接过滤所有列名以Unnamed开头的无效列即可
• 存储索引从1开始：更新DataFrame的索引序列，将整体索引值+1，再写入Excel

修改后完整代码 #

import PySimpleGUI as sg
import pandas as pd
import openpyxl

sg.theme('DarkGreen7')

EXCEL_FILE = 'Data_Entry.xlsx'
df = pd.read_excel(EXCEL_FILE)

# 删除所有Unnamed开头的无效列
df = df.loc[:, ~df.columns.str.startswith('Unnamed')]

my_img = sg.Image(r'C:\Users\master\Desktop\WEB\PYTHON_PYSIMPLEGUI\logo.png')

menu_def = [['File', ['Exit']],['Help', 'About...'],]

layout = [
    [sg.Menu(menu_def)],
    [sg.Column([[my_img]], justification='center')],
    [sg.Text('Simacan ( SIstem Monitoring And Controling Absen Nilai')],
    [sg.Text('Nama', size=(15,1)), sg.InputText(key='Nama')],
    [sg.Text('Kehadiran', size=(15,1)), sg.Combo(['Hadir', 'Sakit', 'Tidak Masuk'], key='Keterangan')],
    [sg.Submit(), sg.Exit()]
]

window =sg.Window('Aplikasi Simacan versi 1.2', layout,size=(800, 600), font='Courier 12')

while True:
    event, values = window.read()
    if event == sg.WIN_CLOSED or event == 'Exit':
        break
    if event == 'Submit':
        # 替换已弃用的append方法，兼容高版本pandas
        new_row = pd.DataFrame([values])
        df = pd.concat([df, new_row], ignore_index=True)
        # 调整索引从1开始
        df.index = df.index + 1
        df.to_excel(EXCEL_FILE, index=True)
        sg.popup('Data saved !')
        print(event, values)

window.close()

兼容旧版pandas写法 #

如果你仍想使用原有的append语法，仅需修改Submit事件逻辑即可：

if event == 'Submit':
    df = df.append(values, ignore_index=True)
    # 调整索引从1开始
    df.index = df.index + 1
    df.to_excel(EXCEL_FILE, index=True)
    sg.popup('Data saved !')
    print(event, values)

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