8、利用xlrd读取excel数据

在同一个sheet表中可以有多个表，表与表之间用空格来隔离，遇到空格，则认为这个表结束了。

代码如下：

import xlrd
file_path = "C:/Users/Administrator/Desktop/python/excel/1.xlsx"
sheet_name = '表2'
table = '学生表'
def read_excel_by_name(file=file_path,sheet_name=sheet_name,table_name=table):
    wb = xlrd.open_workbook(file)
    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

s = read_excel_by_name(file=file_path,sheet_name=sheet_name,table_name=table)

print(s)

读取配置文件：

代码如下：

#coding=utf-8
import xlrd
import sys,os
def read_inparam(classname):
    wb = xlrd.open_workbook('D:\\workspace\\Myproject\\testconfig.xlsx')
    sheet = wb.sheet_by_name("Sheet2")
    rows = sheet.nrows
    list = []
    for i in range(0,rows):
        cols = sheet.col_values(0)
        if cols[i] == "":
            break
        if cols[i] == classname:
            inputfile = sheet.row_values(i)[2]
            presetfile = sheet.row_values(i)[3]
            list.append(inputfile)
            list.append(presetfile)
            return list
    return list


def  test():
    classname = __file__  #获取当前文件名字Scene_02
    classname = classname.split("/")[-1].split(".")[0]
    list = read_inparam(classname)
    print(list)


if __name__ == '__main__':
    test()