How do I clear the contents of Excel by sheet?

37 次查看（过去 30 天）
Jorge
Jorge 2011-1-31
评论： Carlos 2024-9-27
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?

请先登录，再回答此问题。

采纳的回答

the cyclist
the cyclist 2011-1-31
Would it meet your need to write an array of empty strings ("") instead?

更多回答（3 个）

Oleg Komarov
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 个评论
显示 2更早的评论
Nagham Kabbara
Nagham Kabbara 2015-11-12
can i use this method to clear the content of specific cells in the sheet?
Image Analyst
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

请先登录，再进行评论。

Václav Vesely
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)
Jeff
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
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.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by