C# - Creating a List Of Spreadsheet Column Names

Question

I have some C# code. I'm trying to generate a list of column names in a spreadsheet. In a spreadsheet, cells are listed from left to right starting at "A" and going to something like "ABC". I'm trying to dynamically generate the cell names into a list like this:

var cells = new List<string>();
for (var i=1; i<=5; i++)
{
  for (var j = 0; j < 26; j++)
  {
    var column = "";
    for (var k=0; k < i; k++)
    {
      char letter = Convert.ToChar(j + (int)'A');
      column = column + letter;
    }
    cells.Add(column);
  }
}

Unfortunately, my approach isn't working. Once I get past "Z", I immediately see "AA", "BB", "CC", .... Its not looping through alphabet again. What am I doing wrong? I keep staring at it and it looks correct to me. Any help is appreciated.

The logic appears to be "for as many times as the `k` loop executes, add the current letter' which would explain it. It's not really clear what you expect the output to be? AA AB AC? — stuartd, Mar 10 '16 at 23:02
If it helps, you could replace the `j` loop with `foreach(char letter in "ABCDEFGHIJKLMNOPQRSTUVWXYZ")` — stuartd, Mar 10 '16 at 23:05

score 0 · Accepted Answer · edited May 23 '17 at 12:31

As @stuartd pointed out in the comments, your algorithm is just setting the same letter i - k times. You should learn how to use step-by-step execution in Visual Studio, as it helps great deal to debug this kind of algorithm.

As an alternative, I can suggest this algorithm:

static void Main(String[] args)
{
    foreach (var column in GetColumns().Take(52))
    {
        Console.WriteLine(column);
    }

    Console.ReadLine();
}

public static IEnumerable<string> GetColumns()
{
    const string Alphabet = " ABCDEFGHIJKLMNOPQRSTUVWXYZ";

    for (int index = 1; ; index++)
    {
        if (index % Alphabet.Length == 0)
        {
            continue;
        }

        var columnName = string.Empty;

        int value = index;

        do
        {
            columnName = Alphabet[value % Alphabet.Length] + columnName;
            value = value / Alphabet.Length;
        } while (value > 0);

        yield return columnName;
    }
}

The main idea is thinking that the column name is pretty much the numerical index of the column but transposed in base 26, with a few quirks:

Digits are replaced by letters
There is no "0" in this representation, so we skip every number that would contain a 0 in base 26. That is, every multiple of 26.

The implementation is then wrapped in an enumerator for easier reuse.

The algorithm to convert to base 26 was taken from another question: https://stackoverflow.com/a/923814/869621

C# - Creating a List Of Spreadsheet Column Names

1 Answers1