Jump to content

Macros/ScriptForge/Learning ScriptForge

From The Document Foundation Wiki


Beginner's guide to programming with ScriptForge

This syllabus covers the most important concepts, from the basics to advanced tasks, allowing for gradual learning on how to program scripts and macros in LibreOffice using Python or BASIC. Each step includes a practical example that gradually increases in complexity.

Introduction to ScriptForge and environment setup

Get familiar with ScriptForge Help pages, and set up the development environment. In fact, ScriptForge is quite easy to understand. It's a matter of loading the library and creating the appropriate services, depending on what you will need. Then, using objects is simple and intuitive.

There are 2 types of services in ScriptForge:

  • Singletons: the exact list is Array, Exception, FileSystem, Platform, Region, Session, String, and Basic (for Python scripts, only). They are services that encompass generic features.
  • Instances: Document, Menu, Dialog, etc. With these services you can have several documents or dialogs, and these can be open/active at the same time.

Is SF an object-oriented framework?

Forks! Yes, in the same way that any OO programming language is. That means it is possible to get and set the properties of objects, or run its methods, and inherit the properties and methods of parent objects.

Initial load of ScriptForge library

  • In Basic, the loading has to happen at least once per LO session. However multiplying the statement in each Sub/Function is harmless, if you cannot avoid it.
  • In Python, the import statement has to be done exactly once per module. Preferably, add the statement that loads the ScriptForge library at the beginning of your code:

REM (put the following line inside your Sub or Function)
GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
# This statement goes at the top of your Python module.
from scriptforge import CreateScriptService

Later on, and depending on the functionality of your macro or script, you can create the corresponding instances of the ScriptForge services you will require.

Only a Python def can be run from LibreOffice, with the condition that it is listed in the g_exportedScripts tuple. Usually you put this statement at the end of the module or file that stores all related functions. The def names in that tuple are those visible in LibreOffce. The Import PDF data into a Calc spreadsheet example shows how the Python code must be structured to accomplish this.

How to debug with ScriptForge

ScriptForge has great tools for help code debugging Basic macros and Python scripts, and also for error handling Basic scripts. Create the Exception service to access those tools.

In BASIC, to help debugging, the IDE is capable enough to perform many basic debugging functions including breakpoints and a watch window. In addition, the ScriptForge Console can provide debugging support, as well, especially when coding event processing.

But for Python scripts, which can be run within or outside the LibreOffice process, the terminal[1] is a great helper. However, many scripts can be debugged with tools that ScriptForge already provide, such as the ScriptForg Console[2]. Another tool you can use is the MsgBox dialog, identical to the BASIC built-in one. You must load the ScriptForge Basic service and create the instance that calls it:

from scriptforge import CreateScriptService

bas = CreateScriptService("Basic")
exc = CreateScriptService("Exception")

def debug():
	bas.MsgBox('Hello, world!')
	someVar = 100
	exc.DebugPrint('Value of someVar', someVar) # this sends the values to the SF console
	exc.Console() # this displays the SF console
	exc.PythonShell({**globals(), **locals()}) # this opens the APSO shell
	print('Value of someVar', someVar) # the values are sent to the APSO shell


g_exportedScripts = (debug,)

if __name__ == "__main__":
	debug()

The MsgBox is native in BASIC. But in Python scripts, you need to call the ScriptForge Basic service.

Recommended extensions to help debugging

If you debug BASIC macros or Python scripts, the XRay[3] extension can be a great help, specially because it stops the execution until the dialog closes.

If you will be running Python scripts from within the same LibreOffice process, it is highly recommended to install and use the APSO[4] extension for easier and more efficient debugging and management.

In case you will need to explore UNO objects thoroughly, install the MRI[5] extension, and then add a line at the beginning of the script:

import mri

Attention

Remember that with both BASIC and Python, running the macro or script can be achieved via different launchers (buttons, events, hot keys or menus, ...).

Open and format a CSV into a Calc sheet

In this simple example, we are going to load a CSV file which contains the monthly bank statements from an organization. The bank provides the CSV with comma character as columns-delimiter. The dates columns are in D/M/Y format. This CSV contains numerous columns, not all of them are of our interest. Also, we want to add the data already preformatted according to the content of the file (text as text, dates as D/M/Y, income and outcome columns as numbers).

Just as an example of the usefulness of MsgBox, we are displaying which file naming is used in the system.

The variables used are:

  • fs: instance of FileSystem service, in order to explore the file naming system
  • bas: instance of Basic service, helps to display the MsgBox dialog
  • ui: instance of UI service, the "desktop" analogy to open a new Calc document where to import CSV data
  • ss: instance of Calc service, to manage the content of a spreadsheet Calc document

The PickFile function opens a dialog where the user picks a file from the system and returns the name of the file to be loaded in the new Calc document.

The ImportFromCSVFile function returns a String object, which represents the modified range of cells. The returned cell range will start in cell A1. The filter options[6] provided to this function tells SF to use comma as delimiter (44), double quote as text delimiter (34), UTF-8 as character set (76), first line to start importing data (1), and cell format codes for each column (the rest of the filter options: 9=do not import column, 4=dates as D/M/Y, etc.).

The columns that will be imported are just "FECHA DE OPERACIÓN" (date as D/M/Y), "FECHA" (date as D/M/Y), "DESCRIPCIÓN" (text), "DEPÓSITOS" (standard), "RETIROS" (standard), and "DESCRIPCIÓN DETALLADA" (text). Other columns will be discarded.

It is not mandatory, but is often a good practice to group all singleton services at module level (see Python example):

Code

Sub openNFormatCSV
    GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
	
	Dim ui As Object, fs As Object, ss As Object
	
    ui  = CreateScriptService("UI")
    fs = CreateScriptService("FileSystem")

    MsgBox(fs.FileNaming, MB_OK)
    ss = ui.CreateDocument("Calc")
    ss.ImportFromCSVFile( fs.PickFile("OPEN"), "A1", _
"44,34,76,1,1/9/2/3/3/3/4/9/5/1/6/9/7/9/8/10/9/10/10/9/11/9/12/2/13/9,'10',false,false,,,,,false,false,false")
End Sub
from scriptforge import CreateScriptService
# Remember: Basic service is only useful (and is limited) to Python scripts!
fs  = CreateScriptService("FileSystem")
bas = CreateScriptService("Basic")
ui  = CreateScriptService("UI")

def openNformatCSV():
    ss = ui.CreateDocument("Calc")

    bas.MsgBox(fs.FileNaming, bas.MB_OK) # Just to check which kind of file notation the user is using
    ss.ImportFromCSVFile(fs.PickFile("OPEN"), "A1", "44,34,76,1,1/9/2/3/3/3/4/9/5/1/6/9/7/9/8/10/9/10/10/9/11/9/12/2/13/9,'10',false,false,,,,,false,false,false")


g_exportedScripts = (openNformatCSV, )

Other resources

Import PDF data into a Calc spreadsheet

In this example, a variation of the Base Guide example made in BASIC, we will import data from multiple PDF filled forms (though this detail is not critical for the code itself; the original form created in LibreOffice Writer is available here: ODT file for a simple form). The data will be loaded into a Calc spreadsheet. As part of this example, five .txt sample files are provided to test the code and learn: ZIP containing 5 dumped data .txt files from five corresponding PDF simple forms. One Calc document is required to run this macro.

Unfortunately, PDF format makes no distinction between numeric fields, date fields, and text fields. For the example provided here, it is sufficient to use text fields for all the entries. Other field formats within the Writer form will inevitably be lost during PDF dump of data.

All the PDF form files must reside in the same directory for this macro/script to work. To dump data from PDF files into temporary .txt files, we use the open source program pdftk.[7] Each field of the PDF form is represented by a record of five to six lines in the dump data file. For our code to work, for each record (each PDF form field), the important lines are FieldName, and FieldValue (the actual content of the field after saving the PDF file). The whole import process is controlled by our code. The records are read out of it into the text file, and then read into the database from this text file. This continues for all the PDF files in the folder. In order the code to be more functional, old fulfilled PDF forms and .txt files should be removed from the folder as far as possible, because the function does not check for data duplication.

The pdftk program dumps each PDF file received with data with the following command:

   $ pdftk simple-form.pdf dump_data_fields_utf8 >simple-form.txt

Each simple-form.txt file has the following structure:

--- FieldType: Text FieldName: txtApellido FieldFlags: 0 FieldValue: Díaz Palacios FieldJustification: Left --- FieldType: Choice FieldName: lstEntFed FieldFlags: 131072 FieldValue: Ciudad de México FieldJustification: Left FieldStateOption: Aguascalientes ... (more FieldStateOption lines) FieldStateOption: Zacatecas --- FieldType: Button FieldName: chkSexoF FieldFlags: 0 FieldValue: No FieldValueDefault: Off FieldJustification: Left FieldStateOption: Off FieldStateOption: Yes

Our code will read each .txt file in a directory, looping through each record, reading and saving the FieldName and FieldValue values, and then it will create a new Calc spreadsheet with a table from those values.

Attention:

Selecting a folder with a single irrelevant .txt file results in saving a faulty document, if the current doc is Calc. This situation should be prevented in real, production environments.

Code

Do not forget to put a tuple at the end of your code, so LibreOffice will be able to see the selected, exported def:

   g_exportedScripts = (data_extract, )

from scriptforge import CreateScriptService
# Crear servicio de ScriptForge para diálogos de archiv
exc = CreateScriptService("Exception")
bas = CreateScriptService("Basic")
fs = CreateScriptService("FileSystem")
ui = CreateScriptService("UI")

home = fs.HomeFolder

def nombres_arch():
    # Solicitar al usuario que seleccione el directorio donde están los archivos .txt
    dir_selecc = fs.PickFolder(home, freetext = "Pick a folder or press Cancel")

    # bas.MsgBox("Selected folder is: \n" + dir_selecc, bas.MB_ICONINFORMATION, "CONFIRMATION MESSAGE")

    # Si el usuario cancela, salir
    if dir_selecc == '' :
        bas.MsgBox("Operation cancelled by user", bas.MB_ICONINFORMATION, "WARNING")
        return []

    # Esta lista recupera todos los archivos .txt que encuentra en el directorio seleccionado
    lista_archivos = fs.Files(dir_selecc, filter = "*.txt", includesubfolders = False)

    return lista_archivos


def data_extract():
    # exc.Console(modal = False)

    todos_formularios = {}
    archivos = nombres_arch()
    for archivo in archivos:
        a = fs.OpenTextFile(archivo, fs.ForReading)
        lineastodas = a.ReadAll()
        lineas = lineastodas.split(a.NewLine)
        datos = []
        for l in lineas:
            if l.startswith("FieldName: "):
                datos.append(l.lstrip("FieldName:  "))
            if l.startswith("FieldValue: "):
                datos.append(l.lstrip("FieldValue:  "))

        este_formulario = {}
        este_formulario = dict(zip(datos[::2], datos[1::2]))

        for k,v in este_formulario.items():
            todos_formularios.setdefault(k,[]).append(v)

        a.CloseFile()


    hojaCalc = ui.CreateDocument("Calc")

    titulosCalc = list(todos_formularios.keys())
    columna=1
    for v in titulosCalc:
        anclaje = hojaCalc.A1Style(1, columna)
        hojaCalc.SetValue(anclaje, v)
        columna+=1

    columna=1
    for k,v in todos_formularios.items():
        anclaje = hojaCalc.A1Style(2,columna)
        hojaCalc.SetArray(anclaje, todos_formularios[k])
        columna+=1

    filename = fs.PickFile(fs.BuildPath(home, "outputfile"), "SAVE", "ods")
    if len(filename) > 0:
        hojaCalc.SaveAs(filename, overwrite = True)

    hojaCalc.Dispose()


g_exportedScripts = (data_extract, )

How to display a chart in a dialog

The chart is built completely from scratch. Data is extracted from a database, and stored in a Calc sheet. A pivot table and a chart are derived from the data. The chart is stored in a file for loading in the dialog. This example is a creative way to show these services in action altogether: Dialog, DialogControl, Calc, Chart, and Database.

Go to: How to display a chart in a dialog.

Compound dialogs with ScriptForge: How to create a clone of BASIC macro organizer

The objective is to illustrate that tabbed dialogs can be designed with ScriptForge in a modular eased way. This reduces the need to develop dynamic dialogs scripts/macros —such as those using com.sun.star.awt.tab services— that require numerous and tedious graphical adjustments. The services used in this example are Dialog, and DialogControl.

Go to: Compound dialogs with ScriptForge: How to create a clone of BASIC macro organizer.

See also