Macros/ScriptForge/FormatPortionsCalcRangeExample
Appearance
< Macros | ScriptForge
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
How to format portions of a Calc range selectively
Authored by Jean-Pierre Ledure.
The example applies several formatting options on a Calc range, depending on the even/odd status of cell values. The Calc range is initialized with random values between 1 and 10.

How to run it in BASIC
- Create a new document
- Open the Basic IDE
- Select the first available blank module
- Copy and paste the code
- Run Main()
How to run it in Python
- Create a new document
- Run APSO
- Create a new module, call it 'Module1'
- Copy and paste the Python code below
- Save and run the Main() method
Code
REM SCRIPTFORGE WIKI EXAMPLE
REM How to format cells selectively in a Calc sheet ?
REM Minimal required version: LibreOffice 25.8
REM Used services
REM SFDocuments.Calc
Option Explicit
Public calc As Object ' The Calc document
Public datarange As String ' The range where formatting applies
'*******************************************************************
'*** Modify the targeted range
'*******************************************************************
Const targetrange = "B2:K30"
Const sleeptime = 1000 ' milliseconds
'*******************************************************************
'* Run the demo below
'*******************************************************************
Sub Main(Optional event As Object)
GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
SetupCalcSheet()
FormatDataRange()
calc.Dispose()
End Sub
'*******************************************************************
'* Specific demo code
'*******************************************************************
Sub FormatDataRange()
' Apply several formattings to the targeted range
' All impacted ranges and filter formulas are computed
Dim formula As String ' The calc.Printf() method
Dim toprow As String ' The top row is computed with the calc.Offset() method
calc.ClearFormats(datarange) ' Clear any actual format
calc.AlignRange(datarange, "Middle,Center") ' Align all cells
Wait sleeptime
' Define a number format (2 decimals) to all even numbers
formula = calc.Printf("=(MOD(%C1%R1;2)=0)", datarange) ' =(MOD(B2;2)=0)
calc.FormatRange(datarange, "0,00", "fr-BE", FilterFormula := formula, FilterScope := "CELL")
Wait sleeptime
' Border all cells by column only when top cell is even
formula = calc.Printf("=(MOD(%C1$%R1;2)=0)", datarange) ' =(MOD(B$2;2)=0)
calc.BorderRange(datarange, "Top,Bottom,Left,Right,Horizontal,Vertical", _
FilterFormula := formula, FilterScope := "COLUMN")
Wait sleeptime
' Put the header row in bold
toprow = calc.Offset(datarange, Height := 1) ' $Sheet1.*B*2:$K$2
calc.DecorateFont(toprow, Decoration := "Bold", FontSize := 15)
Wait sleeptime
' Surround the header row
calc.BorderRange(toprow, "Top,vBottom,vLeft,vRight")
End Sub
'*******************************************************************
'* Setup a sheet with random data
'*******************************************************************
Sub SetupCalcSheet()
' Build from scratch a new calc document and load data in the first sheet
Dim ui As Object : Set ui = CreateScriptService("UI")
Dim sheets As Variant ' Array of sheet names
Dim sheet As String ' Name of the first sheet
Dim data As Variant ' Random data, each value between 1 and 10
Dim i As Long, j As Long
Set calc = ui.CreateDocument("Calc")
sheets = calc.Sheets()
sheet = sheets(0) ' We assume there is at least 1 sheet
data = Array()
datarange = sheet & "." & targetrange
ReDim data(1 To calc.Height(datarange), 1 To calc.Width(datarange))
For i = 1 To UBound(data, 1)
For j = 1 To UBound(data, 2)
data(i, j) = CInt(Rnd() * 9) + 1
Next j
Next i
calc.SetArray(datarange, data)
calc.Activate(sheet) ' Make the targeted sheet visible
End Sub
# coding: utf-8
from __future__ import unicode_literals
# SCRIPTFORGE WIKI EXAMPLE
# How to format cells selectively in a Calc sheet ?
# Minimal required version: LibreOffice 25.8
# Used services
# SFDocuments.Calc
from scriptforge import CreateScriptService
import random
# *******************************************************************
# *** Modify the targeted range
# *******************************************************************
targetrange = 'B2:K30'
# *******************************************************************
# * Run the demo below
# *******************************************************************
def Main(event = None):
calc, datarange = SetupCalcSheet()
FormatDataRange(calc, datarange)
calc.Dispose()
# *******************************************************************
# * Specific demo code
# *******************************************************************
def FormatDataRange(calc, datarange):
# Apply several formattings to the targeted range
# All impacted ranges and filter formulas are computed
# formula Output of the calc.Printf() method
# toprow The top row is computed with the calc.Offset() method
# calc The Calc document
# datarange The range where formatting applies
calc.ClearFormats(datarange) # Clear any actual format
calc.AlignRange(datarange, 'MC') # Align all cells: Middle and Center
# Define a number format (2 decimals) to all even numbers
formula = calc.Printf('=(MOD(%C1%R1;2)=0)', datarange) # =(MOD(B2;2)=0)
calc.FormatRange(datarange, '0,00', 'fr-BE', filterformula = formula, filterscope = 'CELL')
# Border all cells by column only when top cell is even
formula = calc.Printf('=(MOD(%C1$%R1;2)=0)', datarange) # =(MOD(B$2;2)=0)
calc.BorderRange(datarange, 'TBLRHV', filterformula = formula, filterscope = 'COLUMN')
# Put the header row in bold
toprow = calc.Offset(datarange, height = 1) # $Sheet1.*B*2:$K$2
calc.DecorateFont(toprow, decoration = 'B', fontsize = 15)
# Surround the header row
calc.BorderRange(toprow, 'TBLR')
# *******************************************************************
# * Setup a sheet with random data
# *******************************************************************
def SetupCalcSheet():
# Build from scratch a new calc document and load data in the first sheet
# ui UI service
# sheets Tuple of sheet names
# sheet Name of the first sheet
# data Random data, each value between 1 and 10
# datarow One single row of data
# calc The Calc document
# datarange The range where formatting applies
ui = CreateScriptService('UI')
calc = ui.CreateDocument('Calc')
sheets = calc.Sheets
sheet = sheets[0] # We assume there is at least 1 sheet
data = ()
datarange = sheet + '.' + targetrange
for i in range(0, calc.Height(datarange)):
datarow = ()
for j in range(0, calc.Width(datarange)):
datarow += (random.randrange(0, 10) + 1,)
data += (datarow,)
calc.SetArray(datarange, data)
calc.Activate(sheet) # Make the targeted sheet visible
return calc, datarange
g_exportedScripts = (Main,)
if __name__ == "__main__":
Main()