SFDocuments.Calc service

The SFDocuments shared library provides a number of methods and properties to facilitate the management and handling of LibreOffice documents.

The SFDocuments.Calc service is a subclass of the SFDocuments.Document service. All methods and properties defined for the Document service can also be accessed using a Calc service instance.

The Calc service is focused on:

Handling sheets within a Calc document (copy, insert, move, etc)
Exchanging data between Basic data structures and Calc ranges
Copying and importing massive amounts of data

This help page describes methods and properties that are applicable only to Calc documents.

Service invocation

Before using the Calc service the ScriptForge library needs to be loaded or imported:

• Basic macros require to load ScriptForge library using the following statement:
GlobalScope.BasicLibraries.loadLibrary("ScriptForge")

• Python scripts require an import from scriptforge module:
from scriptforge import CreateScriptService

The Calc service is closely related to the UI service of the ScriptForge library. Below are a few examples of how the Calc service can be invoked.

In Basic

The code snippet below creates a Calc service instance that corresponds to the currently active Calc document.


    Set oDoc = CreateScriptService("Calc")

Another way to create an instance of the Calc service is using the UI service. In the following example, a new Calc document is created and oDoc is a Calc service instance:


    Dim ui As Object, oDoc As Object
    Set ui = CreateScriptService("UI")
    Set oDoc = ui.CreateDocument("Calc")

Or using the OpenDocument method from the UI service:


    Set oDoc = ui.OpenDocument("C:\Documents\MyFile.ods")

It is also possible to instantiate the Calc service using the CreateScriptService method:


    Dim oDoc As Object
    Set oDoc = CreateScriptService("SFDocuments.Calc", "MyFile.ods")

In the example above, "MyFile.ods" is the name of an open document window. If this argument is not provided, the active window is considered.

It is recommended to free resources after use:


    Set oDoc = oDoc.Dispose()

However, if the document was closed using the CloseDocument method, it becomes unnecessary to free resources using the command described above.

In Python


    myDoc = CreateScriptService("Calc")


    ui = CreateScriptService("UI")
    myDoc = ui.CreateDocument("Calc")


    myDoc = ui.OpenDocument(r"C:\Documents\MyFile.ods")


    myDoc = CreateScriptService("SFDocuments.Calc", "MyFile.ods")
    myDoc.Dispose()

The use of the prefix "SFDocuments." while calling the service is optional.

Definitions

Many methods require a "Sheet" or a "Range" as argument. Single cells are considered a special case of a Range.

Both may be expressed either as a string or as a reference (= object) depending on the situation:

Within a specific Calc instance, sheets and ranges are given as strings such as "Sheet1" and "D2:F6".
Additionally, the .Sheet and .Range properties return a reference that may be used as argument of a method called from another instance of the Calc service.

Näide:

The example below copies data from document A (opened as read-only and hidden) to document B.

In Basic


    Dim oDocA As Object, oDocB As Object
    Set oDocA = ui.OpenDocument("C:\Documents\FileA.ods", Hidden := True, ReadOnly := True)
    Set oDocB = ui.OpenDocument("C:\Documents\FileB.ods")
    oDocB.CopyToRange(oDocA.Range("SheetX.D4:F8"), "D2:F6") 'CopyToRange(source, target)

In Python


    docA = ui.OpenDocument(r"C:\Documents\FileA.ods", hidden = True, readonly = True)
    docB = ui.OpenDocument(r"C:\Documents\FileB.ods")
    docB.CopyToRange(docA.Range("SheetX.D4:F8"), "D2:F6")

SheetName

Either the sheet name as a string or an object produced by the .Sheet property.

The shortcut "~" (tilde) represents the current sheet.

RangeName

Either a string designating a set of contiguous cells located in a sheet of the current instance or an object produced by the .Range property.

The shortcut "~" (tilde) represents the current selection or the first selected range if multiple ranges are selected.

The shortcut "*" represents all used cells.

The sheet name is optional when defining a range. If no sheet name is provided, then the active sheet is used. Surrounding single quotes and $ signs are allowed but ignored.

