ScriptForge - (SF_Calc) new ShiftDown, ShiftLeft, ShiftRight, ShiftUp methods

ShiftRight()/ShiftDown() insert empty cells at the left/on the top of
a given range and move all cells at the right/bottom of the range
accordingly.
They are similar to the Insert Cells ... menu commands

ShiftLeft()/ShiftUp() delete cells at the left/top of a given
range and move all cells at the right/below the range
accordingly.
They are similar to the Delete Cells ... menu commands

The insertion/deletion may be done for a whole column/row.

The number of columns/rows to insert/delete may be chosen.

All methods are available in Basic and Python user scripts.

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

2 files changed, 343 insertions, 12 deletions

diff --git a/wizards/source/scriptforge/python/scriptforge.py b/wizards/source/scriptforge/python/scriptforge.py
index 9972660de0d3..4643ade07229 100644
--- a/wizards/source/scriptforge/python/scriptforge.py
+++ b/wizards/source/scriptforge/python/scriptforge.py
@@ -2019,6 +2019,18 @@ class SFDocuments:
         def SetValue(self, targetrange, value):
             return self.ExecMethod(self.vbMethod + self.flgArrayArg, 'SetValue', targetrange, value)
 
+        def ShiftDown(self, range, wholerow = False, rows = 0):
+            return self.ExecMethod(self.vbMethod, 'ShiftDown', range, wholerow, rows)
+
+        def ShiftLeft(self, range, wholecolumn = False, columns = 0):
+            return self.ExecMethod(self.vbMethod, 'ShiftLeft', range, wholecolumn, columns)
+
+        def ShiftRight(self, range, wholecolumn = False, columns = 0):
+            return self.ExecMethod(self.vbMethod, 'ShiftRight', range, wholecolumn, columns)
+
+        def ShiftUp(self, range, wholerow = False, rows = 0):
+            return self.ExecMethod(self.vbMethod, 'ShiftUp', range, wholerow, rows)
+
         def SortRange(self, range, sortkeys, sortorder = 'ASC', destinationcell = ScriptForge.cstSymEmpty,
                       containsheader = False, casesensitive = False, sortcolumns = False):
             return self.ExecMethod(self.vbMethod, 'SortRange', range, sortkeys, sortorder, destinationcell,
diff --git a/wizards/source/sfdocuments/SF_Calc.xba b/wizards/source/sfdocuments/SF_Calc.xba
index a816b96920a0..75642598fa66 100644
--- a/wizards/source/sfdocuments/SF_Calc.xba
+++ b/wizards/source/sfdocuments/SF_Calc.xba
@@ -1599,6 +1599,7 @@ Public Function Methods() As Variant
 	Methods = Array( _
 					"A1Style" _
 					, "Activate" _
+					, "Charts" _
 					, "ClearAll" _
 					, "ClearFormats" _
 					, "ClearValues" _
@@ -1607,6 +1608,7 @@ Public Function Methods() As Variant
 					, "CopySheetFromFile" _
 					, "CopyToCell" _
 					, "CopyToRange" _
+					, "CreateChart" _
 					, "DAvg" _
 					, "DCount" _
 					, "DMax" _
@@ -1634,6 +1636,10 @@ Public Function Methods() As Variant
 					, "SetFormula" _
 					, "SetPrinter" _
 					, "SetValue" _
+					, "ShiftDown" _
+					, "ShiftLeft" _
+					, "ShiftRight" _
+					, "ShiftUp" _
 					, "SortRange" _
 					)
 
@@ -2271,6 +2277,330 @@ Catch:
 End Function	'	SF_Documents.SF_Calc.SetValue
 
 REM -----------------------------------------------------------------------------
+Public Function ShiftDown(Optional ByVal Range As Variant _
+								, Optional ByVal WholeRow As Variant _
+								, Optional ByVal Rows As Variant _
+								) As String
+'''	Move a specified range and all cells below in the same columns downwards by inserting empty cells
+'''	The inserted cells can span whole rows or be limited to the width of the range
+'''	The height of the inserted area is provided by the Rows argument
+'''	Nothing happens if the range shift crosses one of the edges of the worksheet
+'''	The execution of the method has no effect on the current selection
+'''	Args:
+'''		Range: the range above which cells have to be inserted, as a string
+'''		WholeRow: when True (default = False), insert whole rows
+'''		Rows: the height of the area to insert. Default = the height of the Range argument
+'''	Returns:
+'''		A string representing the new location of the initial range
+'''	Examples:
+'''		newrange = oDoc.ShiftDown("SheetX.A1:F10")						'	"$SheetX.$A$11:$F$20"
+'''		newrange = oDoc.ShiftDown("SheetX.A1:F10", Rows := 3)			'	"$SheetX.$A$4:$F$13"
+
+Dim sShift As String			'	Return value
+Dim oSourceAddress As Object	'	Alias of Range as _Address
+Dim lHeight As Long				'	Range height
+Dim oShiftAddress As Object		'	com.sun.star.table.CellRangeAddress - Range adjusted to the right width
+Dim lShiftMode As Long			'	One of the com.sun.star.sheet.CellInsertMode enum values
+
+Const cstThisSub = "SFDocuments.Calc.ShiftDown"
+Const cstSubArgs = "Range, [WholeRow=False], [Rows]"
+
+	If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+	sShift = ""
+
+Check:
+	If IsMissing(WholeRow) Or IsEmpty(WholeRow) Then WholeRow = False
+	If IsMissing(Rows) Or IsEmpty(Rows) Then Rows = 0
+	If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+		If Not _IsStillAlive(True) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(Range, "Range", V_STRING) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(WholeRow, "WholeRow", ScriptForge.V_BOOLEAN) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(Rows, "Rows", ScriptForge.V_NUMERIC) Then GoTo Finally
+	End If
+
+Try:
+	Set oSourceAddress = _ParseAddress(Range)
+
+	With oSourceAddress
+
+		'	Manage the height of the area to shift
+		'	The insertCells() method inserts a number of rows equal to the height of the cell range to shift
+		lHeight = .Height
+		If Rows = 0 Then Rows = lHeight
+		If _LastCell(.XSpreadsheet)(1) + Rows > MAXROWS Then GoTo Catch
+		If Rows <> lHeight Then
+			Set oShiftAddress = _Offset(oSourceAddress, 0, 0, Rows, 0).XCellRange.RangeAddress
+		Else
+			Set oShiftAddress = .XCellRange.RangeAddress
+		End If
+
+		'	Determine the shift mode
+		With com.sun.star.sheet.CellInsertMode
+			If WholeRow Then lShiftMode = .ROWS Else lShiftMode = .DOWN
+		End With
+
+		'	Move the cells as requested. This modifies .XCellRange
+		.XSpreadsheet.insertCells(oShiftAddress, lShiftMode)
+
+		'	Determine the receiving area
+		sShift = .XCellRange.AbsoluteName
+
+	End With
+
+Finally:
+	ShiftDown = sShift
+	ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+	Exit Function
+Catch:
+	'	When error, return the original range
+	If Not IsNull(oSourceAddress) Then sShift = oSourceAddress.RangeName
+	GoTo Finally
+End Function    '   SFDocuments.SF_Calc.ShiftDown
+
+REM -----------------------------------------------------------------------------
+Public Function ShiftLeft(Optional ByVal Range As Variant _
+								, Optional ByVal WholeColumn As Variant _
+								, Optional ByVal Columns As Variant _
+								) As String
+'''	Delete the leftmost columns of a specified range and move all cells at their right leftwards
+'''	The deleted cells can span whole columns or be limited to the height of the range
+'''	The width of the deleted area is provided by the Columns argument
+'''	The execution of the method has no effect on the current selection
+'''	Args:
+'''		Range: the range in which cells have to be erased, as a string
+'''		WholeColumn: when True (default = False), erase whole columns
+'''		Columns: the width of the area to delete.
+'''			Default = the width of the Range argument, it is also its maximum value
+'''	Returns:
+'''		A string representing the location of the remaining part of the initial range,
+'''		or the zero-length string if the whole range has been deleted
+'''	Examples:
+'''		newrange = oDoc.ShiftLeft("SheetX.G1:L10")						'	"""
+'''		newrange = oDoc.ShiftLeft("SheetX.G1:L10", Columns := 3)		'	"$SheetX.$G$1:$I$10"
+
+Dim sShift As String			'	Return value
+Dim oSourceAddress As Object	'	Alias of Range as _Address
+Dim lWidth As Long				'	Range width
+Dim oShiftAddress As Object		'	com.sun.star.table.CellRangeAddress - Range adjusted to the right width
+Dim lShiftMode As Long			'	One of the com.sun.star.sheet.CellDeleteMode enum values
+
+Const cstThisSub = "SFDocuments.Calc.ShiftLeft"
+Const cstSubArgs = "Range, [WholeColumn=False], [Columns]"
+
+	If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+	sShift = ""
+
+Check:
+	If IsMissing(WholeColumn) Or IsEmpty(WholeColumn) Then WholeColumn = False
+	If IsMissing(Columns) Or IsEmpty(Columns) Then Columns = 0
+	If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+		If Not _IsStillAlive(True) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(Range, "Range", V_STRING) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(WholeColumn, "WholeColumn", ScriptForge.V_BOOLEAN) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(Columns, "Columns", ScriptForge.V_NUMERIC) Then GoTo Finally
+	End If
+
+Try:
+	Set oSourceAddress = _ParseAddress(Range)
+
+	With oSourceAddress
+
+		'	Manage the width of the area to delete
+		'	The removeRange() method erases a number of columns equal to the width of the cell range to delete
+		lWidth = .Width
+		If Columns = 0 Then Columns = lWidth
+		If Columns < lWidth Then
+			Set oShiftAddress = _Offset(oSourceAddress, 0, 0, 0, Columns).XCellRange.RangeAddress
+		Else						'	Columns is capped at the range width
+			Set oShiftAddress = .XCellRange.RangeAddress
+		End If
+
+		'	Determine the Delete mode
+		With com.sun.star.sheet.CellDeleteMode
+			If WholeColumn Then lShiftMode = .COLUMNS Else lShiftMode = .LEFT
+		End With
+
+		'	Move the cells as requested. This modifies .XCellRange
+		.XSpreadsheet.removeRange(oShiftAddress, lShiftMode)
+
+		'	Determine the remaining area
+		If Columns < lWidth Then sShift = .XCellRange.AbsoluteName
+
+	End With
+
+Finally:
+	ShiftLeft = sShift
+	ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+	Exit Function
+Catch:
+	'	When error, return the original range
+	If Not IsNull(oSourceAddress) Then sShift = oSourceAddress.RangeName
+	GoTo Finally
+End Function    '   SFDocuments.SF_Calc.ShiftLeft
+
+REM -----------------------------------------------------------------------------
+Public Function ShiftRight(Optional ByVal Range As Variant _
+								, Optional ByVal WholeColumn As Variant _
+								, Optional ByVal Columns As Variant _
+								) As String
+'''	Move a specified range and all next cells in the same rows to the right by inserting empty cells
+'''	The inserted cells can span whole columns or be limited to the height of the range
+'''	The width of the inserted area is provided by the Columns argument
+'''	Nothing happens if the range shift crosses one of the edges of the worksheet
+'''	The execution of the method has no effect on the current selection
+'''	Args:
+'''		Range: the range before which cells have to be inserted, as a string
+'''		WholeColumn: when True (default = False), insert whole columns
+'''		Columns: the width of the area to insert. Default = the width of the Range argument
+'''	Returns:
+'''		A string representing the new location of the initial range
+'''	Examples:
+'''		newrange = oDoc.ShiftRight("SheetX.A1:F10")						'	"$SheetX.$G$1:$L$10"
+'''		newrange = oDoc.ShiftRight("SheetX.A1:F10", Columns := 3)		'	"$SheetX.$D$1:$I$10"
+
+Dim sShift As String			'	Return value
+Dim oSourceAddress As Object	'	Alias of Range as _Address
+Dim lWidth As Long				'	Range width
+Dim oShiftAddress As Object		'	com.sun.star.table.CellRangeAddress - Range adjusted to the right width
+Dim lShiftMode As Long			'	One of the com.sun.star.sheet.CellInsertMode enum values
+
+Const cstThisSub = "SFDocuments.Calc.ShiftRight"
+Const cstSubArgs = "Range, [WholeColumn=False], [Columns]"
+
+	If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+	sShift = ""
+
+Check:
+	If IsMissing(WholeColumn) Or IsEmpty(WholeColumn) Then WholeColumn = False
+	If IsMissing(Columns) Or IsEmpty(Columns) Then Columns = 0
+	If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+		If Not _IsStillAlive(True) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(Range, "Range", V_STRING) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(WholeColumn, "WholeColumn", ScriptForge.V_BOOLEAN) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(Columns, "Columns", ScriptForge.V_NUMERIC) Then GoTo Finally
+	End If
+
+Try:
+	Set oSourceAddress = _ParseAddress(Range)
+
+	With oSourceAddress
+
+		'	Manage the width of the area to Shift
+		'	The insertCells() method inserts a number of columns equal to the width of the cell range to Shift
+		lWidth = .Width
+		If Columns = 0 Then Columns = lWidth
+		If _LastCell(.XSpreadsheet)(0) + Columns > MAXCOLS Then GoTo Catch
+		If Columns <> lWidth Then
+			Set oShiftAddress = _Offset(oSourceAddress, 0, 0, 0, Columns).XCellRange.RangeAddress
+		Else
+			Set oShiftAddress = .XCellRange.RangeAddress
+		End If
+
+		'	Determine the Shift mode
+		With com.sun.star.sheet.CellInsertMode
+			If WholeColumn Then lShiftMode = .COLUMNS Else lShiftMode = .RIGHT
+		End With
+
+		'	Move the cells as requested. This modifies .XCellRange
+		.XSpreadsheet.insertCells(oShiftAddress, lShiftMode)
+
+		'	Determine the receiving area
+		sShift = .XCellRange.AbsoluteName
+
+	End With
+
+Finally:
+	ShiftRight = sShift
+	ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+	Exit Function
+Catch:
+	'	When error, return the original range
+	If Not IsNull(oSourceAddress) Then sShift = oSourceAddress.RangeName
+	GoTo Finally
+End Function    '   SFDocuments.SF_Calc.ShiftRight
+
+REM -----------------------------------------------------------------------------
+Public Function ShiftUp(Optional ByVal Range As Variant _
+								, Optional ByVal WholeRow As Variant _
+								, Optional ByVal Rows As Variant _
+								) As String
+'''	Delete the topmost rows of a specified range and move all cells below upwards
+'''	The deleted cells can span whole rows or be limited to the width of the range
+'''	The height of the deleted area is provided by the Rows argument
+'''	The execution of the method has no effect on the current selection
+'''	Args:
+'''		Range: the range in which cells have to be erased, as a string
+'''		WholeRow: when True (default = False), erase whole rows
+'''		Rows: the height of the area to delete.
+'''			Default = the height of the Range argument, it is also its maximum value
+'''	Returns:
+'''		A string representing the location of the remaining part of the initial range,
+'''		or the zero-length string if the whole range has been deleted
+'''	Examples:
+'''		newrange = oDoc.ShiftUp("SheetX.G1:L10")				'	""
+'''		newrange = oDoc.ShiftUp("SheetX.G1:L10", Rows := 3)		'	"$SheetX.$G$1:$I$10"
+
+Dim sShift As String			'	Return value
+Dim oSourceAddress As Object	'	Alias of Range as _Address
+Dim lHeight As Long				'	Range height
+Dim oShiftAddress As Object		'	com.sun.star.table.CellRangeAddress - Range adjusted to the right height
+Dim lShiftMode As Long			'	One of the com.sun.star.sheet.CellDeleteMode enum values
+
+Const cstThisSub = "SFDocuments.Calc.ShiftUp"
+Const cstSubArgs = "Range, [WholeRow=False], [Rows]"
+
+	If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+	sShift = ""
+
+Check:
+	If IsMissing(WholeRow) Or IsEmpty(WholeRow) Then WholeRow = False
+	If IsMissing(Rows) Or IsEmpty(Rows) Then Rows = 0
+	If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+		If Not _IsStillAlive(True) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(Range, "Range", V_STRING) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(WholeRow, "WholeRow", ScriptForge.V_BOOLEAN) Then GoTo Finally
+		If Not ScriptForge.SF_Utils._Validate(Rows, "Rows", ScriptForge.V_NUMERIC) Then GoTo Finally
+	End If
+
+Try:
+	Set oSourceAddress = _ParseAddress(Range)
+
+	With oSourceAddress
+
+		'	Manage the height of the area to delete
+		'	The removeRange() method erases a number of rows equal to the height of the cell range to delete
+		lHeight = .Height
+		If Rows = 0 Then Rows = lHeight
+		If Rows < lHeight Then
+			Set oShiftAddress = _Offset(oSourceAddress, 0, 0, Rows, 0).XCellRange.RangeAddress
+		Else						'	Rows is capped at the range height
+			Set oShiftAddress = .XCellRange.RangeAddress
+		End If
+
+		'	Determine the Delete mode
+		With com.sun.star.sheet.CellDeleteMode
+			If WholeRow Then lShiftMode = .ROWS Else lShiftMode = .UP
+		End With
+
+		'	Move the cells as requested. This modifies .XCellRange
+		.XSpreadsheet.removeRange(oShiftAddress, lShiftMode)
+
+		'	Determine the remaining area
+		If Rows < lHeight Then sShift = .XCellRange.AbsoluteName
+
+	End With
+
+Finally:
+	ShiftUp = sShift
+	ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+	Exit Function
+Catch:
+	'	When error, return the original range
+	If Not IsNull(oSourceAddress) Then sShift = oSourceAddress.RangeName
+	GoTo Finally
+End Function    '   SFDocuments.SF_Calc.ShiftUp
+
+REM -----------------------------------------------------------------------------
 Public Function SortRange(Optional ByVal Range As Variant _
 								, Optional ByVal SortKeys As Variant _
 								, Optional ByVal SortOrder As Variant _
@@ -3069,7 +3399,7 @@ Dim oSelect As Object				'	Current selection
 		If IsNull(.XCellRange) Then GoTo CatchAddress
 
 		Set oRangeAddress = .XCellRange.RangeAddress
-		.RangeName = _RangeToString(oRangeAddress)
+		.RangeName = .XCellRange.AbsoluteName
 		.Height = oRangeAddress.EndRow - oRangeAddress.StartRow + 1
 		.Width = oRangeAddress.EndColumn - oRangeAddress.StartColumn + 1
 
@@ -3215,17 +3545,6 @@ Finally:
 End Function	'	SFDocuments.SF_Calc._PropertyGet
 
 REM -----------------------------------------------------------------------------
-Private Function _RangeToString(ByRef poAddress As Object) As String
-'''	Converts a range address to its A1 notation)
-
-	With poAddress
-		_RangeToString = _GetColumnName(.StartColumn + 1) & CStr(.StartRow + 1) & ":" _
-									& _GetColumnName(.EndColumn + 1) & CStr(.EndRow + 1)
-	End With
-
-End Function	'	SFDocuments.SF_Calc._RangeToString
-
-REM -----------------------------------------------------------------------------
 Private Function _Repr() As String
 '''	Convert the SF_Calc instance to a readable string, typically for debugging purposes (DebugPrint ...)
 '''	Args: