Select Specific Sheet from Python Imported Excel File

user_18084286 0
Viewed 448

The following code requests the user to select an excel file they'd like to import as a pandas data frame; however, it doesn't provide the ability to select which sheet (if multiple exist):

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

Conditions to include in new solution:

  • If only one sheet exists, automatically select and upload to pandas data frame
  • If multiple sheets exists, allow user to choose through a dialog box which sheet they'd like to import
openpyxl pandas dataframe python
2 Answers

The solution offered by @GordonAitchJay, and implementation with Tkinter, is excellent. Definitely the way to go, if you're running the script directly with Python or in an IDE like Spyder.

However, the OP is working in Jupyter and it turns out that Jupyter and Tkinter do not get along very well. The OP expressed difficulties, and while I do get it to work at first, if I push the code for performance, I'm also noticing serious lags and hiccups. This being the case, I thought I would just add a way to make the interaction work smoothly in Jupyter by using the ipywidgets framework.

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

Snippet of interaction in notebook:

nb interaction df from excel file

user_184706920

If that's all you need tkinter for, this will do.

It shows a simple combobox with the sheetnames. In this case, the first sheetname is named Orders.

As soon as you select an item, the window closes and it parses that sheet.

Tkinter combobox

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

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

# path = filedialog.askopenfilename()
# limit user input to Excel file (or path == '' in case of "Cancel")
path = filedialog.askopenfilename(filetypes = [('Excel files', '*.xls*')])

# if user didn't cancel, continue
if path != '':
    # Get the sheetnames first without parsing all the sheets
    excel_file = pd.ExcelFile(path)
    sheet_names = excel_file.sheet_names
    sheet_name = None
    
    if len(sheet_names) == 1:
        sheet_name = sheet_names[0]
    elif len(sheet_names) > 1:
        # Show the window again
        root.deiconify()
        root.minsize(280, 30)
        root.title('Select sheet to open')
        
        # Create a combobox with the sheetnames as options to select
        combotext = tk.StringVar(value=sheet_names[0])
        box = ttk.Combobox(root, 
                           textvariable=combotext, 
                           values=sheet_names, 
                           state='readonly')
        box.pack()
        
        # This function gets called when you select an item in the combobox
        def callback_function(event):
            # Mark sheet_name as global so it doesn't just make a new local variable
            global sheet_name
            sheet_name = combotext.get()
            
            # Close tkinter so Python can continue execution after root.mainloop()
            root.destroy()
        
        root.bind('<<ComboboxSelected>>', callback_function)
        root.mainloop()
    
    # Finally, parse the selected sheet
    # This is equivalent to pd.read_excel
    df = excel_file.parse(sheet_name=sheet_name)
user_35891220
Related