18

Possible Duplicate:
How to convert a column number (eg. 127) into an excel column (eg. AA)

Ok so I am writing a method which accepts a 2d array as a parameter. I want to put this 2d array onto an Excel worksheet but I need to work out the final cell location. I can get the height easily enough like so:

var height = data.GetLength(1); //`data` is the name of my 2d array

This gives me my Y axis but it's not so easy to get my X axis. Obviously I can get the number like so:

var width = data.GetLength(0);

This gives me a number, which I want to convert to a letter. So, for example, 0 is A, 1 is B and so on until we get to 26 which goes back to A again. I am sure that there is a simple way to do this but I have a total mental block.

What would you do?

Thanks

Improve this question
5
  • I don't full understand your question. If you want the x-axis you could do data[0].GetLength(1). Or if your 2D array is of different sizes you could do data[i].GetLength(1) Commented Apr 29, 2012 at 16:00
  • I am trying to get the name of the Cell, so for example if I had a 4x4 array and I was starting at A1 the final cell name would be "D4". I can get the 4 easy enough with just data.GetLength(1) but data.GetLength(0) gives me a number, from which I want to work out the corresponding letter ("D"). Commented Apr 29, 2012 at 16:02
  • 3
    use the R1C1 reference style so you don't have to calculate the letter, or get the cell with topLeftCell.Offset(arrayHeight, arrayWidth) Commented Apr 29, 2012 at 16:07
  • @Mathias And the selected answer of that question is more complete than any of the answers here. Commented Apr 30, 2012 at 6:04
  • @phoog Apologies, I didn't see this question, thanks for the heads up! Commented Apr 30, 2012 at 13:59

2 Answers 2

Reset to default
67

Here's a version that also handles two-letter columns (after column Z):

static string GetColumnName(int index)
{
    const string letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

    var value = "";

    if (index >= letters.Length)
        value += letters[index / letters.Length - 1];

    value += letters[index % letters.Length];

    return value;
}
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Please explain the reasoning behind this in words.
@EdwardKarak If you look at the A-Z range as a set of digits, an integer in base 10 n can be converted to a number written in A-Z notation (of at most two digits), where: the first digit is the result of the division of n to the number of digits (26) and the second digit will be the remainder of that division.
It works but has a mistake, When you sent bigger than 701, It throws IndexOutOfRangeException error.
Also, it returns 'AA' for both 26 and 27 - the index is assumed to be zero-based for the first letter, but one-based for the second.
@bafsar, Yes, this only handles two letter column names.
|
1

Just cast it to a char and do "ToString()".

using System;
using System.Collections.Generic;

public class MyClass
{
    public static void RunSnippet()
    {
        int myNumber = 65;
        string myLetter = ((char) myNumber).ToString();
        WL(myLetter);
    }

    #region Helper methods

    public static void Main()
    {
        try
        {
            RunSnippet();
        }
        catch (Exception e)
        {
            string error = string.Format("---\nThe following error occurred while executing the snippet:\n{0}\n---", e.ToString());
            Console.WriteLine(error);
        }
        finally
        {
            Console.Write("Press any key to continue...");
            Console.ReadKey();
        }
    }

    private static void WL(object text, params object[] args)
    {
        Console.WriteLine(text.ToString(), args);   
    }

    private static void RL()
    {
        Console.ReadLine(); 
    }

    private static void Break() 
    {
        System.Diagnostics.Debugger.Break();
    }

    #endregion
}
Improve this answer

3 Comments

This won't even compile, let alone convert the column number properly.
GC Sid, here is a compilable version.
But it still doesn't fully answer the OP's question of how to convert column numbers into names. Once it gets to 27 your code will return [. The first answer here will do it well.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.