diff options
-rw-r--r-- | wizards/source/scriptforge/SF_PythonHelper.xba | 1 | ||||
-rw-r--r-- | wizards/source/scriptforge/python/scriptforge.py | 27 | ||||
-rw-r--r-- | wizards/source/sfdocuments/SF_Calc.xba | 168 |
3 files changed, 95 insertions, 101 deletions
diff --git a/wizards/source/scriptforge/SF_PythonHelper.xba b/wizards/source/scriptforge/SF_PythonHelper.xba index f32e077d83d0..5b919ba5abe1 100644 --- a/wizards/source/scriptforge/SF_PythonHelper.xba +++ b/wizards/source/scriptforge/SF_PythonHelper.xba @@ -777,7 +777,6 @@ Try: Select Case Script Case "Charts" : vReturn = vBasicObject.Charts(vArgs(0), vArgs(1)) Case "Forms" : vReturn = vBasicObject.Forms(vArgs(0), vArgs(1)) - Case "GetRangeAddress" : vReturn = vBasicObject.GetRangeAddress(vArgs(0), vArgs(1)) Case "GetFormula" : vReturn = vBasicObject.GetFormula(vArgs(0)) Case "GetValue" : vReturn = vBasicObject.GetValue(vArgs(0)) Case "SetArray" : vReturn = vBasicObject.SetArray(vArgs(0), vArgs(1)) diff --git a/wizards/source/scriptforge/python/scriptforge.py b/wizards/source/scriptforge/python/scriptforge.py index ebd33ab9a7f2..31a3f29ee799 100644 --- a/wizards/source/scriptforge/python/scriptforge.py +++ b/wizards/source/scriptforge/python/scriptforge.py @@ -1876,17 +1876,26 @@ class SFDocuments: return windowname, # Next functions are implemented in Basic as read-only properties with 1 argument + def FirstCell(self, rangename): + return self.GetProperty('FirstCell', rangename) + + def FirstColumn(self, rangename): + return self.GetProperty('FirstColumn', rangename) + + def FirstRow(self, rangename): + return self.GetProperty('FirstRow', rangename) + def Height(self, rangename): return self.GetProperty('Height', rangename) - def LastCell(self, sheetname): - return self.GetProperty('LastCell', sheetname) + def LastCell(self, rangename): + return self.GetProperty('LastCell', rangename) - def LastColumn(self, sheetname): - return self.GetProperty('LastColumn', sheetname) + def LastColumn(self, rangename): + return self.GetProperty('LastColumn', rangename) - def LastRow(self, sheetname): - return self.GetProperty('LastRow', sheetname) + def LastRow(self, rangename): + return self.GetProperty('LastRow', rangename) def Range(self, rangename): return self.GetProperty('Range', rangename) @@ -1897,6 +1906,9 @@ class SFDocuments: def Sheet(self, sheetname): return self.GetProperty('Sheet', sheetname) + def SheetName(self, rangename): + return self.GetProperty('SheetName', rangename) + def Width(self, rangename): return self.GetProperty('Width', rangename) @@ -1974,9 +1986,6 @@ class SFDocuments: def GetFormula(self, range): return self.ExecMethod(self.vbMethod + self.flgArrayRet, 'GetFormula', range) - def GetRangeAddress(self, range, addressitem = ''): - return self.ExecMethod(self.vbMethod + self.flgArrayArg, 'GetRangeAddress', range, addressitem) - def GetValue(self, range): return self.ExecMethod(self.vbMethod + self.flgArrayRet, 'GetValue', range) diff --git a/wizards/source/sfdocuments/SF_Calc.xba b/wizards/source/sfdocuments/SF_Calc.xba index 96dd08d5562c..87c111a64c96 100644 --- a/wizards/source/sfdocuments/SF_Calc.xba +++ b/wizards/source/sfdocuments/SF_Calc.xba @@ -127,6 +127,10 @@ Private Const SERVICEREFERENCE = "SFDocuments.CalcReference" Private Const ISCALCFORM = 2 ' Form is stored in a Calc document +Private Const cstSPECIALCHARS = " `~!@#$%^&()-_=+{}|;:,<.>""" + ' Presence of a special character forces surrounding the sheet name with single quotes in absolute addresses + + REM ====================================================== CONSTRUCTOR/DESTRUCTOR REM ----------------------------------------------------------------------------- @@ -206,27 +210,48 @@ Catch: End Property ' SFDocuments.SF_Calc.CurrentSelection (let) REM ----------------------------------------------------------------------------- +Property Get FirstCell(Optional ByVal RangeName As Variant) As String +''' Returns the First used cell in a given range or sheet +''' When the argument is a sheet it will always return the "sheet.$A$1" cell + FirstCell = _PropertyGet("FirstCell", RangeName) +End Property ' SFDocuments.SF_Calc.FirstCell + +REM ----------------------------------------------------------------------------- +Property Get FirstColumn(Optional ByVal RangeName As Variant) As Long +''' Returns the leftmost column in a given sheet or range +''' When the argument is a sheet it will always return 1 + FirstColumn = _PropertyGet("FirstColumn", RangeName) +End Property ' SFDocuments.SF_Calc.FirstColumn + +REM ----------------------------------------------------------------------------- +Property Get FirstRow(Optional ByVal RangeName As Variant) As Long +''' Returns the First used column in a given range +''' When the argument is a sheet it will always return 1 + FirstRow = _PropertyGet("FirstRow", RangeName) +End Property ' SFDocuments.SF_Calc.FirstRow + +REM ----------------------------------------------------------------------------- Property Get Height(Optional ByVal RangeName As Variant) As Long ''' Returns the height in # of rows of the given range Height = _PropertyGet("Height", RangeName) End Property ' SFDocuments.SF_Calc.Height REM ----------------------------------------------------------------------------- -Property Get LastCell(Optional ByVal SheetName As Variant) As String -''' Returns the last used cell in a given sheet - LastCell = _PropertyGet("LastCell", SheetName) +Property Get LastCell(Optional ByVal RangeName As Variant) As String +''' Returns the last used cell in a given sheet or range + LastCell = _PropertyGet("LastCell", RangeName) End Property ' SFDocuments.SF_Calc.LastCell REM ----------------------------------------------------------------------------- -Property Get LastColumn(Optional ByVal SheetName As Variant) As Long +Property Get LastColumn(Optional ByVal RangeName As Variant) As Long ''' Returns the last used column in a given sheet - LastColumn = _PropertyGet("LastColumn", SheetName) + LastColumn = _PropertyGet("LastColumn", RangeName) End Property ' SFDocuments.SF_Calc.LastColumn REM ----------------------------------------------------------------------------- -Property Get LastRow(Optional ByVal SheetName As Variant) As Long +Property Get LastRow(Optional ByVal RangeName As Variant) As Long ''' Returns the last used column in a given sheet - LastRow = _PropertyGet("LastRow", SheetName) + LastRow = _PropertyGet("LastRow", RangeName) End Property ' SFDocuments.SF_Calc.LastRow REM ----------------------------------------------------------------------------- @@ -249,6 +274,12 @@ Property Get Sheet(Optional ByVal SheetName As Variant) As Variant End Property ' SFDocuments.SF_Calc.Sheet REM ----------------------------------------------------------------------------- +Property Get SheetName(Optional ByVal RangeName As Variant) As String +''' Returns the sheet name part of a range + SheetName = _PropertyGet("SheetName", RangeName) +End Property ' SFDocuments.SF_Calc.SheetName + +REM ----------------------------------------------------------------------------- Property Get Sheets() As Variant ''' Returns an array listing the existing sheet names Sheets = _PropertyGet("Sheets") @@ -301,11 +332,14 @@ Public Function A1Style(Optional ByVal Row1 As Variant _ ''' A range as a string ''' Exceptions: ''' Examples: -''' range = oDoc.A1Style(5, 2, 10, 4, "SheetX") ' "'$SheetX'.E2:J4" +''' range = oDoc.A1Style(5, 2, 10, 4, "SheetX") ' "'$SheetX'.$E$2:$J$4" Dim sA1Style As String ' Return value Dim vSheetName As Variant ' Alias of SheetName - necessary see [Bug 145279] Dim lTemp As Long ' To switch 2 values +Dim lInStr As Long ' Position of special character in sheet name +Dim i As Long + Const cstThisSub = "SFDocuments.Calc.A1Style" Const cstSubArgs = "Row1, Column1, [Row2], [Column2], [SheetName]=""""" @@ -340,8 +374,16 @@ Check: End If Try: + ' Surround the sheet name with single quotes when required by the presence of special characters + If InStr(vSheetName, "'") > 0 Then vSheetName = "'" & Replace(vSheetName, "'", "''") & "'" + For i = 1 To Len(cstSPECIALCHARS) + If InStr(vSheetName, Mid(cstSPECIALCHARS, i, 1)) > 0 Then + vSheetName = "'" & vSheetName & "'" + Exit For + End If + Next i ' Define the new range string - sA1Style = "'$" & vSheetName & "'." _ + sA1Style = "$" & vSheetName & "." _ & "$" & _GetColumnName(Column1) & "$" & CLng(Row1) _ & Iif(Row2 > 0 And Column2 > 0, ":$" & _GetColumnName(Column2) & "$" & CLng(Row2), "") @@ -1318,74 +1360,6 @@ Catch: End Function ' SFDocuments.SF_Calc.GetProperty REM ----------------------------------------------------------------------------- -Public Function GetRangeAddress(Optional ByVal Range As Variant _ - , Optional ByVal AddressItem As Variant _ - ) As Variant -''' Extracts from a range expressed in A1-style one or more of its coordinates -''' Args: -''' Range: the range as a string from which to extract the coordinates -''' AddressItem: either "firstrow", "firstcol" (topleft cell), "lastrow", "lastcol" (bottom right cell), "sheet" or omitted -''' Returns: -''' Either: -''' - one of the cell coordinates as a Long (coordinates start with 1) -''' when Range is a single cell, row2 and row1 return the same value -''' - the sheet name as a string -''' - all of them as a zero-based array in the order firstrow, firstcol, lastrow, lastcol, sheet -''' Exceptions: -''' Examples: -''' row2 = oDoc.GetRangeAddress("'$SheetX'.E2:J4", "row2") ' 4 -''' arr = oDoc.GetRangeAddress("'$SheetX'.E2:J4") ' (2, 5, 4, 10, "SheetX") - -Dim vRangeAddress As Variant ' Return value -Dim oAddress As Object ' Alias of input range as _Address -Dim oCellRange As Object ' com.sun.star.table.XCellRange -Const cstThisSub = "SFDocuments.Calc.GetRangeAddress" -Const cstSubArgs = "Range, [AddressItem=""firstrow""|""firstcol""|""lastrow""|""lastcol""|""sheet""]" - - If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch - vRangeAddress = 0 - -Check: - If IsMissing(AddressItem) Or IsEmpty(AddressItem) Then AddressItem = "" - - 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 - If Not ScriptForge.SF_Utils._Validate(AddressItem, "AddressItem", V_STRING _ - , Array("firstrow", "firstcol", "firstcolumn", "lastrow", "lastcol", "lastcolumn", "sheet", "sheetname", "")) Then GoTo Finally - End If - -Try: - Set oAddress = _ParseAddress(Range) - Set oCellRange = oAddress.XCellRange - - With oCellRange - Select Case UCase(AddressItem) - Case "FIRSTROW" : vRangeAddress = CLng(.RangeAddress.StartRow + 1) - Case "FIRSTCOL", "FIRSTCOLUMN" : vRangeAddress = CLng(.RangeAddress.StartColumn + 1) - Case "LASTROW" : vRangeAddress = CLng(.RangeAddress.EndRow + 1) - Case "LASTCOL", "LASTCOLUMN" : vRangeAddress = CLng(.RangeAddress.EndColumn + 1) - Case "SHEET", "SHEETNAME" : vRangeAddress = .Spreadsheet.Name - Case "" - vRangeAddress = Array( _ - CLng(.RangeAddress.StartRow + 1) _ - , CLng(.RangeAddress.StartColumn + 1) _ - , CLng(.RangeAddress.EndRow + 1) _ - , CLng(.RangeAddress.EndColumn + 1) _ - , .Spreadsheet.Name _ - ) - End Select - End With - -Finally: - GetRangeAddress = vRangeAddress - ScriptForge.SF_Utils._ExitFunction(cstThisSub) - Exit Function -Catch: - GoTo Finally -End Function ' SF_Documents.SF_Calc.GetRangeAddress - -REM ----------------------------------------------------------------------------- Public Function GetValue(Optional ByVal Range As Variant) As Variant ''' Get the value(s) stored in the given range of cells ''' Args: @@ -1688,7 +1662,6 @@ Public Function Methods() As Variant , "ExportAsPDF" _ , "GetColumnName" _ , "GetFormula" _ - , "GetRangeAddress" _ , "GetValue" _ , "ImportFromCSVFile" _ , "ImportFromDatabase" _ @@ -1961,6 +1934,9 @@ Public Function Properties() As Variant , "Description" _ , "DocumentProperties" _ , "DocumentType" _ + , "FirstCell" _ + , "FirstColumn" _ + , "FirstRow" _ , "Height" _ , "IsBase" _ , "IsCalc" _ @@ -1976,6 +1952,7 @@ Public Function Properties() As Variant , "Readonly" _ , "Region" _ , "Sheet" _ + , "SheetName" _ , "Sheets" _ , "Subject" _ , "Title" _ @@ -2193,7 +2170,7 @@ Public Function SetFormula(Optional ByVal TargetRange As Variant _ ''' oDoc.SetFormula("A1:F1", Array("=A2", "=B2", "=C2+10")) ' Horizontal vector, partially empty ''' oDoc.SetFormula("A1:D2", "=E1") ' D2 contains the formula "=H2" -Dim sSet As String ' Return value +Dim sSet As String ' Return value.XSpreadsheet.Name) Dim oAddress As Object ' Alias of TargetRange Dim vDataArray As Variant ' DataArray compatible with .DataArray UNO property Const cstThisSub = "SFDocuments.Calc.SetFormula" @@ -2301,7 +2278,7 @@ Public Function SetValue(Optional ByVal TargetRange As Variant _ ''' Vectors are always expanded vertically, except if the range has a height of exactly 1 row ''' Args: ''' TargetRange : the range as a string that should receive a new value -''' Value: a scalar, a vector or an array with the new values for each cell of the range. +''' Value: a scalar, a vector or an array with the new values for each cell o.XSpreadsheet.Name)f the range. ''' The new values should be strings, numeric values or dates. Other types empty the corresponding cell ''' Returns: ''' A string representing the updated range @@ -3553,19 +3530,28 @@ Const cstSubArgs = "" If Not ScriptForge.SF_Utils._Validate(pvArg, "Range", V_STRING) Then GoTo Finally _PropertyGet = _ParseAddress(pvArg).Height End If - Case UCase("LastCell"), UCase("LastColumn"), UCase("LastRow") + Case UCase("FirstCell"), UCase("FirstRow"), UCase("FirstColumn") _ + , UCase("LastCell"), UCase("LastColumn"), UCase("LastRow") _ + , UCase("SheetName") If IsMissing(pvArg) Or IsEmpty(pvArg) Then ' Avoid errors when instance is watched in Basic IDE - _PropertyGet = -1 + If InStr(UCase(psProperty), "CELL") > 0 Then _PropertyGet = "" Else _PropertyGet = -1 Else - If Not _ValidateSheet(pvArg, "SheetName", , True) Then GoTo Finally - vLastCell = _LastCell(_Component.getSheets.getByName(pvArg)) - If psProperty = "LastRow" Then - _PropertyGet = vLastCell(1) - ElseIf psProperty = "LastColumn" Then - _PropertyGet = vLastCell(0) - Else - _PropertyGet = GetColumnName(vLastCell(0)) & CStr(vLastCell(1)) - End If + If Not ScriptForge.SF_Utils._Validate(pvArg, "Range", V_STRING) Then GoTo Finally + Set oAddress = _ParseAddress(pvArg) + With oAddress.XCellRange + Select Case UCase(psProperty) + Case UCase("FirstCell") + _PropertyGet = A1Style(.RangeAddress.StartRow + 1, .RangeAddress.StartColumn + 1, , , oAddress.XSpreadsheet.Name) + Case UCase("FirstRow") : _PropertyGet = CLng(.RangeAddress.StartRow + 1) + Case UCase("FirstColumn") : _PropertyGet = CLng(.RangeAddress.StartColumn + 1) + Case UCase("FirstRow") : _PropertyGet = CLng(.RangeAddress.StartRow + 1) + Case UCase("LastCell") + _PropertyGet = A1Style(.RangeAddress.EndRow + 1, .RangeAddress.EndColumn + 1, , , oAddress.XSpreadsheet.Name) + Case UCase("LastColumn") : _PropertyGet = CLng(.RangeAddress.EndColumn + 1) + Case UCase("LastRow") : _PropertyGet = CLng(.RangeAddress.EndRow + 1) + Case UCase("SheetName") : _PropertyGet = oAddress.XSpreadsheet.Name + End Select + End With End If Case UCase("Range") If IsMissing(pvArg) Or IsEmpty(pvArg) Then |