diff options
| -rw-r--r-- | wizards/source/scriptforge/python/scriptforge.py | 3 | ||||
| -rw-r--r-- | wizards/source/sfdocuments/SF_Calc.xba | 81 |
2 files changed, 83 insertions, 1 deletions
diff --git a/wizards/source/scriptforge/python/scriptforge.py b/wizards/source/scriptforge/python/scriptforge.py index 7dd9c62a6da7..e15c63e1e4a8 100644 --- a/wizards/source/scriptforge/python/scriptforge.py +++ b/wizards/source/scriptforge/python/scriptforge.py @@ -1904,6 +1904,9 @@ class SFDocuments: return self.ExecMethod(self.vbGet + self.flgUno, 'XSpreadsheet', sheetname) # Usual methods + def A1Style(self, row1, column1, row2 = 0, column2 = 0, sheetname = '~'): + return self.ExecMethod(self.vbMethod, 'A1Style', row1, column1, row2, column2, sheetname) + def Activate(self, sheetname = ''): return self.ExecMethod(self.vbMethod, 'Activate', sheetname) diff --git a/wizards/source/sfdocuments/SF_Calc.xba b/wizards/source/sfdocuments/SF_Calc.xba index 390e4b274165..2e651651dc1b 100644 --- a/wizards/source/sfdocuments/SF_Calc.xba +++ b/wizards/source/sfdocuments/SF_Calc.xba @@ -265,6 +265,77 @@ End Property ' SFDocuments.SF_Calc.XSpreadsheet REM ===================================================================== METHODS REM ----------------------------------------------------------------------------- +Public Function A1Style(Optional ByVal Row1 As Variant _ + , Optional ByVal Column1 As Variant _ + , Optional ByVal Row2 As Variant _ + , Optional ByVal Column2 As Variant _ + , Optional ByVal SheetName As Variant _ + ) As String +''' Returns a range expressed in A1-style as defined by its coordinates +''' If only one pair of coordinates is given, the range will embrace only a single cell +''' Args: +''' Row1 : the row number of the first coordinate +''' Column1 : the column number of the first coordinates +''' Row2 : the row number of the second coordinate +''' Column2 : the column number of the second coordinates +''' SheetName: Default = the current sheet. If present, the sheet must exist. +''' Returns: +''' A range as a string +''' Exceptions: +''' Examples: +''' range = oDoc.A1Style(5, 2, 10, 4, "SheetX") ' "'$SheetX'.E2:J4" + +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 +Const cstThisSub = "SFDocuments.Calc.A1Style" +Const cstSubArgs = "Row1, Column1, [Row2], [Column2], [SheetName]=""""" + + If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch + sA1Style = "" + +Check: + If IsMissing(Row2) Or IsEmpty(Row2) Then Row2 = 0 + If IsMissing(Column2) Or IsEmpty(Column2) Then Column2 = 0 + If IsMissing(SheetName) Or IsEmpty(SheetName) Then SheetName = "~" + vSheetName = SheetName + + If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then + If Not _IsStillAlive() Then GoTo Finally + If Not ScriptForge.SF_Utils._Validate(Row1, "Row1", ScriptForge.V_NUMERIC) Then GoTo Finally + If Not ScriptForge.SF_Utils._Validate(Column1, "Column1", ScriptForge.V_NUMERIC) Then GoTo Finally + If Not ScriptForge.SF_Utils._Validate(Row2, "Row2", ScriptForge.V_NUMERIC) Then GoTo Finally + If Not ScriptForge.SF_Utils._Validate(Column2, "Column2", ScriptForge.V_NUMERIC) Then GoTo Finally + If Not _ValidateSheet(vSheetName, "SheetName", , True, True, , , True) Then GoTo Finally + End If + + If Row1 > MAXROWS Then Row1 = MAXROWS + If Row2 > MAXROWS Then Row2 = MAXROWS + If Column1 > MAXCOLS Then Column1 = MAXCOLS + If Column2 > MAXCOLS Then Column2 = MAXCOLS + + If Row2 > 0 And Row2 < Row1 Then + lTemp = Row2 : Row2 = Row1 : Row1 = lTemp + End If + If Column2 > 0 And Column2 < Column1 Then + lTemp = Column2 : Column2 = Column1 : Column1 = lTemp + End If + +Try: + ' Define the new range string + sA1Style = "'$" & vSheetName & "'." _ + & "$" & _GetColumnName(Column1) & "$" & CLng(Row1) _ + & Iif(Row2 > 0 And Column2 > 0, ":$" & _GetColumnName(Column2) & "$" & CLng(Row2), "") + +Finally: + A1Style = sA1Style + ScriptForge.SF_Utils._ExitFunction(cstThisSub) + Exit Function +Catch: + GoTo Finally +End Function ' SF_Documents.SF_Calc.A1Style + +REM ----------------------------------------------------------------------------- Public Function Activate(Optional ByVal SheetName As Variant) As Boolean ''' Make the current document or the given sheet active ''' Args: @@ -667,7 +738,7 @@ Try: If Not FSO.FileExists(FileName) Then GoTo CatchNotExists sFileName = FSO._ConvertToUrl(FileName) - ' Insert a blank new sheet and import sheet from file va link setting and deletion + ' Insert a blank new sheet and import sheet from file via link setting and deletion If Not InsertSheet(Newname, BeforeSheet) Then GoTo Finally Set oSheet = _Component.getSheets.getByName(NewName) With oSheet @@ -3166,6 +3237,7 @@ Private Function _ValidateSheet(Optional ByRef pvSheetName As Variant _ , Optional ByVal pvOptional as Variant _ , Optional ByVal pvNumeric As Variant _ , Optional ByVal pvReference As Variant _ + , Optional ByVal pvResetSheet As Variant _ ) As Boolean ''' Sheet designation validation function similar to the SF_Utils._ValidateXXX functions ''' Args: @@ -3177,12 +3249,14 @@ Private Function _ValidateSheet(Optional ByRef pvSheetName As Variant _ ''' pvNumeric: if True, the sheet position is accepted (default = False) ''' pvReference: if True, a sheet reference is acceptable (default = False) ''' pvNumeric and pvReference must not both be = True +''' pvResetSheet: if True, return in pvSheetName the correct (case-sensitive) sheet name (default = False) ''' Returns ''' True if valid. SheetName is reset to current value if = "~" ''' Exceptions ''' DUPLICATESHEETERROR A sheet with the given name exists already Dim vSheets As Variant ' List of sheets +Dim lSheet As Long ' Index in list of sheets Dim vTypes As Variant ' Array of accepted variable types Dim bValid As Boolean ' Return value @@ -3192,6 +3266,7 @@ Check: If IsMissing(pvOptional) Or IsEmpty(pvOptional) Then pvOptional = False If IsMissing(pvNumeric) Or IsEmpty(pvNumeric) Then pvNumeric = False If IsMissing(pvReference) Or IsEmpty(pvReference) Then pvReference = False + If IsMissing(pvResetSheet) Or IsEmpty(pvResetSheet) Then pvResetSheet = False ' Define the acceptable variable types If pvNumeric Then @@ -3215,6 +3290,10 @@ Try: If ScriptForge.SF_Array.Contains(vSheets, pvSheetName) Then GoTo CatchDuplicate Else If Not ScriptForge.SF_Utils._Validate(pvSheetName, psArgName, V_STRING, vSheets) Then GoTo Finally + If pvResetSheet Then + lSheet = ScriptForge.SF_Array.IndexOf(vSheets, pvSheetName, CaseSensitive := False) + pvSheetName = vSheets(lSheet) + End If End If End If End If |