T-sql (sql server 18)

第一页的解决方案副本

CREATE FUNCTION dbo.getExcelColumnNameByOrdinal(@RowNum int)  
RETURNS varchar(5)   
AS   
BEGIN  
    DECLARE @dividend int = @RowNum;
    DECLARE @columnName varchar(max) = '';
    DECLARE @modulo int;

    WHILE (@dividend > 0)
    BEGIN  
        SELECT @modulo = ((@dividend - 1) % 26);
        SELECT @columnName = CHAR((65 + @modulo)) + @columnName;
        SELECT @dividend = CAST(((@dividend - @modulo) / 26) as int);
    END
    RETURN 
       @columnName;

END;

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

打印稿

function lengthToExcelColumn(len: number): string {

    let dividend: number = len;
    let columnName: string = '';
    let modulo: number = 0;

    while (dividend > 0) {
        modulo = (dividend - 1) % 26;
        columnName = String.fromCharCode(65 + modulo).toString() + columnName;
        dividend = Math.floor((dividend - 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());

这是我在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;
}

我想在我使用的静态类中加入，用于在col index和col Label之间进行交互。我对ColumnLabel方法使用了修改后的可接受答案

public static class Extensions
{
    public static string ColumnLabel(this int col)
    {
        var dividend = col;
        var columnLabel = string.Empty;
        int modulo;

        while (dividend > 0)
        {
            modulo = (dividend - 1) % 26;
            columnLabel = Convert.ToChar(65 + modulo).ToString() + columnLabel;
            dividend = (int)((dividend - modulo) / 26);
        } 

        return columnLabel;
    }
    public static int ColumnIndex(this string colLabel)
    {
        // "AD" (1 * 26^1) + (4 * 26^0) ...
        var colIndex = 0;
        for(int ind = 0, pow = colLabel.Count()-1; ind < colLabel.Count(); ++ind, --pow)
        {
            var cVal = Convert.ToInt32(colLabel[ind]) - 64; //col A is index 1
            colIndex += cVal * ((int)Math.Pow(26, pow));
        }
        return colIndex;
    }
}

用这个…

30.ColumnLabel(); // "AD"
"AD".ColumnIndex(); // 30