How to use writematrix to create a single xlsx file with multiple sheets with name of the analysis file?
41 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
CheshireM
am 17 Sep. 2021
Kommentiert: CheshireM
am 21 Sep. 2021
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
0 Kommentare
Akzeptierte Antwort
Image Analyst
am 17 Sep. 2021
Bearbeitet: Image Analyst
am 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 Kommentare
Image Analyst
am 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.
Weitere Antworten (1)
Simon Chan
am 17 Sep. 2021
3 Kommentare
Simon Chan
am 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)
Siehe auch
Kategorien
Mehr zu Spreadsheets finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!