No BSD License  

4.86486

4.9 | 39 ratings Rate this file 160 Downloads (last 30 days) File Size: 10.5 KB File ID: #10465

xlswrite1

by

 

21 Mar 2006 (Updated )

increases speed of xlswrite function when used in loops or multiple times.

| Watch this File

File Information
Description

This code increases the speed of the xlswrite function when used in loops or multiple times. The problem with the original function is that it opens and closes the Excel server every time the function is used. To increase the speed I have just edited the original function by removing the server open and close function from the xlswrite function and moved them outside of the function. To use this first run the following code which opens the activex server and checks to see if the file already exists (creates if it doesnt):

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

Then run the new xlswrite1 function as many times as needed or in a loop (for example xlswrite1(File,data,location). Then run the following code to close the activex server:

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

This works exactly like the original xlswrite function, only many many times faster.

Acknowledgements

This file inspired Office Doc Read/Write/Format Ms Office Docs (Xls/Doc/Ppt), Xlswrite Mod, Xlswrite2007, Saveppt2, Function For Faster Data Transfer Matlab < > Excel, and Mkxlsfunc: Easily Integrate Legacy Spreadsheet Tools Into Matlab.

MATLAB release MATLAB 7.1.0 (R14SP3)
Tags for This File   Please login to tag files.
Please login to add a comment or rating.
Comments and Ratings (59)
11 Sep 2014 kyle

Hello, I am encountering a problem with this function when writing an array with a large number of columns. The data gets written, but the Excel range to write to is incorrect, so it ends up writing a whole bunch of columns with #N/A.

I've dug into the xlswrite1 function and it appears the problem is in the function dec2base27(d). For my example below, the end column should be DGB, but it creates it as GGB. I don't see this problem with smaller data sets however.

My example:
Excel = actxserver('Excel.Application');
% Show window (optional).
Excel.Visible = 0;
% Open file located in the current folder.
Workbook = Excel.Workbooks.Open('TEST.xlsx');

data = ones(50,2887);
xlswrite1('TEST.xlsx',data,'Sheet1','B1');

Workbook.Save;
Excel.Quit;
Excel.release;

Thank you very much!

15 Aug 2014 Claire

To follow up on the error I saw before... I added a try - catch block. In case invoke fails, try again. This solved my problem posted below.

try
invoke(Excel.Workbooks,'Open',fileNameFull);
catch
Excel.Quit;
Excel.delete;
clear Excel;
Excel = actxserver('Excel.Application');
invoke(Excel.Workbooks,'Open',fileNameFull);
end

29 Jul 2014 Claire

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

This is the error I message I received.

23 Jul 2014 Irene

Hello, I have a little problem running this function.
I opened the activex server and all, specifing an existing .xls file, and also changed the second line of the script with 'caller', but still is giving me an error.

The error I got is:

Error using xlswrite1 (line 143)
Error: Object returned error code: 0x800A03EC

Error in xlswrite1 (line 143)
error('MATLAB:xlswrite:SelectDataRange',lasterr);

The error seems to be in the range I specify, but I call it as I used to do with xlswrite, so having something like 'A1:B1'.

I'm working on windows 7, matlab 2012b and excel 2007 (italian version, thus the sheet is originally named as 'Foglio1' instead of 'Sheet1', but I also have manually changed it in the xls file).
Thank you very much in advance.

23 Jul 2014 Sheldon

ActiveX is not available on Mac OS. So this solution is Windows only. If you are doing a lot of xls writing on a Mac, I'd suggest using csvwrite, which is much quicker than xlswrite, and then just opening the csv in Excel.

A csv file, is a text document, with each column separated by a comma and each row on a new line. It can be opened in Excel, and quite easily saved as an excel file.

17 Jun 2014 katerina

i changed from 'base' to 'caller' on line two, and also checked the task manager. it still doesn't work. what am i missing?? i would love to benefit from faster excel accesses.

11 Jun 2014 Clark Gee  
26 Feb 2014 Bert  
17 Feb 2014 Christine

@Mikaela: Line 2 in xlswrite1 should be

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

Did you name the handle to the ActiveX Server 'Excel', like in

Excel = actxserver ('Excel.Application');?

For xlswrite1, you need the variable 'Excel' in your workspace. If you started the ActiveX Server like

E = actxserver ('Excel.Application');

you have to change Line 2 of xlswrite1 into

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

But, in addition, I don't think that it works with Mac OS. If you get it run on a Mac, please let me know how. :)

06 Feb 2014 Mikaela

I'm also having the following error:

Error using evalin
Undefined function or variable 'Excel'.

Error in xlswrite1 (line 2)
Excel=evalin('caller','Excel');

Perhaps this is not compatible with Microsoft Excel 2008 for Mac?

04 Jan 2014 Roy Veldhuizen

Never mind, the problem did not return after a restart.
Thanks for the file =)