When specifying a SheetName as a string, the use of single quotes to enclose the sheet name are required if the name contains blank spaces " " or periods ".".

The examples below illustrate in which cases the use of single quotes is mandatory:


      ' The use of single quotes is optional
      oDoc.clearAll("SheetA.A1:B10")
      oDoc.clearAll("'SheetA'.A1:B10")
      ' The use of single quotes is required
      oDoc.clearAll("'Sheet.A'.A1:B10")

Except for the CurrentSelection property, the Calc service considers only single ranges of cells.

Examples of valid ranges
1) $'SheetX'.D2 2) $D$2	A single cell
1) $'SheetX'.D2:F6 2) D2:D10	Single range with multiple cells
$'SheetX'.*	All used cells in the given sheet
1) $'SheetX'.A:A (column A) 2) 3:5 (rows 3 to 5)	All cells in contiguous columns or rows up to the last used cell
myRange	A range named "myRange" at spreadsheet level
1) ~.someRange 2) SheetX.someRange	A range name at sheet level
myDoc.Range("SheetX.D2:F6")	A range within the sheet SheetX in file associated with the myDoc Calc instance
~.~ or ~	The current selection in the active sheet

Properties

All the properties generic to any document are implicitly applicable also to Calc documents. For more information, read the Document service Help page.

The properties specifically available for Calc documents are:

Name	Readonly	Argument	Return type	Description
CurrentSelection	No	None	String or array of strings	The single selected range as a string or the list of selected ranges as an array.
FirstCell	Yes	SheetName or RangeName as String	String	Returns the first used cell in a given range or sheet.
FirstColumn	Yes	SheetName or RangeName as String	Long	Returns the leftmost column number in a given range or sheet.
FirstRow	Yes	SheetName or RangeName as String	Long	Returns the topmost row number in a given range or sheet.
Height	Yes	RangeName As String	Long	The number of rows (>= 1) in the given range.
LastCell	Yes	SheetName or RangeName as String	String	Returns the last used cell in a given range or sheet.
LastColumn	Yes	SheetName or RangeName as String	Long	The last used column in a given range or sheet.
LastRow	Yes	SheetName or RangeName as String	Long	The last used row in a given range or sheet.
Range	Yes	RangeName As String	Object	A range reference that can be used as argument of methods like CopyToRange.
Region	Yes	RangeName As String	String	Returns the address of the smallest area that contains the specified range so that the area is surrounded by empty cells or sheet edges. This is equivalent to applying the shortcut to the given range.
Sheet	Yes	SheetName As String	Object	A sheet reference that can be used as argument of methods like CopySheet.
SheetName	Yes	RangeName As String	String	Returns the sheet name of a given range address.
Sheets	Yes	None	Array of strings	The list with the names of all existing sheets.
Width	Yes	RangeName As String	Long	The number of columns (>= 1) in the given range.
XCellRange	Yes	RangeName As String	Object	A com.sun.star.Table.XCellRange UNO object.
XSheetCellCursor	Yes	RangeName As String	Object	A com.sun.star.sheet.XSheetCellCursor UNO object. After moving the cursor, the resulting range address can be accessed through the AbsoluteName UNO property of the cursor object, which returns a string value that can be used as argument for properties and methods of the Calc service.
XSpreadsheet	Yes	SheetName As String	Object	A com.sun.star.sheet.XSpreadsheet UNO object.

Visit LibreOffice API Documentation's website to learn more about XCellRange, XSheetCellCursor and XSpreadsheet UNO objects.

Methods

List of Methods in the Calc Service
A1Style Activate Charts ClearAll ClearFormats ClearValues CompactLeft CompactUp CopySheet CopySheetFromFile CopyToCell CopyToRange CreateChart CreatePivotTable DAvg	DCount DMax DMin DSum ExportRangeToFile Forms GetColumnName GetFormula GetValue ImportFromCSVFile ImportFromDatabase InsertSheet MoveRange MoveSheet Offset	OpenRangeSelector PrintOut Printf RemoveSheet RenameSheet SetArray SetValue SetCellStyle SetFormula ShiftDown ShiftLeft ShiftRight ShiftUp SortRange

