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

Learn moreOpportunities for recent engineering grads.

Apply Today**New to MATLAB?**

Asked by Tim
on 19 Nov 2012

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

*No products are associated with this question.*

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 =

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

## 0 Comments