Hey Brian, let's work through your three issues one by one and get your PySimpleGUI Excel data entry tool up and running properly:

Issue 1: Fixing the InputCombo Data Source #

Your current code passes a 2-column DataFrame to InputCombo, which won't work—PySimpleGUI expects a flat list of selectable items. You have two options depending on what you want to display:

Option 1: Use a single column (e.g., first column of COA data)

Replace this line:

coa_df1 = coa_df.iloc[:,[0,1]]

With:

# Extract first column and convert to a list
coa_options = coa_df.iloc[:, 0].tolist()

Option 2: Combine both columns for better readability (e.g., "Code - Name")

If you want to show both the code and name in the dropdown:

# Format rows as "Code - Name" strings
coa_options = [f"{row[0]} - {row[1]}" for row in coa_df.iloc[:, [0,1]].values]

Then update your InputCombo to use coa_options:

sg.InputCombo(coa_options, key='COA', size=(50,1))

Issue 2: Dynamic Expense No Display #

Your static Text element can't update automatically. We'll modify it to show the latest Expense No from your Excel file, and increment it after each submission:

1. Initialize the latest Expense No when loading the Excel file:

EXCEL_FILE = 'D:\\92Python\\Acdb\\Data_Entry.xlsx'
try:
    df = pd.read_excel(EXCEL_FILE)
    # Get last ExNo, or start at 1 if file is empty
    current_exno = df['ExNo'].iloc[-1] + 1 if not df.empty else 1
except FileNotFoundError:
    # Create empty DataFrame if file doesn't exist yet
    df = pd.DataFrame(columns=['ExNo', 'Date', 'COA', 'Supplier', 'Total', 'VAT', 'Cash', 'CreditCard', 'Bank', 'Owners', 'Desc', 'Children'])
    current_exno = 1

2. Update the layout to use an updatable Text element for Expense No:

[sg.Text('Expense No', size=(15,1)), sg.Text(str(current_exno), key='ExNo', size=(10,1))]

3. Increment and update after saving a record:

if event == 'Submit':
    # ... save logic ...
    current_exno += 1
    window['ExNo'].Update(str(current_exno))

Issue 3: Fixing the Amount Calculation Syntax Error #

Your original code tries to convert a list (['Total']) to an integer, which is invalid. We'll use the actual form values, add decimal support, and handle invalid inputs:

Replace your commented calculation with:

try:
    # Convert inputs to floats (handle empty fields as 0)
    total = float(values['Total']) if values['Total'] else 0.0
    vat = float(values['VAT']) if values['VAT'] else 0.0
    amount = total - vat
    # Uncomment below if you want to display the calculated amount (add the Amount element back to layout first)
    # window['Amount'].Update(f"{amount:.2f}")
except ValueError:
    sg.popup('Error: Please enter valid numbers for Total and VAT!')
    continue  # Skip saving if inputs are invalid

Full Corrected Code #

import PySimpleGUI as sg
from datetime import datetime
import pandas as pd

# Add some color to the window
sg.theme('DarkTeal9')

coa_file = 'D:\\92Python\\Acdb\\coa.xlsx'
coa_data = pd.read_excel(coa_file, sheet_name="coa", skiprows=1, index_col=False)
coa_df = pd.DataFrame(coa_data)

# Fix Issue 1: Prepare dropdown options (combined columns for clarity)
coa_options = [f"{row[0]} - {row[1]}" for row in coa_df.iloc[:, [0,1]].values]

# Fix Issue 2: Initialize Expense No from Excel file
EXCEL_FILE = 'D:\\92Python\\Acdb\\Data_Entry.xlsx'
try:
    df = pd.read_excel(EXCEL_FILE)
    current_exno = df['ExNo'].iloc[-1] + 1 if not df.empty else 1
except FileNotFoundError:
    df = pd.DataFrame(columns=['ExNo', 'Date', 'COA', 'Supplier', 'Total', 'VAT', 'Cash', 'CreditCard', 'Bank', 'Owners', 'Desc', 'Children'])
    current_exno = 1

layout = [
    [sg.Text('Please fill out the following fields:')],
    # Fix Issue 2: Dynamic Expense No display
    [sg.Text('Expense No', size=(15,1)), sg.Text(str(current_exno), key='ExNo', size=(10,1))],
    [sg.Text('Date', size=(15,1)), sg.InputText(key='Date'), sg.CalendarButton("Date", close_when_date_chosen=True, target="Date", location=(0,0), no_titlebar=False)],
    # Fix Issue 1: Use prepared coa_options for InputCombo
    [sg.Text('COA', size=(15,1)), sg.InputCombo(coa_options, key='COA', size=(50,1))],
    [sg.Text('Supplier', size=(15,1)), sg.InputText(key='Supplier')],
    [sg.Text('Total', size=(15,1)), sg.InputText(key='Total')],
    [sg.Text('VAT', size=(15,1)), sg.InputText(key='VAT')],
    # Optional: Uncomment to display calculated Amount
    #[sg.Text('Amount', size=(15,1)), sg.Text('', key='Amount', size=(10,1))],
    [sg.Text('Paid', size=(15,1)), sg.Checkbox('Cash', key='Cash'), sg.Checkbox('CreditCard', key='CreditCard'), sg.Checkbox('Bank', key='Bank'), sg.Checkbox('Owners', key='Owners')],
    [sg.Text('Description', size=(15,1)), sg.InputText(key='Desc')],
    [sg.Text('No. of Children', size=(15,1)), sg.Spin([i for i in range(0,16)], initial_value=0, key='Children')],
    [sg.Submit(), sg.Button('Clear'), sg.Exit()]
]

window = sg.Window('Simple data entry form', layout, no_titlebar=False, location=(10,10), size=(800,600), keep_on_top=True)

def clear_input():
    # Reset all fields except Expense No
    fields_to_clear = ['Date', 'Supplier', 'Total', 'VAT', 'Cash', 'CreditCard', 'Bank', 'Owners', 'Desc', 'Children']
    for key in fields_to_clear:
        if key in ['Cash', 'CreditCard', 'Bank', 'Owners']:
            window[key](False)
        elif key == 'Children':
            window[key](0)
        else:
            window[key]('')
    return None

while True:
    event, values = window.read()
    if event == sg.WIN_CLOSED or event == 'Exit':
        break
    if event == 'Clear':
        clear_input()
    if event == 'Submit':
        # Fix Issue 3: Correct Amount calculation with error handling
        try:
            total = float(values['Total']) if values['Total'] else 0.0
            vat = float(values['VAT']) if values['VAT'] else 0.0
            amount = total - vat
            # Optional: Update Amount display
            # window['Amount'].Update(f"{amount:.2f}")
        except ValueError:
            sg.popup('Error: Please enter valid numeric values for Total and VAT!')
            continue
        
        # Add current Expense No to the submission
        values['ExNo'] = current_exno
        
        # Save new record to Excel
        new_record = pd.DataFrame(values, index=[0])
        df = pd.concat([df, new_record], ignore_index=True)
        df.to_excel(EXCEL_FILE, index=False)
        sg.popup('Data saved successfully!')
        
        # Update Expense No for next entry
        current_exno += 1
        window['ExNo'].Update(str(current_exno))
        
        clear_input()

window.close()

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