Jump to content

Macros/ScriptForge/HowToPilotSnakeExample

From The Document Foundation Wiki


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
Snake pixels game.
Snake pixels game.

How to run it in BASIC

Create a new document.

  1. Open the Basic IDE
  2. Select the first available blank module
  3. Copy and paste the Basic code
  4. Verify line 12
  5. Optionally, enter your parameters in lines 14+
  6. Run Main()

How to run it in Python

Create a new document.

  1. Run APSO
  2. Create a new module, call it 'Module1'
  3. Copy and paste the Python code below
  4. Verify line 19
  5. Optionally, enter your parameters in lines 21+
  6. Run main()
  7. To avoid inopportune screen freeze in Python, the expression basic.Wait(1000 * waittime) has been preferred to time.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()

See also