How i can reorder the sheets of excel in matlab to do a same sheet ?

Hello, thank you so much for reading me, i have a question please.

How can reorder the sheets that i have in this program in matlab to do only a matrix with all the sheets? i want the original data of the archive named REN

clear;clc;

Cyears = 9;
Cdays = 31; % Enero

RAD = nan(Cdays, Cyears); % Average daily data: dia  x año


for i=1:Cyears 
    RS = xlsread('REN',i); 
    RAD(:, i) = 600*sum(RS)'/1000000;
end

figure(1); imagesc(RAD); colorbar;

Thank you in advance ..

Best!!

Answer 1

[KSSV]

clear;clc;

Cyears = 9;
Cdays = 31; % Enero

iwant = zeros(144,31,9) ; 
for i=1:Cyears 
    RS = xlsread('REN.xlsx',i);
    iwant(:,:,i) = RS ; 
end

[Ana Soph]

thank you, but how can i collect the data ?

[Ana Soph]

• i have this

[KSSV]

The data is already collected in iwant. It is a 3D matrix of size 144*31*9.

You can access data using

iwant(:,:,1)   % first matrix/ first sheet data
iwant(:,:,7)   % seventh matrix/ seventh sheet data

You canget the sheetnames of excel using the functions sheetnames. Read about it:

https://www.mathworks.com/help/matlab/ref/sheetnames.html#:~:text=sheets%20%3D%20sheetnames(%20filename%20)%20returns,from%20the%20specified%20spreadsheet%20file.

[Ana Soph]

thanks, but i want to copy the result in one sheet in excel :$

[Ana Soph]

i want to copy all the result of the matrix in one sheet :O like all the matrix are together in the same matrix

[Walter Roberson]

Currently each sheet is 144 x 31 it appears, and you have 9 sheets.

When you collect all of the data onto one sheet, do you want (144*9) rows and 31 columns, or do you want 144 rows and (31 * 9) columns ?

[Ana Soph]

yes, the second one 144 rows and (31 * 9) columns

[KSSV]

clear;clc;
Cyears = 9;
Cdays = 31; % Enero

RAD = nan(Cdays, Cyears); % Average daily data: dia  x año

iwant = cell(9,1) ; 
for i=1:Cyears 
    RS = xlsread('REN.xlsx',i); 
    iwant{i} = RS ; 
end
iwant = cell2mat(iwant) ; 
xlswrite('test.xlsx',iwant) ; 

Use writetable..it is suggested when comapred to xlswrite.

[KSSV]

clear;clc;

Cyears = 9;
Cdays = 31; % Enero

RAD = nan(Cdays, Cyears); % Average daily data: dia  x año

iwant = cell(1,9) ; 
for i=1:Cyears 
    RS = xlsread('REN.xlsx',i); 
    iwant{i} = RS ; 
end
iwant = cell2mat(iwant) ; 
xlswrite(iwant,'test.xlsx') ; 

[Ana Soph]

thank you so much!!

another thing if you can ... how can i average every 6 rows, is like i want to conver the values to hours and this values are in every 10 minutes

[Walter Roberson]

squeeze(mean(reshape(iwant, size(iwant,1)/6, 6, []), 2))

[Ana Soph]

thank you so much good man :D !

Answer 2

[Image Analyst]

I'm not sure how the accepted answer does that. Maybe I didn't read closely enough. But if you want to do "only a matrix with all the sheets" in the workbook and not process any workbook where all the expected sheets are not there, then you can do this:

sheets = sheetnames('airlinesmall_subset.xlsx')
if length(sheets) == 13
	% All sheets are present.  We're expecting there to be 13 sheets in this workbook.
else
	% All sheets are NOT present.  Some are missing.
end

Now, to "reorder the sheets of excel" as you requested, there are two ways. You can either open the workbook using ActiveX (if using Windows) and then use ActiveX commands to put the sheets in whatever order you want, or you can call writecell() or writematrix() once per sheet in the desired order.

[Ana Soph]

Thank you, but i have the matlab R2019 and i can't use sheetnames

[Walter Roberson]

https://www.mathworks.com/help/matlab/ref/xlsfinfo.html

Second output of xlsfinfo has the sheet names

[Image Analyst]

It was introduced in R2019b, so you must have the r2019a version and can use the function Walter mentioned. You see, it really does help when you completely fill out the submission form and not just blow by/ignore things like what version you're using.

[Ana Soph]

Awesome! , yes i have R2019a, so.. ok i will try this tool, thank you so much! :D

[Image Analyst]

Try this:

[~, sheetNames] = xlsfinfo('airlinesmall_subset.xlsx')
if length(sheetNames) == 13
	% All sheets are present.
else
	% All sheets are not present.  Some are missing.
end

If you're skilled enough to use ActiveX, I'm attaching my ActiveX Excel utilities class. If you have Windows and ActiveX you can do literally anything that you'd do in Excel itself directly.

If I helped you maybe you could at least vote for my answer even though it wasn't your preferred answer.