04 Jan 2014 Roy Veldhuizen

Thanks for the program, it works really fast. However, I get this error when i'm writing to an xlsx file

Error using Interface.Microsoft_Excel_14.0_Object_Library.Workbooks/Open
Invoke Error, Dispatch Exception:
Source: Microsoft Excel
Description: Excel cannot open the file 'Splinefit.xlsx' because the file format or file
extension is not valid. Verify that the file has not been corrupted and that the file
extension matches the format of the file.
Help File: xlmain11.chm
Help Context ID: 0

Error in HDD_spline_fit (line 35)
ExcelWorkbook = Excel.workbooks.Open(File);
In which Splinefit.xlsx is the target file.

If I try to write to an .xls file, thus Splinefit.xls, the error does not arise.
As I'm using a predefined xlsx file, switching to .xls does not really work for me.

Thanks in advance for your time!

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?

19 Nov 2013 Christoph E.

I read xlsfiles by opening my own ActiveX-Server for a while. Suddenly I got the error:
Interface.000208DB_0000_0000_C000_000000000046/Open

After a while I checked the taskmgr and saw that there was still an Excel-task. After killing it the error was gone... happened that because of a breakpoint I did not stop the ActiveX-Server which caused some problems...

10 Oct 2013 Annick

Hello,

Is there any equivalent of this for xlsread?

I am getting the following error:
Error using xlsread (line 247)
Error registering event(s), Advise failed

Error in MoT_automated_bigTable_RunSet (line 119)
couples(5,r)=xlsread(DataDHS,'Sheet1',sprintf('F%d',r+1)); %SC neg Ncirc

And I guess it's because I am reading an excel file many times in a loop.

Any advice would be much appreciated!

many thanks,

Annick

23 Aug 2013 James Anderson

For those who are getting an error like this:

Error using
Interface.000208DB_0000_0000_C000_000000000046/Open
Invoke Error, Dispatch Exception:
Source: Microsoft Office Excel
Description: 'Desktop\filename.xlsx'....

change invoke(Excel.Workbooks,'Open',File);

to:
ExcelWorkbook = Excel.workbooks.Open(File);

and then for the save and close commands:

ExcelWorkbook.Save
ExcelWorkbook.Close(false) % Close Excel workbook.
Excel.Quit;
delete(Excel);

17 Apr 2013 Leo

Thank you

22 Mar 2013 Richa

Including the entire path in the file name fixed my error.

22 Mar 2013 Richa

I am still getting the error:

Error using
Interface.000208DB_0000_0000_C000_000000000046/Open
Invoke Error, Dispatch Exception:
Source: Microsoft Office Excel
Description: 'Desktop\filename.xlsx'
could not be found. Check the spelling of the file
name, and verify that the file location is correct.

If you are trying to open the file from your list of
most recently used files, make sure that the file
has not been renamed, moved, or deleted.
Help File: C:\Program Files (x86)\Microsoft
Office\Office12\1033\XLMAIN11.CHM
Help Context ID: 0

I changed line 2 from 'base' to 'caller' but it did not help. Has anyone found a solution to this?

21 Jul 2012 sumith s.pillai

Hi

