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

}

已经有30多个解决方案了，但这里是我的一行c#解决方案……

public string IntToExcelColumn(int i)
{
    return ((i<16926? "" : ((char)((((i/26)-1)%26)+65)).ToString()) + (i<2730? "" : ((char)((((i/26)-1)%26)+65)).ToString()) + (i<26? "" : ((char)((((i/26)-1)%26)+65)).ToString()) + ((char)((i%26)+65)));
}

如果有人需要在没有VBA的Excel中做到这一点，这里有一种方法:

=SUBSTITUTE(ADDRESS(1;colNum;4);"1";"")

其中colNum是列号

在VBA中:

Function GetColumnName(colNum As Integer) As String
    Dim d As Integer
    Dim m As Integer
    Dim name As String
    d = colNum
    name = ""
    Do While (d > 0)
        m = (d - 1) Mod 26
        name = Chr(65 + m) + name
        d = Int((d - m) / 26)
    Loop
    GetColumnName = name
End Function

private String getColumn(int c) {
    String s = "";
    do {
        s = (char)('A' + (c % 26)) + s;
        c /= 26;
    } while (c-- > 0);
    return s;
}

它不是以26为底，系统中没有0。如果有的话，'Z'后面应该是'BA'而不是'AA'。

谢谢你的回答!!帮助我想出了这些帮助函数，与我正在Elixir/Phoenix中工作的谷歌Sheets API进行一些交互

以下是我想到的(可能需要一些额外的验证和错误处理)

长生不老药:

def number_to_column(number) do
  cond do
    (number > 0 && number <= 26) ->
      to_string([(number + 64)])
    (number > 26) ->
      div_col = number_to_column(div(number - 1, 26))
      remainder = rem(number, 26)
      rem_col = cond do
        (remainder == 0) ->
          number_to_column(26)
        true ->
          number_to_column(remainder)
      end
      div_col <> rem_col
    true ->
      ""
  end
end

逆函数是:

def column_to_number(column) do
  column
    |> to_charlist
    |> Enum.reverse
    |> Enum.with_index
    |> Enum.reduce(0, fn({char, idx}, acc) ->
      ((char - 64) * :math.pow(26,idx)) + acc
    end)
    |> round
end

还有一些测试:

describe "test excel functions" do
  @excelTestData [{"A", 1}, {"Z",26}, {"AA", 27}, {"AB", 28}, {"AZ", 52},{"BA", 53}, {"AAA", 703}]

  test "column to number" do
    Enum.each(@excelTestData, fn({input, expected_result}) ->
      actual_result = BulkOnboardingController.column_to_number(input)
      assert actual_result == expected_result
    end)
  end

  test "number to column" do
    Enum.each(@excelTestData, fn({expected_result, input}) ->
      actual_result = BulkOnboardingController.number_to_column(input)
      assert actual_result == expected_result
    end)
  end
end

您可能需要两种方式转换，例如从Excel列地址(如AAZ)到整数，以及从任何整数到Excel。下面的两个方法就可以做到这一点。假设基于1的索引，“数组”中的第一个元素是元素1。 这里没有大小限制，所以你可以使用ERROR这样的地址，这将是列号2613824…

public static string ColumnAdress(int col)
{
  if (col <= 26) { 
    return Convert.ToChar(col + 64).ToString();
  }
  int div = col / 26;
  int mod = col % 26;
  if (mod == 0) {mod = 26;div--;}
  return ColumnAdress(div) + ColumnAdress(mod);
}

public static int ColumnNumber(string colAdress)
{
  int[] digits = new int[colAdress.Length];
  for (int i = 0; i < colAdress.Length; ++i)
  {
    digits[i] = Convert.ToInt32(colAdress[i]) - 64;
  }
  int mul=1;int res=0;
  for (int pos = digits.Length - 1; pos >= 0; --pos)
  {
    res += digits[pos] * mul;
    mul *= 26;
  }
  return res;
}