Generate columns range for Excel (c#)

Question

I need help with a fast function to generate a range with the names of Excel columns, that receives as input the start and end indexes.

i.e. Generate(2, 4) -> B, C, D

Speed is important as I will need to generate ranges of up to 16,384 columns.

Is this for C# (per the title)? If so, you may want to drop that tag in there too :) — RocketDonkey
Will you stop at 26 columns or could have columns AA, AB, etc. — nutsch

Avi Shmidman Avi Shmidman · Accepted Answer · 2012-10-22T18:20:08

The basic algorithm for generating Excel column names has been explicated wonderfully by Graham over here: https://stackoverflow.com/a/182924/1644733

Putting it into the framework of the C# Generate() function, you could do the following:

    static List<string> Generate(int start, int end) {
        List<string> listOfColumns = new List<string>();
        for (int i = start; i<=end; i++) {
            listOfColumns.Add(NumberToColumn(i));
        }
        return listOfColumns;
    }

    static string NumberToColumn(int col) {

        string retval = "";

        // cannot be greater than 16384
        if (col > 16384)
            return "invalid";

        int dividend = col;
        while (dividend > 0)
        {
            int leftover = (dividend - 1) % 26;
            char extraletter = (char) (65 + leftover);
            retval = extraletter + retval;
            dividend = (int)((dividend - leftover) / 26);
        } 
        return retval;
    }

In terms of speed: if you are going to be frequently generating many (thousands) of ranges of this sort, you may want to run this once at the beginning of the code, on the full range of 1-16384, and then just use the values in the resulting List directly, as needed.

Generate columns range for Excel (c#)

1 Answers