将excel表格数据转换为字典

将如下的表格数据转换成字典形式
{‘列1’: ‘值1’, ‘列2’: ‘值2’, ‘列3’: ‘值3’}, {‘列1’: ‘值4’, ‘列2’: ‘值5’, ‘列3’: ‘值6’}]

pandas：

import pandas as pd

dict = {}
data = pd.read_excel(r'C:\Users\good day\Desktop\dic.xlsx')
data.fillna("",inplace = True)

list = []

for i in data.index.values:
    line = data.loc[i,['列1','列2','列3']].to_dict()
    list.append(line)
dict['data'] = list
print(dict)

xlrd：

import xlrd
def get_data(sheetnum):
    data = xlrd.open_workbook(r'C:\Users\good day\Desktop\dic.xlsx')
    table = data.sheets()[sheetnum]
    # print(table)
    nor = table.nrows
    nol = table.ncols
    dict = {}
    for i in range(1,nor):
        for j in range(nol):
            title = table.cell_value(0,j)
            value = table.cell_value(i,j)
            dict[title] = value
        yield dict

if __name__ == '__main__':
    for i in get_data(0):
        print(i)

存在合并单元格的数据处理：