I created 10 .xls files for testing with 5 sheets each. All sheets have 5x6 cells of random numbers. Here is my first solution:
%# get input XLS files
dName = uigetdir('.', 'Select folder containing Excel XLS files');
if dName==0, error('No folder selected'); end
files = dir( fullfile(dName,'*.xls') );
files = strcat(dName, filesep, {files.name}'); %'
%# prepare output XLS file
[fName dName] = uiputfile({'*.xls' 'Excel (*.xls)'}, 'Output File', 'final.xls');
if dName==0, error('No file selected'); end
fOut = fullfile(dName,fName);
%# process
NUM_SHEETS = 5; %# number of sheets per file
for s=1:NUM_SHEETS
%# extract contents of same sheet from all files
numData = cell(numel(files),1);
for f=1:numel(files)
numData{f} = xlsread(files{f}, s);
end
%# rearrange data
numData = cat(3,numData{:});
numData = reshape(permute(numData,[3 1 2]), [], size(numData,2));
%# write data to corresponding sheet of output XLS file
xlswrite(fOut, numData, s);
end
This was quite slow. It took around 3 minutes to finish... The reason is that a connection to Excel automation server is created then destroyed repeatedly in each call to XLSREAD/XLSWRITE. On the plus side, these two functions hide away a lot of the dirty work needed to interact with Excel, and expose an easy-to-use interface.
In my second solution, I manually call the Excel COM API. The advantage is that we initiate it only one time, and tear it down once we are finished, eliminating a lot of overhead. In fact, this code executes in less than 4 seconds!:
%# get input XLS files
dName = uigetdir('.', 'Select folder containing Excel XLS files');
if dName==0, error('No folder selected'); end
files = dir( fullfile(dName,'*.xls') );
files = strcat(dName, filesep, {files.name}'); %'
%# get output XLS file
[fName dName] = uiputfile({'*.xls' 'Excel (*.xls)'},'Output File','final.xls');
if dName==0, error('No file selected'); end
fOut = fullfile(dName,fName);
%# open Excel COM Server
Excel = actxserver('Excel.Application');
Excel.DisplayAlerts = 0;
%# prepare output
if ~exist(fOut, 'file')
%# create if doesnt exist
wb = Excel.workbooks.Add;
wb.SaveAs(fOut,1);
wb.Close(false);
else
%# delete existing file
delete(fOut);
end
%# extract contents of input files
NUM_SHEETS = 5;
data = cell(numel(files),NUM_SHEETS);
for f=1:numel(files)
wb = Excel.Workbooks.Open(files{f}, 0, true); %# open XLS file for reading
assert( wb.sheets.Count == NUM_SHEETS );
for s=1:NUM_SHEETS %# loop over all sheets
%# activate sheet, and extract entire content
Excel.sheets.get('item',s).Activate();
Excel.Range('A1').Activate();
data{f,s} = cell2num( Excel.ActiveSheet.UsedRange.Value );
end
wb.Close(false); %# close XLS file
end
%# rearrange data
D = cell(NUM_SHEETS,1);
for s=1:NUM_SHEETS
x = cat(3,data{:,s});
D{s} = reshape(permute(x,[3 1 2]), [], size(x,2));
end
%# write data to sheets of output XLS file
wb = Excel.Workbooks.Open(fOut, 0, false); %# open XLS file for writing
while Excel.Sheets.Count < NUM_SHEETS %# create sheets as required
Excel.Sheets.Add([], Excel.Sheets.Item(Excel.Sheets.Count));
end
for s=1:NUM_SHEETS %# write conents to each sheet
cellRange = sprintf('A1:%s%d', 'A'+size(D{s},2)-1, size(D{s},1));
wb.sheets.get('item',s).Activate();
Excel.Range(cellRange).Select();
set(Excel.selection, 'Value',num2cell(D{s}));
end
wb.Save();
wb.Close(false); %# close XLS file
%# cleanup
Excel.Quit();
Excel.delete();
clear Excel;
I believe there are already submissions on the FEX that do a similar thing...
