ScriptForge = (SFDatabases) New Datasheet service

A datasheet is the visual representation
of tabular data produced by a database.
In the user interface of LibreOffice
it is the result of the opening of a table or a query.
In this case the concerned Base document must be open.

In the context of ScriptForge, a datasheet
may be opened automatically by script code :
- either by reproducing the behaviour of the user interface
- or at any moment.
In this case the Base document does not need to be open.
Additionally, any SELECT SQL statement
may define the datasheet display.

The proposed API allows for either datasheets
(opened manually of by code) in particular
to know which cell is selected and its content.

Properties:
ColumnHeaders
CurrentColumn
CurrentRow
LastRow
Source
SourceType
XComponent
XControlModel
XTabControllerModel

Methods
Activate
ApplyFilter
CloseDatasheet
GetValue
GetText
GoToCell
OrderBy

The Base and Database services are enriched with the
OpenTable
OpenQuery
methods. The Database service gets also a new
OpenSql
method.

The whole set of properties and methods is available
both for Basic and Python scripts.

This new service requires a new help page dedicated
to this service, as well as an update of the
pages about the Base and Database services.

Change-Id: Ib409ce74d95de78f2792ba53e7ae554eab0867ab
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/142118
Tested-by: Jenkins
Reviewed-by: Jean-Pierre Ledure <jp@ledure.be>

3 files changed, 134 insertions, 27 deletions

