Created
August 8, 2015 00:59
-
-
Save dangtrinhnt/d0c7d50967b6213adeaa to your computer and use it in GitHub Desktop.
Excel file to list of dictionaries in Python
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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])