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

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

Answer 1

[Image Analyst]

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

[CheshireM]

That's great!! Thank you so much!

This code has one more problem: it creates excel file with sheet names I want, but they are all identical: instead of writing the analysis of each file to different sheet, it writes analysis of all files in every sheet. Would you be able to help me, please, with this too? Why does it happen, if my code inside the cycle for each file?

[Image Analyst]

Take the semicolon off the thisSheetName and see what the sheet names are. If they are different, they will go to different sheets. If they are the same, you need to figure out how to get the correct and different sheet names. I don't really know what your filenames are, what the sheet names in those files are, and what sheet names you want for the output workbook. Please explain that.

[CheshireM]

Everything with the sheet names works beautifully!

Problem is with writing the data.

Now I have a file summary.xlsx with sheet names: "Mary", "Bob" and "Tom".

My goal is to have data for Mary on the sheet "Mary" in a column B (starting B2), data for Bob on the sheet "Bob" in a column B(starting also with B2) and etc. But I have a data for Mary, Bob and Tom on the sheet Mary in the colums B, C, D, as well as on the sheet Bob in the columns B, C, D, and the sheet Tom also has the data for all three students.

Thank you!

[Image Analyst]

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.

[CheshireM]

Thank you, @Image Analyst

I got it!

Answer 2

[Simon Chan]

Use function fileparts to extract the name instead of the entire file name as the sheetname

[CheshireM]

Isn't baseFileName is already extracting name?

baseFileName = theFiles(k).name;

[Simon Chan]

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)

[CheshireM]

Thank you@Simon Chan