Created
June 19, 2024 06:33
-
-
Save Sven-Bo/6f42f4151a23bb0c19a3a27239961a19 to your computer and use it in GitHub Desktop.
Modification of the initial code from the video tutorial "Simple Data Entry Form with PySimpleGUI and Pandas" (https://youtu.be/svcv8uub0D0). The enhancement allows the Excel columns to autofit their widths based on the content using the openpyxl library.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| from pathlib import Path | |
| import PySimpleGUI as sg | |
| import pandas as pd | |
| from openpyxl import load_workbook | |
| # Add some color to the window | |
| sg.theme('DarkTeal9') | |
| current_dir = Path(__file__).parent if '__file__' in locals() else Path.cwd() | |
| EXCEL_FILE = current_dir / 'Data_Entry.xlsx' | |
| # Load the data if the file exists, if not, create a new DataFrame | |
| if EXCEL_FILE.exists(): | |
| df = pd.read_excel(EXCEL_FILE) | |
| else: | |
| df = pd.DataFrame() | |
| layout = [ | |
| [sg.Text('Please fill out the following fields:')], | |
| [sg.Text('Name', size=(15, 1)), sg.InputText(key='Name')], | |
| [sg.Text('City', size=(15, 1)), sg.InputText(key='City')], | |
| [sg.Text('Favorite Colour', size=(15, 1)), sg.Combo(['Green', 'Blue', 'Red'], key='Favorite Colour')], | |
| [sg.Text('I speak', size=(15, 1)), | |
| sg.Checkbox('German', key='German'), | |
| sg.Checkbox('Spanish', key='Spanish'), | |
| sg.Checkbox('English', key='English')], | |
| [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) | |
| def clear_input(): | |
| for key in values: | |
| window[key]('') | |
| return None | |
| def autofit_column_width(file_path): | |
| workbook = load_workbook(file_path) | |
| worksheet = workbook.active | |
| for column in worksheet.columns: | |
| max_length = 0 | |
| column = list(column) | |
| for cell in column: | |
| try: | |
| if len(str(cell.value)) > max_length: | |
| max_length = len(cell.value) | |
| except: | |
| pass | |
| adjusted_width = (max_length + 2) | |
| worksheet.column_dimensions[column[0].column_letter].width = adjusted_width | |
| workbook.save(file_path) | |
| while True: | |
| event, values = window.read() | |
| if event == sg.WIN_CLOSED or event == 'Exit': | |
| break | |
| if event == 'Clear': | |
| clear_input() | |
| if event == 'Submit': | |
| new_record = pd.DataFrame(values, index=[0]) | |
| df = pd.concat([df, new_record], ignore_index=True) | |
| df.to_excel(EXCEL_FILE, index=False) # This will create the file if it doesn't exist | |
| autofit_column_width(EXCEL_FILE) # Autofit the column width | |
| sg.popup('Data saved!') | |
| clear_input() | |
| window.close() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment