How do I clear the contents of Excel by sheet?
114 views (last 30 days)
Show older comments
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?
0 Comments
Accepted Answer
the cyclist
on 31 Jan 2011
Would it meet your need to write an array of empty strings ("") instead?
0 Comments
More Answers (3)
Oleg Komarov
on 31 Jan 2011
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
4 Comments
Nagham Kabbara
on 12 Nov 2015
can i use this method to clear the content of specific cells in the sheet?
Image Analyst
on 17 Jan 2016
Edited: Image Analyst
on 17 Jan 2016
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
Jeff
on 8 Apr 2015
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
1 Comment
Heng Sun
on 17 Jan 2016
Edited: Heng Sun
on 17 Jan 2016
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.
Václav Vesely
on 6 Nov 2020
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)
0 Comments
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!