使用pandas pd.Excel文件和用户输入的文件夹路径和文件名

使用Pathlib，os并且pandas和一些功能。

您需要的关键功能之一是while True不断执行代码块，直到它变为真并启动一个break

随时根据自己的规范进行编辑。

引用库

from pathlib import Path
import os
import pandas as pd
from xlrd import XLRDError

读取

df = load_parser()

out:
#Hello Umar.Hussain please enter a valid target directory
#C:\Users\UmarH\Files
#1 excels_0
#2 excels_1
#Choose a number between 1 and 2
1
#Your Choice is excels_0.xlsx
#Choose a Sheet - Lists all sheets
'Sheet1'
# returns dataframe

主要功能

def load_parser():

    user = os.getlogin()

    print(f"Hello {user} please enter a valid target directory") 
    cmd = input('')

    p = file_tester(cmd,file_type='path')
    print("Please select a number from the following file")
    target_file = create_excel_dict(p)
    target_df = enumerate_sheets(target_file)



    return target_df

辅助功能

def file_tester(string_path, file_type="path"):
    path = Path(string_path)

    while True:
        if path.is_dir():
            break
        else:
            cmd = input(f"Please Enter a Valid {file_type}")
            path = Path(cmd)

    return path


def create_excel_dict(target_path):
    xlsx_dict = {i: x for i, x in enumerate(target_path.glob('*.xlsx'), 1)}

    for k,v in xlsx_dict.items():
        print(k,v.stem)

    rng = [i for i in xlsx_dict.keys()]

    file_choice = input(f'Choose a number between {rng[0]} and {rng[-1]}')

    while True:
        try:
            file_choice = int(file_choice)
            print(f"Your Choice is {xlsx_dict[file_choice]}")
            break
        except KeyError:
            file_choice = input(f'Choose a number between {rng[0]} and {rng[-1]}')

    return xlsx_dict[file_choice]

def enumerate_sheets(target_file):

    xl = pd.ExcelFile(target_file)

    for sheet in xl.sheet_names:
        print(sheet)
    target_sheet = input("Please Type Your sheet name")

    while True:
        try:
            df = pd.read_excel(xl,sheet_name=target_sheet)
            break
        except XLRDError:
            target_sheet = input("Please enter a sheet from above.")

    return df

回答来源stackoverflow