{"id":5961,"date":"2015-05-01T09:00:39","date_gmt":"2015-05-01T13:00:39","guid":{"rendered":"https:\/\/blogs.mathworks.com\/pick\/?p=5961"},"modified":"2015-05-01T15:21:09","modified_gmt":"2015-05-01T19:21:09","slug":"remove-default-excel-sheets","status":"publish","type":"post","link":"https:\/\/blogs.mathworks.com\/pick\/2015\/05\/01\/remove-default-excel-sheets\/","title":{"rendered":"Remove Default Excel Sheets"},"content":{"rendered":"
\r\n \r\n

Sean<\/a>'s pick this week is RemoveSheet123<\/a> by Noam Greenboim<\/a>.\r\n <\/p>\r\n <\/introduction>\r\n

Working with MATLAB And Excel<\/a><\/h3>\r\n

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

To facilitate this, my mode of operation has been to take the data and stick it into a table<\/tt><\/a> and then use writetable<\/tt><\/a> to export to Excel. In general, writetable<\/tt> \"does the right thing\". If you have heterogenous data, it figures out how to write it, the variable names already exist\r\n 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\r\n additional control xlswrite<\/tt> offers.\r\n <\/p>\r\n

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

writetable(table(rand(5)),'SimpleExcelFile.xlsx'<\/span>,'Sheet'<\/span>,'Rand5'<\/span>)<\/pre>
Warning: Added specified\r\nworksheet. \r\n<\/pre>
Fortunately, this can be turned off pretty easily.<\/p>
warning('off'<\/span>,'MATLAB:xlswrite:AddSheet'<\/span>);<\/pre>
I actually turn off this warning in my startup.m<\/tt><\/a> file so I never need to see this message except when blogging about it!\r\n   <\/p>\r\n   
The other thing that happens with this approach is that the default Excel sheets are not used, remaining empty and taking\r\n      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\r\n      default sheets, plain and simple.\r\n   <\/p>
% Download Some Data from Yahoo! (Requires Datafeed Toolbox)<\/span>\r\ny = yahoo;\r\n\r\n% Fetch<\/span>\r\nClosePrice = fetch(y,'SAM'<\/span>,'Close'<\/span>,'04\/01\/15'<\/span>,'04\/30\/15'<\/span>);\r\n\r\n% Convert to table and datetime with descriptive variable names<\/span>\r\nTClosePrice = table(datetime(ClosePrice(:,1),'ConvertFrom'<\/span>,'datenum'<\/span>),ClosePrice(:,2));\r\nTClosePrice.Properties.VariableNames = {'Date'<\/span>,'SamAdamsClosingPrice'<\/span>};\r\n\r\n% Write it<\/span>\r\nwritetable(TClosePrice,'StockData.xlsx'<\/span>,'Sheet'<\/span>,'SamAdams'<\/span>)<\/pre>
Before:<\/p>\r\n   
 <\/p>\r\n   
Note:<\/b> This only works on Windows because it relies on the Excel API.\r\n   <\/p>
% Remove sheets<\/span>\r\nRemoveSheet123('StockData.xlsx'<\/span>);<\/pre>
\r\nsheet #1 - deleted.<\/pre>
After:<\/p>\r\n   
 <\/p>\r\n   
Thanks Noam for saving me the effort of researching the API for Excel.<\/p>\r\n   
I did make one small change which was to comment out the catch<\/b> 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\r\n      Excel template that gives me just one sheet by default.  I never have to delete all three, at least not on this machine. \r\n      This change takes care of the fact that when the error is thrown, the document is not saved or exited.  I'd recommend using\r\n      an onCleanup<\/tt><\/a> object to prevent this state from being possible.\r\n   <\/p>\r\n   
Comments<\/a><\/h3>\r\n   
How do you integrate MATLAB and Excel?  Any tips, tricks, or challenges, you'd like to share for some MathWorks swag?<\/p>\r\n   
Give it a try and let us know what you think here<\/a> or leave a comment<\/a> for Noam.\r\n   <\/p>