Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 5 down vote accepted

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\ 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/ 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: <> and
   '                  <>.
   ' 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
   Dim oPropertyValue As New
   If Not IsMissing( cName ) Then
      oPropertyValue.Name = cName
   If Not IsMissing( uValue ) Then
      oPropertyValue.Value = uValue
   MakePropertyValue() = oPropertyValue
End Function
share|improve this answer
Wow, that's a great, profound answer. Wish I could give you more than 1 upvote. – Adam Matan Mar 7 '10 at 9:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .