How do I clear the contents of Excel by sheet?
108 次查看(过去 30 天)
显示 更早的评论
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 个评论
采纳的回答
the cyclist
2011-1-31
Would it meet your need to write an array of empty strings ("") instead?
0 个评论
更多回答(3 个)
Oleg Komarov
2011-1-31
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 个评论
Nagham Kabbara
2015-11-12
can i use this method to clear the content of specific cells in the sheet?
Image Analyst
2016-1-17
编辑:Image Analyst
2016-1-17
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
2015-4-8
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 个评论
Heng Sun
2016-1-17
编辑:Heng Sun
2016-1-17
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
2020-11-6
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 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!