javascript 将excel列字母表（例如AA）转换为数字（例如25）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9905533/
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
Convert excel column alphabet (e.g. AA) to number (e.g., 25)
提问by Jaison Justus
In my grid the column headers are named A,B,C...,AA,AB,AC,...etc like an excel spreadsheet. How can I convert the string to number like: A => 1, B => 2, AA => 27
在我的网格中，列标题的名称A,B,C...,AA,AB,AC,...类似于 Excel 电子表格。如何将字符串转换为数字，如：A => 1, B => 2, AA => 27
回答by Yoshi
Try:
尝试：
var foo = function(val) {
var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', i, j, result = 0;
for (i = 0, j = val.length - 1; i < val.length; i += 1, j -= 1) {
result += Math.pow(base.length, j) * (base.indexOf(val[i]) + 1);
}
return result;
};
console.log(['A', 'AA', 'AB', 'ZZ'].map(foo)); // [1, 27, 28, 702]
回答by iMoses
Here's a quick example of the code you should implement. This will work with any given number of letters.
这是您应该实现的代码的快速示例。这将适用于任何给定数量的字母。
function letterToNumbers(string) {
string = string.toUpperCase();
var letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', sum = 0, i;
for (i = 0; i < string.length; i++) {
sum += Math.pow(letters.length, i) * (letters.indexOf(string.substr(((i + 1) * -1), 1)) + 1);
}
return sum;
}
回答by cuixiping
solution 1: best performance and browser compatibility
解决方案 1：最佳性能和浏览器兼容性
// convert A to 1, Z to 26, AA to 27
function lettersToNumber(letters){
var chrs = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ', mode = chrs.length - 1, number = 0;
for(var p = 0; p < letters.length; p++){
number = number * mode + chrs.indexOf(letters[p]);
}
return number;
}
solution 2: best performance and compatibility and shorter code (Recommended)
解决方案 2：最佳性能和兼容性以及更短的代码（推荐）
// convert A to 1, Z to 26, AA to 27
function lettersToNumber(letters){
for(var p = 0, n = 0; p < letters.length; p++){
n = letters[p].charCodeAt() - 64 + n * 26;
}
return n;
}
solution 3: short code (es6 arrow function)
解决方案3：短代码（es6箭头功能）
// convert A to 1, Z to 26, AA to 27
function lettersToNumber(letters){
return letters.split('').reduce((r, a) => r * 26 + parseInt(a, 36) - 9, 0);
}
test:
测试：
['A', 'Z', 'AA', 'AB', 'ZZ','BKTXHSOGHKKE'].map(lettersToNumber);
// [1, 26, 27, 28, 702, 9007199254740991]
lettersToNumber('AAA'); //703
回答by Jaison Justus
i just wrote a junk yard f@#$ snippet... need to be optimized.. :)
我刚刚写了一个垃圾场 f@#$ 片段...需要优化.. :)
charToNum = function(alpha) {
var index = 0
for(var i = 0, j = 1; i < j; i++, j++) {
if(alpha == numToChar(i)) {
index = i;
j = i;
}
}
console.log(index);
}
numToChar = function(number) {
var numeric = (number - 1) % 26;
var letter = chr(65 + numeric);
var number2 = parseInt((number - 1) / 26);
if (number2 > 0) {
return numToChar(number2) + letter;
} else {
return letter;
}
}
chr = function (codePt) {
if (codePt > 0xFFFF) {
codePt -= 0x10000;
return String.fromCharCode(0xD800 + (codePt >> 10), 0xDC00 + (codePt & 0x3FF));
}
return String.fromCharCode(codePt);
}
charToNum('A') => returns 1 and charToNum('AA') => returns 27;
charToNum('A') => 返回 1 和 charToNum('AA') => 返回 27;
回答by Sled
I rewrote Yoshi's answerin a more verbose form that explains better how it works and is easier to port to other languages:
我以更详细的形式重写了Yoshi 的答案，更好地解释了它的工作原理，并且更容易移植到其他语言：
var foo = function(val) {
var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
var baseNumber = base.length;
var runningTotal = 0;
var characterIndex = 0;
var indexExponent = val.length - 1;
while( characterIndex < val.length ){
var digit = val[characterIndex];
var digitValue = base.indexOf(digit) + 1;
runningTotal += Math.pow(baseNumber, indexExponent) * digitValue;
characterIndex += 1
indexExponent -= 1
}
return runningTotal;
};
console.log(['A', 'AA', 'AB', 'ZZ'].map(foo)); // [1, 27, 28, 702]
回答by R.Alonso
Public Function ColLet2Num(Letras As String)
'RALONSO MAYO 2017
'A-> 1
'OQ ->407
'XFD->16384
Dim UnChar As String
Dim NAsc As Long
Dim F As Long
Dim Acum As Long
Dim Indice As Long
Letras = UCase(Letras)
Acum = 0
Indice = 0
For F = Len(Letras) - 1 To 0 Step -1
UnChar = Mid(Letras, F + 1, 1)
NAsc = Asc(UnChar) - 64
Acum = Acum + (NAsc * (26 ^ Indice))
Indice = Indice + 1
Next
If Acum > 16384 Then
MsgBox "La celda máxima es la XFD->16384", vbCritical
End If
ColLet2Num = Acum
End Function
