Get the Column Index of a Cell in Excel using OpenXML C#

I've been looking around for a while now and cannot seem to find out how to do this. I've got an excel sheet, which I'm reading using OpenXML. Now the normal thing would be to loop through the rows and then loop through the cells to get the values, which is fine. But along with the values I need the location of the cell, which would be in the format (rowindex, ColumnIndex). I've managed to get the rowIndex, but cant seem to figure out getting the column Index.

I actually thought this was going to be easy but apparently it isnt.

Solution 1:

This is slightly trickier than you might imagine because the schema allows for empty cells to be omitted.

To get the index you can use the Cell object wihch has a CellReference property that gives the reference in the format A1, B1 etc. You can use that reference to extract the column number.

As you probably know, in Excel A = 1, B = 2 etc up to Z = 26 at which point the cells are prefixed with A to give AA = 27, AB = 28 etc. Note that in the case of AA the first A has a value of 26 times the second; i.e. it is "worth" 26 whilst the second A is "worth" 1 giving a total of 27.

To work out the column index you can reverse the letters then take the value of the first letter and add it to a running total. Then take the value of the second letter and multiply it by 26, adding the total to the first number. For the third you multiply it by 26 twice and add it, for the fourth multiply it by 26 3 times and so on.

So for column ABC you would do:

C = 3
B = 2 * 26 = 52
A = 1 * 26 *26 = 676
3 + 52 + 676 = 731

In C# the following will work:

private static int? GetColumnIndex(string cellReference)
{
    if (string.IsNullOrEmpty(cellReference))
    {
        return null;
    }

    //remove digits
    string columnReference = Regex.Replace(cellReference.ToUpper(), @"[\d]", string.Empty);

    int columnNumber = -1;
    int mulitplier = 1;

    //working from the end of the letters take the ASCII code less 64 (so A = 1, B =2...etc)
    //then multiply that number by our multiplier (which starts at 1)
    //multiply our multiplier by 26 as there are 26 letters
    foreach (char c in columnReference.ToCharArray().Reverse())
    {
        columnNumber += mulitplier * ((int)c - 64);

        mulitplier = mulitplier * 26;
    }

    //the result is zero based so return columnnumber + 1 for a 1 based answer
    //this will match Excel's COLUMN function
    return columnNumber + 1;
}

Note that the CellReference is not guaranteed to be in the XML either (although I've never seen it not there). In the case where the CellReference is null the cell is placed in the leftmost available cell. The RowIndex is also not mandatory in the spec so it too can be omitted in which case the cell is placed in the highest row available. More information can be seen in this question. The answer from @BCdotWEB is correct approach in cases where the CellReference is null.

Solution 2:

Small is beautifull

int ColumnIndex(string reference)
{
  int ci=0;
  reference=reference.ToUpper();
  for (int ix = 0; ix < reference.Length && reference[ix] >= 'A';ix++ ) 
       ci = (ci * 26) + ((int)reference[ix] - 64);
  return ci;
}