The code from my video on how to view an excel file or Pandas Dataframe inside Tkinter (with comments)

TkinterExcel.py

# Youtube Link: https://www.youtube.com/watch?v=PgLjwl6Br0k

import tkinter as tk
from tkinter import filedialog, messagebox, ttk

import pandas as pd

# initalise the tkinter GUI
root = tk.Tk()

root.geometry("500x500") # set the root dimensions
root.pack_propagate(False) # tells the root to not let the widgets inside it determine its size.
root.resizable(0, 0) # makes the root window fixed in size.

# Frame for TreeView
frame1 = tk.LabelFrame(root, text="Excel Data")
frame1.place(height=250, width=500)

# Frame for open file dialog
file_frame = tk.LabelFrame(root, text="Open File")
file_frame.place(height=100, width=400, rely=0.65, relx=0)

# Buttons
button1 = tk.Button(file_frame, text="Browse A File", command=lambda: File_dialog())
button1.place(rely=0.65, relx=0.50)

button2 = tk.Button(file_frame, text="Load File", command=lambda: Load_excel_data())
button2.place(rely=0.65, relx=0.30)

# The file/file path text
label_file = ttk.Label(file_frame, text="No File Selected")
label_file.place(rely=0, relx=0)


## Treeview Widget
tv1 = ttk.Treeview(frame1)
tv1.place(relheight=1, relwidth=1) # set the height and width of the widget to 100% of its container (frame1).

treescrolly = tk.Scrollbar(frame1, orient="vertical", command=tv1.yview) # command means update the yaxis view of the widget
treescrollx = tk.Scrollbar(frame1, orient="horizontal", command=tv1.xview) # command means update the xaxis view of the widget
tv1.configure(xscrollcommand=treescrollx.set, yscrollcommand=treescrolly.set) # assign the scrollbars to the Treeview Widget
treescrollx.pack(side="bottom", fill="x") # make the scrollbar fill the x axis of the Treeview widget
treescrolly.pack(side="right", fill="y") # make the scrollbar fill the y axis of the Treeview widget


def File_dialog():
    """This Function will open the file explorer and assign the chosen file path to label_file"""
    filename = filedialog.askopenfilename(initialdir="/",
                                          title="Select A File",
                                          filetype=(("xlsx files", "*.xlsx"),("All Files", "*.*")))
    label_file["text"] = filename
    return None


def Load_excel_data():
    """If the file selected is valid this will load the file into the Treeview"""
    file_path = label_file["text"]
    try:
        excel_filename = r"{}".format(file_path)
        if excel_filename[-4:] == ".csv":
            df = pd.read_csv(excel_filename)
        else:
            df = pd.read_excel(excel_filename)

    except ValueError:
        tk.messagebox.showerror("Information", "The file you have chosen is invalid")
        return None
    except FileNotFoundError:
        tk.messagebox.showerror("Information", f"No such file as {file_path}")
        return None

    clear_data()
    tv1["column"] = list(df.columns)
    tv1["show"] = "headings"
    for column in tv1["columns"]:
        tv1.heading(column, text=column) # let the column heading = column name

    df_rows = df.to_numpy().tolist() # turns the dataframe into a list of lists
    for row in df_rows:
        tv1.insert("", "end", values=row) # inserts each list into the treeview. For parameters see https://docs.python.org/3/library/tkinter.ttk.html#tkinter.ttk.Treeview.insert
    return None


def clear_data():
    tv1.delete(*tv1.get_children())
    return None


root.mainloop()

rfrfr

nice one

You did a great job. thanks a lot

This code helped me a lot. I just wanted to ask, how do I get rid of an additional "unnamed" column without hard coding it?

This code helped me a lot. I just wanted to ask, how do I get rid of an additional "unnamed" column without hard coding it?

Hi Kiefah,
The code i wrote above will assume that the first line of the spreadsheet is a header. A better alternative is the tutorial I made on dragging and dropping files excel files directly into tkinter. There i inherit from a treeview object and extend its functionality so that it can be built by taking in a pandas dataframe via set_datatable

class DataTable(ttk.Treeview):
    def __init__(self, parent):
        super().__init__(parent)
        scroll_Y = tk.Scrollbar(self, orient="vertical", command=self.yview)
        scroll_X = tk.Scrollbar(self, orient="horizontal", command=self.xview)
        self.configure(yscrollcommand=scroll_Y.set, xscrollcommand=scroll_X.set)
        scroll_Y.pack(side="right", fill="y")
        scroll_X.pack(side="bottom", fill="x")
        self.stored_dataframe = pd.DataFrame()

    def set_datatable(self, dataframe):
        self.stored_dataframe = dataframe
        self._draw_table(dataframe)

    def _draw_table(self, dataframe):
        self.delete(*self.get_children())
        columns = list(dataframe.columns)
        self.__setitem__("column", columns)
        self.__setitem__("show", "headings")

        for col in columns:
            self.heading(col, text=col)

        df_rows = dataframe.to_numpy().tolist()
        for row in df_rows:
            self.insert("", "end", values=row)
        return None

    def find_value(self, pairs):
        # pairs is a dictionary
        new_df = self.stored_dataframe
        for col, value in pairs.items():
            query_string = f"{col}.str.contains('{value}')"
            new_df = new_df.query(query_string, engine="python")
        self._draw_table(new_df)

    def reset_table(self):
        self._draw_table(self.stored_dataframe)

Would I have to change everything to classes? Also, am I correct in thinking that the "def find_value" is the new method you implemented to deal with the issue of the extra column at the end? I was utilising your previous code so I'm just wondering.

you could put the class at the top of your code or import it from another file. def find_value is used to filter the columns.

Are you able to provide an attachment/example of the dataset you are using?

Hi there,

Hunted for this as I found the video a fantastic help.

One thing I am wondering is if there is a way that when selecting the spreadsheet to open/load, can I select a certain sheet within the spreadsheet to open?

TIA :)

Hi seancsmith89,

this is a crude implementation but you could have an entry box to specify the sheets

entry1 = tk.Entry(frame1)
entry1.pack(side = RIGHT)


def Load_excel_data():
    """If the file selected is valid this will load the file into the Treeview"""
    file_path = label_file["text"]
    try:
        excel_filename = r"{}".format(file_path)
        if excel_filename[-4:] == ".csv":
            df = pd.read_csv(excel_filename)
        else:
            df = pd.read_excel(excel_filename, sheet_name=entry1)  # Here you would enter the sheet you want.  

Hey, thanks for the script, I don´t know why I´m having an unsupported file issue whenever I try to load an .xlsx or .xls. Does anyone know what could be going on?

I got this error running my code in jupyter notebook in Mac OS big sure , i copied exactly the same code you used , can you please help me figure out the error?

Amazing vision for the script, and the incredible skills to make it a reality! Thank you. For the user to choose xlsx files however increases the variability in needing to select alternative tabs/worksheets, and would also increase complexity, as well as potential formatting issues available in xlsx, but not csv, perhaps/presumably encountered above, like with merged cells. Thinking of trying to restrict this variability by changing to only read csv files in order to minimize these types of variability. Thanks again for such a powerful demonstration of what's possible!

Eu recebi esse erro ao executar meu código no notebook jupyter no Mac OS grande com certeza, copiei exatamente o mesmo código que você usou, você pode me ajudar a descobrir o erro?

Hello @ounadi,
You must change 'filetype' to 'filetypes'.

Can 2 sheets be read from the same file and display onto the Treeview/Frame?

hi, why when i open file with .csv format, the program just show dialogbox "The file you have chosen is invalid"?