Lectura i escriptura de valors als intervals

Macros in LibreOffice Calc often need to read and write values from/to sheets. This help page describes the various approaches to accessing sheets and ranges to read or write their values.

All examples presented in this page can be implemented both in Basic and Python.

Accés a una única cel·la

The example below enters the numeric value 123 into cell "A1" of the current sheet.

    Dim oSheet as Object
    Dim oCell as Object
    oSheet = ThisComponent.CurrentController.getActiveSheet()
    oCell = oSheet.getCellRangeByName("A1")
    oCell.setValue(123)
  

El mateix es pot aconseguir en Python:

    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.getCurrentController().getActiveSheet()
    cell = sheet.getCellRangeByName("A1")
    cell.setValue(123)
  

Note that in the previous examples the cell is accessed using its range name "A1". It is also possible to access cells using indices as though the sheet were a matrix where columns and rows are indexed starting from zero.

This can be done using the getCellByPosition(colIndex, rowIndex) method, that takes in a column and a row index. The example below in Basic changes the text value in cell "C1" (column 2, row 0).

    oSheet = ThisComponent.CurrentController.getActiveSheet()
    oCell = oSheet.getCellByPosition(2, 0)
    oCell.setString("Hello")
  

This example can also be implemented in Python as follows:

    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.getCurrentController().getActiveSheet()
    cell = sheet.getCellByPosition(2, 0)
    cell.setString("Hello")
  

The main difference between Python and Basic scripts lies on how to get access to the sheet object by using the XSCRIPTCONTEXT context variable. After that, all methods and properties are identical in Basic and Python.

Valors, cadenes i fórmules

Les cel·les del Calc tenen tres tipus de valors: numèric, cadena i fórmula. Cada tipus té mètodes de lectura i escriptura propis:

Dates and currency values are considered as numeric values in Calc.

The following example enters numeric values into cells "A1" and "A2" and inserts a formula in cell "A3" that returns the multiplication of these values.

    oSheet = ThisComponent.CurrentController.getActiveSheet()
    oCell = oSheet.getCellRangeByName("A1")
    oCell.setValue(10)
    oCell = oSheet.getCellRangeByName("A2")
    oCell.setValue(20)
    oCell = oSheet.getCellRangeByName("A3")
    oCell.setFormula("=A1*A2")
  

Accés a intervals definits en altres fulls

The previous examples used only the active sheet to perform operations. It is possible to access cell ranges in different sheets by their indices or names.

The example below enters a numeric value into cell "A1" of the sheet named "Sheet2".

    oSheet = ThisComponent.Sheets.getByName("Sheet2")
    oCell = oSheet.getCellRangeByName("A1")
    oCell.setValue(123)
  

This example can also be implemented in Python as follows:

    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.Sheets["Sheet2"]
    cell = sheet.getCellRangeByName("A1")
    cell.setValue(123)
  

Sheets can also be accessed using zero-based indices indicating which sheet considering the order they appear in the Calc file.

In Basic, instead of using the getByName method, use Sheets(sheetIndex) as shown next:

    oSheet = ThisComponent.Sheets(0)
  

Això es pot fer d'una manera semblant en Python:

    sheet = doc.Sheets[0]
  

Ús de la biblioteca ScriptForge

El servei Calc de la biblioteca ScriptForge es pot usar per a obtindre i modificar el valors de les cel·les així:

    
' Carrega la biblioteca ScriptForge
    
GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")
    
' Obté accés al document del Calc actual
    
oDoc = CreateScriptService("Calc")
    
' Sets the value of cells A1 and A2
    
oDoc.setValue("A1", "Hello")
    
oDoc.setValue("A2", 123)
  

The setValue method can be used to set both numeric and text values. To set a cell formula, use the setFormula method.

With the Calc service, getting and setting cell values can be done with a single line of code. The example below gets the value from cell "A1" and shows it on a message box.

    Dim val as Variant, oDoc as Object
    oDoc = CreateScriptService("Calc")
    val = oDoc.getValue("A1")
    MsgBox val
  

La biblioteca ScriptForge també simplifica l'accés als intervals d'altres fulls, com es demostra a l'exemple següent:

    Dim val1, val2
    ' Gets cell "A1" from the sheet named "Sheet1"
    val1 = oDoc.getValue("Sheet1.A1")
    ' Gets cell "B3" from the sheet named "Sheet2"
    val2 = oDoc.getValue("Sheet2.B3")
    ' Places the result into cell "A1" of sheet "Report"
    Dim result : result = val1 * val2
    oDoc.setValue("Report.A1", result)
  

Els exemples anteriors també es poden implementar en Python així:

    from scriptforge import CreateScriptService
    doc = CreateScriptService("Calc")
    doc.setValue("A1", "Hello")
  

    doc = CreateScriptService("Calc")
    bas = CreateScriptService("Basic")
    val = doc.getValue("A1")
    bas.MsgBox(val)
  

    first_val = doc.getValue("Sheet1.A1")
    second_val = doc.getValue("Sheet2.B3")
    result = first_val * second_val
    doc.setValue("Report.A1", result)