Is it possible to format a LibreOffice spreadsheet using a shell script?

A bash shell script (on Linux) creates an .ods spreadsheet file (for LibreOffice) from a .csv using unoconv. It now needs to perform some formatting of the generated spreadsheet.

Examples of things it needs to do are

• set fonts and sizes
• make the first (header) row bold
• freeze the first (header) row
• perform a sort on two columns
• insert a column containg a formula
• add conditional formatting to columns
edit retag close merge delete

Sort by » oldest newest most voted

Maybe consider creating a macro in basic to do the formatting.

Instead of converting the csv file to an ods, and then formatting the ods file, you can start LibreOffice from command line or bash to open the csv file and then run your formatting macro.

You might be able to create most of your formatting using the macro recorder, although its sometimes better to write the macros in basic and not rely on the recorder.

Using this method from Windows bat file (which you can modify to run from bash).

"C:\Program Files\LibreOffice 5\program\soffice" --headless --infilter="Text - txt - csv (StarCalc):44,34,0,1,1/1" "Untitled 10.csv" "macro:///FormatCSVtoODS.FormatCSVtoODS.FormatCSVtoODS()"


Above line will load the csv to LO and then run the FormatCSVtoODS sub in the Library and Module of the same name.

Example below will only set the top row to bold and freeze the top row of the sheet.

REM  *****  BASIC  *****

Option Explicit

Sub FormatCSVtoODS()

Dim oDoc As Object
Dim oSheet As Object
Dim Args(0) As New com.sun.star.beans.PropertyValue
Dim sDirectory As String
Dim sFileName As String

oDoc = thisComponent

sFileName = oDoc.Title
sDirectory = replace(oDoc.Location, "%20", " " )    '   Allow for spaces in name as %20
sDirectory = replace(sDirectory, sFileName, "" )

sFileName = left(sFileName, instr(sFileName,".") - 1)

oSheet = oDoc.Sheets(0)

'   Make the sheet active
oDoc.getcurrentController.setActiveSheet(oSheet)

dim document   as object
dim dispatcher as object

document   = oDoc.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

'   Apply formatting, these were recorded macros

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1:$D$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Bold"
args2(0).Value = true

dispatcher.executeDispatch(document, ".uno:Bold", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$A$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:FreezePanes", "", 0, Array())

'   End of formatting

'   Save the file as calc .ods format
Args(0).Name = "Overwrite"
Args(0).value = false

On Error Resume Next
oDoc.storeAsURL( sDirectory & sFileName & ".ods", Args)
On Error Goto 0

thisComponent.Close(true)
End Sub


Edit to add alternate method: The basic macro could be included in a separate .ods file, but then you probably don't want to use --headless option as you need to accept the file has macros, or always enable macros which is probably a bad idea.

Attached example.FormatCSVtoODS.ods

Call from windows bat file is changed to:

"C:\Program Files\LibreOffice 5\program\soffice" "D:\UserData\Mark\Desktop\Calc\FormatCSVtoODS.ods" "macro://FormatCSVtoODS/Standard.FormatCSVtoODS.FormatCSVtoODS(Untitled 10.csv)"


A better method might be to put all the function of the bash script into the ods file and create a user interface in LibreOffice basic to select the files to process.

more

Thanks for that. I have a couple of questions... Can you explain the infilter arguments - perhaps it't similar to the 124,34,utf-8 I used with unoconv (124=pipe delimiter that I used). Also, can you explain the library bit (sorry not that familar with LO) - I would prefer to define the macro in a file if I can't do it as command-line arguments.

( 2016-08-19 18:08:35 +0200 )edit

The --infilter arguments are similar to those used with unoconv. I found this OpenOffice page helpfull https://wiki.openoffice.org/wiki/Docu...

( 2016-08-19 18:33:14 +0200 )edit

To see the libraries from calc, use Tools, Macros, Organise Macros, LibreOffice Basic... You should see Standard library under the MyMacros and also in each open document. You can put macros in My Macros, Standard that will then be available to the user in any document that is opened. If you create a separate library instead of using Standard then it can be exported as an extension and easily loaded by other users by double clicking the .oxt file. I'll attach example to my answer.

( 2016-08-19 18:43:09 +0200 )edit

Sorry, it seems .oxt files can not be attached, possibly due to security risks. I'll try and find a link to documentation

( 2016-08-19 18:49:11 +0200 )edit
( 2016-08-19 18:54:35 +0200 )edit

Thanks @mark_t. It makes it a bit impractical if the macros cannot be portable with the script.

( 2016-08-19 21:21:04 +0200 )edit