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

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


当前回答

public static string ConvertToAlphaColumnReferenceFromInteger(int columnReference)
    {
        int baseValue = ((int)('A')) - 1 ;
        string lsReturn = String.Empty; 

        if (columnReference > 26) 
        {
            lsReturn = ConvertToAlphaColumnReferenceFromInteger(Convert.ToInt32(Convert.ToDouble(columnReference / 26).ToString().Split('.')[0]));
        } 

        return lsReturn + Convert.ToChar(baseValue + (columnReference % 26));            
    }

其他回答

另一个解决方案:

private void Foo()
{
   l_ExcelApp = new Excel.ApplicationClass();
   l_ExcelApp.ReferenceStyle = Excel.XlReferenceStyle.xlR1C1;
   // ... now reference by R[row]C[column], Ex. A1 <==> R1C1, C6 <==> R3C6, ...
}

在这里查看更多- Excel中的单元格引用!作者:Nitin Paranjape博士

看到了另一个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

如果有人需要在没有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

我在我的第一篇文章中发现了一个错误,所以我决定坐下来算算。我发现用来识别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;
    }

..并转换为php:

function GetExcelColumnName($columnNumber) {
    $columnName = '';
    while ($columnNumber > 0) {
        $modulo = ($columnNumber - 1) % 26;
        $columnName = chr(65 + $modulo) . $columnName;
        $columnNumber = (int)(($columnNumber - $modulo) / 26);
    }
    return $columnName;
}