diff options
| -rw-r--r-- | wizards/source/scriptforge/python/scriptforge.py | 12 | ||||
| -rw-r--r-- | wizards/source/sfdocuments/SF_Calc.xba | 343 |
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: |