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

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


当前回答

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'。

其他回答

似乎很多答案都比必要的要复杂得多。下面是一个基于上面描述的递归的通用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

我在我的第一篇文章中发现了一个错误,所以我决定坐下来算算。我发现用来识别Excel列的数字系统不是另一个人说的26进制系统。以10为基数考虑以下情况。你也可以用字母表中的字母来做这件事。

空间 :.........................S1, s2, s3: S1, s2, s3 ....................................0,00, 000:..A aa aaa ....................................1,01, 001:..B ab aab ....................................…,…,…:……,…,… ....................................9,99,999:..Z, zz, ZZZ 空间中的总状态:10,100,1000:26,676,17576 国家总 :............... 1110年 ................ 18278年

Excel在以26为基数的字母空格中对列进行编号。你可以看到,一般来说,状态空间的级数是a, a^2, a^3,…对于以a为底的情况,状态的总数是a + a^2 + a^3 + ... .

Suppose you want to find the total number of states A in the first N spaces. The formula for doing so is A = (a)(a^N - 1 )/(a-1). This is important because we need to find the space N that corresponds to our index K. If I want to find out where K lies in the number system I need to replace A with K and solve for N. The solution is N = log{base a} (A (a-1)/a +1). If I use the example of a = 10 and K = 192, I know that N = 2.23804… . This tells me that K lies at the beginning of the third space since it is a little greater than two.

The next step is to find exactly how far in the current space we are. To find this, subtract from K the A generated using the floor of N. In this example, the floor of N is two. So, A = (10)(10^2 – 1)/(10-1) = 110, as is expected when you combine the states of the first two spaces. This needs to be subtracted from K because these first 110 states would have already been accounted for in the first two spaces. This leaves us with 82 states. So, in this number system, the representation of 192 in base 10 is 082.

使用基本索引为0的c#代码是

    private string ExcelColumnIndexToName(int Index)
    {
        string range = string.Empty;
        if (Index < 0 ) return range;
        int a = 26;
        int x = (int)Math.Floor(Math.Log((Index) * (a - 1) / a + 1, a));
        Index -= (int)(Math.Pow(a, x) - 1) * a / (a - 1);
        for (int i = x+1; Index + i > 0; i--)
        {
            range = ((char)(65 + Index % a)).ToString() + range;
            Index /= a;
        }
        return range;
    }

/ /旧邮政

c#中的零基础解决方案。

    private string ExcelColumnIndexToName(int Index)
    {
        string range = "";
        if (Index < 0 ) return range;
        for(int i=1;Index + i > 0;i=0)
        {
            range = ((char)(65 + Index % 26)).ToString() + range;
            Index /= 26;
        }
        if (range.Length > 1) range = ((char)((int)range[0] - 1)).ToString() + range.Substring(1);
        return range;
    }

这是所有其他人以及谷歌重定向到的问题,所以我在这里发布这个。

这些答案中有许多是正确的,但对于简单的情况来说太麻烦了,比如当您的列不超过26个时。如果你怀疑你是否会进入双字符列,那么忽略这个答案,但如果你确定你不会,那么你可以在c#中简单地这样做:

public static char ColIndexToLetter(short index)
{
    if (index < 0 || index > 25) throw new ArgumentException("Index must be between 0 and 25.");
    return (char)('A' + index);
}

见鬼,如果你对你传递的东西有信心,你甚至可以删除验证并使用内联:

(char)('A' + index)

这在许多语言中是非常相似的,因此您可以根据需要进行调整。

同样,只有在100%确定不超过26列的情况下才使用这种方法。

我在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

如果你想以实用的方式引用单元格,那么如果你使用工作表的Cells方法,你会得到更可读的代码。它接受行和列索引,而不是传统的单元格引用。它与Offset方法非常相似。