如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
如何在c#中将数值转换为Excel列名,而不使用直接从Excel中获取值的自动化。
Excel 2007的范围可能是1到16384,这是它支持的列数。结果值应以excel列名的形式出现,例如A、AA、AAA等。
当前回答
谢谢你的回答!!帮助我想出了这些帮助函数,与我正在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
其他回答
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
public static string ConvertToAlphaColumnReferenceFromInteger(int columnReference)
{
int baseValue = ((int)('A')) - 1 ;
string lsReturn = String.Empty;
if (columnReference > 26)
{
lsReturn = ConvertToAlphaColumnReferenceFromInteger(Convert.ToInt32(Convert.ToDouble(columnReference / 26).ToString().Split('.')[0]));
}
return lsReturn + Convert.ToChar(baseValue + (columnReference % 26));
}
如果有人需要在没有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
看到了另一个VBA答案-这可以在excel-vba中用1行UDF完成:
Function GetColLetter(ByVal colID As Integer) As String
If colID > Columns.Count Then
Err.Raise 9, , "Column index out of bounds"
Else
GetColLetter = Split(Cells(1, colID).Address, "$")(1)
End If
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));
}