How to use field name or column header in openpyxl?

Question

See my code below. This code works very well, but I would like to do two things. One thing I did if an expression with or is much shorter than the actual one, for example. I have many such columns, not all are close to each other. I would like it to be shorter. Also, sometimes I may not know the exact letter in the column.

So, I want to know if there is a way to find out the column name or heading. Like the values ​​that would be on the very top line. Therefore, I can check if this is one of these values, always execute the function in this cell if it is in the specified column. I cannot find the openpyxl function to create a column name. Not sure if he understands that the first line is different from the rest. I think, maybe, if it weren’t for me, I could try to run the test on the first line, but I don’t understand how to do it.

So is there a way to call the column name? or if there is no way to call the column name for validation, can someone help me do the validation on the first row to find out if it matters? then will you change the correct line I'm in? It makes sense.

So, instead of code:

if cellObj.column == 'H' or ...

He would say:

if cellObj.column_header == 'NameOfField or ...

Or, if this cannot be done, then:

if this cell has column where first row value is 'NameOfField' ...

, . stackoverflow , , , ( ).

for row in sheet.iter_rows():
 for cellObj in row:
    if cellObj.column == 'H' or cellObj.column == 'I' or cellObj.column == 'L' or cellObj.column == 'M':
        print(cellObj.value),
        if cellObj.value.upper() == 'OldValue1':
            cellObj.value = 1
            print(cellObj.value)
        elif cellObj.value.upper() == 'OldValue2':
            cellObj.value = 2
            print(cellObj.value)

Answer 1

, , :

colnames = ['Header1', 'Header2', 'Header3']

:

col_indices = {n for n, cell in enumerate(sheet.rows[0]) if cell.value in colnames}

:

for row in sheet.rows[1:]:
    for index, cell in enumerate(row):
         if index in col_indices:
             if cell.value.upper() == 'OldValue1':
                  cell.value = 1
                  print(cell.value)
             elif cell.value.upper() == 'OldValue2':
                 cell.value = 2
                 print(cell.value)

, :

col_indices = {n: cell.value for n, cell in enumerate(sheet.rows[0]) 
               if cell.value in colnames}

for row in sheet.rows[1:]:
    for index, cell in enumerate(row):
        if index in col_indices:
            print('col: {}, row: {}, content: {}'.format(
                   col_indices[index], index, cell.value))
            if cell.value.upper() == 'OldValue1':
                 cell.value = 1
            elif cell.value.upper() == 'OldValue2':
                 cell.value = 2

if :

if cellObj.column in 'HILM':
    print(cellObj.value),

:

if cellObj.column in ['H', 'AA', 'AB', 'AD']:
    print(cellObj.value),

Answer 2

, sheet.cell(row = #, column = #). :

for row in enumerate(sheet.iter_rows()):
    for j, cellObj in enumerate(row):
        header_cell = sheet.cell(row=1, column=j)

        if cellObj.column in ['H', 'I', 'L', 'M', 'AA', 'AB']:
            print(cellObj.value),
            if cellObj.value.upper() == 'OldValue1':
                cellObj.value = 1
                print(cellObj.value)
            elif cellObj.value.upper() == 'OldValue2':
                cellObj.value = 2
                print(cellObj.value)