我想在我使用的静态类中加入，用于在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

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

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

以下是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;

}

这些我的代码转换特定的数字(索引从1开始)到Excel列。

    public static string NumberToExcelColumn(uint number)
    {
        uint originalNumber = number;

        uint numChars = 1;
        while (Math.Pow(26, numChars) < number)
        {
            numChars++;

            if (Math.Pow(26, numChars) + 26 >= number)
            {
                break;
            }               
        }

        string toRet = "";
        uint lastValue = 0;

        do
        {
            number -= lastValue;

            double powerVal = Math.Pow(26, numChars - 1);
            byte thisCharIdx = (byte)Math.Truncate((columnNumber - 1) / powerVal);
            lastValue = (int)powerVal * thisCharIdx;

            if (numChars - 2 >= 0)
            {
                double powerVal_next = Math.Pow(26, numChars - 2);
                byte thisCharIdx_next = (byte)Math.Truncate((columnNumber - lastValue - 1) / powerVal_next);
                int lastValue_next = (int)Math.Pow(26, numChars - 2) * thisCharIdx_next;

                if (thisCharIdx_next == 0 && lastValue_next == 0 && powerVal_next == 26)
                {
                    thisCharIdx--;
                    lastValue = (int)powerVal * thisCharIdx;
                }
            }

            toRet += (char)((byte)'A' + thisCharIdx + ((numChars > 1) ? -1 : 0));

            numChars--;
        } while (numChars > 0);

        return toRet;
    }

我的单元测试:

    [TestMethod]
    public void Test()
    {
        Assert.AreEqual("A", NumberToExcelColumn(1));
        Assert.AreEqual("Z", NumberToExcelColumn(26));
        Assert.AreEqual("AA", NumberToExcelColumn(27));
        Assert.AreEqual("AO", NumberToExcelColumn(41));
        Assert.AreEqual("AZ", NumberToExcelColumn(52));
        Assert.AreEqual("BA", NumberToExcelColumn(53));
        Assert.AreEqual("ZZ", NumberToExcelColumn(702));
        Assert.AreEqual("AAA", NumberToExcelColumn(703));
        Assert.AreEqual("ABC", NumberToExcelColumn(731));
        Assert.AreEqual("ACQ", NumberToExcelColumn(771));
        Assert.AreEqual("AYZ", NumberToExcelColumn(1352));
        Assert.AreEqual("AZA", NumberToExcelColumn(1353));
        Assert.AreEqual("AZB", NumberToExcelColumn(1354));
        Assert.AreEqual("BAA", NumberToExcelColumn(1379));
        Assert.AreEqual("CNU", NumberToExcelColumn(2413));
        Assert.AreEqual("GCM", NumberToExcelColumn(4823));
        Assert.AreEqual("MSR", NumberToExcelColumn(9300));
        Assert.AreEqual("OMB", NumberToExcelColumn(10480));
        Assert.AreEqual("ULV", NumberToExcelColumn(14530));
        Assert.AreEqual("XFD", NumberToExcelColumn(16384));
    }

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

这是我的解，在常数时间内运行(没有循环)。此解决方案适用于所有可能的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);
    }
}