Code covered by the BSD License  

Highlights from
xlwrite: Generate XLS(X) files without Excel on Mac/Linux/Win

4.80952

4.8 | 26 ratings Rate this file 163 Downloads (last 30 days) File Size: 8.45 MB File ID: #38591
image thumbnail

xlwrite: Generate XLS(X) files without Excel on Mac/Linux/Win

by

 

12 Oct 2012 (Updated )

Generates ‘.xls’ & '.xlsx' files on Mac/Linux/Win without Excel, using same syntax as xlswrite.

| Watch this File

File Information
Description

xlwrite allows to generate '.xls' & '.xlsx' files easily without having Excel installed or using COM-objects. It generates Excel files by using the Apache POI java library. This way Excel files can be generated on any platform supporting Java, including Linux & mac.

xlwrite uses the same syntax as xlswrite, making it easy to integrate in current applications.

xlwrite supports following datatypes:
- string
- numerical
- logical/boolean
The type of data is kept in Excel: for example boolean data in matlab will be saved as boolean in Excel.

xlwrite also supports a combination of above datatypes in a cell array like for example:
xlsData = {1 true 'text' false; 10 -12.5 6 false};

To generate the Excel file without too much overhead, xlwrite interacts directly with the Apache POI library.

The original idea of this script is based on the xlwrite function written by Marin Deresco, however due to some limitations we had to write an improved version.

Although created under Matlab 2012a, xlwrite should also work for earlier versions of Matlab.

UPDATE: if you find any bugs, please contact me directly.

Acknowledgements

Xlwrite : Export Data To Excel From Matlab On Mac/Win inspired this file.

Required Products MATLAB
MATLAB release MATLAB 7.14 (R2012a)
Other requirements Requires Apache POI library. Binaries included in zip file.
Tags for This File   Please login to tag files.
Please login to add a comment or rating.
Comments and Ratings (62)
15 Apr 2014 thomas

Hey, just a beautiful and useful function !
the only thing i changed is the auto-cast to cell array for dataset type (will do the same for tables)

if isa(A,'dataset')
A = dataset2cell(A);
end

Only one request : will you provide an xlread which does the same job in the other way ?

EDIT

I also add the auto-import command :

pkg = dir(fullfile(fileparts(mfilename('fullpath')),'lib','*.jar'));
for i=1:length(pkg)
jar{i} = fullfile(fileparts(mfilename('fullpath')),'lib',pkg(i).name);
end
cellfun(@javaaddpath,jar);

05 Apr 2014 florian

I am getting the following error when trying to paste a 28900,14 cell array in an xlsx file. it seems my memory funs out?

Warning: A Java exception occurred trying to load the sprintf class:
Java exception occurred:
java.lang.OutOfMemoryError: GC overhead limit exceeded
at java.util.Arrays.copyOf(Arrays.java:2271)
at java.util.zip.ZipCoder.getBytes(ZipCoder.java:89)
at java.util.zip.ZipFile.getEntry(ZipFile.java:306)
at java.util.jar.JarFile.getEntry(JarFile.java:226)
at java.util.jar.JarFile.getJarEntry(JarFile.java:209)
at sun.misc.URLClassPath$JarLoader.getResource(URLClassPath.java:840)
at sun.misc.URLClassPath.getResource(URLClassPath.java:199)
at java.net.URLClassLoader$1.run(URLClassLoader.java:358)
at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:270)
at com.mathworks.util.jarloader.SimpleClassLoader.loadClass(SimpleClassLoader.java:233)
at com.mathworks.util.jarloader.SimpleClassLoader.loadClass(SimpleClassLoader.java:201)
at com.mathworks.jmi.OpaqueJavaInterface.findClass(OpaqueJavaInterface.java:213)
> In ExternalException>ExternalException.ExternalException at 35
In JavaException>JavaException.JavaException at 38
In xlwrite at 260
In dikablis_analysis at 445
Error using xlwrite (line 260)
Java exception occurred:
java.lang.OutOfMemoryError: GC overhead limit exceeded
at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:174)
at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:147)
at org.apache.poi.xssf.usermodel.XSSFRow.createCell(XSSFRow.java:39)

Error in dikablis_analysis (line 445)
xlwrite(Xcel_FileNameExt,CELLTIME,sheetName,startRange);

05 Apr 2014 florian

feature request: table support...unfortunately it seems I can only export cells, vectors or matrixes but not tables?

30 Jan 2014 Alec de Zegher

@Dan: I just got a reply back from Mathworks support:
"This bug should be fixed with R2014a. The PreRelease for R2014a is available already and it should also contain the bug fix."

