如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
当前回答
以下是Graham在Powershell中的代码:
function ConvertTo-ExcelColumnID {
param (
[parameter(Position = 0,
HelpMessage = "A 1-based index to convert to an excel column ID. e.g. 2 => 'B', 29 => 'AC'",
Mandatory = $true)]
[int]$index
);
[string]$result = '';
if ($index -le 0 ) {
return $result;
}
while ($index -gt 0) {
[int]$modulo = ($index - 1) % 26;
$character = [char]($modulo + [int][char]'A');
$result = $character + $result;
[int]$index = ($index - $modulo) / 26;
}
return $result;
}
其他回答
在Delphi (Pascal)中:
function GetExcelColumnName(columnNumber: integer): string;
var
dividend, modulo: integer;
begin
Result := '';
dividend := columnNumber;
while dividend > 0 do begin
modulo := (dividend - 1) mod 26;
Result := Chr(65 + modulo) + Result;
dividend := (dividend - modulo) div 26;
end;
end;
有点晚了,但这里是我使用的代码(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));
}
Objective-C实现:
-(NSString*)getColumnName:(int)n {
NSString *name = @"";
while (n>0) {
n--;
char c = (char)('A' + n%26);
name = [NSString stringWithFormat:@"%c%@",c,name];
n = n/26;
}
return name;
}
迅速实现:
func getColumnName(n:Int)->String{
var columnName = ""
var index = n
while index>0 {
index--
let char = Character(UnicodeScalar(65 + index%26))
columnName = "\(char)\(columnName)"
index = index / 26
}
return columnName
}
答案是基于:https://stackoverflow.com/a/4532562/2231118
这是一个javascript版本,根据格雷厄姆的代码
function (columnNumber) {
var dividend = columnNumber;
var columnName = "";
var modulo;
while (dividend > 0) {
modulo = (dividend - 1) % 26;
columnName = String.fromCharCode(65 + modulo) + columnName;
dividend = parseInt((dividend - modulo) / 26);
}
return columnName;
};
以下是Graham在Powershell中的代码:
function ConvertTo-ExcelColumnID {
param (
[parameter(Position = 0,
HelpMessage = "A 1-based index to convert to an excel column ID. e.g. 2 => 'B', 29 => 'AC'",
Mandatory = $true)]
[int]$index
);
[string]$result = '';
if ($index -le 0 ) {
return $result;
}
while ($index -gt 0) {
[int]$modulo = ($index - 1) % 26;
$character = [char]($modulo + [int][char]'A');
$result = $character + $result;
[int]$index = ($index - $modulo) / 26;
}
return $result;
}