Fastest function to generate Excel column letters in C#

Question

What is the fastest c# function that takes and int and returns a string containing a letter or letters for use in an Excel function? For example, 1 returns "A", 26 returns "Z", 27 returns "AA", etc.

This is called tens of thousands of times and is taking 25% of the time needed to generate a large spreadsheet with many formulas.

public string Letter(int intCol) {

    int intFirstLetter = ((intCol) / 676) + 64;
    int intSecondLetter = ((intCol % 676) / 26) + 64;
    int intThirdLetter = (intCol % 26) + 65;

    char FirstLetter = (intFirstLetter > 64) ? (char)intFirstLetter : ' ';
    char SecondLetter = (intSecondLetter > 64) ? (char)intSecondLetter : ' ';
    char ThirdLetter = (char)intThirdLetter;

    return string.Concat(FirstLetter, SecondLetter, ThirdLetter).Trim();
}

Figment

can you post your current function? Something as simple as this should not be 25% of your processing. – Wellgrounded

See this question: <#297713> – Leuctra

I'm not sure how fast my code would be. But it should be a darn site faster than the original code you posted. My code is posted at blackbeltcoder.com/Articles/strings/…. – Weathercock

See also this question: #182096 – Ventre

If you're using the Excel Automation library from Microsoft, your speed issue may not be related to your column letter function. I have some code that runs in a loop and applies formulas to cells. There is no complex logic, but it runs terribly slow. – Edy

Answer 1

I currently use this, with Excel 2007

public static string ExcelColumnFromNumber(int column)
        {
            string columnString = "";
            decimal columnNumber = column;
            while (columnNumber > 0)
            {
                decimal currentLetterNumber = (columnNumber - 1) % 26;
                char currentLetter = (char)(currentLetterNumber + 65);
                columnString = currentLetter + columnString;
                columnNumber = (columnNumber - (currentLetterNumber + 1)) / 26;
            }
            return columnString;
        }

and

public static int NumberFromExcelColumn(string column)
        {
            int retVal = 0;
            string col = column.ToUpper();
            for (int iChar = col.Length - 1; iChar >= 0; iChar--)
            {
                char colPiece = col[iChar];
                int colNum = colPiece - 64;
                retVal = retVal + colNum * (int)Math.Pow(26, col.Length - (iChar + 1));
            }
            return retVal;
        }

As mentioned in other posts, the results can be cached.

Generalissimo

+1 : Might not be the fastest, but useful for the stuff I need to do :) – Sanbo

Nice variant on conversion from one base to another! :) – Polypus

Upvote for not searching in a string or character array – Gazetteer

Answer 2

I can tell you that the fastest function will not be the prettiest function. Here it is:

private string[] map = new string[]
    { 
        "A", "B", "C", "D", "E" .............
    };

public string getColumn(int number)
{
    return map[number];
}

Tubuliflorous

A good point to draw attention to the array approach, though defining it manually wouldn't be such a great idea. Pre-generation is the way to go. – Lavalava

Hey, he asked for the fastest! Any code you add to automatically pre-populate it is going to be slower ;) – Tubuliflorous

