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

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


当前回答

虽然已经有了一堆有效的答案,但没有一个能深入到它背后的理论。

Excel列名是其数字的以26为基数的双射表示。这与普通的26进制有很大的不同(没有前导零),我真的建议阅读维基百科的条目来了解区别。例如,十进制值702(分解为26*26 + 26)以“普通”底数26 × 110表示(即1x26^2 + 1x26^1 + 0x26^0),以双射底数26 × ZZ表示(即26x26^1 + 26x26^0)。

除了区别之外,双射计数是一种位置符号,因此我们可以使用迭代(或递归)算法来执行转换,该算法在每次迭代中查找下一个位置的数字(类似于普通的基数转换算法)。

获得十进制数m的双射base-k表示的最后一个位置(索引为0的位置)的数字的一般公式是(f是天花板函数- 1):

m - (f(m / k) * k)

下一个位置的数字(即下标为1的数字)可以通过对f(m / k)的结果应用相同的公式来求得。我们知道,对于最后一位数字(即下标最高的数字),f(m / k)为0。

这构成了迭代的基础,该迭代查找十进制数的双射进制k中的每个连续数字。在伪代码中,它看起来像这样(digit()将一个十进制整数映射到它在双射进制中的表示——例如,digit(1)将在双射进制26中返回a):

fun conv(m)
    q = f(m / k)
    a = m - (q * k)
    if (q == 0)
        return digit(a)
    else
        return conv(q) + digit(a);

因此,我们可以将其转换为c# 2,以获得一个通用的“conversion to bijective base-k”ToBijective()例程:

class BijectiveNumeration {
    private int baseK;
    private Func<int, char> getDigit;
    public BijectiveNumeration(int baseK, Func<int, char> getDigit) {
        this.baseK = baseK;
        this.getDigit = getDigit;
    }

    public string ToBijective(double decimalValue) {
        double q = f(decimalValue / baseK);
        double a = decimalValue - (q * baseK);
        return ((q > 0) ? ToBijective(q) : "") + getDigit((int)a);
    }

    private static double f(double i) {
        return (Math.Ceiling(i) - 1);
    }
}

现在转换为双射base-26(我们的“Excel列名”用例):

static void Main(string[] args)
{
    BijectiveNumeration bijBase26 = new BijectiveNumeration(
        26,
        (value) => Convert.ToChar('A' + (value - 1))
    );

    Console.WriteLine(bijBase26.ToBijective(1));     // prints "A"
    Console.WriteLine(bijBase26.ToBijective(26));    // prints "Z"
    Console.WriteLine(bijBase26.ToBijective(27));    // prints "AA"
    Console.WriteLine(bijBase26.ToBijective(702));   // prints "ZZ"
    Console.WriteLine(bijBase26.ToBijective(16384)); // prints "XFD"
}

Excel的最大列索引是16384 / XFD,但是这段代码可以转换任何正数。

作为一个额外的奖励,我们现在可以很容易地转换为任何双射基。例如,以10为基数的双射:

static void Main(string[] args)
{
    BijectiveNumeration bijBase10 = new BijectiveNumeration(
        10,
        (value) => value < 10 ? Convert.ToChar('0'+value) : 'A'
    );

    Console.WriteLine(bijBase10.ToBijective(1));     // prints "1"
    Console.WriteLine(bijBase10.ToBijective(10));    // prints "A"
    Console.WriteLine(bijBase10.ToBijective(123));   // prints "123"
    Console.WriteLine(bijBase10.ToBijective(20));    // prints "1A"
    Console.WriteLine(bijBase10.ToBijective(100));   // prints "9A"
    Console.WriteLine(bijBase10.ToBijective(101));   // prints "A1"
    Console.WriteLine(bijBase10.ToBijective(2010));  // prints "19AA"
}

1这个一般的答案最终可以简化为其他正确的具体答案,但我发现,如果没有双射数背后的形式理论,很难完全掌握解决方案的逻辑。这也很好地证明了它的正确性。此外,还有几个类似的问题与此相关,有些与语言无关,有些则更通用。这就是为什么我认为这个答案的增加是有必要的,这个问题是一个很好的地方。

2 c#免责声明:我在c#中实现了一个例子,因为这是这里要求的,但我从未学习或使用过这种语言。我已经验证了它的编译和运行,但如果有必要,请调整它以适应语言的最佳实践/一般惯例。

这个例子只是为了正确和容易理解;它可以也应该优化性能(例如使用尾递归——但这似乎需要在c#中进行蹦蹦跳),并变得更安全(例如通过验证参数)。

其他回答

似乎很多答案都比必要的要复杂得多。下面是一个基于上面描述的递归的通用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
int nCol = 127;
string sChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
string sCol = "";
while (nCol >= 26)
{
    int nChar = nCol % 26;
    nCol = (nCol - nChar) / 26;
    // You could do some trick with using nChar as offset from 'A', but I am lazy to do it right now.
    sCol = sChars[nChar] + sCol;
}
sCol = sChars[nCol] + sCol;

更新:Peter的评论是正确的。这就是我在浏览器中编写代码的结果。:-)我的解决方案是不编译,它是最左边的字母,它是在反向顺序构建字符串-现在都固定了。

除了bug之外,该算法基本上是将一个数字从10进制转换为26进制。

更新2:Joel Coehoorn是对的-上面的代码将返回AB为27。如果它是一个以26为底的实数,AA就等于A, Z之后的下一个数字就是BA。

int nCol = 127;
string sChars = "0ABCDEFGHIJKLMNOPQRSTUVWXYZ";
string sCol = "";
while (nCol > 26)
{
    int nChar = nCol % 26;
    if (nChar == 0)
        nChar = 26;
    nCol = (nCol - nChar) / 26;
    sCol = sChars[nChar] + sCol;
}
if (nCol != 0)
    sCol = sChars[nCol] + sCol;

(我知道这个问题与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());
 static string[] ExcelColumnAlphabetIdentifiers = new string[] { "", "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" };
 public static string ExcelColumnAlphabetIdentifier( int ColumnNumber)
    {
        StringBuilder sb = new StringBuilder();
        int remainder = ColumnNumber;
        do
        {
            sb.Append(ExcelColumnAlphabetIdentifiers[remainder % 26]);
            remainder = remainder / 26;
        }
        while (remainder > 0);
       return sb.ToString();
    }

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;