如何在openpyxl中使用字段名称或列标题? [英] How to use field name or column header in openpyxl?

问题描述

请参阅下面的代码.这段代码效果很好，但是我想做两件事.例如，如果语句的长度小于或等于实际值，我会做一件事.我有很多这样的专栏，但并非彼此相邻.我希望它短一些.另外，有时我可能不知道确切的列字母.

See my code below. This code works very well, but I would like to do two things. One thing is I made if statement with or much shorter than actual for example. I have many columns like this, not all next to each other. I would like it to be shorter. Also, sometimes I may not know exact column letter.

所以我想知道是否有一种方法可以知道列名或标题.就像最上面一行中的值一样.因此，我可以测试一下它是否是始终在该单元格上执行功能的那些值之一(如果它在指定的列中). 我找不到openpyxl函数来做列名.不知道它是否理解第一行与其他行不同.我认为，如果没有的话，我可以尝试在第一行进行测试，但不知道如何进行测试.

So I want to know if there is a way to know the column name or header. Like the values that would be in very top row. So I can test to see if it is one of those values to always perform function on that cell if it's in the specified column. I can't find openpyxl function to do column name. Not sure if it understands that first row is different than rest. I think maybe if not I can try to do test on first row, but don't understand how to make this.

那么有没有一种方法可以调用列名?或者如果无法调用列名进行测试，有人可以帮助我检查第一行以查看其是否有价值吗?然后在我所在的正确行上进行更改吗?这有意义吗?

So is there a way to call column name? or if there is no way to call column name to test, can someone help me with doing check on first row to see if it has value? then do change on correct row I'm in? Does this make sense.

因此，而不是代码:

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

它会说:

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

或者，如果不可能的话，那么:

Or if not possible to do that, then:

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

请提供最佳方法帮助.我已经看过stackoverflow以及书和博客网站，但似乎不是一种调用列名(而不是列名)的方法.

Please help with best way to do this. I have looked on stackoverflow and in book and blog site, but does not seem to be a way to call column name (not the letter of column).

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)

推荐答案

编辑

假设这些是您要查找的标题名称:

Assuming these are the header names you are looking for:

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)

使用字典而不是集合来保留列名:

Use a dictionary instead of a set to keep the column names around:

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),

对于多字母列坐标，您需要使用一个列表:

For multi letter column coordinates you need to use a list:

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

这篇关于如何在openpyxl中使用字段名称或列标题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！