ALL BUSINESS ENGLISH ARTICLES MICROSOFT EXCEL TIPS PYTHON

How to Create an Excel Data Entry Form in 10 Minutes Using Python (No VBA) | Easy & Simple

  • Did you know you can use Python code to create an Excel Data Entry Form and avoid duplicate records?
  • Πως να δημιουργήσετε μια φόρμα εισαγωγής δεδομένων σε 10 λεπτά με τη γλώσσα προγραμματισμού PYTHON (No VBA)
  • Πως να διασφαλίσετε ότι δεν θα κάνετε διπλοεγγραφές

Excel fields : Name, City, Favorite Color, German, Spanish, English, ChildrenData entry

Excel before data entry

Data entry form

Data entry form

Excel after data entry

‘Python code starts here.

import PySimpleGUI as sg

import pandas as pd

# Add some color to the window

sg.theme(‘DarkTeal9’)

EXCEL_FILE = ‘data_entry.xlsx’

df = pd.read_excel(EXCEL_FILE)

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 Color’, size=(15,1)), sg.Combo([‘Green’, ‘Blue’, ‘Red’], key=’Favorite Color’)],

[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 check_duplicate_entries(df, values):

duplicate_entries = df[

(df[‘Name’] == values[‘Name’]) &

(df[‘City’] == values[‘City’]) &

(df[‘Favorite Color’] == values[‘Favorite Color’]) &

(df[‘German’] == values[‘German’]) &

(df[‘Spanish’] == values[‘Spanish’]) &

(df[‘English’] == values[‘English’]) &

(df[‘Children’] == values[‘Children’])

]

return not duplicate_entries.empty

while True:

event, values = window.read()

if event == sg.WIN_CLOSED or event == ‘Exit’:

break

if event == ‘Clear’:

clear_input()

if event == ‘Submit’:

if check_duplicate_entries(df, values):

sg.popup(‘Duplicate entry found!’)

else:

if not check_duplicate_entries(df, values):

df = df._append(values, ignore_index=True)

df.drop_duplicates(inplace=True)

df.to_excel(EXCEL_FILE, index=False)

sg.popup(‘Data saved!’)

clear_input()

window.close()

‘Python code ends here.

Source video

This code has been created with the help of Coding is Fun. Keep in mind that additional code has been added to check for duplicate records.

Views: 5

Comments are closed.

Pin It

By continuing to use the site, you agree to the use of cookies. / Συνεχίζοντας να χρησιμοποιείτε την ιστοσελίδα, συμφωνείτε με τη χρήση των cookies. more information / περισσότερες πληροφορίες

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close