How to use writematrix to create a single xlsx file with multiple sheets with name of the analysis file?
38 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
CheshireM
el 17 de Sept. de 2021
Comentada: CheshireM
el 21 de Sept. de 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 comentarios
Respuesta aceptada
Image Analyst
el 17 de Sept. de 2021
Editada: Image Analyst
el 17 de Sept. de 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 comentarios
Image Analyst
el 17 de Sept. de 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.
Más respuestas (1)
Simon Chan
el 17 de Sept. de 2021
3 comentarios
Simon Chan
el 17 de Sept. de 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)
Ver también
Categorías
Más información sobre Spreadsheets en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!