Does any one have algorithm or logic to Convert A to 1 ,B to 2, ... ,Z to 26 and then ,AA to 27, AB to 28 etc.
+4
A:
Have a look at these
/// <summary>
/// 1 -> A<br/>
/// 2 -> B<br/>
/// 3 -> C<br/>
/// ...
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
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;
}
/// <summary>
/// A -> 1<br/>
/// B -> 2<br/>
/// C -> 3<br/>
/// ...
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
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;
}
astander
2009-12-23 09:03:47
Works Great ,Thank you
Thunder
2009-12-23 09:13:49
Is there any reason why you are using decimal as type for columnNumber and currentLetterNumber?
winSharp93
2009-12-23 09:17:00
No, this is just the way I originally implemented it. You can change it to ints. Tested quick and it seems fine.
astander
2009-12-23 09:28:23
+20
A:
Here's a simple LINQ expression:
static int TextToNumber(this string text) {
return text
.Select(c => c - 'A' + 1)
.Aggregate((sum, next) => sum*26 + next);
}
This test
Console.WriteLine(" A -> " + "A".TextToNumber());
Console.WriteLine(" B -> " + "B".TextToNumber());
Console.WriteLine(" Z -> " + "Z".TextToNumber());
Console.WriteLine("AA -> " + "AA".TextToNumber());
Console.WriteLine("AB -> " + "AB".TextToNumber());
will produce this output:
A -> 1
B -> 2
Z -> 26
AA -> 27
AB -> 28
Update: Here's the same code but targetting .NET 2.0:
static int TextToNumber(string text) {
int sum = 0;
foreach (char c in text) {
sum = sum*26 + c - 'A' + 1;
}
return sum;
}
Thomas Freudenberg
2009-12-23 09:20:20
+1 for using a fold. You don't even need to convert the string to a char array, since a string behaves like an IEnumerable<char> already. E.g. `s.Select(c => c - 'A' + 1).Aggregate((sum, next) => sum * 26 + next)`
cfern
2009-12-23 09:39:34
Thx cfern. I forgot that string implements IEnumerable<char>. I've updated my answer
Thomas Freudenberg
2009-12-23 09:47:23