Convert an Excel File to a Pandas Data Frame by Asking User to Choose Excel Workbook and Worksheet From Directory

user_18084286 0
Viewed 156

I'm looking to create a Pandas data frame by asking the user to choose a file from their directory. In addition to choosing the file, though, I'm looking to also specify a specific sheet in the file. If only one sheet exists, then automatically choose the first one.

I have tried the code below:

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

This works, but it doesn't provide the user the ability to choose which sheet of the excel file they'd like to use. Perhaps a new window should pop up (if there's more than one sheet) and ask the user to select which sheet names are available. If only one sheet is available, then it should automatically choose the first sheet.

openpyxl pandas dataframe dialog python
1 Answers
# 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)
user_180842860
Related