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.

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.

    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.

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

    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)
  

    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.

Ominaisuudet

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:

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

Methods

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.

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

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.

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

    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
  

    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.

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

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.

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

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

    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.

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

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.

The example below shows the number of chart objects in "Sheet1".

    Dim arrNames as Object
    arrNames = oDoc.Charts("Sheet1")
    MsgBox "There are " & UBound(arrNames) + 1 & " charts in Sheet1"
  

The following example accesses the chart named "MyChart" in "Sheet1" and prints its type.

    Dim oChart as Object
    oChart = oDoc.Charts("Sheet1", "MyChart")
    MsgBox oChart.ChartType
  

    bas = CreateScriptService("Basic")
    chart_names = doc.Charts("Sheet1")
    bas.MsgBox(f"There are {len(chart_names)} charts in Sheet1")
  

    chart = doc.Charts("Sheet1", "MyChart")
    bas.MsgBox(chart.ChartType)
  

ClearAll

Clears all the contents and formats of the given range.

A filter formula can be specified to determine which cells shall be affected.

svc.ClearAll(range: str, opt filterformula: str, opt filterscope: str)

range: The range to be cleared, as a string.

filterformula: A Calc formula that shall be applied to the given range to determine which cells will be affected. The specified formula must return True or False. If this argument is not specified, then all cells in the range are affected.

filterscope: Determines how filterformula is expanded to the given range. This argument is mandatory if a filterformula is specified. The following values are accepted:

• "CELL": The formula specified in the filterformula argument is expanded once for each cell in range.

• "ROW": The formula specified in the filterformula argument is expanded once for each row in range.

• "COLUMN": The formula specified in the filterformula argument is expanded once for each column in range.

    ' Clears all cells in the range SheetX.A1:J10
    oDoc.ClearAll("SheetX.A1:J10")
    ' Clears all cells in the range SheetX.A1:J10 that have a value greater than 100
    oDoc.ClearAll("SheetX.A1:J10", "=SheetX.A1>100", "CELL")
    ' Clears all rows in the range SheetX.A1:J10 whose sum is greater than 500
    oDoc.ClearAll("SheetX.A1:J10", "=SUM(SheetX.A1:J1)>100", "ROW")
    ' Clears all columns in the range SheetX.A1:J10 whose sum is greater than 500
    oDoc.ClearAll("SheetX.A1:J10", "=SUM(SheetX.A1:A10)>100", "COLUMN")
  

    myDoc.ClearAll("SheetX.A1:F10")
    myDoc.ClearAll("SheetX.A1:J10", "=SheetX.A1>100", "CELL")
    myDoc.ClearAll("SheetX.A1:J10", "=SUM(SheetX.A1:J1)>100", "ROW")
    myDoc.ClearAll("SheetX.A1:J10", "=SUM(SheetX.A1:A10)>100", "COLUMN")
  

ClearFormats

Clears the formats and styles in the given range.

A filter formula can be specified to determine which cells shall be affected.

svc.ClearFormats(range: str, opt filterformula: str, opt filterscope: str)

range: The range whose formats and styles are to be cleared, as a string.

      oDoc.ClearFormats("SheetX.*")
  

    myDoc.ClearFormats("SheetX.*")
  

Refer to the ClearAll method documentation for examples on how to use the arguments filterformula and filterscope.

ClearValues

Clears the values and formulas in the given range.

A filter formula can be specified to determine which cells shall be affected.

svc.ClearValues(range: str, opt filterformula: str, opt filterscope: str)

range: The range whose values and formulas are to be cleared, as a string.

      oDoc.ClearValues("SheetX.A1:F10")
  

    myDoc.ClearValues("SheetX.A1:F10")
  

Refer to the ClearAll method documentation for examples on how to use the arguments filterformula and filterscope.

CompactLeft

Deletes the columns of a specified range that match a filter expressed as a Calc formula. The filter is applied to each column to decide whether it will be deleted or not.

