xlsfinfo question

I have the following script for gathering information about Excel files under investigation:

clear all
pathName='F:\path'; 
TopFolder = pathName; 
dirListing = dir(TopFolder); 
dirListing=dirListing(3:end);%first 2 are just pointers
%obtain the name of each of the excel file:
for i=1:length(dirListing);
    SubFolder{i}=dirListing(i,1).name;
    SubFolderPath{i} = fullfile(pathName, dirListing(i,1).name);
    ExcelFile{i}=dir(fullfile(SubFolderPath{i},'*.xls'));
    for j=1:length(ExcelFile{1,i});
        ExcelFileName{1,i}{j,1}=ExcelFile{1,i}(j,1).name;
    end
end

%obtain the name of the worksheets
for i=1:length(ExcelFileName);
    for k=1:length(ExcelFileName{1,i});
        [status{1,i}{k,1},sheets{1,i}{k,1},format{1,i}{k,1}] = xlsfinfo((fullfile(pathName,SubFolder{1,i},ExcelFileName{1,i}{k,1})));
    end
end

This provides the name of each of the worksheets in the spreadsheet. However, I only want to import the data from one of the worksheets in each of the spreadsheets. Therefore is there a way for matlab to display a list of the worksheet names. So, say that we have 20 worksheet names, could matlab show a message saying something along the lines of 'Select Worksheet' and then have each of the worksheet names shown in 'sheets{1,i}{k,1}' being assigned a number (i.e. from 1-20) then by typing 20 in the command window it would store the name of the last worksheet which could then be used by xlsread to import the data?

Please ask if I'm not clear on what I'm trying to achieve.

Answer 1

[Matt Tearle]

A little bit of clarification needed... From your code it appears that you have m directories; in any given directory there are n files; in any given file there are p worksheets, correct? (Sounds like the old "St Ives" nursery rhyme...) Now, at what level do you want to ask the user for the worksheet name? For every file in every directory (so m*n times in total)? Or will the files have the same worksheets, so that you need only ask at each directory (just m times because all n files have the same p worksheets), or even just once overall (because all m*n files have the same p worksheets)? This will affect your second set of for-loops.

Anyway, that detail aside, the neatest solution is probably to gather the worksheet names into a cell array of strings, then use the menu function:

k = menu('Choose a worksheet',sheetnames);

This will pop up a dinky pushbutton menu and return, in the variable k, the number of the pushbutton that the user clicked.

EDIT TO ADD: Based on your comment, then, I'd do something like this

for i=1:length(ExcelFileName);
  [~,sheetnames] = xlsfinfo((fullfile(pathName,SubFolder{1,i},ExcelFileName{1,i}{1,1})));
  sheetnum = menu('Choose a worksheet',sheetnames);
  for k=1:length(ExcelFileName{1,i});
     % Read in worksheet sheetnum from kth file
  end
end

Gah. Words are indeed terrible for this, sometimes! Maybe you mean that it should ask the user for the sheet of each file (but the sheet names are the same)? If so, slight modification: just move the menu command inside the loop over k.

[Richard]

correct for the first bit. With regards to asking for the worksheet name, all worksheets (p) in the files (n) are named the same, so when matlab loops over one of the files (n) it would then ideally ask for the name of the worksheet (p) which would be the same for all of n, and then do the same when looping over the second file (n2).

This must sound really confusing to someone who can't see my matlab screen, but I can't seem to write it any clearer.

I'll attempt to use the menu function and see how I get on.