How to read multiple excel tabs each as a separate matrix

Dear All,

 I am given the task to find the maximum value within each tab of a number of excel files. I used xlsread only to find out that it took a day (25 hours) to read an excel file with 6600 tabs. These are my codes. Is there other ways to get this done faster?

myFolder = 'D:\study';

filePattern = fullfile(myFolder, '*.xlsx'); excelFiles = dir(filePattern); for k = 1:length(excelFiles) baseFileName = excelFiles(k).name; fullFileName = fullfile(myFolder, baseFileName); fprintf(1, 'Now reading %s\n', fullFileName);

[type,sheetname] = xlsfinfo(fullFileName); 
m=size(sheetname,2);
for i=5:m
 Sheet = char(sheetname(1,i)) ;
 alldata = xlsread(fullFileName, Sheet);

   [x,y]=max(alldata(:,2));
   output(i,:)=alldata(y,:);

end

Warm regards, Hallis

Answer 1

[abdulhallis aziz]

Omitted reading the header row in excel files by redefining data range. Got the program to read each sheet in an excel file and store the row with max value for column 2. Thanks to Fangjun and all. these are my codes:

[File,Folder] = uigetfile('.xlsx', 'Select Excel XLSx File to be Process');

excel_data_range = 'A2:B' ;% self define, data is column A and B, 1st row header omitted, exl = actxserver ('Excel.Application'); % open com server

exlFile = exl.Workbooks.Open(fullfile(Folder,File)); [type,sheetname] = xlsfinfo(fullfile(Folder,File)); sheet_max = numel(sheetname); %Determine no. of sheets in current .xlsx file

    for i=1:sheet_max
        exlSheet = exlFile.Sheets.Item(cell2mat(sheetname(1,i)));

        robj = exlSheet.Columns.End(4);       % Find the end of the column
        numrows = robj.row;                    % And determine what row it is
        dat_range2 = [excel_data_range num2str(numrows)]; % Read to the last row
        rngObj = exlSheet.Range(dat_range2);
        exlData2 = rngObj.Value;

        arr = cell2mat(exlData2);

        %finding max in array, y is value, x is row of occurance
        [x,y]=max(arr(:,2));
        output(i,:)=arr(y,:);
     end
end
toc

exl.Quit; exl.delete;

Answer 2

[Fangjun Jiang]

xlsread() has a lot if overhead. Follow this link to use Excel COM method.

http://www.mathworks.com/matlabcentral/answers/16299-is-there-instead-solution-of-xlsread

[abdulhallis aziz]

Many thanks Fangjun. I've looked into the code and barely understand what it does. Running the codes below, I am getting error

??? Error using ==> cell2mat at 47 All contents of the input cell array must be of the same data type.

How do I change the code from reading all tabs as a matrix to a single tab a matrix. These are the codes:

Folder=cd;
d = dir('*.xlsx');
N_File=numel(d);
e = actxserver ('Excel.Application');
h=waitbar(0,'Progress...');

for o = 1:N_File
waitbar(o/N_File,h);
ExcelWorkbook = e.workbooks.Open(fullfile(Folder,d(o).name));
Sheet=ExcelWorkbook.Sheets.Item(2);
Range=Sheet.UsedRange;
r=cell2mat(Range.Value);
ExcelWorkbook.Close;
end

e.Quit;
e.delete;

[Fangjun Jiang]

It is an error about using cell2mat(). It requires all the data to be the same data type. You can't have a mix of e.g. numerical and string data.

[hime skuld]

Why I cannot run this code properly. It looks like it cannot read my sheet in excel. after run, I found my N_file = 0 . I`m using matlab R2015b and excel 2013. somebody can tell me whats the problem.