xlswrite for Mac, Linux

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

Related posts:

  1. 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...
  2. Undocumented XML functionality Matlab's built-in XML-processing functions have several undocumented features that can be used by Java-savvy users...
  3. GUI automation using a Robot This article explains how Java's Robot class can be used to programmatically control mouse and keyboard actions...
  4. JFreeChart graphs and gauges JFreeChart is an open-source charting library that can easily be integrated in Matlab...
  5. Formatting numbers Number formatting is easy to achieve in Matlab, by using a dedicated built-in Java object....
  6. Converting Java vectors to Matlab arrays Converting Java vectors to Matlab arrays is pretty simple - this article explains how....

Categories: Java, Low risk of breaking in future versions

Tags: , , , ,

Bookmark and SharePrint Print

9 Responses to xlswrite for Mac, Linux

  1. Frank Midgley says:

    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.

  2. the cyclist says:

    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.

    • Marin says:

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

    • Marin says:

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

  3. jamy says:

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

<pre lang="matlab">
a = magic(3);
sum(a)
</pre>