coupling of coorresponding sheets of excel files

2 views (last 30 days)
I have data in 10 excel files and each excel file contains 50 sheets. I want to couple data of each corresponding sheet of excel file means first sheet data of all 10 files, 2nd sheet data of all 10 excel files to 50th sheet of all 10 files. Hence finally there should be 50 sheets in one file. How is it possible. Please suggest.

Accepted Answer

Bob Thompson
Bob Thompson on 22 May 2019
There are two ways of doing this. You can either use xlsread and load each set of data into matlab one sheet at a time, or you can use actX and do much the same thing. Personally, because you have so many sheets and files, I would suggest using actX because it doesn't require you to open an close excel 500 times.
Examples of both methods:
files = dir('*.xlsx'); % Get list of files; this does assume you are working directly in the directory which contains them
%% xlsread method
for i = 3:length(files)
for j = 1:50
data(:,:,j,i) = xlsread(files(i).name,j);
end
end
%% actx method
xl = actxserver('Excel.Application'); % Start Excel
% set(xl,'Visible',1); % Make excel visible
xlsdir = pwd; % Work in current directory
for i = 3:length(files)
xls = xl.Workbooks.Open(files(i).name); % Open specific document; may need to use full file path
xlss = xls.Worksheets;
for j = 1:50
xlssl = xlss.get('Item',j);
colEnd = xlssl.Range('A1').End('xlToRight').Column;
rowEnd = xlssl.Range('A1').End('xlDown').Row;
range = ['A1:',char('A'-1+rem),char('A'-1+rem2),num2str(rowEnd)];
Range = get(eSheet1,'Range',range);
data(:,:,j,i) = Range.Value;
end
end
%% Reorganizing things for one sheet
for j = 1:50
out = [data(:,:,j,:)];
% Preferred print to file method, xlswrite, or actx
xlswrite('myoutfile.xlsx',out,j);
% or
xls = xl.WorkBooks.Open('myoutfile.xlsx'); % Should move this outside the loop, no need to repeat
xlssl = xls.Worksheets.get('Item',j);
range = 'A1:XX100'; % You will need to find your own range
Range = get(xlssl,'Range',range);
Range.Value = out;
end
That should give you a sterting point from whichever method you prefer. Note that I assumed all of your data was the same size. If not, you will need to adjust your matlab data storage variable (I called it data) to be able to accomodate the different sizes.

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!