in c#, how can i build up array from A to ZZ that is similar to the way that excel orders columns

Question

i am looking for code that can generate an array where the first item is A, then B, then C . . .after Z it would then go to AA, then AB then AC . . . all the way up to ZZ.

what is the best way of doing this in C#?

Answer 1

One of the ways is:

IEnumerable<string> generate()
{
    for (char c = 'A'; c <= 'Z'; c++)
        yield return new string(c, 1);
    for (char c = 'A'; c <= 'Z'; c++)
        for (char d = 'A'; d <= 'Z'; d++)
            yield return new string(new[] { c, d });
}

---

Edit:
you can actually produce "infinite" sequence (bounded by maximal long value) with somewhat more complicated code:

string toBase26(long i)
{
    if (i == 0) return ""; i--;
    return toBase26(i / 26) + (char)('A' + i % 26);
}

IEnumerable<string> generate()
{
    long n = 0;
    while (true) yield return toBase26(++n);
}

This one goes like that: A, B, ..., Z, AA, AB, ..., ZZ, AAA, AAB, ... etc:

foreach (var s in generate().Take(200)) Console.WriteLine(s);

Answer 2

Great answer of Vlad.

Here's another variation on that:

 static IEnumerable<string> generate() {
   for (char c = 'A'; c <= 'Z'; c++) {
     yield return c.ToString();
   }

   foreach (string s in generate()) {
     for (char c = 'A'; c <= 'Z'; c++) {
       yield return s + c;
     }
   }
 }

If you don't mind starting the sequence with an empty string you could write it as follows:

 static IEnumerable<string> generate() {
   yield return "";

   foreach (string s in generate()) {
     for (char c = 'A'; c <= 'Z'; c++) {
       yield return s + c;
     }
   }
 }

Answer 3

You could generate numbers using Enumerable.Range and cast them to char to generate A-Z. Next step is to combine them.

Answer 4

Found this in SO albeit in PHP. Would this help? Algorithm to get the excel-like column name of a number

Answer 5

var q = Enumerable.Range(Convert.ToInt32('A'),26).Select( x => Convert.ToChar(x) );
var result = (
  q.Select( x => x.ToString() )
   .Concat( 
      q.SelectMany(
        x => q.Select( y => x.ToString() + y.ToString() )
      )
   )
);

Answer 6

class Program
{
    public static string IntegerToExcelColumn(int col)
    {
        // I've put a 256 upper bound here because Excel 2003 
        // allows only 256 columns. Change it if you're using 
        // Excel 2007 or 2010.
        Debug.Assert(col >= 1 && col <= 256);

        if (col >= 1 && col <= 26)
        {
            return ((char)(((int)'A') + (col - 1))).ToString();
        }

        // I've put a 256 upper bound here because Excel 2003 
        // allows only 256 columns. Change it if you're using 
        // Excel 2007 or 2010.
        if (col > 26 && col <= 256)
        {
            int rem = col % 26;
            int pri = col / 26;
            if (rem == 0)
            {
                rem = 26;
                pri--;
            }
            char[] buffer = new char[2];
            buffer[0] = (char)(((int)'A') + (pri - 1));
            buffer[1] = (char)(((int)'A') + (rem - 1));
            return new string(buffer);
        }

        return "";
    }

    static void Main(string[] args)
    {
        string[] columns= new string[255];
        for (int i = 1; i <= 255; i++)
            columns[i-1] = IntegerToExcelColumn(i);
        foreach(var col in columns)
            Console.WriteLine(col);
    }
}

Answer 7

Here's one way. :)

string[] values =
  Enumerable.Range(0, 27 * 26)
  .Select(
    n => new String(
      new[] { (char)('@' + n / 26), (char)('A' + n % 26) },
      n < 26 ? 1 : 0, n < 26 ? 1 : 2
    )
  )
  .ToArray();

Answer 8

Have a look at an ASCII table and take note of the values of the characters. You should be able to work out a loop to increment the character from A to Z for as many times as you need to replicate the characters. :)

Answer 9

You can also try this. A to ZZ

public class Program
{
    public static void Main()
    {
        int c=1;
        string cc=AtoZZ(c);
        for(int i=1;i<=702;i++){
        cc=AtoZZ(i);
        Console.WriteLine(cc);
        }
    }

    static string AtoZZ(int chr)
    {
        char c1;
        char c2;
        string alp = "";
        if(chr<=26)
        {
            c1 = (char)(chr + 64);
            alp = c1 + "";
        }        
        else
        {
            int cl1 = chr / 26;
            int cl2 = chr % 26;
            if(cl2==0){
                cl1--;
                cl2=26;
            }
            c1 = (char)(cl1 + 64);
            c2 = (char)(cl2 + 64);
            alp = c1 + "" + c2;
        }
        return alp;
     }
}

Answer 10

To complement Vlad's answer, here's the reverse operation of ToBase26(int):

static long ToBase10(string str)
{
   if (string.IsNullOrWhiteSpace(str)) return 0;
   var value = str[0] - 'A' + 1;

   return (long) (value * Math.Pow(26, str.Length - 1) + 
          ToBase10(str.Substring(1, str.Length - 1)));
}