File Exchange

image thumbnail

Function for faster data transfer Matlab <--> Excel

version 1.3.0.0 (7.24 KB) by Antonio
This modified version makes the original xlsread.m faster and also allows add-ins to be loaded.

6 Downloads

Updated 28 Mar 2012

View Version History

View License

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.

Cite As

Antonio (2020). Function for faster data transfer Matlab <--> Excel (https://www.mathworks.com/matlabcentral/fileexchange/22365-function-for-faster-data-transfer-matlab-excel), MATLAB Central File Exchange. Retrieved .

Comments and Ratings (48)

Marios

The function does not work for me. When I run it once it works fine. But when I run it in a loop and the variable "file" (see below) changes each time then at the second loop I have this message error:
Error: Call was rejected by callee.

Error in xlsread1 (line 239)
Excel.DisplayAlerts = 0;

If it does help my code is:
For i=1...
Excel = actxserver ('Excel.Application');
file=[main_directory, '\frequency-times\', char(exp_no(i)),'.xlsx'];
if ~exist(file,'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(file,1);
ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',file);

filename=file;
eval(['x_coordinate_end' '=xlsread1(filename,spreedsheet_no,Range_x_coordinate_end);']);

invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit
Excel.delete
clear Excel

End of for

Farhad Gharagheizi

This program doesn't work!
You better explain how this program work using a very simple example.
Most of the users prefer having a straitforward method to use.

I blame Mathworks because they already know that "xlsread" is a pain in MATLAB and wastes users' time and in some cases it fails and they should either push Microsoft to modify Excel or find another way to speed up "xlsread".

MQ

Tolga ERKEN

Thank you for everything. It really helps me a lot. I have an excel have which have 480 sheets and I read 2800 x 2 values from everysheet. Also thanks for everybody who commands here. I solve my problem by reading them.

Abhishek kumar

Any idea how to solve this issue..

handles.EDM_Log_File_Name = ['Sample_log_file_' datestr(clock,30) '_fast.csv'];

Excel = actxserver ('Excel.Application');
Excel.Workbooks.Open('C:\Users\op181\Documents\Abhishek\Matlab CODE\MatlabCode\Bluetooth_Vaibhav\Data log\',[handles.EDM_Log_File_Name,'.csv']);
Excel.Workbooks.Item([handles.EDM_Log_File_Name,'.csv']).RunAutoMacros(1);
File='C:\Users\op181\Documents\Abhishek\Matlab CODE\MatlabCode\Bluetooth_Vaibhav\Data log\',[handles.EDM_Log_File_Name,'.csv'];
if ~exist(File,'file')
ExcelWorkbook = Excel.Workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
Excel.Workbooks.Open(File);

**I am getting this ERROR**

??? Invoke Error, Dispatch Exception:
Source: Microsoft Excel
Description: Open method of Workbooks class failed
Help File: xlmain11.chm
Help Context ID: 0

Error in ==> NIL_Tool_Version>pushbutton1_Callback at 383
Excel.Workbooks.Open('C:\Users\op181\Documents\Abhishek\Matlab
CODE\MatlabCode\Bluetooth_Vaibhav\Data log\',[handles.EDM_Log_File_Name,'.csv']);

Error in ==> gui_mainfcn at 96
feval(varargin{:});

Error in ==> NIL_Tool_Version at 42
gui_mainfcn(gui_State, varargin{:});

Error in ==> @(hObject,eventdata)NIL_Tool_Version('pushbutton1_Callback',hObject,eventdata,guidata(hObject))

Siyuan Lin

Firstly, the code improves the speed of reading from Excel document dramatically when handling several sheets. But for a single sheet data, the speed is slower than the xlsread command.

Secondly, do remember to add the code from the description in your actual program.

santosh kushwaha

Andy Park

Make sure to add the code from the description into your actual program.

%% Copy + Paste BEFORE first instance of xlsread1()

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

%% Your code here

a = 3;
b = 4;
c = sqrt(a^2+b^2);

%% Copy + Paste AFTER last instance of xlsread1()

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

Clay Harden

Like Vinay and others, I'm having trouble getting past the first line. I've read the other user comments about alternatives, and have replaced the first line, as follows:

WAS
Excel = evalin('base','Excel');

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

but to no avail. Is there something else I should do?

Vinay Kandakur

Vinay Kandakur

I used this code it worked fine and processing speed also increased but after one day it is giving error as:

Undefined function or variable 'Excel'.

Error in ==> xlsread1 at 108
Excel = evalin('base','Excel');

Please any one clarify my doubt

thanks in advance.

Stefan

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?

Madelaine Santini

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!

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.

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!

Albert

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.

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

Clare

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.

Haibo

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

Bert

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

Peter Verhoog

Peter Verhoog

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?

Antonio

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

Hugh

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

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

Fontys

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

Antonio

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

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

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

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

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

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

Kokalz

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

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

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.

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

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?

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.

Billy

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

Billy

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

Danila

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

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

lh ‹

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

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.

MATLAB Release Compatibility
Created with R2008a
Compatible with any release
Platform Compatibility
Windows macOS Linux

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!