如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
当前回答
如果你想以实用的方式引用单元格,那么如果你使用工作表的Cells方法,你会得到更可读的代码。它接受行和列索引,而不是传统的单元格引用。它与Offset方法非常相似。
其他回答
下面是一个基于零的列索引的更简单的解决方案
public static string GetColumnIndexNumberToExcelColumn(int columnIndex)
{
int offset = columnIndex % 26;
int multiple = columnIndex / 26;
int initialSeed = 65;//Represents column "A"
if (multiple == 0)
{
return Convert.ToChar(initialSeed + offset).ToString();
}
return $"{Convert.ToChar(initialSeed + multiple - 1)}{Convert.ToChar(initialSeed + offset)}";
}
我的解决方案基于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);
}
}
有点晚了,但这里是我使用的代码(c#):
private static readonly string _Alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
public static int ColumnNameParse(string value)
{
// assumes value.Length is [1,3]
// assumes value is uppercase
var digits = value.PadLeft(3).Select(x => _Alphabet.IndexOf(x));
return digits.Aggregate(0, (current, index) => (current * 26) + (index + 1));
}
如果有人需要在没有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
NodeJS实现:
/**
* getColumnFromIndex
* Helper that returns a column value (A-XFD) for an index value (integer).
* The column follows the Common Spreadsheet Format e.g., A, AA, AAA.
* See https://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa/3444285#3444285
* @param numVal: Integer
* @return String
*/
getColumnFromIndex: function(numVal){
var dividend = parseInt(numVal);
var columnName = '';
var modulo;
while (dividend > 0) {
modulo = (dividend - 1) % 26;
columnName = String.fromCharCode(65 + modulo) + columnName;
dividend = parseInt((dividend - modulo) / 26);
}
return columnName;
},
将excel列字母(如AA)转换为数字(如25)。反过来说:
/**
* getIndexFromColumn
* Helper that returns an index value (integer) for a column value (A-XFD).
* The column follows the Common Spreadsheet Format e.g., A, AA, AAA.
* See https://stackoverflow.com/questions/9905533/convert-excel-column-alphabet-e-g-aa-to-number-e-g-25
* @param strVal: String
* @return Integer
*/
getIndexFromColumn: function(val){
var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', i, j, result = 0;
for (i = 0, j = val.length - 1; i < val.length; i += 1, j -= 1) {
result += Math.pow(base.length, j) * (base.indexOf(val[i]) + 1);
}
return result;
}