我需要帮助编写一个逻辑,将数字值转换为相应的MS Excel表头值。
例如:
1 = "A" 2 = "B" 3 = "C" 4 = "D" 5 = "E" ......... 25 = "Y" 26 = "Z" 27 = "AA" 28 = "AB" 29 = "AC" 30 = "AD" .........
希望能提供一些相关的.NET代码(C#或VB)。谢谢。
我需要帮助编写一个逻辑,将数字值转换为相应的MS Excel表头值。
例如:
1 = "A" 2 = "B" 3 = "C" 4 = "D" 5 = "E" ......... 25 = "Y" 26 = "Z" 27 = "AA" 28 = "AB" 29 = "AC" 30 = "AD" .........
希望能提供一些相关的.NET代码(C#或VB)。谢谢。
这里是我在Excel中编写的一些VBA代码(带有测试代码),可以解决问题。除非VB.NET发生了巨大变化,否则它应该能够正常工作。即使有所改变,您也应该能够将思路转化为可行的代码。
' num2col - translate Excel column number (1-n) into column string ("A"-"ZZ"). '
Function num2col(num As Integer) As String
' Subtract one to make modulo/divide cleaner. '
num = num - 1
' Select return value based on invalid/one-char/two-char input. '
If num < 0 Or num >= 27 * 26 Then
' Return special sentinel value if out of range. '
num2col = "-"
Else
' Single char, just get the letter. '
If num < 26 Then
num2col = Chr(num + 65)
Else
' Double char, get letters based on integer divide and modulus. '
num2col = Chr(num \ 26 + 64) + Chr(num Mod 26 + 65)
End If
End If
End Function
' Test code in Excel VBA. '
Sub main()
MsgBox ("- should be " & num2col(0))
MsgBox ("A should be " & num2col(1))
MsgBox ("B should be " & num2col(2))
MsgBox ("Z should be " & num2col(26))
MsgBox ("AA should be " & num2col(27))
MsgBox ("AB should be " & num2col(28))
MsgBox ("AY should be " & num2col(51))
MsgBox ("AZ should be " & num2col(52))
MsgBox ("BA should be " & num2col(53))
MsgBox ("ZY should be " & num2col(27 * 26 - 1))
MsgBox ("ZZ should be " & num2col(27 * 26))
MsgBox ("- should be " & num2col(27 * 26 + 1))
End Sub
public string ColumnNumberToLetter(int ColumnNumber)
{
if (ColumnNumber > 26)
{
return ((char) (Math.Floor(((double)ColumnNumber - 1) / 26) + 64)).ToString()
+ ((char) (((ColumnNumber - 1) % 26) + 65)).ToString();
}
return ((char)(ColumnNumber+64)).ToString();
}
试试这个:
public static string ToExcelString(int number)
{
if (number > 25)
{
int secondaryCounter = 0;
while (number > 25)
{
secondaryCounter = secondaryCounter + 1;
number = number - 25;
}
return ToExcelChar(number) + ToExcelChar(secondaryCounter);
}
else
{
return ToExcelChar(number)
}
}
private const string alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
private static string ToExcelChar(int number)
{
if (number > 25 || number < 0)
{
throw new InvalidArgumentException("the number passed in (" + number + ") must be between the range 0-25");
}
return alphabet[number];
}
只需使用activecell.address,然后根据$的位置来操纵字符串以得到所需结果。
0
。不能计算 8、9、11、12、……19、21、22……
。记住 A
代表 1
。将 Z 加 1 应该得到 A0(这个系统中没有),而不是 AA。 - user289086