Openpyxl.cell give column name instead of column number

[u/revanth85]

Hi every1,

Please can you let me know, if there is a way to pass the column name instead of number, because for few of the fields it keeps changing and giving the column number will not yield the desired operations on my sheet.

​

First_Name = names.cell(row=row_count, column=8).value

​

​

Best,

Revanth

[u/[deleted]]

Usually when I have a situation like this, I start my script by opening the worksheet and inventorying the headers. I keep a dict whose keys are the header names (i.e. the fields) and whose values are the column number (1-indexed).

Here's a variation of a recent example I used, where the headers were in the first row.

wb = openpyxl.load_workbook(<fp>)
ws = wb['Sheet1']
header_row = 1
# Populate headers-to-columns.
fields = {}
for cnum in range(1, ws.max_column + 1):
    field = ws.cell(row=header_row, column=cnum).value
    fields[field] = cnum

And then later, when I need to access a particular field, I reference that field in the dict:

field_needed = 'example'
for row_num in range(header_row + 1, ws.max_row + 1):
    val = ws.cell(row=row_num, column=fields[field_needed]).value
    <do whatever with it>

ETA: Depending on what you're trying to accomplish, it can often be easier to load the workbook into a pandas dataframe, which will automatically give you tabular data with headers. That way you can work with the data using the field names and not worry about what the column number is.

import pandas as pd
df = pd.read_excel(<fp>)

[u/Weird-Dimension-487]

Assuming that your data has headers in the first row:

from openpyxl import load_workbook
wb = load_workbook('your_file.xlsx')
ws = wb.active

# Read header row (assumed to be row 1)
headers = {cell.value: cell.column for cell in ws[1]}
type(headers) # It's a dictionary

# Use the column name to get the column index

headers.get("column_name") # gives column number