ScriptForge - (Calc) extend use cases of FilterFormula

A FilterFormula is a Calc formula that returns TRUE or FALSE
The formula is expressed in terms of
- the top-left cell of the range when FilterScope = "CELL"
- the topmost row of the range when FilterScope = "ROW"
- the leftmost column of the range when FilterScope = "COLUMN"
After pasting, the relative and absolute references
will be interpreted correctly.

The FilterScope indicates the way the formula is applied,
once by row, column or individual cell.

The requested action is done only on the rows/columns/cells
for which the FilterFormula returns True.

Next methods receive 2 new arguments:
=>   FilterFormula and FilterScope:
ClearAll()
ClearFormats()
ClearValues()
SetCellStyle()

These changes are valid for Basic and Python user scripts.
Without these new arguments above methods behave as before.

The documentation needs to be adapted:
- inclusion of FilterFormula and FilterScope concepts in the
DEFINITIONS section of the SF_Calc help page
- new arguments to add in resp. methods

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

2 files changed, 149 insertions, 111 deletions

diff --git a/wizards/source/scriptforge/python/scriptforge.py b/wizards/source/scriptforge/python/scriptforge.py
index 6d944d1460aa..e4a0aa5ea5b4 100644
--- a/wizards/source/scriptforge/python/scriptforge.py
+++ b/wizards/source/scriptforge/python/scriptforge.py
@@ -2082,14 +2082,14 @@ class SFDocuments:
         def Charts(self, sheetname, chartname = ''):
             return self.ExecMethod(self.vbMethod + self.flgArrayRet, 'Charts', sheetname, chartname)
 
-        def ClearAll(self, range):
-            return self.ExecMethod(self.vbMethod, 'ClearAll', range)
+        def ClearAll(self, range, filterformula = '', filterscope = ''):
+            return self.ExecMethod(self.vbMethod, 'ClearAll', range, filterformula, filterscope)
 
-        def ClearFormats(self, range):
-            return self.ExecMethod(self.vbMethod, 'ClearFormats', range)
+        def ClearFormats(self, range, filterformula = '', filterscope = ''):
+            return self.ExecMethod(self.vbMethod, 'ClearFormats', range, filterformula, filterscope)
 
-        def ClearValues(self, range):
-            return self.ExecMethod(self.vbMethod, 'ClearValues', range)
+        def ClearValues(self, range, filterformula = '', filterscope = ''):
+            return self.ExecMethod(self.vbMethod, 'ClearValues', range, filterformula, filterscope)
 
         def CompactLeft(self, range, wholecolumn = False, filterformula = ''):
             return self.ExecMethod(self.vbMethod, 'CompactLeft', range, wholecolumn, filterformula)
@@ -2194,8 +2194,8 @@ class SFDocuments:
         def SetArray(self, targetcell, value):
             return self.ExecMethod(self.vbMethod + self.flgArrayArg, 'SetArray', targetcell, value)
 
-        def SetCellStyle(self, targetrange, style):
-            return self.ExecMethod(self.vbMethod, 'SetCellStyle', targetrange, style)
+        def SetCellStyle(self, targetrange, style, filterformula = '', filterscope = ''):
+            return self.ExecMethod(self.vbMethod, 'SetCellStyle', targetrange, style, filterformula, filterscope)
 
         def SetFormula(self, targetrange, formula):
             return self.ExecMethod(self.vbMethod + self.flgArrayArg, 'SetFormula', targetrange, formula)
diff --git a/wizards/source/sfdocuments/SF_Calc.xba b/wizards/source/sfdocuments/SF_Calc.xba
index 04b4924f1924..3530544bc038 100644
--- a/wizards/source/sfdocuments/SF_Calc.xba
+++ b/wizards/source/sfdocuments/SF_Calc.xba
@@ -543,126 +543,63 @@ Catch:
 End Function    '   SFDocuments.SF_Calc.Charts
 
 REM -----------------------------------------------------------------------------
-Public Sub ClearAll(Optional ByVal Range As Variant) As String
+Public Sub ClearAll(Optional ByVal Range As Variant _
+							, Optional FilterFormula As Variant _
+							, Optional FilterScope As Variant _
+							)
 '''	Clear entirely the given range
 '''	Args:
 '''		Range : the cell or the range as a string that should be cleared
+'''		FilterFormula: a Calc formula to select among the given Range
+'''			When left empty, all the cells of the range are cleared
+'''		FilterScope: "CELL" (default value), "ROW" or "COLUMN"
+'''			When FilterFormula is present, FilterScope is mandatory
 '''	Examples:
