CODEWITHSUNDEEP
excelmatlabmatrix
bill
bill

Reputation: 147

How to find the last column index in excel with Matlab

I am writing data as matrices into excel file using Matlab. My matrices' dimension varies and I don't know the matrices' dimension in advance. Let's say I have matrix A, B, and C of various dimensions. What I want to do is to write matrix A into excel first and write matrix B after A with 1 column of gap between them. I don't know if there is a way Matlab could find the last column index in excel and create a gap between these two matrices.

My code is something like this now:

xlswrite('My file.xls',A,'My Sheet','B2');

%%
Here is what I don't know to fill in to find the starting range index for my next matrix. 
%%

xlswrite('My file.xls',B,'My Sheet','newindex');

Upvotes: 0

Views: 1504

Answers (2)

Hoki
Hoki

Reputation: 11812

To calculate an Excel column ID based on an index number I made the following function:

function col = excel_column(n)

    %// find LSB
    xlsb = mod(n-1,26)+1 ;
    xmsb = fix((n-1)/26) ;

    %// find MSB (recursively if necessary)
    if xmsb >= 1
        col = [excel_column(xmsb) char(xlsb+64)] ;
    else
        col = char(xlsb+64) ;
    end

This will work for any number, but be careful that Excel has a maximum number of column (2^14=16384 columns max in my version). As an example, to shows that it handles the letter incrementation, you can run the short test:

>> x = [25:28 233:236 700:705 16383:16385] ;
for n=x
    fprintf('Index: %5d => Column: %s\n', n , excel_column(n) )
end

Index:    25 => Column: Y
Index:    26 => Column: Z
Index:    27 => Column: AA
Index:    28 => Column: AB
Index:   233 => Column: HY
Index:   234 => Column: HZ
Index:   235 => Column: IA
Index:   236 => Column: IB
Index:   700 => Column: ZX
Index:   701 => Column: ZY
Index:   702 => Column: ZZ
Index:   703 => Column: AAA
Index:   704 => Column: AAB
Index:   705 => Column: AAC
Index: 16383 => Column: XFC
Index: 16384 => Column: XFD %// Last real column
Index: 16385 => Column: XFE %// Careful. This column DOES NOT exist in Excel

So in your case, You start to write your matrix A at column 'B...', which is column index 2. To know where to start your matrix B, simply calculate the size of A and add the necessary gap. Let's say your matrix A has 573 columns.

startIdx_A   = 2 ;         %// Matrix "A" started at column index 2
ncA          = size(A,2) ; %// Number of column in A, should return 573
columnGap    = 1 ;         %// how much gap you want between "A" and "B"

startColumnMatrixB_index = startIdx + ncA + columnGap ; %// index of the first column for Matrix "B"
startColumnMatrixB_excel = excel_column(startColumnMatrixB_index) ; %// return 'VD' (assuming A had 573 columns)

If your matrices are very large (in number of columns), it would be prudent to include a check to make sure you won't run out of column before you call the xlswrite

Upvotes: 1

Alex
Alex

Reputation: 699

You could try something like:

cellVector= ['A2';'B2';'C2';'D2';'E2']; %...etc as many as you need
cellstart = 'B2';
cellVectorLoc = find(cellVector == cellstart(1)); % Finds where B is located in cellVector  
xlswrite('My file.xls',A,'My Sheet',cellstart);
nextCellLoc = length(A) + cellVectorLoc + 1; % length of A plus location in index + 1 for blank column.  I can't remember if you should use length() or size().
newIndex= cellVector(nextCellLoc,:); % new index
xlswrite('My file.xls',B,'My Sheet',newindex);

Just be aware, this won't work for cells after column Z, because, for example, cellVectorLoc = find(cellVector == cellstart(1)); will find two locations in cellVector for column 'AA'.

Also, I can't remember if length(A) or which element of size(A) refers to the number of columns in A. Play around with the other one if length(A) doesn't give you the correct number of cols.

Upvotes: 0

Related Questions