Dynamic ranges using xlswrite

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

[Andriy Chubatyy]

You can use this by dynamic range:

https://es.mathworks.com/matlabcentral/fileexchange/28343-column-converter-for-excel

Answer 1

[owr]

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

[owr]

Note that I used the function "allcomb" in my implementation. Its not built in to MATLAB, but is on the File Exchange here:

http://www.mathworks.com/matlabcentral/fileexchange/10064-allcomb

Thanks again Jos!

[Tim]

Thanks! Only, it does not recognize 'allcomb'. Is this another function perhaps?

[Tim]

Woops, saw this only now, thanks! :)

[Tim]

Tried it, works like a charm!

[Sunil Semwal]

great... it works thanks

[Bashar Hariri]

Thanks

Answer 2

[Remco Hamoen]

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