wizards/source/scriptforge/python/scriptforge.py | 12
wizards/source/sfdocuments/SF_Calc.xba | 343 ++++++++++++++++++++++-
2 files changed, 343 insertions(+), 12 deletions(-)
New commits:
commit 020967fde86d77c362658e49e256add265806292
Author: Jean-Pierre Ledure <j...@ledure.be>
AuthorDate: Sun Oct 31 16:12:49 2021 +0100
Commit: Jean-Pierre Ledure <j...@ledure.be>
CommitDate: Sun Oct 31 18:16:42 2021 +0100
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 <j...@ledure.be>
Tested-by: Jenkins
Reviewed-by: Jean-Pierre Ledure <j...@ledure.be>
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" _
)
@@ -2270,6 +2276,330 @@ Catch:
GoTo Finally
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 _
@@ -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
@@ -3214,17 +3544,6 @@ Finally:
Exit Function
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 ...)
