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
- Setting system tray icons System-tray icons can be programmatically set and controlled from within Matlab, using new functionality available since R2007b....
- Tab panels – uitab and relatives This article describes several undocumented Matlab functions that support tab-panels...
- GUI automation utilities This article explains a couple of Matlab utilities that use Java's Robot class to programmatically control mouse and keyboard actions...
- Matlab-Java interface using a static control The switchyard function design pattern can be very useful when setting Matlab callbacks to Java GUI controls. This article explains why and how....
- Running VB code in Matlab Matlab does not natively enable running VB code, but a nice trick enables us to do just that...
- Javacomponent background color This article explains how to align Java component background color with a Matlab color....