Pull data from excel into Python

Question

I am working on a menu app and I need it to be dynamic.

1. I am having trouble figuring out how I would be able to pull data from an excel sheet into my 'menu_dictionary' value.
2. I want to be able to change the prices without having to manually change the code
3. I also want to be able to make the excel file accessible from a OneDrive account so it can be updated to a different computer from another.
4. create a new .exe file. is this possible?

Here is my code:

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")
x = ''
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:"), sg.InputText('$' + str(Total), key='Cost', size=5)],
    [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.Multiline(x, key='New', size=(20, 5))],
    [sg.Submit(), sg.Button("Clear"), sg.Exit()],
]

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


def clear_input():
    for key in values:
        items = []
        Name = ''
        Total = 0

        window[key]('')
    return None


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

    if event == 'Clear':
        Total = 0
        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()
        Total = 0
        window['Cost'].update(Total)


    if event == "Review":
        order = ", ".join(items)
        order = "{} Order: {}".format(Name, order)
        x = Name + order + " for $" + str(Total)
        window['New'].update(x)

    if event in menu_dictionary:
        Total = Total + menu_dictionary[event]
        if event not in items:
            items.append(event)

        window['Cost'].update('$' + str(Total))

window.close()

Answer 1

Let's start with OneDrive.
In Onedrive, simply share your Excel file and copy the URL (Copy Link). If you need to write back to the Excel file, you must share it with edit right. Actually, this is #insecure!
[proceed with caution! | insecure access ahead]

• Get OneDrive link
• Parse OneDrive link to get 'direct access'
• Read Excel file into pandas with `pd.read_excel(onedrivedirectlink)
  See Santhanavanich article.
  Read more on pandas-read-excel

What to do in Python: for more prudent access to your Excel file securely.
Use the O365 Python or office365 API libraries

1. import libraries
2. authenticate
3. access Excel file
4. interact with file

The second part: pull data from an excel sheet into my 'menu_dictionary' value
The menu_dictionary you define must map to your df

dynamically change pricing:
All that is left is for you to make changes in your OneDrive Excel file as you desire.

pandas.ExcelWriter gives you flexibility in writing to Excel file.
Openpyxl, on the other hand, enable you to engage Excel file robustly. You can use other libraries.

Lastly, create .exe:
You may want to leverage Python library, pyinstaller for your .exe