Whats the best way to iterate over the alphabet-CodePudding

Whats the best way to iterate over the alphabet except when Z is reached it should become AA , AB , AC , ... Once AZ is reached it should start with AAA , AAB , AAC , ... and further on.

Should be used for Excel sheets. Or are their any other ways to do this?

What i have been trying:

 char[] alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
            int i = 0;
            foreach (string tagName in names)
            {
                string cellname = alpha[i]   "1";
                if (alpha[i].Equals("Z"))
                {
                    cellname = "A"   alpha[i]   "1";
                }
                cell.PutValue("hello");
                i  ;
            }
           
            wb.Save("Excel.xlsx", SaveFormat.Xlsx);

CodePudding user response：

That was a fun exercise. The key is to realize that AAA, AAB, ... etc. behave just like natural numbers 000, 001, ..., except for being in base 26 rather than base 10.

Thus, to create the three-letter values, we can just count from 0 to (26^3)-1 and convert each value to base 26. We also need the outer loop over length to first create the one-letter values, then the two-letter values, etc.

static IEnumerable<string> GetBase26Numbers(int maxLength) 
{
    for (int length = 1; length <= maxLength; length  )
    {
        for (int i = 0; i < (int)Math.Pow(26, length); i  )
        {
            yield return FormatAsBase26(i, length);
        }
    }
}

// convert 0 to AAA, 1 to AAB, ...
static string FormatAsBase26(int i, int length) {
    var result = new StringBuilder();

    for (int pos = 0; pos < length; pos  )
    {
        result.Insert(0, (char)('A'   (i % 26)));
        i /= 26;
    }

    return result.ToString();
}

Can be called like this (fiddle):

// prints all values from A to ZZ
foreach (var s in GetBase26Numbers(2))
{
    Console.WriteLine(s);
}

CodePudding user response：

My answer is in fact very similar to Heinzis answer. But it does not produce leading 'A's

string FormatAsBase26(int n)
{
    var digits = new List<char>();
    do
    {
        digits.Insert(0, (char)('A'   n % 26));
        n /= 26;
    } while (n != 0);
    return new string(digits.ToArray());
}

and then:

for (var i = 0; i < max; i  )
{
    Console.WriteLine(FormatAsBase26(i));
}

CodePudding user response：

Just by way of comparison, here's a different approach that uses recursion:

public static IEnumerable<string> ExcelColumns()
{
    var current = new [] { ' ', ' ', ' ' };

    bool inc(int col)
    {
        if (current[col] == 'Z')
        {
            if (col == 0) // Run out of columns?
                return false;

            current[col] = 'A';
            return inc(col - 1);
        }

        current[col] = current[col] == ' ' ? 'A' : (char)(current[col]   1);
        return true;
    }

    while (inc(2))
    {
        yield return new string(current).TrimStart();
    }
}

This works how you'd do it manually: Increment the value at the rightmost column. If that passes Z, set that column to A and increment the column to the left (exactly like you do when increasing numbers in base 10).

There's a special case to handle if the column is currently ' ' because then you need to set it to A.

If you run out of columns, you're done. The recursive function indicates this by returning false; otherwise, it returns true.

This implementation is complicated by the fact that you don't want leading As. If it's OK to go AAA, AAB etc, then a simpler implementation is:

public static IEnumerable<string> ExcelColumns()
{
    var current = new [] { 'A', 'A', 'A' };

    bool inc(int col)
    {
        if (current[col] == 'Z')
        {
            if (col == 0) // Run out of columns?
                return false;

            current[col] = 'A';
            return inc(col - 1);
        }

          current[col];
        return true;
    }

    do
    {
        yield return new string(current);
    }
    while (inc(2));
}