From 67083387c1b82f17f7027ad53c9438766c0c41f3 Mon Sep 17 00:00:00 2001 From: Rafael Lima Date: Fri, 21 May 2021 21:28:06 +0200 Subject: Python support in SF_Calc help page Change-Id: I0b7a83fdd49dd4cf5fb1682b193a61ceef5bbe55 Reviewed-on: https://gerrit.libreoffice.org/c/help/+/115956 Tested-by: Jenkins Tested-by: Jean-Pierre Ledure Reviewed-by: Jean-Pierre Ledure --- source/text/sbasic/shared/03/sf_calc.xhp | 756 +++++++++++++++++++------------ 1 file changed, 477 insertions(+), 279 deletions(-) diff --git a/source/text/sbasic/shared/03/sf_calc.xhp b/source/text/sbasic/shared/03/sf_calc.xhp index a2b782bfb1..d2e7b191d7 100644 --- a/source/text/sbasic/shared/03/sf_calc.xhp +++ b/source/text/sbasic/shared/03/sf_calc.xhp @@ -25,8 +25,8 @@

SFDocuments.Calc service

- The SFDocuments library provides a number of methods and properties to facilitate the management and handling of LibreOffice Calc documents. - Some methods are generic for all types of documents and are inherited from the SF_Document service, whereas other methods are specific for the SF_Calc module. + The SFDocuments library provides a number of methods and properties to facilitate the management and handling of %PRODUCTNAME Calc documents. + Some methods are generic for all types of documents and are inherited from the Document service, whereas other methods are specific for the SF_Calc module. The SF_Calc module is focused on: @@ -42,28 +42,48 @@

Service invocation

- Before using the Calc service the ScriptForge library needs to be loaded using: + 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. - GlobalScope.BasicLibraries.LoadLibrary("ScriptForge") + Set oDoc = CreateScriptService("Calc") - 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. + 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: - '1) From the ScriptForge.UI service: Dim ui As Object, oDoc As Object Set ui = CreateScriptService("UI") Set oDoc = ui.CreateDocument("Calc") - 'Or: Set oDoc = ui.OpenDocument("C:\Me\MyFile.ods") + 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: - '2) Directly if the document is already open Dim oDoc As Object - Set oDoc = CreateScriptService("SFDocuments.Calc", "Untitled 1") 'Default = ActiveWindow + 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. + + + myDoc = CreateScriptService("Calc") + + + svcUI = CreateScriptService("UI") + myDoc = svcUI.CreateDocument("Calc") + + + myDoc = svcUI.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

@@ -77,21 +97,28 @@ 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:\FileA.ods", Hidden := True, ReadOnly := True) - Set oDocB = ui.OpenDocument("C:\FileB.ods") + 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 = svcUI.OpenDocument(r"C:\Documents\FileA.ods", hidden = True, readonly = True) + docB = svcUI.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 range if multiple ranges are selected. + 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 in a range (default = the active sheet). Surrounding single quotes and $ signs are allowed but ignored. Except for the CurrentSelection property, the Calc service considers only single ranges of cells. @@ -436,20 +463,27 @@

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. -

- - oDoc.Activate([SheetName As String]) As Boolean - -

- SheetName : The name of the sheet to be activated in the document. -

+ + + doc.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") + Dim ui as Variant, oDoc as Object + Set ui = CreateScriptService("UI") + Set oDoc = ui.GetDocument(ui.ActiveWindow) + oDoc.Activate("Sheet4") + + + svcUI = CreateScriptService("UI") + myDoc = svcUI.GetDocument(svcUI.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. @@ -460,16 +494,21 @@

ClearAll

Clears all the contents and formats of the given range. -

- - oDoc.ClearAll(Range As String) - -

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

+ + + doc.ClearAll(range: str) + + + range: The range to be cleared, as a string. + + oDoc.ClearAll("SheetX.A1:F10") + + + myDoc.ClearAll("SheetX.A1:F10") +
@@ -479,16 +518,21 @@

