MATLAB: Dynamic ranges using xlswrite

rangexlswrite

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

Best 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

Related Solutions

MATLAB: How to write into an excel file column wise

for col=1,N
  V=yourColumnOutputFunction(...
  xlswrite(file,sheet,[xlsAddr(1,col) ':' xlsAddr(size(V,1),col)]);
end

where xlsAddr is my helper utility function

function rnge=xlsAddr(row,col)
% Build Excel cell address from row, column
%



% RNGE=XLSADDR(COL,ROW) will return an Excel cell address 
% formed from the input ROW,COL values.  Either input may be
% string or numeric and will be converted to canonical form
if isnumeric(col)
  if ~isscalar(col),  error('Input Column Not Scalar'), end
  rnge=num2str('A'+[fix(col/26) rem(col,26)]-1,'%c%c');
  rnge(rnge=='@')=[];   % cleanup for single character
else
  rnge=col;
end
if isnumeric(row)
  if ~isscalar(row),  error('Input Row Not Scalar'), end
  rnge=[rnge num2str(row,'%d')];
else
  row=num2str(row,'%d');
  if ~all(ismember(row,'0':'9')),  error('Invalid Excel Address: Row not numeric'), end
  rnge=[rnge row];
end

There's a complement,

function [row,col]=xlsRowCol(rnge,r1,c1)
% Return row, column from Excel range address and optional offset
%
% [ROW,COL]=XLSADDR(RNGE) will return a ROW,COL array index values
% formed from the input Excel cell range expression.  Default addressing
% is one-based array indexing.
%
% [ROW,COL]=XLSADDR(RNGEA:RNGEB) will return a ROW,COL array index values
% formed from the input Excel cell range expression as 2D array by row.
%
% [ROW,COL]=XLSADDR(RNGE,R1,C1) will use optional R1, C1 values as base
% indices for the returned ROW,COL array index values
    switch nargin
      case 1
        r1 = 0;
        c1 = 0;
      case 2
        c1 = 0;
    end
    rnge=char(split(rnge,':'));         % split out the ranges if exist
    m=size(rnge,1);
    row=zeros(m,1); col=zeros(m,1);
    for i=1:m
      cstr=rnge(i,isletter(rnge(i,:)));    % pull out column letters only
      if length(cstr)>2, error('Input Column Too Long'), end
      bArr=[1 26];                  % hardcode base vector since not general
      b=bArr(1:length(cstr)).';     % base vector for specific input length
      col(i)=(cstr-'@')*b;
      rstr=rnge(i,ismember(rnge(i,:),'0':'9'));  % pull out row numbers only
      row(i)=str2num(rstr);                  % and convert to numeric row
    end
    % convert to reference origin before returning
    row=row+r1;
    col=col+c1;
  end

MATLAB: How to use strings in function

You need to concatenate the output of the function you created. That way you get a single char vector as the range specification.

ETABSresponse=xlsread('ETABS.xlsx',['A1:' ExcelColumn]);

I sometimes use a function like the one below to create the column name:

L=1;
ExcelColumn=sprintf('%s%d',get_ExcelColName(L/0.001+1),7);
disp(ExcelColumn)
ALM7
function ExcelColName=get_ExcelColName(col_val)
%convert a col value to the Excel col name (so 27 to AA, 705 to AAC)
validateattributes(col_val,{'numeric'},{'scalar','nonzero','integer'})
base=26;
col_val=col_val-1;
digs=floor(log(col_val)/log(base))+1;%number of 'digits' (i.e. letter positions)
ExcelColName=zeros(1,digs);
for cur_d=digs:-1:1
    ExcelColName(cur_d)=mod(col_val,base);
    col_val=(col_val-ExcelColName(cur_d))/base;
end
if isempty(ExcelColName),ExcelColName=0;end%in case of col_val input is 1
ExcelColName(end)=ExcelColName(end)+1;
ExcelColName=char(ExcelColName+64);
end

Best Answer

Related Solutions

MATLAB: How to write into an excel file column wise

MATLAB: How to use strings in function

Related Question