python - 无法通过使用 openpyxl 的列标题读取内容

Question

我正在尝试使用使用openpyxl库的列标题来读取 excel 文件的内容，但我找不到这样做的任何想法。

这通常是我使用列索引（工作之一）读取项目时所做的。

from openpyxl import load_workbook

wb = load_workbook('company_info.xlsx')
ws = wb['Sheet1']

for i in range(1, ws.max_row + 1):
    if ws.cell(row=i,column=1).value==None:break
    print(ws.cell(row=i,column=1).value,ws.cell(row=i,column=2).value,ws.cell(row=i,column=3).value)

excel中的内容如下：

如何使用列标题阅读公司或国家/地区？

注意：我只关注与openpyxl相关的任何解决方案

标签： pythonpython-3.xopenpyxl

Answer 1

openpyxl库似乎有很多隐藏的小宝石。希望这是您所需要的。

from openpyxl import load_workbook

book = load_workbook('company_info.xlsx', data_only=True)
sheet = book.active
company_names = sheet['A']
company_id = sheet['B']
country = sheet['C']

for company in company_names:
  if company.value != 'Company':
    print(company.value)
    # output
    Amigo Foods
    Atlantic Shrimpers Limited
    Amawar Hiwam Co.
    Austral Fishers Pyt Ltd
    Alpha Group - Headquarters

for country in company_location:
  if country.value != 'Country':
    print(country.value)
    # output
    Nepal
    Nigeria
    Myanmar
    Australia
    Kenya

 for id in company_id:
   if id.value != 'ID':
     print (id.value)
     # output
     ED5634
     PN7809
     QZ3871
     TR1089
     UY3090

 max_column = sheet.max_column
 max_row = sheet.max_row

 for row in sheet.iter_rows(min_row=2, max_row=max_row, min_col=1, 
   max_col=max_column, values_only=True):
   print (row)
   # output
   ('Amigo Foods', 'ED5634', 'Nepal')
   ('Atlantic Shrimpers Limited', 'PN7809', 'Nigeria')
   ('Amawar Hiwam Co.', 'QZ3871', 'Myanmar')
   ('Austral Fishers Pyt Ltd', 'TR1089', 'Australia')
   ('Alpha Group - Headquarters', 'UY3090', 'Kenya')


 for row in sheet.iter_rows(min_row=2, max_row=max_row, min_col=1,
   max_col=max_column, values_only=True):
   company_name = row[0]
   company_id = row[1]
   company_location = row[2]
   if 'Amawar' in company_name:
     print (row)
     # output
     ('Amawar Hiwam Co.', 'QZ3871', 'Myanmar')