-'''		oDoc.ClearAll("SheetX")		' Clears the used area of the sheet
+'''		oDoc.ClearAll("SheetX")						' Clears the used area of the sheet
+'''		oDoc.ClearAll("A1:J20", "=($A1=0)", "ROW")	' Clears all rows when 1st cell is zero
 
-Dim lClear As Long				'	The elements to clear
-Dim oRange As Object			'	Alias of Range
-Const cstThisSub = "SFDocuments.Calc.ClearAll"
-Const cstSubArgs = "Range"
-
-	If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
-
-Check:
-	If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
-		If Not _IsStillAlive() Then GoTo Finally
-		If Not ScriptForge.SF_Utils._Validate(Range, "Range", Array(V_STRING, ScriptForge.V_OBJECT)) Then GoTo Finally
-	End If
-
-Try:
-	With com.sun.star.sheet.CellFlags
-		lClear = 0 _
-					+ .VALUE _
-					+ .DATETIME _
-					+ .STRING _
-					+ .ANNOTATION _
-					+ .FORMULA _
-					+ .HARDATTR _
-					+ .STYLES _
-					+ .OBJECTS _
-					+ .EDITATTR _
-					+ .FORMATTED
-		If VarType(Range) = V_STRING Then Set oRange = _ParseAddress(Range) Else Set oRange = Range
-		oRange.XCellRange.clearContents(lClear)
-	End With
+	_ClearRange("All", Range, FilterFormula, FilterScope)
 
-Finally:
-	ScriptForge.SF_Utils._ExitFunction(cstThisSub)
-	Exit Sub
-Catch:
-	GoTo Finally
 End Sub	'	SF_Documents.SF_Calc.ClearAll
 
 REM -----------------------------------------------------------------------------
-Public Sub ClearFormats(Optional ByVal Range As Variant) As String
+Public Sub ClearFormats(Optional ByVal Range As Variant _
+							, Optional FilterFormula As Variant _
+							, Optional FilterScope As Variant _
+							)
 '''	Clear all the formatting elements of the given range
 '''	Args:
 '''		Range : the cell or the range as a string that should be cleared
+'''		FilterFormula: a Calc formula to select among the given Range
+'''			When left empty, all the cells of the range are cleared
+'''		FilterScope: "CELL" (default value), "ROW" or "COLUMN"
+'''			When FilterFormula is present, FilterScope is mandatory
 '''	Examples:
-'''		oDoc.ClearFormats("SheetX:A1:E100")		' Clear the formats of the given range
-
-Dim lClear As Long				'	The elements to clear
-Dim oRange As Object			'	Alias of Range
-Const cstThisSub = "SFDocuments.Calc.ClearFormats"
-Const cstSubArgs = "Range"
+'''		oDoc.ClearFormats("SheetX.*")							' Clears the used area of the sheet
+'''		oDoc.ClearFormats("A1:J20", "=(MOD(A1;0)=0)", "CELL")	' Clears all even cells
 
-	If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+	_ClearRange("Formats", Range, FilterFormula, FilterScope)
 
-Check:
-	If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
-		If Not _IsStillAlive() Then GoTo Finally
-		If Not ScriptForge.SF_Utils._Validate(Range, "Range", V_STRING) Then GoTo Finally
-	End If
-
-Try:
-	With com.sun.star.sheet.CellFlags
-		lClear = 0 _
-					+ .HARDATTR _
-					+ .STYLES _
-					+ .EDITATTR _
-					+ .FORMATTED
-		Set oRange = _ParseAddress(Range)
-		oRange.XCellRange.clearContents(lClear)
-	End With
-
-Finally:
-	ScriptForge.SF_Utils._ExitFunction(cstThisSub)
-	Exit Sub
-Catch:
-	GoTo Finally
 End Sub	'	SF_Documents.SF_Calc.ClearFormats
 
 REM -----------------------------------------------------------------------------
-Public Sub ClearValues(Optional ByVal Range As Variant) As String
+Public Sub ClearValues(Optional ByVal Range As Variant _
+							, Optional FilterFormula As Variant _
+							, Optional FilterScope As Variant _
+							)
 '''	Clear values and formulas in the given range
 '''	Args:
 '''		Range : the cell or the range as a string that should be cleared
+'''		FilterFormula: a Calc formula to select among the given Range
+'''			When left empty, all the cells of the range are cleared
+'''		FilterScope: "CELL" (default value), "ROW" or "COLUMN"
+'''			When FilterFormula is present, FilterScope is mandatory
 '''	Examples:
-'''		oDoc.ClearValues("SheetX:*")		' Clears the used area of the sheet
+'''		oDoc.ClearValues("SheetX.*")					' Clears the used area of the sheet
+'''		oDoc.ClearValues("A2:A20", "=(A2=A1)", "CELL")	' Clears all duplicate cells
 
-Dim lClear As Long				'	The elements to clear
-Dim oRange As Object			'	Alias of Range
-Const cstThisSub = "SFDocuments.Calc.ClearValues"
-Const cstSubArgs = "Range"
-
-	If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
-
-Check:
-	If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
-		If Not _IsStillAlive() Then GoTo Finally
-		If Not ScriptForge.SF_Utils._Validate(Range, "Range", V_STRING) Then GoTo Finally
-	End If
-
-Try:
-	With com.sun.star.sheet.CellFlags
-		lClear = 0 _
-					+ .VALUE _
-					+ .DATETIME _
-					+ .STRING _
-					+ .FORMULA
-		Set oRange = _ParseAddress(Range)
-		oRange.XCellRange.clearContents(lClear)
-	End With
+	_ClearRange("Values", Range, FilterFormula, FilterScope)
 
-Finally:
-	ScriptForge.SF_Utils._ExitFunction(cstThisSub)
-	Exit Sub
-Catch:
-	GoTo Finally
 End Sub	'	SF_Documents.SF_Calc.ClearValues
 
 REM -----------------------------------------------------------------------------
@@ -2779,42 +2716,70 @@ End Function	'	SF_Documents.SF_Calc.SetArray
 REM -----------------------------------------------------------------------------
 Public Function SetCellStyle(Optional ByVal TargetRange As Variant _
 								, Optional ByVal Style As Variant _
+								, Optional ByVal FilterFormula As Variant _
+								, Optional ByVal FilterScope As Variant _
 								) As String
 '''	Apply the given cell style in the given 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 range is updated and the remainder of the sheet is left untouched
+'''	Either the full range is updated or a selection based on a FilterFormula
 '''	Args:
 '''		TargetRange : the range as a string that should receive a new cell style
 '''		Style: the style name as a string
+'''		FilterFormula: a Calc formula to select among the given Range
+'''			When left empty, all the cells of the range are formatted with the new style
+'''		FilterScope: "CELL" (default value), "ROW" or "COLUMN"
+'''			When FilterFormula is present, FilterScope is mandatory
 '''	Returns:
 '''		A string representing the updated range
 '''	Examples:
 '''		oDoc.SetCellStyle("A1:F1", "Heading 2")
