MATLAB: How can i compile many excel (xls) files in one

excelsheet

I have 100 excel (.xls) files, everyone with 1 sheet , but i need to compile all these files in only one excel file with 100 sheets .

Can anyone help me? Please

Thank you 🙂

(sorry if my english isn't perfect)

Best Answer

Luis

Depends on what version of Excel you have. With Excel 2003 and earlier you are limited to 32 worksheets. It's more for later versions but I'm not sure of the limit. Anyway to do this quickly you need to use ActiveX programming. You open Excel once, open an output workbook once, and then open each input workbook and transfer the data to a new worksheet. Then save the output workbook and Quit Excel. I've attached a demo that you can easily modify.

If that's way over your head and you don't want to learn it, then you can call xlsread and xlswrite a hundred times, but it will take forever.

See the FAQ for how to loop over the sequence of file names: http://matlab.wikia.com/wiki/FAQ#How_can_I_process_a_sequence_of_files.3F

Related Solutions

MATLAB: How to control a variable number of sheets in excel files

Yasmine, it can be done with ActiveX. While more complicated, this will be much, much faster than using xlsread() if you have more than 1 file since you will need to launch and shutdown Excel only once, not 10 or more times (once for every single file that you have).

See the FAQ for how to loop over the workbook files, and then inside that loop, with a single specific workbook, you can use ActiveX to loop over the worksheets in that workbook. You just do something like this:

excelObject = actxserver('Excel.Application');
filePattern = fullfile(myFolder, '*.xls');
xlsFiles = dir(filePattern);
for k = 1:length(xlsFiles)
  baseFileName = xlsFiles(k).name;
  fullFileName = fullfile(myFolder, baseFileName);
  fprintf(1, 'Now reading %s\n', fullFileName);
  excelWorkbook = excelObject.workbooks.Open(fullFileName);
  worksheets = excelObject.sheets;
  numberOfSheets = worksheets.Count;
  for sheetIndex  = 1 : numberOfSheets 
    % Do whatever you want to do.
  end
end
excelObject.Quit;

Here's a utility where I use ActiveX to loop over worksheets, deleting empty ones:

% --------------------------------------------------------------------
% DeleteEmptyExcelSheets: deletes all empty sheets in the active workbook.
% This function loops 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)
try
  %   excelObject = actxserver('Excel.Application');
  %   excelWorkbook = excelObject.workbooks.Open(fileName);
  
  % Run Yair's program http://www.mathworks.com/matlabcentral/fileexchange/17935-uiinspect-display-methods-properties-callbacks-of-an-object
  % to see what methods and properties the Excel object has.
  %   uiinspect(excelObject);
  
  worksheets = excelObject.sheets;
  sheetIndex = 1;
  sheetIndex2 = 1;
  initialNumberOfSheets = worksheets.Count;
  % Prevent beeps from sounding if we try to delete a non-empty worksheet.
  excelObject.EnableSound = false;
  % Tell it to not ask you for confirmation to delete the sheet(s).
  excelObject.DisplayAlerts = false;
  
  % Loop over all sheets
  while sheetIndex2 <= initialNumberOfSheets
    % Saves the current number of sheets in the workbook.
    preDeleteSheetCount = 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(sheetIndex>1,initialNumberOfSheets-sheetIndex2>0)
      % worksheets.Item(sheetIndex).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(sheetIndex).UsedRange.Count == 1
        worksheets.Item(sheetIndex).Delete;
      end
    end
    % Check whether the number of sheets has changed. If this is not the
    % case the counter "sheetIndex" is increased by one.
    postDeleteSheetCount = worksheets.count;
    if preDeleteSheetCount == postDeleteSheetCount;
      % If this sheet was not empty, and wasn't deleted, move on to the next sheet.
      sheetIndex = sheetIndex + 1;
    else
      % sheetIndex stays the same.  It's not incremented because the current sheet got deleted,
      % and all the other sheets shift down in their sheet number.  So now sheetIndex will
      % point to the same number which is the next sheet in line for checking.
    end
    sheetIndex2 = sheetIndex2 + 1; % prevent endless loop...
  end
  excelObject.EnableSound = true;
catch ME
  errorMessage = sprintf('Error in function DeleteEmptyExcelSheets.\n\nError Message:\n%s', ME.message);
  fprintf('%s\n', errorMessage);
  WarnUser(errorMessage);
end
return; % from DeleteEmptyExcelSheets
end % of DeleteEmptyExcelSheets

MATLAB: How to delete worksheets in the Excel file using MATLAB

The following code runs through an Excel file and deletes sheets based on user input. Note that the code will produce an error if you delete all the sheets in an Excel file because Excel requires valid files to have at least one worksheet.

The code requires the user to have Microsoft Excel and uses ActiveX.

% This example operates on an Excel file called test.xls in the
% current directory. The test.xls file has 3 worksheets by default. This file can be 
% created by creating a new Excel file via Microsoft Excel and saving it as test.xls.
% Get information returned by XLSINFO on the workbook
XL_file = [pwd '\test.xls'];
[type, sheet_names] = xlsfinfo(XL_file);
% First open Excel as a COM Automation server
Excel = actxserver('Excel.Application'); 
% Make the application invisible
set(Excel, 'Visible', 0);
% Make excel not display alerts
set(Excel,'DisplayAlerts',0);
% Get a handle to Excel's Workbooks
Workbooks = Excel.Workbooks; 
% Open an Excel Workbook and activate it
Workbook=Workbooks.Open(XL_file);
% Get the sheets in the active Workbook
Sheets = Excel.ActiveWorkBook.Sheets;
index_adjust = 0;
% Cycle through the sheets and delete them based on user input
for i = 1:max(size(sheet_names))
      inp_prompt = sprintf('Do you want to delete the Worksheet called %s [y/n]?',sheet_names{i});
      user_inp = lower(input(inp_prompt,'s'));
      switch user_inp
          case{'y'}
              current_sheet = get(Sheets, 'Item', (i-index_adjust));
              invoke(current_sheet, 'Delete')
              out_string = sprintf('Worksheet called %s deleted',sheet_names{i});
              index_adjust = index_adjust +1;
          otherwise
              out_string = sprintf('Worksheet called %s ***NOT*** deleted',sheet_names{i});
      end
      disp(out_string);
      disp(' ');
end
% Now save the workbook
Workbook.Save;
% Close the workbook
Workbooks.Close;
% Quit Excel
invoke(Excel, 'Quit');
% Delete the handle to the ActiveX Object
delete(Excel);

Note that one must exercise caution when using ActiveX to modify Excel as this could lead to potentially serious errors if used incorrectly. An error in ActiveX instructions can not only crash Excel but also adversely affect MATLAB. Therefore, we recommend that critical ActiveX code executed through MATLAB should be used by those who understand ActiveX to the degree that the programmer can undo any particular ActiveX instructions.

Best Answer

Related Solutions

MATLAB: How to control a variable number of sheets in excel files

MATLAB: How to delete worksheets in the Excel file using MATLAB

Related Question