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

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


当前回答

Objective-C实现:

-(NSString*)getColumnName:(int)n {
     NSString *name = @"";
     while (n>0) {
     n--;
     char c = (char)('A' + n%26);
     name = [NSString stringWithFormat:@"%c%@",c,name];
     n = n/26;
  }    
     return name;

}

迅速实现:

func getColumnName(n:Int)->String{
 var columnName = ""
 var index = n
 while index>0 {
     index--
     let char = Character(UnicodeScalar(65 + index%26))
     columnName = "\(char)\(columnName)"
     index = index / 26
 }
 return columnName

}

答案是基于:https://stackoverflow.com/a/4532562/2231118

其他回答

f#版本的各种方式

let rec getExcelColumnName x  = if x<26 then int 'A'+x|>char|>string else (x/26-1|>c)+ c(x%26)

对不起,最小化,正在开发一个更好的https://stackoverflow.com/a/4500043/57883版本

相反的方向:

// return values start at 0
let getIndexFromExcelColumnName (x:string) =
    let a = int 'A'
    let fPow len i =
        Math.Pow(26., len - 1 - i |> float)
        |> int

    let getValue len i c = 
        int c - a + 1 * fPow len i
    let f i = getValue x.Length i x.[i]
    [0 .. x.Length - 1]
    |> Seq.map f
    |> Seq.sum
    |> fun x -> x - 1

您可能需要两种方式转换,例如从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;
}
    public string ToBase26(int number)
    {
        if (number < 0) return String.Empty;

        int remainder = number % 26;
        int value = number / 26;

        return value == 0 ?
            String.Format("{0}", Convert.ToChar(65 + remainder)) :
            String.Format("{0}{1}", ToBase26(value - 1), Convert.ToChar(65 + remainder));
    }

这些我的代码转换特定的数字(索引从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));
    }

看到了另一个VBA答案-这可以在excel-vba中用1行UDF完成:

Function GetColLetter(ByVal colID As Integer) As String
    If colID > Columns.Count Then
        Err.Raise 9, , "Column index out of bounds"
    Else
        GetColLetter = Split(Cells(1, colID).Address, "$")(1)
    End If
End Function