如何实现PySimpleGUI菜单程序计算数据自动存入Excel？

我正在使用PySimpleGUI和pandas开发一款菜单系统，需将姓名（Name）、站点（Site）、用餐时段（Meal Time）、计算得出的总费用（Total）以及格式为“Burger, Onions, Pickles, Drinks”的订单内容存储至Excel工作表。目前仅当手动将计算好的总费用输入到“Total”旁的输入框时，数据才能成功存入Excel，无法实现无需手动输入即可自动将计算数据存入Excel的功能。

原程序代码 #

import PySimpleGUI as sg
import pandas as pd

menu_dictionary = {
    "Cheese":0.50,
    "Sandwhich":1.75,
    "Pickles":0.25,
    "Hot Dog":1.25,
    "Burger": 3.5,
    "Onions": 0.75,
    "Bacon": 1.25,
    "Eggs": 1.00,
    "Fries": 1.25,
    "Chips": 1.25,
    "Salad": 1.25,
    "Potatoes": 1.25,
    "Ranch": 1.25,
    "Ketchup": 1.25,
    "BBQ": 1.25,
    "Drinks": 1.25,
}

Total=0
items = []
Name = ''
sg.theme("DarkTeal9")

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

layout = [
    [sg.Text("Welcom to the MAF Menu ")],
    [sg.Text('Name'), sg.InputText(key='Name'),sg.Text('Site'), sg.Combo(["A01", "B01", "C01", "D01", "E01", "F01", "G01", "H01", "I01", "J01", "K01", "L01", "M01", "N01", "O01"], key='Site')],
    [sg.Text("Total $" + str(Total), key='Total'), sg.InputText(key='Cost')],
    [sg.Text('Meal Time'),
                    sg.Checkbox('Breakfast', key='Breakfast'),
                    sg.Checkbox('Lunch', key='Lunch'),
                    sg.Checkbox('Dinner', key='Dinner')],
    [sg.Text("Entrees"), sg.Button("Burger"), sg.Button("Sandwhich"), sg.Button("Hot Dog"), sg.Button("Eggs")],
    [sg.Text("Toppings"), sg.Button("Onions"), sg.Button("Pickles"), sg.Button("Cheese")],
    [sg.Text("Sides"), sg.Button("Fries"), sg.Button("Chips"), sg.Button("Salad"), sg.Button("Potatoes")],
    [sg.Text('Condiments'), sg.Button("Ranch"), sg.Button("Ketchup"), sg.Button("BBQ")],
    [sg.Text("Beverages"), sg.Button("Drinks")],
    [sg.Button("Review"), sg.Text(Name, key='Order')],
    [sg.Submit(), sg.Button("Clear"), sg.Exit()],
]

window = sg.Window('Sample', layout)

def clear_input():

    for key in values:
        window['Order'].update('')
        items = []
        Name = ''
        Total = 0
        window['Total'].update("Total $0")
        window[key]('')
    return None


while True:
    event, values = window.read()

    if event == 'Clear':
        clear_input()

    if event == sg.WIN_CLOSED or event == "Exit":
        break

    if event == 'Submit':
       
        df = df.append(values, ignore_index=True)
        df.to_excel(EXCEL_FILE, index=False)
        sg.popup('Data Stored')
        clear_input()

    if event == "Review":

        order = ", ".join(items)
        order = "{}'s Order: {}".format(Name, order)
        x = Name + order + " for $" + str(Total)

        window['Order'].update(x)

    if event in menu_dictionary:
        Total = Total + menu_dictionary[event]
        if event not in items:
            items.append(event)
        window['Total'].update("Total $" + str(Total))


window.close()

针对核心问题修改代码，实现自动存储计算数据的功能：

修改要点 #

• 移除冗余输入框：删除手动输入总费用的Cost输入框，无需手动干预。
• 补充计算数据到存储字典：values仅包含界面输入控件内容，提交时需手动将计算好的总费用、订单内容加入存储数据。
• 修复变量作用域：clear_input函数中需声明全局变量，确保能正确重置全局状态。
• 格式化用餐时段：将Checkbox的布尔值转换为文本，支持多时段选中的拼接。

修改后的完整代码 #

import PySimpleGUI as sg
import pandas as pd

