wizards/source/scriptforge/python/scriptforge.py | 19
wizards/source/scriptforge/python/scriptforge.pyi | 194 +++++++
wizards/source/sfdocuments/SF_Calc.xba | 567 +++++++++++++++++++++-
3 files changed, 773 insertions(+), 7 deletions(-)
New commits:
commit ec71ff69fed9a2ed247760abcf653dbc07b47002
Author: Jean-Pierre Ledure <j...@ledure.be>
AuthorDate: Tue Feb 18 22:37:33 2025 +0100
Commit: Jean-Pierre Ledure <j...@ledure.be>
CommitDate: Wed Feb 19 10:59:24 2025 +0100
ScriptForge (SF_Calc) formatting methods
So far only the SetCellStyle() method
provided means to modify cell formats.
The actual patch introduces next methods
to apply simple and common formats. For
other options use the XCellRange
UNO interface.
- calc.AlignRange(targetrange,alignment,...)
- calc.BorderRange(targetrange,borders,...)
- calc.ColorizeRange(targetrange,foreground,background,...)
- calc.DecorateFont(targetrange,fontname,fontsize,
decoration,...)
- calc.FormatRange(targetrange,numberformat,locale,...)
Extra arguments ("...") are:
FilterFormula: a Calc formula to select cells
FilterScope; CELL, ROW or COLUMN
to allow a kind of static conditional formats.
All methods are applicable to Basic and Python
user scripts.
This change will require an update of the
user documentation.
Change-Id: Ic824aebd763f2b55364c2c0bea17430f32512742
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/181854
Tested-by: Jenkins
Reviewed-by: Jean-Pierre Ledure <j...@ledure.be>
diff --git a/wizards/source/scriptforge/python/scriptforge.py
b/wizards/source/scriptforge/python/scriptforge.py
index 9cf19e4d85f5..00fddb2209ee 100644
--- a/wizards/source/scriptforge/python/scriptforge.py
+++ b/wizards/source/scriptforge/python/scriptforge.py
@@ -2518,6 +2518,12 @@ class SFDocuments:
def Activate(self, sheetname = ''):
return self.ExecMethod(self.vbMethod, 'Activate', sheetname)
+ def AlignRange(self, targetrange, alignment, filterformula = '',
filterscope = ''):
+ return self.ExecMethod(self.vbMethod, 'AlignRange', targetrange,
alignment, filterformula, filterscope)
+
+ def BorderRange(self, targetrange, borders, filterformula = '',
filterscope = ''):
+ return self.ExecMethod(self.vbMethod, 'BorderRange', targetrange,
borders, filterformula, filterscope)
+
def Charts(self, sheetname, chartname = ''):
return self.ExecMethod(self.vbMethod + self.flgArrayRet, 'Charts',
sheetname, chartname)
@@ -2530,6 +2536,10 @@ class SFDocuments:
def ClearValues(self, range, filterformula = '', filterscope = ''):
return self.ExecMethod(self.vbMethod, 'ClearValues', range,
filterformula, filterscope)
+ def ColorizeRange(self, targetrange, foreground = -1, background = -1,
filterformula = '', filterscope = ''):
+ return self.ExecMethod(self.vbMethod, 'ColorizeRange',
targetrange, foreground, background,
+ filterformula, filterscope)
+
def CompactLeft(self, range, wholecolumn = False, filterformula = ''):
return self.ExecMethod(self.vbMethod, 'CompactLeft', range,
wholecolumn, filterformula)
@@ -2579,9 +2589,18 @@ class SFDocuments:
def DSum(self, range):
return self.ExecMethod(self.vbMethod, 'DSum', range)
+ def DecorateFont(self, targetrange, fontname = '', fontsize = 0,
decoration = '',
+ filterformula = '', filterscope = ''):
+ return self.ExecMethod(self.vbMethod, 'DecorateFont', targetrange,
fontname, fontsize, decoration,
+ filterformula, filterscope)
+
def ExportRangeToFile(self, range, filename, imagetype = 'pdf',
overwrite = False):
return self.ExecMethod(self.vbMethod, 'ExportRangeToFile', range,
filename, imagetype, overwrite)
+ def FormatRange(self, targetrange, numberformat, locale = '',
filterformula = '', filterscope = ''):
+ return self.ExecMethod(self.vbMethod, 'FormatRange', targetrange,
numberformat, locale,
+ filterformula, filterscope)
+
def Forms(self, sheetname, form = ''):
return self.ExecMethod(self.vbMethod + self.flgArrayRet, 'Forms',
sheetname, form)
diff --git a/wizards/source/scriptforge/python/scriptforge.pyi
b/wizards/source/scriptforge/python/scriptforge.pyi
index 2d34c0f32e31..802853c39ebe 100644
--- a/wizards/source/scriptforge/python/scriptforge.pyi
+++ b/wizards/source/scriptforge/python/scriptforge.pyi
@@ -5098,6 +5098,87 @@ class SFDocuments:
"""
...
+ def AlignRange(self,
+ targetrange: RANGE,
+ alignment: str,
+ filterformula: str = ...,
+ filterscope: Literal['CELL', 'ROW', 'COLUMN'] = ...,
+ ) -> RANGE:
+ """
+ Align horizontally or vertically a range of cells.
+ The range is updated and the remainder of the sheet is left
untouched.
+ Either the full range is updated or a selection based on a
``filterformula``.
+ Args
+ ``targetrange``: the cell or the range as a string in
which cells should be re-aligned.
+
+ ``alignment``: a string combining 1 or 2 of next
characters.
+ - L align Left
+ - C align Center
+ - R align Right
+ - B align Bottom
+ - M center vertically (Middle)
+ - T align Top
+
+ ``filterformula``: a ``Calc`` formula that shall be
applied to the given range
+ to determine which cells will be affected. The
specified formula must return ``True``
+ or ``False``. If this argument is not specified, then
all cells in the range are affected.
+
+ Express the formula in terms of (examples assume a
range = ``"A1:J10"``):
+ - the top-left cell of the range when
``filterscope`` = "CELL" e.g. ``"=(A1>100)"``
+ - the topmost row of the range when
``filterscope`` = "ROW" e.g. ``"=(SUM($A1:$J1)<1000)"``
+ - the leftmost column of the range when
``filterscope`` = "COLUMN" e.g. ``"=(A$10=SUM(A$1:A$9))"``
+
+ ``filterscope``: determines how ``filterformula`` is
expanded to the given range.
+ The argument is mandatory if a ``filterformula`` is
specified.
+ Returns
+ A string representing the modified area as a range of
cells.
+ """
+ ...
+
+ def BorderRange(self,
+ targetrange: RANGE,
+ borders: str,
+ filterformula: str = ...,
+ filterscope: Literal['CELL', 'ROW', 'COLUMN'] = ...,
+ ) -> RANGE:
+ """
+ Apply within and around a range of cells a set of line borders.
+ All the borders have the same standard width, style and color.
+
+ Pre-existing border lines in the impacted cells, rows or
columns are first cleared.
+ Other cells in the range are left untouched.
+ To clear the full range use Border = "" without the
``filterformula`` argument.
+
+ Either the full range is updated or a selection based on a
``filterformula``.
+ Args
+ ``targetrange``: the cell or the range as a string on
which borders should be applied.
+
+ ``borders``: a string combining next characters.
+ - B Bottom outer line
+ - L Left outer line
+ - T Top outer line
+ - R Right outer line
+ - H Horizontal inner line
+ - V Verical inner line
+ - U diagonal bottom-Up line
+ - D diagonal top-Down line
+
+ ``filterformula``: a ``Calc`` formula that shall be
applied to the given range
+ to determine which cells will be affected. The
specified formula must return ``True``
+ or ``False``. If this argument is not specified, then
all cells in the range are affected.
+
+ Express the formula in terms of (examples assume a
range = ``"A1:J10"``):
+ - the top-left cell of the range when
``filterscope`` = "CELL" e.g. ``"=(A1>100)"``
+ - the topmost row of the range when
``filterscope`` = "ROW" e.g. ``"=(SUM($A1:$J1)<1000)"``
+ - the leftmost column of the range when
``filterscope`` = "COLUMN" e.g. ``"=(A$10=SUM(A$1:A$9))"``
+
+ ``filterscope``: determines how ``filterformula`` is
expanded to the given range.
+ The argument is mandatory if a ``filterformula`` is
specified.
+ Returns
+ A string representing the modified area as a range of
cells.
+ """
+ ...
+
def Charts(self, sheetname: SHEETNAME, chartname: str = ...) ->
Union[Tuple[str, ...], CHART]:
"""
Returns either the list with the names of all chart objects in
a given sheet
@@ -5189,6 +5270,41 @@ class SFDocuments:
The argument is mandatory if a ``filterformula`` is
specified.
"""
...
+
+ def ColorizeRange(self,
+ targetrange: RANGE,
+ foreground: int = ...,
+ background: int = ...,
+ filterformula: str = ...,
+ filterscope: Literal['CELL', 'ROW', 'COLUMN'] = ...,
+ ) -> RANGE:
+ """
+ Define fore- and background colors of a range of cells.
+ The range is updated and the remainder of the sheet is left
untouched.
+ Either the full range is updated or a selection based on a
``filterformula``.
+ Args
+ ``targetrange``: the cell or the range as a string in
which cells should be re-colorizeed.
+
+ ``foreground``: the foreground color as the output of
the basic.RGB() function.
+
+ ``background``: the background color as the output of
the basic.RGB() function.
+
+ ``filterformula``: a ``Calc`` formula that shall be
applied to the given range
+ to determine which cells will be affected. The
specified formula must return ``True``
+ or ``False``. If this argument is not specified, then
all cells in the range are affected.
+
+ Express the formula in terms of (examples assume a
range = ``"A1:J10"``):
+ - the top-left cell of the range when
``filterscope`` = "CELL" e.g. ``"=(A1>100)"``
+ - the topmost row of the range when
``filterscope`` = "ROW" e.g. ``"=(SUM($A1:$J1)<1000)"``
+ - the leftmost column of the range when
``filterscope`` = "COLUMN" e.g. ``"=(A$10=SUM(A$1:A$9))"``
+
+ ``filterscope``: determines how ``filterformula`` is
expanded to the given range.
+ The argument is mandatory if a ``filterformula`` is
specified.
+ Returns
+ A string representing the modified area as a range of
cells.
+ """
+ ...
+
def CompactLeft(self, range: RANGE, wholecolumn: bool = ...,
filterformula: str = ...) -> RANGE:
"""
Deletes the columns of a specified range that match a filter
expressed as a ``Calc`` formula.
@@ -5473,6 +5589,47 @@ class SFDocuments:
"""
...
+ def DecorateFont(self,
+ targetrange: RANGE,
+ fontname: str = ...,
+ fontsize: int = ...,
+ decoration: str = ...,
+ filterformula: str = ...,
+ filterscope: Literal['CELL', 'ROW', 'COLUMN'] = ...,
+ ) -> RANGE:
+ """
+ Specify simple and easy properties of the font to be used in a
range of cells.
+ The range is updated and the remainder of the sheet is left
untouched.
+ Either the full range is updated or a selection based on a
``filterformula``.
+ Args
+ ``targetrange``: the cell or the range as a string in
which cell fonts should be re-decorated.
+
+ ``fontname``: the name of the font to be used. The
name is not checked. Default = no change.
+
+ ``fontsize``: the size of the font in pixels. Default
= no change.
+
+ ``decoration``: a string combining 1 or more of next
characters (default = no change).
+ - B Bold
+ - I Italic
+ - U Underline
+ - S Strikethrough
+
+ ``filterformula``: a ``Calc`` formula that shall be
applied to the given range
+ to determine which cells will be affected. The
specified formula must return ``True``
+ or ``False``. If this argument is not specified, then
all cells in the range are affected.
+
+ Express the formula in terms of (examples assume a
range = ``"A1:J10"``):
+ - the top-left cell of the range when
``filterscope`` = "CELL" e.g. ``"=(A1>100)"``
+ - the topmost row of the range when
``filterscope`` = "ROW" e.g. ``"=(SUM($A1:$J1)<1000)"``
+ - the leftmost column of the range when
``filterscope`` = "COLUMN" e.g. ``"=(A$10=SUM(A$1:A$9))"``
+
+ ``filterscope``: determines how ``filterformula`` is
expanded to the given range.
+ The argument is mandatory if a ``filterformula`` is
specified.
+ Returns
+ A string representing the modified area as a range of
cells.
+ """
+ ...
+
def ExportRangeToFile(self,
range: RANGE,
filename: FILE,
@@ -5497,6 +5654,41 @@ class SFDocuments:
"""
...
+ def FormatRange(self,
+ targetrange: RANGE,
+ numberformat: str,
+ locale: str = ...,
+ filterformula: str = ...,
+ filterscope: Literal['CELL', 'ROW', 'COLUMN'] = ...,
+ ) -> RANGE:
+ """
+ Apply a given number format to a cell or a range of cells.
+ The range is updated and the remainder of the sheet is left
untouched.
+ Either the full range is updated or a selection based on a
``filterformula``.
+ Args
+ ``targetrange``: the cell or the range as a string
that should receive the format
+
+ ``numberformat``: the format to apply, as a string.
+
+ ``locale``: a la-CO combination to indicate the used
locale.
+ The default locale is the output of the
platform.FormatLocale property.
+
+ ``filterformula``: a ``Calc`` formula that shall be
applied to the given range
+ to determine which cells will be affected. The
specified formula must return ``True``
+ or ``False``. If this argument is not specified, then
all cells in the range are affected.
+
+ Express the formula in terms of (examples assume a
range = ``"A1:J10"``):
+ - the top-left cell of the range when
``filterscope`` = "CELL" e.g. ``"=(A1>100)"``
+ - the topmost row of the range when
``filterscope`` = "ROW" e.g. ``"=(SUM($A1:$J1)<1000)"``
+ - the leftmost column of the range when
``filterscope`` = "COLUMN" e.g. ``"=(A$10=SUM(A$1:A$9))"``
+
+ ``filterscope``: determines how ``filterformula`` is
expanded to the given range.
+ The argument is mandatory if a ``filterformula`` is
specified.
+ Returns
+ A string representing the modified area as a range of
cells.
+ """
+ ...
+
def Forms(self, sheetname: SHEETNAME, form: Union[int, str] = ...) ->
Union[FORM, Tuple[str, ...]]:
"""
Depending on the parameters provided this method will return:
@@ -5927,7 +6119,7 @@ class SFDocuments:
- the leftmost column of the range when
``filterscope`` = "COLUMN" e.g. ``"=(A$10=SUM(A$1:A$9))"``
``filterscope``: determines how ``filterformula`` is
expanded to the given range.
- Tuple The argument is mandatory if a ``filterformula``
is specified.
+ The argument is mandatory if a ``filterformula`` is
specified.
Returns
A string representing the modified area as a range of
cells.
"""
diff --git a/wizards/source/sfdocuments/SF_Calc.xba
b/wizards/source/sfdocuments/SF_Calc.xba
index 2aef72167281..d0011644dc6c 100644
--- a/wizards/source/sfdocuments/SF_Calc.xba
+++ b/wizards/source/sfdocuments/SF_Calc.xba
@@ -30,6 +30,7 @@ Option Explicit
''' - management (copy, insert, move, ...)
of sheets within a Calc document
''' - exchange of data between Basic data
structures and Calc ranges of values
''' - copying and importing massive amounts
of data
+''' - simple and selective formatting
options
'''
''' The current module is closely related to the
"UI" service of the ScriptForge library
'''
@@ -70,15 +71,14 @@ Option Explicit
''' SheetX.*
All cells up to the last active cell
''' myRange
A range name at spreadsheet level
''' ~.yourRange,
SheetX.someRange A range name at sheet level
-'''
myDoc.Range("SheetX.D2:F6")
-'''
A range within the sheet SheetX in file associated with
the myDoc Calc instance
+'''
myDoc.Range("SheetX.D2:F6") A range within the sheet SheetX
in file associated with the myDoc Calc instance
'''
''' Several methods may receive a
"FilterFormula" as argument.
-''' A FilterFormula may be associated with
a FilterScope: "row", "column" or "cell".
+''' A FilterFormula may be associated with
a FilterScope: "ROW", "COLUMN" or "CELL".
''' These arguments determine on which
rows/columns/cells of a range the method should be applied
''' Examples:
'''
oDoc.ClearAll("A1:J10", FilterFormula := "=(A1<=0)",
FilterScope := "CELL") ' Clear all negative values
-'''
oDoc.ClearAll("SheetX.A1:J10",
"=SUM(SheetX.A1:A10)>100", "COLUMN")
' Clear all columns whose sum is greater than 500
+'''
oDoc.ClearAll("SheetX.A1:J10",
"=SUM(SheetX.A1:A10)>100", "COLUMN")
' Clear all columns whose sum is greater than 100
'''
''' FilterFormula: a Calc formula
that returns TRUE or FALSE
'''
the formula is expressed in terms of
@@ -461,6 +461,165 @@ Catch:
GoTo Finally
End Function ' SFDocuments.SF_Calc.Activate
+REM
-----------------------------------------------------------------------------
+Public Function AlignRange(Optional ByVal TargetRange As Variant _
+ , Optional ByVal
Alignment As Variant _
+ , Optional ByVal
FilterFormula As Variant _
+ , Optional ByVal
FilterScope As Variant _
+ ) As String
+''' Align horizontally or vertically a range of cells.
+''' The impacted cells may be determined with a filter
formula and its scope.
+''' Args:
+''' TargetRange : the cell or the range as a string
in which cells should be re-aligned.
+''' Alignment: a string combining 1 or 2 of next
characters:
+''' L align Left
+''' R align Right
+''' C Center gorizontally
+''' B align Bottom
+''' M center vertically (Middle)
+''' T align Top
+''' FilterFormula: a Calc formula to select among
the given TargetRange
+''' When left empty, the alignments are
applied on the full range
+''' FilterScope: "CELL", "ROW"
or "COLUMN"
+''' When FilterFormula is present,
FilterScope is mandatory
+''' Returns:
+''' A string representing the updated range
+''' Examples:
+''' oDoc.AlignRange("SheetX.A1:J30",
"MC", FilterFormula := "IsNumeric(A1), FilterScope :=
"CELL")
+''' ' Align to the middle of the
cells, horizontally and vertically
+
+Dim sAlign As String ' Return value
+Dim oRange As Object ' Alias of TargetRange
+Dim vRanges() As Variant ' Array of subranges resulting
from the application of the filter
+Dim oARange As Object ' A single element of vRanges
+
+Const cstThisSub = "SFDocuments.Calc.AlignRange"
+Const cstSubArgs = "TargetRange, Alignment,
[FilterFormula=""""],
[FilterScope=""""]"
+
+ If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+ sAlign = ""
+
+Check:
+ If IsMissing(FilterFormula) Or IsEmpty(FilterFormula) Then
FilterFormula = ""
+ If IsMissing(FilterScope) Or IsEmpty(FilterScope) Then FilterScope =
""
+ If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+ If Not _IsStillAlive() Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(TargetRange,
"TargetRange", V_STRING) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(Alignment,
"Alignment", V_STRING) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(FilterFormula,
"FilterFormula", V_STRING) Then GoTo Finally
+ If Len(FilterFormula) > 0 Then
+ If Not ScriptForge.SF_Utils._Validate(FilterScope,
"FilterScope", V_STRING, Array("CELL", "ROW",
"COLUMN")) Then GoTo Finally
+ Else
+ If Not ScriptForge.SF_Utils._Validate(FilterScope,
"FilterScope", V_STRING) Then GoTo Finally
+ End If
+ End If
+
+Try:
+ If VarType(TargetRange) = V_STRING Then Set oRange =
_ParseAddress(TargetRange) Else Set oRange = TargetRange
+
+ ' Without filter, the whole range is re-aligned
+ ' Otherwise the filter cuts the range in subranges and formats
them one by one
+ If Len(FilterFormula) = 0 Then vRanges = Array(oRange) Else vRanges() =
_ComputeFilter(oRange, FilterFormula, UCase(FilterScope))
+ For Each oARange In vRanges
+ With oARange.XCellRange
+ If InStr(Alignment, "L") > 0 Then
.ParaAdjust = com.sun.star.style.ParagraphAdjust.LEFT
+ If InStr(Alignment, "C") > 0 Then
.ParaAdjust = com.sun.star.style.ParagraphAdjust.CENTER
+ If InStr(Alignment, "R") > 0 Then
.ParaAdjust = com.sun.star.style.ParagraphAdjust.RIGHT
+ If InStr(Alignment, "B") > 0 Then
.VertJustify = com.sun.star.table.CellVertJustify.BOTTOM
+ If InStr(Alignment, "M") > 0 Then
.VertJustify = com.sun.star.table.CellVertJustify.CENTER
+ If InStr(Alignment, "T") > 0 Then
.VertJustify = com.sun.star.table.CellVertJustify.TOP
+ End With
+ Next oARange
+
+ sAlign = oRange.RangeName
+
+Finally:
+ AlignRange = sAlign
+ ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+ Exit Function
+Catch:
+ GoTo Finally
+End Function ' SFDocuments.SF_Calc.AlignRange
+
+REM
-----------------------------------------------------------------------------
+Public Function BorderRange(Optional ByVal TargetRange As Variant _
+ , Optional ByVal
Borders As Variant _
+ , Optional ByVal
FilterFormula As Variant _
+ , Optional ByVal
FilterScope As Variant _
+ ) As String
+''' Apply within and around a range of cells a set of line
borders.
+''' The impacted cells may be determined with a filter
formula and its scope.
+''' All the borders have the same standard width, style and
color.
+''' Pre-existing border lines in the impacted cells, rows
or columns
+''' are first cleared. Other cells in the range are left
untouched.
+''' To clear the full range use Border = ""
without the FilterFormula argument.
+''' Args:
+''' TargetRange : the cell or the range as a string
on which borders should be applied
+''' Borders: a string combining next characters:
+''' B Bottom outer line
+''' L Left outer line
+''' T Top outer line
+''' R Right outer line
+''' H Horizontal inner line
+''' V Vertical inner line
+''' U diagonal bottom-Up line
+''' D diagonal top-Down line
+''' FilterFormula: a Calc formula to select among
the given TargetRange
+''' When left empty, the given borders are
applied on the full range
+''' FilterScope: "CELL", "ROW"
or "COLUMN"
+''' When FilterFormula is present,
FilterScope is mandatory
+''' Returns:
+''' A string representing the updated range
+''' Examples:
+''' oDoc.BorderRange("SheetX.A1:J30",
"HB", FilterFormula := "IsNumeric(A1), FilterScope :=
"CELL")
+''' ' Border with a bottom line,
including the horizontal inner lines
+
+Dim sBorder As String ' Return value
+Dim oRange As Object ' Alias of TargetRange
+Dim vRanges() As Variant ' Array of subranges resulting
from the application of the filter
+Dim oBRange As Object ' A single element of vRanges
+
+Const cstThisSub = "SFDocuments.Calc.BorderRange"
+Const cstSubArgs = "TargetRange, Borders,
[FilterFormula=""""],
[FilterScope=""""]"
+
+ If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+ sBorder = ""
+
+Check:
+ If IsMissing(FilterFormula) Or IsEmpty(FilterFormula) Then
FilterFormula = ""
+ If IsMissing(FilterScope) Or IsEmpty(FilterScope) Then FilterScope =
""
+ If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+ If Not _IsStillAlive() Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(TargetRange,
"TargetRange", V_STRING) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(Borders,
"Borders", V_STRING) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(FilterFormula,
"FilterFormula", V_STRING) Then GoTo Finally
+ If Len(FilterFormula) > 0 Then
+ If Not ScriptForge.SF_Utils._Validate(FilterScope,
"FilterScope", V_STRING, Array("CELL", "ROW",
"COLUMN")) Then GoTo Finally
+ Else
+ If Not ScriptForge.SF_Utils._Validate(FilterScope,
"FilterScope", V_STRING) Then GoTo Finally
+ End If
+ End If
+
+Try:
+ If VarType(TargetRange) = V_STRING Then Set oRange =
_ParseAddress(TargetRange) Else Set oRange = TargetRange
+
+ ' Without filter, the whole range gets new borders
+ ' Otherwise the filter cuts the range in subranges and formats
them one by one
+ If Len(FilterFormula) = 0 Then vRanges = Array(oRange) Else vRanges() =
_ComputeFilter(oRange, FilterFormula, UCase(FilterScope))
+ For Each oBRange In vRanges
+ _BorderRange(oBRange, Borders)
+ Next oBRange
+
+ sBorder = oRange.RangeName
+
+Finally:
+ BorderRange = sBorder
+ ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+ Exit Function
+Catch:
+ GoTo Finally
+End Function ' SFDocuments.SF_Calc.BorderRange
+
REM
-----------------------------------------------------------------------------
Public Function Charts(Optional ByVal SheetName As Variant _
, Optional ByVal
ChartName As Variant _
@@ -616,6 +775,81 @@ Public Sub ClearValues(Optional ByVal Range As Variant _
End Sub ' SFDocuments.SF_Calc.ClearValues
+REM
-----------------------------------------------------------------------------
+Public Function ColorizeRange(Optional ByVal TargetRange As Variant _
+ , Optional ByVal
Foreground As Variant _
+ , Optional ByVal
Background As Variant _
+ , Optional ByVal
FilterFormula As Variant _
+ , Optional ByVal
FilterScope As Variant _
+ ) As String
+''' Define fore- and background colors of a range of cells.
+''' The impacted cells may be determined with a filter
formula and its scope.
+''' Args:
+''' TargetRange : the cell or the range as a string
in which cells should be re-colorizeed.
+''' Foreground: the foreground color as the output
of the RGB() function
+''' Background: the foreground color as the output
of the RGB() function
+''' FilterFormula: a Calc formula to select among
the given TargetRange
+''' When left empty, the Colorizements are
applied on the full range
+''' FilterScope: "CELL", "ROW"
or "COLUMN"
+''' When FilterFormula is present,
FilterScope is mandatory
+''' Returns:
+''' A string representing the updated range
+''' Examples:
+''' oDoc.ColorizeRange("SheetX.A1:J30",
Background := RGB(255, 0, 0), FilterFormula := "IsNumeric(A1), FilterScope
:= "CELL")
+''' ' Paint the cell(s) in red.
+
+Dim sColorize As String ' Return value
+Dim oRange As Object ' Alias of TargetRange
+Dim vRanges() As Variant ' Array of subranges resulting
from the application of the filter
+Dim oCRange As Object ' A single element of vRanges
+
+Const cstThisSub = "SFDocuments.Calc.ColorizeRange"
+Const cstSubArgs = "TargetRange, [Foreground=-1], [Background=-1]
[FilterFormula=""""],
[FilterScope=""""]"
+
+ If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+ sColorize = ""
+
+Check:
+ If IsMissing(Foreground) Or IsEmpty(Foreground) Then Foreground = -1
+ If IsMissing(Background) Or IsEmpty(Background) Then Background = -1
+ If IsMissing(FilterFormula) Or IsEmpty(FilterFormula) Then
FilterFormula = ""
+ If IsMissing(FilterScope) Or IsEmpty(FilterScope) Then FilterScope =
""
+ If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+ If Not _IsStillAlive() Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(TargetRange,
"TargetRange", V_STRING) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(Foreground,
"Foreground", ScriptForge.V_NUMERIC) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(Background,
"Background", ScriptForge.V_NUMERIC) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(FilterFormula,
"FilterFormula", V_STRING) Then GoTo Finally
+ If Len(FilterFormula) > 0 Then
+ If Not ScriptForge.SF_Utils._Validate(FilterScope,
"FilterScope", V_STRING, Array("CELL", "ROW",
"COLUMN")) Then GoTo Finally
+ Else
+ If Not ScriptForge.SF_Utils._Validate(FilterScope,
"FilterScope", V_STRING) Then GoTo Finally
+ End If
+ End If
+
+Try:
+ If VarType(TargetRange) = V_STRING Then Set oRange =
_ParseAddress(TargetRange) Else Set oRange = TargetRange
+
+ ' Without filter, the whole range is re-Colorizeed
+ ' Otherwise the filter cuts the range in subranges and formats
them one by one
+ If Len(FilterFormula) = 0 Then vRanges = Array(oRange) Else vRanges() =
_ComputeFilter(oRange, FilterFormula, UCase(FilterScope))
+ For Each oCRange In vRanges
+ With oCRange.XCellRange
+ If Foreground > 0 Then .CharColor = CLng(Foreground)
+ If Background > 0 Then .CellBackColor =
CLng(Background)
+ End With
+ Next oCRange
+
+ sColorize = oRange.RangeName
+
+Finally:
+ ColorizeRange = sColorize
+ ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+ Exit Function
+Catch:
+ GoTo Finally
+End Function ' SFDocuments.SF_Calc.ColorizeRange
+
REM
-----------------------------------------------------------------------------
Public Function CompactLeft(Optional ByVal Range As Variant _
, Optional
ByVal WholeColumn As Variant _
@@ -1509,6 +1743,98 @@ Finally:
Exit Function
End Function ' SFDocuments.SF_Calc.DSum
+REM
-----------------------------------------------------------------------------
+Public Function DecorateFont(Optional ByVal TargetRange As Variant _
+ , Optional ByVal
FontName As Variant _
+ , Optional ByVal
FontSize As Variant _
+ , Optional ByVal
Decoration As Variant _
+ , Optional ByVal
FilterFormula As Variant _
+ , Optional ByVal
FilterScope As Variant _
+ ) As String
+''' Specify simple and easy properties of the font to be
used in a range of cells.
+''' The impacted cells may be determined with a filter
formula and its scope.
+''' To apply more complex font decorations, apply the usual
and numerous UNO properties
+''' available in the XCell or XCellRange interfaces.
+''' Args:
+''' TargetRange : the cell or the range as a string
in which cell fonts should be re-decorated.
+''' FontName: the name of the font to be used. The
name is not checked. Default = no change.
+''' FontSize: the size of the font in pixels.
Default = no change.
+''' Decoration: a string combining 1 or more of
next characters (default = no change):
+''' B Bold
+''' U Underline
+''' I Italic
+''' S Strikethrough
+''' FilterFormula: a Calc formula to select among
the given TargetRange
+''' When left empty, the font properties
are applied on the full range
+''' FilterScope: "CELL", "ROW"
or "COLUMN"
+''' When FilterFormula is present,
FilterScope is mandatory
+''' Returns:
+''' A string representing the updated range
+''' Examples:
+''' oDoc.DecorateRange("SheetX.A1:J30",
FontSize := 15, Decoration := "BU", FilterFormula :=
"IsNumeric(A1), FilterScope := "CELL")
+''' ' Modify the font size and apply
bold and underline properties to the numeric cells
+
+Dim sDecorate As String ' Return value
+Dim oRange As Object ' Alias of TargetRange
+Dim vRanges() As Variant ' Array of subranges resulting
from the application of the filter
+Dim oDRange As Object ' A single element of vRanges
+
+Const cstThisSub = "SFDocuments.Calc.DecorateFont"
+Const cstSubArgs = "TargetRange, [FontName=""""],
[FontSize=0], [Decoration=""""]," _
+ & "
[FilterFormula=""""],
[FilterScope=""""]"
+
+ If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+ sDecorate = ""
+
+Check:
+ If IsMissing(FontName) Or IsEmpty(FontName) Then FontName = ""
+ If IsMissing(FontSize) Or IsEmpty(FontSize) Then FontSize = 0
+ If IsMissing(Decoration) Or IsEmpty(Decoration) Then Decoration =
""
+ If IsMissing(FilterFormula) Or IsEmpty(FilterFormula) Then
FilterFormula = ""
+ If IsMissing(FilterScope) Or IsEmpty(FilterScope) Then FilterScope =
""
+ If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+ If Not _IsStillAlive() Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(TargetRange,
"TargetRange", V_STRING) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(FontName,
"FontName", V_STRING) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(FontSize,
"FontSize", ScriptForge.V_NUMERIC) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(Decoration,
"Decoration", V_STRING) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(FilterFormula,
"FilterFormula", V_STRING) Then GoTo Finally
+ If Len(FilterFormula) > 0 Then
+ If Not ScriptForge.SF_Utils._Validate(FilterScope,
"FilterScope", V_STRING, Array("CELL", "ROW",
"COLUMN")) Then GoTo Finally
+ Else
+ If Not ScriptForge.SF_Utils._Validate(FilterScope,
"FilterScope", V_STRING) Then GoTo Finally
+ End If
+ End If
+
+Try:
+ If VarType(TargetRange) = V_STRING Then Set oRange =
_ParseAddress(TargetRange) Else Set oRange = TargetRange
+
+ ' Without filter, the whole range is re-decorated
+ ' Otherwise the filter cuts the range in subranges and formats
them one by one
+ If Len(FilterFormula) = 0 Then vRanges = Array(oRange) Else vRanges() =
_ComputeFilter(oRange, FilterFormula, UCase(FilterScope))
+ For Each ODRange In vRanges
+ With ODRange.XCellRange
+ If Len(FontName) > 0 Then .CharFontName = FontName
+ If FontSize > 0 Then .CharHeight = FontSize
+ If Len(Decoration) > 0 Then
+ If InStr(Decoration, "B") > 0 Then
.CharWeight = com.sun.star.awt.FontWeight.BOLD
+ If InStr(Decoration, "U") > 0 Then
.CharUnderline = com.sun.star.awt.FontUnderline.SINGLE
+ If InStr(Decoration, "I") > 0 Then
.CharPosture = com.sun.star.awt.FontSlant.ITALIC
+ If InStr(Decoration, "S") > 0 Then
.CharStrikeout = com.sun.star.awt.FontStrikeout.SINGLE
+ End If
+ End With
+ Next ODRange
+
+ sDecorate = oRange.RangeName
+
+Finally:
+ DecorateFont = sDecorate
+ ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+ Exit Function
+Catch:
+ GoTo Finally
+End Function ' SFDocuments.SF_Calc.DecorateFont
+
REM
-----------------------------------------------------------------------------
Public Function ExportRangeToFile(Optional ByVal Range As Variant _
,
Optional ByVal FileName As Variant _
@@ -1616,6 +1942,103 @@ CatchError:
GoTo Finally
End Function ' SFDocuments.SF_Chart.ExportRangeToFile
+REM
-----------------------------------------------------------------------------
+Public Function FormatRange(Optional ByVal TargetRange As Variant _
+ , Optional ByVal
NumberFormat As Variant _
+ , Optional ByVal Locale
As Variant _
+ , Optional
FilterFormula As Variant _
+ , Optional FilterScope
As Variant _
+ ) As String
+''' Apply a given number format to a cell or a range of
cells.
+''' The cells range may be filtered with a filter formula
and its scope.
+''' If the given format does not exist, it is created and
suppressed immediately.
+''' Args:
+''' TargetRange : the cell or the range as a string
that should receive the format
+''' NumberFormat: the format to apply, as a string
+''' Locale: a la-CO combination to indicate the
used locale.
+''' The default locale is the output of the
platform.FormatLocale property
+''' FilterFormula: a Calc formula to select among
the given TargetRange
+''' When left empty, the format is applied
on all the cells
+''' FilterScope: "CELL", "ROW"
or "COLUMN"
+''' When FilterFormula is present,
FilterScope is mandatory
+''' Returns:
+''' A string representing the updated range
+''' Examples:
+''' oDoc.FormatRange("SheetX.A1:J30",
"0,00E+00", FilterFormula := "IsNumeric(A1), FilterScope :=
"CELL")
+''' ' Format only the cells
containing a numeric value
+
+Dim sFormat As String ' Return value
+Dim lClear As Long ' A combination of
com.sun.star.sheet.CellFlags
+Dim oRange As Object ' Alias of TargetRange
+Dim vRanges() As Variant ' Array of subranges resulting
from the application of the filter
+Dim oFRange As Object ' A single element of vRanges
+Dim oNumberFormats As Object ' com.sun.star.util.XNumberFormats
+Dim oLocale As Object ' com.sun.star.lang.Locale
+Dim bNew As Boolean ' When True, the
requested format doen't exist yet
+Dim lFormatID As Long ' The format key in the list of
number formats
+
+Const cstThisSub = "SFDocuments.Calc.FormatRange"
+Const cstSubArgs = "TargetRange, NumberFormat, [Locale=formatlocale],
[FilterFormula=""""],
[FilterScope=""""]"
+
+ If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch
+ sFormat = ""
+
+Check:
+ If IsMissing(Locale) Or IsEmpty(Locale) Then Locale = ""
+ If IsMissing(FilterFormula) Or IsEmpty(FilterFormula) Then
FilterFormula = ""
+ If IsMissing(FilterScope) Or IsEmpty(FilterScope) Then FilterScope =
""
+ If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then
+ If Not _IsStillAlive() Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(TargetRange,
"TargetRange", V_STRING) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(NumberFormat,
"NumberFormat", V_STRING) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(Locale,
"Locale", V_STRING) Then GoTo Finally
+ If Not ScriptForge.SF_Utils._Validate(FilterFormula,
"FilterFormula", V_STRING) Then GoTo Finally
+ If Len(FilterFormula) > 0 Then
+ If Not ScriptForge.SF_Utils._Validate(FilterScope,
"FilterScope", V_STRING, Array("CELL", "ROW",
"COLUMN")) Then GoTo Finally
+ Else
+ If Not ScriptForge.SF_Utils._Validate(FilterScope,
"FilterScope", V_STRING) Then GoTo Finally
+ End If
+ End If
+
+Try:
+ If VarType(TargetRange) = V_STRING Then Set oRange =
_ParseAddress(TargetRange) Else Set oRange = TargetRange
+
+
+ ' Set the locale
+ If Len(Locale) = 0 Then Locale = ScriptForge.SF_Platform.FormatLocale
+ Set oLocale = CreateUnoStruct("com.sun.star.lang.Locale")
+ oLocale.Language = Split(Locale, "-")(0) '
Language is most often 2 chars long, but not always
+ oLocale.Country = Right(Locale, 2)
+
+ ' If the targeted format doesn't exist in the document,
create it, otherwise determine its index
+ Set oNumberFormats = _Component.NumberFormats
+
+ With oNumberFormats
+ lFormatID = .queryKey(NumberFormat, oLocale, False)
+ bNew = ( lFormatID < 0 )
+ If bNew Then lFormatID = .addNew(NumberFormat, oLocale)
+
+ ' Without filter, the whole range is formatted
+ ' Otherwise the filter cuts the range in subranges and
formats them one by one
+ If Len(FilterFormula) = 0 Then vRanges = Array(oRange) Else
vRanges() = _ComputeFilter(oRange, FilterFormula, UCase(FilterScope))
+ For Each oFRange In vRanges
+ oFRange.XCellRange.NumberFormat = lFormatID
+ Next oFRange
+
+ ' Delete the new format
+ If bNew Then .removeByKey(lFormatID)
+ End With
+
+ sFormat = oRange.RangeName
+
+Finally:
+ FormatRange = sFormat
+ ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+ Exit Function
+Catch:
+ GoTo Finally
+End Function ' SFDocuments.SF_Calc.FormatRange
+
REM
-----------------------------------------------------------------------------
Public Function Forms(Optional ByVal SheetName As Variant _
, Optional ByVal Form
As Variant _
@@ -2151,7 +2574,7 @@ Try:
Finally:
Intersect = sIntersect
- 'ScriptForge.SF_Utils._ExitFunction(cstThisSub)
+ ScriptForge.SF_Utils._ExitFunction(cstThisSub)
Exit Function
Catch:
GoTo Finally
@@ -2163,10 +2586,13 @@ Public Function Methods() As Variant
Methods = Array( _
"A1Style" _
+ , "AlignRange" _
+ , "BorderRange" _
, "Charts" _
, "ClearAll" _
, "ClearFormats" _
, "ClearValues" _
+ , "ColorizeRange" _
, "CopySheet" _
, "CopySheetFromFile" _
, "CopyToCell" _
@@ -2174,10 +2600,12 @@ Public Function Methods() As Variant
, "CreateChart" _
, "DAvg" _
, "DCount" _
+ , "DecorateFont" _
, "DMax" _
, "DMin" _
, "DSum" _
, "ExportRangeToFile" _
+ , "FormatRange" _
, "GetColumnName" _
, "GetFormula" _
, "GetValue" _
@@ -2951,7 +3379,7 @@ Public Function SetCellStyle(Optional ByVal TargetRange
As Variant _
''' oDoc.SetCellStype("A1:J20",
"Wrong", "=(A1<0)", "CELL")
Dim sSet As String ' Return value
-Dim oAddress As _Address ' Alias of
TargetRange
+Dim oAddress As _Address ' Alias of TargetRange
Dim oStyleFamilies As Object '
com.sun.star.container.XNameAccess
Dim vStyles As Variant ' Array of existing cell
styles
Dim vRanges() As Variant ' Array of filtered ranges
@@ -3937,6 +4365,133 @@ End Function ' SFDocuments.SF_Calc.XStyle
REM =========================================================== PRIVATE
FUNCTIONS
+REM
-----------------------------------------------------------------------------
+Private Sub _BorderRange(ByRef poRange As Object _
+ , ByVal pvBorders As
Variant _
+ )
+''' Set the border lines of a range. Pre-existing borders
may be cleared or not before
+''' the application of the new definition.
+''' Complex borders are not supported. Only thin solid
lines.
+''' Args:
+''' poRange: the targeted range as an _Address
object
+''' pvBorders:
+''' when a string, a combination of the
letters T, B, L, R, V, H
+''' when an object: a
com.sun.star.table.TableBorder2 UNO object
+
+Dim oTableBorder As Object '
com.sun.star.table.TableBorder2
+Dim oBorderLine As Object '
com.sun.star.table.BorderLine2
+Dim oCellRange As Object '
com.sun.star.table.XCellRange
+Dim iDefaultStyle As Integer '
com.sun.star.table.BorderLineStyle.SOLID
+Const cstLineWidth = 26
+
+Try:
+ Set oCellRange = poRange.XCellRange
+
+ With oCellRange
+
+ ' Clear any existing border in the range
+ Set oBorderLine = New com.sun.star.table.BorderLine2
+ Set .BottomBorder2 = oBorderLine
+ Set .LeftBorder2 = oBorderLine
+ Set .TopBorder2 = oBorderLine
+ Set .RightBorder2 = oBorderLine
+ Set .DiagonalBLTR2 = oBorderLine
+ Set .DiagonalTLBR2 = oBorderLine
+ Set .TableBorder2 = New com.sun.star.table.TableBorder2
+
+ ' Set the new borders
+ ' Usually a string, but sometimes an object
+ If VarType(pvBorders) = V_STRING Then
+ Set oTableBorder = New com.sun.star.table.TableBorder2
+ iDefaultStyle = com.sun.star.table.BorderLineStyle.SOLID
+
+ ' Bottom borders
+ If InStr(pvBorders, "B") > 0 Then
+ With oTableBorder
+ .BottomLine.LineStyle = iDefaultStyle
+ .BottomLine.LineWidth = cstLineWidth
+ .IsBottomLineValid = True
+ End With
+ End If
+
+ ' Left borders
+ If InStr(pvBorders, "L") > 0 Then
+ With oTableBorder
+ .LeftLine.LineStyle = iDefaultStyle
+ .LeftLine.LineWidth = cstLineWidth
+ .IsLeftLineValid = True
+ End With
+ End If
+
+ ' Top borders
+ If InStr(pvBorders, "T") > 0 Then
+ With oTableBorder
+ .TopLine.LineStyle = iDefaultStyle
+ .TopLine.LineWidth = cstLineWidth
+ .IsTopLineValid = True
+ End With
+ End If
+
+ ' Right borders
+ If InStr(pvBorders, "R") > 0 Then
+ With oTableBorder
+ .RightLine.LineStyle = iDefaultStyle
+ .RightLine.LineWidth = cstLineWidth
+ .IsRightLineValid = True
+ End With
+ End If
+
+ ' Horizontal inner borders
+ If InStr(pvBorders, "H") > 0 Then
+ With oTableBorder
+ .HorizontalLine.LineStyle =
iDefaultStyle
+ .HorizontalLine.LineWidth = cstLineWidth
+ .IsHorizontalLineValid = True
+ End With
+ End If
+
+ ' Vertical inner borders
+ If InStr(pvBorders, "V") > 0 Then
+ With oTableBorder
+ .VerticalLine.LineStyle = iDefaultStyle
+ .VerticalLine.LineWidth = cstLineWidth
+ .IsVerticalLineValid = True
+ End With
+ End If
+
+ ' Bottom-up diagonal
+ If InStr(pvBorders, "U") > 0 Then
+ Set oBorderLine = New
com.sun.star.table.BorderLine2
+ With oBorderLine
+ .LineStyle = iDefaultStyle
+ .LineWidth = cstLineWidth
+ End With
+ Set .DiagonalBLTR2 = oBorderLine
+ End If
+
+ ' Top-down diagonal
+ If InStr(pvBorders, "D") > 0 Then
+ Set oBorderLine = New
com.sun.star.table.BorderLine2
+ With oBorderLine
+ .LineStyle = iDefaultStyle
+ .LineWidth = cstLineWidth
+ End With
+ Set .DiagonalTLBR2 = oBorderLine
+ End If
+
+ Else
+ Set oTableBorder = pvBorders
+ End If
+
+ ' Apply the new border definitions
+ Set .TableBorder2 = oTableBorder
+
+ End With
+
+Finally:
+ Exit Sub
+End Sub
+
REM
-----------------------------------------------------------------------------
Private Sub _ClearRange(ByVal psTarget As String _
, Optional ByVal Range
As Variant _
