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
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 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.
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
https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options
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