Numerous Matlab users have expressed frustration over the years at the fact that the xlswrite function, used for saving/updating Excel files, is only supported on the Windows platform. Matlab uses Excel COM to implement xlswrite functionality, and since COM is a Windows-specific technology, xlswrite does not work on non-Windows platforms.
In such cases, Matlab users normally save the data into a CSV (comma-separated values) file, using the built-in csvwrite or dlmwrite functions, or File-Exchange utilities such as Any2CSV. This enables saving the data, but does not enable saving formatting information or graphic objects (e.g., plots or images). As a side note, xlswrite also does not allow saving formatting information or graphic objects, but at least on Windows we can use direct COM to add them (or use my OfficeDoc utility).
Java solutions for spreadsheet access
Luckily, the community of Java developers, which is an order of magnitude larger than the Matlab community, has developed several utilities that we can easily use in Matlab to implement xlswrite‘s functionality on Macs, Linux, and wherever else Matlab may run – even Windows, if we really wish. Most articles on this website that deal with Java focus on its GUI aspects, i.e., how to use Java to improve Matlab’s plain-vanilla appearance/behavior. But Java is in fact a very broad programming platform that has an enormous repository of non-GUI solutions: networking, database support, data structures, computational algorithms, etc. etc. – and a huge number of them are open-source. Unlike Java’s GUI goodies, these Java packages, being non-GUI in nature, are fully supported by Matlab.
The Excel spreadsheets support, and Office support in general, is just another example of the wide range of non-GUI packages available in Java. In fact there are full-fledged Office lookalikes written in Java (most notably the open-source OpenOffice). We can either use these applications directly, or interact with them from Matlab (via Java), just as we can interact with Excel on Windows.
There are several Java packages that enable reading and writing spreadsheet data, without the full-blown Office support. One such open source project is the ODF (Open Document Foundation) Toolkit, which has plenty of online resources. In 2010 a Matlab user contributed a utility to use the ODF Java package for reading and writing ODF-format spreadsheets (*.ods). Other users have also provided ODF utilities for Matlab.
Using ODF is excellent and cross-platform, but does not solve the Excel problem directly, because the ODF format is incompatible with the XLS format. We can use another open-source Java package, JExcelApi, for this. JExcelApi is relatively easy to use and has several online tutorials (example1, example2), although it is not as widely used as ODF.
Another open-source Java package that can be used to directly read and write XLS files is Apache POI. An informal comparison of POI and JExcelApi can be found here.
xlwrite
Very recently, Marin Deresco has posted a Matlab utility called xlwrite that uses JExcelApi for implementing an xlswrite variant that can be used on all Matlab platforms.
After downloading xlwrite, we need to add its two contained JAR files (jxl.jar, MXL.jar) to the Java classpath:
javaaddpath('C:/Yair/Utils/JExcelAPI/jxl.jar') javaaddpath('C:/Yair/Utils/JExcelAPI/MXL.jar') |
We can now use xlwrite to store data in a real *.xls file:
xlwrite('my_data.xls', magic(3)) |
xlwrite has similar syntax and inputs to Matlab’s xlswrite. It can also write 3-d arrays (which xlswrite cannot), of cell and double type (the third dimension is simply stored in separate worksheets).
Note: Matlab’s decimal separator is ‘.’ – in order to be able to work with exported data on Macs, Mac users may need to change Mac decimal separator preferences. To do so you need to go to System Preferences > International > Formats and click on Customize button in the number zone, then type ‘.’ in the required field.
xlwrite is a real working solution. However, it may need further refinements, that will hopefully be added in future updates to this utility:
- automatically add the javaaddpath commands to xlwrite so that users won’t need to do them
- manage Java heap space, as Java heap memory saturates for large arrays exported many times (possible memory leak)
- format dates and strings, as all numbers appear as text in Excel
- optimize performance (the culprit appears to be a non-vectorized Cell2JavaString function)
- include formatting and other goodies that the current xlswrite does not
I recently ported some Windows MATLAB code to Linux that creates Excel and Powerpoint files. I found the Apache POI package very helpful. It can create traditional .XLS files or the .XLSX OpenOffice style documents.
@Frank – thanks. You’re right of course – I’ve known POI for several years so I don’t know why I forgot to mention it in the article… It’s now taken its rightful place in the article text.
This is potentially a very exciting submission for Mac users. It has been an ongoing frustration for me that I cannot fully take advantage of xlswrite.
One caveat, though. Unless I am misunderstanding something, it is not 100% accurate to say that the inputs are the same as those of xlswrite. In particular, it doesn’t seem that xlwrite will accept the “range” argument, that allows the user to specify where on the worksheet to write the array.
@Cyclist – thanks for the catch. I updated the wording accordingly.
@ the cyclist:
check the following submission:
http://www.mathworks.com/matlabcentral/fileexchange/38591
@Marin – I believe that @cyclist was only trying to say that although your xlwrite utility is great, it does not have the exact same inputs format as xlswrite. If you could make xlwrite use exactly the same inputs format as xlswrite, then it will be much easier for users to use xlwrite.
@Yair – I have to reword my previous comment :
Alec de Zegher published a utility http://www.mathworks.com/matlabcentral/fileexchange/38591 (also named ‘xlwrite’) and uses exact syntax as xlswrite. It is an excellent work (use of JExcelApi only).
In contrast, my original submission uses JExcelAPI and MXL package (that I’ve coded to handle 2d/3d data ).
This is why in my previous comment I pointed out to Alec’s excellent submission : same syntax as in xlswrite. However, Alec’s xlwrite handles 2d data only.
It seems to me that Mac/Linux/Unix MATLAB users will soon get xlswrite and xlsread full adaptations!
Have a good day all!
Hi, Just to follow “The cyclist” post: xlwrite doesn’t accept the “range” argument that allows the user to specify where on the worksheet to write the array. Is there any progress on this?
Thanks
Cheers,
J.
@Jamy – I suggest leaving a comment for Marin Deresco on his xlwrite‘s FEX page.