Select Specific Sheet from Python Imported Excel File-CodePudding

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

CodePudding user response：

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.

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

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

path = filedialog.askopenfilename()

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