将列索引转换为Excel列名

给定列的索引,如何获得Excel列名？

问题是棘手比它听起来,因为它不只是基地-26.列不像普通数字那样换行.即使是Microsoft支持示例也不会扩展到ZZZ之外.

_{免责声明:这是我之前做过的一些代码,它今天再次遇到了我的桌面.我认为值得在这里发布作为预先回答的问题.}

我想出的答案是获得一点递归.这段代码在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

在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'};

唯一的缺点是它使用1索引列而不是0索引.

这是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

正是出于这个原因,我在Excel的编程接口中避免了列名.使用列号在Cell(r,c)引用和R1C1寻址中非常有效.

编辑:范围功能也采用单元格引用,如范围(单元格(r1,c1),单元格(r2,c2)).此外,您可以使用地址功能来获取单元格或范围的A1样式地址.

EDIT2:这是一个VBA函数,它使用Address()函数来检索列名:

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