转载 https://www.jianshu.com/p/f2c9dff344c6
pip install xlrd
# -*- coding:utf-8 -*- import xlrd """ 打开excel表格""" workbook = xlrd.open_workbook("register.xls") print(workbook)#<xlrd.book.Book object at 0x0000016B715F68E0> """ 获取所有sheet名称""" sheet_names = workbook.sheet_names() print(sheet_names)#['Sheet1', 'Sheet2', 'Sheet3', 'Sheet4'] """ 获取所有或某个sheet对象""" # 获取所有的sheet对象 sheets_object = workbook.sheets() print(sheets_object) #[Sheet 0:<Sheet1>, Sheet 1:<Sheet2>, Sheet 2:<Sheet3>, Sheet 3:<Sheet4>] # 通过index获取第一个sheet对象 sheet1_object = workbook.sheet_by_index(0) print(sheet1_object) #Sheet 0:<Sheet1> # 通过name获取第一个 name 对象 sheet1_name = workbook.sheet_by_name("Sheet1") #单个的是sheet_by_name 不加 s print(sheet1_name) #Sheet 0:<Sheet1> """ 判断某个sheet是否已导入""" # 通过index判断sheet1是否导入 sheet_is_load = workbook.sheet_loaded(sheet_name_or_index=2) print(sheet_is_load) #True # 通过 sheet名称判断sheet1是否导入 sheet1_is_load = workbook.sheet_loaded(sheet_name_or_index="Sheet1") print(sheet1_is_load) #True """对sheet 对象中执行操作 如有效行数、某行从n1到n2的数据、某行的单元和类型、某行的类型...""" #获取sheet1中的有效行数 # sheet1_object = workbook.sheet_by_index(0) sheet1_object = workbook.sheet_by_name("Sheet1") nrows = sheet1_object.nrows print(nrows) #15 #获取Sheet1中第3行数据 all_row_values = sheet1_object.row_values(3) print(all_row_values) #获取sheet1中第三行的单元 row_slice = sheet1_object.row_slice(rowx=2) print(row_slice) #[text:'s3', text:'s4', text:'s5', text:'s6', text:'s7'] #获取sheet1中第三行的单元类型 row_type = sheet1_object.row_types(rowx=3) print(row_type) #array('B', [1, 1, 1, 1, 1]) #获取sheet1中的第三行的长度 row_len = sheet1_object.row_len(rowx=3) print(row_len) #5 #获取sheet1所有行的生成器 rows_all = sheet1_object.get_rows() print(rows_all) #<generator object Sheet.get_rows.<locals>.<genexpr> at 0x000001F04E084E40> """对sheet对象中的列执行操作""" #获取sheet1中的有效列数 ncols = sheet1_object.ncols print(ncols) #5 #获取sheet1中第一 colx+1列的数据 cols_valus = sheet1_object.col_values(colx=1) #貌似只能读取第一列的值 print(cols_valus) #['ssssss3', 's4', 's5', 's6', 's7', 's8', 's9', 's10', 's11', 's12', 's13', 's14', 's15', 's16', 's17'] cols_values1 = sheet1_object.col_values(1,1,3) #第一个参数表示(行,开始位置，结束位置) print(cols_values1) #['s3', 's4'] #获取sheet1中第2列的单元类型 cols2_type = sheet1_object.col_types(2) print(cols2_type) #[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1] """对sheet对象中的单元进行操作""" #获取sheet1中的rowx+1行，第cols+1列的单元对象 cel1_info = sheet1_object.cell(rowx=1,colx=2) print(cel1_info) #text:'s4' #获取sheet1中第rowx+1行，第colx+1列的单元值 cel1_value = sheet1_object.cell_value(rowx=1,colx=2) print(cel1_value) #s4 # 获取sheet1中第rowx+1行，第colx+1列的单元类型值 rowx1_colx1 = sheet1_object.cell_type(rowx=1,colx=2) print(rowx1_colx1) #1 sheet4_path = xlrd.open_workbook("register.xls") sheet_all_name = sheet4_path.sheet_names() sheet4_table = sheet4_path.sheet_by_index(3) print(sheet_all_name) print(sheet4_table) 读取sheet4 读取第二行， 读取第二列 sheet4_for = sheet4_table.nrows sheet4_for2 = sheet4_table.ncols print("====") print(sheet4_for) print(sheet4_for2)