AxtiveX Equivalent for xlsread

How can I duplicate the functionality of xlsread using ActiveX? That is, open a file, open a sheet in the file, and import all the data regardless of whether the data are 4 rows by 2 columns or 9001 rows by 1337 columns.

After some experimentation, the page below appeared to be helpful. However, the following code only looks at the first column. If cells A1 through A4 have data, then A5 is blank, numrows will be set to 4, even if cells B1 through B6 are all not blank. Even worse, numrows will be set to 4 even if cells A6 through A9001 are all not blank.

robj = exlSheet1.Columns.End(4);
numrows = robj.row;

The equivalent for number of columns is below. It suffers from a similar shortcoming.

cobj = exlSheet1.Rows.End(2);
numcols = cobj.column;

https://www.mathworks.com/help/matlab/matlab_external/example-reading-excel-spreadsheet-data.html

The same question was asked years ago in the link below. No satisfactory answer was given.

https://www.mathworks.com/matlabcentral/answers/218066-equivalent-activex-code-for-xlsread

@MathWorks: If xlsread, xlswrite, and xlsfinfo cleaned up after themselves and weren't so slow these sorts of questions would be less necessary.

[Guillaume]

Perhaps you should explain what your problem is with xlsread. I'm not aware of any issue it has cleaning up after itself. Actually, if you look at the code of xlsread it spends a lot of time ensuring that it cleans up after itself if something goes wrong (perhaps explaining your perceived slowness).

While it is indeed trivial to open a workbook and read data from a predefined range in a worksheet, finding out the actual used range is a lot more complicated. You could use the UsedRange property, it is unfortunately notoriously unreliable. A fully robust solution would requires a fair amount of code.

Note that because xlsread and co. do use activex to automate excel, excel itself can affect their speed. In particular, some excel add-ons can cause big slow downs.

[James]

To be fair to xlsread, it isn't slow, but xlswrite sure is. The problem is that the functions xlsread, xlswrite, and xlsfinfo leave open a link to the Excel file (an EXCEL.EXE process is still active; it terminates upon closure of MATLAB). Since the file is already "open", any macros like SOLVER or in-house add-ins that start automatically fail to do so when I open Excel if it isn't already running. Any file, not just the one that was read. It's annoying, and if I have later have several spreadsheets open it's a crap shoot as to which EXCEL.EXE to kill. The code to properly close a file using ActiveX is simple, so why xlsread et al don't is beyond me.

Because of this issue, I decided to check if there was an easy way using ActiveX. You suggest there isn't.

Answer 1

[Guillaume]

Matlab keeps the excel process alive precisely to speed up future calls to any xls*** function, as starting up the process is normally what takes the longest. On top of that, newest versions of excel also have their own machinery to try to keep instances alive and to a minimum (nothing matlab can do about that).

I'm not sure if you're saying it's a problem or not that the excel process stay alive. If you have some add-in that you want to be executed on each xl*** code, then yes it's not normally going to happen. On the other hand, add-ins are the biggest code of slow execution of xl*** and there's nothing matlab can do about that.

Note that you should be able to force the instance held by matlab to close with:

excel = matlab.io.internal.getExcelInstance;   %Undocumented. No guarantee it works with all matlab versions
excel.Quit

or by killing the process in the task manager (won't cause any harm, just a slower execution of the next xl*** call).

I'm also not clear which function you want to replace. As said, writing a robust xlsread is going to be some work because of the quirks of excel with the UsedRange property. However, writing a naive implementation that works for maybe 80% of files and fails abismally for the rest is very easy. I can provide an example if needed.

Similarly, a basic replacement of xlswrite would be trivial, as long as you're only concerned with writing numerical or textual data and not concerned with robustness. I would be skeptical however that it would be significantly faster than xlswrite. The most likely cause of slowdowns is excel itself.