Python Tkinter显示Excel文件报错，求解决方案

学习numpy时想要实现以下功能：读取CSV文件，提取Housing列为"rent"的行数据存入新CSV文件，并在Tkinter界面中展示。使用pandas和ttk实现后运行失败，将CSV转为XLSX时出现错误：

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

尝试添加my_tree.delete(*my_tree.get_children())语句仍无法解决，求正确的文件展示方法。

原代码如下：

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"] = "customers@mail.com"
    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()

1. 修复Excel转换错误 #

出现该错误的核心原因：

• pandas.ExcelWriter处理xlsx格式时需要指定引擎（如openpyxl）
• 未调用writer.save()完成文件写入

先安装依赖：

pip install openpyxl

修正后的converter函数：

def converter():
    df_new = pandas.read_csv('Potential_customers.csv')
    # 指定openpyxl引擎，完成写入后保存文件
    writer = pandas.ExcelWriter('Potential_customers.xlsx', engine='openpyxl')
    df_new.to_excel(writer, index=False)
    writer.save()

2. 直接读取CSV展示（更高效） #

没必要额外转换为XLSX，直接读取处理后的CSV即可完成展示，减少冗余操作：

def file_open():
    """直接读取CSV并在Treeview展示"""
    file = "Potential_customers.csv"
    my_tree = ttk.Treeview(window)
    my_tree.grid(column=1, columnspan=3, row=1, pady=20)
    try:
        df = pandas.read_csv(file)
        # 清空Treeview旧数据
        my_tree.delete(*my_tree.get_children())
        # 获取列名并设置
        list_of_columns = df.columns.tolist()
        my_tree['column'] = list_of_columns
        my_tree['show'] = 'headings'
        # 设置表头
        for col in list_of_columns:
            my_tree.heading(col, text=col)
        # 插入行数据
        df_rows = df.to_numpy().tolist()
        for row in df_rows:
            my_tree.insert("", "end", values=row)
    except Exception as e:
        messagebox.showerror("Oou", f"Something didn't work!\n{e}")

3. 优化数据处理流程 #

原代码中import_data和add_columns重复读写CSV，合并为一个函数减少IO操作：

def process_data():
    """一次性完成数据提取和列添加"""
    try:
        origin_data = pandas.read_csv("german_credit_data.csv", index_col=0)
        data = origin_data[origin_data["Housing"] == "rent"]
        data["email"] = "customers@mail.com"
        data["Status"] = "waiting"
        data.to_csv("Potential_customers.csv", index=False)
    except Exception as e:
        messagebox.showerror("Erro", f"Falha ao processar dados:\n{e}")

4. 修复界面布局问题 #

原代码中canvas和frame未实际使用，且Treeview会覆盖提示标签，调整布局确保元素正常显示：

# 移除无用的canvas和frame，优化提示标签的padding
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=100, pady=20)

完整修正代码 #

import pandas
from tkinter import *
from tkinter import ttk, messagebox

#----------------------------Tkinker init work----------------------------#
window = Tk()
window.geometry("800x800")
window.resizable(0, 0)
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=100, pady=20)

#----------------------------Data work----------------------------#
def process_data():
    """一次性完成数据提取和列添加"""
    try:
        origin_data = pandas.read_csv("german_credit_data.csv", index_col=0)
        data = origin_data[origin_data["Housing"] == "rent"]
        data["email"] = "customers@mail.com"
        data["Status"] = "waiting"
        data.to_csv("Potential_customers.csv", index=False)
    except Exception as e:
        messagebox.showerror("Erro", f"Falha ao processar dados:\n{e}")

def file_open():
    """读取CSV并在Treeview展示"""
    file = "Potential_customers.csv"
    # 销毁旧Treeview避免重复创建
    for widget in window.grid_slaves(row=1):
        if isinstance(widget, ttk.Treeview):
            widget.destroy()
    
    my_tree = ttk.Treeview(window)
    my_tree.grid(column=1, columnspan=3, row=1, pady=20, padx=20, sticky=NSEW)
    
    # 添加垂直滚动条
    scrollbar = ttk.Scrollbar(window, orient=VERTICAL, command=my_tree.yview)
    scrollbar.grid(column=4, row=1, sticky=NS)
    my_tree.configure(yscrollcommand=scrollbar.set)
    
    try:
        df = pandas.read_csv(file)
        list_of_columns = df.columns.tolist()
        my_tree['column'] = list_of_columns
        my_tree['show'] = 'headings'
        
        # 设置表头和列宽
        for col in list_of_columns:
            my_tree.heading(col, text=col)
            my_tree.column(col, width=100)
        
        # 插入数据行
        df_rows = df.to_numpy().tolist()
        for row in df_rows:
            my_tree.insert("", "end", values=row)
    except Exception as e:
        messagebox.showerror("Oou", f"Something didn't work!\n{e}")

#----------------------------UI Elements----------------------------#
view_button = Button(window, text="Ver clientes", bg="green", command=file_open)
view_button.grid(column=1, row=2, pady=20)

# 初始化处理数据
process_data()

window.mainloop()

内容的提问来源于stack exchange，提问作者Milla Dma