How to Create an Excel Data Form Using Python + ChatGPT
Intro
In this post, I will guide you through the process of building a data entry form for Excel using Python. The exciting part? I won’t be writing any code myself! Instead, I will demonstrate how to create a GUI with Figma, convert that design into Python code, and use ChatGPT to implement functionality such as saving data to a spreadsheet. Finally, I will show you how to package this Python code into a standalone executable, making it accessible for users without Python installed on their machines.
Designing the GUI in Figma
To start designing the GUI, head over to figma.com. The first step is to create a frame on the canvas. Set the dimensions to 650 by 350 pixels. Next, draw a rectangle for the background and set its color to dark blue. For the header, use another rectangle and leave the default color.
Next, insert an image into the header by dragging any image onto the canvas. Adjust the layer level so that the image appears above the background rectangle. After that, add the form title using the text element. Ensure to adjust the font size and weight on the right side. A quick note: sometimes, the text element can get misaligned after conversion, so resize it correctly by double-clicking on the corners.
Now, create the input fields. Use the text element for the label and rectangles for the actual input fields, changing the rectangle color to light yellow. To enhance the appearance, round the corners of the rectangles. Duplicate the fields by selecting both the label and text input field, then pressing Ctrl+D to create additional fields for first name, last name, and email address.
For the submit button, use another rectangle with a light red color. Center the button text both vertically and horizontally, then group the rectangle and text. Rename the button to “button” as the naming is vital for the later export to Python code. The input fields should be renamed to “textbox,” and images should be labeled simply as “image.” Finally, remove any numbers from the background rectangles.
Converting Figma Design to Code
With the design complete, it’s time to convert it into Python code. First, ensure you have a Python virtual environment set up, preferably with Python 3.10 due to dependency issues with TkDesigner. Install the TkDesigner package using the command pip install tkdesigner
.
Next, use the command line to run TkDesigner with the URL of your Figma design. This URL can be obtained by clicking the share button in Figma. Make sure to change “design” to “file” in the link if you encounter any issues. You will also need to generate an access token in your Figma dashboard for this process.
Once you execute the command, a new folder called Build will be created containing your converted Python file. Open this file in an IDE like VS Code. Running this file will display your input form, where the yellow fields are now text input fields.
Using ChatGPT to Add Functionality
Next, I need to write the code to save the entries to a spreadsheet. This can also be adapted to save data to a database or Google Sheets. The GUI uses Tkinter, which is included with Python, so no additional installation is required.
In the command option, I will specify the action for the button click, which currently just prints a message. Instead of coding the functionality from scratch, I’ll ask ChatGPT for help. I’ll provide a prompt like this:
“Adjust the Excel data entry form to save submissions to submissions.xlsx using OpenPyXL. Create the file if it doesn’t exist and assign an auto-incremented ID to each submission. After submitting, clear all fields and display a success message. Ensure all fields are filled, show an error if any are empty, and validate the email before submitting.”
After pasting the generated code into my Python file, I’ll run the script. The entry form appears, and I can input data. Upon pressing the submit button, a message box confirms that the submission has been saved. The data is correctly stored in a new spreadsheet, with validation checks for the email format functioning as expected.
Turning Python Code into a Standalone App
Now, to ensure that anyone can use this application without needing Python installed, I’ll convert the GUI into a standalone app. I’ll first adjust the path settings to use relative paths for the assets. Then, I’ll install PyInstaller using the command pip install pyinstaller
.
Finally, I’ll run PyInstaller with specific commands to include the images stored in the assets folder. This will create a new folder containing the executable version of the application. I’ll test the executable to ensure it works correctly, and upon entering data, it saves as expected.
Outro
And that’s it! I’ve successfully created a data entry form for Excel using Python and ChatGPT, turning it into a standalone application. Feel free to adjust the code and explore ChatGPT for further enhancements. It may not always be perfect, but it certainly speeds up the development process. Thanks for reading!