The deleted column can be limited to the height of the specified range or span to the height of the entire sheet, thus deleting whole columns.

This method returns a string with the range address of the compacted range. If all columns are deleted, then an empty string is returned.

If a range of cells is selected, calling this method will not impact the selection.

svc.CompactLeft(range: str, wholecolumn: bool = False, opt filterformula: str): str

range: The range from which columns will be deleted, as a string.

wholecolumn: If this option is set to True the entire column will be deleted from the sheet. The default value is False, which means that the deleted column will be limited to the height of the specified range.

filterformula: The filter to be applied to each column to determine whether or not it will be deleted. The filter is expressed as a Calc formula that should be applied to the first column. When the formula returns True for a column, that column will be deleted. The default filter deletes all empty columns.

For example, suppose range A1:J200 is selected (height = 200), so the default formula is =(COUNTBLANK(A1:A200)=200). This means that if all 200 cells are empty in the first column (Column A), then the column is deleted. Note that the formula is expressed with respect to the first column only. Internally the CompactLeft method will generalize this formula for all the remaining columns.

Calc functions used in the filterformula argument must be expressed using their English names. Visit the Wiki page List of Calc Functions for a complete list of Calc functions in English.

    ' Delete all empty columns in the range G1:L10 from Sheet1
    newrange = oDoc.CompactLeft("Sheet1.G1:L10")
    ' The example below is similar, but the entire column is deleted from the sheet
    newrange = oDoc.CompactLeft("Sheet1.G1:L10", WholeColumn := True)
    ' Deletes all columns where the first row is marked with an "X"
    newrange = oDoc.CompactLeft("Sheet1.G1:L10", FilterFormula := "=(G1=""X"")")
    ' Deletes all columns where the sum of values in the column is odd
    newrange = oDoc.CompactLeft("Sheet1.G1:L10", FilterFormula := "=(MOD(SUM(G1:G10);2)=1)")
  

    newrange = myDoc.CompactLeft("Sheet1.G1:L10")
    newrange = myDoc.CompactLeft("Sheet1.G1:L10", wholecolumn = True)
    newrange = myDoc.CompactLeft("Sheet1.G1:L10", filterformula = '=(G1="X")')
    newrange = myDoc.CompactLeft("Sheet1.G1:L10", filterformula = '=(MOD(SUM(G1:G10);2)=1)')
  

CompactUp

Deletes the rows of a specified range that match a filter expressed as a Calc formula. The filter is applied to each row to decide whether it will be deleted or not.

The deleted rows can be limited to the width of the specified range or span to the width of the entire sheet, thus deleting whole rows.

This method returns a string with the range address of the compacted range. If all rows are deleted, then an empty string is returned.

If a range of cells is selected, calling this method will not impact the selection.

svc.CompactUp(range: str, wholerow: bool = False, opt filterformula: str): str

range: The range from which rows will be deleted, as a string.

wholerow: If this option is set to True the entire row will be deleted from the sheet. The default value is False, which means that the deleted row will be limited to the width of the specified range.

filterformula: The filter to be applied to each row to determine whether or not it will be deleted. The filter is expressed as a Calc formula that should be applied to the first row. When the formula returns True for a row, that row will be deleted. The default filter deletes all empty rows.

For example, suppose range A1:J200 is selected (width = 10), so the default formula is =(COUNTBLANK(A1:J1)=10). This means that if all 10 cells are empty in the first row (Row 1), then the row is deleted. Note that the formula is expressed with respect to the first row only. Internally the CompactUp method will generalize this formula for all the remaining rows.

