If:

您需要多个工作表，但不是全部 你需要一个df作为输出

然后，您可以传递一个工作表名称列表。你可以手动填充:

import pandas as pd
    
path = "C:\\Path\\To\\Your\\Data\\"
file = "data.xlsx"
sheet_lst_wanted = ["01_SomeName","05_SomeName","12_SomeName"] # tab names from Excel

### import and compile data ###
    
# read all sheets from list into an ordered dictionary    
dict_temp = pd.read_excel(path+file, sheet_name= sheet_lst_wanted)

# concatenate the ordered dict items into a dataframe
df = pd.concat(dict_temp, axis=0, ignore_index=True)

OR

如果你想要的工作表有一个通用的命名约定，也允许你区分不需要的工作表，那么一点自动化是可能的:

# substitute following block for the sheet_lst_wanted line in above block

import xlrd

# string common to only worksheets you want
str_like = "SomeName" 
    
### create list of sheet names in Excel file ###
xls = xlrd.open_workbook(path+file, on_demand=True)
sheet_lst = xls.sheet_names()
    
### create list of sheets meeting criteria  ###
sheet_lst_wanted = []
    
for s in sheet_lst:
    # note: following conditional statement based on my sheets ending with the string defined in sheet_like
    if s[-len(str_like):] == str_like:
        sheet_lst_wanted.append(s)
    else:
        pass

是的，不幸的是，它总是加载完整的文件。如果重复执行此操作，最好提取表以分离csv，然后分别加载。您可以使用d6tstack自动化该过程，它还添加了其他功能，如检查所有工作表或多个Excel文件中的所有列是否相等。

import d6tstack
c = d6tstack.convert_xls.XLStoCSVMultiSheet('multisheet.xlsx')
c.convert_all() # ['multisheet-Sheet1.csv','multisheet-Sheet2.csv']

参见d6tstack Excel示例

If:

您需要多个工作表，但不是全部 你需要一个df作为输出

然后，您可以传递一个工作表名称列表。你可以手动填充:

import pandas as pd
    
path = "C:\\Path\\To\\Your\\Data\\"
file = "data.xlsx"
sheet_lst_wanted = ["01_SomeName","05_SomeName","12_SomeName"] # tab names from Excel

### import and compile data ###
    
# read all sheets from list into an ordered dictionary    
dict_temp = pd.read_excel(path+file, sheet_name= sheet_lst_wanted)

# concatenate the ordered dict items into a dataframe
df = pd.concat(dict_temp, axis=0, ignore_index=True)

OR

如果你想要的工作表有一个通用的命名约定，也允许你区分不需要的工作表，那么一点自动化是可能的:

# substitute following block for the sheet_lst_wanted line in above block

import xlrd

# string common to only worksheets you want
str_like = "SomeName" 
    
### create list of sheet names in Excel file ###
xls = xlrd.open_workbook(path+file, on_demand=True)
sheet_lst = xls.sheet_names()
    
### create list of sheets meeting criteria  ###
sheet_lst_wanted = []
    
for s in sheet_lst:
    # note: following conditional statement based on my sheets ending with the string defined in sheet_like
    if s[-len(str_like):] == str_like:
        sheet_lst_wanted.append(s)
    else:
        pass

有以下几种选择:

将所有表格直接读入有序字典。

import pandas as pd

# for pandas version >= 0.21.0
sheet_to_df_map = pd.read_excel(file_name, sheet_name=None)

# for pandas version < 0.21.0
sheet_to_df_map = pd.read_excel(file_name, sheetname=None)

将第一页直接读入数据框架

df = pd.read_excel('excel_file_path.xls')
# this will read the first sheet into df

阅读excel文件并获得表格列表。然后选择和装载床单。

xls = pd.ExcelFile('excel_file_path.xls')

# Now you can list all sheets in the file
xls.sheet_names
# ['house', 'house_extra', ...]

# to read just one sheet to dataframe:
df = pd.read_excel(file_name, sheet_name="house")

阅读所有的表并将其存储在字典中。和第一个一样，但更明确。

# to read all sheets to a map
sheet_to_df_map = {}
for sheet_name in xls.sheet_names:
    sheet_to_df_map[sheet_name] = xls.parse(sheet_name)
    # you can also use sheet_index [0,1,2..] instead of sheet name.

感谢@ihightower指出了阅读所有表格的方法，感谢@toto_tico，@red-headphone指出了版本问题。

sheetname: string, int, string /int的混合列表，或None，默认为0 0.21.0版后已移除:使用sheet_name代替Source Link

如果你有兴趣阅读所有的表格并将它们合并在一起。最好最快的方法

sheet_to_df_map = pd.read_excel('path_to_file.xls', sheet_name=None)
mdf = pd.concat(sheet_to_df_map, axis=0, ignore_index=True)

这将把所有的表转换成一个单独的数据帧m_df

你也可以指定表名作为参数:

data_file = pd.read_excel('path_to_file.xls', sheet_name="sheet_name")

将只上传表"sheet_name"。