C# 将 Excel 列字母转换为其数字的算法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/667802/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the algorithm to convert an Excel Column Letter into its Number?
提问by David Basarab
I need an algorithm to convert an Excel Column letter to its proper number.
我需要一种算法将 Excel Column 字母转换为其正确的数字。
The language this will be written in is C#, but any would do or even pseudo code.
这将使用的语言是 C#，但任何语言都可以，甚至是伪代码。
Please note I am going to put this in C# and I don't want to use the office dll.
请注意，我将把它放在 C# 中，我不想使用 office dll。
For 'A' the expected result will be 1
对于“A”，预期结果将为 1
For 'AH' = 34
对于“啊”= 34
For 'XFD' = 16384
对于“XFD”= 16384
采纳答案by Ian Nelson
public static int ExcelColumnNameToNumber(string columnName)
{
if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");
columnName = columnName.ToUpperInvariant();
int sum = 0;
for (int i = 0; i < columnName.Length; i++)
{
sum *= 26;
sum += (columnName[i] - 'A' + 1);
}
return sum;
}
回答by Chris
Could you perhaps treat it like a base 26 number, and then substitute letters for a base 26 number?
你能不能把它当作一个基数为 26 的数字，然后用字母代替一个基数为 26 的数字？
So in effect, your right most digit will always be a raw number between 1 and 26, and the remainder of the "number" (the left part) is the number of 26's collected? So A would represent one lot of 26, B would be 2, etc.
因此，实际上，您最右边的数字始终是 1 到 26 之间的原始数字，而“数字”的其余部分（左侧部分）是收集到的 26 的数量？所以 A 代表一批 26，B 代表 2，以此类推。
As an example:
举个例子：
B = 2 = Column 2 AB = 26 * 1(A) + 2 = Column 28 BB = 26 * 2(B) + 2 = Column 54 DA = 26 * 4(D) + 1 = Column 105
etc
等等
回答by Sparr
Loop through the characters from last to first. Multiply the value of each letter (A=1, Z=26) times 26**N, add to a running total. My string manipulation skill in C# is nonexistent, so here is some very mixed pseudo-code:
从最后到第一个循环遍历字符。将每个字母的值 (A=1, Z=26) 乘以 26**N，添加到运行总数中。我在 C# 中的字符串操作技能是不存在的，所以这里有一些非常混合的伪代码：
sum=0;
len=length(letters);
for(i=0;i<len;i++)
sum += ((letters[len-i-1])-'A'+1) * pow(26,i);
回答by mqp
int result = colName.Select((c, i) =>
((c - 'A' + 1) * ((int)Math.Pow(26, colName.Length - i - 1)))).Sum();
回答by wethercotes
int col = colName.ToCharArray().Select(c => c - 'A' + 1).
Reverse().Select((v, i) => v * (int)Math.Pow(26, i)).Sum();
回答by Jesse Puente
Here is a solution I wrote up in JavaScript if anyone is interested.
如果有人感兴趣，这是我用 JavaScript 编写的解决方案。
var letters = "abc".toUpperCase();
var sum = 0;
for(var i = 0; i < letters.length;i++)
{
sum *= 26;
sum += (letters.charCodeAt(i) - ("A".charCodeAt(0)-1));
}
alert(sum);
回答by V. Wolf
in Excel VBA you could use the .RangeMethod to get the number, like so:
在 Excel VBA 中，您可以使用.RangeMethod 获取数字，如下所示：
Dim rng as Range
Dim vSearchCol as variant 'your input column
Set rng.Thisworkbook.worksheets("mySheet").Range(vSearchCol & "1:" & vSearchCol & "1")
Then use .columnproperty:
然后使用.column属性：
debug.print rng.column
if you need full code see below:
如果您需要完整代码，请参见下文：
Function ColumnbyName(vInput As Variant, Optional bByName As Boolean = True) As Variant
Dim Rng As Range
If bByName Then
If Not VBA.IsNumeric(vInput) Then
Set Rng = ThisWorkbook.Worksheets("mytab").Range(vInput & "1:" & vInput & "1")
ColumnbyName = Rng.Column
Else
MsgBox "Please enter valid non Numeric column or change paramter bByName to False!"
End If
Else
If VBA.IsNumeric(vInput) Then
ColumnbyName = VBA.Chr(64 + CInt(vInput))
Else
MsgBox "Please enter valid Numeric column or change paramter bByName to True!"
End If
End If
End Function
回答by B H
I guess this essentially works out pretty much the same as some of the other answers, but it may make a little more clear what's going on with the alpha equivalent of a numeric digit. It's not quite a base 26 system because there is no 0 placeholder. That is, the 26th column would be 'A0' or something instead of Z in base 26. And it's not base 27 because the 'alpha-gits' don't represent powers of 27. Man, it really makes you appreciate what a mess arithmetic must have been before the Babylonians invented the zero!
我想这基本上与其他一些答案几乎相同，但它可能会使数字的 alpha 等价物更清楚一些。它不是一个基本的 26 系统，因为没有 0 占位符。也就是说，第 26 列将是 'A0' 或其他东西，而不是以 26 为基数的 Z。它不是以 27 为基数，因为 'alpha-gits' 不代表 27 的幂。伙计，它真的让你明白这是多么混乱算术一定是在巴比伦人发明零之前出现的！
UInt32 sum = 0, gitVal = 1;
foreach (char alphagit in ColumnName.ToUpperInvariant().ToCharArray().Reverse())
{
sum += gitVal * (UInt32)(alphagit - 'A' + 1)
gitVal *= 26;
}
Like some others, I reversed the character array so I don't need to know anything about exponents.
像其他一些人一样，我反转了字符数组，所以我不需要知道任何关于指数的信息。
回答by Slai
I am not quite happy with any of the answers, so here is a short version:
我对任何答案都不太满意，所以这里有一个简短的版本：
int col = "Ab".Aggregate(0, (a, c) => a * 26 + c & 31); // 28
or better, to ignore non A-Za-zcharacters:
或者更好，忽略非A-Za-z字符：
int col = " !$Ab ".Aggregate(0, (a, c) => (uint)((c | 32) - 97) > 25 ? a : a * 26 + c & 31); // 28
