从 Python 导入的 Excel 文件中 Select 特定工作表

发布于06月15日

以下代码要求用户 Select 要导入为Pandas 数据框的excel文件；但是，它不能 Select 哪一张图纸(如果存在多张):

import pandas as pd
import tkinter as tk
from tkinter import filedialog

root = tk.Tk()
root.withdraw()

path = filedialog.askopenfilename()

x = pd.read_excel(path, sheet_name = 1)
x

新解决方案中包含的条件:

如果只有一张工作表，则自动 Select 并上载到Pandas 数据框
如果存在多张图纸，允许用户通过对话框 Select 要导入的图纸

推荐答案

@GordonAitchJay提供的解决方案以及与Tkinter的实现非常出色.如果您直接使用Python或在Spyder这样的IDE中运行脚本，那么这绝对是一种好方法.

然而，OP正在Jupyter工作，结果证明Jupyter和Tkinter相处得不太好.OP表达了一些困难，虽然我一开始确实让它工作了，但如果我推动代码的性能，我也会注意到严重的滞后和打嗝.在这种情况下，我想我应该添加一种方法，通过使用ipywidgets框架使交互在Jupyter中顺利工作.

# Jupyter notebook

import pandas as pd
import ipywidgets as widgets
from IPython.display import clear_output
from ipyfilechooser import FileChooser
from ipywidgets import interact
from pathlib import Path

# get home dir of user
home = str(Path.home()) 

# initialize a dict for the excel file; this removes the need to set global values
dict_file = {}

# change to simply `home` if you want users to navigate through diff dirs
fc = FileChooser(f'{home}/excel') 

# same here
fc.sandbox_path = f'{home}/excel'

# limit file extensions to '.xls, .xlsb, .xlsm, .xlsx'
fc.filter_pattern = ['*.xls*']
fc.title = '<b>Select Excel file</b>'
display(fc)

# create empty dropdown for sheet names
dropdown = widgets.Dropdown(options=[''], value='', description='Sheets:', disabled=False)

# create output frame for the df
out = widgets.Output(layout=widgets.Layout(display='flex', flex_flow='column', align_items='flex-start', width='100%'))

# callback func for FileChooser
def get_sheets(chooser): 

    # (re)populate dict
    dict_file.clear() 
    dict_file['file'] = pd.ExcelFile(fc.value)
    sheet_names = dict_file['file'].sheet_names
    
    # only 1 sheet, we'll print this one immediate (further below)
    if len(sheet_names) == 1:
        
        # set value of the dropdown to this sheet
        dropdown.options = sheet_names
        dropdown.value = sheet_names[0]
        
        # disable the dropdown; so it's just showing the selection to the user
        dropdown.disabled = True
    else:
        
        # append empty string and set this as default; this way the user must always make a deliberate choice
        sheet_names.append('')
        dropdown.options = sheet_names
        dropdown.value = sheet_names[-1]
        
        # allow selection by user
        dropdown.disabled = False
    return

# bind FileChooser to callback
fc.register_callback(get_sheets)

# prompt on selection sheet
def show_df(sheet):
    if sheet == '':
        if out != None:
            # clear previous df, when user selects a new wb
            out.clear_output()
    else:
        # clear previous output 'out' frame before displaying new df, else they'll get stacked
        out.clear_output()
        with out:
            df = dict_file['file'].parse(sheet_name=sheet)
            if len(df) == 0:
                # if sheet is empty, let the user know
                display('empty sheet')
            else:
                display(df)
    return

# func show_df is called with input of widget as param on selection sheet
interact(show_df, sheet=dropdown)

# display 'out' (with df)
display(out)

笔记本中的互动片段: