MATLAB Features: Persistent Excel ActiveX (DCOM) for xlsread() and xlswrite() R2015b

xlsread() and everything that calls it, such as readtable(), is terribly slow, especially when you have a boatload of Excel files to process. The reason behind it is that xlsread() closes that DCOM handle (which closes the Excel COM session) after it finishes, and restart Excel (DCOM) again when you call xlsread() again to load another spreadsheet file.

That means there’s a lot of opening and closing of the Excel application if you process multiple spreadsheets. The better strategy, which is covered extensively in MATLAB’s File Exchange (FEX), is to have your program open one Excel handle, and process all the spreadsheets within the same DCOM handle before closing it.

This strategy is quite overwhelming for a beginner, and even if you use FEX entries, you still cannot get around the fact that you have to know there’s a handle that manages the Excel session and remember to close it after you are done with it. Nothing beats having xlsread() do it automatically for you.

Starting with R2015b, the Excel DCOM handle called by xlsread() is now persistent: that after you make the first call to xlsread(), Excel.exe will stay in the memory unless you explicitly clear persistent variables or exit MATLAB, so you can reuse them every time xlsread() or xlswrite() is called. Finally!

The code itself is pretty slick. You can find it in ‘matlab.io.internal.getExcelInstance()’. Well, I guess it’s not hard to come up with it, but I guess in TMW, they must have a heated debate about whether it’s a good idea to keep Excel around (taking up resources) when you are done with it. With the computation power required to run R2015b, an extra Excel.exe lying around should be insignificant. Good call!

 

433 total views, 1 views today

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz