Code covered by the BSD License  

Highlights from
Function for faster data transfer Matlab <--> Excel

4.875

4.9 | 18 ratings Rate this file 87 Downloads (last 30 days) File Size: 7.24 KB File ID: #22365

Function for faster data transfer Matlab <--> Excel

by

 

09 Dec 2008 (Updated )

This modified version makes the original xlsread.m faster and also allows add-ins to be loaded.

| Watch this File

File Information
Description

The idea came from a modified version of the xlswrite.m (the xlswrite1.m) posted by Matt Swartz in this fle exchange area on March 21, 2006. This version of xlsread.m was easily accomplished by removing all instances of server open/close functions within the original file, as it was done with xlswrite1.m. Should one use xlswrite1.m in conjunction with the xlsread.m, the data transfer process still takes too long, in addition to yielding undesirable results due to the persistent server open/close.

In order to use it, one must place the following code within the program, as given by Matt Swartz. It opens/closes the activex server, load an add-in if any (by default when Excel opens as a COM server it does NOT load add-ins), and checks to see if the file already exists:

Excel = actxserver ('Excel.Application');
Excel.Workbooks.Open('C:\YourAddInFolder\AddInNameWithExtension');
Excel.Workbooks.Item('AddInNameWithExtension').RunAutoMacros(1);
File='C:\YourFileFolder\FileName';
if ~exist(File,'file')
    ExcelWorkbook = Excel.Workbooks.Add;
    ExcelWorkbook.SaveAs(File,1);
    ExcelWorkbook.Close(false);
end
Excel.Workbooks.Open(File);
 
Use xlsread1.m as needed. Then run the following code to close the activex server:

Excel.ActiveWorkbook.Save;
Excel.Quit
Excel.delete
clear Excel

The result was that processing speed of xlsread.m increased dramatically.

Acknowledgements

Xlswrite1 inspired this file.

This file inspired Mkxlsfunc: Easily Integrate Legacy Spreadsheet Tools Into Matlab.

MATLAB release MATLAB 7.6 (R2008a)
Tags for This File   Please login to tag files.
Please login to add a comment or rating.
Comments and Ratings (32)
30 Nov 2014 Albert  
08 Aug 2014 KRUNAL

So Antonio as per your reply on 28 Mar 2012 to Norbert's post, people who are reading files in a loop that is more than just couple of them, they will have to go through the same execution time as the regular xlsread? I am asking because I have a code that reads one row of data from each of the 150 files and writes into a single file of size 150 x 1.

23 Jul 2014 Michelle Ballard

Love the added speed, but I couldn't get it to run unless I inserted and switched Sebastian's suggestion. Worked perfect after that. Also, I couldn't get the Addins to work, but I didn't need it so I didn't troubleshoot that.

xlsread1 :

try
Excel = evalin('caller','Excel');
catch err
Excel = evalin('base','Excel');
end

13 Jul 2014 Claire  
21 Mar 2014 Haibo

Dear Antonio,thank you for your xlsread1 program, which works fastly. However, it could not work with parallel computing. Could you help me running the xlsread1 code with parallel computing? Many thanks to you.

17 Mar 2014 Haibo

Thanks Antonio and Matt. These codes save more than 50 times faster (one of my calculations)

26 Feb 2014 Bert  
17 Feb 2014 Leendert

Did a little tic - toc to see how much faster this script is. Using xlsread the average time to open all the sheets I needed was ~140s. with xlsread1 it takes 20s.!!!! Great submission

05 Feb 2014 Peter Verhoog  
05 Feb 2014 Peter Verhoog  
12 Dec 2013 Carlos

I am using xlsread1 and xlswrite1 inside a for loop with an excel sheet with enabled iterative calculation. What happens is that the iterative calculation is turn off by himself, what makes that I get wrong results. if I use the original xlsread and xlswrite of MATLAB works well, but is quite slow so I need to use xlsread1 and xlswrite1. Can someone help me with this problem?

21 Oct 2013 Antonio

Hugh, I tried to reproduce the error your reported but got no RPC server unavailability. I'm using Excel 2007 and Matlab R2013a...

18 Oct 2013 Hugh

I solved the evalin error. But got a new one:
Error: The RPC server is unavailable.

error('MATLAB:xlswrite:SelectDataRange',lasterr);

02 Sep 2013 Fontys

I thought I did that already. Appears I didn't. Stupid me.
Thanks Antonio

02 Sep 2013 Antonio

Fontys, maybe you could add the file to the Matlab path.

02 Sep 2013 Fontys

I'm probably really stupid for not knowing this, but...
I added the file xlsread1.m to C:\MATLAB\R2012b\toolbox\matlab\iofun (the folder where xlsread.m is also located), yet still when I'm trying to call on it using matlab I get the 'function not found' error:

>> xlsread1('Test.xlsx')
Undefined function 'xlsread1' for input arguments of type 'char'.

It does work when I put it in the 'My Documents' folder, but that kind of defies the purpose.

Can anyone answer this question or redirect me to a place where it is explained?
Thank you

07 Mar 2013 Tschortscho

Excellent, but it is not very useful with external tools.

I would like to redirect the original xlsread to xlsread1 by using this function handle, and make the external tool believe, that it is using the original file:

xlsread= @xlsread1;

(Background: I cannot change the source of the external tool, which is using the original xlsread function. This tool cannot set filename, actxserver and all this init stuff. But at least, I can redirect the function handle.)

Hence, I need to put all the init stuff within the xlsread1-function, as follows (Pseudo):

***************************
function xls1read (...)

if NOT_INIT or FileName_has changed
DO_ALL_THE_INIT_STUFF
end

(Rest of the xlsread1)
.....

***************************

I tried to do this with global variables (INIT, actserver, XLS_filenames), which I try to keep in the baseworkspace etc. But I was not successful, because I am not so familiar with m.
Can anyone help me?

THANKS

23 Jan 2013 Sébastien

Just as xlswrite1, this is an excellent way to speed up data exchange with Excel (6 times quicker in my case, with data imported from 7 sheets of an Excel file).
Nevertheless I had to provide two modification to run my MatlabR12b code on Win7-64x (with Excel 2010):

1/ provide the full file name from the file name 'File' got by a uigetfile in my main code :

Excel = actxserver ('Excel.Application');

if ~exist(File,'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',fullfile(pwd,File));

2/ change the evalin property to 'caller' in the Brandao added command at the beginning of the xlsread1 code :

Excel = evalin('caller','Excel'); % added command (Brandao 12/09/2008)

So Billy's suggestion should be taken into acount in a new version of xlsread1 :

try
Excel = evalin('base','Excel');
catch err
Excel = evalin('caller','Excel');
end

14 Aug 2012 Kokalz

Sorry, copied wrong code:

Excel = actxserver ('Excel.Application');
File='C:\YourFileFolder\FileName';
if ~exist(File,'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',File);

14 Aug 2012 Kokalz

Also, if you're not so worried about addins, using default xlswrite1 function might be a better idea:

Excel = actxserver ('Excel.Application');
Excel.Workbooks.Open('C:\YourAddInFolder\AddInNameWithExtension');
Excel.Workbooks.Item('AddInNameWithExtension').RunAutoMacros(1);
File='C:\YourFileFolder\FileName';
if ~exist(File,'file')
ExcelWorkbook = Excel.Workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
Excel.Workbooks.Open(File);

14 Aug 2012 Kokalz

Following Ivo's advice fixed the error messsage. A great code, it does reduce the reading time dramatically! Thank you!

28 Mar 2012 Norbert

Thank you. That's exactly how I adjusted the code for myself.
Except for that I'm using File all the time instead of File1, File2, etc. And I'm using the xlsread1 function as follows, because I need the headers as well:
[num1,txt1] = xlsread1(...)

So the thing is, that doesn't really save a lot of time compared to xlsread.

One xlsx-file has about 50x86400 cells (about 50MB).

28 Mar 2012 Antonio

Norbert is right. One can open several .xls files at the same time, but to access information from each one of them the mentioned code must be run, excpet for the first line "Excel = actxserver('Excel.Application');" since the server is already active. Then, one can simply include the name of the file in the call to xlsread1 as in the example below:

Excel = actxserver('Excel.Application');

File1 = 'C:\Users\Brandao\Documents\MATLAB\xlsread1\ExcelInterface.xls';
if ~exist(File1,'file')
ExcelWorkbook = Excel.Workbooks.Add;
ExcelWorkbook.SaveAs(File1,1);
ExcelWorkbook.Close(false);
end
Excel.Workbooks.Open(File1);
from_xls1 = xlsread1(File1,'Sheet1','B2:F7');

File2 = 'C:\Users\Brandao\Documents\MATLAB\xlsread1\ExcelInterface1.xls';
if ~exist(File2,'file')
ExcelWorkbook = Excel.Workbooks.Add;
ExcelWorkbook.SaveAs(File2,1);
ExcelWorkbook.Close(false);
end
Excel.Workbooks.Open(File2);
from_xls2 = xlsread1(File2,'Sheet1','B2:F7');

Excel.ActiveWorkbook.Save;
Excel.Quit
Excel.delete
clear Excel

One can even drop the instance

if ~exist(File,'file')
ExcelWorkbook = Excel.Workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end

if one is sure the file already exists.

I think my line in this file description reading "Run the xlsread1.m as many times as needed." is misleading. What I wanted to say was that once a file is open one can use and abuse of xlsread1.xls. Sorry for that!

Hope this helps.

28 Mar 2012 Norbert

If I want to import several files, do I have to run the following code every time after performing xlsread1.m to change the file location? Because if I specify the file location in "File='C:\YourFileFolder\FileName';", it always imports this file, no matter what file put in in the xlsread1 function. So running xlsread1 several times like mentioned in the first post, doesn't work.

Excel = actxserver ('Excel.Application');
Excel.Workbooks.Open('C:\YourAddInFolder\AddInNameWithExtension');
Excel.Workbooks.Item('AddInNameWithExtension').RunAutoMacros(1);
File='C:\YourFileFolder\FileName';
if ~exist(File,'file')
ExcelWorkbook = Excel.Workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
Excel.Workbooks.Open(File);

22 Mar 2012 Bob

I get the same error:
??? Error using ==> evalin
Undefined function or variable 'Excel'.

I modified xlsread1 to use the try catch. Is this going to fixed officially?

18 Mar 2012 Image Analyst

You should update the title of his submission to xlsread1 instead of xlsread. Some people may not want to download it because they fear that it may overwrite the built-in xlsread. You already have called it xlsread1 in the file itself, you just need to update the title.

22 Jun 2011 Billy

Please ignore my previous comment. This would work!
try
Excel = evalin('base','Excel');
catch err
Excel = evalin('caller','Excel');
end

17 Jun 2011 Billy

How about we change the first line of code to
Excel = eval('Excel');

20 Nov 2009 Danila

Thank you, Ivo. Your suggestion helped me - I also use xlsread1 from another function.

15 Oct 2009 Ivo Torre

lh>, I had the same error. My problem was using xlsread1 from within an m function...
The first line of code in xlsread1 is
Excel = evalin('base','Excel');
that tries to look for the object Excel in the base workspace.
But, since I instantiated the excel object in my m function, here's the error we met.
You should modify it in
Excel = evalin('caller','Excel');
that tries to look fo the Excel object in the workspace of the caller, where the object exists.

Hope it works for you

28 Jul 2009 lh ‹

>> xlsread1('testsheet.xls')
??? Error using ==> evalin
Undefined function or variable 'Excel'.

30 Jun 2009 Oleg Komarov

I would leave in the function:
<pre class = "code">

Excel = actxserver ('Excel.Application');
Excel.Workbooks.Open('C:\YourAddInFolder\AddInNameWithExtension');
Excel.Workbooks.Item('AddInNameWithExtension').RunAutoMacros(1);
File='C:\YourFileFolder\FileName';
if ~exist(File,'file')
ExcelWorkbook = Excel.Workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
Excel.Workbooks.Open(File);
</pre>

Instead when it checks:
if nargin < 1 || isempty(file)
error('MATLAB:xlsread:FileName','Filename must be specified.');
end

I would substitute the error with:
Excel.ActiveWorkbook.Save;
Excel.Quit
Excel.delete

This way all u have to do is:
xlsread("firstfile"......)
xlsread("secondfile"......)
...and so on until
xlsread() % close teh connection!

That would code saving and clean.

Updates
08 Jan 2009

Two additional lines were included to allow add-ins to be installed since by default when Excel opens as a COM server it does NOT load add-ins.

18 Mar 2012

Title updated to avoid possible overwriting of built-in xlsread.m. Thanks to Image Analyst's comment.

28 Mar 2012

Made changes in the file description.

Contact us