I am a beginner in Matlab. I am using xlswrite function. But it takes lots of time. I saw your solution. I downloaded xlswrite1. I changed my program as you said. But it showing an error,
I run the following code
Excel = actxserver ('Excel. Application');
File=Site_File;
if ~exist(File,'file')
ExcelWorkbook = Excel.workbooks.Add;
ExcelWorkbook.SaveAs(File,1);
ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',File);
status=xlswrite1(Site_File, Output, Range);
nvoke(Excel.ActiveWorkbook,'Save');
Excel.Quit
Excel.delete
clear Excel

%where site_file is the output file.

??? Invoke Error, Dispatch Exception:
Source: Microsoft Excel
Description: 'CEll.xls' could not be found. Check the spelling of the file
name, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files, make
sure that the file has not been renamed, moved, or deleted.
Help File: xlmain11.chm
Help Context ID: 0

Error in ==> KL_Database_Process_Shared_RTT_Operator>CopyData at 675
invoke(Excel.Workbooks,'Open',File);

Error in ==> KL_Database_Process_Shared_RTT_Operator>Generate_Report_7 at 489
CopyData(Site_File,'F51:H79',Operator1,'1',n);

Error in ==> KL_Database_Process_Shared_RTT_Operator at 125
[b,Site_File]=Generate_Report_7(n,Set_Date);

I followed your steps and don’t know how to solve this problem.

Please help me.

19 Jun 2012 Sam

ERROR:

I have run the file with the following exaclty as descripted by the author, but it does not work (Excel 2007, Matlab 7.4.0):
--------------------------

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

xlswrite1(File,[1 2 3])

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

---

error:

??? Invoke Error, Dispatch Exception:
Source: Microsoft Office Excel
Description: Excel cannot open the file 'junk.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.
Help File: C:\Program Files\Microsoft Office\Office12\1033\XLMAIN11.CHM
Help Context ID: 0

Error in ==> test_xlswrite1 at 8
invoke(Excel.Workbooks,'Open',File);
invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit
Excel.delete
clear Excel

05 Apr 2012 Abdull

Matt,

You are my man. Brilliant!

04 Apr 2012 Abdull

I still get error message even though, I have changed line 2 from base to caller.
Please help me.

---Error message start---
??? Error using ==> evalin
Undefined function or variable 'Excel'.

Error in ==> xlswrite1 at 2
Excel=evalin('caller','Excel');
---Error message end---

23 Mar 2012 Luis Andres  
08 Feb 2012 Bilen Oytun Peksel  
25 Oct 2011 Ngai-Hang

I found that this didn't work with Office 2007 files, i.e. with .xlsm. I managed to get xlswrite2007 to work though with some modifications.

12 Jul 2011 olivier calcoen

It is very great
Thank you very much

11 Jul 2011 Christopher

Hello all,

I'm still getting an error after changing line 2 to Excel=evalin('caller,'Excel'); I've gone to my task manager and closed all excel processes. What do I need to change to get this code to work??? Thank you in advance.

Chris

20 May 2011 Mike

For those still getting an error after changing line 2 to

Excel = evalin('caller','Excel');

Go to Task Manager, see if the Excel process is still running. Errors stop the program from getting to the Close line. So change line 2, end the Excel process in Task Manager. I did this and it now works great!

10 Feb 2011 Geoff

Thank you Matt! I had about 400 calls to xlswrite which took about 11mins, now takes 9sec! Massive improvement!

Thanks also to Martin Rouse for solving the issue of calling xlswrite1 within a function.

06 Dec 2010 Artik Crazy

Thank you a lot!
This function helped me very much.
Overall run time of my simulation now is almost 10 times faster!
Block that was build of loop iterations on xlswrite now works 20 times faster.

03 Oct 2010 TideMan

Worked like a charm once I changed 'base' to 'caller' in Excel=evalin('base','Excel');
as suggested by Martin Rouse.

31 Mar 2010 Rachel

Does need something in the actual script rather than in the description on this page to avoid the "Error using ==> evalin " error. Not immediately obvious that it requires some lines before and after you call it otherwise.
Time saved by this is blissful!

31 Mar 2010 Rachel  
20 Feb 2010 Harry

Matt,

