Excel align titles to column data using pandas and pySimpleGui

Question

I am creating simple project for data entry, i have successfully created the GUI and excel sheet that has to be in the directory, i am kinda struggling with creating column title and assign text entered by user input in the excel sheet, also struggling with making the program itself create an excel file using pandas lib or other libs.

`from openpyxl import load_workbook
import PySimpleGUI as sg
from datetime import datetime


sg.theme('DarkAmber')

layout = [[sg.Text('First Name'),sg.Push(), sg.Input(key='FIRST_NAME')],
          [sg.Text('Last Name'),sg.Push(), sg.Input(key='LAST_NAME')],
          [sg.Text('TEL:'),sg.Push(), sg.Input(key='NUMBER')],
          [sg.Text('File name:'), sg.Input(key='File_name')] #user use this file name to create new file
          
         [sg.Text('Your Folder', size=(15, 1), auto_size_text=False, justification='right'),
        sg.InputText('Default Folder'), sg.FolderBrowse()], #failed to make it create an excel file to create new directory



          [sg.Button('Submit'), sg.Button('Close')]]

window = sg.Window('Data Entry', layout, element_justification='center')

while True:
    event, values = window.read()
    if event == sg.WIN_CLOSED or event == 'Close':
        break
    if event == 'Submit':
        try:
            wb = load_workbook('Book1.xlsx')
            sheet = wb['Sheet1']
            ID = len(sheet['ID']) + 1
            time_stamp = datetime.now().strftime("%d/%m/%Y %H:%M:%S")

            data = [ID, values['FIRST_NAME'], values['LAST_NAME'], values['NUMBER'], time_stamp]

            sheet.append(data)

            wb.save('Book1.xlsx')

            window['FIRST_NAME'].update(value='')
            window['LAST_NAME'].update(value='')
            window['NUMBER'].update(value='')
            window['FIRST_NAME'].set_focus()

            sg.popup('Success', 'Data Saved')
        except PermissionError:
            sg.popup('File in use', 'File is being used by another User.\nPlease try again later.')


window.close()`

I have tried using Pandas lib for creating excel file but failed to make it recognized by the GUI and to add to the GUI a place to create or save as commented next to the Default folder.

I have faced problems with :

using the PySimpleGUI to create titles with text and take its input in the excel sheet for example if user entered first name, the title in the excel sheet should automatically named as First name :)
use the a function instead of (sg.FolderBrowse()) to create new file or open existing file,I couldnt find in the cookbook of the PySimpleGUI.

score 1 · Accepted Answer · answered Dec 17 '22 at 06:21

I have made some changes to your code in line with my best understanding of what you are attempting to achieve.

Changed the ''Default Folder' input to string. The code will open the Excel file 'Default Folder'/'File_name' if it exists. If either or both do not exist then they are created. Therefore if the file was previously created and written to it will be re-opened and updated. If it did not exist then its created and written to.
A new sheet has the Headers 'ID', 'First Name', 'Last Name', 'Telephone Number', 'Date/Time' added on row 1 when first writting.

No formatting or other changes have been included.

...

from openpyxl import load_workbook, Workbook
import PySimpleGUI as sg
from datetime import datetime
import os
import pathlib


sg.theme('DarkAmber')

layout = [[sg.Text('First Name'), sg.Push(), sg.Input(key='FIRST_NAME')],
          [sg.Text('Last Name'), sg.Push(), sg.Input(key='LAST_NAME')],
          [sg.Text('TEL:'), sg.Push(), sg.Input(key='NUMBER')],
          [sg.Text('File name:'), sg.Input(key='File_name')],  # user use this file name to create new file

          [sg.Text('Your Folder', size=(15, 1), auto_size_text=False, justification='right'),
           sg.Input(key='Default Folder')],
          # failed to make it create an excel file to create new directory

          [sg.Button('Submit'), sg.Button('Close')]]

window = sg.Window('Data Entry', layout, element_justification='center')

while True:
    event, values = window.read()
    if event == sg.WIN_CLOSED or event == 'Close':
        break
    if event == 'Submit':
        try:
            ### Get the path and filename
            path = values['Default Folder']
            filename = values['File_name']

            ### Ensure the filename extension is xlsx
            if pathlib.Path(filename).suffix != '.xlsx':
                filename += '.xlsx'

            ### Check if the entered path exists, if not create it
            ### If the path doesn't exist then we create a new workbook
            if not os.path.exists(path):
                os.makedirs(path)
                wb = Workbook()
            else:
                ### If the path exists then check fo
                if os.path.isfile(os.path.join(path, filename)):
                    ### If the filename exists then open as a workbook
                    wb = load_workbook(os.path.join(path, filename))
                else:
                    ### If the filename does not exist then open a new workbook
                    wb = Workbook()

            sheet = wb.active
            ID = len(sheet['ID'])
            ### If its a new workbook/worksheet ID = 1 so add the Headers into row 1
            ### Subsequent data will be added from row 2 onwards and ID start at ID 1
            if ID == 1:
                sheet.append(['ID', 'First Name', 'Last Name', 'Telephone Number', 'Date/Time'])
                sheet.delete_rows(idx=ID, amount=1)

            time_stamp = datetime.now().strftime("%d/%m/%Y %H:%M:%S")

            data = [ID, values['FIRST_NAME'], values['LAST_NAME'], values['NUMBER'], time_stamp]

            sheet.append(data)

            ### Workbook is saved using the name entered in the GUI
            wb.save(os.path.join(path,filename))
            
            window['FIRST_NAME'].update(value='')
            window['LAST_NAME'].update(value='')
            window['NUMBER'].update(value='')
            window['FIRST_NAME'].set_focus()

            sg.popup('Success', 'Data Saved')
        except PermissionError:
            sg.popup('File in use', 'File is being used by another User.\nPlease try again later.')

window.close()

Excel align titles to column data using pandas and pySimpleGui

1 Answers1