Code covered by the BSD License  

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

4.83333
4.8 | 20 ratings Rate this file 71 Downloads (last 30 days) File Size: 7.24 KB File ID: #22365 Version: 1.3

Function for faster data transfer Matlab <--> Excel

by

Antonio (view profile)

 

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 (37)
17 Jul 2015 Stefan

Stefan (view profile)

I have downloaded the xlsread1.m file and implemented the code. Now I get an error in this line...

[data, text, rawData, customOutput] = xlsread1(xls_filename_complete, sheet_name, 'A1:C500');

Undefined function 'xlsread1' for input arguments of type 'char'.

What could be wrong?

Comment only
28 Apr 2015 Madelaine Santini  
25 Mar 2015 rfigueiredo

I tried to use the Sasquatch's suggestion but I wasn't able to put it working. It always has an error in 'evalin' which says that 'Excel' is an undefined function or variable. I have tried use many different forms but with no success.
I'm not sure either which of the following codes I should use(i think i don't need add-ins)
1)
Excel = actxserver ('Excel.Application');
File='C:\Users\aathar\Documents\MATLAB\EnzymeTest.xlsx';
if ~exist(File,'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
try
invoke(Excel.Workbooks,'Open',File);
catch
Excel.Quit;
Excel.delete;
clear Excel;
Excel = actxserver('Excel.Application');
invoke(Excel.Workbooks,'Open',File);
end

or
2)
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);

Thank you in advance!

Comment only
29 Jan 2015 Kevin Gaukel

Wait a minute - I think I know. I am not using 'Excel'. I am using 'hExcel', since my naming convention uses hXYZ for handle variable.

In other words, my program has the following:

hExcel = actxserver('Excel.Application')
...
hExcel.Quit;
hExcel.delete;
clear hExcel;

That's the danger of using a function with the required prerequisites - including variable names. Be very careful if you deviate in ANY way from the suggestions below.

BTW - caveat understood: This program ROCKS! I am using it to read multiple sections in the same Excel sheet. Before, it was taking a VERY long time as it was reading/closing/reading/closing/reading.... It took over 3 minutes. This last run to less than 15 seconds.

Comment only
26 Jan 2015 Sasquatch

Took me literally 2 hours to get both xlswrite1 and xlsread1 working but I realized I wasn't READING and I got it to work.

For those of you who are trying to follow along but get errors like "Excel Object does not exist" and the crazy "Interface.000208DB" error please follow along:

1) make sure you have added this file to your path. best to just put it in your Documents/MATLAB folder (i'm on windows)
2) be sure to include the code listed in the description before you call any read1/write1 files:

Excel = actxserver ('Excel.Application');
File='C:\Users\aathar\Documents\MATLAB\EnzymeTest.xlsx';
if ~exist(File,'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
try
invoke(Excel.Workbooks,'Open',File);
catch
Excel.Quit;
Excel.delete;
clear Excel;
Excel = actxserver('Excel.Application');
invoke(Excel.Workbooks,'Open',File);
end

3) before you end your function include this code: Excel.ActiveWorkbook.Save;
Excel.Quit
Excel.delete
clear Excel

4) use the tic/toc features to find out how much faster it is. For me it is at least 4x faster which will save me 30+ minutes

Thanks for the help everyone!

30 Nov 2014 Albert

Albert (view profile)

 
08 Aug 2014 KRUNAL

KRUNAL (view profile)

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.

Comment only
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

Claire (view profile)

 
21 Mar 2014 Haibo

Haibo (view profile)

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

Haibo (view profile)

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

26 Feb 2014 Bert

Bert (view profile)

 
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

Carlos (view profile)

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

Antonio (view profile)

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

Comment only
18 Oct 2013 Hugh

Hugh (view profile)

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

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

Comment only
02 Sep 2013 Fontys

Fontys (view profile)

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

Comment only
02 Sep 2013 Antonio

Antonio (view profile)

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

Comment only
02 Sep 2013 Fontys

Fontys (view profile)

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

Comment only
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

Kokalz (view profile)

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);

Comment only
14 Aug 2012 Kokalz

Kokalz (view profile)

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

Kokalz (view profile)

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

Comment only
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).

Comment only
28 Mar 2012 Antonio

Antonio (view profile)

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.

Comment only
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

Bob (view profile)

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?

Comment only
18 Mar 2012 Image Analyst

Image Analyst (view profile)

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.

Comment only
22 Jun 2011 Billy

Billy (view profile)

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

17 Jun 2011 Billy

Billy (view profile)

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

Comment only
20 Nov 2009 Danila

Danila (view profile)

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 ‹

lh ‹ (view profile)

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

Comment only
30 Jun 2009 Oleg Komarov

Oleg Komarov (view profile)

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 1.1

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 1.2

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

28 Mar 2012 1.3

Made changes in the file description.

Contact us