将Excel列号转换为Matlab中的列名 [英] Convert Excel Column Number to Column Name in Matlab
问题描述
我正在使用Excel 2007,它支持列到16,384列。我想获取列号对应的列号。
I am using Excel 2007 which supports Columns upto 16,384 Columns. I would like to obtain the Column name corresponding Column Number.
目前,我正在使用以下代码。但是,此代码最多支持256列。任何想法如果列号大于256,如何获取列名。
Currently, I am using the following code. However this code supports upto 256 Columns. Any idea how to obtain Column Name if the column number is greater than 256.
function loc = xlcolumn(column)
if isnumeric(column)
if column>256
error('Excel is limited to 256 columns! Enter an integer number <256');
end
letters = {'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'};
count = 0;
if column-26<=0
loc = char(letters(column));
else
while column-26>0
count = count + 1;
column = column - 26;
end
loc = [char(letters(count)) char(letters(column))];
end
else
letters = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
if size(column,2)==1
loc =findstr(column,letters);
elseif size(column,2)==2
loc1 =findstr(column(1),letters);
loc2 =findstr(column(2),letters);
loc = (26 + 26*loc1)-(26-loc2);
end
end
谢谢
推荐答案
作为转移,这里是一个全功能句柄示例,(几乎)不需要基于文件的函数。这是基于 dec2base
函数,因为Excel列名称(几乎)是26个数字,令人沮丧的差异是没有0字符。
As a diversion, here is an all function handle example, with (almost) no file-based functions required. This is based on the dec2base
function, since Excel column names are (almost) base 26 numbers, with the frustrating difference that there are no "0" characters.
注意:这可能是一个可怕的想法,但它的工作。更好的解决方案可能在文件交换的其他地方找到。
Note: this is probably a terrible idea overall, but it works. Better solutions are probably found elsewhere in the file exchange.
首先,我无法解决的一个基于文件的函数,执行任意深度函数组合。 p>
First, the one file based function that I couldn't get around, to perform arbitrary depth function composition.
function result = compose( fnHandles )
%COMPOSE Compose a set of functions
% COMPOSE({fnHandles}) returns a function handle consisting of the
% composition of the cell array of input function handles.
%
% For example, if F, G, and H are function handles with one input and
% one output, then:
% FNCOMPOSED = COMPOSE({F,G,H});
% y = FNCOMPOSED(x);
% is equivalent to
% y = F(G(H(x)));
if isempty(fnHandles)
result = @(x)x;
elseif length(fnHandles)==1
result = fnHandles{1};
else
fnOuter = fnHandles{1};
fnRemainder = compose(fnHandles(2:end));
result = @(x)fnOuter(fnRemainder(x));
end
然后,将base26值转换成正确字符串的奇怪的, / p>
Then, the bizarre, contrived path to convert base26 values into the correct string
%Functions leading to "getNumeric", which creates a numeric, base26 array
remapUpper = @(rawBase)(rawBase + (rawBase>='A')*(-55)); %Map the letters 'A-P' to [10:26]
reMapLower = @(rawBase)(rawBase + (rawBase<'A')*(-48)); %Map characters '0123456789' to [0:9]
getRawBase = @(x)dec2base(x, 26);
getNumeric = @(x)remapUpper(reMapLower(getRawBase(x)));
%Functions leading to "correctNumeric"
% This replaces zeros with 26, and reduces the high values entry by 1.
% Similar to "borrowing" as we learned in longhand subtraction
borrowDownFrom = @(x, fromIndex) [x(1:(fromIndex-1)) (x(fromIndex)-1) (x(fromIndex+1)+26) (x((fromIndex+2):end))];
borrowToIfNeeded = @(x, toIndex) (x(toIndex)<=0)*borrowDownFrom(x,toIndex-1) + (x(toIndex)>0)*(x); %Ugly numeric switch
getAllConditionalBorrowFunctions = @(numeric)arrayfun(@(index)@(numeric)borrowToIfNeeded(numeric, index),(2:length(numeric)),'uniformoutput',false);
getComposedBorrowFunction = @(x)compose(getAllConditionalBorrowFunctions(x));
correctNumeric = @(x)feval(getComposedBorrowFunction(x),x);
%Function to replace numerics with letters, and remove leading '@' (leading
%zeros)
numeric2alpha = @(x)regexprep(char(x+'A'-1),'^@','');
%Compose complete function
num2ExcelName = @(x)arrayfun(@(x)numeric2alpha(correctNumeric(getNumeric(x))), x, 'uniformoutput',false)';
现在使用一些强调转换测试:
Now test using some stressing transitions:
>> num2ExcelName([1:5 23:28 700:704 727:729 1024:1026 1351:1355 16382:16384])
ans =
'A'
'B'
'C'
'D'
'E'
'W'
'X'
'Y'
'Z'
'AA'
'AB'
'ZX'
'ZY'
'ZZ'
'AAA'
'AAB'
'AAY'
'AAZ'
'ABA'
'AMJ'
'AMK'
'AML'
'AYY'
'AYZ'
'AZA'
'AZB'
'AZC'
'XFB'
'XFC'
'XFD'
这篇关于将Excel列号转换为Matlab中的列名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!