I'm using an example of pysimplegui from here 
The form on choosing listbox1
submitted data on the listbox column has added unwanted bracket
my question is, how to exclude the bracket? i dont know how to define the listbox variable to exclude the bracket ( line number 44 listbox_names = ('Listbox 1', 'Listbox 2', 'Listbox 3') )
the full py codes is as below :
import PySimpleGUI as sg
import pandas as pd
import re
from pathlib import Path
import time
from openpyxl import Workbook
path_to_file = 'All_Element_Data_Entry.xlsx'
path = Path(path_to_file)
#https://www.pythontutorial.net/python-basics/python-check-if-file-exists/
if path.is_file():
print(f'The file {path_to_file} exists')
else:
print(f'The file {path_to_file} does not exist')
print(f'Creating {path_to_file} ....')
#https://gis.stackexchange.com/a/158402 problem creating xlsx #https://openpyxl.readthedocs.io/en/stable/ how to create blank workbook using openpyxl
wb = Workbook()
ws = wb.active
ws['A1'] = "Full Name"
wb.save(path_to_file)
time.sleep(1)
sg.ChangeLookAndFeel('GreenTan')
sg.set_options(element_padding=(3,2)) #tight padding
EXCEL_FILE = path_to_file
df = pd.read_excel(EXCEL_FILE,engine='openpyxl')
def block_focus(window):
for key in window.key_dict: # Remove dash box of all Buttons
element = window[key]
if isinstance(element, sg.Button):
element.block_focus()
# ------ Menu Definition ------ #
menu_def = [['File', ['Open', 'Save', 'Exit', 'Properties']],
['Edit', ['Paste', ['Special', 'Normal', ], 'Undo'], ],
['Help', 'About...'], ]
# ------ Column Definition ------ #
column1 = [[sg.Text('Column 1', background_color='#F7F3EC', justification='center', size=(10, 1))],
[sg.Spin(values=('Spin Box 1', '2', '3'), key='spin_box1', initial_value='Spin Box 1')],
[sg.Spin(values=('Spin Box 1', '2', '3'), key='spin_box2', initial_value='Spin Box 2')],
[sg.Spin(values=('Spin Box 1', '2', '3'), key='spin_box3', initial_value='Spin Box 3')]]
listbox_names = ('Listbox 1', 'Listbox 2', 'Listbox 3')
list_Menu_Option = ['Menu Option 1', 'Menu Option 2', 'Menu Option 3']
list_combobox = ['Goverment', 'Private'] # set listbox https://www.programcreek.com/python/example/116007/PySimpleGUI.Listbox
lst = ('Full Citizen', 'Resident', 'Legal Migrant')
lst2 = ('Male', 'Female', 'Mix')
addressee1 = [[sg.Frame('Address 1',[[
sg.Multiline(default_text='This is the default Text should you decide not to type anything', key='default_text_multiline11', size=(35, 3))
]])]]
addressee2 = [[sg.Frame('Address 2',[[
sg.Multiline(default_text='A second multi-line', key='default_text_multiline12', size=(35, 3))
]])]]
Occupation = [[sg.Frame('Occupation',[[
sg.InputCombo(list_combobox, key='combo_box', size=(20, 1))
]])]]
Age = [[sg.Frame('Age',[[
sg.Slider(range=(1, 100),key='slider_range_bar', orientation='h', size=(18, 20), default_value=85)
]])]]
Expertise = [[sg.Frame('Expertise',[[
sg.InputOptionMenu(list_Menu_Option, key='menu_option')
]])]]
column_header = ['Full Name','ID Copy', 'Birth Cert Copy', 'Citizen', 'Gender', 'Address 1', 'Address 2', 'combo_box', 'slider_range_bar', 'menu_option', 'listbox_names', 'slider_range1', 'slider_range2', 'slider_range3', 'spin_box1', 'spin_box2', 'spin_box3']
frame_text_color = 'white'
tab1_layout = [
[sg.Text('Here is some text.... and a place to enter text')],
[sg.Text('Full Name', size=(9,1),key='full_name_label' ), sg.InputText('Applicant Full Name',key='default_text1' ),sg.Checkbox("Overwrite", default=False, enable_events=True, key='Overwrite_Existing_or_Not')],
[sg.Frame(layout=[
[sg.Checkbox('ID Copy', size=(10,1), key='Checkbox1'), sg.Checkbox('Birth Cert Copy', key='Checkbox2', default=True)],
], title='Documents Copy',title_color=frame_text_color, relief=sg.RELIEF_SUNKEN, tooltip='Choose wether to provide both or any of two')],
[sg.Frame('Applicant Basic Info',[[
sg.Frame('Citizenship',[[
sg.Radio(text, "Citizen", enable_events=True, key=f"Radio {i}")
for i, text in enumerate(lst)
]]),
sg.Frame('Gender',[[
sg.Radio(text, "Gender", enable_events=True, key=f"Radio2 {i}")
for i, text in enumerate(lst2)
]]),
]])],
[sg.Frame(layout=[
[sg.Column(addressee1, element_justification='c'), sg.Column(addressee2, element_justification='c')]
], title='Communication Address',title_color=frame_text_color, relief=sg.RELIEF_SUNKEN, tooltip='Choose wether to provide both or any of two')],
[sg.Frame(layout=[
[sg.Column(Occupation, element_justification='c'), sg.Column(Age, element_justification='c'), sg.Column(Expertise, element_justification='c')]
], title='Working Environment',title_color=frame_text_color, relief=sg.RELIEF_SUNKEN)]
]
tab2_layout = [
[sg.Listbox(listbox_names, key='listbox_names', size=(30, 3)),
sg.Frame('Labelled Group',[[
sg.Slider(range=(1, 100), orientation='v', size=(5, 20), default_value=25, key='slider_range1'),
sg.Slider(range=(1, 100), orientation='v', size=(5, 20), default_value=75, key='slider_range2'),
sg.Slider(range=(1, 100), orientation='v', size=(5, 20), default_value=10, key='slider_range3'),
sg.Column(column1, background_color='#F7F3EC')]])]
]
layout = [
[sg.Menu(menu_def, tearoff=True)],
[sg.Text('Register New Student', size=(30, 1), justification='center', font=("Helvetica", 25),text_color=frame_text_color)],
[sg.TabGroup([[sg.Tab('Basic Info', tab1_layout, ), sg.Tab('Background', tab2_layout)]])],
[sg.Push(),sg.Text('_' * 40), sg.Text('Choose & Save to a Folder', size=(21, 1), pad=((20,0),0)),sg.Text('_' * 40), sg.Push()],
[sg.Text('Your Folder', size=(15, 1), auto_size_text=False, justification='right'),
sg.InputText('Default Folder',do_not_clear=False), sg.FolderBrowse()],
[sg.Submit(tooltip='Click to submit this window'), sg.Button('Clear'), sg.Exit()],
[sg.StatusBar("", size=(0, 1), key='-STATUS-')]
]
window = sg.Window('Registration Form', layout, default_element_size=(40, 1), size=(890, 770),element_justification='c', grab_anywhere=False , use_default_focus=False, finalize=True)
def clear_input(): #clear input after SUBMITTED DATA
window['default_text1'].Update('')
window['default_text_multiline11'].Update('')
window['default_text_multiline12'].Update('')
window['full_name_label'].Update(text_color='black')
window['-STATUS-'].update('Success! Registration has been submitted!',text_color='green')
return None
def clear_input2(): #clear input after BUTTON CLEAR Click
window['default_text_multiline11'].Update('')
window['default_text_multiline12'].Update('')
window['full_name_label'].Update(text_color='black')
window['-STATUS-'].update('')
return None
while True:
event, values = window.read()
if event == sg.WIN_CLOSED or event == 'Exit':
break
elif event == 'Clear':
clear_input2()
elif event == 'Refresh':
sg.window.refresh()
elif event.startswith("Radio"):
text = window[event].Text
print(text)
elif event == 'Submit':
radio_value = window['Radio 0'].TKIntVar.get()
textradio = lst[radio_value % 1000] if radio_value else None
radio_value2 = window['Radio2 0'].TKIntVar.get()
textradio2 = lst2[radio_value2 % 1000] if radio_value2 else None
checkbox_value = window['Checkbox1'].TKIntVar.get()
textcheckbox = 'With ID' if checkbox_value else None
checkbox_value2 = window['Checkbox2'].TKIntVar.get()
textcheckbox2 = 'With Birth Cert' if checkbox_value2 else None
fullnametext = values['default_text1']
#https://stackoverflow.com/a/5188909/9222942 how-to-check-a-string-for-specific-characters
chars = set('("0123456789$,./~!"@#%^&*()_- =")')
if fullnametext == '':
print('Name Field Should not be empty!')
state = "Name Field Should not be empty!"
window['-STATUS-'].update(state,text_color='red')
sg.popup('Name Field Should not be empty!!',text_color='red')
#https://github.com/PySimpleGUI/PySimpleGUI/issues/4237#issuecomment-830732453 [Question] How can I check if an Input Box is empty before pressing a button?
window['full_name_label'].Update(text_color='red')
elif any((c in chars) for c in fullnametext):
print('Input is an integer number.This is a number, Name Field Should not be integer or mixed with!')
state = "Input is an integer number.This is a number, Name Field Should not be integer or mixed with!"
window['-STATUS-'].update(state,text_color='red')
sg.popup('Input is an integer number.This is a number, Name Field Should not be integer or mixed with!!',text_color='red')
window['full_name_label'].Update(text_color='red')
else:
print("This is not a number. proceed to submit")
record = [values['default_text1'],textcheckbox,textcheckbox2,textradio,textradio2,values['default_text_multiline11'],values['default_text_multiline12'],values['combo_box'],values['slider_range_bar'],values['menu_option'],values['listbox_names'],values['slider_range1'],values['slider_range2'],values['slider_range3'],values['spin_box1'],values['spin_box2'],values['spin_box3']]
# dataframe issue https://stackoverflow.com/a/50186267/9222942 valueerror-shape-of-passed-values-is-1-6-indices-imply-6-6
new_record = pd.DataFrame([record], columns=column_header)
df = pd.concat([df, new_record], ignore_index=True)
if values['Overwrite_Existing_or_Not'] == True:
df_last_record = df.drop_duplicates(subset=["Full Name"], keep="last") #KEEP the LATEST Update
df_last_record.to_excel(path_to_file, index=False)
state = "Success! New Data Submitted!"
window['-STATUS-'].update(state,text_color='green')
sg.popup('Existing Data has been overwriten!')
sg.popup('New Data Submitted',
'The results of the window.',
'The button clicked was "{}"'.format(event),
'The values are', record)
clear_input() # or use this https://stackoverflow.com/a/68729027/9222942 pysimplegui-how-do-you-remove-text-from-input-text-box
#https://stackoverflow.com/a/59333786/9222942 how-do-i-implement-checkbox-functionality-in-pysimpleguiqt
elif values['Overwrite_Existing_or_Not'] == False:
df.to_excel(EXCEL_FILE, index=False)
time.sleep(1)
duplicated = df.duplicated(subset=["Full Name"],keep=False)
print('print next(iter(duplicated[::-1])) ')
print( next(iter(duplicated[::-1])) )
data_read = next(iter(duplicated[::-1])) #https://stackoverflow.com/a/69147940/9222942 how-do-i-get-the-last-element-of-a-list?rq=1
if data_read == True:
df_last_record = df.drop_duplicates(subset=["Full Name"], keep="first") #KEEP the OLD Update never submit new if duplicate
df_last_record.to_excel(path_to_file, index=False)
state2 = "Name should not be a duplicate! User submitted data has been dumped!"
window['-STATUS-'].update(state2,text_color='red')
sg.popup('Duplicate detected! Data has not been saved!',text_color='red')
else:
print("no duplicates detected! Saving data..")
df_last_record = df.drop_duplicates(subset=["Full Name"], keep="first") #KEEP the OLD Update never submit new if duplicate
df_last_record.to_excel(path_to_file, index=False)
state = "Success! Data Submitted!"
window['-STATUS-'].update(state,text_color='green')
sg.popup('Data saved!')
sg.popup('Result Submitted',
'The results of the window.',
'The button clicked was "{}"'.format(event),
'The values are', record)
clear_input()
window['-STATUS-'].update('')
window.close()
#Useful references below :
#https://stackoverflow.com/a/70385882/9222942 overwrite-an-excel-sheet-with-pandas-dataframe-without-affecting-other-sheets
#https://stackoverflow.com/a/61933121/9222942 how-to-overwrite-data-on-an-existing-excel-sheet-while-preserving-all-other-shee
#https://lovespreadsheets.medium.com/how-to-remove-and-detect-duplicates-in-spreadsheets-using-python-3a42b9f11a2e
#https://stackoverflow.com/a/5188909/9222942 how-to-check-a-string-for-specific-characters
#https://www.pysimplegui.org/en/latest/cookbook/#recipe-collapsible-sections-visible-invisible-elements MOST important REFERENCE for pysimplegui
# use this to install as EXE --> pyinstaller -wF All_Element with TAB.py
#https://blog.finxter.com/pd-to_excel-saving-data-to-excel/ index with label
please help, tq!
CodePudding user response：
Try to confirm what data generated by your GUI and if it is in correct format before it sent to pandas or openpyxl.
It's not necessary to post a long script, short script is better here to demo your issue.
Returned value is in format of a list for there maybe lot of items selected.
import PySimpleGUI as sg
sg.ChangeLookAndFeel('GreenTan')
sg.set_options(element_padding=(3,2))
listbox_names = ('Listbox 1', 'Listbox 2', 'Listbox 3')
layout = [
[sg.Listbox(listbox_names, key='listbox_names', size=(30, 3))],
[sg.Button('Submit')],
]
window = sg.Window('Registration Form', layout)
while True:
event, values = window.read()
if event == sg.WIN_CLOSED:
break
elif event == 'Submit' and values['listbox_names']:
selection1 = values['listbox_names']
selection2 = values['listbox_names'][0]
print(f"Wrong data you got: {selection1}")
print(f"Correct data should be: '{selection2}'")
window.close()
Wrong data you got: ['Listbox 2']
Correct data should be: 'Listbox 2'