menu_dictionary = {
    "Cheese":0.50,
    "Sandwhich":1.75,
    "Pickles":0.25,
    "Hot Dog":1.25,
    "Burger": 3.5,
    "Onions": 0.75,
    "Bacon": 1.25,
    "Eggs": 1.00,
    "Fries": 1.25,
    "Chips": 1.25,
    "Salad": 1.25,
    "Potatoes": 1.25,
    "Ranch": 1.25,
    "Ketchup": 1.25,
    "BBQ": 1.25,
    "Drinks": 1.25,
}

# 全局变量初始化
total = 0
items = []
sg.theme("DarkTeal9")

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

layout = [
    [sg.Text("Welcome to the MAF Menu ")],
    [sg.Text('Name'), sg.InputText(key='Name'), sg.Text('Site'), sg.Combo(["A01", "B01", "C01", "D01", "E01", "F01", "G01", "H01", "I01", "J01", "K01", "L01", "M01", "N01", "O01"], key='Site')],
    # 移除手动输入Cost的输入框
    [sg.Text("Total $" + str(total), key='Total')],
    [sg.Text('Meal Time'),
                    sg.Checkbox('Breakfast', key='Breakfast'),
                    sg.Checkbox('Lunch', key='Lunch'),
                    sg.Checkbox('Dinner', key='Dinner')],
    [sg.Text("Entrees"), sg.Button("Burger"), sg.Button("Sandwhich"), sg.Button("Hot Dog"), sg.Button("Eggs")],
    [sg.Text("Toppings"), sg.Button("Onions"), sg.Button("Pickles"), sg.Button("Cheese")],
    [sg.Text("Sides"), sg.Button("Fries"), sg.Button("Chips"), sg.Button("Salad"), sg.Button("Potatoes")],
    [sg.Text('Condiments'), sg.Button("Ranch"), sg.Button("Ketchup"), sg.Button("BBQ")],
    [sg.Text("Beverages"), sg.Button("Drinks")],
    [sg.Button("Review"), sg.Text('', key='Order')],
    [sg.Submit(), sg.Button("Clear"), sg.Exit()],
]

window = sg.Window('Sample', layout)

def clear_input():
    global total, items
    # 清空订单显示
    window['Order'].update('')
    # 重置全局变量
    items = []
    total = 0
    window['Total'].update("Total $0")
    # 清空输入控件
    for key in ['Name', 'Site', 'Breakfast', 'Lunch', 'Dinner']:
        window[key].update('' if key not in ['Breakfast', 'Lunch', 'Dinner'] else False)

while True:
    event, values = window.read()

    if event == 'Clear':
        clear_input()

    if event == sg.WIN_CLOSED or event == "Exit":
        break

    if event == 'Submit':
        # 获取姓名
        name = values['Name']
        # 处理用餐时段：收集选中的选项
        meal_times = []
        if values['Breakfast']:
            meal_times.append('Breakfast')
        if values['Lunch']:
            meal_times.append('Lunch')
        if values['Dinner']:
            meal_times.append('Dinner')
        meal_time_str = ', '.join(meal_times)
        # 处理订单内容
        order_str = ', '.join(items)
        
        # 构造要存储的数据
        new_data = {
            'Name': name,
            'Site': values['Site'],
            'Meal Time': meal_time_str,
            'Total': total,
            'Order': order_str
        }
        # 添加到DataFrame并保存
        df = pd.concat([df, pd.DataFrame([new_data])], ignore_index=True)
        df.to_excel(EXCEL_FILE, index=False)
        sg.popup('Data Stored')
        clear_input()

    if event == "Review":
        name = values['Name']
        order_str = ', '.join(items)
        review_text = f"{name}'s Order: {order_str} for ${total}"
        window['Order'].update(review_text)

    if event in menu_dictionary:
        global total
        total += menu_dictionary[event]
        if event not in items:
            items.append(event)
        window['Total'].update(f"Total ${total}")

window.close()

功能说明 #

1. 自动计算并显示总费用，无需手动输入。
2. 提交时自动收集姓名、站点、用餐时段、总费用、订单内容，一次性存入Excel。
3. 支持多用餐时段选中，自动拼接为文本存储。
4. 清空功能可正确重置所有输入状态和计算数据。

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