The Calc functions used in the formula specified in the filterformula argument must be expressed using their English names. Visit the Wiki page List of Calc Functions for a complete list of Calc functions in English.

    ' Delete all empty rows in the range G1:L10 from Sheet1
    newrange = oDoc.CompactUp("Sheet1.G1:L10")
    ' The example below is similar, but the entire row is deleted from the sheet
    newrange = oDoc.CompactUp("Sheet1.G1:L10", WholeRow := True)
    ' Deletes all rows where the first column is marked with an "X"
    newrange = oDoc.CompactUp("Sheet1.G1:L10", FilterFormula := "=(G1=""X"")")
    ' Deletes all rows where the sum of values in the row is odd
    newrange = oDoc.CompactUp("Sheet1.G1:L10", FilterFormula := "=(MOD(SUM(G1:L1);2)=1)")
  

    newrange = myDoc.CompactUp("Sheet1.G1:L10")
    newrange = myDoc.CompactUp("Sheet1.G1:L10", wholerow = True)
    newrange = myDoc.CompactUp("Sheet1.G1:L10", filterformula = '=(G1="X")')
    newrange = myDoc.CompactUp("Sheet1.G1:L10", filterformula = '=(MOD(SUM(G1:L1);2)=1)')
  

CopySheet

Copies a specified sheet before an existing sheet or at the end of the list of sheets. The sheet to be copied may be contained inside any open Calc document. Returns True if successful.

svc.CopySheet(sheetname: any, newname: str, [beforesheet: any]): bool

sheetname: The name of the sheet to be copied as a string or its reference as an object.

newname: The name of the sheet to insert. The name must not be in use in the document.

beforesheet: The name (string) or index (numeric, starting from 1) of the sheet before which to insert the copied sheet. This argument is optional and the default behavior is to add the copied sheet at the last position.

The following example makes a copy of the sheet "SheetX" and places it as the last sheet in the current document. The name of the copied sheet is "SheetY".

    Dim oDoc as Object
    'Gets the Document object of the active window
    Set oDoc = CreateScriptService("Calc")
    oDoc.CopySheet("SheetX", "SheetY")
  

The example below copies "SheetX" from "FileA.ods" and pastes it at the last position of "FileB.ods" with the name "SheetY":

      Dim oDocA As Object : Set oDocA = ui.OpenDocument("C:\Documents\FileA.ods", Hidden := True, ReadOnly := True)
      Dim oDocB As Object : Set oDocB = ui.OpenDocument("C:\Documents\FileB.ods")
      oDocB.CopySheet(oDocA.Sheet("SheetX"), "SheetY")
  

    myDoc.CopySheet("SheetX", "SheetY")
  

    docA = ui.OpenDocument(r"C:\Documents\FileA.ods", hidden = True, readonly = True)
    docB = ui.OpenDocument(r"C:\Documents\FileB.ods")
    docB.CopySheet(docA.Sheet("SheetX"), "SheetY")
  

To copy sheets between open documents, use CopySheet. To copy sheets from documents that are closed, use CopySheetFromFile.

CopySheetFromFile

Copies a specified sheet from a closed Calc document and pastes it before an existing sheet or at the end of the list of sheets of the file referred to by a Document object.

If the file does not exist, an error is raised. If the file is not a valid Calc file, a blank sheet is inserted. If the source sheet does not exist in the input file, an error message is inserted at the top of the newly pasted sheet.

svc.CopySheetFromFile(filename: str, sheetname: str, newname: str, [beforesheet: any]): bool

filename: Identifies the file to open. It must follow the SF_FileSystem.FileNaming notation. The file must not be protected with a password.

sheetname: The name of the sheet to be copied as a string.

newname: The name of the copied sheet to be inserted in the document. The name must not be in use in the document.

beforesheet: The name (string) or index (numeric, starting from 1) of the sheet before which to insert the copied sheet. This argument is optional and the default behavior is to add the copied sheet at the last position.

The following example copies "SheetX" from "myFile.ods" and pastes it into the document referred to by "oDoc" as "SheetY" at the first position.

    oDoc.CopySheetFromFile("C:\Documents\myFile.ods", "SheetX", "SheetY", 1)
  

    myDoc.CopySheetFromFile(r"C:\Documents\myFile.ods", "SheetX", "SheetY", 1)
  

CopyToCell