diff --git a/wizards/source/sfdocuments/SF_Base.xba b/wizards/source/sfdocuments/SF_Base.xba
index 1e6395dbfa2c..81afacc7bbda 100644
--- a/wizards/source/sfdocuments/SF_Base.xba
+++ b/wizards/source/sfdocuments/SF_Base.xba
@@ -459,7 +459,7 @@ Public Function OpenFormDocument(Optional ByVal FormDocument As Variant _
 '''	Open the FormDocument given by its hierarchical name either in normal or in design mode
 '''	If the form document is already open, the form document is made active without changing its mode
 '''	Args:
-'''		FormDocument: a valid document form name as a case-sensitive string
+'''		FormDocument: a valid form document name as a case-sensitive string
 '''		DesignMode: when True the form document is opened in design mode (Default = False)
 '''	Returns:
 '''		True if the form document could be opened, otherwise False
@@ -510,6 +510,111 @@ Catch:
 End Function	'	SFDocuments.SF_Base.OpenFormDocument
 
 REM -----------------------------------------------------------------------------
+Public Function OpenQuery(Optional ByVal QueryName As Variant _
+									, Optional ByVal DesignMode As Variant _
+									) As Object
+'''	Open the query given by its name either in normal or in design mode
+'''	If the query is already open, the query datasheet is made active without changing its mode
+'''	If still open, the datasheet will be closed together with the actual Base document.
+'''	Args:
+'''		QueryName: a valid Query name as a case-sensitive string
+'''		DesignMode: when True the query is opened in design mode (Default = False)
+'''	Returns:
+'''		A Datasheet class instance if the query could be opened and DesignMode = False, otherwise False
+'''	Exceptions:
+'''		Query name is invalid
+'''	Example:
+'''		oDoc.OpenQuery("myQuery", DesignMode := False)
+
+Dim oOpen As Object					'	Return value
+Dim vQueries As Variant				'	Array of query names
+Dim oNewQuery As Object				'	Output of loadComponent()
+Const cstThisSub = "SFDocuments.Base.OpenQuery"
+Const cstSubArgs = "QueryName, [DesignMode=False]"
+
+	If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+	Set oOpen = Nothing
+
+Check:
+	If IsMissing(DesignMode) Or IsEmpty(DesignMode) Then DesignMode = False
+	vQueries = GetDatabase().Queries	'	Includes _IsStillAlive()
+	If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+		If Not ScriptForge.SF_Utils._Validate(QueryName, "QueryName", V_STRING, vQueries) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(DesignMode, "DesignMode", ScriptForge.V_BOOLEAN) Then GoTo Finally
+	End If
+
+Try:
+	With _Component.CurrentController
+		'	The connection may have been done previously by a user commmand. If not, do it now.
+		If Not .IsConnected Then .connect()
+		'	loadComponent activates the query component when already loaded
+		Set oNewQuery = .loadComponent(com.sun.star.sdb.application.DatabaseObject.QUERY, QueryName, DesignMode)
+	End With
+	'	When design mode, the method returns Nothing
+	If Not DesignMode Then Set oOpen = ScriptForge.SF_Services.CreateScriptService("SFDatabases.Datasheet", [Me], oNewQuery)
+
+Finally:
+	Set OpenQuery = oOpen
+	ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+	Exit Function
+Catch:
+	GoTo Finally
+End Function	'	SFDocuments.SF_Base.OpenQuery
+
+REM -----------------------------------------------------------------------------
+Public Function OpenTable(Optional ByVal TableName As Variant _
+									, Optional ByVal DesignMode As Variant _
+									) As Object
+'''	Open the table given by its name either in normal or in design mode
+'''	If the table is already open, the table datasheet is made active without changing its mode
+'''	If still open, the datasheet will be closed together with the actual Base document.
+'''	Args:
+'''		TableName: a valid table name as a case-sensitive string
+'''		DesignMode: when True the table is opened in design mode (Default = False)
+'''	Returns:
+'''		A Datasheet class instance if the table could be opened or was already open, and DesignMode = False.
+'''		Otherwise Nothing
+'''	Exceptions:
+'''		Table name is invalid
+'''	Example:
+'''		oDoc.OpenTable("myTable", DesignMode := False)
+
+Dim oOpen As Object					'	Return value
+Dim vTables As Variant				'	Array of table names
+Dim oNewTable As Object				'	Output of loadComponent()
+Const cstThisSub = "SFDocuments.Base.OpenTable"
+Const cstSubArgs = "TableName, [DesignMode=False]"
+
+	If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+	Set oOpen = Nothing
+
+Check:
+	If IsMissing(DesignMode) Or IsEmpty(DesignMode) Then DesignMode = False
+	vTables = GetDatabase().Tables
+	If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+		If Not ScriptForge.SF_Utils._Validate(TableName, "TableName", V_STRING, vTables) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(DesignMode, "DesignMode", ScriptForge.V_BOOLEAN) Then GoTo Finally
+	End If
+
+Try:
+	With _Component.CurrentController
+		'	The connection may have been done previously by a user commmand. If not, do it now.
+		If Not .IsConnected Then .connect()
+		'	loadComponent activates the table component when already loaded
+		Set oNewTable = .loadComponent(com.sun.star.sdb.application.DatabaseObject.TABLE, TableName, DesignMode)
+	End With
+	'	When design mode, the method returns Nothing
+	If Not DesignMode Then Set oOpen = ScriptForge.SF_Services.CreateScriptService("SFDatabases.Datasheet", [Me], oNewTable)
+
+Finally:
+	Set OpenTable = oOpen
+	ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+	Exit Function
+Catch:
+	GoTo Finally
+End Function	'	SFDocuments.SF_Base.OpenTable
+
+REM -----------------------------------------------------------------------------
 Public Function PrintOut(Optional ByVal FormDocument As Variant _
 							, Optional ByVal Pages As Variant _
 							, Optional ByVal Copies As Variant _
diff --git a/wizards/source/sfdocuments/SF_Calc.xba b/wizards/source/sfdocuments/SF_Calc.xba
index e32dab652166..0733be07eb6a 100644
--- a/wizards/source/sfdocuments/SF_Calc.xba
+++ b/wizards/source/sfdocuments/SF_Calc.xba
@@ -401,7 +401,7 @@ Finally:
 	Exit Function
 Catch:
 	GoTo Finally
-End Function	'	SF_Documents.SF_Calc.A1Style
+End Function	'	SFDocuments.SF_Calc.A1Style
 
 REM -----------------------------------------------------------------------------
 Public Function Activate(Optional ByVal SheetName As Variant) As Boolean
@@ -560,7 +560,7 @@ Public Sub ClearAll(Optional ByVal Range As Variant _
 
 	_ClearRange("All", Range, FilterFormula, FilterScope)
 
-End Sub	'	SF_Documents.SF_Calc.ClearAll
+End Sub	'	SFDocuments.SF_Calc.ClearAll
 
 REM -----------------------------------------------------------------------------
 Public Sub ClearFormats(Optional ByVal Range As Variant _
@@ -580,7 +580,7 @@ Public Sub ClearFormats(Optional ByVal Range As Variant _
 
 	_ClearRange("Formats", Range, FilterFormula, FilterScope)
 
-End Sub	'	SF_Documents.SF_Calc.ClearFormats
+End Sub	'	SFDocuments.SF_Calc.ClearFormats
 
 REM -----------------------------------------------------------------------------
 Public Sub ClearValues(Optional ByVal Range As Variant _
@@ -600,7 +600,7 @@ Public Sub ClearValues(Optional ByVal Range As Variant _
 
 	_ClearRange("Values", Range, FilterFormula, FilterScope)
 
-End Sub	'	SF_Documents.SF_Calc.ClearValues
+End Sub	'	SFDocuments.SF_Calc.ClearValues
 
 REM -----------------------------------------------------------------------------
 Public Function CompactLeft(Optional ByVal Range As Variant _
@@ -1424,7 +1424,7 @@ Try:
 
 Finally:
 	Exit Function
-End Function	'	SF_Documents.SF_Calc.DAvg
+End Function	'	SFDocuments.SF_Calc.DAvg
 
 REM -----------------------------------------------------------------------------
 Public Function DCount(Optional ByVal Range As Variant) As Long
@@ -1441,7 +1441,7 @@ Try:
 
 Finally:
 	Exit Function
-End Function	'	SF_Documents.SF_Calc.DCount
+End Function	'	SFDocuments.SF_Calc.DCount
 
 REM -----------------------------------------------------------------------------
 Public Function DMax(Optional ByVal Range As Variant) As Double
@@ -1458,7 +1458,7 @@ Try:
 
 Finally:
 	Exit Function
-End Function	'	SF_Documents.SF_Calc.DMax
+End Function	'	SFDocuments.SF_Calc.DMax
 
 REM -----------------------------------------------------------------------------
 Public Function DMin(Optional ByVal Range As Variant) As Double
@@ -1475,7 +1475,7 @@ Try:
 
 Finally:
 	Exit Function
-End Function	'	SF_Documents.SF_Calc.DMin
+End Function	'	SFDocuments.SF_Calc.DMin
 
 REM -----------------------------------------------------------------------------
 Public Function DSum(Optional ByVal Range As Variant) As Double
@@ -1492,7 +1492,7 @@ Try:
 
 Finally:
 	Exit Function
-End Function	'	SF_Documents.SF_Calc.DSum
+End Function	'	SFDocuments.SF_Calc.DSum
 
 REM -----------------------------------------------------------------------------
 Public Function ExportRangeToFile(Optional ByVal Range As Variant _
@@ -1754,7 +1754,7 @@ Finally:
 	Exit Function
 Catch:
 	GoTo Finally
-End Function	'	SF_Documents.SF_Calc.GetFormula
+End Function	'	SFDocuments.SF_Calc.GetFormula
 
 REM -----------------------------------------------------------------------------
 Public Function GetProperty(Optional ByVal PropertyName As Variant _
@@ -1839,7 +1839,7 @@ Finally:
 	Exit Function
 Catch:
 	GoTo Finally
-End Function	'	SF_Documents.SF_Calc.GetValue
+End Function	'	SFDocuments.SF_Calc.GetValue
 
 REM -----------------------------------------------------------------------------
 Public Function ImportFromCSVFile(Optional ByVal FileName As Variant _
@@ -2300,7 +2300,7 @@ Finally:
 	Exit Function
 Catch:
 	GoTo Finally
-End Function	'	SF_Documents.SF_Calc.Offset
+End Function	'	SFDocuments.SF_Calc.Offset
 
 REM -----------------------------------------------------------------------------
 Public Function OpenRangeSelector(Optional ByVal Title As Variant _
@@ -2381,7 +2381,7 @@ Finally:
 	Exit Function
 Catch:
 	GoTo Finally
-End Function	'	SF_Documents.SF_Calc.OpenRangeSelector
+End Function	'	SFDocuments.SF_Calc.OpenRangeSelector
 
 REM -----------------------------------------------------------------------------
 Public Function Printf(Optional ByVal InputStr As Variant _
@@ -2478,7 +2478,7 @@ Finally:
 	Exit Function
 Catch:
 	GoTo Finally
-End Function	'	SF_Documents.SF_Calc.Printf
+End Function	'	SFDocuments.SF_Calc.Printf
 
 REM -----------------------------------------------------------------------------
 Public Function PrintOut(Optional ByVal SheetName As Variant _
@@ -2711,7 +2711,7 @@ Finally:
 	Exit Function
 Catch:
 	GoTo Finally
-End Function	'	SF_Documents.SF_Calc.SetArray
+End Function	'	SFDocuments.SF_Calc.SetArray
 
 REM -----------------------------------------------------------------------------
 Public Function SetCellStyle(Optional ByVal TargetRange As Variant _
@@ -2789,7 +2789,7 @@ Finally:
 	Exit Function
 Catch:
 	GoTo Finally
-End Function	'	SF_Documents.SF_Calc.SetCellStyle
+End Function	'	SFDocuments.SF_Calc.SetCellStyle
 
 REM -----------------------------------------------------------------------------
 Public Function SetFormula(Optional ByVal TargetRange As Variant _
@@ -2858,7 +2858,7 @@ Finally:
 	Exit Function
 Catch:
 	GoTo Finally
-End Function	'	SF_Documents.SF_Calc.SetFormula
+End Function	'	SFDocuments.SF_Calc.SetFormula
 
 REM -----------------------------------------------------------------------------
 Private Function SetProperty(Optional ByVal psProperty As String _
@@ -2966,7 +2966,7 @@ Finally:
 	Exit Function
 Catch:
 	GoTo Finally
-End Function	'	SF_Documents.SF_Calc.SetValue
+End Function	'	SFDocuments.SF_Calc.SetValue
 
 REM -----------------------------------------------------------------------------
 Public Function ShiftDown(Optional ByVal Range As Variant _
@@ -3423,7 +3423,7 @@ Finally:
 	Exit Function
 Catch:
 	GoTo Finally
-End Function	'	SF_Documents.SF_Calc.SortRange
+End Function	'	SFDocuments.SF_Calc.SortRange
 
 REM ======================================================= SUPERCLASS PROPERTIES
 
@@ -3685,7 +3685,7 @@ Finally:
 	Exit Sub
 Catch:
 	GoTo Finally
-End Sub	'	SF_Documents.SF_Calc._ClearRange
+End Sub	'	SFDocuments.SF_Calc._ClearRange
 
 REM -----------------------------------------------------------------------------
 Private Function _ComputeFilter(ByRef poRange As Object _
@@ -3853,7 +3853,7 @@ Try:
 
 Finally:
 	_ConvertFromDataArray = vArray
-End Function	'	SF_Documents.SF_Calc._ConvertFromDataArray
+End Function	'	SFDocuments.SF_Calc._ConvertFromDataArray
 
 REM -----------------------------------------------------------------------------
 Private Function _ConvertToCellValue(ByVal pvItem As Variant) As Variant
@@ -3873,7 +3873,7 @@ Try:
 Finally:
 	_ConvertToCellValue = vCell
 	Exit Function
-End Function	'	SF_Documents.SF_Calc._ConvertToCellValue
+End Function	'	SFDocuments.SF_Calc._ConvertToCellValue
 
 REM -----------------------------------------------------------------------------
 Private Function _ConvertToDataArray(ByRef pvArray As Variant _
@@ -3997,7 +3997,7 @@ Try:
 Finally:
 	_ConvertToDataArray = vDataArray
 	Exit Function
-End Function	'	SF_Documents.SF_Calc._ConvertToDataArray
+End Function	'	SFDocuments.SF_Calc._ConvertToDataArray
 
 REM -----------------------------------------------------------------------------
 Private Function _DFunction(ByVal psFunction As String _
@@ -4043,7 +4043,7 @@ Finally:
 	Exit Function
 Catch:
 	GoTo Finally
-End Function	'	SF_Documents.SF_Calc._DFunction
+End Function	'	SFDocuments.SF_Calc._DFunction
 
 REM -----------------------------------------------------------------------------
 Private Function _FileIdent() As String
@@ -4205,7 +4205,7 @@ CatchAddress:
 				, "Rows", plRows, "Columns", plColumns, "Height", plHeight, "Width", plWidth _
 				, "Document", [_Super]._FileIdent())
 	GoTo Finally
-End Function	'	SF_Documents.SF_Calc._Offset
+End Function	'	SFDocuments.SF_Calc._Offset
 
 REM -----------------------------------------------------------------------------
 Private Function _ParseAddress(ByVal psAddress As String) As Object
diff --git a/wizards/source/sfdocuments/SF_Document.xba b/wizards/source/sfdocuments/SF_Document.xba
index 30508f2e87b1..e537b90e5da1 100644
--- a/wizards/source/sfdocuments/SF_Document.xba
+++ b/wizards/source/sfdocuments/SF_Document.xba
@@ -864,7 +864,9 @@ Check:
 	'	When called from a subclass (Calc, Writer, ..) the arguments are gathered into one single array item
 	vArgs = Args
 	If IsArray(Args) Then
-		If UBound(Args) >= 0 And IsArray(Args(0)) Then vArgs = Args(0)
+		If UBound(Args) >= 0 Then
+			If IsArray(Args(0)) Then vArgs = Args(0)
+		End If
 	End If
 	If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
 		If Not _IsStillAlive() Then GoTo Finally