How do I clear the contents of Excel by sheet?
94 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
Jorge
am 31 Jan. 2011
Kommentiert: Carlos Beltrán Pérez
am 27 Sep. 2024 um 8:06
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 Kommentare
Akzeptierte Antwort
the cyclist
am 31 Jan. 2011
Would it meet your need to write an array of empty strings ("") instead?
0 Kommentare
Weitere Antworten (3)
Oleg Komarov
am 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 Kommentare
Nagham Kabbara
am 12 Nov. 2015
can i use this method to clear the content of specific cells in the sheet?
Image Analyst
am 17 Jan. 2016
Bearbeitet: Image Analyst
am 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
Václav Vesely
am 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)
1 Kommentar
Jeff
am 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 Kommentar
Heng Sun
am 17 Jan. 2016
Bearbeitet: Heng Sun
am 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.
Siehe auch
Kategorien
Mehr zu ActiveX finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!