Hi,

I'm developing with C# and I need a function gets a integer as
parameter, and returns the corresponding column name in a sheet of Excel:

public string ConvertToLetter(int number)
{
    //code...
}

1 --> A
2 --> B
3 --> C
..
..
..
26 --> Z
27 --> AA
28 --> AB
29 --> AC
..
..
..
702 --> ZZ
703 --> AAA
704 --> AAB

.... and so on.

I've found a lot of snippets on the net, but most of the code can count
until "ZZ" and then fails...

Please help.
Thanks.

MAX

public string ConvertToLetter(int pintNumber)
{
    int intNumber = pintNumber;
    string strXL = String.Empty;
    int intColumn3 = 0;
    int intColumn2 = 0;

    if (pintNumber > 16384) { return strXL; }

    if (intNumber > 676)
    {
        intColumn3 = (intNumber - 1) / 676;
        strXL += (char)(intColumn3 + 64);
        intNumber = intNumber - (intColumn3 * 676);
    }

    if (intNumber > 26)
    {
        intColumn2 = (intNumber - 1) / 26;
        strXL += (char)(intColumn2 + 64);
        intNumber = intNumber - (intColumn2 * 26);
    }

    strXL += (char)(intNumber + 64);

    return strXL;
    }
}

Ignore my previous post - this is much more efficient:

public string ConvertToLetter(int pintNumber)
{
    string strXL = String.Empty;

    if (pintNumber > 16384) { return strXL; }

    while (pintNumber > 0)
    {
        strXL = (char)(--pintNumber % 26 + 'A') + strXL;
        pintNumber /= 26;
    }

    return strXL;
}


I've tested it with this simple loop:

for (int i = 1; i <= 16384; i++)
{
    Console.WriteLine(ConvertToLetter(i) + " - " + i.ToString());
}

and, as far as I can tell, it returns the correct value for all 16,384 Excel
columns...

Hi,
   I struggled with this exact problem recently and came up with the
following. I believe it to be a completely general solution for arbitrarily
large numbers to the appropriate Excel column code assuming an infinite
number of columns, but I have not tested it much past "zzz"

Ethan

public static string Number2ExcelColumnLetters(int number)
        {
            List<char> reversedResults = new List<char>();
            int lastRemainingCharValue = number % 26;
            int remainingValue = number - lastRemainingCharValue;
            if (lastRemainingCharValue == 0)
            {
                reversedResults.Add('Z');
                remainingValue -= 26;
            }
            else
            {

reversedResults.Add(Tools.Number2Letter(lastRemainingCharValue -
1).ToCharArray()[0]);
            }
            while (remainingValue > 0)
            {
                remainingValue = (int)Math.Round((double)(remainingValue /
26), 0);
                lastRemainingCharValue = remainingValue % 26;
                remainingValue = remainingValue - lastRemainingCharValue;
                if (lastRemainingCharValue == 0)
                {
                    reversedResults.Add('Z');
                    remainingValue -= 26;
                }
                else
                {

reversedResults.Add(Tools.Number2Letter(lastRemainingCharValue -
1).ToCharArray()[0]);
                }
            }
            reversedResults.Reverse();
            return new string(reversedResults.ToArray());
        }


Number2Letter is just a single number from 0 to 25 to a letter where 0=A etc.

MemoryAccessViolationException and multimedia timer problem
Aren't All MD5 Hashes the Same?
solve c# problems
Illegal Instruction Thrown When Executing a Callback Function
Image
Implementing IDisposable on factory pattern objects
decorator pattern help
VS2005 can't open form
Linq to sql variable where clause
Clearing a Collection