Filter excel files by sheetnames

7 views (last 30 days)
chlor thanks
chlor thanks on 11 Jul 2016
Edited: chlor thanks on 19 Jul 2016
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 :)
  2 Comments
chlor thanks
chlor thanks on 13 Jul 2016
Ok! Thank you so much for your generous help and reaching out to me again!

Sign in to comment.

Accepted Answer

Guillaume
Guillaume on 13 Jul 2016
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.
  2 Comments
chlor thanks
chlor thanks on 19 Jul 2016
Thank you Guillaume, I believe this will work perfectly except that I keep on getting some error saying that the object invoked has disconnected from its clients.
Alternatively I am trying xlsfinfo() now, it turns out this works very slow and not nearly as good as yours... Hope that I can figure out how to fix the disconnection error soon. After all I appreciate all your help very much!! Thank you!

Sign in to comment.

More Answers (2)

Walter Roberson
Walter Roberson on 12 Jul 2016
You construct a variable named filepath as a cell array, but what you pass as the first parameter in your call is SubFolder which we do not know the contents of.
  8 Comments
chlor thanks
chlor thanks on 12 Jul 2016
I must have been confused with when I was able to run a function from fileexchange to add it to path... so to answer your previous question: from my very limited understanding of the different inputs that I have, "data" is a structure array. "filepath" is a cell(? or char? I don't really know the different between the two..) array that contains all the full excel files' path, which I generated from
filepath = {data.AllExcelPath};
"filelist" is another cell(?) array that contains all the excel file names, which I generated from
filelist = {data.AllExcel};
For a better explanation than what I can do, I attached a picture of what they look like in my workspace, thanks!

Sign in to comment.


Image Analyst
Image Analyst on 12 Jul 2016
Rather than using your own CheckForWorksheet() function, you might use the built in function for doing that. From the help:
xlsfinfo
Determine if file contains Microsoft Excel spreadsheet
[status,sheets] = xlsfinfo(filename) additionally returns the name of each spreadsheet in the file.
  6 Comments
Image Analyst
Image Analyst on 14 Jul 2016
It's the version of MATLAB you have that matters, not the version of Excel.

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!

Translated by