Jump to content

Macros/ScriptForge/HowToDeriveNewCellsFromCalcRangeQuickWin

From The Document Foundation Wiki


How to derive new cell ranges from a Calc range

Authored by Jean-Pierre Ledure.

The loading of the ScriptForge Basic library is present for completeness. However it can be done elsewhere. In Basic, all used variables are declared explicitly. The concerned code is presented inside a Basic Sub or a Python def.

Run that piece of code and consider the result.

Obatin cell ranges directiosn from Calc range.
Dialogue showing initial range, and its derived ranges.

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 14
  5. 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 15
  5. Save and run the Main() method

Code

REM		How to derive new ranges from a Calc range ?
REM		Minimal required version: LibreOffice 7.6
REM 	Used service(s)	Calc

Sub computeranges()
Dim ui As Object
Dim calc As Object
Dim range As String
Dim rangeheight As Long, rangewidth As Long
Dim toprow As String, bottomrow As String, leftcol As String, rightcol As String
Dim shiftrange As String

Dim newline As String	:	newline = Chr(10)

	GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
	Set ui = CreateScriptService("UI")

	Set calc = ui.CreateDocument("Calc")
	With calc
		range = .OpenRangeSelector(title := "Select an initial range", _
										singlecell := False, _
										closeafterselect := True)
		If Len(range) > 0 Then
			.CurrentSelection = range			'	Really select the chosen range
			rangeheight = .Height(range)
			rangewidth = .Width(range)

			'	https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03/sf_calc.html?&DbPAR=BASIC#Offset
				topleft = .FirstCell(range)
			bottomright = .LastCell(range)
			toprow = .Offset(range, height := 1)
			bottomrow = .Offset(range, rows := rangeheight - 1, height := 1)
			leftcol = .Offset(range, width := 1)
			rightcol = .Offset(range, columns := rangewidth - 1, width := 1)
			shiftrange = .Offset(range, columns := 1)

			MsgBox "Initial range = " & range & newline & newline _
					& "Dimensions = " & rangeheight & " rows x " & rangewidth & " columns" & newline _
					& "Top-left cell = " & topleft & newline _
					& "Bottom-right cell = " & bottomright & newline  _
					& "Top row = " & toprow & newline _
					& "Bottom row = " & bottomrow & newline _
					& "Left column = " & leftcol & newline _
					& "Right column = " & rightcol & newline _
					& "Range shifted by 1 column = " & shiftrange & newline _
					, title := "Ranges ..."
		End If
	End With

	'	Housekeeping
	Set calc = calc.Dispose()

End Sub
# coding: utf-8
from __future__ import unicode_literals

###		How to derive new ranges from a Calc range ?
###		Minimal required version: LibreOffice 7.6
### 	Used service(s)	Calc

from scriptforge import CreateScriptService

def computeranges():
	newline = '\n'

	ui = CreateScriptService('UI')
	basic = CreateScriptService('Basic')

	calc = ui.CreateDocument('Calc')
	range = calc.OpenRangeSelector(title = 'Select an initial range',
								   singlecell = False,
								   closeafterselect = True)

	if len(range) > 0:
		calc.CurrentSelection = range  # Really select the chosen range
		rangeheight = calc.Height(range)
		rangewidth = calc.Width(range)

		#	https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03/sf_calc.html?+DbPAR=BASIC#Offset
		topleft = calc.FirstCell(range)
		bottomright = calc.LastCell(range)
		toprow = calc.Offset(range, height = 1)
		bottomrow = calc.Offset(range, rows = rangeheight - 1, height = 1)
		leftcol = calc.Offset(range, width = 1)
		rightcol = calc.Offset(range, columns = rangewidth - 1, width = 1)
		shiftrange = calc.Offset(range, columns = 1)

		basic.MsgBox('Initial range = ' + range + newline + newline
				+ 'Dimensions = ' + str(rangeheight) + ' rows x ' + str(rangewidth) + ' columns'+ newline
				+ 'Top-left cell = ' + topleft + newline
				+ 'Bottom-right cell = ' + bottomright + newline
				+ 'Top row = ' + toprow + newline
				+ 'Bottom row = ' + bottomrow + newline
				+ 'Left column = ' + leftcol + newline
				+ 'Right column = ' + rightcol + newline
				+ 'Range shifted by 1 column = ' + shiftrange + newline,
				title = 'Ranges ...')

	# Housekeeping
	calc = calc.Dispose()


g_exportedScripts = (, )

if __name__ == "__main__":
	computeranges()

See also