Tuesday, April 16, 2013

Excel Column Name as a 0-based Index Number

This function is handy if you want to use packages like NPOI or EPPlus or some other Excel manipulation package.

Basically find the numeric value of the letter, and multiply it by the appropriate power of 26.  Think of it like Base-26 numerals converting to decimal (or Base-10).  The process is basically the same.  We have to subtract 1 at the end because A is used like a 1 rather than a 0, and in Excel, the columns start from 1, not 0. So A=0, B=1,C=2, ... AA=26, AB=27, AC=28, ... ZA=676, ... AAA=702, etc.  Although there is a limit to the number of columns in Excel 2007/2010 (I just can't remember it off the top of my head, and it's also dependent on if you are using 32bit Office or 64bit).

/// <summary>
/// Returns 0-based column number from Excel column name 

///   string, like "G" or "ZD" or "DAJ".
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
private static int GetIntFromColumnName(string column)
{

    column = column.ToUpperInvariant();
    const string alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    int multiplier = (int)Math.Pow(26, column.Length - 1) ;
    int result = 0;
    char[] stuff = column.ToCharArray();
    for (int scan = 0; scan < column.Length; scan++)
    {
        if (!alpha.Contains(stuff[scan]))
            throw new ArgumentOutOfRangeException();
        result += (alpha.IndexOf(stuff[scan]) + 1) * multiplier;
        multiplier = (int)(multiplier / 26);
    }
    return result - 1;
}

No comments:

Post a Comment