29 Jan 2014 Alec de Zegher

@Dan: XLSREAD has a bug in its implementation to read xlsx files; I have informed Mathworks support regarding this. The Mathworks development team has acknowlodged the bug. Don't know yet if they fixed it.

The workaround is to use xls files if you can.

29 Jan 2014 Alec de Zegher

@Nik: can you send me a demo code so I can reproduce it and fix the bug?

Thanks,
Alec

27 Jan 2014 Samuel

@Niklas: Try to specify the extension. For me the problem occured when not specifying it:

fileName = 'myfile' does not work with several sheets.

fileName = 'myfile.xlsx' does.

Don't ask me why.

30 Dec 2013 Dan

A great submission but there seems to be problems when I use xlsread to read these files. I am unable to read an xlsx workbook with multiple worksheets created with xlwrite (on a Mac). I just get "Worksheet not found".

09 Dec 2013 Niklas Brown

Hi Alec,
am trying to write a multiple sheet .xls-File. Unfortunately, on every loop is overwrites the worksheet. So at the end I get an xls-File with one worksheet created in the last loop. Sheetname changes on every loop.
If I understand the description correctly, the function should add the worksheet a the end?!
Thanks for help!

04 Dec 2013 Alessio Nava  
01 Dec 2013 SCADA Miner

Regarding the problem below. There was a corrupted existing myfile.xlsx already in the directory where xlwrite was trying to create the new file.

01 Dec 2013 SCADA Miner

Hi Alec. I have been using this on Windows and it is great. I have moved m code onto a linux machine and am encountering the following exception:
java.lang.illegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream at ...WorkbookFactory.create(WorkbookFactory.java (line 137 or xlwrite: xlsWorkbook = WorkbookFactory.create(fileIn))
I am trying to create an xlsx file which has been working flawlessly in windows. i have checked all the .jar dependencies and they are identical on my windows install as the linux one. Any suggestions to help fault-finding?
Thanks,
Tom

19 Nov 2013 Alec de Zegher

Hello Guillaume,
Your are right. I will try to send a bug fix as soon as possible.

Best regards,
Alec

15 Nov 2013 Guillaume

Oups my last post has been deleted.

Actually, i've got the following error

Error using xlwrite (line 179)
Java exception occurred:
java.lang.IllegalArgumentException: The workbook already contains a sheet of this name
at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:619)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:70)

when i used xlwrite several times on a new workbook with a specific worksheet (e.g. 8th sheet). The function created the workbook with one sheet named "sheet 8". So when i tried to use xlwrite again on the 8th sheet, xlwrite didn't recognized "sheet 8" as the 8th spreadsheet and tried to create a newer with the name "sheet 8". This induced the above error.

15 Nov 2013 Guillaume

replacing line 179 : "xlsSheet = xlsWorkbook.createSheet(['Sheet ' num2str(sheet)]);"

by :
"for sheet_number=1:sheet
xlsSheet = xlsWorkbook.createSheet(['Sheet ' num2str(sheet_number)]);
end"

Works well for me, but is it reproducible and reliable with different cases or is it the best way to do this ...

14 Nov 2013 Guillaume

Thanks again!

14 Nov 2013 Alec de Zegher

@Guillaume:
I highly recommend to use the 'fullfile' command of Matlab to generate paths to directories or files. It allows you to use relative and absolute paths, independent of the operating system.

The error you get is because could not open or store the file in the requested location.

Hope it helps.

14 Nov 2013 Guillaume

Thanks Alec!
You're right, my Poi_Library was not loaded. - -'

But after that, I still got an error :

Error using xlwrite (line 286)
Java exception occurred:
java.io.FileNotFoundException: ~/Documents/Recherche/Etude Course vs Bondissements/Donnees/Fichiers Sujets/Cyril
Schmit2/Analyse Force Cyril Schmit2.xlsx (No such file or directory)
at java.io.FileOutputStream.open(Native Method)
at java.io.FileOutputStream.<init>(FileOutputStream.java:194)
at java.io.FileOutputStream.<init>(FileOutputStream.java:84)

Actually, i had recently changed my laptop to the macbook pro and realized that , with MacOS X, the path filename attribution method is not as easy as Windows .

After multiple trial, i realized that the relative mode didn't work with XlWrite. Therefore, the path filename have to be loaded in absolute mode (starting with /User/username/etc.) with MacOS X

Don't know if it's mentioned in the Help section...

Guillaume

P.S: Sorry for my poor english skills.

13 Nov 2013 Alec de Zegher

