Jump to content

Macros/ScriptForge/CopyRangeofCells

From The Document Foundation Wiki


How to copy a range of cells

Authored by Jean-Pierre Ledure.

The example shows how to copy a range of cells to a defined destination. Four alternatives are presented:

  • Copy the range to another cell located in the same file
  • Copy the range to another cell located in another spreadsheet
  • Copy the range to another range located in the same file
  • Store the range as a PDF file

The initial range is populated arbitrarily with scalars and formulas. The targeted range is specified with an interactive range selector.

A dialog to choose 1 out of 4 alternatives to copy a range of cells in Calc.
A dialog to choose 1 out of 4 alternatives to copy a range of cells in Calc.

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 Basic code
  5. Verify line 14
  6. 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. Verify line 15
  6. Optionally, enter your parameters in lines 19+
  7. Run main()

Code

REM  SCRIPTFORGE WIKI EXAMPLE
REM 	How to copy a cell range ?
REM		Minimal required version: LibreOffice 7.6
REM 	Used services
REM 		Calc, FileSystem, Array

Public calc1 As Object
Public calc2 As Object
Public ui As Object			'	The UI service

'*******************************************************************
'***	Define the input range
'*******************************************************************
Const inputrange = "$B$2:$L$9"

'*******************************************************************
'* Run the demo below
'*******************************************************************
Sub Main(Optional event As Object)
Dim copytype As String		'	1 = same file, 2 = other file
	GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
	copytype = InputBox(	"1 = Copy to cell in same file" & Chr(10) _
						&	"2 = Copy to cell in other file" & Chr(10) _
						&	"3 = Copy to range in same file (destination must be >= source)" & Chr(10) _
						&	"4 = Export range to a PDF file" _
						, "Copy range")
	If copytype = "" Or copytype = "0" Then Exit Sub
	DefineInputRange()
	CopyInputRange(copytype)
End Sub

'*******************************************************************
'* Specific demo code
'*******************************************************************
Sub CopyInputRange(copytype As String)

Dim target As String
Dim inputarea As Object
Dim fs As Object

	Select Case copytype
		Case "1"		'	Copy to same file, same or other sheet
			target = calc1.OpenRangeSelector("Select the target cell", singlecell := True)
			If target = "" Then Exit Sub
			calc1.CopyToCell(inputrange, target)
		Case "2"		'	Copy to another file
			Set calc2 = ui.CreateDocument("Calc")
			target = calc2.OpenRangeSelector("Select the target cell", singlecell := True)
			inputarea = calc1.Range(inputrange)		'	Make a reference from the range given as a string
			calc2.CopyToCell(inputarea, target)
		Case "3"		'	Copy to range in same file
			target = calc1.OpenRangeSelector("Select the target range of cells", singlecell := False)
			If target = "" Then Exit Sub
			calc1.CopyToRange(inputrange, target)
		Case "4"
			fs = CreateScriptService("FileSystem")
			target = fs.PickFile(mode := "SAVE")
			calc1.ExportRangeToFile(inputrange, target, imagetype := "PDF", overwrite := True)
		Case Else
	End Select

End Sub

'*******************************************************************
'* Compute the content of the input range
'*******************************************************************
Sub DefineInputRange()
'	The range contains scalars in the 1st row and formulas elsewhere

Dim toprow As String		'	The 1st row of the input range
Dim firstcell As String		'	Top-left cell of the input range
Dim formula As String		'	The formula to apply across the range

	Set ui = CreateScriptService("UI")
	Set arr = CreateScriptService("Array")
	Set calc1 = ui.CreateDocument("Calc")

	toprow = calc1.Offset(inputrange, height := 1)
	calc1.SetValue(toprow, arr.RangeInit(1, calc1.Width(toprow)))
	
	firstcell = Replace(calc1.FirstCell(inputrange), "$", "")
	formula = "=" & firstcell & "+1"
	calc1.SetFormula(calc1.Offset(inputrange, 1, height := calc1.Height(inputrange) - 1), formula)

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

from scriptforge import CreateScriptService

###  SCRIPTFORGE WIKI EXAMPLE
### 	How to copy a cell range ?
### 	Minimal required version: LibreOffice 7.6
### 	Used services
### 		Calc, FileSystem, Basic

basic = CreateScriptService('Basic')


# *******************************************************************
# ***	Define the input range
# *******************************************************************
inputrange = '$B$2:$L$9'


# *******************************************************************
# * Run the demo below
# *******************************************************************
def main():
	copytype = basic.InputBox('1 = Copy to cell in same file\n'
							  + '2 = Copy to cell in other file\n'
							  + '3 = Copy to range in same file (destination must be >= source)\n'
							  + '4 = Export range to a PDF file'
							  , 'Copy range')
	if copytype == '' or copytype == '0':
		return None
	calc1 = defineinputrange()
	copyinputrange(calc1, copytype)


# *******************************************************************
# * Specific demo code
# *******************************************************************
def copyinputrange(calc1, copytype):

	ui = CreateScriptService('UI')

	if copytype == '1':  # Copy to same file, same or other sheet
		target = calc1.OpenRangeSelector('Select the target cell', singlecell = True)
		if target == '':
			return None
		calc1.CopyToCell(inputrange, target)
	elif copytype == '2':  # Copy to another file
		calc2 = ui.CreateDocument('Calc')
		target = calc2.OpenRangeSelector('Select the target cell', singlecell = True)
		inputarea = calc1.Range(inputrange)  # Make a reference from the range given as a string
		calc2.CopyToCell(inputarea, target)
	elif copytype == '3':  # Copy to range in same file
		target = calc1.OpenRangeSelector('Select the target range of cells', singlecell = False)
		if target == '':
			return None
		calc1.CopyToRange(inputrange, target)
	elif copytype == '4':
		fs = CreateScriptService('FileSystem')
		target = fs.PickFile(mode = 'SAVE')
		calc1.ExportRangeToFile(inputrange, target, imagetype = 'PDF', overwrite = True)

	return None

# *******************************************************************
# * Compute the content of the input range
# *******************************************************************
def defineinputrange():
	#	The range contains scalars in the 1st row and formulas elsewhere

	# toprow						The 1st row of the input range
	# firstcell						Top-left cell of the input range
	# formula						The formula to apply across the range

	ui = CreateScriptService('UI')
	calc1 = ui.CreateDocument('Calc')

	toprow = calc1.Offset(inputrange, height = 1)
	calc1.SetValue(toprow, tuple(range(1, calc1.Width(toprow) + 1)))

	firstcell = calc1.FirstCell(inputrange).replace('$', '')
	formula = '=' + firstcell + '+1'
	calc1.SetFormula(calc1.Offset(inputrange, 1, height = calc1.Height(inputrange) - 1), formula)

	return calc1


g_exportedScripts = (main,)

if __name__ == "__main__":
	main()


See also