Dynamic ranges using xlswrite

Question

Tim 2012-11-19

2
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/54153-dynamic-ranges-using-xlswrite

编辑： Remco Hamoen 2020-4-22

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

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Andriy Chubatyy 2020-4-12

You can use this by dynamic range:

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

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

owr 2012-11-19

4
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/54153-dynamic-ranges-using-xlswrite#answer_65828

在 MATLAB Online 中打开

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 个评论
显示 4更早的评论隐藏 4更早的评论

Sunil Semwal 2013-7-27

great... it works thanks

Bashar Hariri 2019-12-25

Thanks

请先登录，再进行评论。

Answer 2

Remco Hamoen 2020-4-18

3
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/54153-dynamic-ranges-using-xlswrite#answer_426830

编辑：Remco Hamoen 2020-4-22

在 MATLAB Online 中打开

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