Macros/ScriptForge/HowToPilotSnakeExample
Appearance
< Macros | ScriptForge
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
How to pilot a snake in a spreadsheet
Authored by Jean-Pierre Ledure.
Define a menu in the menubar of a new spreadsheet, a snake is walking already inside the predefined limits of a cell range.
The menu items are:
- Stop
- Suspended (checkbox)
- Increase speed
- Decrease speed

How to run it in BASIC
Create a new document.
- Open the Basic IDE
- Select the first available blank module
- Copy and paste the Basic code
- Verify line 12
- Optionally, enter your parameters in lines 14+
- 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
- Verify line 19
- Optionally, enter your parameters in lines 21+
- Run main()
- To avoid inopportune screen freeze in Python, the expression
basic.Wait(1000 * waittime)
has been preferred totime.sleep(waittime)
.
Code
REM SCRIPTFORGE WIKI EXAMPLE
REM How to have a snake in your worksheet ?
REM Minimal required version: LibreOffice 25.8
REM Used services
REM Calc, Menu, Array
Option Explicit
'*******************************************************************
'* Snake parameters
'*******************************************************************
Const menuscript = "vnd.sun.star.script:Standard.Module1.ManageMenu?language=Basic&location=document"
Const enclosure = "~.$B$2:$Z$20"
Const head = "X"
Const snakelength = 25
Const timer = 300 ' Milliseconds
Const snakewidth = 800 ' in 1/100 mm
Const menuheader = "Snake"
'*******************************************************************
'* Shared variables
'*******************************************************************
Dim calc As Object ' The (new) sheet where the snake will be born
Dim toprow As Long ' Row and column numbers of enclosure
Dim bottomrow As Long
Dim leftcol As Long
Dim rightcol As Long
Dim snakebody As variant ' Array of snake body components, head = last item
' Stop/suspend/speed indicators
Dim stoprequest As Boolean
Dim waittime As Long
Dim suspendrequest As Boolean
'*******************************************************************
'* Run the demo below
'*******************************************************************
Sub Main()
GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
calc = SetupCalc()
SetupMenu()
WalkSnake()
' Housekeeping
calc.RemoveMenu("Snake")
calc.Dispose()
End Sub
'*******************************************************************
'* Setup a new Calc sheet
'*******************************************************************
Function SetupCalc() As Object
' Create a new Calc document
' Drop all gridlines
' Transform the cells in the target area into squares
' Put a frame around the target area
' Center all cells
Dim i As Long
Set calc = CreateScriptService("Calc", ThisComponent)
calc.InsertSheet("Snake")
calc.Activate("Snake")
With calc
toprow = .FirstRow(enclosure)
bottomrow = .LastRow(enclosure)
leftcol = .FirstColumn(enclosure)
rightcol = .LastColumn(enclosure)
.RunCommand(".uno:ToggleFormula") ' Hide the formula bar
.RunCommand(".uno:ViewRowColumnHeaders") ' Hide row and column headers
For i = toprow To bottomrow
.RunCommand(".uno:RowHeight", "RowHeight", snakewidth, "Row", i)
Next i
For i = leftcol To rightcol
.RunCommand(".uno:ColumnWidth", "ColumnWidth", snakewidth, "Column", i)
Next i
.BorderRange(enclosure, "Top,Bottom,Left,Right")
.AlignRange(enclosure, "Middle,Center")
.ColorizeRange(enclosure, background := RGB(255, 255, 255))
End With
Set SetupCalc = calc
End Function
'*******************************************************************
'* Setup the menu in the Calc worksheet
'*******************************************************************
Sub SetupMenu()
' Creation of the Snake menu with next items:
' Stop
' Suspended
' Increase speed
' Decrease speed
Dim menu As Object ' An instance of the ScriptForge Menu service
stoprequest = False
suspendrequest = False
waittime = timer
Set menu = calc.CreateMenu(menuheader)
With menu
.AddItem("Stop", tooltip := "Stop the snake", script := menuscript)
.AddCheckBox("Suspended", status := False, script := menuscript)
.AddItem("---")
.AddItem("Increase speed", name := "more", script := menuscript)
.AddItem("Decrease speed", name := "less", script := menuscript)
.Dispose()
End With
End Sub
'*******************************************************************
'* Execute the Snake menu actions
'*******************************************************************
Sub ManageMenu(menuevent)
' Interpret the passed event
Dim events As Variant
' event = "menuheader,itemname,itemid,status"
events = Split(menuevent, ",")
Select Case events(1)
Case "Stop"
stoprequest = True
Case "Suspended"
suspendrequest = CBool(events(3))
Case "more"
If waittime > 100 Then waittime = waittime - 100
Case "less"
waittime = waittime + 100
Case Else
MsgBox menuevent
End Select
End Sub
'*******************************************************************
'* Run a snake in the Calc sheet
'*******************************************************************
Sub WalkSnake()
' The snake emerges from a random position in the area
' Every time interval, the head moves with 1 position,
' in any direction, except:
' - not backwards
' - inside the area boundaries
' The last portion of the tail is reset.
Dim startpos As String ' Where the snake emerges, as a range
Dim nextdir As String ' Next direction U, D, L, R
Dim nextpos As String ' Next box position
Dim oldestpos As String ' Range containing the end of the tail
Dim bodylength As Long ' Actual snake length
Dim arr As Object : Set arr = CreateScriptService("Array")
With calc
startpos = .A1Style(Int(Rnd * (bottomrow - toprow + 1)) + toprow, _
Int(Rnd * (rightcol - leftcol + 1)) + leftcol, _
sheetname := "~")
snakebody = Array()
nextdir = ""
Do While Not stoprequest
' Suspended ?
If Not suspendrequest Then
bodylength = UBound(snakebody) + 1
If bodylength = 0 Then nextpos = startpos Else nextpos = NextStep(nextpos, nextdir)
' Clean old head
If bodylength > 0 Then .ClearValues(snakebody(bodylength - 1))
' Format new head
.ColorizeRange(nextpos, foreground := RGB(255, 255, 255), background := RGB(0, 0, 0))
.SetValue(nextpos, head)
.CurrentSelection = nextpos ' Visually nicer head
' Update the snakebody array: add the new item, drop the oldest one
If bodylength > 0 Then snakebody = arr.Insert(snakebody, bodylength, nextpos) Else snakebody = Array(nextpos)
oldestpos = snakebody(0)
If bodylength = snakelength Then snakebody = arr.Slice(snakebody, 1)
' Clean visually end of tail, only if not present elsewhere
If bodylength = snakelength Then
If Not arr.Contains(snakebody, oldestpos) Then .ColorizeRange(oldestpos, _
foreground := RGB(0, 0, 0), background := RGB(255, 255, 255))
End If
End If
Wait waittime
Loop
End With
End Sub
'*******************************************************************
'* Determine next step of the snake
'*******************************************************************
Function NextStep(lastpos As String, ByRef dir As String) As String
' Snake must not go backward vs. lastdir
' Snake must not cross borders
Dim alloweddirs As String ' Ex. "UDLR" : Up, Down, Left, Right
Dim nextdir As String ' Next direction
alloweddirs = ""
' Is Top etc. direction allowed ?
If dir <> "D" And calc.FirstRow(lastpos) <> toprow Then alloweddirs = alloweddirs & "U"
If dir <> "D" And calc.LastRow(lastpos) <> bottomrow Then alloweddirs = alloweddirs & "D"
If dir <> "R" And calc.FirstColumn(lastpos) <> leftcol Then alloweddirs = alloweddirs & "L"
If dir <> "L" And calc.LastColumn(lastpos) <> rightcol Then alloweddirs = alloweddirs & "R"
If Len(alloweddirs) = 0 Then Stop ' Deadlock. Should not happen.
' Choose among the allowed directions
nextdir = Mid(alloweddirs, Int(Rnd * Len(alloweddirs) + 1), 1)
Select Case nextdir
Case "U" : NextStep = calc.Offset(lastpos, rows := -1)
Case "D" : NextStep = calc.Offset(lastpos, rows := +1)
Case "L" : NextStep = calc.Offset(lastpos, columns := -1)
Case "R" : NextStep = calc.Offset(lastpos, columns := +1)
End Select
dir = nextdir ' The new direction is also returned to the caller
End Function
# coding: utf-8
from __future__ import unicode_literals
### SCRIPTFORGE WIKI EXAMPLE
### How to have a snake in your worksheet ?
### Minimal required version: LibreOffice 25.8
### Used services
### Calc, Menu
from scriptforge import CreateScriptService, CALC
from random import randint
basic = CreateScriptService('Basic')
# *******************************************************************
# * Snake parameters
# *******************************************************************
menuscript = 'vnd.sun.star.script:Module1.py$managemenu?language=Python&location=document'
enclosure = '~.$B$2:$Z$20'
head = 'X'
snakelength = 25
timer = 0.300 # Seconds
snakewidth = 800 # in 1/100 mm
menuheader = 'Snake'
# Stop/suspend/speed indicators
waittime = timer
stoprequest = False
suspendrequest = False
# *******************************************************************
# * Shared variables
# *******************************************************************
calc: CALC = None # The (new) sheet where the snake will be born
toprow = -1 # Row and column numbers of enclosure
bottomrow = -1
leftcol = -1
rightcol = -1
snakebody = [] # List of snake body components, head = last item
# *******************************************************************
# * Run the demo below
# *******************************************************************
def main(event = None):
global calc
setupcalc()
setupmenu()
walksnake()
# Housekeeping
calc.RemoveMenu(menuheader)
calc.Dispose()
# *******************************************************************
# * Setup a new Calc sheet
# *******************************************************************
def setupcalc():
# Create a new Calc document
# Drop all gridlines
# Transform the cells in the target area into squares
# Put a frame around the target area
# Center all cells
global calc, toprow, bottomrow, leftcol, rightcol
calc = CreateScriptService('Calc', basic.ThisComponent)
calc.InsertSheet('Snake')
calc.Activate('Snake')
toprow = calc.FirstRow(enclosure)
bottomrow = calc.LastRow(enclosure)
leftcol = calc.FirstColumn(enclosure)
rightcol = calc.LastColumn(enclosure)
calc.RunCommand('calc.uno:ToggleFormula') # Hide the formula bar
calc.RunCommand('calc.uno:ViewRowColumnHeaders') # Hide row and column headers
for i in range(toprow, bottomrow + 1):
calc.RunCommand('.uno:RowHeight', RowHeight = snakewidth, Row = i)
for i in range(leftcol, rightcol + 1):
calc.RunCommand('.uno:ColumnWidth', ColumnWidth = snakewidth, Column = i)
calc.BorderRange(enclosure, 'Top,Bottom,Left,Right')
calc.AlignRange(enclosure, 'Middle,Center')
calc.ColorizeRange(enclosure, background = basic.RGB(255, 255, 255))
# *******************************************************************
# * Setup the menu in the Calc worksheet
# *******************************************************************
def setupmenu():
# Creation of the Snake menu with next items:
# Stop
# Suspended
# Increase speed
# Decrease speed
global calc
menu = calc.CreateMenu(menuheader)
menu.AddItem('Stop', tooltip = 'Stop the snake', script = menuscript)
menu.AddCheckBox('Suspended', status = False, script = menuscript)
menu.AddItem('---')
menu.AddItem('Increase speed', name = 'more', script = menuscript)
menu.AddItem('Decrease speed', name = 'less', script = menuscript)
menu.Dispose()
# *******************************************************************
# * Execute the Snake menu actions
# *******************************************************************
def managemenu(menuevent = None):
# Interpret the passed event
# event = 'menuheader,itemname,itemid,status'
global stoprequest, suspendrequest, waittime
events = menuevent.split(',')
if events[1] == 'Stop':
stoprequest = True
elif events[1] == 'Suspended':
suspendrequest = events[3] == '1'
elif events[1] == 'more':
if waittime > 0.100:
waittime -= 0.100
elif events[1] == 'less':
waittime += 0.100
# *******************************************************************
# * Run a snake in the Calc sheet
# *******************************************************************
def walksnake():
# The snake emerges from a random position in the area
# Every time interval, the head moves with 1 position,
# in any direction, except:
# - not backwards
# - inside the area boundaries
# The last portion of the tail is reset.
# startpos Where the snake emerges, as a range
# nextdir Next direction U, D, L, R
# nextpos Next box position
# oldestpos Range containing the end of the tail
# bodylength Actual snake length
global calc, snakebody, suspendrequest
# *******************************************************************
# * Determine next step of the snake
# *******************************************************************
def nextstep(lastpos):
# Snake must not go backward vs. lastdir
# Snake must not cross borders
# alloweddirs Ex. 'UDLR' : Up, Down, Left, Right
# nextdir Next direction
nonlocal nextdir
alloweddirs = ''
# Is Top etc. direction allowed ?
if nextdir != 'D' and calc.FirstRow(lastpos) != toprow:
alloweddirs = alloweddirs + 'U'
if nextdir != 'D' and calc.LastRow(lastpos) != bottomrow:
alloweddirs = alloweddirs + 'D'
if nextdir != 'R' and calc.FirstColumn(lastpos) != leftcol:
alloweddirs = alloweddirs + 'L'
if nextdir != 'L' and calc.LastColumn(lastpos) != rightcol:
alloweddirs = alloweddirs + 'R'
# Choose among the allowed directions
nextdir = alloweddirs[randint(0, len(alloweddirs) - 1)]
if nextdir == 'U':
nextpos = calc.Offset(lastpos, rows = -1)
elif nextdir == 'D':
nextpos = calc.Offset(lastpos, rows = +1)
elif nextdir == 'L':
nextpos = calc.Offset(lastpos, columns = -1)
elif nextdir == 'R':
nextpos = calc.Offset(lastpos, columns = +1)
else:
nextpos = ''
return nextpos
startpos = calc.A1Style(randint(toprow, bottomrow),
randint(leftcol, rightcol),
sheetname = '~')
nextdir = ''
while stoprequest is False:
# Suspended ?
if suspendrequest is False:
bodylength = len(snakebody)
if bodylength == 0:
nextpos = startpos
else:
nextpos = nextstep(nextpos)
# Clean old head
if bodylength > 0:
calc.ClearValues(snakebody[-1])
# Format new head
calc.ColorizeRange(nextpos, foreground = basic.RGB(255, 255, 255), background = basic.RGB(0, 0, 0))
calc.SetValue(nextpos, head)
calc.CurrentSelection = nextpos # Visually nicer head
# Update the snakebody list : add the new item, drop the oldest one
snakebody.append(nextpos)
oldestpos = snakebody[0]
if bodylength == snakelength:
del snakebody[0]
# Clean visually end of tail, only if not present elsewhere in the snake body
if bodylength == snakelength:
if oldestpos not in snakebody:
calc.ColorizeRange(oldestpos,
foreground = basic.RGB(0, 0, 0), background = basic.RGB(255, 255, 255))
basic.Wait(1000 * waittime)
g_exportedScripts = (main, managemenu)
if __name__ == "__main__":
main()