Convert Excel into a Table using PySimpleGUI

PySimpleGUI is a Python library that allows you to easily create GUI applications using a simple and intuitive API. One of the features of PySimpleGUI is its ability to interact with Excel files, making it easy to read data from an Excel file and display it in a table within your GUI application. In this tutorial, we will walk through the process of creating a GUI application using PySimpleGUI to display data from an Excel file in a table.

Step 1: Install PySimpleGUI
First, you will need to install PySimpleGUI if you haven’t already. You can do this using pip by running the following command in your terminal:

pip install PySimpleGUI

Step 2: Create a GUI window
Next, we will create a simple GUI window using PySimpleGUI. Here is an example code snippet that creates a basic window with a title and size:

import PySimpleGUI as sg

layout = [
    [sg.Text('Excel to Table')],
    [sg.Button('Load Data')],
    [sg.Table(values=[], headings=[], auto_size_columns=True, justification='right')]
]

window = sg.Window('Excel to Table', layout)

Step 3: Load data from an Excel file
Now, we will add functionality to load data from an Excel file into our table. We will use the sg.popup_get_file function to prompt the user to select an Excel file, and then use Pandas to read the data from the file. Here is the updated code with the functionality to load data from an Excel file:

import PySimpleGUI as sg
import pandas as pd

layout = [
    [sg.Text('Excel to Table')],
    [sg.Button('Load Data')],
    [sg.Table(values=[], headings=[], auto_size_columns=True, justification='right')]
]

window = sg.Window('Excel to Table', layout)

while True:
    event, values = window.read()

    if event == sg.WIN_CLOSED:
        break

    if event == 'Load Data':
        file_path = sg.popup_get_file('Select an Excel file')
        if file_path:
            df = pd.read_excel(file_path)
            headings = list(df.columns)
            values = df.values.tolist()
            window['Table'].update(values=values, headings=headings)

window.close()

In the updated code, we added an event loop that listens for events from the GUI window. When the ‘Load Data’ button is clicked, the program prompts the user to select an Excel file using sg.popup_get_file. It then reads the data from the file using Pandas and updates the values and headings of the table in the GUI window using the update method.

Step 4: Run the application
To run the application, save the code to a Python file and run it in your terminal. You should see a GUI window with a ‘Load Data’ button. Clicking the button will prompt you to select an Excel file, and the data from the file will be displayed in a table within the window.

In conclusion, PySimpleGUI makes it easy to create GUI applications that interact with Excel files. With just a few lines of code, you can create a GUI application using PySimpleGUI to display data from an Excel file in a table. I hope this tutorial helps you get started with PySimpleGUI and Excel file handling in your Python projects.