获取excel数据并按照名称组成字典

excel数据:模块名不能 test1 test2 test1这样进行穿插

代码解析结果:此举主要是为了通过模块把数据进行分类,在进行测试的时候可以根据模块进行数据驱动测试
此结果主要是为了通过映射关系对比数据

{'test1': [{1: [100, None]}, {2: [101, None]}], 'test2': [{3: [102, None]}, {4: [103, None]}], 'test3': [{5: [104, None]}, {6: [105, None]}]}

代码实现

import openpyxl

file_patch = 'excel路径'
def read_excel(sheets):
    wb = openpyxl.load_workbook(file_patch)
    sheet = wb[sheets]
    map = {}
    lists = []
    for i in range(2, sheet.max_row + 1):
        dicts = {}
        if sheet.cell(i, 1).value not in map:
            map.setdefault(sheet.cell(i, 1).value)
            lists = []
        if sheet.cell(i, 1).value in map:
            key = sheet.cell(i, 2).value
            value1 = sheet.cell(i, 3).value
            value2 = sheet.cell(i, 4).value
            dicts[key] = [value1, value2]
            lists.append(dicts)
            map[sheet.cell(i, 1).value] = lists
    return map

if __name__ == '__main__':
    map = read_excel('Sheet1')
    print(map)

这是去掉了模块名

def read_excel(sheets, module='mod'):
    wb = openpyxl.load_workbook(file_patch)
    sheet = wb[sheets]
    lists = []
    for i in range(2, sheet.max_row + 1):
        dicts = {}
        if sheet.cell(i, 1).value == module:
            key = sheet.cell(i, 2).value
            value1 = sheet.cell(i, 3).value
            value2 = sheet.cell(i, 4).value
            dicts[key] = [value1, value2]
            lists.append(dicts)
        elif 'mod' == module:
            key = sheet.cell(i, 2).value
            value1 = sheet.cell(i, 3).value
            value2 = sheet.cell(i, 4).value
            dicts[key] = [value1, value2]
            lists.append(dicts)
    return lists

if __name__ == '__main__':
    map = read_excel1('Sheet1', 'test1')  # 不填写module,会返回所有的数据
    print(map)