wizards/source/scriptforge/python/scriptforge.py | 5 +
wizards/source/scriptforge/python/scriptforge.pyi | 13 +++
wizards/source/sfdocuments/SF_Calc.xba | 77 +++++++++++++++++++++-
wizards/source/sfdocuments/SF_Register.xba | 1
wizards/source/sfdocuments/script.xlb | 20 ++---
5 files changed, 102 insertions(+), 14 deletions(-)
New commits:
commit 1032ba121411b2c75f7a4be130cd1409e829bf88
Author: Jean-Pierre Ledure <j...@ledure.be>
AuthorDate: Sun Jan 19 15:23:21 2025 +0100
Commit: Jean-Pierre Ledure <j...@ledure.be>
CommitDate: Sun Jan 19 16:43:49 2025 +0100
ScriptForge (SF_Calc new Intersect() method
The
calc.Intersect(range1, range2) method
computes the common area as a string
which is a sub-range of both input ranges.
It returns an empty string when the
sub-range is empty.
Potential use case: customizing a popup
or context menu depending on the presence
of the selected cell in a given area.
In addition, the A1Style() method returns
the sheetname in the output string only
when it is an explicit argument.
Changes are applicable both for Basic and
Python scripts.
They require an update of the user documentation.
Change-Id: I920fc8bee6dace9ceed263a0570b4c0197bc8cb8
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/180479
Reviewed-by: Jean-Pierre Ledure <j...@ledure.be>
Tested-by: Jenkins
diff --git a/wizards/source/scriptforge/python/scriptforge.py
b/wizards/source/scriptforge/python/scriptforge.py
index d558cc0bbd2c..3b8df9083ef6 100644
--- a/wizards/source/scriptforge/python/scriptforge.py
+++ b/wizards/source/scriptforge/python/scriptforge.py
@@ -2512,7 +2512,7 @@ class SFDocuments:
return self.ExecMethod(self.vbGet + self.flgUno, 'XSpreadsheet',
sheetname)
# Usual methods
- def A1Style(self, row1, column1, row2 = 0, column2 = 0, sheetname =
'~'):
+ 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 = ''):
@@ -2608,6 +2608,9 @@ class SFDocuments:
def InsertSheet(self, sheetname, beforesheet = 32768):
return self.ExecMethod(self.vbMethod, 'InsertSheet', sheetname,
beforesheet)
+ def Intersect(self, range1, range2):
+ return self.ExecMethod(self.vbMethod, 'Intersect', range1, range2)
+
def MoveRange(self, source, destination):
return self.ExecMethod(self.vbMethod, 'MoveRange', source,
destination)
diff --git a/wizards/source/scriptforge/python/scriptforge.pyi
b/wizards/source/scriptforge/python/scriptforge.pyi
index d35ee2304fb0..0c0bf92868b3 100644
--- a/wizards/source/scriptforge/python/scriptforge.pyi
+++ b/wizards/source/scriptforge/python/scriptforge.pyi
@@ -5655,6 +5655,19 @@ class SFDocuments:
"""
...
+ def Intersect(self, range1:RANGE, range2:RANGE) -> RANGE:
+ """
+ Computes the intersection between two ranges, i.e. the range
common to both input ranges.
+ Args
+ ``range1``: the first range, as a string.
+
+ ``range2``: the second range, as a string.
+ Returns
+ The string representing the intersection between the
two input ranges,
+ or a zero-length string when the intersection is empty.
+ """
+ ...
+
def MoveRange(self, source: RANGE, destination: RANGE) -> RANGE:
"""
Moves a specified source range to a destination cell.
diff --git a/wizards/source/sfdocuments/SF_Calc.xba
b/wizards/source/sfdocuments/SF_Calc.xba
index 8b3b7101ed40..7bd5e4e8d251 100644
--- a/wizards/source/sfdocuments/SF_Calc.xba
+++ b/wizards/source/sfdocuments/SF_Calc.xba
@@ -372,7 +372,7 @@ Const cstSubArgs = "Row1, Column1, [Row2], [Column2],
[SheetName]="&qu
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 =
"~"
+ If IsMissing(SheetName) Or IsEmpty(SheetName) Then SheetName =
""
vSheetName = SheetName
If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
@@ -388,6 +388,11 @@ Check:
If Row2 > MAXROWS Then Row2 = MAXROWS
If Column1 > MAXCOLS Then Column1 = MAXCOLS
If Column2 > MAXCOLS Then Column2 = MAXCOLS
+ If Row1 <= 0 Or Column1 <= 0 Then GoTo Catch
+ If Row2 = Row1 And Column2 = Column1 Then ' Single
cell
+ Row2 = 0
+ Column2 = 0
+ End If
If Row2 > 0 And Row2 < Row1 Then
lTemp = Row2 : Row2 = Row1 : Row1 = lTemp
@@ -398,9 +403,9 @@ Check:
Try:
' Surround the sheet name with single quotes when required by the
presence of special characters
- vSheetName = _QuoteSheetName(vSheetName)
+ If Len(vSheetName) > 0 Then vSheetName = "$" &
_QuoteSheetName(vSheetName) & "."
' Define the new range string
- sA1Style = "$" & vSheetName & "." _
+ sA1Style = vSheetName _
& "$" &
_GetColumnName(Column1) & "$" & CLng(Row1) _
& Iif(Row2 > 0 And Column2 >
0, ":$" & _GetColumnName(Column2) & "$" &
CLng(Row2), "")
@@ -2087,6 +2092,71 @@ Catch:
GoTo Finally
End Function ' SFDocuments.SF_Calc.InsertSheet
+REM
-----------------------------------------------------------------------------
+Public Function Intersect(Optional ByVal Range1 As Variant _
+ , Optional ByVal Range2
As Variant _
+ ) As String
+''' Returns the cell range as a string that is common to
the input ranges
+''' Args:
+''' Range1: a first range as a string
+''' Range2: a second range as a string
+''' Returns:
+''' The intersection, as a string, representing the
range common to both input ranges,
+''' or a zero-length string when the intersection
is empty.
+''' Example:
+''' calc.Intersect("J7:M11",
"$Sheet2.$L$10:$N$17")
+''' ' $Sheet2.$L$10:$M$11 when Sheet2
is the current sheet, otherwise the empty string
+
+Dim sIntersect As String ' Return value
+Dim oRangeAddress1 As Object ' SF_UI._Address type
+Dim oRangeAddress2 As Object ' SF_UI._Address type
+Dim oRange1 As Object '
com.sun.star.table.CellRangeAddress
+Dim oRange2 As Object '
com.sun.star.table.CellRangeAddress
+Dim lStartRow As Long ' Intersection starting
row
+Dim lEndRow As Long ' Intersection
ending row
+Dim lStartColumn As Long ' Intersection starting
column
+Dim lEndColumn As Long ' Intersection ending
column
+
+Const cstThisSub = "SFDocuments.Calc.Intersect"
+Const cstSubArgs = "Range1, Range2"
+
+ If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+ sIntersect = ""
+
+Check:
+ If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+ If Not _IsStillAlive(True) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(Range1,
"Range1", V_STRING) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(Range2,
"Range2", V_STRING) Then GoTo Finally
+ End If
+
+ Set oRangeAddress1 = _ParseAddress(Range1)
+ Set oRange1 = oRangeAddress1.XCellRange.RangeAddress
+ Set oRangeAddress2 = _ParseAddress(Range2)
+ Set oRange2 = oRangeAddress2.XCellRange.RangeAddress
+
+ If oRangeAddress1.SheetName <> oRangeAddress2.SheetName Then GoTo
Finally
+
+Try:
+ ' Find the top-left and bottom-right coordinates of the
intersection
+ lStartRow = Iif(oRange1.StartRow > oRange2.StartRow,
oRange1.StartRow, oRange2.StartRow) + 1
+ lStartColumn = Iif(oRange1.StartColumn > oRange2.StartColumn,
oRange1.StartColumn, oRange2.StartColumn) + 1
+ lEndRow = Iif(oRange1.EndRow < oRange2.EndRow, oRange1.EndRow,
oRange2.EndRow) + 1
+ lEndColumn = Iif(oRange1.EndColumn < oRange2.EndColumn,
oRange1.EndColumn, oRange2.EndColumn) + 1
+
+ ' Check that the 2 ranges overlap each other
+ If lStartRow <= lEndRow And lStartColumn <= lEndColumn Then
+ sIntersect = A1Style(lStartRow, lStartColumn, lEndRow,
lEndColumn, oRangeAddress1.SheetName)
+ End If
+
+Finally:
+ Intersect = sIntersect
+ 'ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+ Exit Function
+Catch:
+ GoTo Finally
+End Function ' SFDocuments.SF_Calc.Intersect
+
REM
-----------------------------------------------------------------------------
Public Function Methods() As Variant
''' Return the list of public methods of the Calc service
as an array
@@ -2114,6 +2184,7 @@ Public Function Methods() As Variant
, "ImportFromCSVFile" _
, "ImportFromDatabase" _
, "InsertSheet" _
+ , "Intersect" _
, "MoveRange" _
, "MoveSheet" _
, "Offset" _
diff --git a/wizards/source/sfdocuments/SF_Register.xba
b/wizards/source/sfdocuments/SF_Register.xba
index c2a58bc61a8d..028a483ea050 100644
--- a/wizards/source/sfdocuments/SF_Register.xba
+++ b/wizards/source/sfdocuments/SF_Register.xba
@@ -422,6 +422,7 @@ Try:
Case Else ' Only superclass
Set oDocument = New SF_Document
Set oSuperDocument = oDocument
+ Set oSuperDocument.[_SubClass] = oDocument
End Select
With oDocument ' Initialize attributes of
subclass
Set .[Me] = oDocument
diff --git a/wizards/source/sfdocuments/script.xlb
b/wizards/source/sfdocuments/script.xlb
index 3d2264bd3f6d..8188fd53bb1f 100644
--- a/wizards/source/sfdocuments/script.xlb
+++ b/wizards/source/sfdocuments/script.xlb
@@ -1,15 +1,15 @@
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE library:library PUBLIC "-//OpenOffice.org//DTD OfficeDocument
1.0//EN" "library.dtd">
<library:library xmlns:library="http://openoffice.org/2000/library";
library:name="SFDocuments" library:readonly="false"
library:passwordprotected="false">
- <library:element library:name="__License"/>
- <library:element library:name="SF_Form"/>
- <library:element library:name="SF_DocumentListener"/>
- <library:element library:name="SF_Document"/>
- <library:element library:name="SF_Calc"/>
- <library:element library:name="SF_Writer"/>
- <library:element library:name="SF_Register"/>
- <library:element library:name="SF_Base"/>
- <library:element library:name="SF_FormControl"/>
- <library:element library:name="SF_FormDocument"/>
<library:element library:name="SF_Chart"/>
+ <library:element library:name="SF_FormDocument"/>
+ <library:element library:name="SF_FormControl"/>
+ <library:element library:name="SF_Base"/>
+ <library:element library:name="SF_Register"/>
+ <library:element library:name="SF_Writer"/>
+ <library:element library:name="SF_Calc"/>
+ <library:element library:name="SF_Document"/>
+ <library:element library:name="SF_DocumentListener"/>
+ <library:element library:name="SF_Form"/>
+ <library:element library:name="__License"/>
</library:library>
\ No newline at end of file