@Guillaume: Thanks.
Yes it is possible to write to any file on your drive. We do it all the time.
The error you get seems to be caused by one of the libraries that is not loaded. I think it is poi-ooxml or poi-ooxml-schemas, but not sure.

13 Nov 2013 Guillaume

Very, Very Nice function!!! Thanks for the submission

I would just like to know, is it possible to write on specific path file? I'm working with MACOS X (Mavericks) and when i tried to write in excel spreadsheet out of the matlab path folder the following error appeared :

Error using xlwrite (line 148)
Java exception occurred:
java.lang.NoClassDefFoundError: Could not initialize class org.apache.xmlbeans.XmlBeans
at org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook$Factory.newInstance(Unknown Source)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.onWorkbookCreate(XSSFWorkbook.java:290)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:170)

24 Oct 2013 Michael

THANK YOU!!!!!

21 Oct 2013 Alec de Zegher

@ S A: Just add the folder to your Matlab path, and then it will always be loaded by default.
You can access the "Set Path" in the ribbon toolbar of Matlab. Hope it helps!

21 Oct 2013 KoenV

Works great!

15 Oct 2013 S A

I have used Matlab for years on a Windows system, and am just starting on a Mac. How do you add this file to your directory? I copied the poi_library folder to my java folder, and added the .jar files to the classpath.txt file. But how do I add xlwrite to the general matlab directory (so that it may be called in any folder as a built-in matlab function)?

17 Sep 2013 Dominik

Hi all,
The column index error is due to the Microsoft limitation for *.xls-files (Excel 1997-2003). Just change your specified file extension to *.xlsx to solve the problem.
Thanks Alec for the hint.
Cheers,
Dominik

04 Sep 2013 Dominik

Hi Alec,
Thanks for the great submission.
However, like Corbin, I am also receiving an error concerning the column size:
"java.lang.IllegalArgumentException: Invalid column index (256). Allowable column
range for BIFF8 is (0..255) or ('A'..'IV')"
Is there a workaround to this?
Thanks,
Dominik

23 Jul 2013 Alec de Zegher

Hi Robert,
It should normally append the data. It will only overwrite data if your column header matrix is too big or if your cell range is not properly defined.
If you cannot solve it, please send me a mail with a demo code, so I can reproduce it, and I will fix it as soon as possible.

20 Jul 2013 Robert

Hi Alec,

Thanks for sharing this excellent code. I just tried to write data and then add column headers but found that xlwrite (as opposed to xlswrite) does not append to an already existing file but rather replaces it. Am I missing something?

Thanks, Robert

07 Jun 2013 Corbin Cunningham

Hi Alec,

I am receiving this error: "Warning: Could not add cell at RY338 because it exceeds the maximum column limit"

What is the limit on the number of columns xlwrite can make and is there a wordaround for that?

Any info would be great!

Best,
Corbin

03 Jun 2013 Alec de Zegher

@Klever: You get this error, because you are running an old version of Matlab(2009a) which does not support the tilde (~) operator to ignore outputs of a function. This has only been introduced in Matlab 2009b. You can read more about it on http://blogs.mathworks.com/loren/2009/09/11/matlab-release-2009b-best-new-feature-or/

As a workaround, you could try to modify line 140 in xlwrite from
[~,~,fileExt] = fileparts(filename);
to
[unusedA,unusedB,fileExt] = fileparts(filename);

02 Jun 2013 Klever

Hi Alec, This script seems like it has great potential for my application. However, it works great on Windows, but on Red Hat and with MATLAB 7.8.0 (2009a) I keep getting the following error when I run your test script:

