MATLAB Answers


Dynamic ranges using xlswrite

Asked by Tim
on 19 Nov 2012
Accepted Answer by owr

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')

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!




No products are associated with this question.

1 Answer

Answer by owr
on 19 Nov 2012
 Accepted answer

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 =

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 =

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');
    % 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};


on 19 Nov 2012

Woops, saw this only now, thanks! :)

on 19 Nov 2012

Tried it, works like a charm!

on 27 Jul 2013

great... it works thanks

Join the 15-year community celebration.

Play games and win prizes!

Learn more
Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

MATLAB Academy

New to MATLAB?

Learn MATLAB today!