@womp: This is true... though it's a one-off operation, so it's effectively discountable. What's the difference between generating the enormous array as code or during initialisation, except messiness? I know, you're just being pedantic and taking it literally for the fun of it (unless I'm mistaken). – Lavalava

Nope, you're right :) Clearly it would be MUCH more practical to pre-generate the array with some code. But this is technically the absolute fastest. Just for the record, I voted up some other answers ;) – Tubuliflorous

By the way, this map should be static, so that it is not duplicated (and potentially regenerated) for each instance. – Diplodocus

Answer 3

Don't convert it at all. Excel can work in R1C1 notation just as well as in A1 notation.

So (apologies for using VBA rather than C#):

Application.Worksheets("Sheet1").Range("B1").Font.Bold = True

can just as easily be written as:

Application.Worksheets("Sheet1").Cells(1, 2).Font.Bold = True

The Range property takes A1 notation whereas the Cells property takes (row number, column number).

To select multiple cells: Range(Cells(1, 1), Cells(4, 6)) (NB would need some kind of object qualifier if not using the active worksheet) rather than Range("A1:F4")

The Columns property can take either a letter (e.g. F) or a number (e.g. 6)

Ludovico

Answer 4

Here's my version: This does not have any limitation as such 2-letter or 3-letter. Simply pass-in the required number (starting with 0) Will return the Excel Column Header like Alphabet sequence for passed-in number:

private string GenerateSequence(int num)
{
    string str = "";
    char achar;
    int mod;
    while (true)
    {
        mod = (num % 26) + 65;
        num = (int)(num / 26);
        achar = (char)mod;
        str = achar + str;
        if (num > 0) num--;
        else if (num == 0) break;
    }
    return str;
}

I did not tested this for performance, if someone can do that will great for others. (Sorry for being lazy) :)

Cheers!

Albertalberta

Answer 5

Here is a concise implementation using LINQ.

static IEnumerable<string> GetExcelStrings()
{
    string[] alphabet = { string.Empty, "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };

    return from c1 in alphabet
           from c2 in alphabet
           from c3 in alphabet.Skip(1)                    // c3 is never empty
           where c1 == string.Empty || c2 != string.Empty // only allow c2 to be empty if c1 is also empty
           select c1 + c2 + c3;
}

This generates A to Z, then AA to ZZ, then AAA to ZZZ.

On my PC, calling GetExcelStrings().ToArray() takes about 30 ms. Thereafter, you can refer to this array of strings if you need it thousands of times.

Caste

Thanks for the constraint based programming. It is great for some tasks!. Here's a lazy alphabet which might speed up the initial generation even more: var a = new string[] { "" }.Union(from c in Enumerable.Range((int)'A', 26) select Convert.ToString((char)c)); – Schizomycete

@pwned Although having a lazy data source can often reduce latency, in this case the creation of alphabet is near-instantaneous so I don't think it will help. A quick test with your amendment suggests that it slows down the total runtime (1000 repetitions went from 14 secs to over 20). – Caste

Answer 6

You could pre-generate all the values into an array of strings. This would take very little memory and could be calculated on the first call.

Nicko

Answer 7

The absolute FASTEST, would be capitalizing that the Excel spreadsheet only a fixed number of columns, so you would do a lookup table. Declare a constant string array of 256 entries, and prepopulate it with the strings from "A" to "IV". Then you simply do a straight index lookup.

Broyles

I don't know where you are getting that Excel has a fixed 256 columns. I started scrolling and adding text, and I am on AEW and have given up. – Fossilize

It used to, before Excel 2007, which allows 16384 (XFD). Still, don't blame you for quitting. ;) – Versus

I'm running Office 2005. Apparently the 256 column limit has been extended (or lifted entirely?) in newer versions. Which, of course, makes it more important to programatically seed your lookup table. – Broyles

Answer 8

Try this function.

// Returns name of column for specified 0-based index.
public static string GetColumnName(int index)
{
    var name = new char[3]; // Assumes 3-letter column name max.
    int rem = index;
    int div = 17576; // 26 ^ 3

    for (int i = 2; i >= 0; i++)
    {
        name[i] = alphabet[rem / div];
        rem %= div;
        div /= 26;
    }

    if (index >= 676)
        return new string(name, 3);
    else if (index >= 26)
        return new string(name, 2);
    else
        return new string(name, 1);
}

Now it shouldn't take up that much memory to pre-generate each column name for every index and store them in a single huge array, so you shouldn't need to look up the name for any column twice.

If I can think of any further optimisations, I'll add them later, but I believe this function should be pretty quick, and I doubt you even need this sort of speed if you do the pre-generation.

Lavalava

@esac: You're absolutely right. (And there was even another with the for loop. :P) I shouldn't be writing code at this hour, frankly... So yeah, that did deserve a downvote in fairness. Thanks for having the courtesy to remove it though. :) +1 to you for the corrections. – Lavalava

return new string(name, 3); there is no overload for string(char[], int). Maybe you meant "new string(name)". Also you get an index out of the bounds of the array exception for case index = (26 % 100) on the line name[i] = alphabet[rem / div]; (yes i have alphabet defined as earlier) – Fossilize

Answer 9

Once your function has run, let it cache the results into a dictionary. So that, it won't have to do the calculation again.

e.g. Convert(27) will check if 27 is mapped/stored in dictionary. If not, do the calculation and store "AA" against 27 in the dictionary.

Troat

Answer 10

Your first problem is that you are declaring 6 variables in the method. If a methd is going to be called thousands of times, just moving those to class scope instead of function scope will probably cut your processing time by more than half right off the bat.

Wellgrounded

Answer 11

This is written in Java, but it's basically the same thing.

Here's code to compute the label for the column, in upper-case, with a 0-based index:

public static String findColChars(long index) {
    char[] ret = new char[64];
    for (int i = 0; i < ret.length; ++i) {
        int digit = ret.length - i - 1;
        long test = index - powerDown(i + 1);
        if (test < 0)
            break;
        ret[digit] = toChar(test / (long)(Math.pow(26, i)));
    }
    return new String(ret);
}

private static char toChar(long num) {
    return (char)((num % 26) + 65);
}

Here's code to compute 0-based index for the column from the upper-case label:

public static long findColIndex(String col) {
    long index = 0;
    char[] chars = col.toCharArray();
    for (int i = 0; i < chars.length; ++i) {
        int cur = chars.length - i - 1;
        index += (chars[cur] - 65) * Math.pow(26, i);
    }
    return index + powerDown(chars.length);
}

private static long powerDown(int limit) {
    long acc = 0;
    while (limit > 1)
        acc += Math.pow(26, limit-- - 1);
    return acc;
}

Godfather

Umm... Your Convert back function has issues... It always returns -1 – Nonpareil

Correction, it happens when the value from findColChars is passed into it. – Nonpareil

Answer 12

@Neil N -- nice code I think the thirdLetter should have a +64 rather than +65 ? am I right?

public string Letter(int intCol) {

    int intFirstLetter = ((intCol) / 676) + 64;
    int intSecondLetter = ((intCol % 676) / 26) + 64;
    int intThirdLetter = (intCol % 26) + 65;  ' SHOULD BE + 64?

    char FirstLetter = (intFirstLetter > 64) ? (char)intFirstLetter : ' ';
    char SecondLetter = (intSecondLetter > 64) ? (char)intSecondLetter : ' ';
    char ThirdLetter = (char)intThirdLetter;

    return string.Concat(FirstLetter, SecondLetter, ThirdLetter).Trim();
}

Rm

Answer 13

Why don't we try factorial?

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

    int NextPos = (index / 26);
    int LastPos = (index % 26);
    if (LastPos == 0) NextPos--;

    if (index > 26)
        return GetColumnName(NextPos) + letters[LastPos];
    else
        return letters[LastPos] + "";
}

