How to convert Excel sheet column names into numbers?

Question

I was wondering what is the best way to convert excel sheet column names into numbers.

I'm working with Excel Package, a good library to handle .xlsx documents. This library unfortunately doesn't have this functionality included.

OBS: The first column, A, corresponds to number 1 in this library.

Answer 1

This function should work for an arbitrary length column name.

public static int GetColumnNumber(string name)
{
    int number = 0;
    int pow = 1;
    for (int i = name.Length - 1; i >= 0; i--)
    {
        number += (name[i] - 'A' + 1) * pow;
        pow *= 26;
    }

    return number;
}

Answer 2

I had to deal with this a few months ago. The inverse - column index to column name - is fun, too, and becomes really messy if you try to solve it with a zero based index not recognizing that this complicates things. It could be so simple if it would be a normal polyadic numeral system ...

Here is a simplified version of my solution as a extension method without error handling and all that stuff.

public static Int32 ToOneBasedIndex(this String name)
{
    return name.ToUpper().
       Aggregate(0, (column, letter) => 26 * column + letter - 'A' + 1);
}