ScriptForge - (SF_Calc) new Region() property

The Region() property expects one mandatory argument,
a range given as a string.

It returns a new range as a string, including the sheet name,
corresponding with the smallest area
- containing the input range
- completely surrounded with empty cells

It is complementary to Offset() and A1Style() to make easy the
definition of new ranges relative to known ranges

The property is available both in Basic and Python user scripts.

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

2 files changed, 37 insertions, 11 deletions

diff --git a/wizards/source/scriptforge/python/scriptforge.py b/wizards/source/scriptforge/python/scriptforge.py
index e15c63e1e4a8..d162c6ed5781 100644
--- a/wizards/source/scriptforge/python/scriptforge.py
+++ b/wizards/source/scriptforge/python/scriptforge.py
@@ -1891,6 +1891,9 @@ class SFDocuments:
         def Range(self, rangename):
             return self.GetProperty('Range', rangename)
 
+        def Region(self, rangename):
+            return self.GetProperty('Region', rangename)
+
         def Sheet(self, sheetname):
             return self.GetProperty('Sheet', sheetname)
 
diff --git a/wizards/source/sfdocuments/SF_Calc.xba b/wizards/source/sfdocuments/SF_Calc.xba
index 2e651651dc1b..7608fd2f8af9 100644
--- a/wizards/source/sfdocuments/SF_Calc.xba
+++ b/wizards/source/sfdocuments/SF_Calc.xba
@@ -233,6 +233,13 @@ Property Get Range(Optional ByVal RangeName As Variant) As Variant
 End Property	'	SFDocuments.SF_Calc.Range
 
 REM -----------------------------------------------------------------------------
+Property Get Region(Optional ByVal RangeName As Variant) As String
+'''	Returns the smallest area as a range string that contains the given range
+'''	and which is compeletely surrounded with empty cells
+	Region = _PropertyGet("Region", RangeName)
+End Property	'	SFDocuments.SF_Calc.Region
+
+REM -----------------------------------------------------------------------------
 Property Get Sheet(Optional ByVal SheetName As Variant) As Variant
 '''	Returns a (internal) sheet object
 	Sheet = _PropertyGet("Sheet", SheetName)
@@ -1582,7 +1589,8 @@ Public Function Methods() As Variant
 '''	Return the list of public methods of the Calc service as an array
 
 	Methods = Array( _
-					"Activate" _
+					"A1Style" _
+					, "Activate" _
 					, "ClearAll" _
 					, "ClearFormats" _
 					, "ClearValues" _
@@ -1880,6 +1888,7 @@ Public Function Properties() As Variant
 					, "LastRow" _
 					, "Range" _
 					, "Readonly" _
+					, "Region" _
 					, "Sheet" _
 					, "Sheets" _
 					, "Subject" _
@@ -3083,6 +3092,8 @@ Dim oProperties As Object			'	Document or Custom properties
 Dim vLastCell As Variant			'	Coordinates of last used cell in a sheet
 Dim oSelect As Object				'	Current selection
 Dim vRanges As Variant				'	List of selected ranges
+Dim oAddress As Object				'	_Address type for range description
+Dim oCursor As Object				'	com.sun.star.sheet.XSheetCellCursor
 Dim i As Long
 Dim cstThisSub As String
 Const cstSubArgs = ""
@@ -3093,8 +3104,8 @@ Const cstSubArgs = ""
 	ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs)
 	If Not _IsStillAlive() Then GoTo Finally
 
-	Select Case psProperty
-		Case "CurrentSelection"
+	Select Case UCase(psProperty)
+		Case UCase("CurrentSelection")
 			Set oSelect = _Component.CurrentController.getSelection()
 			If IsNull(oSelect) Then
 				_PropertyGet = Array()
@@ -3107,14 +3118,14 @@ Const cstSubArgs = ""
 			Else
 				_PropertyGet = oSelect.AbsoluteName
 			End If
-		Case "Height"
+		Case UCase("Height")
 			If IsMissing(pvArg) Or IsEmpty(pvArg) Then
 				_PropertyGet = 0
 			Else
 				If Not ScriptForge.SF_Utils._Validate(pvArg, "Range", V_STRING) Then GoTo Finally
 				_PropertyGet = _ParseAddress(pvArg).Height
 			End If
-		Case "LastCell", "LastColumn", "LastRow"
+		Case UCase("LastCell"), UCase("LastColumn"), UCase("LastRow")
 			If IsMissing(pvArg) Or IsEmpty(pvArg) Then	'	Avoid errors when instance is watched in Basic IDE
 				_PropertyGet = -1
 			Else
@@ -3128,37 +3139,49 @@ Const cstSubArgs = ""
 					_PropertyGet = GetColumnName(vLastCell(0)) & CStr(vLastCell(1))
 				End If
 			End If
-		Case "Range"
+		Case UCase("Range")
 			If IsMissing(pvArg) Or IsEmpty(pvArg) Then
 				Set _PropertyGet = Nothing
 			Else
 				If Not ScriptForge.SF_Utils._Validate(pvArg, "Range", V_STRING) Then GoTo Finally
 				Set _PropertyGet = _ParseAddress(pvArg)
 			End If
-		Case "Sheet"
+		Case UCase("Region")
+			If IsMissing(pvArg) Or IsEmpty(pvArg) Then
+				_PropertyGet = ""
+			Else
+				If Not ScriptForge.SF_Utils._Validate(pvArg, "Range", V_STRING) Then GoTo Finally
+				Set oAddress = _ParseAddress(pvArg)
+				With oAddress
+					Set oCursor = .XSpreadsheet.createCursorByRange(.XCellRange)
+					oCursor.collapseToCurrentRegion()
+					_PropertyGet = oCursor.AbsoluteName
+				End With
+			End If
+		Case UCase("Sheet")
 			If IsMissing(pvArg) Or IsEmpty(pvArg) Then
 				Set _PropertyGet = Nothing
 			Else
 				If Not _ValidateSheet(pvArg, "SheetName", , True) Then GoTo Finally
 				Set _PropertyGet = _ParseAddress(pvArg)
 			End If
-		Case "Sheets"
+		Case UCase("Sheets")
 			_PropertyGet = _Component.getSheets.getElementNames()
-		Case "Width"
+		Case UCase("Width")
 			If IsMissing(pvArg) Or IsEmpty(pvArg) Then
 				_PropertyGet = 0
 			Else
 				If Not ScriptForge.SF_Utils._Validate(pvArg, "Range", V_STRING) Then GoTo Finally
 				_PropertyGet = _ParseAddress(pvArg).Width
 			End If
-		Case "XCellRange"
+		Case UCase("XCellRange")
 			If IsMissing(pvArg) Or IsEmpty(pvArg) Then
 				Set _PropertyGet = Nothing
 			Else
 				If Not ScriptForge.SF_Utils._Validate(pvArg, "Range", V_STRING) Then GoTo Finally
 				Set _PropertyGet = _ParseAddress(pvArg).XCellRange
 			End If
-		Case "XSpreadsheet"
+		Case UCase("XSpreadsheet")
 			If IsMissing(pvArg) Or IsEmpty(pvArg) Then
 				Set _PropertyGet = Nothing
 			Else