# 如果xlrd运行报错，先卸载当前安装的xlrd
# pip uninstall xlrd
# 再安装低版本的xlrd
# pip install xlrd==1.2.0

import xlrd
import sys
import os

def read_excel_dict(excelPath,sheetName):

    if not os.path.exists(excelPath):
        print("ERROR!\nexcel不存在")
        sys.exit()

    wb = xlrd.open_workbook(excelPath)  # 打开文件
    sheet_names = wb.sheet_names()      # 获取所有工作表名称

    # 判断输入表名是否实际存在于excel内
    if sheetName not in sheet_names:
        print("ERROR!\n输入表名错误，输入表名为：{}, 实际表名为：{}".format(sheetName, sheet_names))
        sys.exit()

    table = wb.sheet_by_name(sheetName)
    key = table.row_values(0)   # 取第一行作为key值
    rowNum = table.nrows        # 获取总行数
    colNum = table.ncols        # 获取总列数

    if rowNum <= 1:
        print("数据为空，总行数小于1")
    else:
        r = []
        x = 1
        for row in range(1,rowNum):
            cell = {}
            # cell['rowNum'] = row
            values = table.row_values(x)
            for col in range(colNum):
                cell[key[col]] = values[col]
            r.append(cell)
            x += 1
        return r

def read_excel_list(excelPath,sheetName):

    if not os.path.exists(excelPath):
        print("ERROR!\nexcel不存在")
        sys.exit()

    wb = xlrd.open_workbook(excelPath)  # 打开文件
    sheet_names = wb.sheet_names()      # 获取所有工作表名称

    # 判断输入表名是否实际存在于excel内
    if sheetName not in sheet_names:
        print("ERROR!\n输入表名错误，输入表名为：{}, 实际表名为：{}".format(sheetName, sheet_names))
        sys.exit()

    table = wb.sheet_by_name(sheetName)
    key = table.row_values(0)   # 取第一行作为key值
    rowNum = table.nrows        # 获取总行数
    colNum = table.ncols        # 获取总列数

    if rowNum <= 1:
        print("数据为空，总行数小于1")
    else:
        r = []
        x = 1
        for row in range(1,rowNum):
            cell = []
            values = table.row_values(x)
            for col in range(colNum):
                cell.append(values[col])
            r.append(cell)
            x += 1
        return r



if __name__ == "__main__":
    filepath = "../Data/body.xlsx"
    sheetName = "Sheet1"

    data = read_excel_dict(filepath, sheetName)
    print(data)

    data = read_excel_list(filepath, sheetName)
    print(data)