How to merge excel files?

Question

Anil Verma 2016-2-15

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/268151-how-to-merge-excel-files

编辑： Anil Verma 2016-2-15

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Yona 2016-2-15

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/268151-how-to-merge-excel-files#answer_209861

在 MATLAB Online 中打开

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.

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Anil Verma 2016-2-15

编辑：Anil Verma 2016-2-15

在 MATLAB Online 中打开

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