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

Question

CheshireM 2021-9-17

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1455484-how-to-use-writematrix-to-create-a-single-xlsx-file-with-multiple-sheets-with-name-of-the-analysis-f

评论： CheshireM 2021-9-21

采纳的回答： Image Analyst

在 MATLAB Online 中打开

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 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Image Analyst 2021-9-17

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1455484-how-to-use-writematrix-to-create-a-single-xlsx-file-with-multiple-sheets-with-name-of-the-analysis-f#answer_789739

编辑：Image Analyst 2021-9-17

在 MATLAB Online 中打开

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')