you are my main man... Big ups..

Cheers

21 Dec 2009 Anping

It reduces my computing time from 20s to 2s.

28 Jul 2009 lh ‹

When I run the program, the result:??? Error using ==> evalin
Undefined function or variable 'Excel'.

So how to make excel com available? thx!

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.

29 Jun 2009 Michele Colombo  
04 Jun 2009 Travis

Ana, I am getting the same result, I have used this code for a few months now with no issue until this.

28 Apr 2009 alei

I've got an error message saying that 'Excel' is not defined.

??? Error using ==> evalin
Undefined function or variable 'Excel'.

Error in ==> xlswrite1 at 2
Excel=evalin('base','Excel');

Anyone know how to fix it? Thx

23 Apr 2009 Aviator

I'm getting the following error. It seems Matlab cannot save/close the spreadsheet.

??? Invoke Error, Dispatch Exception:
Source: Microsoft Office Excel
Description: 'My_File.xls' is read-only. To save a copy, click OK,
then give the workbook a new name in the Save As dialog box.
Help File: C:\Program Files\Microsoft Office\Office12\1033\XLMAIN11.CHM
Help Context ID: 0

Error in ==> Line 280
invoke(Excel.ActiveWorkbook,'Save');

Anybody know what can be done to fix this? (by the way, I'm using Office 2007, in compatibility mode) ; Thanks

15 Apr 2009 Travis

Is there any way to adjust this so that it writes to office 2007 (.xlsx)?

31 Mar 2009 David Gómez Jiménez

Great job¡ thank you, its very usefull

08 Jan 2009 katie

awesome. I was wondering if this could be modified to write to multiple files within a single loop?

08 Nov 2008 Alessandro Sacco

Thank you for your good job; I just want to ask you if there is anything similar for xlsread. It's so bad leaving the excel com server open each time we run xlsread.

Thanks a lot!

20 Oct 2008 Mark Hayworth

Note: If you call this from within your own function instead of the MATLAB command line, you will need to change the second line to
Excel = evalin('caller', 'Excel');
(instead of Excel = evalin('base', 'Excel'); like he has it)

Plus all the error messages arguments need to be changed to "MATLAB:xlswrite1" instead of "MATLAB:xlswrite"

He also needs to add the description at the top of the m-file. As it is, there is no description in the file itself and one would have to return here to the file exchange to get it. It should be also in the first few lines of the m-file.

08 Sep 2008 magda anusca  
23 Jun 2008 N Rolfes

worked like a godsend, turned a several hour job into a 10 minute task. Thank you!

Also, thanks for the tip Martin as I used this as well.

30 May 2008 Cristi Pedotto

This just saved me so much time! My code processes 10x faster! Thanks so much!

23 Jan 2008 Martin Rouse

Excellent solution to an annoying problem! Works very quickly!

1 point i would like to add, if executing xlswrite1 within a function or GUI, change line 2;

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

to;

Excel=evalin('caller','Excel');

Many thanks

04 Oct 2007 Angie Blue

Perfect :) Thanks a million, you saved a lot of time and effort.

01 Aug 2007 Tal Raviv

This works remarkably quickly. Thanks for solving the problem for all of us!

02 Jun 2007 Andrej Skraba

This is a very good solution. However, there should be some attention paid on closing the Excel session.

A great addition.

10 May 2007 Rahul Bagdia

Thanks a lot Matt. It works awesomely fast.

21 Mar 2007 Alan L

Excellent. Works perfect.

10 Feb 2007 Peter Suh

Very very excellent!!

The best part about this is that did eliminate the xlswrite bug which would leave the excel com server open each time it ran xlswrite. This did save me lots and lots of time and energy..I was rewriting my own xlswrite function.

21 Dec 2006 Unai Cornes

THANKS A LOT!!!! It's amazing how fast the program goes now!!

Thank you, Matt.

10 Nov 2006 Stacey Chang

cool stuff!! I appreciated ur code so much! But I couldn't run my loop over 65535 times (I need to run the loop for a million times), hope that you could help me with this...big thanks!!

Contact us