Delineation

I tried your code and it wasn't working at first because LastPos will return a decimal number (at least in JavaScript) and I had to use Math.floor to take the closest integer number const LastPos = Math.floor(index % 26); and then it works. Also note that I ran a perf test, using this way #111868, for 1 million call with random index and it's pretty much the same perf as the accepted answer (100.72ms vs 100.88ms, 100.80ms vs 100.75ms) – Garland

Answer 14

Caching really does cut the runtime of 10,000,000 random calls to 1/3 its value though:

    static Dictionary<int, string> LetterDict = new Dictionary<int, string>(676);
    public static string LetterWithCaching(int index)
    {
        int intCol = index - 1;
        if (LetterDict.ContainsKey(intCol)) return LetterDict[intCol];
        int intFirstLetter = ((intCol) / 676) + 64;
        int intSecondLetter = ((intCol % 676) / 26) + 64;
        int intThirdLetter = (intCol % 26) + 65;
        char FirstLetter = (intFirstLetter > 64) ? (char)intFirstLetter : ' ';
        char SecondLetter = (intSecondLetter > 64) ? (char)intSecondLetter : ' ';
        char ThirdLetter = (char)intThirdLetter;
        String s = string.Concat(FirstLetter, SecondLetter, ThirdLetter).Trim();
        LetterDict.Add(intCol, s);
        return s;
    }

I think caching in the worst-case (hit every value) couldn't take up more than 250kb (17576 possible values * (sizeof(int)=4 + sizeof(char)*3 + string overhead=2)

Thermae

Answer 15

It is recursive. Fast, and right :

class ToolSheet
{


    //Not the prettyest but surely the fastest :
    static string[] ColName = new string[676];


    public ToolSheet()
    {
        ColName[0] = "A";
        for (int index = 1; index < 676; ++index) Recurse(index, index);

    }

    private int Recurse(int i, int index)
    {
        if (i < 1) return 0;
        ColName[index] = ((char)(65 + i % 26)).ToString() + ColName[index];

        return Recurse(i / 26, index);
    }

    public string GetColName(int i)
    {
        return ColName[i - 1];
    }



}

Hooknose

Answer 16

sorry there was a shift. corrected.

class ToolSheet
{


    //Not the prettyest but surely the fastest :
    static string[] ColName = new string[676];


    public ToolSheet()
    {

        for (int index = 0; index < 676; ++index)
        {
            Recurse(index, index);
        }

    }

    private int Recurse(int i, int index)
    {
        if (i < 1)
        {
            if (index % 26 == 0 && index > 0) ColName[index] = ColName[index - 1].Substring(0, ColName[index - 1].Length - 1) + "Z";

            return 0;
        }


        ColName[index] = ((char)(64 + i % 26)).ToString() + ColName[index];


        return Recurse(i / 26, index);
    }

    public string GetColName(int i)
    {
        return ColName[i - 1];
    }



}

Hooknose

Answer 17

My solution:

static class ExcelHeaderHelper
{
    public static string[] GetHeaderLetters(uint max)
    {
        var result = new List<string>();
        int i = 0;
        var columnPrefix = new Queue<string>();
        string prefix = null;
        int prevRoundNo = 0;
        uint maxPrefix = max / 26;

        while (i < max)
        {
            int roundNo = i / 26;
            if (prevRoundNo < roundNo)
            {
                prefix = columnPrefix.Dequeue();
                prevRoundNo = roundNo;
            }
            string item = prefix + ((char)(65 + (i % 26))).ToString(CultureInfo.InvariantCulture);
            if (i <= maxPrefix)
            {
                columnPrefix.Enqueue(item);
            }
            result.Add(item);
            i++;
        }
        return result.ToArray();
    }
}

