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

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


当前回答

精炼原始的解决方案(在c#中):

public static class ExcelHelper
{
    private static Dictionary<UInt16, String> l_DictionaryOfColumns;

    public static ExcelHelper() {
        l_DictionaryOfColumns = new Dictionary<ushort, string>(256);
    }

    public static String GetExcelColumnName(UInt16 l_Column)
    {
        UInt16 l_ColumnCopy = l_Column;
        String l_Chars = "0ABCDEFGHIJKLMNOPQRSTUVWXYZ";
        String l_rVal = "";
        UInt16 l_Char;


        if (l_DictionaryOfColumns.ContainsKey(l_Column) == true)
        {
            l_rVal = l_DictionaryOfColumns[l_Column];
        }
        else
        {
            while (l_ColumnCopy > 26)
            {
                l_Char = l_ColumnCopy % 26;
                if (l_Char == 0)
                    l_Char = 26;

                l_ColumnCopy = (l_ColumnCopy - l_Char) / 26;
                l_rVal = l_Chars[l_Char] + l_rVal;
            }
            if (l_ColumnCopy != 0)
                l_rVal = l_Chars[l_ColumnCopy] + l_rVal;

            l_DictionaryOfColumns.ContainsKey(l_Column) = l_rVal;
        }

        return l_rVal;
    }
}

其他回答

抱歉,这是Python而不是c#,但至少结果是正确的:

def ColIdxToXlName(idx):
    if idx < 1:
        raise ValueError("Index is too small")
    result = ""
    while True:
        if idx > 26:
            idx, r = divmod(idx - 1, 26)
            result = chr(r + ord('A')) + result
        else:
            return chr(idx + ord('A') - 1) + result


for i in xrange(1, 1024):
    print "%4d : %s" % (i, ColIdxToXlName(i))

巧合和优雅的Ruby版本:

def col_name(col_idx)
    name = ""
    while col_idx>0
        mod     = (col_idx-1)%26
        name    = (65+mod).chr + name
        col_idx = ((col_idx-mod)/26).to_i
    end
    name
end

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

谢谢你的回答!!帮助我想出了这些帮助函数,与我正在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

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