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;

这是一个javascript版本，根据格雷厄姆的代码

function (columnNumber) {
    var dividend = columnNumber;
    var columnName = "";
    var modulo;

    while (dividend > 0) {
        modulo = (dividend - 1) % 26;
        columnName = String.fromCharCode(65 + modulo) + columnName;
        dividend = parseInt((dividend - modulo) / 26);
    }

    return columnName;
};

似乎很多答案都比必要的要复杂得多。下面是一个基于上面描述的递归的通用Ruby答案:

这个答案的一个好处是，它不局限于26个英文字母。你可以在COLUMNS常量中定义任何你喜欢的范围，它会做正确的事情。

  # vim: ft=ruby
  class Numeric
    COLUMNS = ('A'..'Z').to_a

    def to_excel_column(n = self)
      n < 1 ?  '' : begin
        base = COLUMNS.size
        to_excel_column((n - 1) / base) + COLUMNS[(n - 1) % base]
      end
    end
  end

  # verify:
  (1..52).each { |i| printf "%4d => %4s\n", i, i.to_excel_column }

这将打印以下内容，例如:

   1 =>    A
   2 =>    B
   3 =>    C
  ....
  33 =>   AG
  34 =>   AH
  35 =>   AI
  36 =>   AJ
  37 =>   AK
  38 =>   AL
  39 =>   AM
  40 =>   AN
  41 =>   AO
  42 =>   AP
  43 =>   AQ
  44 =>   AR
  45 =>   AS
  46 =>   AT
  47 =>   AU
  48 =>   AV
  49 =>   AW
  50 =>   AX
  51 =>   AY
  52 =>   AZ

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

到目前为止，所有的解决方案都包含迭代或递归，这让我感到惊讶。

这是我的解，在常数时间内运行(没有循环)。此解决方案适用于所有可能的Excel列，并检查输入是否可以转换为Excel列。可能的列在[A, XFD]或[1,16384]范围内。(这取决于你的Excel版本)

private static string Turn(uint col)
{
    if (col < 1 || col > 16384) //Excel columns are one-based (one = 'A')
        throw new ArgumentException("col must be >= 1 and <= 16384");

    if (col <= 26) //one character
        return ((char)(col + 'A' - 1)).ToString();

    else if (col <= 702) //two characters
    {
        char firstChar = (char)((int)((col - 1) / 26) + 'A' - 1);
        char secondChar = (char)(col % 26 + 'A' - 1);

        if (secondChar == '@') //Excel is one-based, but modulo operations are zero-based
            secondChar = 'Z'; //convert one-based to zero-based

        return string.Format("{0}{1}", firstChar, secondChar);
    }

    else //three characters
    {
        char firstChar = (char)((int)((col - 1) / 702) + 'A' - 1);
        char secondChar = (char)((col - 1) / 26 % 26 + 'A' - 1);
        char thirdChar = (char)(col % 26 + 'A' - 1);

        if (thirdChar == '@') //Excel is one-based, but modulo operations are zero-based
            thirdChar = 'Z'; //convert one-based to zero-based

        return string.Format("{0}{1}{2}", firstChar, secondChar, thirdChar);
    }
}

我在VB中使用这个。NET 2003和它的工作良好…

Private Function GetExcelColumnName(ByVal aiColNumber As Integer) As String
    Dim BaseValue As Integer = Convert.ToInt32(("A").Chars(0)) - 1
    Dim lsReturn As String = String.Empty

    If (aiColNumber > 26) Then
        lsReturn = GetExcelColumnName(Convert.ToInt32((Format(aiColNumber / 26, "0.0").Split("."))(0)))
    End If

    GetExcelColumnName = lsReturn + Convert.ToChar(BaseValue + (aiColNumber Mod 26))
End Function