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

You can use Python… add file example and help you with this…

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
	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
	dim document   as object
	dim dispatcher as object

	document   = oDoc.CurrentController.Frame
	dispatcher = createUnoService("")

	'	Apply formatting, these were recorded macros
rem ----------------------------------------------------------------------
dim args1(0) as new
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1:$D$1"

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

rem ----------------------------------------------------------------------
dim args2(0) as new
args2(0).Name = "Bold"
args2(0).Value = true

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

rem ----------------------------------------------------------------------
dim args3(0) as new
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
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.

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.

The --infilter arguments are similar to those used with unoconv. I found this OpenOffice page helpfull

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.

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

Organizing Libraries and Modules - LibreOffice Help and Extension Manager - LibreOffice Help

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