summaryrefslogtreecommitdiff
path: root/wizards
diff options
context:
space:
mode:
authorJean-Pierre Ledure <jp@ledure.be>2021-11-09 11:00:04 +0100
committerJean-Pierre Ledure <jp@ledure.be>2021-11-09 13:09:24 +0100
commit754247fe3fb1c8baa8f37f7ea1eb74aa3f0bcd57 (patch)
tree0a9a36e6653cd904536290e4ef8b2e7e6bc7cd20 /wizards
parenta14b783bbe8eda32b4b79530d85ffc48b6ed0305 (diff)
ScriptForge - (SF_Calc) First/Last * Row/Column/Cell accept sheets and ranges
The methods FirstCell FirstColumn FirstRow LastCell LastColumn LastRow SheetName accept uniformly either a sheet name or a range name. They return a single cell as an absolute address (First/LastCell), a String (SheetName) or a Long. The range name is to be understood as the extended notation for ranges. FirstCell/Column/Row accept sheet names. However they will then always return either the "A1" cell or the value 1. The LastCell/Column/Row return the real last xxx for ranges, and the last USED xxx when the argument is a sheet. The A1Style() method surrounds from now on sheet names with single quotes only when necessary. The GetRangeAddress() method has been dropped as redundant with the actual methods. (Was new in 7.3 => no effect on upward compatibiity) Change-Id: Iff1ce437ee27dfb8481361f648b3f7a33ae2c4b1 Reviewed-on: https://gerrit.libreoffice.org/c/core/+/124904 Tested-by: Jean-Pierre Ledure <jp@ledure.be> Tested-by: Jenkins Reviewed-by: Jean-Pierre Ledure <jp@ledure.be>
Diffstat (limited to 'wizards')
-rw-r--r--wizards/source/scriptforge/SF_PythonHelper.xba1
-rw-r--r--wizards/source/scriptforge/python/scriptforge.py27
-rw-r--r--wizards/source/sfdocuments/SF_Calc.xba168
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 &quot;Charts&quot; : vReturn = vBasicObject.Charts(vArgs(0), vArgs(1))
Case &quot;Forms&quot; : vReturn = vBasicObject.Forms(vArgs(0), vArgs(1))
- Case &quot;GetRangeAddress&quot; : vReturn = vBasicObject.GetRangeAddress(vArgs(0), vArgs(1))
Case &quot;GetFormula&quot; : vReturn = vBasicObject.GetFormula(vArgs(0))
Case &quot;GetValue&quot; : vReturn = vBasicObject.GetValue(vArgs(0))
Case &quot;SetArray&quot; : 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 = &quot;SFDocuments.CalcReference&quot;
Private Const ISCALCFORM = 2 &apos; Form is stored in a Calc document
+Private Const cstSPECIALCHARS = &quot; `~!@#$%^&amp;()-_=+{}|;:,&lt;.&gt;&quot;&quot;&quot;
+ &apos; 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 &apos; SFDocuments.SF_Calc.CurrentSelection (let)
REM -----------------------------------------------------------------------------
+Property Get FirstCell(Optional ByVal RangeName As Variant) As String
+&apos;&apos;&apos; Returns the First used cell in a given range or sheet
+&apos;&apos;&apos; When the argument is a sheet it will always return the &quot;sheet.$A$1&quot; cell
+ FirstCell = _PropertyGet(&quot;FirstCell&quot;, RangeName)
+End Property &apos; SFDocuments.SF_Calc.FirstCell
+
+REM -----------------------------------------------------------------------------
+Property Get FirstColumn(Optional ByVal RangeName As Variant) As Long
+&apos;&apos;&apos; Returns the leftmost column in a given sheet or range
+&apos;&apos;&apos; When the argument is a sheet it will always return 1
+ FirstColumn = _PropertyGet(&quot;FirstColumn&quot;, RangeName)
+End Property &apos; SFDocuments.SF_Calc.FirstColumn
+
+REM -----------------------------------------------------------------------------
+Property Get FirstRow(Optional ByVal RangeName As Variant) As Long
+&apos;&apos;&apos; Returns the First used column in a given range
+&apos;&apos;&apos; When the argument is a sheet it will always return 1
+ FirstRow = _PropertyGet(&quot;FirstRow&quot;, RangeName)
+End Property &apos; SFDocuments.SF_Calc.FirstRow
+
+REM -----------------------------------------------------------------------------
Property Get Height(Optional ByVal RangeName As Variant) As Long
&apos;&apos;&apos; Returns the height in # of rows of the given range
Height = _PropertyGet(&quot;Height&quot;, RangeName)
End Property &apos; SFDocuments.SF_Calc.Height
REM -----------------------------------------------------------------------------
-Property Get LastCell(Optional ByVal SheetName As Variant) As String
-&apos;&apos;&apos; Returns the last used cell in a given sheet
- LastCell = _PropertyGet(&quot;LastCell&quot;, SheetName)
+Property Get LastCell(Optional ByVal RangeName As Variant) As String
+&apos;&apos;&apos; Returns the last used cell in a given sheet or range
+ LastCell = _PropertyGet(&quot;LastCell&quot;, RangeName)
End Property &apos; 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
&apos;&apos;&apos; Returns the last used column in a given sheet
- LastColumn = _PropertyGet(&quot;LastColumn&quot;, SheetName)
+ LastColumn = _PropertyGet(&quot;LastColumn&quot;, RangeName)
End Property &apos; 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
&apos;&apos;&apos; Returns the last used column in a given sheet
- LastRow = _PropertyGet(&quot;LastRow&quot;, SheetName)
+ LastRow = _PropertyGet(&quot;LastRow&quot;, RangeName)
End Property &apos; SFDocuments.SF_Calc.LastRow
REM -----------------------------------------------------------------------------
@@ -249,6 +274,12 @@ Property Get Sheet(Optional ByVal SheetName As Variant) As Variant
End Property &apos; SFDocuments.SF_Calc.Sheet
REM -----------------------------------------------------------------------------
+Property Get SheetName(Optional ByVal RangeName As Variant) As String
+&apos;&apos;&apos; Returns the sheet name part of a range
+ SheetName = _PropertyGet(&quot;SheetName&quot;, RangeName)
+End Property &apos; SFDocuments.SF_Calc.SheetName
+
+REM -----------------------------------------------------------------------------
Property Get Sheets() As Variant
&apos;&apos;&apos; Returns an array listing the existing sheet names
Sheets = _PropertyGet(&quot;Sheets&quot;)
@@ -301,11 +332,14 @@ Public Function A1Style(Optional ByVal Row1 As Variant _
&apos;&apos;&apos; A range as a string
&apos;&apos;&apos; Exceptions:
&apos;&apos;&apos; Examples:
-&apos;&apos;&apos; range = oDoc.A1Style(5, 2, 10, 4, &quot;SheetX&quot;) &apos; &quot;&apos;$SheetX&apos;.E2:J4&quot;
+&apos;&apos;&apos; range = oDoc.A1Style(5, 2, 10, 4, &quot;SheetX&quot;) &apos; &quot;&apos;$SheetX&apos;.$E$2:$J$4&quot;
Dim sA1Style As String &apos; Return value
Dim vSheetName As Variant &apos; Alias of SheetName - necessary see [Bug 145279]
Dim lTemp As Long &apos; To switch 2 values
+Dim lInStr As Long &apos; Position of special character in sheet name
+Dim i As Long
+
Const cstThisSub = &quot;SFDocuments.Calc.A1Style&quot;
Const cstSubArgs = &quot;Row1, Column1, [Row2], [Column2], [SheetName]=&quot;&quot;&quot;&quot;&quot;
@@ -340,8 +374,16 @@ Check:
End If
Try:
+ &apos; Surround the sheet name with single quotes when required by the presence of special characters
+ If InStr(vSheetName, &quot;&apos;&quot;) &gt; 0 Then vSheetName = &quot;&apos;&quot; &amp; Replace(vSheetName, &quot;&apos;&quot;, &quot;&apos;&apos;&quot;) &amp; &quot;&apos;&quot;
+ For i = 1 To Len(cstSPECIALCHARS)
+ If InStr(vSheetName, Mid(cstSPECIALCHARS, i, 1)) &gt; 0 Then
+ vSheetName = &quot;&apos;&quot; &amp; vSheetName &amp; &quot;&apos;&quot;
+ Exit For
+ End If
+ Next i
&apos; Define the new range string
- sA1Style = &quot;&apos;$&quot; &amp; vSheetName &amp; &quot;&apos;.&quot; _
+ sA1Style = &quot;$&quot; &amp; vSheetName &amp; &quot;.&quot; _
&amp; &quot;$&quot; &amp; _GetColumnName(Column1) &amp; &quot;$&quot; &amp; CLng(Row1) _
&amp; Iif(Row2 &gt; 0 And Column2 &gt; 0, &quot;:$&quot; &amp; _GetColumnName(Column2) &amp; &quot;$&quot; &amp; CLng(Row2), &quot;&quot;)
@@ -1318,74 +1360,6 @@ Catch:
End Function &apos; SFDocuments.SF_Calc.GetProperty
REM -----------------------------------------------------------------------------
-Public Function GetRangeAddress(Optional ByVal Range As Variant _
- , Optional ByVal AddressItem As Variant _
- ) As Variant
-&apos;&apos;&apos; Extracts from a range expressed in A1-style one or more of its coordinates
-&apos;&apos;&apos; Args:
-&apos;&apos;&apos; Range: the range as a string from which to extract the coordinates
-&apos;&apos;&apos; AddressItem: either &quot;firstrow&quot;, &quot;firstcol&quot; (topleft cell), &quot;lastrow&quot;, &quot;lastcol&quot; (bottom right cell), &quot;sheet&quot; or omitted
-&apos;&apos;&apos; Returns:
-&apos;&apos;&apos; Either:
-&apos;&apos;&apos; - one of the cell coordinates as a Long (coordinates start with 1)
-&apos;&apos;&apos; when Range is a single cell, row2 and row1 return the same value
-&apos;&apos;&apos; - the sheet name as a string
-&apos;&apos;&apos; - all of them as a zero-based array in the order firstrow, firstcol, lastrow, lastcol, sheet
-&apos;&apos;&apos; Exceptions:
-&apos;&apos;&apos; Examples:
-&apos;&apos;&apos; row2 = oDoc.GetRangeAddress(&quot;&apos;$SheetX&apos;.E2:J4&quot;, &quot;row2&quot;) &apos; 4
-&apos;&apos;&apos; arr = oDoc.GetRangeAddress(&quot;&apos;$SheetX&apos;.E2:J4&quot;) &apos; (2, 5, 4, 10, &quot;SheetX&quot;)
-
-Dim vRangeAddress As Variant &apos; Return value
-Dim oAddress As Object &apos; Alias of input range as _Address
-Dim oCellRange As Object &apos; com.sun.star.table.XCellRange
-Const cstThisSub = &quot;SFDocuments.Calc.GetRangeAddress&quot;
-Const cstSubArgs = &quot;Range, [AddressItem=&quot;&quot;firstrow&quot;&quot;|&quot;&quot;firstcol&quot;&quot;|&quot;&quot;lastrow&quot;&quot;|&quot;&quot;lastcol&quot;&quot;|&quot;&quot;sheet&quot;&quot;]&quot;
-
- If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
- vRangeAddress = 0
-
-Check:
- If IsMissing(AddressItem) Or IsEmpty(AddressItem) Then AddressItem = &quot;&quot;
-
- If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
- If Not _IsStillAlive() Then GoTo Finally
- If Not ScriptForge.SF_Utils._Validate(Range, &quot;Range&quot;, V_STRING) Then GoTo Finally
- If Not ScriptForge.SF_Utils._Validate(AddressItem, &quot;AddressItem&quot;, V_STRING _
- , Array(&quot;firstrow&quot;, &quot;firstcol&quot;, &quot;firstcolumn&quot;, &quot;lastrow&quot;, &quot;lastcol&quot;, &quot;lastcolumn&quot;, &quot;sheet&quot;, &quot;sheetname&quot;, &quot;&quot;)) Then GoTo Finally
- End If
-
-Try:
- Set oAddress = _ParseAddress(Range)
- Set oCellRange = oAddress.XCellRange
-
- With oCellRange
- Select Case UCase(AddressItem)
- Case &quot;FIRSTROW&quot; : vRangeAddress = CLng(.RangeAddress.StartRow + 1)
- Case &quot;FIRSTCOL&quot;, &quot;FIRSTCOLUMN&quot; : vRangeAddress = CLng(.RangeAddress.StartColumn + 1)
- Case &quot;LASTROW&quot; : vRangeAddress = CLng(.RangeAddress.EndRow + 1)
- Case &quot;LASTCOL&quot;, &quot;LASTCOLUMN&quot; : vRangeAddress = CLng(.RangeAddress.EndColumn + 1)
- Case &quot;SHEET&quot;, &quot;SHEETNAME&quot; : vRangeAddress = .Spreadsheet.Name
- Case &quot;&quot;
- 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 &apos; SF_Documents.SF_Calc.GetRangeAddress
-
-REM -----------------------------------------------------------------------------
Public Function GetValue(Optional ByVal Range As Variant) As Variant
&apos;&apos;&apos; Get the value(s) stored in the given range of cells
&apos;&apos;&apos; Args:
@@ -1688,7 +1662,6 @@ Public Function Methods() As Variant
, &quot;ExportAsPDF&quot; _
, &quot;GetColumnName&quot; _
, &quot;GetFormula&quot; _
- , &quot;GetRangeAddress&quot; _
, &quot;GetValue&quot; _
, &quot;ImportFromCSVFile&quot; _
, &quot;ImportFromDatabase&quot; _
@@ -1961,6 +1934,9 @@ Public Function Properties() As Variant
, &quot;Description&quot; _
, &quot;DocumentProperties&quot; _
, &quot;DocumentType&quot; _
+ , &quot;FirstCell&quot; _
+ , &quot;FirstColumn&quot; _
+ , &quot;FirstRow&quot; _
, &quot;Height&quot; _
, &quot;IsBase&quot; _
, &quot;IsCalc&quot; _
@@ -1976,6 +1952,7 @@ Public Function Properties() As Variant
, &quot;Readonly&quot; _
, &quot;Region&quot; _
, &quot;Sheet&quot; _
+ , &quot;SheetName&quot; _
, &quot;Sheets&quot; _
, &quot;Subject&quot; _
, &quot;Title&quot; _
@@ -2193,7 +2170,7 @@ Public Function SetFormula(Optional ByVal TargetRange As Variant _
&apos;&apos;&apos; oDoc.SetFormula(&quot;A1:F1&quot;, Array(&quot;=A2&quot;, &quot;=B2&quot;, &quot;=C2+10&quot;)) &apos; Horizontal vector, partially empty
&apos;&apos;&apos; oDoc.SetFormula(&quot;A1:D2&quot;, &quot;=E1&quot;) &apos; D2 contains the formula &quot;=H2&quot;
-Dim sSet As String &apos; Return value
+Dim sSet As String &apos; Return value.XSpreadsheet.Name)
Dim oAddress As Object &apos; Alias of TargetRange
Dim vDataArray As Variant &apos; DataArray compatible with .DataArray UNO property
Const cstThisSub = &quot;SFDocuments.Calc.SetFormula&quot;
@@ -2301,7 +2278,7 @@ Public Function SetValue(Optional ByVal TargetRange As Variant _
&apos;&apos;&apos; Vectors are always expanded vertically, except if the range has a height of exactly 1 row
&apos;&apos;&apos; Args:
&apos;&apos;&apos; TargetRange : the range as a string that should receive a new value
-&apos;&apos;&apos; Value: a scalar, a vector or an array with the new values for each cell of the range.
+&apos;&apos;&apos; Value: a scalar, a vector or an array with the new values for each cell o.XSpreadsheet.Name)f the range.
&apos;&apos;&apos; The new values should be strings, numeric values or dates. Other types empty the corresponding cell
&apos;&apos;&apos; Returns:
&apos;&apos;&apos; A string representing the updated range
@@ -3553,19 +3530,28 @@ Const cstSubArgs = &quot;&quot;
If Not ScriptForge.SF_Utils._Validate(pvArg, &quot;Range&quot;, V_STRING) Then GoTo Finally
_PropertyGet = _ParseAddress(pvArg).Height
End If
- Case UCase(&quot;LastCell&quot;), UCase(&quot;LastColumn&quot;), UCase(&quot;LastRow&quot;)
+ Case UCase(&quot;FirstCell&quot;), UCase(&quot;FirstRow&quot;), UCase(&quot;FirstColumn&quot;) _
+ , UCase(&quot;LastCell&quot;), UCase(&quot;LastColumn&quot;), UCase(&quot;LastRow&quot;) _
+ , UCase(&quot;SheetName&quot;)
If IsMissing(pvArg) Or IsEmpty(pvArg) Then &apos; Avoid errors when instance is watched in Basic IDE
- _PropertyGet = -1
+ If InStr(UCase(psProperty), &quot;CELL&quot;) &gt; 0 Then _PropertyGet = &quot;&quot; Else _PropertyGet = -1
Else
- If Not _ValidateSheet(pvArg, &quot;SheetName&quot;, , True) Then GoTo Finally
- vLastCell = _LastCell(_Component.getSheets.getByName(pvArg))
- If psProperty = &quot;LastRow&quot; Then
- _PropertyGet = vLastCell(1)
- ElseIf psProperty = &quot;LastColumn&quot; Then
- _PropertyGet = vLastCell(0)
- Else
- _PropertyGet = GetColumnName(vLastCell(0)) &amp; CStr(vLastCell(1))
- End If
+ If Not ScriptForge.SF_Utils._Validate(pvArg, &quot;Range&quot;, V_STRING) Then GoTo Finally
+ Set oAddress = _ParseAddress(pvArg)
+ With oAddress.XCellRange
+ Select Case UCase(psProperty)
+ Case UCase(&quot;FirstCell&quot;)
+ _PropertyGet = A1Style(.RangeAddress.StartRow + 1, .RangeAddress.StartColumn + 1, , , oAddress.XSpreadsheet.Name)
+ Case UCase(&quot;FirstRow&quot;) : _PropertyGet = CLng(.RangeAddress.StartRow + 1)
+ Case UCase(&quot;FirstColumn&quot;) : _PropertyGet = CLng(.RangeAddress.StartColumn + 1)
+ Case UCase(&quot;FirstRow&quot;) : _PropertyGet = CLng(.RangeAddress.StartRow + 1)
+ Case UCase(&quot;LastCell&quot;)
+ _PropertyGet = A1Style(.RangeAddress.EndRow + 1, .RangeAddress.EndColumn + 1, , , oAddress.XSpreadsheet.Name)
+ Case UCase(&quot;LastColumn&quot;) : _PropertyGet = CLng(.RangeAddress.EndColumn + 1)
+ Case UCase(&quot;LastRow&quot;) : _PropertyGet = CLng(.RangeAddress.EndRow + 1)
+ Case UCase(&quot;SheetName&quot;) : _PropertyGet = oAddress.XSpreadsheet.Name
+ End Select
+ End With
End If
Case UCase(&quot;Range&quot;)
If IsMissing(pvArg) Or IsEmpty(pvArg) Then