Jump to content

Macros/ScriptForge/FormatPortionsCalcRangeExample

From The Document Foundation Wiki


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.

A Calc range with portions of it selectively formatted.
A Calc range with portions of it selectively formatted.

How to run it in BASIC

  1. Create a new document
  2. Open the Basic IDE
  3. Select the first available blank module
  4. Copy and paste the code
  5. Run Main()

How to run it in Python

  1. Create a new document
  2. Run APSO
  3. Create a new module, call it 'Module1'
  4. Copy and paste the Python code below
  5. 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()

See also