pd.read_excel('filename.xlsx') 

默认情况下，读取工作簿的第一张。

pd.read_excel('filename.xlsx', sheet_name = 'sheetname') 

阅读练习册上的具体表格

pd.read_excel('filename.xlsx', sheet_name = None) 

将所有工作表从excel读取到pandas数据帧作为OrderedDict的类型，意味着嵌套的数据帧，所有工作表作为数据帧收集在数据帧内，它的类型是OrderedDict。

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

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

pd.read_excel('filename.xlsx') 

默认情况下，读取工作簿的第一张。

pd.read_excel('filename.xlsx', sheet_name = 'sheetname') 

阅读练习册上的具体表格

pd.read_excel('filename.xlsx', sheet_name = None) 

将所有工作表从excel读取到pandas数据帧作为OrderedDict的类型，意味着嵌套的数据帧，所有工作表作为数据帧收集在数据帧内，它的类型是OrderedDict。

有以下几种选择:

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

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

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

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

将只上传表"sheet_name"。

根据用例有不同的选项:

如果你不知道床单的名字。 如果表名不相关。 如果有人知道床单的名字。

下面我们将仔细研究每个选项。

有关查找表名等信息，请参阅Notes部分。

选项1

如果你不知道床单的名字

# Read all sheets in your File
df = pd.read_excel('FILENAME.xlsx', sheet_name=None)
    
# Prints all the sheets name in an ordered dictionary
print(df.keys())

然后，根据想要读取的表，可以将每个表传递到特定的数据帧，例如

sheet1_df = pd.read_excel('FILENAME.xlsx', sheet_name=SHEET1NAME)
sheet2_df = pd.read_excel('FILENAME.xlsx', sheet_name=SHEET2NAME)

选项2

如果名字无关紧要，人们只关心纸张的位置。假设一个人只想要第一页

# Read all sheets in your File
df = pd.read_excel('FILENAME.xlsx', sheet_name=None)

sheet1 = list(df.keys())[0]

然后，根据表名，可以将每个表传递给特定的数据框架，例如

sheet1_df = pd.read_excel('FILENAME.xlsx', sheet_name=SHEET1NAME)

选项3

这里我们将考虑已知床单名称的情况。 对于示例，可以考虑有三个表，分别为Sheet1、Sheet2和Sheet3。每一个的内容都是一样的，看起来像这样

     0         1     2
0   85   January  2000
1   95  February  2001
2  105     March  2002
3  115     April  2003
4  125       May  2004
5  135      June  2005

根据个人的目标，有多种方法:

Store everything in same dataframe. One approach would be to concat the sheets as follows sheets = ['Sheet1', 'Sheet2', 'Sheet3'] df = pd.concat([pd.read_excel('FILENAME.xlsx', sheet_name = sheet) for sheet in sheets], ignore_index = True) [Out]: 0 1 2 0 85 January 2000 1 95 February 2001 2 105 March 2002 3 115 April 2003 4 125 May 2004 5 135 June 2005 6 85 January 2000 7 95 February 2001 8 105 March 2002 9 115 April 2003 10 125 May 2004 11 135 June 2005 12 85 January 2000 13 95 February 2001 14 105 March 2002 15 115 April 2003 16 125 May 2004 17 135 June 2005 Basically, this how pandas.concat works (Source): Store each sheet in a different dataframe (let's say, df1, df2, ...) sheets = ['Sheet1', 'Sheet2', 'Sheet3'] for i, sheet in enumerate(sheets): globals()['df' + str(i + 1)] = pd.read_excel('FILENAME.xlsx', sheet_name = sheet) [Out]: # df1 0 1 2 0 85 January 2000 1 95 February 2001 2 105 March 2002 3 115 April 2003 4 125 May 2004 5 135 June 2005 # df2 0 1 2 0 85 January 2000 1 95 February 2001 2 105 March 2002 3 115 April 2003 4 125 May 2004 5 135 June 2005 # df3 0 1 2 0 85 January 2000 1 95 February 2001 2 105 March 2002 3 115 April 2003 4 125 May 2004 5 135 June 2005

注:

If one wants to know the sheets names, one can use the ExcelFile class as follows sheets = pd.ExcelFile('FILENAME.xlsx').sheet_names [Out]: ['Sheet1', 'Sheet2', 'Sheet3'] In this case one is assuming that the file FILENAME.xlsx is on the same directory as the script one is running. If the file is in a folder of the current directory called Data, one way would be to use r'./Data/FILENAME.xlsx' create a variable, such as path as follows path = r'./Data/Test.xlsx' df = pd.read_excel(r'./Data/FILENAME.xlsx', sheet_name=None) This might be a relevant read.