Convert a number to a letter in C# for use in Microsoft Excel

Question

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

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)` — noMAD, Apr 29 '12 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"). — JMK, Apr 29 '12 at 16:02
use the R1C1 reference style so you don't have to calculate the letter, or get the cell with topLeftCell.Offset(arrayHeight, arrayWidth) — phoog, Apr 29 '12 at 16:07
@Mathias And the selected answer of that question is more complete than any of the answers here. — Sid Holland, Apr 30 '12 at 06:04
@phoog Apologies, I didn't see this question, thanks for the heads up! — JMK, Apr 30 '12 at 13:59

score 66 · Accepted Answer · answered Apr 29 '12 at 16:33

66

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;
}

answered Apr 29 '12 at 16:33

Cristian Lupascu

39,078
16
100
137

Please explain the reasoning behind this in words. – lost_in_the_source Dec 23 '13 at 21:52
2

@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. – Cristian Lupascu Dec 26 '13 at 15:13
3

It works but has a mistake, When you sent bigger than 701, It throws IndexOutOfRangeException error. – bafsar May 09 '15 at 14:00
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. – MaSiMan Jul 17 '18 at 12:25
@bafsar, Yes, this only handles two letter column names. – Cristian Lupascu Jul 29 '18 at 19:28
@MaSiMan It works correctly for 26 and 27. It returns `AA` and `AB` respectively. – Cristian Lupascu Jul 29 '18 at 19:29

bnieland · Answer 2 · 2012-04-30T11:38:32.890

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
}

This won't even compile, let alone convert the column number properly. — Sid Holland, Apr 30 '12 at 06:08
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](http://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa) will do it well. — Sid Holland, Apr 30 '12 at 16:46

Convert a number to a letter in C# for use in Microsoft Excel

2 Answers2

Linked