Copies a specified source range (values, formulas and formats) to a destination range or cell. The method reproduces the behaviour of a Copy/Paste operation from a range to a single cell.

It returns a string representing the modified range of cells. The size of the modified area is fully determined by the size of the source area.

The source range may belong to another open document.

svc.CopyToCell(sourcerange: any, destinationcell: str): str

sourcerange: The source range as a string when it belongs to the same document or as a reference when it belongs to another open Calc document.

destinationcell: The destination cell where the copied range of cells will be pasted, as a string. If a range is given, only its top-left cell is considered.

Next is an example where the source and destination are in the same file:

      oDoc.CopyToCell("SheetX.A1:F10", "SheetY.C5")
  

The example below illustrates how to copy a range from another open Calc document:

    Dim ui as Variant : ui = CreateScriptService("UI")
    Dim oDocSource As Object, oDocDestination As Object
    'Open the source document in the background (hidden)
    Set oDocSource = ui.OpenDocument("C:\SourceFile.ods", Hidden := True, ReadOnly := True)
    Set oDocDestination = CreateScriptService("Calc")
    oDocDestination.CopyToCell(oDocSource.Range("Sheet1.C2:C4"), "SheetT.A5")
    'Do not forget to close the source document because it was opened as hidden
    oDocSource.CloseDocument()
  

    docSource = ui.OpenDocument(r"C:\Documents\SourceFile.ods", hidden = True, readonly = True)
    docDestination = CreateScriptService("Calc")
    docDestination.CopyToCell(docSource.Range("Sheet1.C2:C4"), "SheetT.A5")
    docSource.CloseDocument()
  

To simulate a Copy/Paste from a range to a single cell, use CopyToCell. To simulate a Copy/Paste from a range to a larger range (with the same cells being replicated several times), use CopyToRange.

CopyToRange

Copies downwards and/or rightwards a specified source range (values, formulas and formats) to a destination range. The method imitates the behaviour of a Copy/Paste operation from a source range to a larger destination range.

• If the height (or width) of the destination area is > 1 row (or column) then the height (or width) of the source must be <= the height (or width) of the destination. Otherwise nothing happens.

• If the height (or width) of the destination is = 1 then the destination is expanded downwards (or rightwards) up to the height (or width) of the source range.

The method returns a string representing the modified range of cells.

The source range may belong to another open document.

svc.CopyToRange(sourcerange: any, destinationrange: str): str

sourcerange: The source range as a string when it belongs to the same document or as a reference when it belongs to another open Calc document.

destinationrange: The destination of the copied range of cells, as a string.

Copy within the same document:

    oDoc.CopyToRange("SheetX.A1:F10", "SheetY.C5:J5")
    ' Returns a range string: "$SheetY.$C$5:$J$14"
  

Copy from one file to another:

    Dim oDocA As Object : Set oDocA = ui.OpenDocument("C:\Documents\FileA.ods", Hidden := True, ReadOnly := True)
    Dim oDocB As Object : Set oDocB = ui.OpenDocument("C:\Documents\FileB.ods")
    oDocB.CopyToRange(oDocA.Range("SheetX.A1:F10"), "SheetY.C5:J5")
  

    doc.CopyToRange("SheetX.A1:F10", "SheetY.C5:J5")
  

    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.A1:F10"), "SheetY.C5:J5")
  

CreateChart

Creates a new chart object showing the data in the specified range. The returned chart object can be further manipulated using the Chart service.

svc.CreateChart(chartname: str, sheetname: str, range: str, columnheader: bool = False, rowheader: bool = False): obj

chartname: The user-defined name of the chart to be created. The name must be unique in the same sheet.

sheetname: The name of the sheet where the chart will be placed.

range: The range to be used as the data source for the chart. The range may refer to any sheet of the Calc document.

columnheader: When True, the topmost row of the range is used as labels for the category axis or the legend (Default = False).

rowheader: When True, the leftmost column of the range is used as labels for the category axis or the legend. (Default = False).

