[FIXED] Error when trying to display excel file in tkinker python

Issue

I was reading about numpy and watching some videos about it, when I had an idea. The objective here is to read a CSV archive, extract few lines, the one with "rent" in "Housing" column and add the information to a new CSV file and display it in a "tkinker screen". Doing the initial research, I've found numpy, and ttk as a way to make it happen.

Unhappily, it doesn't work. Here's the code:

import pandas
import numpy as np
from tkinter import *
from tkinter import ttk, filedialog, messagebox

#----------------------------Tkinker init work----------------------------#
## window/ interface
window = Tk()
window.geometry("800x800")
window.pack_propagate(True)
window.resizable(0, 0)
title = window.title("House to You")

tip_label = Label(text="Clique no botão para ver os clientes selecionados pelos sistema ")
tip_label.grid(column=1, row=0, columnspan=3, padx=200, pady=50)

canvas = Canvas(width=300, height=300, highlightthickness=0)
frame = Frame(canvas)
frame.grid(column=1, row=1)

#----------------------------Data work----------------------------#
def import_data():
    """import data"""
    origin_data = pandas.read_csv("german_credit_data.csv", index_col=0)
    data = origin_data[origin_data["Housing"] == "rent"]
    customers = pandas.DataFrame(data=data)
    customers = customers.to_csv("Potential_customers.csv", index=False)
    return customers


def add_columns():
    """ initial transform data"""
    mail = pandas.read_csv("Potential_customers.csv")
    mail["email"] = "[email protected]"
    mail.to_csv("Potential_customers.csv", index=False)
    status_of = pandas.read_csv("Potential_customers.csv")
    status_of["Status"] = "waiting"
    full_csv = status_of.to_csv("Potential_customers.csv", index=False)
    return full_csv

def converter():
    df_new = pandas.read_csv('Potential_customers.csv')
    writer = pandas.ExcelWriter('Potential_customers.xlsx')
    df_new.to_excel(writer, index=False)

def file_open():
    """open the xlsx file"""
    converter()
    file = "Potential_customers.xlsx"
    my_tree = ttk.Treeview(window)
    my_tree.grid(column=1, columnspan=3, row=1, pady=20)
    try:
        df = pandas.read_excel(file)
    except Exception as e:
        messagebox.showerror("Oou", f"Something didn't work!\n{e}")
#tree
    my_tree.delete(*my_tree.get_children())
    my_tree['column'] = list_of_columns
    my_tree['show'] = 'headings'
    for col in my_tree['column']:
        my_tree.heading(col, text=col)
    df_rows = df.to_numpy().tolist()
    for row in df_rows:
        my_tree.insert("", "end", values=row)




view_button = Button(window, text="Ver clientes", bg="green", command=file_open)
view_button.grid(column=1, row=2, padx=300)

import_data()
add_columns()
window.mainloop()

In my second research, I've found out someone saying that this method only works with XLSX files. So I've written a function to convert the file. It's called in the first line of open file function. I did another minor change to make it easier to read.

The error changes, it was saying that:

ValueError: Excel file format cannot be determined, you must specify an engine manually.

I've caught this error in an exception, also looked for more info and adapt this as a possible solution:

my_tree.delete(*my_tree.get_children())

Well... It doesn't work. How can I display the file?

Solution

You forget to close the Excel file inside converter(), so the output file is empty:

def converter():
    df_new = pandas.read_csv('Potential_customers.csv')
    writer = pandas.ExcelWriter('Potential_customers.xlsx')
    df_new.to_excel(writer, index=False)
    writer.close()

Or use context manager:

def converter():
    df_new = pandas.read_csv('Potential_customers.csv')
    with pandas.ExcelWriter('Potential_customers.xlsx') as writer:
        df_new.to_excel(writer, index=False)

Answered By - acw1668