??? Error: File: xlwrite.m Line: 140 Column: 7
Expression or statement is incorrect--possibly unbalanced (, {, or [.

Error in ==> Test_xlWrite at 28
xlwrite(fileName, xlsData, sheetName, startRange);

Can't seem to solve this error. Any Suggestions?

28 May 2013 Alec de Zegher

@Jeremy: This is a bug in the implementation of XLSREAD. When opening an XLSX on a Mac, XLSREAD opens it in 'basic' mode. The 'basic' mode in XLSREAD has a bug when opening XLSX. If you try [~,b,c] = xlsread('myXlsx.xls', 1, '', 'basic') on your windows PC you will see the same bug.
I would recommend to save the file as XLS if possible.

@Barry: There is no plan to create an xlsread version with the POI library. Did you try using the xlsread in 'basic' mode? This should normally avoid the "Could not start Excel server..." error.

24 May 2013 Barry

I was just wondering if there is a xlread version using the POI library. When using the normal xlsread in a loop I eventually get a "Could not start Excel server...". Thanks.

09 May 2013 Jeremy

Update: I tried this on Windows 7 and it worked correctly. On my Mac(10.8) I encountered the problem. Also this was as a .xlsx, it seems the .xls works as stated.
Hi, I've noticed a bug(s) when trying to use xlsread after xlwrite. In my situation it was occurring when the first column of a record was a '1A' instead of a '1a', but solving this next bug may catch that one. I tried to make a sample program to illustrate the bug. The return did not get the second row on the read. Note: If i go into Excel and do something and resave, xlsread then reads the second row:
titles = {'label1', 'label2'};
record = {1, 2};
xlwrite('myXlsx.xlsx', titles, 'A1:B1');
xlwrite('myXlsx.xlsx', record, 'A2:B2');
[~,b,c] = xlsread('myXlsx.xlsx')
b =
'label1'
c =
'label1'

06 May 2013 Tom

great - a little slow - but still great!

10 Apr 2013 Denis Anikiev

Great submission, thanks.
Why don't you add something like this to beginning:

path = mfilename('fullpath');
name = mfilename;
path = path(1:length(path)-length(name));
javaaddpath([path 'poi_library/poi-3.8-20120326.jar']);
javaaddpath([path 'poi_library/poi-ooxml-3.8-20120326.jar']);
javaaddpath([path 'poi_library/poi-ooxml-schemas-3.8-20120326.jar']);
javaaddpath([path 'poi_library/xmlbeans-2.3.0.jar']);
javaaddpath([path 'poi_library/dom4j-1.6.1.jar']);

05 Mar 2013 Alexandre Laurin  
27 Feb 2013 Alec de Zegher

@Mario: Thanks!

@Thierry: thanks for the recommendations!
1) The Stax library should indeed also be loaded. This will be fixed in next release.

2) Originally we also wanted to add an auto-load function, but we ran into the issue that the POI folder location is unknown. It is allowed to be anywhere on your computer. For example in our matlab-webapplications, the POI libraries are put next to the other java libraries in a central folder. To keep it as generic as possible we decided to throw an error if it is not loaded.

3)xlwrite replacing NaN with the previous number is indeed a bug. This was solved in the update of 25/01/2013.

Best Regards,
Alec

27 Feb 2013 Mario

Hi Alec thanks for this wonderful submission it works great.

19 Feb 2013 Thierry Dalon

1.It seems you also need to javaaddpath the stax*.jar to get it work, right?
(it is omitted in the test_xlwrite).
I would add something like this in the beginning of xlwrite:
if exist('org.apache.poi.ss.usermodel.WorkbookFactory', 'class') ~= 8 ...
% || exist('org.apache.poi.hssf.usermodel.HSSFWorkbook', 'class') ~= 8 ...
% || exist('org.apache.poi.xssf.usermodel.XSSFWorkbook', 'class') ~= 8
% auto-initialize
javadir=fullfile(fileparts(mfilename('fullpath')),'poi_library');
jarfiles=dir(javadir,'*.jar');
javaaddpath(jarfiles);

2. xlwrite replace NaN by previous numeric values
whereas xlswrite replace NaN by empty cell.

03 Feb 2013 Alec de Zegher

Hello Jveer,

It should not be a problem to use javaaddpath together with the compiler. We use it for example with de Matlab JA compiler in our company.

To make it work with the compiler:
- Add/drag&drop the POI folder to 'Shared Resource and Helper Files'-window of the compile project
- make sure the javaaddpath point to the right (relative) location.

I hope this helps

03 Feb 2013 Jveer

Thank you for the excellent submission.

Is there a way around 'javaaddpath' in order to compile using Matlab compiler?

26 Jan 2013 Robert

Very nice, many thanks for sharing this. I've added the javaaddpath-commands to my startup.m file - this way xlwrite is immediately available

25 Jan 2013 Alec de Zegher

Hello Klaus, the bug is fixed & also the documentation was adapted. I uploaded a new version. It should be online once accepted by the Mathworks.

22 Jan 2013 Klaus

to get xlswrite behavier for NaN's just insert

if isnumeric(data)
if isnan(data)
data=[];
end
end
after
if ~isempty(data)

22 Jan 2013 Klaus

using cell arrays the NaN to empty conversion is currently not working.

in the help example xlSwrite is copy-pasted

04 Jan 2013 Wouter  
10 Dec 2012 Marianna

Alec thanks a lot for the immediate feedback! I tried it and it works great, I can't thank you enough :)

04 Dec 2012 Alec de Zegher

Hello Marianna,
When you read the documentation of xl(s)write, you are not allowed to give an empty ([]) sheet. You should use the syntax xlwrite('test.xls',M,'A1:B10') instead of xlwrite('test.xls',M,[],'A1').

