Here is something that will read the given "data" sheets (in this case 'MissingData' and 'NewlyAddedData'), separate their contents according to the value in the first column (status), and write the data for each status to a new sheet with format more or less as given in the example file:
% read the input data sheets
file_name = 'SummaryResult.xlsx';
sheet_names = {'MissingData' 'NewlyAddedData'};
N_data = numel(sheet_names);
data = cell(1,N_data);
for ii = 1:N_data
data{ii} = readcell(file_name, ...
'Sheet',sheet_names{ii}, ...
'NumHeaderLines',1);
end
% make sure they have the same number of columns
Ncol_in = cellfun(@(x)size(x,2),data);
Ncol_out = max(Ncol_in);
idx = find(Ncol_in < Ncol_out);
for ii = idx(:).'
data{ii} = [data{ii} repmat({''},size(data{ii},1),Ncol_out-Ncol_in(ii))];
end
% get the set of unique statuses (column 1 of each)
status = cellfun(@(x)x(:,1),data,'UniformOutput',false);
status = unique(vertcat(status{:}));
% new sheet names are upper(status)
new_sheet_names = upper(status);
% write one new sheet for each status
N_new_sheets = numel(status);
% with 3 lines at the top (data name to be put in later)
section_header = repmat({''},3,Ncol_out);
% and 2 blank lines between sections
section_break = repmat({''},2,Ncol_out);
for jj = 1:N_new_sheets
% concatenate data for each new sheet
new_sheet_data = cell(0,Ncol_out);
% for each data section
for ii = 1:N_data
% put data name in section header
section_header{2,2} = sheet_names{ii};
% followed by data where column 1 is status{jj}
% followed by section break (2 blank lines)
new_sheet_data = [new_sheet_data; ...
section_header; ...
data{ii}(strcmp(data{ii}(:,1),status{jj}),:); ...
section_break];
end
% write the new sheet, overwriting anything that was already there
writecell(new_sheet_data,file_name, ...
'Sheet',new_sheet_names{jj}, ...
'WriteMode','overwritesheet');
end
% check the new sheets
for jj = 1:N_new_sheets
opts = detectImportOptions(file_name,'Sheet',new_sheet_names{jj});
opts.DataRange = 'A1';
disp(new_sheet_names{jj});
disp(readcell(file_name,opts));
end
Note that if a given status has no data (e.g., 'ddd' and 'eee' have no 'MissingData' in this case), then that data section in the sheet is still written but it contains only blank lines. To change the behavior so that those sections are not written at all, you can make a slight modification to the code, as follows:
for jj = 1:N_new_sheets
% concatenate data for each new sheet
new_sheet_data = cell(0,Ncol_out);
% for each data section
for ii = 1:N_data
% idx: logical index whether each row matches status{jj}
idx = strcmp(data{ii}(:,1),status{jj});
if ~any(idx)
% if no matches, don't write this section
continue
end
% put data name in section header
section_header{2,2} = sheet_names{ii};
% followed by data where column 1 is status{jj}
% followed by section break (2 blank lines)
new_sheet_data = [new_sheet_data; ...
section_header; ...
data{ii}(idx,:); ...
section_break];
end
% write the new sheet, overwriting anything that was already there
writecell(new_sheet_data,file_name, ...
'Sheet',new_sheet_names{jj}, ...
'WriteMode','overwritesheet');
end
% check the new sheets
for jj = 1:N_new_sheets
opts = detectImportOptions(file_name,'Sheet',new_sheet_names{jj});
opts.DataRange = 'A1';
disp(new_sheet_names{jj});
disp(readcell(file_name,opts));
end
