How to merge excel files?

I have 4 excel files. Each excel file contains 5 sheets (Sheet1, Sheet2, Sheet3, Sheet4 and Sheet5). Each sheet contains different number of rows.

I want to merge all the excel files into one excel file containing 5 sheets. Content of sheet1 of all the 4 excel files should merge into sheet1 of the new excel file. Similarly content of sheet2 of all the 4 excel files should merge into sheet2 of the new excel file. Same is the case with rest of sheets of the 4 excel file.

Also all the sheet of all 4 excel files contains a header row. But in final output excel file I want only one header row per sheet.

Please tell me how this can be done

Thanks in advance

Answer 1

[Yona]

This code will work, If you have the same number of column in the same sheets (all sheet1s have the same columns number, ext.)

res = cell(1,5); %sheets number
for i=1:4 %file number
    if exist(strcat(FilePath,filesep,'file_name', num2str(i),'.xlsx'),'file')
        for j=1:5 %sheets number
           [~, ~, HelpCell] = xlsread(strcat(FilePath,filesep,'file_name', num2str(i),'.xlsx'),strcat('sheet', num2str(j));
           if isempty(HSrejectC)
               res{j} = HelpCell;
           else
               res{j} = [res{j}; HelpCell(2:end,:)];
           end
        end
    end
end

after this you need to save res to 5 sheets by using xslwrite.

[Anil Verma]

Thanks, your code worked for me after replacing variable with Values. Here is my code created from your code

res = cell(1,5); %sheets number
files=dir('*.xlsx');
filesCount = size(files,1);
for i=1:filesCount %file number
    if exist(files(i).name,'file')
        [type,sheetname] = xlsfinfo(fullfile('Matlab', files(i).name));
        for j=1:size(sheetname, 2) %sheets number
           [~, ~, HelpCell] = xlsread(files(i).name, sheetname{j});
           if i == 1
               res{j} = HelpCell;
           else
               res{j} = [res{j}; HelpCell(2:end,:)];
           end
        end
    end
end