C# 如何将列号（例如 127）转换为 Excel 列（例如 AA）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/181596/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert a column number (e.g. 127) into an Excel column (e.g. AA)
提问by robertkroll
How do you convert a numerical number to an Excel column name in C# without using automation getting the value directly from Excel.
如何在 C# 中将数字转换为 Excel 列名，而不使用直接从 Excel 获取值的自动化。
Excel 2007 has a possible range of 1 to 16384, which is the number of columns that it supports. The resulting values should be in the form of excel column names, e.g. A, AA, AAA etc.
Excel 2007 的可能范围是 1 到 16384，这是它支持的列数。结果值应采用 excel 列名称的形式，例如 A、AA、AAA 等。
采纳答案by Graham
Here's how I do it:
这是我的方法：
private string GetExcelColumnName(int columnNumber)
{
int dividend = columnNumber;
string columnName = String.Empty;
int modulo;
while (dividend > 0)
{
modulo = (dividend - 1) % 26;
columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
dividend = (int)((dividend - modulo) / 26);
}
return columnName;
}
回答by Franci Penov
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;
Update: Peter's comment is right. That's what I get for writing code in the browser. :-) My solution was not compiling, it was missing the left-most letter and it was building the string in reverse order - all now fixed.
更新：彼得的评论是正确的。这就是我在浏览器中编写代码的结果。:-) 我的解决方案没有编译，它缺少最左边的字母，它以相反的顺序构建字符串 - 现在都已修复。
Bugs aside, the algorithm is basically converting a number from base 10 to base 26.
撇开错误不谈，该算法基本上是将数字从基数 10 转换为基数 26。
Update 2: Joel Coehoornis right - the code above will return AB for 27. If it was real base 26 number, AA would be equal to A and the next number after Z would be BA.
更新 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;
回答by Peter
Easy with recursion.
简单的递归。
public static string GetStandardExcelColumnName(int columnNumberOneBased)
{
int baseValue = Convert.ToInt32('A');
int columnNumberZeroBased = columnNumberOneBased - 1;
string ret = "";
if (columnNumberOneBased > 26)
{
ret = GetStandardExcelColumnName(columnNumberZeroBased / 26) ;
}
return ret + Convert.ToChar(baseValue + (columnNumberZeroBased % 26) );
}
回答by RoMa
Sorry, this is Python instead of C#, but at least the results are correct:
抱歉，这是 Python 而不是 C#，但至少结果是正确的：
def ColIdxToXlName(idx):
if idx < 1:
raise ValueError("Index is too small")
result = ""
while True:
if idx > 26:
idx, r = divmod(idx - 1, 26)
result = chr(r + ord('A')) + result
else:
return chr(idx + ord('A') - 1) + result
for i in xrange(1, 1024):
print "%4d : %s" % (i, ColIdxToXlName(i))
回答by Graham
private String getColumn(int c) {
String s = "";
do {
s = (char)('A' + (c % 26)) + s;
c /= 26;
} while (c-- > 0);
return s;
}
Its not exactly base 26, there is no 0 in the system. If there was, 'Z' would be followed by 'BA' not by 'AA'.
它不完全以 26 为基数，系统中没有 0。如果有，'Z' 后面将是 'BA' 而不是 'AA'。
回答by Graham
I'm using this one in VB.NET 2003 and it works well...
我在 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
回答by pipTheGeek
If you are wanting to reference the cell progmatically then you will get much more readable code if you use the Cells method of a sheet. It takes a row and column index instead of a traditonal cell reference. It is very similar to the Offset method.
如果您想以编程方式引用单元格，那么如果您使用工作表的 Cells 方法，您将获得更具可读性的代码。它采用行和列索引而不是传统的单元格引用。它与 Offset 方法非常相似。
回答by vzczc
If anyone needs to do this in Excel without VBA, here is a way:
如果有人需要在没有 VBA 的 Excel 中执行此操作，这是一种方法：
=SUBSTITUTE(ADDRESS(1;colNum;4);"1";"")
where colNum is the column number
其中 colNum 是列号
And in VBA:
在 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
回答by vzczc
Using this in VB.Net 2005 :
在 VB.Net 2005 中使用它：
Private Function ColumnName(ByVal ColumnIndex As Integer) As String
Dim Name As String = ""
Name = (New Microsoft.Office.Interop.Owc11.Spreadsheet).Columns.Item(ColumnIndex).Address(False, False, Microsoft.Office.Interop.Owc11.XlReferenceStyle.xlA1)
Name = Split(Name, ":")(0)
Return Name
End Function
回答by ShloEmi
Refining the original solution (in C#):
改进原始解决方案（在 C# 中）：
public static class ExcelHelper
{
private static Dictionary<UInt16, String> l_DictionaryOfColumns;
public static ExcelHelper() {
l_DictionaryOfColumns = new Dictionary<ushort, string>(256);
}
public static String GetExcelColumnName(UInt16 l_Column)
{
UInt16 l_ColumnCopy = l_Column;
String l_Chars = "0ABCDEFGHIJKLMNOPQRSTUVWXYZ";
String l_rVal = "";
UInt16 l_Char;
if (l_DictionaryOfColumns.ContainsKey(l_Column) == true)
{
l_rVal = l_DictionaryOfColumns[l_Column];
}
else
{
while (l_ColumnCopy > 26)
{
l_Char = l_ColumnCopy % 26;
if (l_Char == 0)
l_Char = 26;
l_ColumnCopy = (l_ColumnCopy - l_Char) / 26;
l_rVal = l_Chars[l_Char] + l_rVal;
}
if (l_ColumnCopy != 0)
l_rVal = l_Chars[l_ColumnCopy] + l_rVal;
l_DictionaryOfColumns.ContainsKey(l_Column) = l_rVal;
}
return l_rVal;
}
}
