Ubuntu: Convert OpenOffice Calc to Excel workbook using CLI

Question

I need to create an automated report in a spreadsheet format. Unfortunately, There seems to be an easy way to create these reports using OpenOffice Calc, but upper managements wants them in MS Excel format.

As these reports are to be created and emailed automatically, is there a nice, command-line way to convert between these file formats?

Answer 1

Yes, there is a command-line way to convert between these file formats. But a macro must first be installed into OpenOffice. This macro will take a specified OpenOffice file name from the command line and create a Microsoft Excel file (.xls) with the same name except for the file extension.

I have tested this with OpenOffice 3.2 on Windows, but I expect it to work on Ubuntu and with OpenOffice 1.1 or later.

The program to use on the command-line is soffice. On Windows it is not on the path and the absolute path must be used or the current directory be there.

Sample Ubuntu command-line (untested) that converts an OpenOffice spreadsheet file in /home/mortense/temp9/test2.ods to Excel format, /home/mortense/temp9/test2.xls:

"/usr/lib/openoffice/program/soffice" "macro:///Standard.doConvertToExcel.doConvertToExcel(/home/mortense/temp9/test2.ods)"

Sample Windows command-line used during testing that converts an OpenOffice spreadsheet file in D:\temp9\test2.ods to Excel format, D:\temp9\test2.xls:

"D:\Program Files (x86)\OpenOffice32\OpenOffice.org 3\program\soffice.exe" "macro:///Standard.doConvertToExcel.doConvertToExcel(D:/temp9/test2.ods)"

The macro is listed below. Here are the installation instructions:

1. Start OpenOffice Calc.

2. Copy the code at the end of this answer to the clipboard. Create a macro: menu Tools/Macros/Organise Macros/OpenOffice.org Basic/. Then expand to "My Macros/Standard/" so that "Standard" is selected. Press button "New".

3. Right click on the tab in the lower left and select: Insert/BASIC Module. Right click on the created module, select "Rename" and type "doConvertToExcel". Click in the edit area (to set the focus there), select all (Ctrl + A) and paste in the code. Close the window (e.g. with Ctrl + W). That's it!

---

The code for the macro:

Sub doConvertToExcel( aFile )
   URL = ConvertToURL( aFile )

   ' Open the document.
   ' Just blindly assume that the document is of a type that OOo will
   '  correctly recognize and open -- without specifying an import filter.
   doc = StarDesktop.loadComponentFromURL( URL, "_blank", 0, Array(_
            MakePropertyValue( "Hidden", True ),_
            ) )

   outFile = Left( aFile, Len( aFile ) - 4 ) + ".xls"

   outURL = ConvertToURL( outFile)

   ' List of filters: <http://www.oooforum.org/forum/viewtopic.phtml?t=3549> and
   '                  <http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options>.
   '
   ' Save the document using a filter.
   doc.storeToURL( outURL, Array(_
                              MakePropertyValue( "FilterName", "MS Excel 97" ),_
                 )

   doc.close( True )
End Sub


Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
   Dim oPropertyValue As New com.sun.star.beans.PropertyValue
   If Not IsMissing( cName ) Then
      oPropertyValue.Name = cName
   EndIf
   If Not IsMissing( uValue ) Then
      oPropertyValue.Value = uValue
   EndIf
   MakePropertyValue() = oPropertyValue
End Function