How to get excel cell value by using column name in c# using NPOI

I suggest you to trash NPOI and use EPPlus instead. I wasted 3 days to automate some excel creation with NPOI just to discover that to ADD A COLUMN I have to shift every cell manually and manage empty cell on which NPOI returns null objects.

In EPPlus it woule be simple as that

worksheet.Cells["columnname"]

In my case I needed the column names (in the first row of the xls file) to be used to update a database table. With the hint from @stevenh I did this:

                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                rows.MoveNext();
                IRow r = (HSSFRow)rows.Current;
                int z = r.LastCellNum - 1;
                DataTable dt = new DataTable();

                for (int j = 0; j < z; j++)
                {
                    ICell cell = r.GetCell(j);
                    dt.Columns.Add(cell.StringCellValue);                    
                }

                rows.Reset();
                while (rows.MoveNext())
                {
                //... get the rest of the data....

Which was straightforward enough for me.

Although this is a month old, I also ran into the issue of converting a column name to column position (and vice-versa) in NPOI.

Found the answer in this post: How to get the Excel Cell value by address (A1, A2) using NPOI using NPOI.SS.Util.CellReference.

CellReference allows you to convert in both directions. You can pass in a string cell reference (e.g., "AA1") to get a position, or a row and column to get the string (in CellRefParts array).

This is a bit of a broad question without being able to see examples of the data you are working with or how you would like the end result but here are some ideas to get you started:

You could have separate designations for each column and sort your data by those columns in a particular order that retains integrity across the sheet. This could be achieved by reading the first row and assigning each column to a variable perhaps.

or

You could possibly note which columns contain what information and order your information in a data structure auch as a binary search tree or hash to organize the data and preserve its structure.

You can do the following:

1. Read the header row and persist data in a dictionary of header name (key) and its index (value).
2. Get (or TryGet) value from the dictionary.

The code would look like:

Dictionary<string, int> indices = 
    headersRow.Cells
              .Select(x => new { x.StringCellValue, x.ColumnIndex })
              .ToDictionary(x => x.StringCellValue, x => x.ColumnIndex);

for (var rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
{
    var row = sheet.GetRow(rowIndex);

    var hasSuchHeader = indices.TryGetValue(name, out int cellIndex);
    if (hasSuchHeader)
    {
        var cell = row.GetCell(cellIndex);
    }
}