MATLAB Answers

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

조회 수: 9(최근 30일)
CheshireM 2021년 9월 17일
댓글: CheshireM 2021년 9월 21일 0:25
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);
%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')

채택된 답변

Image Analyst
Image Analyst 2021년 9월 17일
편집: Image Analyst 2021년 9월 17일
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

댓글을 달려면 로그인하십시오.

추가 답변(1개)

Simon Chan
Simon Chan 2021년 9월 17일
Use function fileparts to extract the name instead of the entire file name as the sheetname
  댓글 수: 3

댓글을 달려면 로그인하십시오.




Community Treasure Hunt

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

Start Hunting!

Translated by