ClearFormats

Clears the formats and styles in the given range. -

- - oDoc.ClearFormats(Range As String) - -

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

+ + + doc.ClearFormats(range: str) + + + range: The range whose formats and styles are to be cleared, as a string. + + oDoc.ClearFormats("SheetX.*") + + + myDoc.ClearFormats("SheetX.*") +
@@ -498,16 +542,21 @@

ClearValues

Clears the values and formulas in the given range. -

- - oDoc.ClearValues(Range As String) - -

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

+ + + doc.ClearValues(range: 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") +
@@ -517,15 +566,16 @@

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. -

- - oDoc.CopySheet(SheetName As Variant, NewName As String, [BeforeSheet As Variant]) As Boolean - -

- 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. -

+ + + doc.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 @@ -535,11 +585,19 @@ 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:\Temp\FileA.ods", Hidden := True, ReadOnly := True) - Dim oDocB As Object : Set oDocB = ui.OpenDocument("C:\Temp\FileB.ods") + 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 = svcUI.OpenDocument(r"C:\Documents\FileA.ods", hidden = True, readonly = True) + docB = svcUI.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.
@@ -551,20 +609,25 @@

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. -

- - oDoc.CopySheetFromFile(FileName As String, SheetName As String, NewName As String, [BeforeSheet As Variant]) As Boolean - -

- 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. -

+ + + doc.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:\myFile.ods", "SheetX", "SheetY", 1) + oDoc.CopySheetFromFile("C:\Documents\myFile.ods", "SheetX", "SheetY", 1) + + + myDoc.CopySheetFromFile(r"C:\Documents\myFile.ods", "SheetX", "SheetY", 1) +
@@ -574,16 +637,17 @@

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. -

- - oDoc.CopyToCell(SourceRange As Variant, DestinationCell As String) As String - -

- 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. -

+ 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. + + + doc.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") @@ -599,6 +663,13 @@ 'Do not forget to close the source document because it was opened as hidden oDocSource.CloseDocument() + + + docSource = svcUI.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.
@@ -619,25 +690,35 @@ The method returns a string representing the modified range of cells. The source range may belong to another open document. -

+ + + doc.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(SourceRange As Variant, DestinationRange As String) As String + oDoc.CopyToRange("SheetX.A1:F10", "SheetY.C5:J5") + ' Returns a range string: "$SheetY.$C$5:$J$14" -

- 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 : + Copy from one file to another: - oDoc.CopyToRange("SheetX.A1:F10", "SheetY.C5:J5") - 'Returned range: $SheetY.$C$5:$J$14 - - Copy from one file to another : - - Dim oDocA As Object : Set oDocA = ui.OpenDocument("C:\Temp\FileA.ods", Hidden := True, ReadOnly := True) - Dim oDocB As Object : Set oDocB = ui.OpenDocument("C:\Temp\FileB.ods") - oDocB.CopyToRange(oDocA.Range("SheetX.A1:F10"), "SheetY.C5:J5") + 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 = svcUI.OpenDocument(r"C:\Documents\FileA.ods", hidden = True, readonly = True) + docB = svcUI.OpenDocument(r"C:\Documents\FileB.ods") + docB.CopyToRange(docA.Range("SheetX.A1:F10"), "SheetY.C5:J5") +
@@ -651,21 +732,34 @@

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. -

- - oDoc.DAvg(Range As String) As Double - oDoc.DCount(Range As String) As Long - oDoc.DMax(Range As String) As Double - oDoc.DMin(Range As String) As Double - oDoc.DSum(Range As String) As Double - -

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

+ + + doc.DAvg(range: str): float + + + doc.DCount(range: str): float + + + doc.DMax(range: str): float + + + doc.DMin(range: str): float + + + doc.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.
@@ -676,17 +770,23 @@

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. -

+ + + doc.GetColumnName(columnnumber: int): str + + + columnnumber: The column number as an integer value in the interval 1 ... 1024. + + + Displays a message box with the name of the third column, which by default is "C". - oDoc.GetColumnName(ColumnNumber As Integer) As String - -

- ColumnNumber : The column number as an integer value in the interval 1 ... 1024. -

- - 'Shows a message box with the string "C" MsgBox oDoc.GetColumnName(3) + + + sBasic = CreateScriptService("Basic") + sBasic.MsgBox(myDoc.GetColumnName(3)) + The maximum number of columns allowed on a Calc sheet is 1024. @@ -697,17 +797,22 @@

GetFormula

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

- - oDoc.GetFormula(Range As String) As Variant - -

- Range : The range where to get the formulas from, as a string. -

+ + + doc.GetFormula(range: str): any + + + range: The range where to get the formulas from, as a string. + + The following example returns a 3 by 2 array with the formulas in the range "A1:B3" (3 rows by 2 columns): - arrFormula = oDoc.GetFormula("~.A1:B3") + arrFormula = oDoc.GetFormula("~.A1:B3") + + + arrFormula = myDoc.GetFormula("~.A1:B3") +
@@ -717,17 +822,22 @@

GetValue

Get the value(s) stored in the given range of cells as a single value, a 1D array or a 2D array. All values are either doubles or strings. -

- - oDoc.GetValue(Range As String) As Variant - -

- Range : The range where to get the values from, as a string. -

+ + + doc.GetValue(range: str): any + + + range: The range where to get the values from, as a string. + + arrValues = oDoc.GetValue("~.B1:C100") - If a cell contains a date, the number corresponding to that date will be returned. To convert numeric values to dates, use the Basic CDate builtin function. + + + arrValues = myDoc.GetValue("~.B1:C100") + + If a cell contains a date, the number corresponding to that date will be returned. To convert numeric values to dates in Basic scripts, use the Basic CDate builtin function. In Python scripts, use the CDate function from the Basic service.
@@ -739,14 +849,14 @@ Imports the contents of a CSV-formatted text file and places it on a given destination cell. The destination area is cleared of all contents and formats before inserting the contents of the CSV file. The size of the modified area is fully determined by the contents of the input file. The method returns a string representing the modified range of cells. -

- - oDoc.ImportFromCSVFile(FileName As String, DestinationCell As String, FilterOptions As String) As String - -

- FileName : Identifies the file to open. It must follow the SF_FileSystem.FileNaming notation. - DestinationCell : The destination cell to insert the imported data, as a string. If instead a range is given, only its top-left cell is considered. - FilterOptions : The arguments for the CSV input filter. The default filter makes following assumptions: + + + doc.ImportFromCSVFile(filename: str, destinationcell: str, [filteroptions: str]): str + + + filename: Identifies the file to open. It must follow the SF_FileSystem.FileNaming notation. + destinationcell: The destination cell to insert the imported data, as a string. If instead a range is given, only its top-left cell is considered. + filteroptions: The arguments for the CSV input filter. The default filter makes following assumptions: The input file encoding is UTF8. @@ -773,10 +883,15 @@ The language is English/US, which implies that the decimal separator is "." and the thousands separator is ",". -

+ + - oDoc.ImportFromCSVFile("C:\Temp\myCsvFile.csv", "SheetY.C5") + oDoc.ImportFromCSVFile("C:\Temp\myCSVFile.csv", "SheetY.C5") + + + myDoc.ImportFromCSVFile(r"C:\Temp\myCSVFile.csv", "SheetY.C5") + To learn more about the CSV Filter Options, refer to the Filter Options Wiki page.
@@ -789,20 +904,25 @@ Imports the contents of a database table, query or resultset, i.e. the result of a SELECT SQL command, inserting it on a destination cell. The destination area is cleared of all contents and formats before inserting the imported contents. The size of the modified area is fully determined by the contents in the table or query. The method returns True when the import was successful. -

- - oDoc.ImportFromDatabase([FileName As String], [RegistrationName As String], DestinationCell As String, SQLCommand As String, [DirectSQL As Boolean]) As Boolean - -

- FileName : Identifies the file to open. It must follow the SF_FileSystem.FileNaming notation. - RegistrationName : The name to use to find the database in the databases register. This argument is ignored if a FileName is provided. - DestinationCell : The destination of the imported data, as a string. If a range is given, only its top-left cell is considered. - SQLCommand : A table or query name (without surrounding quotes or square brackets) or a SELECT SQL statement in which table and field names may be surrounded by square brackets or quotes to improve its readability. - DirectSQL : When True, the SQL command is sent to the database engine without pre-analysis. Default is False. The argument is ignored for tables. For queries, the applied option is the one set when the query was defined. -

+ + + doc.ImportFromDatabase(filename: str = "", registrationname: str = "", destinationcell: str = "", sqlcommand: str = "", directsql: bool): bool + + + filename: Identifies the file to open. It must follow the SF_FileSystem.FileNaming notation. + registrationname: The name to use to find the database in the databases register. This argument is ignored if a filename is provided. + destinationcell: The destination of the imported data, as a string. If a range is given, only its top-left cell is considered. + sqlcommand: A table or query name (without surrounding quotes or square brackets) or a SELECT SQL statement in which table and field names may be surrounded by square brackets or quotes to improve its readability. + directsql: When True, the SQL command is sent to the database engine without pre-analysis. Default is False. The argument is ignored for tables. For queries, the applied option is the one set when the query was defined. + + - oDoc.ImportFromDatabase("C:\Temp\myDbFile.odb", , "SheetY.C5", "SELECT * FROM [Employees] ORDER BY [LastName]") + oDoc.ImportFromDatabase("C:\Temp\myDbFile.odb", , "SheetY.C5", "SELECT * FROM [Employees] ORDER BY [LastName]") + + + myDoc.ImportFromDatabase(r"C:\Temp\myDbFile.odb", , "SheetY.C5", "SELECT * FROM [Employees] ORDER BY [LastName]") +
@@ -812,18 +932,23 @@

InsertSheet

Inserts a new empty sheet before an existing sheet or at the end of the list of sheets. -

- - oDoc.InsertSheet(SheetName As String, [BeforeSheet As Variant]) As Boolean - -

- SheetName : The name of the new sheet. - BeforeSheet : The name (string) or index (numeric, starting from 1) of the sheet before which to insert the new sheet. -

+ + + doc.InsertSheet(sheetname: str, [beforesheet: any]): bool + + + sheetname: The name of the new sheet. + beforesheet: The name (string) or index (numeric, starting from 1) of the sheet before which to insert the new sheet. This argument is optional and the default behavior is to insert the sheet at the last position. + The following example inserts a new empty sheet named "SheetX" and places it before "SheetY": + - oDoc.InsertSheet("SheetX", "SheetY") + oDoc.InsertSheet("SheetX", "SheetY") + + + myDoc.InsertSheet("SheetX", "SheetY") +
@@ -833,17 +958,23 @@

MoveRange

Moves a specified source range to a destination range of cells. The method returns a string representing the modified range of cells. The dimension of the modified area is fully determined by the size of the source area. -

- - oDoc.MoveRange(Source As String, Destination As String) As String - -

- Source : The source range of cells, as a string. - Destination : The destination cell, as a string. If a range is given, its top-left cell is considered as the destination. -

+ + + + doc.MoveRange(source: str, destination: str): str + + + source: The source range of cells, as a string. + destination: The destination cell, as a string. If a range is given, its top-left cell is considered as the destination. + + - oDoc.MoveRange("SheetX.A1:F10", "SheetY.C5") + oDoc.MoveRange("SheetX.A1:F10", "SheetY.C5") + + + myDoc.MoveRange("SheetX.A1:F10", "SheetY.C5") +
@@ -853,18 +984,23 @@

MoveSheet

Moves an existing sheet and places it before a specified sheet or at the end of the list of sheets. -

- - oDoc.MoveSheet(SheetName As String, [BeforeSheet As Variant]) As Boolean - -

- SheetName : The name of the sheet to move. The sheet must exist or an exception is raised. - BeforeSheet : The name (string) or index (numeric, starting from 1) of the sheet before which the original sheet will be placed. -

+ + + doc.MoveSheet(sheetname: str, [beforesheet: any]): bool + + + sheetname: The name of the sheet to move. The sheet must exist or an exception is raised. + beforesheet: The name (string) or index (numeric, starting from 1) of the sheet before which the original sheet will be placed. This argument is optional and the default behavior is to move the sheet to the last position. + The example below moves the existing sheet "SheetX" and places it before "SheetY": + - oDoc.MoveSheet("SheetX", "SheetY") + oDoc.MoveSheet("SheetX", "SheetY") + + + myDoc.MoveSheet("SheetX", "SheetY") +
@@ -875,25 +1011,31 @@

Offset

Returns a new range (as a string) offset by a certain number of rows and columns from a given range. This method has the same behavior as the homonymous Calc's Offset function. -

+ + + doc.Offset(reference: str, rows: int = 0, columns: int = 0, [height: int], [width: int]): str + + + reference: The range, as a string, that the method will use as reference to perform the offset operation. + rows: The number of rows by which the initial range is offset upwards (negative value) or downwards (positive value). Use 0 (default) to stay in the same row. + columns: The number of columns by which the initial range is offset to the left (negative value) or to the right (positive value). Use 0 (default) to stay in the same column. + height: The vertical height for an area that starts at the new range position. Omit this argument when no vertical resizing is needed. + width: The horizontal width for an area that starts at the new range position. Omit this argument when no horizontal resizing is needed. + Arguments rows and columns must not lead to zero or negative start row or column. + Arguments height and width must not lead to zero or negative count of rows or columns. + + - oDoc.Offset(Reference As String, [Rows As Long], [Columns As Long], [Height As Long], [Width As Long]) As String - -

- Reference : The range, as a string, that the method will use as reference to perform the offset operation. - Rows : The number of rows by which the initial range is offset upwards (negative value) or downwards (positive value). Use 0 (default) to stay in the same row. - Columns : The number of columns by which the initial range is offset to the left (negative value) or to the right (positive value). Use 0 (default) to stay in the same column. - Height : The vertical height for an area that starts at the new range position. Default = 0 (no vertical resizing). - Width : The horizontal width for an area that starts at the new range position. Default = 0 (no horizontal resizing). - Arguments Rows and Columns must not lead to zero or negative start row or column. - Arguments Height and Width must not lead to zero or negative count of rows or columns. -

- - oDoc.Offset("A1", 2, 2) - 'SheetX.$C$3 (A1 moved by two rows and two columns down) - oDoc.Offset("A1", 2, 2, 5, 6) - 'SheetX.$C$3:$H$7 (A1 offset by two rows and columns with width of 5 rows and 6 columns) + oDoc.Offset("A1", 2, 2) + 'SheetX.$C$3 (A1 moved by two rows and two columns down) + oDoc.Offset("A1", 2, 2, 5, 6) + 'SheetX.$C$3:$H$7 (A1 offset by two rows and columns with width of 5 rows and 6 columns) + + + myDoc.Offset("A1", 2, 2) + myDoc.Offset("A1", 2, 2, 5, 6) +
@@ -903,16 +1045,21 @@

RemoveSheet

Removes an existing sheet from the document. -

- - oDoc.RemoveSheet(SheetName As String) As Boolean - -

- SheetName : The name of the sheet to remove. -

+ + + doc.RemoveSheet(sheetname: str): bool + + + sheetname: The name of the sheet to remove. + + - oDoc.RemoveSheet("SheetY") + oDoc.RemoveSheet("SheetY") + + + myDoc.RemoveSheet("SheetY") +
@@ -922,18 +1069,23 @@

RenameSheet

Renames the given sheet and returns True if successful. -

- - oDoc.RenameSheet(SheetName As Variant, NewName As String) As Boolean - -

- SheetName : The name of the sheet to rename. - NewName : the new name of the sheet. It must not exist yet. -

+ + + doc.RenameSheet(sheetname: str, newname: str): bool + + + sheetname: The name of the sheet to rename. + newname: the new name of the sheet. It must not exist yet. + This example renames the active sheet to "SheetY": + - oDoc.RenameSheet("~", "SheetY") + oDoc.RenameSheet("~", "SheetY") + + + mydoc.RenameSheet("~", "SheetY") +
@@ -942,16 +1094,17 @@ Document service;SetArray

SetArray

- Stores the given value starting from a specified target cell. The updated area expands itself from the target cell or from the top-left corner of the given range to accommodate the size of the input Value argument. Vectors are always expanded vertically. + Stores the given value starting from a specified target cell. The updated area expands itself from the target cell or from the top-left corner of the given range to accommodate the size of the input value argument. Vectors are always expanded vertically. The method returns a string representing the modified area as a range of cells. -

- - oDoc.SetArray(TargetCell As String, Value As Variant) As String - -

- TargetCell : The cell or a range as a string from where to start to store the given value. - Value : A scalar, a vector or an array with the new values to be stored from the target cell or from the top-left corner of the range if TargetCell is a range. The new values must be strings, numeric values or dates. Other types will cause the corresponding cells to be emptied. -

+ + + doc.SetArray(targetcell: str, value: any): str + + + targetcell: The cell or a range as a string from where to start to store the given value. + value: A scalar, a vector or an array (in Python, one or two-dimensional lists and tuples) with the new values to be stored from the target cell or from the top-left corner of the range if targetcell is a range. The new values must be strings, numeric values or dates. Other types will cause the corresponding cells to be emptied. + + The following example uses the builtin DimArray function to create an array and then store it in cell "A1": Dim arrData as Variant @@ -962,9 +1115,17 @@ This example uses the RangeInit method of the ScriptForge Array service to create an array with values that are then stored from cell "A1" and downwards. - 'Fill 1st column with values from 1 to 1000 - oDoc.SetArray("SheetX.A1", SF_Array.RangeInit(1, 1000)) + 'Fill 1st column with values from 1 to 1000 + oDoc.SetArray("Sheet1.A1", SF_Array.RangeInit(1, 1000)) + + + arrData = ((1, "One"), (2, "Two"), (3, "Three")) + myDoc.SetArray("Sheet1.A1", arrData) + + + myDoc.SetArray("Sheet1.A1", tuple(i + 1 for i in range(1000))) + To dump the full contents of an array in a sheet, use SetArray. To dump the contents of an array only within the boundaries of the targeted range of cells, use SetValue.
@@ -976,24 +1137,43 @@

SetValue

Stores the given value in the specified range. The size of the modified area is equal to the size of the target range. The method returns a string representing the modified area as a range of cells. -

+ + + doc.SetValue(targetrange: str, value: any): str + + + targetrange: The range where to store the given value, as a string. + value: A scalar, a vector or an array with the new values for each cell of the range. The new values must be strings, numeric values or dates. Other types will cause the corresponding cells to be emptied. + The full range is updated and the remainder of the sheet is left unchanged. If the size of value is smaller than the size of targetrange, then the remaining cells will be emptied. + If the size of value is larger than the size of targetrange, then value is only partially copied until it fills the size of targetrange. + Vectors are expanded vertically, except if targetrange has a height of exactly 1 row. + + - oDoc.SetValue(TargetRange As String, Value As Variant) As String + oDoc.SetValue("A1", 2) + 'Below the Value array is smaller than the TargetRange (remaining cells are emptied) + oDoc.SetValue("A1:F1", Array(1, 2, 3)) + 'Below the Value and TargetRange have the same size + oDoc.SetValue("A1:D2", SF_Array.AppendRow(Array(1, 2, 3, 4), Array(5, 6, 7, 8))) -

- TargetRange : The range where to store the given value, as a string. - Value : A scalar, a vector or an array with the new values for each cell of the range. The new values must be strings, numeric values or dates. Other types will cause the corresponding cells to be emptied. - The full range is updated and the remainder of the sheet is left unchanged. If the size of Value is smaller than the size of TargetRange, then the remaining cells will be emptied. - If the size of Value is larger than the size of TargetRange, then Value is only partially copied until it fills the size of TargetRange. - Vectors are expanded vertically, except if the range has a height of exactly 1 row. -

+ If you want to fill a single row with values, you can use the Offset function. In the example below, consider that arrData is a one-dimensional array: - oDoc.SetValue("A1", 2) - 'Below the Value array is smaller than the TargetRange (remaining cells are emptied) - oDoc.SetValue("A1:F1", Array(1, 2, 3)) - 'Below the Value and TargetRange have the same size - oDoc.SetValue("A1:D2", SF_Array.AppendRow(Array(1, 2, 3, 4), Array(5, 6, 7, 8))) + Dim firstCell As String : firstCell = "A1" + Dim lenArray As Integer : lenArray = UBound(arrData) - LBound(arrData) + 1 + Dim newRange As String : newRange = oDoc.Offset(firstCell, width = lenArray) + oDoc.SetValue(newRange, arrData) + + + myDoc.SetValue("A1", 2) + myDoc.SetValue("A1:F1", (1, 2, 3)) + myDoc.SetValue("A1:D2", ((1, 2, 3, 4), (5, 6, 7, 8))) + + + firstCell = "A1" + newRange = doc.Offset(firstCell, width = len(arrData)) + doc.SetValue(newRange, arrData) +
@@ -1004,18 +1184,24 @@

SetCellStyle

Applies the specified cell style to the given target range. The full range is updated and the remainder of the sheet is left untouched. If the cell style does not exist, an error is raised. The method returns a string representing the modified area as a range of cells. -

- - oDoc.SetCellStyle(TargetRange As String, Style As String) As String - -

- TargetRange : The range to which the style will be applied, as a string. - Style : The name of the cell style to apply. -

+ + + doc.SetCellStyle(targetrange: str, style: str): str + + + targetrange: The range to which the style will be applied, as a string. + style: The name of the cell style to apply. + + - oDoc.SetCellStyle("A1:J1", "Heading 1") - oDoc.SetCellStyle("A2:J100", "Neutral") + oDoc.SetCellStyle("A1:J1", "Heading 1") + oDoc.SetCellStyle("A2:J100", "Neutral") + + + myDoc.SetCellStyle("A1:J1", "Heading 1") + myDoc.SetCellStyle("A2:J100", "Neutral") +
@@ -1026,26 +1212,33 @@

SetFormula

Inserts the given (array of) formula(s) in the specified range. The size of the modified area is equal to the size of the range. The method returns a string representing the modified area as a range of cells. -

+ + + doc.SetFormula(targetrange: str, formula: any): str + + + targetrange: The range to insert the formulas, as a string. + formula: A string, a vector or an array of strings with the new formulas for each cell in the target range. + The full range is updated and the remainder of the sheet is left unchanged. + If the given formula is a string, the unique formula is pasted along the whole range with adjustment of the relative references. + If the size of formula is smaller than the size of targetrange, then the remaining cells are emptied. + If the size of formula is larger than the size of targetrange, then the formulas are only partially copied until it fills the size of targetrange. + Vectors are always expanded vertically, except if targetrange has a height of exactly 1 row. + + - oDoc.SetFormula(TargetRange As String, Formula As Variant) As String - -

- TargetRange : The range to insert the formulas, as a string. - Formula : A string, a vector or an array of strings with the new formulas for each cell in the target range. - The full range is updated and the remainder of the sheet is left unchanged. - If the given formula is a string, the unique formula is pasted along the whole range with adjustment of the relative references. - If the size of Formula is smaller than the size of TargetRange, then the remaining cells are emptied. - If the size of Formula is larger than the size of TargetRange, then the formulas are only partially copied until it fills the size of TargetRange. - Vectors are always expanded vertically, except if the range has a height of exactly 1 row. -

- - oDoc.SetFormula("A1", "=A2") - 'Horizontal vector, partially empty - oDoc.SetFormula("A1:F1", Array("=A2", "=B2", "=C2+10")) - 'D2 contains the formula "=H2" - oDoc.SetFormula("A1:D2", "=E1") + oDoc.SetFormula("A1", "=A2") + 'Horizontal vector, partially empty + oDoc.SetFormula("A1:F1", Array("=A2", "=B2", "=C2+10")) + 'D2 contains the formula "=H2" + oDoc.SetFormula("A1:D2", "=E1") + + + myDoc.SetFormula("A1", "=A2") + myDoc.SetFormula("A1:F1", ("=A2", "=B2", "=C2+10")) + myDoc.SetFormula("A1:D2", "=E1") +
@@ -1055,23 +1248,28 @@

SortRange

Sorts the given range based on up to 3 columns/rows. The sorting order may vary by column/row. 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. -

- - oDoc.SortRange(Range As String, SortKeys As Variant, [SortOrder As Variant], [DestinationCell As String], [ContainsHeader As Boolean], [CaseSensitive As Boolean], [SortColumns As Boolean]) As String - -

- Range : The range to be sorted, as a string. - SortKeys : A scalar (if 1 column/row) or an array of column/row numbers starting from 1. The maximum number of keys is 3. - SortOrder : A scalar or an array of strings containing the values "ASC" (ascending), "DESC" (descending) or "" (which defaults to ascending). Each item is paired with the corresponding item in SortKeys. If the SortOrder array is shorter than SortKeys, the remaining keys are sorted in ascending order. - DestinationCell : The destination cell of the sorted range of cells, as a string. If a range is given, only its top-left cell is considered. By default the source Range is overwritten. - ContainsHeader : When True, the first row/column is not sorted. - CaseSensitive : Only for string comparisons. Default = False - SortColumns : When True, the columns are sorted from left to right. Default = False : rows are sorted from top to bottom. -

+ + + doc.SortRange(range: str, sortkeys: any, sortorder: any = "ASC", destinationcell: str = "", containsheader: bool = False, casesensitive: bool = False, sortcolumns: bool = False): str + + + range: The range to be sorted, as a string. + sortkeys: A scalar (if 1 column/row) or an array of column/row numbers starting from 1. The maximum number of keys is 3. + sortorder: A scalar or an array of strings containing the values "ASC" (ascending), "DESC" (descending) or "" (which defaults to ascending). Each item is paired with the corresponding item in sortkeys. If the sortorder array is shorter than sortkeys, the remaining keys are sorted in ascending order. + destinationcell: The destination cell of the sorted range of cells, as a string. If a range is given, only its top-left cell is considered. By default the source Range is overwritten. + containsheader: When True, the first row/column is not sorted. + casesensitive: Only for string comparisons. Default = False + sortcolumns: When True, the columns are sorted from left to right. Default = False : rows are sorted from top to bottom. + + 'Sort range based on columns A (ascending) and C (descending) oDoc.SortRange("A2:J200", Array(1, 3), Array("ASC", "DESC"), CaseSensitive := True) + + + myDoc.SortRange("A2:J200", (1, 3), ("ASC", "DESC"), casesensitive = True) +
-- cgit