Generate (lazy and infinite) list of excel column names sequence

Posted on Updated on


While I was looking into the excel data population, I found that in order to populate the data with different column size (fields) into excel, I will definitely require some list of excel column names for this purpose.

 

// "A" = column index (int) to populate from 
// "E" = column index (int) based on A above + total fields count
worksheet.get_Range("A1:E10000"); 

The interesting here is how the excel column names sequence is not the same as number sequence (below)

Excel Sequence

There are various ways described here which will get the job done, and excel is limited to 16,384 columns (last column name is XFD)

However, I’m keen to create a function which can generate “unlimited” sequence. I got this idea from the recent Functional Programming course where Haskell has this infinite list and lazy evaluation and in C#, this laziness can be represented with IEnumerable and yield.

Here’s the function:

/// <summary>
/// This is an infinite list of excel kind of sequence, the caller will be controlling how much of data that it really requires
/// The method signature is very similar to Enumerable.Range(start, count)
/// 
/// Whereas, the other 2 optionals parameters are not for the caller, they are only for internal recursive call
/// </summary>
/// <param name="start">The value of the first integer in the sequence.</param>
/// <param name="count">The number of sequential integers to generate.</param>
/// <param name="depth">NOT TO BE SPECIFIED DIRECTLY, used for recursive purpose</param>
/// <param name="slots">NOT TO BE SPECIFIED DIRECTLY, used for recursive purpose</param>
/// <returns></returns>
IEnumerable<int[]> Iterate(int start, int count, int depth = 1, int[] slots = null)
{
    if (slots == null) slots = new int[depth];

    for (int i = start; i < start + count; i++)
    {
        slots[depth - 1] = i;

        if (depth > 1)
            foreach (var x in Iterate(start, count, depth - 1, slots)) yield return x;
        else
            yield return slots.Reverse().ToArray();
    }

    if (slots.Length == depth)
        foreach (var x in Iterate(start, count, depth + 1, null)) yield return x;
}

Any of the code below will generate the same column names from “A” to “XFD” in 30ms on my laptop (i7 2.5Ghz Quad core)

foreach (var item in Iterate(0, 26).Take(16384))
{
    var columnName = string.Join("-", item.Select(x => (char)(x + 65)).ToArray()).Trim();
    Debug.WriteLine(columnName);
}

foreach (var item in Iterate(65, 26).Take(16384))
{
    var columnName = string.Join("-", item.Select(x => (char)x).ToArray()).Trim();
    Debug.WriteLine(columnName);
}

And the most interesting “infinite” test :)

var i = 0;
foreach (var item in Iterate(0, 26))
{
    var columnName = string.Join("-", item.Select(x => (char)(x + 65)).ToArray()).Trim();
    Debug.WriteLine("#{0} = {1}", i, columnName);
    i++;
}

/* 
#0 = A
.....
#16383 = X-F-D (excel limit)
.....
#133866109 = K-F-X-J-T-N
.....
#321272406 = A-A-A-A-A-A-A
.....
*/
Advertisements

One thought on “Generate (lazy and infinite) list of excel column names sequence

    […] One obvious answer will be using Reflection but there is also another alternative which is generating dynamic methods using Expression Tree. There are pros and cons between the 2 options above (nicely explained by this post). […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s