1

I want to convert ods documents to xls using libreOffice macro in cli but it seems i do not have any output and libreOffice does not produce any error ( at least not visible ones ).

Here is the command i run : 

libreoffice --norestore --invisible --headless --nologo "macro:///Standard.Convert.ConvertTo(\"MYFILE.ods\",\"OUTPUT.xls\",\"MS Excel 97\",\"\")"

the output is not created and i do not get any errors (i also tried with 2>&1 but has no effect).

libreoffice --version
LibreOffice 6.0.7.3 00m0(Build:3)

I tried different version from 4.2 to 6.0 but nothings different and i do not think it's a version issue.

I also know about the --convert-to option but i need to name my output and this macro is used everywhere in the software i am debuging so it would be easier for me to keep on using this.

Is there something i did wrong ?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
PopHip
  • 700
  • 6
  • 23

2 Answers2

1

Running Libre office as a service is an extremely powerful way to run a system.

The (Universal Network Objects) UNO / Basic project is the back bone to this power.

The unoconv answer is solid and valid, however I guessed you might want to run a libre as a service.

You asked a couple of questions.

  1. How to debug a macro in Libre or Open Office

And

  1. Can you run a macro and save a file as xls (using Filter Options MS Excel 97 / xls format) ?

Here's the command

soffice --headless "macro:///Library1.Module4.OdsToXls(~/Desktop/Test/test1.ods)"

First of all I ran the macro from inside libre then I added the parameter to the procedure.

You could use MsgBox to debug

I've left the debug info in for you to look at.

sub OdsToXls(AbsoluteFileName as String)

REM IN SpreadSheetPath is the FULL PATH and file
REM AbsoluteFileName="/home/<user>/Desktop/Test/test1.ods"
REM AbsoluteFileName="~/Desktop/Test/test1.ods"

REM SaveAsFile 
rem ----------------------------------------------------------------------
rem define variables
Dim Doc As Object  
Dim Dummy()
dim dispatcher as object
dim PartURL as string
dim args1(1) as new com.sun.star.beans.PropertyValue
dim BaseFilename as string
rem used for testing
rem AbsoluteFileName="~/Desktop/Test/test1.ods"
PartURL="file:///"

rem ----------------------------------------------------------------------
rem get access to the document

If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then
  GlobalScope.BasicLibraries.LoadLibrary("Tools")
End If

rem content of an opened window can be replaced with the help of the frame parameter and SearchFlags:
SearchFlags = com.sun.star.frame.FrameSearchFlag.CREATE + com.sun.star.frame.FrameSearchFlag.ALL  


Url=ConvertToUrl(PartURL+AbsoluteFileName) 

rem MsgBox(Url)

Doc = StarDesktop.loadComponentFromURL(Url, "MyFrame", _SearchFlags, Dummy)



BaseFilename = Tools.Strings.GetFileNameWithoutExtension(Url)
rem msgBox(BaseFilename)

rem ----------------------------------------------------------------------

args1(0).Name = "URL"
args1(0).Value = BaseFilename+"."+"xls"
args1(1).Name = "FilterName"
args1(1).Value = "MS Excel 97"

Doc.StoreAsURL( args1(0).Value , args1())

Doc.close(true)

end sub

You could add to this by using a while loop on a directory and having an output directory.

If you're interested in scanning a directory and picking up all the files and processing them, then this may help you Stackoverflow Question: Saving specific Excel sheet as .csv

If you have any questions please feel free to ask.

Ref Filter_Options

All the best

  • Actually was a macro installation problem ( i'm working in docker ). So yes MsgBox can be very useful when your macro is already taken in account. But in case the macro is not installed and you try to use it, you won't get any error. Quite strange no ? – PopHip Jan 03 '20 at 08:35
  • Hi PopHip The Macro's should be in /home//.config/libreoffice/4/user/basic/Standard/ –  Jan 04 '20 at 13:39
  • yeah it's a custom install so the path was a bit different but the macro file was here. The probleme was in the file script.xlb where the macro was not correctly referenced ! – PopHip Jan 06 '20 at 08:03
  • Pleased you found the bug PopHip –  Jan 06 '20 at 16:02
  • Hi PopHip can you help other users find the solution ? https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work/5235#5235 –  Jan 08 '20 at 04:53
0

In your place, I would install unoconv and run, from the command line:

unoconv -o OUTPUT.xls MYFILE.ods

It is very fast, anyway faster than launching LibreOffice headless, as far as I could experiment.

Pierre François
  • 5,850
  • 1
  • 17
  • 38