Excel file to list of dictionaries in Python

excel_utils.py

import openpyxl
import string


def index_to_col(index):
    return string.uppercase[index]

def excel_to_dict(excel_path, headers=[]):
    wb = openpyxl.load_workbook(excel_path)
    sheet = wb.get_sheet_by_name('Sheet1')
    result_dict = []
    for row in range(2, sheet.get_highest_row() +1):
        line = dict()
        for header in headers:
            cell_value = sheet[index_to_col(headers.index(header)) + str(row)].value
            if type(cell_value) is unicode:
                cell_value = cell_value.encode('utf-8').decode('ascii', 'ignore')
                cell_value = cell_value.strip()
            elif type(cell_value) is int:
                cell_value = str(cell_value)
            elif cell_value is None:
                cell_value = ''
            line[header] = cell_value
        result_dict.append(line)
    return result_dict

max_row=sheet.max_row
sheet.get_highest_row() seems not to work

Perhaps was in python 2 and older openpyxl

I found this post very useful
My working code

import openpyxl

selhdrs=['VR_NO','CATE','COLL','REG_NO','REG_YR','S_NAME', 'SEX','F_NAME','MOB_NO']

headers=[]

result_dict = []

https://gist.github.com/dangtrinhnt/d0c7d50967b6213adeaa#file-excel_utils-py

https://www.dangtrinh.com/2015/08/excel-to-list-of-dictionaries-in-python.html

wb = openpyxl.load_workbook('ERROR.xlsx')
sheet = wb['ERROR']

for colnum in range(1,sheet.max_column +1):
headers.append(sheet.cell(column=colnum,row=1).value)

print(headers)

for row in range(2, sheet.max_row +1):
line = dict()
for onehdr in selhdrs:
cell_value = sheet.cell(column=headers.index(onehdr)+1,row=row).value
if type(cell_value).name == 'str':
cell_value = cell_value.strip()
elif type(cell_value).name == 'int':
cell_value = str(cell_value)
elif cell_value is None:
cell_value = ''
line[onehdr] = cell_value
result_dict.append(line)

print(result_dict[0])