How do I clear the contents of Excel by sheet?

I have an Excel file with 5 sheets. I need to clear the contents of the sheets 2, 3, 4 and 5.

I tried it with an empty cell array but I get error.

Any ideas?

Answer 1

[the cyclist]

Would it meet your need to write an array of empty strings ("") instead?

Answer 2

[Oleg Komarov]

A slightly more elaborate way with ActiveX:

% Name of the excel file
filename = 'C:\Users\Oleg\Desktop\myExcelFile.xlsx';
% Retrieve sheet names 
[~, sheetNames] = xlsfinfo(filename);

% Open Excel as a COM Automation server
Excel = actxserver('Excel.Application');

% Open Excel workbook
Workbook = Excel.Workbooks.Open(filename);

% Clear the content of the sheets (from the second onwards)
cellfun(@(x) Excel.ActiveWorkBook.Sheets.Item(x).Cells.Clear, sheetNames(2:end));

% Now save/close/quit/delete
Workbook.Save;
Excel.Workbook.Close;
invoke(Excel, 'Quit');
delete(Excel)

Oleg

[Thulasi Durai Durai Samy]

Hello

I tried to use this command by matlab error

cellfun(@(x) Excel.ActiveWorkBook.Sheets.Item(x).Cells.Clear, SheetNames(1))
??? Undefined function or method 'SheetNames' for input arguments of type 'double'.

Can you pls help me

[Oleg Komarov]

I am using sheetNames, note the lower case.

[Nagham Kabbara]

can i use this method to clear the content of specific cells in the sheet?

[Image Analyst]

Nagham, yes, see this snippet to clear cells in a certain range:

%-------------------------------------------------------------
% Clears/erases cells in the current worksheet in the specified range.
% Example call:
% Excel_utils.ClearCells(Excel, 'A1..C5');
function ClearCells(Excel, cellReference)
  try
    % Select the range
    Excel.Range(cellReference).Select;
    % Clear the cell contents.
    Excel.Selection.Clear;
    % Put "cursor" or active cell at A1, the upper left cell.
    Excel.Range('A1').Select;
  catch ME
    errorMessage = sprintf('Error in function ClearCells.\n\nError Message:\n%s', ME.message);
    fprintf('%s\n', errorMessage);
    uiwait(warndlg(errorMessage));
  end % from ClearCells
  return;
end

Answer 3

[Václav Vesely]

If I want to overwrite contetnt of an excel sheet 'cnt' I do it this way:

oldTable = readtable(configOutPAth,'Sheet',cnt);
oldTable = array2table(nan(size(oldTable)),'VariableNames',oldTable.Properties.VariableNames);
writetable(oldTable,configOutPAth,'Sheet',cnt)

writetable(newTable,configOutPAth,'Sheet',cnt)

[Carlos]

Excellent, thanks!

Answer 4

[Jeff]

Here's another way. It may not be the most efficient but it requires very little code.

Just read all the data in from a sheet, write NaN to all cells that were read, and write those cells back to your sheet. Do this for all sheets that you want.

Filename='C:\Users\Jeff\Desktop\Spreadsheet.xlsx';
for SheetNum=2:5
     [N, T, Raw]=xlsread(Filename, SheetNum);
     [Raw{:, :}]=deal(NaN);
     xlswrite(Filename, Raw, SheetNum);
end

[Heng Sun]

I feel this is the simplest way. The accepted answer require knowing the size of the sheet in advance, which may not be practical.

Later edit: Well, it is not so great. If I have a sheet with column A empty, this method would leave the last column untouched. The reason is that Matlab function xlsread returned Raw does not include empty columns.