如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
当前回答
(我知道这个问题与c#有关,但是,如果读者需要用Java做同样的事情,那么下面的内容可能会有用)
事实证明,使用Jakarta POI中的“CellReference”类可以很容易地做到这一点。此外,转换可以以两种方式进行。
// Convert row and column numbers (0-based) to an Excel cell reference
CellReference numbers = new CellReference(3, 28);
System.out.println(numbers.formatAsString());
// Convert an Excel cell reference back into digits
CellReference reference = new CellReference("AC4");
System.out.println(reference.getRow() + ", " + reference.getCol());
其他回答
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;
递归很简单。
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) );
}
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;
}
简单而简洁的JavaScript函数,将列号转换为电子表格列名。
function column(number) { const name = []; for(let n = number - 1; n >= 0; n = Math.floor(n / 26) - 1) { name.push(String.fromCharCode(65 + n % 26)); } return name.reverse().join(""); }; console.log(column(1), "A"); console.log(column(26), "Z"); console.log(column(27), "AA"); console.log(column(52), "AZ"); console.log(column(53), "BA"); console.log(column(702), "ZZ"); console.log(column(703), "AAA"); console.log(column(704), "AAB"); console.log(column(16384), "XFD");
谢谢你的回答!!帮助我想出了这些帮助函数,与我正在Elixir/Phoenix中工作的谷歌Sheets API进行一些交互
以下是我想到的(可能需要一些额外的验证和错误处理)
长生不老药:
def number_to_column(number) do
cond do
(number > 0 && number <= 26) ->
to_string([(number + 64)])
(number > 26) ->
div_col = number_to_column(div(number - 1, 26))
remainder = rem(number, 26)
rem_col = cond do
(remainder == 0) ->
number_to_column(26)
true ->
number_to_column(remainder)
end
div_col <> rem_col
true ->
""
end
end
逆函数是:
def column_to_number(column) do
column
|> to_charlist
|> Enum.reverse
|> Enum.with_index
|> Enum.reduce(0, fn({char, idx}, acc) ->
((char - 64) * :math.pow(26,idx)) + acc
end)
|> round
end
还有一些测试:
describe "test excel functions" do
@excelTestData [{"A", 1}, {"Z",26}, {"AA", 27}, {"AB", 28}, {"AZ", 52},{"BA", 53}, {"AAA", 703}]
test "column to number" do
Enum.each(@excelTestData, fn({input, expected_result}) ->
actual_result = BulkOnboardingController.column_to_number(input)
assert actual_result == expected_result
end)
end
test "number to column" do
Enum.each(@excelTestData, fn({expected_result, input}) ->
actual_result = BulkOnboardingController.number_to_column(input)
assert actual_result == expected_result
end)
end
end