The examples below in Basic and Python create a chart using the data contained in the range "A1:B5" of "Sheet1" and place the chart in "Sheet2".

    Set oChart = oDoc.CreateChart("MyChart", "Sheet2", "Sheet1.A1:B5", RowHeader := True)
    oChart.ChartType = "Donut"
  

    chart = doc.CreateChart("MyChart", "Sheet2", "Sheet1.A1:B5", rowheader=True)
    chart.ChartType = "Donut"
  

Refer to the help page about ScriptForge's Chart service to learn more how to further manipulate chart objects. It is possible to change properties as the chart type, chart and axes titles and chart position.

CreatePivotTable

Creates a new pivot table with the properties defined by the arguments passed to the method.

A name must be provided for the pivot table. If a pivot table with the same name already exists in the targeted sheet, it will be replaced without warning.

This method returns a string containing the range where the new pivot table was placed.

svc.CreatePivotTable(pivottablename: str, sourcerange: str, targetcell: str, datafields: str[0..*], rowfields: str[0..*], columnfields: str[0..*], filterbutton: bool = true, rowtotals: bool = true, columntotals: bool = true): str

pivottablename: The user-defined name of the new pivot table.

sourcerange: The range containing the raw data, as a string. It is assumed that the first row contains the field names that are used by the pivot table.

targetcell: The top-left cell where the new pivot table will be placed. If a range is specified, only its top-left cell is considered.

datafields: It can be either a single string or an array containing strings that define field names and functions to be applied. When an array is specified, it must follow the syntax Array("FieldName[;Function]", ...).

The allowed functions are: Sum, Count, Average, Max, Min, Product, CountNums, StDev, StDevP, Var, VarP and Median. Function names must be provided in English. When all values are numerical, Sum is the default function, otherwise the default function is Count.

rowfields: A single string or an array with the field names that will be used as the pivot table rows.

columnfields: A single string or an array with the field names that will be used as the pivot table columns.

filterbutton: Determines whether a filter button will be displayed above the pivot table (Default = True).

rowtotals: Specifies if a separate column for row totals will be added to the pivot table (Default = True).

columntotals Specifies if a separate row for column totals will be added to the pivot table (Default = True)

    Dim vData As Variant, oDoc As Object, ui As Object, sTable As String, sPivot As String
    Set ui = CreateScriptService("UI")
    Set oDoc = ui.CreateDocument("Calc")
    vData = Array(Array("Item", "State", "Team", "2002", "2003", "2004"), _
        Array("Books", "Michigan", "Jean", 14788, 30222, 23490), _
        Array("Candy", "Michigan", "Jean", 26388, 15641, 32849), _
        Array("Pens", "Michigan", "Jean", 16569, 32675, 25396), _
        Array("Books", "Michigan", "Volker", 21961, 21242, 29009), _
        Array("Candy", "Michigan", "Volker", 26142, 22407, 32841))
    sTable = oDoc.SetArray("A1", vData)
    sPivot = oDoc.CreatePivotTable("PT1", sTable, "H1", _
        Array("2002", "2003;count", "2004;average"), _ ' Three data fields
        "Item", _ ' A single row field
        Array("State", "Team"), False) ' Two column fields
  

    ui = CreateScriptService("UI")
    doc = ui.CreateDocument("Calc")
    vData = [["Item", "State", "Team", "2002", "2003", "2004"],
             ["Books", "Michigan", "Jean", 14788, 30222, 23490],
             ["Candy", "Michigan", "Jean", 26388, 15641, 32849],
             ["Pens", "Michigan", "Jean", 16569, 32675, 25396)],
             ["Books", "Michigan", "Volker", 21961, 21242, 29009],
             ["Candy", "Michigan", "Volker", 26142, 22407, 32841]]
    sTable = doc.SetArray("A1", vData)
    sPivot = doc.CreatePivotTable("PT1", sTable, "H1",
                                  ["2002", "2003;count", "2004;average"],
                                  "Item",
                                  ["State", "Team"], False)
  

