如何在c#中将数值转换为Excel列名，而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384，这是它支持的列数。结果值应以excel列名的形式出现，例如A、AA、AAA等。
当前回答
我的解决方案基于Graham, Herman Kan和desseim的回答，使用StringBuilder:
internal class Program
{
#region get_excel_col_name
/// <summary>
/// Returns the name of the column by its number
/// </summary>
/// <param name="col_num">Column number</param>
/// <returns>Column name</returns>
/// <remarks>Numbering columns from zero</remarks>
private static string get_excel_col_name(int col_num)
{
StringBuilder sb = new StringBuilder(2);
if (col_num >= 0)
{
do
{
sb.Insert(0, (char)(col_num % 26 + 65));
col_num /= 26;
}
while (--col_num >= 0);
}
return sb.ToString();
}
#endregion
private static void Main(string[] args)
{
Console.WriteLine(get_excel_col_name(34));//outputs AI
Console.ReadKey(true);
}
}
其他回答
我是这样做的:
private string GetExcelColumnName(int columnNumber)
{
string columnName = "";
while (columnNumber > 0)
{
int modulo = (columnNumber - 1) % 26;
columnName = Convert.ToChar('A' + modulo) + columnName;
columnNumber = (columnNumber - modulo) / 26;
}
return columnName;
}
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));
}
我的解决方案基于Graham, Herman Kan和desseim的回答，使用StringBuilder:
internal class Program
{
#region get_excel_col_name
/// <summary>
/// Returns the name of the column by its number
/// </summary>
/// <param name="col_num">Column number</param>
/// <returns>Column name</returns>
/// <remarks>Numbering columns from zero</remarks>
private static string get_excel_col_name(int col_num)
{
StringBuilder sb = new StringBuilder(2);
if (col_num >= 0)
{
do
{
sb.Insert(0, (char)(col_num % 26 + 65));
col_num /= 26;
}
while (--col_num >= 0);
}
return sb.ToString();
}
#endregion
private static void Main(string[] args)
{
Console.WriteLine(get_excel_col_name(34));//outputs AI
Console.ReadKey(true);
}
}
这个片段适用于A到ZZ列名
string columnName = columnNumber > 26 ? Convert.ToChar(64 + (columnNumber / 26)).ToString() + Convert.ToChar(64 + (columnNumber % 26)) : Convert.ToChar(64 + columnNumber).ToString();
抱歉，这是Python而不是c#，但至少结果是正确的:
def excel_column_number_to_name(column_number):
output = ""
index = column_number-1
while index >= 0:
character = chr((index%26)+ord('A'))
output = output + character
index = index/26 - 1
return output[::-1]
for i in xrange(1, 1024):
print "%4d : %s" % (i, excel_column_number_to_name(i))
通过这些测试用例:
列号:494286 => ABCDZ 列号:27 => 列号:52 => AZ
