How to use writematrix to create a single xlsx file with multiple sheets with name of the analysis file?
39 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
CheshireM
il 17 Set 2021
Commentato: CheshireM
il 21 Set 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 Commenti
Risposta accettata
Image Analyst
il 17 Set 2021
Modificato: Image Analyst
il 17 Set 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 Commenti
Image Analyst
il 17 Set 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.
Più risposte (1)
Simon Chan
il 17 Set 2021
3 Commenti
Simon Chan
il 17 Set 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)
Vedere anche
Categorie
Scopri di più su Spreadsheets in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!