.net 将列索引转换为 Excel 列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/297213/
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
Translate a column index into an Excel Column Name
提问by Joel Coehoorn
Given a columns' index, how can you get an Excel column name?
给定列的索引，如何获得 Excel 列名？
The problem is trickier than it sounds because it's notjust base-26. The columns don't wrap over like normal digits would. Even the Microsoft Support Exampledoesn't scale beyond ZZZ.
问题是棘手比它听起来，因为它不只是基地-26。列不会像普通数字那样换行。甚至Microsoft 支持示例也不会扩展到 ZZZ 之外。
Disclaimer: This is some code I had done a while back, and it came across my desktop again today. I thought it was worthy of posting here as a pre-answered question.
免责声明：这是我不久前完成的一些代码，今天它再次出现在我的桌面上。我认为值得在这里发布一个预先回答的问题。
回答by Joel Coehoorn
The answer I came up with is to get a little recursive. This code is in VB.Net:
我想出的答案是有点递归。此代码在 VB.Net 中：
Function ColumnName(ByVal index As Integer) As String
Static chars() As Char = {"A"c, "B"c, "C"c, "D"c, "E"c, "F"c, "G"c, "H"c, "I"c, "J"c, "K"c, "L"c, "M"c, "N"c, "O"c, "P"c, "Q"c, "R"c, "S"c, "T"c, "U"c, "V"c, "W"c, "X"c, "Y"c, "Z"c}
index -= 1 ''//adjust so it matches 0-indexed array rather than 1-indexed column
Dim quotient As Integer = index \ 26 ''//normal / operator rounds. \ does integer division, which truncates
If quotient > 0 Then
ColumnName = ColumnName(quotient) & chars(index Mod 26)
Else
ColumnName = chars(index Mod 26)
End If
End Function
And in C#:
在 C# 中：
string ColumnName(int index)
{
index -= 1; //adjust so it matches 0-indexed array rather than 1-indexed column
int quotient = index / 26;
if (quotient > 0)
return ColumnName(quotient) + chars[index % 26].ToString();
else
return chars[index % 26].ToString();
}
private char[] chars = new char[] {'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};
The only downside it that it uses 1-indexed columns rather than 0-indexed.
唯一的缺点是它使用 1 索引列而不是 0 索引列。
回答by Joey
Here's Joel's awesome code modified to work with zero-based column indexes and without the char array.
这是 Joel 修改后的很棒的代码，可以使用从零开始的列索引，而无需使用 char 数组。
Public Shared Function GetExcelColumn(ByVal index As Integer) As String
Dim quotient As Integer = index \ 26 ''//Truncate
If quotient > 0 Then
Return GetExcelColumn(quotient - 1) & Chr((index Mod 26) + 64).ToString
Else
Return Chr(index + 64).ToString
End If
End Function
回答by Ken Paul
It's for this very reason that I avoid column names in programmed interface to Excel. Using column numbersworks very well in Cell(r,c) references and R1C1 addressing.
正是出于这个原因，我避免在 Excel 的编程界面中使用列名。在 Cell(r,c) 引用和 R1C1 寻址中使用列号非常有效。
EDIT: The Range function also takes cell references, as in Range(Cell(r1,c1),Cell(r2,c2)). Also, you can use the Address function to get the A1-style address of a cell or range.
编辑：Range 函数也接受单元格引用，如 Range(Cell(r1,c1),Cell(r2,c2))。此外，您可以使用 Address 函数获取单元格或区域的 A1 样式地址。
EDIT2: Here's a VBA function that uses the Address() function to retrieve the column name:
EDIT2：这是一个使用 Address() 函数来检索列名的 VBA 函数：
Function colname(colindex)
x = Cells(1, colindex).Address(False, False) ' get the range name (e.g. AB1)
colname = Mid(x, 1, Len(x) - 1) ' return all but last character
End Function
回答by John Machin
# Python 2.x, no recursive function calls
def colname_from_colx(colx):
assert colx >= 0
colname = ''
r = colx
while 1:
r, d = divmod(r, 26)
colname = chr(d + ord('A')) + colname
if not r:
return colname
r -= 1
回答by Mathe Szabolcs
public static String translateColumnIndexToName(int index) {
//assert (index >= 0);
int quotient = (index)/ 26;
if (quotient > 0) {
return translateColumnIndexToName(quotient-1) + (char) ((index % 26) + 65);
} else {
return "" + (char) ((index % 26) + 65);
}
}
and the test:
和测试：
for (int i = 0; i < 100; i++) {
System.out.println(i + ": " + translateColumnIndexToName(i));
}
here is the output:
这是输出：
0: A
1: B
2: C
3: D
4: E
5: F
6: G
7: H
8: I
9: J
10: K
11: L
12: M
13: N
14: O
15: P
16: Q
17: R
18: S
19: T
20: U
21: V
22: W
23: X
24: Y
25: Z
26: AA
27: AB
28: AC
I needed 0 based for POI
我需要基于 0 的 POI
and translation from index to names:
以及从索引到名称的翻译：
public static int translateComunNameToIndex0(String columnName) {
if (columnName == null) {
return -1;
}
columnName = columnName.toUpperCase().trim();
int colNo = -1;
switch (columnName.length()) {
case 1:
colNo = (int) columnName.charAt(0) - 64;
break;
case 2:
colNo = ((int) columnName.charAt(0) - 64) * 26 + ((int) columnName.charAt(1) - 64);
break;
default:
//illegal argument exception
throw new IllegalArgumentException(columnName);
}
return colNo;
}
回答by dana
This is an old post, but after seeing some of the solutions I came up with my own C# variation. 0-Based, without recursion:
这是一篇旧帖子，但在看到一些解决方案后，我想出了自己的 C# 变体。基于 0，无递归：
public static String GetExcelColumnName(int columnIndex)
{
if (columnIndex < 0)
{
throw new ArgumentOutOfRangeException("columnIndex: " + columnIndex);
}
Stack<char> stack = new Stack<char>();
while (columnIndex >= 0)
{
stack.Push((char)('A' + (columnIndex % 26)));
columnIndex = (columnIndex / 26) - 1;
}
return new String(stack.ToArray());
}
Here are some test results at key transition points:
以下是一些关键过渡点的测试结果：
0: A
1: B
2: C
...
24: Y
25: Z
26: AA
27: AB
...
50: AY
51: AZ
52: BA
53: BB
...
700: ZY
701: ZZ
702: AAA
703: AAB
回答by Micah Stubbs
in python, with recursion. translated from Joey's answer. so far, it's tested to work up to GetExcelByColumn(35) = 'AI'
在 python 中，使用递归。翻译自乔伊的回答。到目前为止，它已经过测试可以达到 GetExcelByColumn(35) = 'AI'
def GetExcelColumn(index):
quotient = int(index / 26)
if quotient > 0:
return GetExcelColumn(quotient) + str(chr((index % 26) + 64))
else:
return str(chr(index + 64))
回答by Ally
JavaScript Solution
JavaScript 解决方案
/**
* Calculate the column letter abbreviation from a 0 based index
* @param {Number} value
* @returns {string}
*/
getColumnFromIndex = function (value) {
var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('');
value++;
var remainder, result = "";
do {
remainder = value % 26;
result = base[(remainder || 26) - 1] + result;
value = Math.floor(value / 26);
} while (value > 0);
return result;
};
回答by eka808
The php version, thank's to this post to help me figure it out ! ^^
php版本，感谢这篇文章帮我弄明白了！^^
/**
* Get excel column name
* @param index : a column index we want to get the value in excel column format
* @return (string) : excel column format
*/
function getexcelcolumnname($index) {
//Get the quotient : if the index superior to base 26 max ?
$quotient = $index / 26;
if ($quotient >= 1) {
//If yes, get top level column + the current column code
return getexcelcolumnname($quotient-1). chr(($index % 26)+65);
} else {
//If no just return the current column code
return chr(65 + $index);
}
}
回答by Mark Lane
I enjoy writing recursive functions, but I don't think it's necessary here. This is my solution in VB. It works up to column ZZ. If someone can tell me if it works for AAA to ZZZ that would be nice to know.
我喜欢编写递归函数，但我认为这里没有必要。这是我在VB中的解决方案。它适用于 ZZ 列。如果有人能告诉我它是否适用于 AAA 到 ZZZ，那就太好了。
Public Function TranslateColumnIndexToName(index As Integer) As String
'
Dim remainder As Integer
Dim remainder2 As Integer
Dim quotient As Integer
Dim quotient2 As Integer
'
quotient2 = ((index) / (26 * 26)) - 2
remainder2 = (index Mod (26 * 26)) - 1
quotient = ((remainder2) / 26) - 2
remainder = (index Mod 26) - 1
'
If quotient2 > 0 Then
TranslateColumnIndexToName = ChrW(quotient2 + 65) & ChrW(quotient + 65) & ChrW(remainder + 65)
ElseIf quotient > 0 Then
TranslateColumnIndexToName = ChrW(quotient + 65) & ChrW(remainder + 65)
Else
TranslateColumnIndexToName = ChrW(remainder + 65)
End If
End Function
结束函数