Setting the sheet to 1 is also an option, like you said... and the error you are getting is a bug:) I fixed it and uploaded a new version today. It should be online by tomorrow.

02 Dec 2012 Marianna

Hi and thanks for the much needed submission :)
I have been using the function for some weeks now without problem. Recently I tried to use it in the code of a simple GUI that I implemented and for some reason nothing works..
More specifically, I have o command of the type:
xlwrite('test.xls',M,[],'A1')
and get the following error:
??? No method 'getSheet' with matching signature found for class
'org.apache.poi.hssf.usermodel.HSSFWorkbook'.

Error in ==> xlwrite at 161
xlsSheet = xlsWorkbook.getSheet(0);

If I set the sheet to 1, I get:
??? Java exception occurred:
java.lang.IllegalArgumentException: Sheet index (0) is out of range (0..-1)

Note that everything works fine when I save my variables in the workspace and use the same code from the command line (so it obviously has to do with the GUI).

Any idea if/how I can work around this?

Any help would be much appreciated!
Thanks,
Marianna

27 Nov 2012 Alec de Zegher

@ Dan & Jason: Thanks for the feedback!

I fixed the bug & uploaded a new version. Should be approved by tha Mathworks soon...

27 Nov 2012 Dan

Excellent job!

I had the same problem mentioned by Jason. I replaced line 211 by:

currentRow = xlsSheet.getRow(iRow);
if isempty(currentRow)
currentRow = xlsSheet.createRow(iRow);
end

Hope it helps!

20 Nov 2012 Jason

When I try to specify a range, it wipes out the entire row. So for example the following code

xlwrite('M:\test.xls',[1,2,3,4],'Data','B5:E5');

writes the data in the proper cells but all of the other cells of row 5 are overwritten to be blank. Is there a way to preserve the data in the other cells?

06 Nov 2012 omar

Hi,
problem solved, xlsread(fileName) works. Great job, thanks a millon.

05 Nov 2012 Alec de Zegher

Omar,
the problem should be solved with this update (for XLS files).
Alec

28 Oct 2012 omar

Hi,

How to read the file again in matlab

xlsread give the following:

Error using xlsread (line 232)
XLS File contains unicode text which is not yet
supported.

Any suggestions?

Thanks in advance

28 Oct 2012 omar  
28 Oct 2012 omar

Your code just worked on Linux ubuntu with MATLAB 2011b.Thanks

17 Oct 2012 Marin Deresco

nice!

12 Oct 2012 peter

Now I got it !!!
thanks a lot

12 Oct 2012 Alec de Zegher

@ Peter

This error is caused because the jxl.jar (JExcelApi) package is not loaded correctly in your workspace.

- Check that you extracted correctly the jxl.jar file from the zip file.
- You should not forget to add the jxl.jar file to your Matlab Java path as explained in test_xlwrite. If jxl.jar is in the same directory as your current workspace: you should execute the "javaaddpath('jxl.jar');" command. If jxl.jar is not in the same directory as your current workspace, you should execute the "javaaddpath('\FULL\PATH\TO_jxl.jar');".

Please let me know if this solves your problem or not.

Alec

Please let me know

12 Oct 2012 peter

??? Undefined variable "jxl" or class "jxl.Workbook.createWorkbook".

I tried to run the test_xlwrite and got the following ?

Error in ==> xlwrite at 87
xlsWorkbook = jxl.Workbook.createWorkbook(xlsFile);

Error in ==> test_XLWRITE at 19
xlwrite(fileName, xlsData, sheetName, startRange);

Updates
05 Nov 2012

The new version of xlwrite uses the Apache POI library, instead of JExcelApi. The Apache POI library has some advantages over JExcelApi library:
- It can create XLSX files
- xlsread supports xls files generated by POI lib.

28 Nov 2012

Fixed bug: use existing rows if present, instead of overwrite rows by default. Thanks to Dan & Jason.

28 Nov 2012

Fixed bug: use existing rows if present, instead of overwrite rows by default. Thanks to Jason.

04 Dec 2012

Bugfix: if a numeric sheet was given & didn't exist an error was returned instead of creating the sheet. Thanks to Marianna

07 Jan 2013

Fixed bug: use existing cell if present, instead of overwriting. This way original XLS formatting is kept & not overwritten.

25 Jan 2013

20130125 - Fixed bug & documentation. Incorrect working of NaN. Thanks Klaus

27 Feb 2013

20130227 - Fixed bug when no sheet number given & added Stax to java load. Thanks to Thierry

Contact us