def read_excel_by_name(file_path, sheet_name, table_name): wb = xlrd.open_workbook(file_path) sheet = wb.sheet_by_name(sheet_name) col_val = sheet.col_values(0) nrows = sheet.nrows row_end = nrows - 1 for row in range(nrows): if col_val[row] == table_name: table_row = row row_start = table_row + 1 break for row in range(row_start, nrows): if col_val[row] == '': row_end = row - 1 break col_names = list(sheet.row_values(row_start)) while '' in col_names: col_names.remove('') lis = [] for row in range(row_start + 1, row_end + 1): row_values = sheet.row_values(row) if row_values: app = {} for i in range(len(col_names)): app[col_names[i]] = row_values[i] lis.append(app) return lis
调用方法:
read_excel_by_name('c:\\config.xls', 'Sheet1','student')
第一个参数为excel的路径包括excel名字
第二个参数为页签名Sheet1
第三个参数为表名