+'''		oDoc.SetCellStype("A1:J20", "Wrong", "=(A1<0)", "CELL")
 
 Dim sSet As String					'	Return value
 Dim oAddress As _Address				'	Alias of TargetRange
 Dim oStyleFamilies As Object		'	com.sun.star.container.XNameAccess
 Dim vStyles As Variant				'	Array of existing cell styles
+Dim vRanges() As Variant			'	Array of filtered ranges
+Dim i As Long
+
 Const cstStyle = "CellStyles"
 Const cstThisSub = "SFDocuments.Calc.SetCellStyle"
-Const cstSubArgs = "TargetRange, Style"
+Const cstSubArgs = "TargetRange, Style, [FilterFormula=""], [FilterScope=""CELL""|""ROW""|""COLUMN""]"
 
 	If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
 	sSet = ""
 
 Check:
+	If IsMissing(FilterFormula) Or IsEmpty(FilterFormula) Then FilterFormula = ""
+	If IsMissing(FilterScope) Or IsEmpty(FilterScope) Then FilterScope = "CELL"
 	If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
 		If Not _IsStillAlive() Then GoTo Finally
-		If Not ScriptForge.SF_Utils._Validate(TargetRange, "TargetRange", V_STRING) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(TargetRange, "TargetRange", Array(V_STRING, ScriptForge.V_OBJECT)) Then GoTo Finally
+		'	Check that the given style really exists
 		Set oStyleFamilies = _Component.StyleFamilies
 		If oStyleFamilies.hasByName(cstStyle) Then vStyles = oStyleFamilies.getByName(cstStyle).getElementNames() Else vStyles = Array()
 		If Not ScriptForge.SF_Utils._Validate(Style, "Style", V_STRING, vStyles) Then GoTo Finally
+		'	Filter formula
+		If Not ScriptForge.SF_Utils._Validate(FilterFormula, "FilterFormula", V_STRING) Then GoTo Finally
+		If Len(FilterFormula) > 0 Then
+			If Not ScriptForge.SF_Utils._Validate(FilterScope, "FilterScope", V_STRING, Array("CELL", "ROW", "COLUMN")) Then GoTo Finally
+		Else
+			If Not ScriptForge.SF_Utils._Validate(FilterScope, "FilterScope", V_STRING) Then GoTo Finally
+		End If
 	End If
 
 Try:
-	Set oAddress = _ParseAddress(TargetRange)
+	If VarType(TargetRange) = V_STRING Then Set oAddress = _ParseAddress(TargetRange) Else Set oAddress = TargetRange
 	With oAddress
-		.XCellRange.CellStyle = Style
+		If Len(FilterFormula) = 0 Then			'	When the full range should be updated
+			.XCellRange.CellStyle = Style
+		Else									'	When the range has to be cut in subranges
+			vRanges() = _ComputeFilter(oAddress, FilterFormula, UCase(FilterScope))
+			For i = 0 To UBound(vRanges)
+				vRanges(i).XCellRange.CellStyle = Style
+			Next i
+		End If
 		sSet = .RangeName
 	End With
 
@@ -3650,7 +3615,80 @@ End Function	'   SFDocuments.SF_Calc.SetPrinter
 REM =========================================================== PRIVATE FUNCTIONS
 
 REM -----------------------------------------------------------------------------
-Public Function _ComputeFilter(ByVal poRange As Object _
+Private Sub _ClearRange(ByVal psTarget As String _
+							, Optional ByVal Range As Variant _
+							, Optional FilterFormula As Variant _
+							, Optional FilterScope As Variant _
+							)
+'''	Clear the given range with the given options
+'''	The range may be filtered by a formula for a selective clearance
+'''	Arguments checking is done in this Sub, not in the calling one
+'''	Args:
+'''		psTarget: "All", "Formats" or "Values"
+'''		Range: the range to clear as a string
+'''		FilterFormula: a selection of cells based on a Calc formula
+'''			When left empty, all the cells of the range are cleared
+'''		psFilterScope: "CELL", "ROW" or "COLUMN"
+
+Dim lClear As Long				'	A combination of com.sun.star.sheet.CellFlags
+Dim oRange As Object			'	Alias of Range
+Dim vRanges() As Variant		'	Array of subranges resulting from the application of the filter
+Dim i As Long
+
+Dim cstThisSub As String		:	cstThisSub = "SFDocuments.Calc.Clear" & psTarget
+Const cstSubArgs = "Range, [FilterFormula=""], [FilterScope=""CELL""|""ROW""|""COLUMN""]"
+
+	If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+
+Check:
+	If IsMissing(FilterFormula) Or IsEmpty(FilterFormula) Then FilterFormula = ""
+	If IsMissing(FilterScope) Or IsEmpty(FilterScope) Then FilterScope = "CELL"
+	If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+		If Not _IsStillAlive() Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(Range, "Range", Array(V_STRING, ScriptForge.V_OBJECT)) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(FilterFormula, "FilterFormula", V_STRING) Then GoTo Finally
+		If Len(FilterFormula) > 0 Then
+			If Not ScriptForge.SF_Utils._Validate(FilterScope, "FilterScope", V_STRING, Array("CELL", "ROW", "COLUMN")) Then GoTo Finally
+		Else
+			If Not ScriptForge.SF_Utils._Validate(FilterScope, "FilterScope", V_STRING) Then GoTo Finally
+		End If
+	End If
+
+Try:
+	With com.sun.star.sheet.CellFlags
+		Select Case psTarget
+			Case "All"
+				lClear = .VALUE + .DATETIME + .STRING + .ANNOTATION + .FORMULA _
+						+ .HARDATTR + .STYLES + .OBJECTS + .EDITATTR + .FORMATTED
+			Case "Formats"
+				lClear = .HARDATTR + .STYLES + .EDITATTR + .FORMATTED
+			Case "Values"
+				lClear = .VALUE + .DATETIME + .STRING + .FORMULA
+		End Select
+	End With
+
+	If VarType(Range) = V_STRING Then Set oRange = _ParseAddress(Range) Else Set oRange = Range
+
+	'	Without filter, the whole range is cleared
+	'	Otherwise the filter cuts the range in subranges and clears them one by one
+	If Len(FilterFormula) = 0 Then
+		oRange.XCellRange.clearContents(lClear)
+	Else
+		vRanges() = _ComputeFilter(oRange, FilterFormula, UCase(FilterScope))
+		For i = 0 To UBound(vRanges)
+			vRanges(i).XCellRange.clearContents(lClear)
+		Next i
+	End If
+
+Finally:
+	ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+	Exit Sub
+Catch:
+	GoTo Finally
+End Sub	'	SF_Documents.SF_Calc._ClearRange
+
+REM -----------------------------------------------------------------------------
+Private Function _ComputeFilter(ByRef poRange As Object _
 								, ByVal psFilterFormula As String _
 								, ByVal psFilterScope As String _
 								) As Variant
@@ -4627,4 +4665,4 @@ CatchSheet:
 End Function	'	SFDocuments.SF_Calc._ValidateSheetName
 
 REM ============================================ END OF SFDOCUMENTS.SF_CALC
-</script:module>
+</script:module>
\ No newline at end of file