MATLAB: How to clear excel content of an old file before saving the news results to it

clear contentsexcelMATLAB and Simulink Student Suite

I am new to Matlab and just wrote small script to calculate the cooling cycle for my device. I would like to know how can I clear contents of the excel file i created before posting new results to it when its over written.

For ex, when I run my code for 1500 temp degree, I get two columns from A1:B50

now when I run again with temp of 1200, I get the results in the same excel but overwritten with range A1:B40… however, i will still see B40:B50 from the old results which is very confusing.

so I would like to know of a away that before i save the results, clear all the contents i had previously in the created excel so I can fill it with my new results.

here is my code

Code:

clear all; close all; clc;
% this program calculates the cooling cycle temps for 
 prompt = 'What''s your temperature? ';
 x = input(prompt);
  sheetnumber = 'Enter sheet number where results will be stored.. ';
  sheetnum = input(sheetnumber);
  filename = 'temp_increaments.xlsx';
 y = x/30; % this is to calculate the stepsize based on 30 mins interval
 
 z = round(y);  % rounding the answer to cloeset integer so i can ..
 % preallocate bcoz preallocation needs integers.
 difference= zeros(1 ,z); % preallocated to save memory
for i = 2:z    % *in this sense, 50 steps means every almost 35 seconds..
                   % .., i decrease another 5% (cooling increament).  
 x(i) = x(i-1)*0.95;   % here is my equation, i use...
% the output of last first calc as the input of second cal
% and so on for 30 steps. 
difference(i) = x(i-1) - x(i);
end
format longG
 disp(x)
 y = x.';  % to save results as column
 change= sort(difference, 'descend'); % sorted the results from high temp..
 % to low temp so i can see how much i need to change every cooling cycle
 disp(change)
 z= change.';   % same concept as saving y
%------------------------------------------------------------------------
  sheet = sheetnum;
  xlRange = 'A1';
  xlswrite(filename,[y, z], sheet,xlRange)

Best Answer

What I do is to simply delete the file and then write the new one:

recycle on % Send to recycle bin instead of permanently deleting.
delete(filename); % Delete (send to recycle bin).
% Now write new file with none of the old/existing stuff in there.
xlswrite(filename, ...............

Related Solutions

MATLAB: How to rename a sheet in Excel using the COM interface in MATLAB

You can rename an Excel sheet using the "Name" property of a sheet object. For example the following renames the first sheet of an Excel file:

filename = 'C:\SomeExcelFile.xls';
% Open Excel Automation server
Excel = actxserver('Excel.Application');
% Make Excel visible
Excel.Visible=1;
% Open Excel file
Workbook = Excel.Workbooks.Open(filename);
% Get the list of sheets in the workbook
Sheets = Excel.ActiveWorkbook.Sheets;
% Rename the first sheet
Sheets.Item(1).Name = 'This is sheet 1';

The following code then saves the Workbook, quits Excel and removes the COM server:

% Save the file
Workbook.Save();
% Quit Excel, remove the COM server and delete the related objects
Excel.Quit();
Excel.delete();
clear Excel;
clear Workbook;
clear Sheets;

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

Best Answer

Related Solutions

MATLAB: How to rename a sheet in Excel using the COM interface in MATLAB

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

Related Question