matlab several excel files

Question

I have 4 folders each consisting of several (>10) excel spreadsheets. How is it possible to extract the last worksheet of each spreadsheet into matlab. So that in matlab, the data will be such that a cell would represent a folder and within that cell you would have the contents of each folder i.e. the data from the last worksheet of each spreadsheet?

cheers

Answer 1

Use XLSFINFO function. It determines that the file is Excel spreadsheet, will give you the list of all worksheets in it, and also can detect the excel file format.

[status,sheets,format] = xlsfinfo(filename);
if ~isempty(status)
    lastsheet = sheets{end};
end

Once you know the last sheet name you can use XLSREAD to get the data from it.

[num,txt,raw] = xlsread(filename,lastsheet);

Use DIR to collect the file names in your folders.

---

UPDATE

Check this script:

folders = {'test1','test2'};
DATA = cell(numel(folders),1);

for fo = 1:numel(folders)
    files = dir(folders{fo}); %# get array fo all files in a folder
    files([files(:).isdir]) = []; %# remove directories from the structure array
    DATA{fo} = cell(numel(files),1);
    for fi = 1:numel(files)
        filename = fullfile(folders{fo},files(fi).name);
        disp(filename)
        [status,sheets] = xlsfinfo(filename);
        if ~isempty(status)
            lastsheet = sheets{end};
            [num,txt,raw] = xlsread(filename,lastsheet);
            DATA{fo}{fi} = num; %# or txt, or raw
        end
    end
end