SFDocuments.Calc service

The SFDocuments shared library provides a number of methods and properties to facilitate the management and handling of LibreOfficeDev 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
Simple cell ranges formatting

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 specifying a window name for 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 also possible to invoke the Calc service using the document referenced by ThisComponent. This is specially useful when running a macro from within the Basic IDE.


    Dim oDoc As Object
    Set oDoc = CreateScriptService("Calc", ThisComponent)

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()


    bas = CreateScriptService("Basic")
    myDoc = CreateScriptService("Calc", bas.ThisComponent)

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.
DefinedNames	Yes	None	String or array of strings	Returns the full sorted list of all named ranges in the document. The names defined in a single sheet are qualified with the sheet name.
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.
XRectangle	Yes	RangeName As String	Object	A com.sun.star.awt.Rectangle UNO structure, describing, in pixels, the area on the screen where the range is located.
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 LibreOfficeDev API Documentation's website to learn more about XCellRange, XSheetCellCursor and XSpreadsheet UNO objects.

Methods

List of Methods in the Calc Service
A1Style Activate AlignRange BorderRange Charts ClearAll ClearFormats ClearValues ColorizeRange CompactLeft CompactUp CopySheet CopySheetFromFile CopyToCell CopyToRange CreateChart CreatePivotTable DAvg	DCount DecorateFont DefineName DMax DMin DSum ExportRangeToFile FormatRange Forms GetColumnName GetFormula GetValue ImportFromCSVFile ImportFromDatabase ImportStylesFromFile InsertSheet Intersect MoveRange	MoveSheet Offset OpenRangeSelector PrintOut Printf RemoveDuplicates RemoveSheet RenameSheet SetArray SetCellStyle SetFormula SetValue 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