Remove Default Excel Sheets

著者 Sean de Wolski, May 1, 2015

10 ビュー (過去 30 日間) | 0 いいね | 1 コメント

Sean's pick this week is RemoveSheet123 by Noam Greenboim.

Working with MATLAB And Excel

Recently, I've been doing data analysis for colleagues who are not MATLAB users. However, most of them are familiar with Excel. Thus, I do the heavy lifting in MATLAB, such as aggregating millions of records from a database, or complex set analysis, and export the results to Excel so they can see it, manipulate it, or pivot it out in Excel.

To facilitate this, my mode of operation has been to take the data and stick it into a table and then use writetable to export to Excel. In general, writetable "does the right thing". If you have heterogenous data, it figures out how to write it, the variable names already exist so those are used as column headers, etc. I find this to be a simple workflow for most cases where I don't usually need the additional control xlswrite offers.

There are two things about this approach that have to be taken care of though. First, if you write to a named sheet, which I always do, you'll get a warning for adding a new sheet to the Excel file.

writetable(table(rand(5)),'SimpleExcelFile.xlsx','Sheet','Rand5')

Warning: Added specified
worksheet.

Fortunately, this can be turned off pretty easily.

warning('off','MATLAB:xlswrite:AddSheet');

I actually turn off this warning in my startup.m file so I never need to see this message except when blogging about it!

The other thing that happens with this approach is that the default Excel sheets are not used, remaining empty and taking up space or time when clicking on them to see what they contain. And this is where Noam's file comes in: It removes those default sheets, plain and simple.

% Download Some Data from Yahoo! (Requires Datafeed Toolbox)
y = yahoo;

% Fetch
ClosePrice = fetch(y,'SAM','Close','04/01/15','04/30/15');

% Convert to table and datetime with descriptive variable names
TClosePrice = table(datetime(ClosePrice(:,1),'ConvertFrom','datenum'),ClosePrice(:,2));
TClosePrice.Properties.VariableNames = {'Date','SamAdamsClosingPrice'};

% Write it
writetable(TClosePrice,'StockData.xlsx','Sheet','SamAdams')

Before:

Note: This only works on Windows because it relies on the Excel API.

% Remove sheets
RemoveSheet123('StockData.xlsx');

sheet #1 - deleted.

After:

Thanks Noam for saving me the effort of researching the API for Excel.

I did make one small change which was to comment out the catch block after the delete (line 59) so if it it fails, it does so silently. The reason for this is that I have a different Excel template that gives me just one sheet by default. I never have to delete all three, at least not on this machine. This change takes care of the fact that when the error is thrown, the document is not saved or exited. I'd recommend using an onCleanup object to prevent this state from being possible.