如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
当前回答
private String getColumn(int c) {
String s = "";
do {
s = (char)('A' + (c % 26)) + s;
c /= 26;
} while (c-- > 0);
return s;
}
它不是以26为底,系统中没有0。如果有的话,'Z'后面应该是'BA'而不是'AA'。
其他回答
精炼原始的解决方案(在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;
}
}
打印稿
function lengthToExcelColumn(len: number): string {
let dividend: number = len;
let columnName: string = '';
let modulo: number = 0;
while (dividend > 0) {
modulo = (dividend - 1) % 26;
columnName = String.fromCharCode(65 + modulo).toString() + columnName;
dividend = Math.floor((dividend - modulo) / 26);
}
return columnName;
}
public string ToBase26(int number)
{
if (number < 0) return String.Empty;
int remainder = number % 26;
int value = number / 26;
return value == 0 ?
String.Format("{0}", Convert.ToChar(65 + remainder)) :
String.Format("{0}{1}", ToBase26(value - 1), Convert.ToChar(65 + remainder));
}
前面的答案大部分是正确的。下面是将列号转换为excel列的另一种方法。 如果我们把它看作一个基转换,解就很简单了。简单地,将列号转换为以26为基数,因为只有26个字母。 你可以这样做:
步骤:
将列设置为商 从商变量中减去1(从上一步),因为我们需要以97为a的ASCII表结束。 除以26,得到余数。 在余数上加97并转换为字符(因为97在ASCII表中是a) 商变成了新的商/ 26(因为我们可能会越过26列) 继续这样做,直到商大于0,然后返回结果
下面是这样做的代码:)
def convert_num_to_column(column_num):
result = ""
quotient = column_num
remainder = 0
while (quotient >0):
quotient = quotient -1
remainder = quotient%26
result = chr(int(remainder)+97)+result
quotient = int(quotient/26)
return result
print("--",convert_num_to_column(1).upper())
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;
}