How to use writematrix to create a single xlsx file with multiple sheets with name of the analysis file?

44 views (last 30 days)
I analyse multiple excel files in one folder.
After I want to create one summary.xlsx file with multiple sheets, where each sheet will have a name of analyzed file.
For example, if I have files: "Mary.xlsx", "Bob.xlsx" and "Tom.xlsx", I want to create an excel file "summary.xlsx" with sheets names "Mary", "Bob", "Tom".
My code below gives the error "Invalid sheet name. Name must be text and contain 1-31 characters, excluding :, \, /, ?, *, [, and ]."
And if the name of the files are long, like "Mary_grades_first year_sp1.xlsx", how can I choose from this name just a part of the name for sheet name, like "Mary_sp1"?
Thank you very much for your help!
% Get a list of all files in the folder with the desired file name pattern.
filePattern = fullfile(myFolder, '*.xlsx');
theFiles = dir(filePattern);
%Read each file in "data" and perform analysis
for k = 1 : length(theFiles)
baseFileName = theFiles(k).name;
fullFileName = fullfile(theFiles(k).folder, baseFileName);
%result{k} = cell(1,k);
fprintf(1, 'Now reading %s\n', fullFileName);
% Now do whatever you want
[~,Sheets] = xlsfinfo(fullFileName);
%reading multiple sheets of every xlsx file
for i = 1 : length(Sheets)
Name = Sheets{i};
data{k,i} = readmatrix(fullFileName,'Sheet',Name);
end;
%Extract desired information
T = cellfun(@(x){x(4,1:3)},data)';
Type = strings(size(T));
Type(cellfun(@(x)isequal(x, [1 0 0]), T)) = "E";
Type(cellfun(@(x)isequal(x, [0 1 0]), T)) = "M";
Type(cellfun(@(x)isequal(x, [0 0 1]), T)) = "P";
writematrix(Type,'summary.xlsx','Sheet', baseFileName,'Range','B2')
end

Accepted Answer

Image Analyst
Image Analyst on 17 Sep 2021
Edited: Image Analyst on 17 Sep 2021
You need to call writematrix() with a valid sheetname, not the filename. If you want the sheet name to be the filename you need to clip it to 31 characters
[~, thisSheetNameNoExt, ext] = fileparts(baseFileName);
lastIndex = min([length(thisSheetNameNoExt), 31]);
thisSheetName = thisSheetNameNoExt(1 : lastIndex);
writematrix(Type, 'summary.xlsx', 'Sheet', thisSheetName, 'Range', 'B2')
  5 Comments
Image Analyst
Image Analyst on 17 Sep 2021
I don't know what Type is. What does this say
size(Type)
Perhaps you need to extract just one row
rowVector = Type(rowNumber, :);
or colum vector
columnVector = Type(:, columnNumber);
and write that instead of the whole Type matrix.

Sign in to comment.

More Answers (1)

Simon Chan
Simon Chan on 17 Sep 2021
Use function fileparts to extract the name instead of the entire file name as the sheetname
  3 Comments
Simon Chan
Simon Chan on 17 Sep 2021
The baseFileName contains the entire file name, such as 'Mary.xlsx', but it is not supported as the sheet name.
The following code would extract the name 'Mary' only and you can use it as the sheet name
[~,name,~] = fileparts(baseFileName)

Sign in to comment.

Products


Release

R2021a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!