Create a dictionary where key would be the header_name from excel sheet using openpyxl

Question

from openpyxl import *
Variable_Model_Path=defaultdict(list)
Sheet_Name=wb.get_sheet_by_name(Sheet)
for row in Sheet_Name.iter_rows():
    Row=[cell.value for cell in row]
    for cell_ind,cell in enumerate(Row):
        print cell

Above is my sample code.

I have an excel sheet which have headers var1, var2 ,var3. I want to create a dictionary {Var1:1,4,7,10} Where Var1 would be header name and the values would be corresponding column values. I am using openpyxl module.Here variable names and values would be given by the user and they will be dynamic.

Var1 Var2 Var3

1 2 3
4 5 6
7 8 9
10 11 12

I want to represent header's as key and the corresponding column values as values to form a dictionary. — user1681102

Romain Jouin Romain Jouin · Accepted Answer · 2017-03-22T22:49:03

This :

from openpyxl    import *
from collections import defaultdict
path  = "/Users/romain/Desktop/Classeur2.xlsx"
Sheet = "Feuil1"
wb    = load_workbook(path)

Variable_Model_Path = defaultdict(list)
Sheet_Name          = wb.get_sheet_by_name(Sheet)
keys                = False

for row in Sheet_Name.iter_rows():
    if not keys:
        keys = [cell.value for cell in row]
        continue
    Row=[cell.value for cell in row]
    for cell_ind,cell in enumerate(Row):
        Variable_Model_Path[keys[cell_ind]].append( cell)

gives me :

Variable_Model_Path
defaultdict(list,
            {u'var 1': [1L, 4L, 7L],
             u'var 2': [2L, 5L, 8L],
             u'var 3': [3L, 6L, 9L]})

Create a dictionary where key would be the header_name from excel sheet using openpyxl

1 Answers