How to read several excel files (30 files) without repeating the program?

Hi Everyone;

I just wrote a program to read from one excel file and then calculate something by using the column data. Now I want to extend it to read 30 excel files and do exactly the same thing (I mean calculate same column in each files). The question is I have to copy all the program and change the name of each line which is too difficult so I wonder to know if another way that I can examine. Could you please help me?

Answer 1

[Image Analyst]

Ara: Here is an example of how to use ActiveX with Excel (get xlswrite1 from the FileExchange, then save code below as ExcelDemo.m and run):

% Demo macro to write numerical arrays and cell arrays
% to two different worksheets in an Excel workbook file.
% Uses xlswrite1, available from the File Exchange
% http://www.mathworks.com/matlabcentral/fileexchange/10465-xlswrite1
function ExcelDemo
clc;
close all;
clear all;
fullFileName = GetXLFileName();
if isempty(fullFileName)
  % User clicked Cancel.
  return;
end
Excel = actxserver('Excel.Application');   

% Prepare proper filename extension.
% Get the Excel version because if it's version 11 (Excel 2003) the file extension should be .xls,
% but if it's 12.0 (Excel 2007) then we'll need to use an extension of .xlsx to avoid nag messages.
excelVersion = str2double(Excel.Version);
if excelVersion < 12
  excelExtension = '.xls';
else
  excelExtension = '.xlsx';
end

% Determine the proper format to save the files in.  It depends on the extension (Excel version).
switch excelExtension
  case '.xls' %xlExcel8 or xlWorkbookNormal
     xlFormat = -4143;
  case '.xlsb' %xlExcel12
     xlFormat = 50;
  case '.xlsx' %xlOpenXMLWorkbook
     xlFormat = 51;
  case '.xlsm' %xlOpenXMLWorkbookMacroEnabled 
     xlFormat = 52;
  otherwise
     xlFormat = -4143;
end

if ~exist(fullFileName, 'file')
  message = sprintf('I am going to create Excel workbook:\n\n%s\n\nClick OK to continue.\nClick Exit to exit this function', fullFileName);
  button = questdlg(message, 'Creating new workbook', 'OK', 'Exit', 'OK');
  drawnow;  % Refresh screen to get rid of dialog box remnants.
  if strcmpi(button, 'Exit')
    return;
  end
  % Add a new workbook.
  ExcelWorkbook = Excel.workbooks.Add;
  % Save this workbook we just created.
  ExcelWorkbook.SaveAs(fullFileName, xlFormat);
  ExcelWorkbook.Close(false);
else
  % Delete the existing file.
  delete(fullFileName);
end

% Open up the workbook named in the variable fullFileName.
invoke(Excel.Workbooks,'Open', fullFileName);
Excel.visible = true;

% Create some sample data.
myData = magic(20);
myOtherData = rand(10);

