Logic to generate an alphabetical sequence in C#

Question

The sequence should go like this.
A-Z,AA-AZ,BA-BZ,CA-CZ,.......,ZA-ZZ
After ZZ it should start from AAA.
Then AAA to ZZZ and then AAAA to ZZZZ and so on.

This sequence is pretty much like that of an Excel sheet.

Edit: Added my code

        private void SequenceGenerator()
    {
        var numAlpha = new Regex("(?<Numeric>[0-9]*)(?<Alpha>[a-zA-Z]*)");
        var match = numAlpha.Match(txtBNo.Text);

        var alpha = match.Groups["Alpha"].Value;
        var num = Convert.ToInt32(match.Groups["Numeric"].Value);
        lastChar = alpha.Substring(alpha.Length - 1);

        if (lastChar=="Z")
        {
            lastChar = "A";
            txtBNo.Text = num.ToString() + "A" + alpha.Substring(0, alpha.Length - 1) + lastChar;
        }

        else
        {
            txtBNo.Text = num.ToString() + alpha.Substring(0, alpha.Length - 1) + Convert.ToChar(Convert.ToInt32(Convert.ToChar(lastChar)) + 1);
        }
    }

This is what I've done. But, I know that is a wrong logic.

Thanks.

Answer 1

Lucky for you, I've done this once before. the problems I've encountered is that in the Excel sheet there is no 0, not even in double 'digit' 'numbers'. meaning you start with a (that's 1) and then from z (that's 26) you go straight to aa (27). This is why is't not a simple base conversion problem, and you need some extra code to handle this. Testing the function suggested by xanatos results with the following:

NumToLetters(0) --> A

NumToLetters(25) --> Z

NumToLetters(26) --> BA

My solution has more code but it has been tested against Excel and is fully compatible, except it starts with 0 and not 1, meaning that a is 0, z is 25, aa is 26, zz 701, aaa is 702 and so on). you can change it to start from 1 if you want, it's fairly easy.

private static string mColumnLetters = "zabcdefghijklmnopqrstuvwxyz";

// Convert Column name to 0 based index
public static int ColumnIndexByName(string ColumnName)
    {
        string CurrentLetter;
        int ColumnIndex, LetterValue, ColumnNameLength;
        ColumnIndex = -1; // A is the first column, but for calculation it's number is 1 and not 0. however, Index is alsways zero-based.
        ColumnNameLength = ColumnName.Length;
        for (int i = 0; i < ColumnNameLength; i++)
        {
            CurrentLetter = ColumnName.Substring(i, 1).ToLower();
            LetterValue = mColumnLetters.IndexOf(CurrentLetter);
            ColumnIndex += LetterValue * (int)Math.Pow(26, (ColumnNameLength - (i + 1)));
        }
        return ColumnIndex;
    }

// Convert 0 based index to Column name
public static string ColumnNameByIndex(int ColumnIndex)
    {
        int ModOf26, Subtract;
        StringBuilder NumberInLetters = new StringBuilder();
        ColumnIndex += 1; // A is the first column, but for calculation it's number is 1 and not 0. however, Index is alsways zero-based.
        while (ColumnIndex > 0)
        {
            if (ColumnIndex <= 26)
            {
                ModOf26 = ColumnIndex;
                NumberInLetters.Insert(0, mColumnLetters.Substring(ModOf26, 1));
                ColumnIndex = 0;
            }
            else
            {
                ModOf26 = ColumnIndex % 26;
                Subtract = (ModOf26 == 0) ? 26 : ModOf26;
                ColumnIndex = (ColumnIndex - Subtract) / 26;
                NumberInLetters.Insert(0, mColumnLetters.Substring(ModOf26, 1));
            }
        }
        return NumberInLetters.ToString().ToUpper();
    }