Effacer les filtres
Effacer les filtres

Filter excel files by sheetnames

5 vues (au cours des 30 derniers jours)
chlor thanks
chlor thanks le 11 Juil 2016
Modifié(e) : chlor thanks le 19 Juil 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 commentaires
Guillaume
Guillaume le 13 Juil 2016
Note that I've just realised that there's a minor bug (I never close the workbooks I open) in the original code I wrote for CheckForWorksheet. Fixed in the original discussion.
chlor thanks
chlor thanks le 13 Juil 2016
Modifié(e) : chlor thanks le 13 Juil 2016
Ok! Thank you so much for your generous help and reaching out to me again!

Connectez-vous pour commenter.

Réponse acceptée

Guillaume
Guillaume le 13 Juil 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 commentaires
Guillaume
Guillaume le 15 Juil 2016
Following on from Image Analyst's point that new versions of xlsfinfo keep a persistent excel instance. The above code could be simplified to:
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
for fileidx = 1:numel(filelist)
[~, sheetnames] = xlsfinfo(filelist{fileidx});
if ismember(sheetname, sheetnames)
hassheet(fileidx) = true;
end
end
end
chlor thanks
chlor thanks le 19 Juil 2016
Modifié(e) : chlor thanks le 19 Juil 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!

Connectez-vous pour commenter.

Plus de réponses (2)

Walter Roberson
Walter Roberson le 12 Juil 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 commentaires
Guillaume
Guillaume le 12 Juil 2016
In general, you cannot call a function without input arguments just to test it, regardless of the version of matlab. If you try to call the built-in sin function without an input, you're going to get the same error.
Some functions may work without any input (e.g. spy) but they're in the minority and their behaviour is most likely different from when inputs are supplied.
chlor thanks
chlor thanks le 12 Juil 2016
Modifié(e) : chlor thanks le 12 Juil 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!

Connectez-vous pour commenter.


Image Analyst
Image Analyst le 12 Juil 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 commentaires
chlor thanks
chlor thanks le 14 Juil 2016
Modifié(e) : chlor thanks le 14 Juil 2016
My excel is 2011, so it should not have the same problem? But you are right, if this code is send to a computer that has updated excel, it will have some trouble.
Image Analyst
Image Analyst le 14 Juil 2016
It's the version of MATLAB you have that matters, not the version of Excel.

Connectez-vous pour commenter.

Catégories

En savoir plus sur Data Import from MATLAB dans Help Center et File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by