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

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


当前回答

只是抛出一个简单的使用递归的两行c#实现,因为这里所有的答案似乎都比必要的复杂得多。

/// <summary>
/// Gets the column letter(s) corresponding to the given column number.
/// </summary>
/// <param name="column">The one-based column index. Must be greater than zero.</param>
/// <returns>The desired column letter, or an empty string if the column number was invalid.</returns>
public static string GetColumnLetter(int column) {
    if (column < 1) return String.Empty;
    return GetColumnLetter((column - 1) / 26) + (char)('A' + (column - 1) % 26);
}

其他回答

我是这样做的:

private string GetExcelColumnName(int columnNumber)
{
    string columnName = "";

    while (columnNumber > 0)
    {
        int modulo = (columnNumber - 1) % 26;
        columnName = Convert.ToChar('A' + modulo) + columnName;
        columnNumber = (columnNumber - modulo) / 26;
    } 

    return columnName;
}

(我知道这个问题与c#有关,但是,如果读者需要用Java做同样的事情,那么下面的内容可能会有用)

事实证明,使用Jakarta POI中的“CellReference”类可以很容易地做到这一点。此外,转换可以以两种方式进行。

// Convert row and column numbers (0-based) to an Excel cell reference
CellReference numbers = new CellReference(3, 28);
System.out.println(numbers.formatAsString());

// Convert an Excel cell reference back into digits
CellReference reference = new CellReference("AC4");
System.out.println(reference.getRow() + ", " + reference.getCol());

下面是一个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;
    }

抱歉,这是Python而不是c#,但至少结果是正确的:

def ColIdxToXlName(idx):
    if idx < 1:
        raise ValueError("Index is too small")
    result = ""
    while True:
        if idx > 26:
            idx, r = divmod(idx - 1, 26)
            result = chr(r + ord('A')) + result
        else:
            return chr(idx + ord('A') - 1) + result


for i in xrange(1, 1024):
    print "%4d : %s" % (i, ColIdxToXlName(i))

简单而简洁的JavaScript函数,将列号转换为电子表格列名。

function column(number) { const name = []; for(let n = number - 1; n >= 0; n = Math.floor(n / 26) - 1) { name.push(String.fromCharCode(65 + n % 26)); } return name.reverse().join(""); }; console.log(column(1), "A"); console.log(column(26), "Z"); console.log(column(27), "AA"); console.log(column(52), "AZ"); console.log(column(53), "BA"); console.log(column(702), "ZZ"); console.log(column(703), "AAA"); console.log(column(704), "AAB"); console.log(column(16384), "XFD");