% Then run the new xlswrite1 function as many times as needed or in a loop 
% (for example xlswrite1(fullFileName, yourArrayName, XL_CellLocation). 
% IMPORTANT NOTE: the Excel object variable MUST exist in the routine that calls xlswrite1()
% and it MUST be named "Excel" EXACTLY because xlswrite1() has this line it it:
%   Excel = evalin('caller', 'Excel');
xlswrite1(fullFileName, myData, 'mySheetName', 'B2');
ca = {'Column Header 1', 'Column Header 2'};
xlswrite1(fullFileName, ca, 'mySheetName', 'B1');
ca = {'Row Header 1'; 'Row Header 2'};
xlswrite1(fullFileName, ca, 'mySheetName', 'A2');
xlswrite1(fullFileName, myOtherData, 'myOtherSheetName', 'B2');

% Delete all empty sheets in the active workbook.
DeleteEmptyExcelSheets(Excel);

% For fun, add comments to cells A1:A12 on sheet #1.
worksheets = Excel.sheets;
thisSheet = get(worksheets, 'Item', 1); 
for k = 1 : 12
  myComment = sprintf('Comment for cell A%d', k);
  cellReference = sprintf('A%d', k);
  theCell = thisSheet.Range(cellReference);
  theCell.AddComment(myComment);
end

% Then run the following code to close the activex server:
invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit;
Excel.delete;
clear Excel;
message = sprintf('Done!\nThis Excel workbook has been created:\n%s', fullFileName);
msgbox(message);
% End of main function: ExcelDemo.m -----------------------------

%--------------------------------------------------------------------
% Gets the name of the workbook from the user.
function fullExcelFileName = GetXLFileName()
  fullExcelFileName = [];  % Default.
  % Ask user for a filename.
  FilterSpec = {'*.xls', 'Excel workbooks (*.xls)'; '*.*', 'All Files (*.*)'};
  DialogTitle = 'Save workbook file name';
  % Get the default filename.  Make sure it's in the folder where this m-file lives.
  % (If they run this file but the cd is another folder then pwd will show that folder, not this one.
  thisFile = mfilename('fullpath');
  [thisFolder, baseFileName, ext] = fileparts(thisFile);
  DefaultName = sprintf('%s/%s.xls', thisFolder, baseFileName);
  [fileName, specifiedFolder] = uiputfile(FilterSpec, DialogTitle, DefaultName);
  if fileName == 0
    % User clicked Cancel.
    return;
  end
  % Parse what they actually specified.
  [folder, baseFileName, ext] = fileparts(fileName);
  % Create the full filename, making sure it has a xls filename.
  fullExcelFileName = fullfile(specifiedFolder, [baseFileName '.xls']);

% --------------------------------------------------------------------
% DeleteEmptyExcelSheets: deletes all empty sheets in the active workbook.
% This function looped through all sheets and deletes those sheets that are
% empty. Can be used to clean a newly created xls-file after all results
% have been saved in it.
function DeleteEmptyExcelSheets(excelObject)
%   excelObject = actxserver('Excel.Application');
%   excelWorkbook = excelObject.workbooks.Open(fileName);
  worksheets = excelObject.sheets;
  sheetIdx = 1;
  sheetIdx2 = 1;
  numSheets = worksheets.Count;
  % Prevent beeps from sounding if we try to delete a non-empty worksheet.
  excelObject.EnableSound = false;

  % Loop over all sheets
  while sheetIdx2 <= numSheets
    % Saves the current number of sheets in the workbook
    temp = worksheets.count;
    % Check whether the current worksheet is the last one. As there always
    % need to be at least one worksheet in an xls-file the last sheet must
    % not be deleted.
    if or(sheetIdx>1,numSheets-sheetIdx2>0)
      % worksheets.Item(sheetIdx).UsedRange.Count is the number of used cells.
      % This will be 1 for an empty sheet.  It may also be one for certain other
      % cases but in those cases, it will beep and not actually delete the sheet.
      if worksheets.Item(sheetIdx).UsedRange.Count == 1
        worksheets.Item(sheetIdx).Delete;
      end
    end
    % Check whether the number of sheets has changed. If this is not the
    % case the counter "sheetIdx" is increased by one.
    if temp == worksheets.count;
      sheetIdx = sheetIdx + 1;
    end
    sheetIdx2 = sheetIdx2 + 1; % prevent endless loop...
  end
  excelObject.EnableSound = true;
  return;

[Ara]

Sorry for the late response and thank you for the useful code. I did exactly what you said but I get this error massage. Could you please help me regarding this?

Error in ==> ExcelDemo at 64 xlswrite1(fullFileName, myData, 'mySheetName', 'B2');

??? Error: File: xlswrite1.m Line: 1 Column: 42 Unexpected MATLAB expression.

[Image Analyst]

I'm not sure you did exactly as I said. Did you read where I said "get xlswrite1 from the FileExchange". I even gave you the link:

% Uses xlswrite1, available from the File Exchange
% http://www.mathworks.com/matlabcentral/fileexchange/10465-xlswrite1

Looks like you overlooked those parts. If you do have xlswrite1, and it's on your search path, then do this

which -all xlswrite1

and write back.

[Ara]

No, I did not overlook that part and I saved xlswrite1 from this link on my search path and also saved the code as ExcelDemo.m but when I run it I get that error. In both code I have to give my path and my folder and my data name, right?

http://www.mathworks.com/matlabcentral/fileexchange/10465-xlswrite1/content/xlswrite1.m

[Image Analyst]

Then just set a breakpoint there and type "which -all xlswrite1" and examine all the variables you're sending into it so they all look like they should. I know it works for me - only on my work computer actually because here at home I only have the free "Excel starter edition" which doesn't allow ActiveX communication.

[Ara]

In demo.m, this line should be changed by my path and folder, right? When I try to type my path and folder name, the error comes out. My path is H:\Year 2009-2010 (More than 200 excel that save as look like this (1613_8_6.xls) and all are in one sheet about 46 columns and 5736 rows. I think my problem is just to match the path and folders with xlswrite 1 and demo.m

xlswrite1(fullFileName, myData, 'mySheetName', 'B2');

[Image Analyst]

What line is that? I don't see any line in there where you need to replace it with your path.

[Ara]

Thank you very much:). Works perfect.

Answer 2

[Walter Roberson]

http://matlab.wikia.com/wiki/FAQ#How_can_I_process_a_sequence_of_files.3F

Answer 3

[Mark Whirdy]

Hi Ara, what does "change the name of each line" mean? Can you provide some sample code to help would-be assisters understand the problem more clearly? Generally, the more effort you put into the question, the better the answer you'll get.

My guess is that you want to loop through a cell-array of excel filenames, opening each file in turn and reading in its contents ... is this right?

myPath = 'C:\';
myFileArray = {'myfile1.xlsx';'myfile30.xlsx'};
exl = actxGetRunningServer('Excel.Application');
exlWkbks = exl.Workbooks;
for i = 1:size(myFileArray,1)
      tempWkbook = exlWkbks.Open(fullfile(myPath,myFileArray{i,:}));
      tempSheet = tempWkbook.ActiveSheet;
      tempArray = tempSheet.Range('A1:A100').Value;
      tempWkbook.Close;      

        % myVector = cell2mat(tempArray)*2; % PERFORM SOME OPERATION HERE

end

another link may help with excel activex properties/methods http://www.mathworks.co.uk/matlabcentral/answers/42710-problem-with-xlsread-bizarre-results

besides this, google "matlab excel actxserver"

[Ara]

Thank you, both. Right on you Mark. I provide the code to make clear by means each line. I have 30 different excel files that I want to calculate this for all of them and show in one figure.

data=xlsread('1615_0.xls');

data_filterr=find(data(:,25)>60);

data_filtered=data(data_filterr,:);

elev_cutof20=find(data_filtered(:,6)>30);

data_cutoff15=data_filtered(elev_cutof20,:);

r=data_cutoff15(:,2);

time=(r./3600)-(24*0);

s4r=data_cutoff15(:,8);

s4cor=data_cutoff15(:,9);

s4=sqrt(s4r.^2-s4cor.^2);

plot (time,s4);

[Mark Whirdy]

So putting this snippet inside a loop (as per my post above) doesn't provide a solution? Populate a matrix "s4" then iteratively (as below)with your required data from each file, and plot outside the loop.

What am I missing here?

n = 50; % how many rows in '1615_0.xls', do all files contain the same rows?
s4 = NaN(n,30);
for i = 1:...
   s4(:,i) = sqrt(s4r.^2-s4cor.^2); % "i" increments on each loope (i.e. each file)
end
plot(s4);

[Ara]

No all files contain different row and same column(27) but I just want to plot the specific row so would not brings about any problem. I try to put it but can not read this line, Invalid syntax error?!?

data = xlsread{'1608_1.xls','A120:AF3723'; '1616_5.xls', 'A120:AF3723'};

[Mark Whirdy]

I think we have a language barrier problem. Can you start with the code I have posted above and then adapt it, I can't write this for you as I don't have your excel files.

In terms of this line: ...

data = xlsread{'1608_1.xls','A120:AF3723'; '1616_5.xls', 'A120:AF3723'};

1) you need round brackets, not chain brackets

2) have you looked at xlsread helpfile?, as your arguments do not conform to the required inputs [xlsread(filename,sheet,range)], you're referencing several files in the same xlsread.

