MATLAB: Filter excel files by sheetnames

excelfilter filesfunctionmatlab functionopen excelparsingsort

Hi, I am trying to filter out excel files by checking whether it has a sheet named "Cooking_is_fun" within the excel.
This is the code I have been working on.
function hassheet = CheckForWorksheet(filepath, filelist, sheetname)
 hassheet = false(size(filelist));  
     excel = actxserver('Excel.Application');  
     cleanupobj = onCleanup(@() excel.Quit);   
     for fileidx = 1:numel(filelist)
                workbook = excel.Workbooks.Open(filepath{fileidx}, false, true);
                sheetnames = arrayfun(@(i) workbook.Sheets.Item(i).Name, 1:workbook.Sheets.Count, 'UniformOutput', false);
        if ismember(sheetname, sheetnames)
           hassheet(fileidx) = true;
        end
     end
end      
%now call the function, note that data is a pre-existing structure array that I have.
filepath = {data.AllExcelPath};
filelist = {data.AllExcel};
hassheet = CheckForWorksheet(SubFolder, filelist, 'Cooking_is_fun');
wantedExcel = filelist(hassheet);
However, this kept on giving me errors such as:
    Cell contents reference from a non-cell array object.
Error in Open_Excel_Files_All/CheckForWorksheet         
workbook = excel.Workbooks.Open(filepath{fileidx}, false, true);
Error in Open_Excel_Files_All 
hassheet = CheckForWorksheet(SubFolder, filelist,'Cooking_is_fun');
My goal is to:
be able to write the function and call the function in the same script, so that when I send the code to someone they don't have to run a function and a m-file separately.
be able to extract wanted excel files based on whether it contain a sheet named "Cooking_is_fun".
I am fairly new to matlab and am still confused with many basic concepts. Please advice me with any tips/guide.
Thank you very much for reading and any help will be greatly appreciated 🙂

Best Answer

Assuming that filepath is just a list of folders (no filename) and filelist is the corresponding list of files (just filenames), then the simplest thing might be to concatenate the two into just one list (with fullfile) and just pass that list to the check function. Therefore,
in the calling code:
fullpaths = fullfile(filepath, filelist);  %concatenate paths with filenames
hassheet = CheckForWorksheet(fullpaths, 'Cooking_is_fun');
%fullpaths(hassheet) is what you want to keep
the check function:
function hassheet = CheckExcelFilesForWorksheet(filelist, sheetname)
   %HASSHEET Check whether or not the given excel files have a sheet with the given name
   %filelist:  full paths of excel files (cell array of 1D char arrays / string array)
   %sheetname: name of sheet to find in excel files (1D char array / string)
   %hassheet:  array the same size as filelist, indicating whether or not the excel file has a sheet with sheetname (logical)
     hassheet = false(size(filelist));  %output 
     excel = actxserver('Excel.Application');  %start microsoft excel
     cleanupobj = onCleanup(@() excel.Quit);   %close excel when function returns or error occurs
     for fileidx = 1:numel(filelist)
        %open without updating link and as read only. read only ensure the file can be opened even if it's already in use:
        workbook = excel.Workbooks.Open(filelist{fileidx}, false, true);
        %get the list of worksheet name by iterating over the Sheets collection:
        sheetnames = arrayfun(@(i) workbook.Sheets.Item(i).Name, 1:workbook.Sheets.Count, 'UniformOutput', false);
        workbook.Close(false);  %close workbook, without saving
        if ismember(sheetname, sheetnames)
           hassheet(fileidx) = true;
        end
     end
  end
Note that you should really learn what a cell array is. Yes, your filelist and filepath are cell arrays. Cell arrays are containers like matrices except that matrices can only contain numbers while cell arrays can contain anything. Therefore, if you want to put a bunch of strings (char arrays) together, you put them in a cell array.
Related Question