In ClosedXML, is there anyway to get the column letter from column header name?

Question

I have an excel worksheet that has column headers and I don't want to hard code the column letter or index so I am trying to figure out how I could make it dynamic. I am looking for something like this:

var ws = wb.Worksheet("SheetName");

var range = ws.RangeUsed();
var table = range.AsTable();

string colLetter = table.GetColumnLetter("ColHeader");

foreach (var row in table.Rows())
{
  if (i > 1)
  {
    string val = row.Cell(colLetter).Value.ToString();
  }
  i++;
}

Does ClosedXML support anything like the made up GetColumnLetter() function above so I don't have to hard code column letters?

score 13 · Accepted Answer · edited Apr 11 '15 at 14:12

13

Sure, get the cell you want using a predicate on the CellsUsed collection on the row with the headers, then return the column letter from the column.

public string GetColumnName(IXLTable table, string columnHeader)
{
    var cell = table.HeadersRow().CellsUsed(c => c.Value.ToString() == columnHeader).FirstOrDefault();
    if (cell != null)
    {
        return cell.WorksheetColumn().ColumnLetter();
    }
    return null;
}

edited Apr 11 '15 at 14:12

leora

188,729
360
878
1,366

answered Apr 11 '15 at 06:50

christophano

915
2
20
29

missing this IXLTable as first parameter – Emanuele Dec 12 '17 at 15:49
i dont understand this example, can you show me how i would implement it? – aaron lilly Jun 23 '20 at 19:58

score 3 · Answer 2 · answered Jan 05 '21 at 15:41

For version 0.95.4.0 I did the next steps

var ws = wb.Worksheet("SheetName");

var range = ws.RangeUsed();
var table = range.AsTable();

var cell = table.FindColumn(c => c.FirstCell().Value.ToString() == yourColumnName);
if (cell != null)
{
     var columnLetter = cell.RangeAddress.FirstAddress.ColumnLetter;
}

In ClosedXML, is there anyway to get the column letter from column header name?

2 Answers2

Linked