Ask Your Question
0

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

asked 2016-08-19 15:15:28 +0200

starfry gravatar image

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-08-19 18:01:00 +0200

mark_t gravatar image

updated 2016-08-20 00:00:35 +0200

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.

edit flag offensive delete link more

Comments

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.

starfry gravatar imagestarfry ( 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...

mark_t gravatar imagemark_t ( 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.

mark_t gravatar imagemark_t ( 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

mark_t gravatar imagemark_t ( 2016-08-19 18:49:11 +0200 )edit

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

starfry gravatar imagestarfry ( 2016-08-19 21:21:04 +0200 )edit
0

answered 2016-08-19 17:15:17 +0200

mauricio gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-08-19 15:15:28 +0200

Seen: 1,933 times

Last updated: Aug 20 '16