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.
After downloading xlwrite, we need to add its two contained JAR files (jxl.jar, MXL.jar) to the Java classpath:
We can now use xlwrite to store data in a real *.xls file:
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
- Uicontrol callbacks This post details undocumented callbacks exposed by the underlying Java object of Matlab uicontrols, that can be used to modify the control's behavior in a multitude of different events...
- Undocumented XML functionality Matlab's built-in XML-processing functions have several undocumented features that can be used by Java-savvy users...
- GUI automation using a Robot This article explains how Java's Robot class can be used to programmatically control mouse and keyboard actions...
- JFreeChart graphs and gauges JFreeChart is an open-source charting library that can easily be integrated in Matlab...
- Formatting numbers Number formatting is easy to achieve in Matlab, by using a dedicated built-in Java object....
- Converting Java vectors to Matlab arrays Converting Java vectors to Matlab arrays is pretty simple - this article explains how....