How to implement column self-naming from its index?

Question

I wish to be able to instantiate my Cell class while naming the cell instance with such name as "A", "B", "C", etc. just like in an Excel spreadsheet.

I have my Cell class like so:

public class Cell {
    public Cell(Range nativeCell) {
        NativeCell = nativeCell;
    }

    public Range NativeCell { get; private set; }
}

And my Sheet class:

public class Sheet {
    private IDictionary<string, Cell> _cells;

    public Sheet(Worksheet nativeSheet) {
        NativeSheet = nativeSheet;

        _cells = new Dictionary<string, Cell>();

        for (int rowIndex = 1; rowIndex <= NativeSheet.Rows.Count; ++rowIndex)
            for (int colIndex = 1; colIndex <= NativeSheet.Columns.Count; ++colIndex) {
                ICell newCell = new Cell(NativeSheet.Cells(rowIndex, colIndex));
                newCell.Name = ?? // This name should look like "A1", "B1", "AA3", "CB20", etc.
                Cells.Add(newCell.Name, newCell);
            }
    }

    public IDictionary<string, Cell> Cells {
        get {
            return _cells;
        }
    }

    public Worksheet NativeSheet { get; private set; }
}

I would need to generate a name based on the alphabetic letters and double and triple them once I encounter the last alphabet letter 'Z'. The algorithm would have to generate the letters that I would concatenate with the rowIndex value that would result to this naming strategy such as Excel.

The letters would be:

A, B, C, D...Z, AA, AB, AC...AZ, BA, BB, BC...BZ, CA...XAA, XAB, XAC...

While we clearly know that colIndex value 1 will definitely designate column "A", value 2 = "B", value 3 = "C", etc.

My problem is particularly when we double the letters.

Do you have any idea on how I could achieve this in the simplest possible form?

Thanks! =)

score 1 · Accepted Answer · edited May 23 '17 at 10:24

1

Here is this. Translate a column index into an Excel Column Name

Shouldn't be to hard to make it recursive and give you exactly what you need. I hope this helps.

edited May 23 '17 at 10:24

Community

1
1

answered Mar 01 '11 at 21:25

TyCobb

8,909
1
33
53

score 1 · Answer 2 · edited Mar 09 '11 at 09:10

1

This function will do it for you. ~~It is in VB.NET but I trust you'll be able to port it to C# if need be.~~
I have updated the answer with the C# version of the function.

VB.NET

''' <summary>Returns the Excel-style name of the column from the column index.</summary>
''' <param name="colIndex">The column index.</param>
Function GetColumnName(ByVal colIndex As Integer) As String
    If colIndex < 1 Then Throw New ArgumentException("Column number must be greater or equal to 1.")

    Dim result As New List(Of String)

    'letter codes start at Chr(65)'
    Do While colIndex > 0
        'reduce the column number by 1 else the 26th column (Z) will become 0 (@) '
        'add 65 to the result and find the Chr() value.                           '
        'insert the character at position 0 of the character list                 '
        'integer divide by 26 to remove the column from the stack and repeat till '
        'there are no columns in the stack.                                       '
        result.Insert(0, Chr(65 + CInt((colIndex - 1) Mod 26)))
        colIndex = (colIndex - 1) \ 26
    Loop

    Return String.Join("", result.ToArray)
End Function

C#

/// <summary>Returns the Excel-style name of the column from the column index.</summary>
/// <param name="colIndex">The column index.</param>
static string GetColumnName(int colIndex)
{
    if (colIndex < 1)
        throw new ArgumentException("Column number must be greater or equal to 1.");

    var result = new List<char>();

    //letter codes start at Chr(65)'
    while (colIndex > 0)
    {
        //reduce the column number by 1 else the 26th column (Z) will become 0 (@) 
        //add 65 to the result and find the Chr() value.
        //insert the character at position 0 of the char list
        //integer divide the column index by 26 to remove the last calculated column 
        //from the stack and repeat till  there are no columns in the stack.                                       
        result.Insert(0, Microsoft.VisualBasic.Strings.Chr(65 + Convert.ToInt32((colIndex - 1) % 26)));
        colIndex = (int)((colIndex-1)/ 26);
    }

    return new string(result.ToArray());
}

I tested this up to column index 1000 and it worked without fail. I hope you find it useful.

edited Mar 09 '11 at 09:10

Jeff Atwood

63,320
48
150
153

answered Mar 02 '11 at 12:18

Alex Essilfie

12,339
9
70
108

I tested and it works almost flawlessly. Aside, once the A to Z letters have been spent, the following column which should be Z..AA is actually Z..AZ, AA, AB... That is to say that somehow a value is kept in memory or something, since the same repeats once a change of the previous letter occurs, the following names are perfect. What do you think this could be? – Will Marcouiller Mar 02 '11 at 18:49
@Will: It appears the problem was with the VB.NET code. I have fixed it now. – Alex Essilfie Mar 03 '11 at 09:33
And the difference between my implementation and [Joel Coehoorn's answer to a similar question](http://stackoverflow.com/questions/297213/translate-an-index-into-an-excel-column-name/297214#297214) linked by [@TyCobb](http://stackoverflow.com/questions/5160001/how-to-implement-column-self-naming-from-its-index/5160537#5160537) is that mine can calculate from column index 1 to [Integer.MaxValue](http://msdn.microsoft.com/en-us/library/system.int32.maxvalue.aspx "Integer.MaxValue = 2,147,483,647") while Joel's works only up to column index 26. – Alex Essilfie Mar 03 '11 at 09:36
Thanks for pointing this out, though I favorited the use of Ken Paul's answer who suggests to use the `Range.Address()` method to retrieve the "name" of the cell directly from the API, that is native Interop code. That is the reason that made me accept TyCobb's answer, though yours yet not tested on my side WILL make it, since you say you fixed the glitch I informed you of. I would like to accept your also, as you both provided me with such important solution to this problem. I'm sorry, really really sorry. Besides, please note that I am the upvoter for your answer. – Will Marcouiller Mar 03 '11 at 15:02
Aside, I think that the `while` loop shall bring some overhead processing over the mathematical `Modulo` operation against the 26 integer value, since there is only 26 characters to care about. That is, when obtaining the modulo of a number which would be grater than 1, this means you have to double the character and so forth. To tell about Joel Coehoorn's answer, it is recursive, so it shall double and triple the character string returned as the column name, so it should virtually handle any integer value, as I understand the algorithm. Hey, no harm made here, I simply wish to share. Thanks! – Will Marcouiller Mar 03 '11 at 15:08
@Will: I didn't know of any issue about '`while` loops bringing overhead processing mathematical `Modulo` operation' so I'd like to get a link to any document to educate me some more. I revisited Joel Coehoorn's work and found it is a more or less recursive version of my iterative algorithm but I'm not deleting my initial post for posterity sake. Thanks. – Alex Essilfie Mar 03 '11 at 17:48

How to implement column self-naming from its index?

2 Answers2

Linked