[Ara]

I used round brackets, but still doesn't work. And I also read the link you send me and am trying to adapt my code with the code you wrote me but I couldn't. Could you please look at my program? I should have changed Excel.Application?

??? Error using ==> actxGetRunningServer

The server 'Excel.Application' is not running on this system.

Answer 4

[Azzi Abdelmalek]

fic=struct2cell(dir('yourfolder/*.xls'))
file=fic(1,:)
for k=1:numel(file)
  data{k}=xlsread(file{k})
end

[Image Analyst]

This will work but take a very long time since you'd have to launch and shutdown Excel 30 times. Better to use ActiveX.

[Ara]

Thank you. But this code make Matlab keep busy. How can I stop it?

[Image Analyst]

I know - just like I said. You can try control-c to stop the running m-file. Click in the command window first and then try typing control-c a bunch of times. Otherwise you may have to use control-shift-Esc and kill Excel or MATLAB via the Process list. Next time use ActiveX if you have more than about two Excel workbooks that you need to work with - don't use xlsread. Let me know if you need a demo.

[Ara]

Thank you, now Matlab stop the previous work fortunately without close it all program. Could you please guide me? As Walter helped me, I wrote this but now I wanted to extend it for one month means 30 excel files want to read just in a specific time. It means 30 times for 30 different files should do this below and then plot it in one figure. And yes please I need a demo.

data=xlsread('1608_1.xls','A120:AF3723');

data_filterr=find(data(:,25)>60);

data_filtered=data(data_filterr,:);

elev_cutof20=find(data_filtered(:,6)>30);

data_cutoff15=data_filtered(elev_cutof20,:);

r=data_cutoff15(:,2);

time=(r./3600)-(24*0);

s4r=data_cutoff15(:,8);

s4cor=data_cutoff15(:,9);

s4=sqrt(s4r.^2-s4cor.^2);

prn=data_cutoff15(:,3);

numsat = max(prn);

cmap = jet(numsat); %use any handy colormap

idx = s4_0 >= 0.2;

scatter(longi(idx), lati(idx), s4_0(idx)*3500, cmap(prn(idx),:) );

hold on; plot (longi(idx), lati(idx), '.k');

[Image Analyst]

Not sure if you were replying to me when you asked for a demo. I offered an ActiveX demo but then you seem insistent on using xlsread which will take an eternity. Mark Whirdy gave you some ActiveX code that you can use instead of xlsread.

[Ara]

No, I am not insistent on using xlsread. I just do not know how to use ActiveX code and how to adapt it with my code that was why I asked you the demo. Sorry, if any misunderstood happens.