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

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


当前回答

int nCol = 127;
string sChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
string sCol = "";
while (nCol >= 26)
{
    int nChar = nCol % 26;
    nCol = (nCol - nChar) / 26;
    // You could do some trick with using nChar as offset from 'A', but I am lazy to do it right now.
    sCol = sChars[nChar] + sCol;
}
sCol = sChars[nCol] + sCol;

更新:Peter的评论是正确的。这就是我在浏览器中编写代码的结果。:-)我的解决方案是不编译,它是最左边的字母,它是在反向顺序构建字符串-现在都固定了。

除了bug之外,该算法基本上是将一个数字从10进制转换为26进制。

更新2:Joel Coehoorn是对的-上面的代码将返回AB为27。如果它是一个以26为底的实数,AA就等于A, Z之后的下一个数字就是BA。

int nCol = 127;
string sChars = "0ABCDEFGHIJKLMNOPQRSTUVWXYZ";
string sCol = "";
while (nCol > 26)
{
    int nChar = nCol % 26;
    if (nChar == 0)
        nChar = 26;
    nCol = (nCol - nChar) / 26;
    sCol = sChars[nChar] + sCol;
}
if (nCol != 0)
    sCol = sChars[nCol] + sCol;

其他回答

JavaScript解决方案

/**
 * Calculate the column letter abbreviation from a 1 based index
 * @param {Number} value
 * @returns {string}
 */
getColumnFromIndex = function (value) {
    var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('');
    var remainder, result = "";
    do {
        remainder = value % 26;
        result = base[(remainder || 26) - 1] + result;
        value = Math.floor(value / 26);
    } while (value > 0);
    return result;
};

(我知道这个问题与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());

以下是Graham在Powershell中的代码:

function ConvertTo-ExcelColumnID {
param (
    [parameter(Position = 0,
        HelpMessage = "A 1-based index to convert to an excel column ID. e.g. 2 => 'B', 29 => 'AC'",
        Mandatory = $true)]
    [int]$index
);

[string]$result = '';
if ($index -le 0 ) {
    return $result;
}

while ($index -gt 0) {
    [int]$modulo = ($index - 1) % 26;
    $character = [char]($modulo + [int][char]'A');
    $result = $character + $result;
    [int]$index = ($index - $modulo) / 26;
}

return $result;

}

只是抛出一个简单的使用递归的两行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);
}

下面是一个基于零的列索引的更简单的解决方案

 public static string GetColumnIndexNumberToExcelColumn(int columnIndex)
        {
            int offset = columnIndex % 26;
            int multiple = columnIndex / 26;

            int initialSeed = 65;//Represents column "A"
            if (multiple == 0)
            {
                return Convert.ToChar(initialSeed + offset).ToString();
            }

            return $"{Convert.ToChar(initialSeed + multiple - 1)}{Convert.ToChar(initialSeed + offset)}";
        }