How do I clear the contents of Excel by sheet?
121 vues (au cours des 30 derniers jours)
Afficher commentaires plus anciens
Jorge
le 31 Jan 2011
Réponse apportée : Václav Vesely
le 6 Nov 2020
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 commentaires
Réponse acceptée
the cyclist
le 31 Jan 2011
Would it meet your need to write an array of empty strings ("") instead?
0 commentaires
Plus de réponses (3)
Oleg Komarov
le 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 commentaires
Nagham Kabbara
le 12 Nov 2015
can i use this method to clear the content of specific cells in the sheet?
Image Analyst
le 17 Jan 2016
Modifié(e) : Image Analyst
le 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
le 8 Avr 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 commentaire
Heng Sun
le 17 Jan 2016
Modifié(e) : Heng Sun
le 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
le 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 commentaires
Voir également
Catégories
En savoir plus sur Spreadsheets dans Help Center et File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!