如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。

Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。


当前回答

我正在尝试在Java中做同样的事情… 我写了以下代码:

private String getExcelColumnName(int columnNumber) {

    int dividend = columnNumber;
    String columnName = "";
    int modulo;

    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;

        char val = Character.valueOf((char)(65 + modulo));

        columnName += val;

        dividend = (int)((dividend - modulo) / 26);
    } 

    return columnName;
}

现在,一旦我用columnNumber = 29运行它,它给我的结果=“CA”(而不是“AC”) 有什么意见吗? 我知道我可以通过StringBuilder....反转它但看着格雷厄姆的回答,我有点困惑....

其他回答

下面是一个Actionscript版本:

private var columnNumbers:Array = ['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'];

    private function getExcelColumnName(columnNumber:int) : String{
        var dividend:int = columnNumber;
        var columnName:String = "";
        var modulo:int;

        while (dividend > 0)
        {
            modulo = (dividend - 1) % 26;
            columnName = columnNumbers[modulo] + columnName;
            dividend = int((dividend - modulo) / 26);
        } 

        return columnName;
    }

看到了另一个VBA答案-这可以在excel-vba中用1行UDF完成:

Function GetColLetter(ByVal colID As Integer) As String
    If colID > Columns.Count Then
        Err.Raise 9, , "Column index out of bounds"
    Else
        GetColLetter = Split(Cells(1, colID).Address, "$")(1)
    End If
End Function

这是我在PHP中的超级后期实现。这个是递归的。我是在发现这篇文章之前写的。我想看看其他人是否已经解决了这个问题……

public function GetColumn($intNumber, $strCol = null) {

    if ($intNumber > 0) {
        $intRem = ($intNumber - 1) % 26;
        $strCol = $this->GetColumn(intval(($intNumber - $intRem) / 26), sprintf('%s%s', chr(65 + $intRem), $strCol));
    }

    return $strCol;
}

Objective-C实现:

-(NSString*)getColumnName:(int)n {
     NSString *name = @"";
     while (n>0) {
     n--;
     char c = (char)('A' + n%26);
     name = [NSString stringWithFormat:@"%c%@",c,name];
     n = n/26;
  }    
     return name;

}

迅速实现:

func getColumnName(n:Int)->String{
 var columnName = ""
 var index = n
 while index>0 {
     index--
     let char = Character(UnicodeScalar(65 + index%26))
     columnName = "\(char)\(columnName)"
     index = index / 26
 }
 return columnName

}

答案是基于:https://stackoverflow.com/a/4532562/2231118

 static string[] ExcelColumnAlphabetIdentifiers = new string[] { "", "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" };
 public static string ExcelColumnAlphabetIdentifier( int ColumnNumber)
    {
        StringBuilder sb = new StringBuilder();
        int remainder = ColumnNumber;
        do
        {
            sb.Append(ExcelColumnAlphabetIdentifiers[remainder % 26]);
            remainder = remainder / 26;
        }
        while (remainder > 0);
       return sb.ToString();
    }