#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.
