MATLAB: Excel – Clearing Sheet Content

sheet

Hello I have created fle name 'MAT.XLS'

I need to clear the content of 'sheet1' in MAT.XLS fle through matlab, I used ActiveXSERVER command

Excel = actxserver('Excel.Application');
Workbook = Excel.Workbooks.Open(strcat(pwd,'\MAT.xls'));
% 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;
Sheets = Excel.ActiveWorkBook.Sheets;
[type, sheet_names] = xlsfinfo(strcat(pwd,'\MAT.xls'));
current_sheet = get(Sheets, 'Item', 1);
invoke  (current_sheet, 'Activate');

How do I clear the contents of the sheet1

Can somebody help me.

Best Answer

Here's a snippet of code I used to clear the comments.

thisSheet = Excel.ActiveSheet;
% Get a reference to the cell at this row in column A.
cellReference = sprintf('%s%d', columnLetterCode, startingRow);
theCell = thisSheet.Range(cellReference);
% You need to clear any existing comment or else the AddComment method will throw an exception.
theCell.ClearComments();

I imagine it would be

theCell.Clear();

or something similar. Give it a try.

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 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 rename a sheet in Excel using the COM interface in MATLAB

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

Related Question