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
It was pretty slow. It took about 3 minutes to finish ... The reason is that a connection to the Excel Automation Server is created and then destroyed again in each XLSREAD / XLSWRITE call . On the plus side, these two functions hide most 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 once and rip it off when done, 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 already believe representations on FEX that do a similar thing ...