如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
当前回答
另一种VBA方式
Public Function GetColumnName(TargetCell As Range) As String
GetColumnName = Split(CStr(TargetCell.Cells(1, 1).Address), "$")(1)
End Function
其他回答
有点晚了,但这里是我使用的代码(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));
}
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;
更新:Peter的评论是正确的。这就是我在浏览器中编写代码的结果。:-)我的解决方案是不编译,它是最左边的字母,它是在反向顺序构建字符串-现在都固定了。
除了bug之外,该算法基本上是将一个数字从10进制转换为26进制。
更新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;
另一个解决方案:
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博士
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;
}
只是抛出一个简单的使用递归的两行c#实现,因为这里所有的答案似乎都比必要的复杂得多。
/// <summary>
/// Gets the column letter(s) corresponding to the given column number.
/// </summary>
/// <param name="column">The one-based column index. Must be greater than zero.</param>
/// <returns>The desired column letter, or an empty string if the column number was invalid.</returns>
public static string GetColumnLetter(int column) {
if (column < 1) return String.Empty;
return GetColumnLetter((column - 1) / 26) + (char)('A' + (column - 1) % 26);
}