A1Style

Returns a range address as a string based on sheet coordinates, i.e. row and column numbers.

If only a pair of coordinates is given, then an address to a single cell is returned. Additional arguments can specify the bottom-right cell of a rectangular range.

Süntaks:

svc.A1Style(row1: int, column1: int, row2: int = 0; column2: int = 0; sheetname: str = "~"): str

Parameetrid:

row1, column1: Specify the row and column numbers of the top-left cell in the range to be considered. Row and column numbers start at 1.

row2, column2: Specify the row and column numbers of the bottom-right cell in the range to be considered. If these arguments are not provided, or if values smaller than row1 and column1 are given, then the address of the single cell range represented by row1 and column1 is returned.

sheetname: The name of the sheet to be appended to the returned range address. The sheet must exist. The default value is "~" corresponding to the currently active sheet.

Näide:

The examples below in Basic and Python consider that "Sheet1" is the currently active sheet.

In Basic


    Set oDoc = CreateScriptService("Calc")
    addr1 = oDoc.A1Style(1, 1) ' '$Sheet1'.$A$1
    addr2 = oDoc.A1Style(2, 2, 3, 6) ' '$Sheet1'.$B$2:$F$3
    addr3 = oDoc.A1Style(2, 2, 0, 6) ' '$Sheet1'.$B$2
    addr4 = oDoc.A1Style(3, 4, 3, 8, "Sheet2") ' '$Sheet2'.$D$3:$H$3
    addr5 = oDoc.A1Style(5, 1, SheetName := "Sheet3") ' '$Sheet3'.$A$5

In Python


    doc = CreateScriptService("Calc")
    addr1 = doc.A1Style(1, 1) # '$Sheet1'.$A$1
    addr2 = doc.A1Style(2, 2, 3, 6) # '$Sheet1'.$B$2:$F$3
    addr3 = doc.A1Style(2, 2, 0, 6) # '$Sheet1'.$B$2
    addr4 = doc.A1Style(3, 4, 3, 8, "Sheet2") # '$Sheet2'.$D$3:$H$3
    addr5 = doc.A1Style(5, 1, sheetname="Sheet3") # '$Sheet3'.$A$5

The method A1Style can be combined with any of the many properties and methods of the Calc service that require a range as argument, such as GetValue, GetFormula, ClearAll, etc.

Activate

If the argument sheetname is provided, the given sheet is activated and it becomes the currently selected sheet. If the argument is absent, then the document window is activated.

Süntaks:

svc.Activate(sheetname: str = ""): bool

Parameetrid:

sheetname: The name of the sheet to be activated in the document. The default value is an empty string, meaning that the document window will be activated without changing the active sheet.

Näide:

The example below activates the sheet named "Sheet4" in the currently active document.

In Basic


    Dim ui as Variant, oDoc as Object
    Set ui = CreateScriptService("UI")
    Set oDoc = ui.GetDocument(ui.ActiveWindow)
    oDoc.Activate("Sheet4")

In Python


    ui = CreateScriptService("UI")
    myDoc = ui.GetDocument(ui.ActiveWindow)
    myDoc.Activate("Sheet4")

Activating a sheet makes sense only if it is performed on a Calc document. To make sure you have a Calc document at hand you can use the isCalc property of the document object, which returns True if it is a Calc document and False otherwise.

Charts

Returns either the list with the names of all chart objects in a given sheet or a single Chart service instance.

If only sheetname is specified, a zero-based array of strings containing the names of all charts is returned.
If a chartname is provided, then a single object corresponding to the desired chart is returned. The specified chart must exist.

Süntaks:

svc.Charts(sheetname: str, chartname: str = ""): obj

Parameetrid:

sheetname: The name of the sheet from which the list of charts is to be retrieved or where the specified chart is located.

chartname: The user-defined name of the chart object to be returned. If the chart does not have a user-defined name, then the internal object name can be used. If this argument is absent, then the list of chart names in the specified sheet is returned.

Use the Navigator sidebar to check the names assigned to charts under the OLE objects category.