Dynamic ranges using xlswrite

14 次查看（过去 30 天）
Tim
Tim 2012-11-19
编辑： Remco Hamoen 2020-4-22
Hi all,
I want to write my results into excel. This works perfectly, only I want my ranges to become dynamic. At the moment I have this:
xlswrite('d:\test3.xlsx',stuff(:,:,newtry,1), sprintf('Sheet%d',newtry), 'C1:CX20')
xlswrite('d:\test3.xlsx',stuff(:,:,newtry,2),sprintf('Sheet%d',newtry),'C22:CX41')
xlswrite('d:\test3.xlsx',stuff(:,:,newtry,3),sprintf('Sheet%d',newtry),'C43:CX62')
In this case the results would be 100 (C->CX) columns long and 20 rows deep (1->20), where 100 (i) & 20 (cnt) naturally represent for-loops.
But is it possible to make this range dynamic? In other words, if I change i to 50 and cnt to 5 so that 'C1:CX20' automatically changes to 'C1:AY5'.
I hope I'm being clear. Thanks in forward!
Tim

请先登录，再回答此问题。

采纳的回答

owr
owr 2012-11-19
You can do this by dynamically creating the character string that you send to xlswrite to specify xl range.
Something like this:
>> firstRow = 1;
>> lastRow = 5;
>> firstCol = 'C';
>> lastCol = 'AY';
>> cellRange = [firstCol,num2str(firstRow),':',lastCol,num2str(lastRow)]
cellRange =
C1:AY5
The only thing left, is to figure out what "lastCol" is based on the column number. I wrote a function to do this myself a little while back. I know there are other versions floating around as well.
For example:
>> xlcolumnletter(51)
ans =
AY
Here's the code:
function colLetter = xlcolumnletter(colNumber)
% Excel formats columns using letters.
% This function returns the letter combination that corresponds to a given
% column number.
% Limited to 702 columns
if( colNumber > 26*27 )
error('XLCOLUMNLETTER: Requested column number is larger than 702. Need to revise method to work with 3 character columns');
else
% Start with A-Z letters
atoz = char(65:90)';
% Single character columns are first
singleChar = cellstr(atoz);
% Calculate double character columns
n = (1:26)';
indx = allcomb(n,n);
doubleChar = cellstr(atoz(indx));
% Concatenate
xlLetters = [singleChar;doubleChar];
% Return requested column
colLetter = xlLetters{colNumber};
end
  6 个评论
显示 4更早的评论

请先登录，再进行评论。

更多回答（1 个）

Remco Hamoen
Remco Hamoen 2020-4-18
编辑：Remco Hamoen 2020-4-22
The following function returns the column characters in Excel as function of the column number.
function CHAR = xlcol2char(COL)
%% Returns the column characters of Excel given a certain column number
% Input COL : number of column
% Output CHAR : Character combination in Excel
if COL <= 26 % [A..Z]
CHAR = char(mod(COL-1,26)+1+64);
elseif COL <= 702 % [AA..ZZ]
COL = COL-26;
CHAR1 = char(floor((COL-1)/26)+1+64);
CHAR0 = char(mod(COL-1,26)+1+64);
CHAR = [CHAR1 CHAR0];
elseif COL <= 16384 % [AAA..XFD]
COL = COL-702;
CHAR2 = char(floor((COL-1)/676)+1+64);
COL=COL-(floor((COL-1)/676))*676;
CHAR1 = char(floor((COL-1)/26)+1+64);
CHAR0 = char(mod(COL-1,26)+1+64);
CHAR = [CHAR2 CHAR1 CHAR0];
else
disp('Column does not exist in Excel!');
end
end
Hope this helps you even if you have more than 702 columns.
The next function returns the complete range description:
function CELL = xlRC2A1(ROW,COL)
%% Returns the column characters of Excel given a certain column number
% Input ROW : row number
% COL : number of column
% Output CHAR : Character combination in Excel
if COL <= 26 % [A..Z]
CHAR = char(mod(COL-1,26)+1+64);
CELL = [CHAR num2str(ROW)];
elseif COL <= 702 % [AA..ZZ]
COL = COL-26;
CHAR1 = char(floor((COL-1)/26)+1+64);
CHAR0 = char(mod(COL-1,26)+1+64);
CHAR = [CHAR1 CHAR0];
CELL = [CHAR num2str(ROW)];
elseif COL <= 16384 % [AAA..XFD]
COL = COL-702;
CHAR2 = char(floor((COL-1)/676)+1+64);
COL=COL-(floor((COL-1)/676))*676;
CHAR1 = char(floor((COL-1)/26)+1+64);
CHAR0 = char(mod(COL-1,26)+1+64);
CHAR = [CHAR2 CHAR1 CHAR0];
CELL = [CHAR num2str(ROW)];
else
disp('Column does not exist in Excel!');
end
end
Regards,
Remco

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by