Convert index to letter excel like

Follow extension methods allow to convert a number to a string ( mostly used to refer a spreadsheet column name ) and back the string to column index ( first column index = 0 ).

---

#region Lib0.Spreadsheet, Copyright(C) 2015 Lorenzo Delana, License under Ms-PL
/*
 * This source is subject to the Microsoft Public License (Ms-PL).
 * Please see http://www.microsoft.com/en-us/openness/licenses.aspx for details.
 * 
 * Author : Lorenzo Delana <user@searchathing.com>
 * 
 * Contributors :
*/
#endregion

using System.Text;

namespace Lib0.Spreadsheet
{

    public static class Ext
    {

        static readonly string Alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

        /// <summary>
        /// Convert spreadsheet column string to index (first column index = 0).        
        /// Pre: value must non empty uppercase letters.
        /// </summary>                
        public static int LetterToIndex(this string value)
        {
            var sb = new StringBuilder();

            return LetterToIndex(value, 0, sb);
        }

        /// <summary>
        /// LetterToIndex (recursive body).
        /// </summary>        
        static int LetterToIndex(this string value, int curIdx, StringBuilder sb)
        {
            var res = 0;

            int pow = value.Length - curIdx - 1;
            int charpos = value[curIdx] - Alphabet[0];

            if (pow > 0)
            {
                var x = 1; do { x *= 26; --pow; } while (pow != 0);
                res += x * (charpos + 1);

                res += LetterToIndex(value, curIdx + 1, sb);
            }
            else
                res += charpos;

            return res;
        }

        /// <summary>
        /// Convert index to spreadsheet column (first column index = 0).
        /// </summary>        
        public static string IndexToLetter(this int index)
        {
            if (index < 0) return string.Empty;

            var sb = new StringBuilder();

            IndexToLetter(index, sb);

            return sb.ToString();
        }

        /// <summary>
        /// IndexToLetter (recursive body).
        /// </summary>        
        public static void IndexToLetter(this int index, StringBuilder sb)
        {
            do
            {
                var x = index / 26;
                var r = index % 26;

                if (x > 0)
                    IndexToLetter(x - 1, sb);
                else
                    sb.Append(Alphabet[r]);

                if (x == 0) break;

                index -= 26 * x;
            }
            while (index >= 0);
        }

    }

}

---

Timing considerations

Pow using double

test total time : 00:00:06.9890933

Pow using integer

test total time : 00:00:04.7791627

Considerations about the Math.Pow

• test timing difference total : about 2 seconds
• timing difference from the inclusive samples 2118 (using Math.Pow) vs 205 (using custom integer pow)

Why int Math.Pow(int, int) extension method cannot exists

I think, but not sure, that the reason why Math.Pow not have an overloaded method is cause :

• power of number using integers can easily run overflow
• introduce such method now will results in an inconsistent behavior of the current code cause the current method upcast integer arguments to double, so that I could have :
• Math.Pow(20, 100) returns about 1.25e130
• Math.Pow(20, 100) return 0

A solution to avoid conflict could be to add an Math.IPow(int,int) using different name or create your own extension.

Convert index to letter excel like by Lorenzo Delana is licensed under a Creative Commons Attribution 4.0 International License.