MATLAB Answers

Dynamic ranges using xlswrite

153 views (last 30 days)
Tim
Tim on 19 Nov 2012
Edited: Remco Hamoen on 22 Apr 2020
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

Accepted Answer

owr
owr on 19 Nov 2012
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 Comments

Sign in to comment.

More Answers (1)

Remco Hamoen
Remco Hamoen on 18 Apr 2020
Edited: Remco Hamoen on 22 Apr 2020
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

Community Treasure Hunt

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

Start Hunting!

Translated by