To learn more about Pivot Tables in LibreOffice Calc, read the Pivot Table help page.

DAvg, DCount, DMax, DMin and DSum

Apply the functions Average, Count, Max, Min and Sum, respectively, to all the cells containing numeric values on a given range, excluding values from filtered and hidden rows and hidden columns, the same as for the status bar functions.

svc.DAvg(range: str): float

svc.DCount(range: str): float

svc.DMax(range: str): float

svc.DMin(range: str): float

svc.DSum(range: str): float

range: The range to which the function will be applied, as a string.

The example below applies the Sum function to the range "A1:A1000" of the currently selected sheet:

      result = oDoc.DSum("~.A1:A1000")
  

    result = myDoc.DSum("~.A1:A1000")
  

Cells in the given range that contain text will be ignored by all of these functions. For example, the DCount method will not count cells with text, only numerical cells.

ExportRangeToFile

Exports the specified range as an image or PDF file.

This method returns True if the destination file was successfully saved.

Hidden rows or columns in the specified range are not exported to the destination file.

svc.ExportRangeToFile(range: str, filename: str, imagetype: str = "pdf", overwrite: bool = False): bool

range: A sheet name or a cell range to be exported, as a string.

filename: The name of the file to be saved. It must follow the SF_FileSystem.FileNaming notation.

imagetype: Identifies the destination file type. Possible values are "jpeg", "pdf" (default) and "png".

overwrite: When set to True, the destination file may be overwritten (Default = False).

    ' Exports the entire sheet as a PDF file
    oDoc.ExportRangeToFile("SheetX", "C:\Temp\image.pdf")
    ' Exports the range as a PNG file and overwrites the destination file if it exists
    oDoc.ExportRangeToFile("SheetX.A1:D10", "C:\Temp\image.png", "png", Overwrite := True)
  

    doc.ExportRangeToFile("SheetX", r"C:\Temp\image.pdf")
    doc.ExportRangeToFile("SheetX.A1:D10", r"C:\Temp\image.png", "png", overwrite = True)
  

Forms

Depending on the parameters provided this method will return:

• A zero-based Array (or a tuple in Python) with the names of all the forms contained in a given sheet (if the form argument is absent)

• A SFDocuments.Form service instance representing the form specified as argument.

svc.Forms(sheetname: str): str[0..*]

svc.Forms(sheetname: str, form: str = ''): svc

svc.Forms(sheetname: str, form: int): svc

sheetname: The name of the sheet, as a string, from which the form will be retrieved.

form: The name or index corresponding to a form stored in the specified sheet. If this argument is absent, the method will return a list with the names of all forms available in the sheet.

In the following examples, the first line gets the names of all forms stored in "Sheet1" and the second line retrieves the Form object of the form named "Form_A" which is stored in "Sheet1".

    Set FormNames = oDoc.Forms("Sheet1")
    Set FormA = oDoc.Forms("Sheet1", "Form_A")
  

    form_names = doc.Forms("Sheet1")
    form_A = doc.Forms("Sheet1", "Form_A")
  

GetColumnName

Converts a column number ranging between 1 and 1024 into its corresponding letter (column 'A', 'B', ..., 'AMJ'). If the given column number is outside the allowed range, a zero-length string is returned.

svc.GetColumnName(columnnumber: int): str

columnnumber: The column number as an integer value in the interval 1 ... 16384.

Displays a message box with the name of the third column, which by default is "C".

    MsgBox oDoc.GetColumnName(3)
  

    bas = CreateScriptService("Basic")
    bas.MsgBox(myDoc.GetColumnName(3))
  

The maximum number of columns allowed on a Calc sheet is 16384.

GetFormula

Get the formula(s) stored in the given range of cells as a single string, a 1D or a 2D array of strings.

The names of Calc functions used in the returned formulas are expressed in English. Visit the Wiki page List of Calc Functions for a complete list of Calc functions in English.

svc.GetFormula(range: str): any