How can I delete the default sheets Sheet1, Sheet2 and Sheet3 in Excel, when I use XLSWRITE?

조회 수: 243(최근 30일)
MathWorks Support Team
MathWorks Support Team 2009년 8월 12일
답변: Pruthvi G 2020년 4월 13일
I would like to delete the default sheets Sheet1, Sheet2 and Sheet3 in Excel, when I use XLSWRITE.

채택된 답변

MathWorks Support Team
MathWorks Support Team 2009년 8월 12일
You can delete the sheets that get created automatically by Excel when you use XLSWRITE with a new file name by using ActiveX functionality (Windows only).
Example:
excelFileName = 'Test.xls';
excelFilePath = pwd; % Current working directory.
sheetName = 'Sheet'; % EN: Sheet, DE: Tabelle, etc. (Lang. dependent)
% Open Excel file.
objExcel = actxserver('Excel.Application');
objExcel.Workbooks.Open(fullfile(excelFilePath, excelFileName)); % Full path is necessary!
% Delete sheets.
try
% Throws an error if the sheets do not exist.
objExcel.ActiveWorkbook.Worksheets.Item([sheetName '1']).Delete;
objExcel.ActiveWorkbook.Worksheets.Item([sheetName '2']).Delete;
objExcel.ActiveWorkbook.Worksheets.Item([sheetName '3']).Delete;
catch
; % Do nothing.
end
% Save, close and clean up.
objExcel.ActiveWorkbook.Save;
objExcel.ActiveWorkbook.Close;
objExcel.Quit;
objExcel.delete;
In this way, you will be able to delete the default sheets from the workbook. But as the workbook should at least contain one worksheet, you will not be able to delete the last remaining sheet. You can work around this by creating your own sheet first and then deleting the default sheets.
  댓글 수: 1
Pappu Murthy
Pappu Murthy 2016년 5월 6일
It seemed to have worked but not very cleanly. I wanted a simpler solution but may be not possible. Not sure at all. Here are the messages I get.
Warning: Added specified worksheet. > In xlswrite>activate_sheet (line 292) In xlswrite/ExecuteWrite (line 258) In xlswrite (line 213) In ProofSimGUI>Save_Results_Callback (line 387) In gui_mainfcn (line 95) In ProofSimGUI (line 42) In matlab.graphics.internal.figfile.FigFile/read>@(hObject,eventdata)ProofSimGUI('Save_Results_Callback',hObject,eventdata,guidata(hObject)) Warning: Added specified worksheet. > In xlswrite>activate_sheet (line 292) In xlswrite/ExecuteWrite (line 258) In xlswrite (line 213) In ProofSimGUI>Save_Results_Callback (line 388) In gui_mainfcn (line 95) In ProofSimGUI (line 42) In matlab.graphics.internal.figfile.FigFile/read>@(hObject,eventdata)ProofSimGUI('Save_Results_Callback',hObject,eventdata,guidata(hObject)) >>

댓글을 달려면 로그인하십시오.

추가 답변(2개)

Matthias
Matthias 2016년 6월 7일
편집: Matthias 2016년 6월 7일
EDIT: this was too quick, although working on a first glance this is not a solution - an excel process keeps being opened :-( Nontheless this should not be too hard to cleanly implement (at least not by the mathworks ^^)
The option I thought would work is a modification of xlswrite.m. Replacing the last block in the function by this
% Export data to selected region.
set(Excel.selection,'Value',A);
% MRZ: delte default sheets in freshly created .xls
if bCreated
[~, sheets] = xlsfinfo(file);
sheetNames2remove = setdiff(sheets,sheet);
for i = 1:numel(sheetNames2remove)
ExcelWorkbook.Worksheets.Item(sheetNames2remove{i}).Delete;
end
end
ExcelWorkbook.Save
did the trick for me (almost). Should work in basic excel mode and is language independent :-) Btw.: I'm still using R2012a - no idea if this still works in later (or earlier) releases...

Pruthvi G
Pruthvi G 2020년 4월 13일
%%**********************************************************************************************************
% Name : Delete_sheets_Excel
% Author : Pruthvi Raj G :: (9677066394 :: www.prudhvy.com )
% Version : Version 1.0 - 2011b Compactible
% Description : Deleting Excel Sheets required after writing data to Excel.
% Input : File_Name with path included , Sheet_name / Sheet_names.
% Date : 22-April-2019
%
% Examples : Delete_sheets_Excel('D:\Pruthvi\Test_file.xls',{'Sheet1','Sheet2'}) %To delete 2 sheets
% Delete_sheets_Excel('D:\Pruthvi\Test_file.xls','Sheet1')
% Delete_sheets_Excel('D:\Pruthvi\Test_file.xls') % Takes 'Sheet1' as Default
%************************************************************************************************************
Use the Below Lines of Code ::
Delete_sheets_Excel('D:\Pruthvi\Test_file.xls',{'Sheet1','Sheet2'})

Community Treasure Hunt

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

Start Hunting!

Translated by