xlswrite in for loop

I wrote a genetic algorithm code about Travelling Salesman Problem. I want to run the program 100 times (each run includes 5000 iterations). In each run, the program yields the optimal route w.r.t its total distance. I want the computer to write this best solution (it is the first row o a matrix) into an excel file, each solution added in different rows in excel (i.e. the result of first run in row 1 in spreadsheet, the result of second run in second row in spreadsheet and so on). I used xlsread('experiments', L(1,:), 'exp1', 'A(counter):Q(counter)') but dinna work and gave object returned error code: 0x800A03EC. Thanks.

Answer 1

[Image Analyst]

You should not run xlswrite() 100 times, unless you are prepared to wait a long time - a very long time. It will have to launch Excel and shutdown Excel each time you call xlswrite(). It will be so much faster to use ActiveX, and xlswrite1() from the File Exchange. I've posted ActiveX demos here before. Let me know if you want me to post it again here.

[Image Analyst]

% 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;

[Ali]

thanks image analyst you are right it takes too long. is xlswrite1 function faster? does it work in matlab7. and how can I code it?

[Image Analyst]

My code above will be much, much faster. You need to download xlswrite1 using the link in the comments at the top. Then see how I open up Excel using ActiveX. It's not hard:

Excel = actxserver('Excel.Application');

From then on it's just like xlswrite() except you use xlswrite1() instead.

Then you need to shut down Excel when you're all done with it using the lines (already in my code):

% Then run the following code to close the activex server:
invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit;
Excel.delete;
clear Excel;

[Ali]

I tried it but I couldnt achive:(.I think I will keep on the slow way. Thanks anyway.

[Image Analyst]

Tried what? Did you download xlswrite1()? If so then all you really need are the 5 lines in my last comment plus the call to xlswrite1(), which you already have. That doesn't seem too difficult for a smart engineer like you.

[Ali]

tried what you write but there is an error in xlswrite1 function while running in my code. I will work on that soon. For now I solve the problem using workspace, I copy the related matrix from workspace. by the way you are smarter I think:) take care

[Image Analyst]

Let me know what the error is. If you're going to be using Excel with MATLAB, then using ActiveX is something you should learn at some point. The basics aren't hard but sometimes if you want to do something advanced it takes a while to find the name of the right method to do what you want to do.

[Ali]

hi image analyst, The error is Error using==>xlswrite1 at 142 Error:Object returned error code 0x800A03EC

Error in ==> xlswrite1 at 142 error('MATLAB:xlswrite:SelectDatarange',lasterr);

Error in ==> ullyesses16ali at 244 xlswrite1('experiments',minc,'exp1_TS_PMX_SWAP',sprintf('B%d', counter+1));

Answer 2

[Iain]

Define the top-left corner of where you want to write data and ensure it is a string.

xlswrite('filename.xls',L(1,:),'sheet name here',['A' num2str(counter)]);

[Ali]

thanks lian but it did not work. I wrote directly xlswrite('experiments',L(1,:),'exp1',['A':num2str(counter)]) but it yields only one row, and I think it's the best solution of final run. I waited for 100 solutions(number of runs).

[Ali]

uuuuups got it friend:) xlswrite('experiments',L(1,:),'exp1',sprintf('A%d',counter)); it works I think:) thanks a lot anyway lian.

[Iain]

If you really did put a colon between 'A' and num2str(counter) it would do that.

['A' num2str(5)] concatenates 'A' with '5' to get 'A5'

[Ali]

thanks a lot again friend:)