Blockhouse

Answer 18

barrowc's idea is much more convenient and fastest than any conversion function! i have converted his ideas to actual c# code that i use:

  var start = m_xlApp.Cells[nRow1_P, nCol1_P];
  var end = m_xlApp.Cells[nRow2_P, nCol2_P];
  // cast as Range to prevent binding errors
  m_arrRange = m_xlApp.get_Range(start as Range, end as Range);
  object[] values = (object[])m_arrRange.Value2;

Odrick

Answer 19

private String columnLetter(int column) {
    if (column <= 0) 
        return "";
    if (column <= 26){
        return (char) (column + 64) + "";
    }

    if (column%26 == 0){
        return columnLetter((column/26)-1) + columnLetter(26) ;        
    }

    return columnLetter(column/26) + columnLetter(column%26) ;        
}

Shandy

Answer 20

Just use an Excel formula instead of a user-defined function (UDF) or other program, per Allen Wyatt (https://excel.tips.net/T003254_Alphabetic_Column_Designation.html):

=SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),"")

(In my organization, using UDFs would be very painful.)

Managua

I believe the post was requesting help with C# code, this will not work. – Indeterminable

Answer 21

The code I'm providing is NOT C# (instead is python) but the logic can be used for any language.

Most of previous answers are correct. Here is one more way of converting column number to excel columns. solution is rather simple if we think about this as a base conversion. Simply, convert the column number to base 26 since there is 26 letters only. Here is how you can do this:

steps:

• set the column as a quotient

• subtract one from quotient variable (from previous step) because we need to end up on ascii table with 97 being a.

• divide by 26 and get the remainder.

• add +97 to remainder and convert to char (since 97 is "a" in ASCII table)
• quotient becomes the new quotient/ 26 (since we might go over 26 column)
• continue to do this until quotient is greater than 0 and then return the result

here is the code that does this :)

def convert_num_to_column(column_num):
    result = ""
    quotient = column_num
    remainder = 0
    while (quotient >0):
        quotient = quotient -1
        remainder = quotient%26
        result = chr(int(remainder)+97)+result
        quotient = int(quotient/26)
    return result

print("--",convert_num_to_column(1).upper())

Sostenuto

Answer 22

If you need to generate letters not starting only from A1

private static string GenerateCellReference(int n, int startIndex = 65)
            {
                string name = "";
                n += startIndex - 65;

                while (n > 0)
                {
                    n--;
                    name = (char)((n % 26) + 65) + name;
                    n /= 26;
                }

                return name + 1;
            }

Judaica

Answer 23

After looking at the answers, I thought about how performant they would be where these methods are called very often.

I wrote my own implementation using a predefined/generated array with a fixed, but also flexible in length, and the results speak for themselves. Sometimes we need to consider performance over ease of implementation.

Here is a snippet:

static int _maxColums = -1;

static string[] _columns = null!;

static string GetColumnByIndex(int index)
{
    return _columns[index];
}

static void GenerateColumns(int maxColumns)
{
    _maxColums = maxColumns;
    _columns = new string[_maxColums];

    string prefix = "";

    int currentAlphaIndex = 1;
    int currentMainIndex = 0;

    for (int i = 0; i < maxColumns; i++)
    {
        _columns[i] = $"{prefix}{(char)(currentAlphaIndex + 64)}";

        currentAlphaIndex++;

        if (currentAlphaIndex > 26)
        {
            prefix = _columns[currentMainIndex];
            currentAlphaIndex = 1;
            currentMainIndex++;
        }
    }
}

Here is a benchmark test against the top rated answer:

using System.Diagnostics;

Random random = new Random();

int randIt = random.Next(20000, 1000000);

GenerateColumns(randIt);

Stopwatch sw = Stopwatch.StartNew();

sw.Start();

for (int i = 0; i < randIt; i++)
    GetColumnByIndex(i);
double arrayTime = sw.Elapsed.TotalNanoseconds;
Console.WriteLine($"Array Time: {arrayTime}");

sw.Restart();

for (int i = 0; i < randIt; i++)
    ExcelColumnFromNumber(i);
double everyTime = sw.Elapsed.TotalNanoseconds;
Console.WriteLine($"Every Time: {everyTime}");

sw.Stop();


Console.WriteLine($"Difference: {((everyTime / arrayTime) * 100):N2} %");

Example Output:

Array Time: 969400
Every Time: 186148700
Difference: 19 202.47 %

The benchmark, albeit random, was consistently at least 100x faster.

Intenerate