No BSD License  

4.85714
4.9 | 44 ratings Rate this file 140 Downloads (last 30 days) File Size: 10.5 KB File ID: #10465 Version: 1.0

xlswrite1

by

Matt Swartz (view profile)

  • 1 file
  • 140 downloads
  • 4.85714

 

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, Mkxlsfunc: Easily Integrate Legacy Spreadsheet Tools Into Matlab, and Wr Colwithheader.

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 (68)
14 Jun 2015 Ariel

Ariel (view profile)

Perfect, thanks.

30 Apr 2015 Suraj Srivastava

Hi,

I want to use it in GUI. I have used it but it gives error.
Kinldy help me out.

Thanks,
Suraj

23 Apr 2015 Christine

@Bharath and Kevin: thanks for the advice of using

ExcelWorkbook.SaveAs(fname);

instead of

ExcelWorkbook.SaveAs(fname,1);

I had the same problem.

@Kevin: to avoid using an Excel instance named exactly 'Excel', you can just add an additional argument 'Excel' to the function and delete the line

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

at the very beginning of the function.

Then you can use for example the following code:

e = actxserver('excel.application');
if ~exist(File,'file')
excelWb = e.workbooks.Add;
excelWb.SaveAs(File);
excelWb.Close(false);
end
invoke(e.Workbooks,'Open',File);
xlswrite1(File,data,sheet,range,e);

Hope that helps.

Best
Christine

13 Feb 2015 Kevin Gaukel

Bharath,

I just found that out myself. Now the sheets are present again. I don't know if this is Microsoft or my own oversight, but conditions changed.

Thank you for confirming that.

Comment only
13 Feb 2015 Bharath

********Regarding the .xlsx file issue**********

Just found out the solution for error mentioned by me and Kevin Gaukel. But unlike him my Excel file is created with Sheets, yet i still had the "Invoke Error, Dispatch Exception: " error.

I found out that in the saving line
>>ExcelWorkbook.SaveAs(fname,1);
the optional argument 1 is making the errors.
If i change the line to
>>ExcelWorkbook.SaveAs(fname);
it works perfectly.

That argument i think is an Enumeration Constant specifying the file format.
link to the method:
https://msdn.microsoft.com/en-us/library/office/ff841185.aspx

Comment only
12 Feb 2015 Bharath

I am having the same issue as mentioned by Kevin Gaukel.
I have tried the solution provided by him, but still couldn't resolve the issue.
Is there any other way.

P.S : There is no error while using invoke if i create a ".xls" file. The issue is only with the ".xlsx" file.

Comment only
11 Feb 2015 Kevin Gaukel

I was having a very frustrating situation happen recently where (out of nowhere), the program as shown below would not work. I would get

Error using Interface.000208DB_0000_0000_C000_000000000046/Open
Invoke Error, Dispatch Exception:
Source: Microsoft Office Excel
Description: Excel cannot open the file 'testfile.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 (x86)\Microsoft Office\Office12\1033\XLMAIN11.CHM
Help Context ID: 0

Further, the Excel file would not open, claiming the file was invalid or corrupted.

Well, I just learned why - and it's a warning based on a recent Microsoft update.

When I stepped through a similar program where it called an existing file, it worked without problems. However, when I did a Excel.Workbooks.Add then saved the file, I crashed when I reopened the file. I then added a step after the Workbooks.Add

set(Excel,'Visible',1);

and found the problem. The Excel File was opening...WITHOUT a single TAB. No sheets to store data. I suspect a recent Microsoft update created this problem.

What I did to solve this is to do the following:

Excel = actxserver('Excel.Application');
fname = fullfile(pwd,'testfile.xlsx');
if ~exist(fname,'file')
ExcelWorkbook = Excel.Workbooks.Add;
>>> ExcelWorkbook.Sheets.Add;
ExcelWorkbook.SaveAs(fname,1);
ExcelWorkbook.Close(false);
end
invoke(Excel.Workbooks,'Open',fname);

and the errors stopped.

If you have this problem, try that and it may disappear.

Comment only
30 Jan 2015 Kevin Gaukel

Good program. Main thing - which seems to be missing from my list is that the "Excel" name is actually used in the xlswrite1 function instead of an arbitrary name (like hExcel for the handle). This is due to the evalin('base','Excel') function. That makes the function "not self contained" like xlswrite. However, the xlswrite1 is MUCH faster than xlswrite on my specific requirement. As a result, I am rating it 5 stars.

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!

11 Sep 2014 kyle

kyle (view profile)

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!

Comment only
15 Aug 2014 Claire

Claire (view profile)

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

Claire (view profile)

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

Irene (view profile)

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.

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

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

Comment only
11 Jun 2014 Clark Gee  
26 Feb 2014 Bert

Bert (view profile)

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

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

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

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?

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

Annick (view profile)

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

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

Comment only
17 Apr 2013 Leo

Leo (view profile)

Thank you

22 Mar 2013 Richa

Richa (view profile)

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

Comment only
22 Mar 2013 Richa

Richa (view profile)

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?

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

Comment only
19 Jun 2012 Sam

Sam (view profile)

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

Comment only
05 Apr 2012 Abdull

Abdull (view profile)

Matt,

You are my man. Brilliant!

04 Apr 2012 Abdull

Abdull (view profile)

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

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

Mike (view profile)

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

Geoff (view profile)

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

Rachel (view profile)

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!

Comment only
31 Mar 2010 Rachel

Rachel (view profile)

 
20 Feb 2010 Harry

Harry (view profile)

Matt,

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

Cheers

21 Dec 2009 Anping

Anping (view profile)

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

Comment only
28 Jul 2009 lh ‹

lh ‹ (view profile)

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

So how to make excel com available? thx!

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.

Comment only
29 Jun 2009 Michele Colombo  
04 Jun 2009 Travis

Travis (view profile)

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

Comment only
28 Apr 2009 alei

alei (view profile)

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

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

Travis (view profile)

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

Comment only
31 Mar 2009 David Gómez Jiménez

Great job¡ thank you, its very usefull

08 Jan 2009 katie

katie (view profile)

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