通过要求用户从目录中选择Excel工作簿和工作表，将Excel文件转换为PANDAS数据框架

Question

我希望通过要求用户从其目录中选择文件来创建大熊猫的数据框架。不过，除了选择文件外，我还希望在文件中指定特定表格。如果只有一张纸，则自动选择第一个。

我已经尝试了以下代码：

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

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

file_path = filedialog.askopenfilename()

df = pd.read_excel(file_path, sheet_name = 1)
df

这起作用，但是它不能为用户提供选择他们想要使用的Excel文件的能力。也许应该弹出一个新窗口（如果有多个表格），并要求用户选择哪些表格可用。如果只有一个纸，则应自动选择第一张纸。

Answer 1

# 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)