select a column by its name - openpyxl

Question

This is my current code. As you can see that I'm fetching a value from every cell from each row by specifying the column index.

for row_cells in sheet.iter_rows(min_row=1, max_row=10):
   object = {"key1": str(row_cells[1].value), "Key2": str(row_cells[3].value),
                           "Key3": str(row_cells[4].value), "Key4": str(row_cells[2].value),
                           "Key5": str(row_cells[5].value)}

Now, in the given excel sheet, the first row always contains the titles of each column. Is there a way, where instead of specifying the indexes, I specify the names of the columns and then get a value in the cell. The .upper() method does give us the value of the uppermost cell of a column.

Answer 1

You can do this by first building a dictionary where the keys are the column names and the values are the column number. Then, use the dictionary to translate from the name to the number.

import openpyxl

workbook = openpyxl.load_workbook('Data/Test01.xlsx')
worksheet = workbook['Sheet1']

## Create a dictionary of column names
ColNames = {}
Current  = 0
for COL in worksheet.iter_cols(1, worksheet.max_column):
    ColNames[COL[0].value] = Current
    Current += 1

## Now you can access by column name
## (My data has a column named 'Dogs')
for row_cells in worksheet.iter_rows(min_row=1, max_row=4):
    print(row_cells[ColNames['Dogs']].value)

Answer 2

You can do this by iterating columns. Check for the column you need. And get the values.

import openpyxl
book = openpyxl.load_workbook(path)
sheet = book['Data']
column_name = 'Username'
for column_cell in sheet.iter_cols(1, sheet.max_column):  # iterate column cell
    if column_cell[0].value == column_name:    # check for your column
        j = 0
        for data in column_cell[1:]:    # iterate your column
            print(data.value)
        break