Tabbladen samenvoegen (LibreOffice Basic): verschil tussen versies

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen
k (Jeroen Strompf heeft pagina Tabbladen Calc samenvoegen hernoemd naar Tabbladen samenvoegen (LibreOffice Basic))
 
(5 tussenliggende versies door dezelfde gebruiker niet weergegeven)
Regel 1: Regel 1:
−
== Debugging ==
 
−
 
−
https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=86186
 
−
 
−
== getDataArray() ==
 
−
 
−
Get an array from the contents of the cell range:
 
−
 
−
<pre>
 
−
sequence< sequence< any > > getDataArray()
 
−
 
−
 
−
''' Bronnen '''
 
−
 
−
* https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sheet_1_1XCellRangeData.html#abde435a89989ab90d13779c1ec49ca00
 
−
 
−
== copyByName() ==
 
−
 
−
<code>copyByName</code> is een method van ''sheets'':
 
−
 
−
<pre>
 
−
void copyByName
 
−
(
 
−
  [in] string aName,
 
−
  [in] string aCopy,
 
−
  [in] short  nDestination
 
−
)
 
−
 
−
* aName:        Name of the sheet to be copied
 
−
* aCopy:        Name of the copied sheet
 
−
* nDestination: Numerical index of the new sheet
 
−
</pre>
 
−
 
−
''' Voorbeeld '''
 
−
 
−
<pre>
 
−
Sub CopySheet
 
−
    Dim oCurrentController As Object
 
−
    Dim oActiveSheet As Object
 
−
    Dim oSheets As Object
 
−
    oCurrentController = ThisComponent.getCurrentController()
 
−
    oActiveSheet = oCurrentController.getActiveSheet()
 
−
    oSheets = ThisComponent.getSheets()
 
−
    If oSheets.hasByName( oActiveSheet.Name & "Copy" ) Then
 
−
        MsgBox "Sheet name " & oActiveSheet.Name & "Copy, already exists"
 
−
    Else
 
−
        oSheets.copyByName(oActiveSheet.Name, oActiveSheet.Name & "Copy", oActiveSheet.RangeAddress.Sheet + 1)
 
−
    End If
 
−
End Sub
 
−
</pre>
 
−
 
−
''' Bronnen '''
 
−
 
−
* https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sheet_1_1XSpreadsheets.html#a920aee3cc061fb6c1a018583354a004a
 
−
 
 
== Casus (okt. 2016) ==
 
== Casus (okt. 2016) ==
  
Regel 257: Regel 202:
 
== Casus (okt. 2019) ==
 
== Casus (okt. 2019) ==
  
−
Het is drie jaar later, en ik wil hetzelfde truukje doen. Ik heb in de tussenliggende jaren nix meer gedaan op dit gebied. Dus hoe gaat dat ook al weer?
+
Gelukt! Functie <code>merge_sheets</code> doet 't allemaal. 't Roept een tweetal andere functies aan. Verder staan er hier en daar wat testfuncties.
−
 
 
−
De code hieronder is een beginnetje. De echte truuk - Kopiëren van de ene sheet naar de andere, is nog niet gelukt. Dat kostte nu te veel tijd.
 
  
 
<pre>
 
<pre>
−
 
+
'
−
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
+
' Module "merge_calc_tabs"
−
' Insert sheet name on each row in new column on all sheets
 
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
'
 
'
−
' This function doesn't call any other own functions - This is all
+
' The problem
 +
'''''''''''''''''''''''''''''''''''''
 +
'
 +
' Sometimes I receive spreadsheets with lots of different tabs. I can only
 +
' import one tab at a time in MySQL, plus that the name of the tab, should
 +
' actually be a column. Taking care of all this manually, takes a lot of
 +
' time
 +
'
 +
' The solution
 +
'''''''''''''''''''''''''''''''''''''
 +
'
 +
' The function "merge_sheets" here below does it all automatically:
 +
'
 +
'
 
'
 
'
 +
function merge_sheets()
 +
'
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
' Merge all sheets to sheet "aggregate" - This is the complete function
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 +
' * Insert sheet "Aggregate" (if not exists)
 +
' * Include sheet name as first column on all sheets (except for "Aggregate")
 +
' * Insert all content on sheet "Aggregate"
 +
 +
'''''''''''''''''''''''''''''''''''''''''
 +
' Insert sheet "Aggregate" (if not exists)
 +
'''''''''''''''''''''''''''''''''''''''''
 +
'
 +
insert_sheet_aggregate
 +
 +
 +
'''''''''''''''''''''''''''''''''''''''''
 +
' Insert sheet name on all rows
 +
'''''''''''''''''''''''''''''''''''''''''
 +
'
 +
' Except for sheet "Aggregate"
 +
'
 +
insert_sheet_name_on_each_row_on_all_sheets
 +
 +
 +
'''''''''''''''''''''''''''''''''''''
 +
' Copy all content to "Aggregate"
 +
'''''''''''''''''''''''''''''''''''''
 +
'
 +
' Go through all sheets
 +
'''''''''''''''''''''''''''''''''''''
 +
'
 +
for sheet_counter = 0 to ThisComponent.sheets.count-1
 +
 +
' Only do stuff when sheet name is not "Aggregate"
 +
''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 +
if ThisComponent.sheets(sheet_counter).name<>"Aggregate" then
 +
'
 +
' Define source cell range
 +
'''''''''''''''''''''''''''''''''''''
 +
'
 +
o_cursor = ThisComponent.Sheets(sheet_counter).createCursor()
 +
o_cursor.gotoEndOfUsedArea(false)
 +
i_row_max = o_cursor.getRangeAddress().EndRow
 +
i_column_max = o_cursor.getRangeAddress().EndColumn
 +
cells_source=ThisComponent.sheets(sheet_counter).getCellRangeByPosition(0,0,i_column_max, i_row_max)
 +
 +
' Figure out destination cell location
 +
'''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 +
sheet_dest = ThisComponent.Sheets.getByName("Aggregate")
 +
o_cursor = sheet_dest.createCursor()
 +
o_cursor.gotoEndOfUsedArea(false)
 +
i_first_available_row = o_cursor.getRangeAddress().EndRow+1
 +
' msgBox "First available row: " & i_first_available_row
 +
 +
cells_dest=sheet_dest.getCellByPosition(0, i_first_available_row)
 +
 +
 +
' Copy!
 +
'''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 +
ThisComponent.sheets(sheet_counter).copyRange _
 +
( _
 +
cells_dest.CellAddress, _
 +
cells_source.RangeAddress _
 +
)
 +
 +
else
 +
 +
' Do nothing - We are on the "Aggregate" sheet
 +
 +
end if
 +
 +
next
 +
 +
end function
 +
 +
function test_detect_sheet_by_name()
 +
'
 +
' Loop through all sheets
 +
'''''''''''''''''''''''''''''''''''''
 +
'
 +
for sheet_counter = 0 to ThisComponent.sheets.count-1
 +
 +
 +
if ThisComponent.sheets(sheet_counter).name<>"ELU" then
 +
 +
' Do nothing
 +
 +
else
 +
 +
msgbox "Sheet gevonden bij index " & sheet_counter
 +
 +
end if
 +
 +
next
 +
 +
 +
end function
 +
 
function insert_sheet_name_on_each_row_on_all_sheets()
 
function insert_sheet_name_on_each_row_on_all_sheets()
 +
'
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
' Insert sheet name on each row of each sheet, except for "Aggregate" sheet
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 +
' * This one function, does almost everything at once
 +
' * This function is self-containing: No calls to other functions or subs
 
'
 
'
 
dim o_cursor as object
 
dim o_cursor as object
Regel 281: Regel 346:
 
for sheet_counter = 0 to ThisComponent.sheets.count-1
 
for sheet_counter = 0 to ThisComponent.sheets.count-1
 
 
−
' Get number of rows » i_rows
+
if ThisComponent.sheets(sheet_counter).name<>"Aggregate" then
−
'''''''''''''''''''''''''''''''''''''
+
−
'
+
' Get number of rows » i_rows
−
o_cursor = ThisComponent.Sheets(sheet_counter).createCursor()
+
'''''''''''''''''''''''''''''''''''''
−
o_cursor.gotoEndOfUsedArea(false)
+
'
−
i_rows = o_cursor.getRangeAddress().EndRow+1
+
o_cursor = ThisComponent.Sheets(sheet_counter).createCursor()
 +
o_cursor.gotoEndOfUsedArea(false)
 +
i_rows = o_cursor.getRangeAddress().EndRow+1
 +
 +
' Sheet name » s_sheetname
 +
'''''''''''''''''''''''''''''''''''''
 +
'
 +
s_sheetname = ThisComponent.sheets(sheet_counter).Getname
 +
 +
' Insert column
 +
'''''''''''''''''''''''''''''''''''''
 +
'
 +
Thiscomponent.sheets(sheet_counter).columns.insertbyindex(0,1)
 +
 +
' Insert sheet name on each row
 +
'''''''''''''''''''''''''''''''''''''
 +
'
 +
for i = 0 to i_rows-1
 +
ThisComponent.sheets(sheet_counter).getCellByPosition(0,i).String=s_sheetname
 +
next
 +
 +
else
 
 
−
' Sheet name » s_sheetname
+
' Do nothing: This is sheet "Aggregate"
−
'''''''''''''''''''''''''''''''''''''
+
 
 +
end if
 +
 +
next
 +
 
 +
end function
 +
 
 +
 
 +
function insert_sheet_aggregate()
 +
'
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
' Insert sheet 'Aggregate' at first position (if not exists)
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 +
if ThisComponent.sheets.hasByName("Aggregate") then
 +
'
 +
' Do nothing: Sheet "Aggregate" bestaat al
 
'
 
'
−
s_sheetname = ThisComponent.sheets(sheet_counter).Getname
+
else
−
+
'
−
' Insert column
+
ThisComponent.Sheets.insertNewByName("Aggregate", 0)
−
'''''''''''''''''''''''''''''''''''''
 
 
'
 
'
−
Thiscomponent.sheets(sheet_counter).columns.insertbyindex(0,1)
+
end if
−
+
 
−
' Insert sheet name on each row
+
end function
−
'''''''''''''''''''''''''''''''''''''
+
 
−
+
function test_copy_paste()
−
for i = 0 to i_rows-1
+
'
−
+
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
−
ThisComponent.sheets(sheet_counter).getCellByPosition(0,i).String=s_sheetname
+
' Copy & paste een paar cellen binnen de eerste sheet
−
+
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
−
next
+
'
 +
cell_org=ThisComponent.sheets(0).getCellRangeByPosition(0,0,0,2)
 +
cell_dest=ThisComponent.sheets(0).getCellByPosition(3,3)
 +
 
 +
ThisComponent.sheets(0).copyRange _
 +
( _
 +
cell_dest.CellAddress, _
 +
cell_org.RangeAddress _
 +
)
 +
 +
end function
 +
 
 +
function test_get_used_area_coordinates()
 +
'
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
' Return the number of the last used row
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 +
o_cursor = ThisComponent.Sheets(0).createCursor()
 +
o_cursor.gotoEndOfUsedArea(false)
 +
i_rows = o_cursor.getRangeAddress().EndRow+1
 +
i_columns = o_cursor.getRangeAddress().EndColumn+1
 
 
−
next
+
msgBox "Rijen: " & i_rows
 +
msgBox "Kolommen: " & i_columns
  
 
end function
 
end function
  
  
−
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
+
function test_copy_used_cells_within_same_sheet()
−
' Insert sheet 'Aggregate' (if not exists)
+
'
−
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
+
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
−
'
+
' Copy used cells at sheet(0)
−
function insert_sheet()
+
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 +
'
 +
' Create cursor
 +
'''''''''''''''''''''''''''''''''''''
 +
'
 +
o_cursor = ThisComponent.Sheets(0).createCursor()
 +
 +
 +
' Get coordinates of used area
 +
'''''''''''''''''''''''''''''''''''''
 +
'
 +
o_cursor.gotoEndOfUsedArea(false)
 +
i_row_max = o_cursor.getRangeAddress().EndRow
 +
i_column_max = o_cursor.getRangeAddress().EndColumn
 +
 
  
−
dim doc as object
+
' Define source cell range
−
dim sheet as object
+
'''''''''''''''''''''''''''''''''''''
 +
'
 +
cells_source=ThisComponent.sheets(0).getCellRangeByPosition(0,0,i_column_max, i_row_max)
 +
 
 
−
doc = ThisComponent
+
' Define destination cell location
 +
'''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 +
cells_dest=ThisComponent.sheets(0).getCellByPosition(6,0)
 +
 
 +
 
 +
' Copy!
 +
'''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 +
ThisComponent.sheets(0).copyRange _
 +
( _
 +
cells_dest.CellAddress, _
 +
cells_source.RangeAddress _
 +
)
 
 
−
if ThisComponent.sheets.hasByName("Aggregate") then
+
end function
 +
 
 +
 
 +
function test_copy_used_cells_to_aggregate_sheet()
 +
'
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
' Copy used cells from sheet(0) to aggregate-sheet
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 +
' Define source cell range
 +
'''''''''''''''''''''''''''''''''''''
 +
'
 +
o_cursor = ThisComponent.Sheets(0).createCursor()
 +
o_cursor.gotoEndOfUsedArea(false)
 +
i_row_max = o_cursor.getRangeAddress().EndRow
 +
i_column_max = o_cursor.getRangeAddress().EndColumn
 +
cells_source=ThisComponent.sheets(0).getCellRangeByPosition(0,0,i_column_max, i_row_max)
 
 
−
' Do nothing: Sheet "Aggregate" bestaat al
 
−
'
 
−
MsgBox "Sheet 'Aggregate' already exists"
 
−
 
−
else
 
 
 
−
sheet = doc.createInstance("com.sun.star.sheet.Spreadsheet")
+
' Define destination cell location
−
doc.sheets.insertByName("Aggregate",sheet)
+
'''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 +
sheet_dest = ThisComponent.Sheets.getByName("Aggregate")
 +
o_cursor = sheet_dest.createCursor()
 +
o_cursor.gotoEndOfUsedArea(false)
 +
i_first_available_row = o_cursor.getRangeAddress().EndRow+1
 +
msgBox "First available row: " & i_first_available_row
 
 
−
end if
+
cells_dest=sheet_dest.getCellByPosition(0, i_first_available_row)
 +
 
 +
 
 +
' Copy!
 +
'''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 +
ThisComponent.sheets(0).copyRange _
 +
( _
 +
cells_dest.CellAddress, _
 +
cells_source.RangeAddress _
 +
)
  
 +
 
end function
 
end function
 
</pre>
 
</pre>
Regel 341: Regel 523:
 
== Zie ook ==
 
== Zie ook ==
  
−
* [[LibreOffice Basic]]
+
* [[LibreOffice Basic - Inleiding]]
 +
* [[LibreOffice Basic - Objecten]]
  
 
== Bronnen ==
 
== Bronnen ==

Huidige versie van 4 nov 2019 om 15:31

Casus (okt. 2016)

Ik wil een rekenblad met zo'n 30 tabbladen importeren in MySQL. Daarbij wil ik die tabbladen samenvoegen, waarbij de naam van het tabblad als extra kolom wordt opgevoerd. Het liefst wordt dat geaggregeerde tabblad volautomatisch geëxporteerd in een passend formaat (TSV, etc.).

Gezochte functionaliteiten:

  • Voeg een nieuw tabblad toe
  • Voeg een nieuwe kolom toe aan alle tabbladen
  • Vul de naam van het tabblad in, in die nieuwe kolommen
  • Voeg de inhoud van alle tabbladen in, op het nieuw-aangemaakte tabblad.

De code hieronder, vervult deze subfunctionaliteiten. Er is geen code die alles samenvoegt.

Voeg een nieuw tabblad toe

function insert_sheet()

' ======================================================
' Insert sheet 'Aggregate' (if not exists)
' ======================================================	

dim doc as object
dim sheet as object

doc = ThisComponent

if ThisComponent.sheets.hasByName("Aggregate") then
	' Do nothing: Sheet "Aggregate" bestaat al
else

	sheet = doc.createInstance("com.sun.star.sheet.Spreadsheet")
	doc.sheets.insertByName("Aggregate",sheet)	

end if

end function

Voeg een kolom toe

function insert_column()

' ======================================================
' Insert column on current sheet
' ======================================================	
'
dim doc as object
dim sheet as object
dim new_column as object

doc = Thiscomponent
sheet = doc.Sheets(0)	' Select first sheet (counting starts at 0)

sheet.Columns.insertByIndex(0,1)	' At position 0, insert 1 column

end function

Get number of sheets

function get_number_of_sheets()

' ======================================================
' Get the number of sheets in the current file
' ======================================================	
'
' In dit geval niet gewerkt met tussenliggende variableen
' Op zich handig, zo'n abstractielaag, maar hier overkill
'
print ThisComponent.sheets.count

end function

Insert column on each sheet

function insert_column_on_each_sheet()
'
' ======================================================
' Insert column on each sheet
' ======================================================	
'
for i=0 to ThisComponent.sheets.count-1

	Thiscomponent.sheets(i).columns.insertbyindex(0,1)

next

end function

Get number of rows

function get_number_of_rows()
'
' ======================================================
' Get number of rows
' ======================================================	
'
' * Dat gaat alleen indirect, mbv. een cursor & gotEndOfUsedArea
' * Base=0. Dus het getal is 1 cijfer te laag
'
dim o_cursor as object

o_cursor = ThisComponent.Sheets(0).createCursor()
o_cursor.gotoEndOfUsedArea(false)

print o_cursor.getRangeAddress().EndRow

end function

Get sheet name

function get_sheet_name()

	' ======================================================
	' Get sheet name
	' ======================================================	
	' 
	get_sheet_name = thiscomponent.getcurrentcontroller.activesheet.GetName

end function

Insert sheet name on each row

function insert_sheet_name_on_each_row()

' ======================================================
' Insert sheet name on each row in new column
' ======================================================	

dim o_cursor as object
dim s_sheetname as string
dim i_rows as integer

' Get number of rows » i_rows
' ===================================
'
o_cursor = ThisComponent.Sheets(0).createCursor()
o_cursor.gotoEndOfUsedArea(false)
i_rows = o_cursor.getRangeAddress().EndRow+1

' Sheet name » s_sheetname
' ===================================
'
s_sheetname = ThisComponent.sheets(0).Getname

' Insert column
' =============
'
Thiscomponent.sheets(0).columns.insertbyindex(0,1)

' Insert sheet name on each row
' ============================

for i = 0 to i_rows-1

	ThisComponent.sheets(0).getCellByPosition(0,i).String=s_sheetname

next	

' Debug
' =====
'
' print s_sheetname & " " & i_rows

end function

Copy & paste cell ranges

Zie aparte hoofdstuk voor details: 

function copy_and_paste_copyrange_3()
'
' ===============================================================================================================
' copyRange + Range - Dit is 'm!
' ===============================================================================================================

cell_org=ThisComponent.sheets(0).getCellRangeByPosition(0,0,0,2)
cell_dest=ThisComponent.sheets(0).getCellByPosition(0,3)

ThisComponent.sheets(0).copyRange _
( _
	cell_dest.CellAddress, _
	cell_org.RangeAddress _
)	

end function

Casus (okt. 2019)

Gelukt! Functie merge_sheets doet 't allemaal. 't Roept een tweetal andere functies aan. Verder staan er hier en daar wat testfuncties. 

'
' Module "merge_calc_tabs"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' The problem
'''''''''''''''''''''''''''''''''''''
'
' Sometimes I receive spreadsheets with lots of different tabs. I can only 
' import one tab at a time in MySQL, plus that the name of the tab, should 
' actually be a column. Taking care of all this manually, takes a lot of
' time
'
' The solution
'''''''''''''''''''''''''''''''''''''
'
' The function "merge_sheets" here below does it all automatically:
'
'
'
function merge_sheets()
	'
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	' Merge all sheets to sheet "aggregate" - This is the complete function
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	'
	' * Insert sheet "Aggregate" (if not exists)
	' * Include sheet name as first column on all sheets (except for "Aggregate")
	' * Insert all content on sheet "Aggregate"
	
	'''''''''''''''''''''''''''''''''''''''''
	' Insert sheet "Aggregate" (if not exists)
	'''''''''''''''''''''''''''''''''''''''''
	'
	insert_sheet_aggregate


	'''''''''''''''''''''''''''''''''''''''''
	' Insert sheet name on all rows
	'''''''''''''''''''''''''''''''''''''''''
	'
	' Except for sheet "Aggregate"
	'
	insert_sheet_name_on_each_row_on_all_sheets
	
	
	'''''''''''''''''''''''''''''''''''''
	' Copy all content to "Aggregate"
	'''''''''''''''''''''''''''''''''''''
	'
	' Go through all sheets
	'''''''''''''''''''''''''''''''''''''	
	'
	for sheet_counter = 0 to ThisComponent.sheets.count-1
		
		' Only do stuff when sheet name is not "Aggregate"
		''''''''''''''''''''''''''''''''''''''''''''''''''
		'	
		if ThisComponent.sheets(sheet_counter).name<>"Aggregate" then
			'	
			' Define source cell range
			'''''''''''''''''''''''''''''''''''''	
			'
			o_cursor = ThisComponent.Sheets(sheet_counter).createCursor()
			o_cursor.gotoEndOfUsedArea(false)
			i_row_max = o_cursor.getRangeAddress().EndRow
			i_column_max = o_cursor.getRangeAddress().EndColumn
			cells_source=ThisComponent.sheets(sheet_counter).getCellRangeByPosition(0,0,i_column_max, i_row_max)
			
			' Figure out destination cell location
			'''''''''''''''''''''''''''''''''''''''''''''''''''
			'
			sheet_dest = ThisComponent.Sheets.getByName("Aggregate")
			o_cursor = sheet_dest.createCursor()
			o_cursor.gotoEndOfUsedArea(false)
			i_first_available_row = o_cursor.getRangeAddress().EndRow+1
			' msgBox "First available row: " & i_first_available_row

			cells_dest=sheet_dest.getCellByPosition(0, i_first_available_row)
		
		
			' Copy!
			'''''''''''''''''''''''''''''''''''''''''''''''''''
			'
			ThisComponent.sheets(sheet_counter).copyRange _
			( _
				cells_dest.CellAddress, _
				cells_source.RangeAddress _		
			)
	
		else
		
			' Do nothing - We are on the "Aggregate" sheet
	
		end if

	next
	
end function

function test_detect_sheet_by_name()
	'
	' Loop through all sheets
	'''''''''''''''''''''''''''''''''''''
	'
	for sheet_counter = 0 to ThisComponent.sheets.count-1


		if ThisComponent.sheets(sheet_counter).name<>"ELU" then
		
			' Do nothing

		else
					
			msgbox "Sheet gevonden bij index " & sheet_counter

		end if

	next
	
	
end function					

function insert_sheet_name_on_each_row_on_all_sheets()
	'
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	' Insert sheet name on each row of each sheet, except for "Aggregate" sheet
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	'
	' * This one function, does almost everything at once
	' * This function is self-containing: No calls to other functions or subs
	'
	dim o_cursor as object
	dim s_sheetname as string
	dim i_rows as integer
	dim i_sheets as integer
	
	' Loop through all sheets
	'''''''''''''''''''''''''''''''''''''
	'
	for sheet_counter = 0 to ThisComponent.sheets.count-1
	
		if ThisComponent.sheets(sheet_counter).name<>"Aggregate" then
		
			' Get number of rows » i_rows
			'''''''''''''''''''''''''''''''''''''
			'
			o_cursor = ThisComponent.Sheets(sheet_counter).createCursor()
			o_cursor.gotoEndOfUsedArea(false)
			i_rows = o_cursor.getRangeAddress().EndRow+1
			
			' Sheet name » s_sheetname
			'''''''''''''''''''''''''''''''''''''
			'
			s_sheetname = ThisComponent.sheets(sheet_counter).Getname
			
			' Insert column
			'''''''''''''''''''''''''''''''''''''
			'
			Thiscomponent.sheets(sheet_counter).columns.insertbyindex(0,1)
			
			' Insert sheet name on each row
			'''''''''''''''''''''''''''''''''''''
			'
			for i = 0 to i_rows-1
				ThisComponent.sheets(sheet_counter).getCellByPosition(0,i).String=s_sheetname
			next	
	
		else
		
			' Do nothing: This is sheet "Aggregate"

		end if			
	
	next

end function


function insert_sheet_aggregate()
	'
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	' Insert sheet 'Aggregate' at first position (if not exists)
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	'
	if ThisComponent.sheets.hasByName("Aggregate") then
		'	
		' Do nothing: Sheet "Aggregate" bestaat al
		'
	else
		'	
		ThisComponent.Sheets.insertNewByName("Aggregate", 0)
		'
	end if

end function

function test_copy_paste()
	'
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	' Copy & paste een paar cellen binnen de eerste sheet
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	'
	cell_org=ThisComponent.sheets(0).getCellRangeByPosition(0,0,0,2)
	cell_dest=ThisComponent.sheets(0).getCellByPosition(3,3)

	ThisComponent.sheets(0).copyRange _
	( _
		cell_dest.CellAddress, _
		cell_org.RangeAddress _
	)	
	
end function	

function test_get_used_area_coordinates()
	'
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	' Return the number of the last used row
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	'
	o_cursor = ThisComponent.Sheets(0).createCursor()
	o_cursor.gotoEndOfUsedArea(false)
	i_rows = o_cursor.getRangeAddress().EndRow+1
	i_columns = o_cursor.getRangeAddress().EndColumn+1
	
	msgBox "Rijen: " & i_rows
	msgBox "Kolommen: " & i_columns

end function


function test_copy_used_cells_within_same_sheet()
	'
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	' Copy used cells at sheet(0)
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	'
		'
	' Create cursor
	'''''''''''''''''''''''''''''''''''''
	'
	o_cursor = ThisComponent.Sheets(0).createCursor()
	
	
	' Get coordinates of used area
	'''''''''''''''''''''''''''''''''''''	
	'
	o_cursor.gotoEndOfUsedArea(false)
	i_row_max = o_cursor.getRangeAddress().EndRow
	i_column_max = o_cursor.getRangeAddress().EndColumn


	' Define source cell range
	'''''''''''''''''''''''''''''''''''''
	'
	cells_source=ThisComponent.sheets(0).getCellRangeByPosition(0,0,i_column_max, i_row_max)
	
	
	' Define destination cell location
	'''''''''''''''''''''''''''''''''''''''''''''''''''
	'
	cells_dest=ThisComponent.sheets(0).getCellByPosition(6,0)


	' Copy!
	'''''''''''''''''''''''''''''''''''''''''''''''''''
	'
	ThisComponent.sheets(0).copyRange _
	( _
		cells_dest.CellAddress, _
		cells_source.RangeAddress _		
	)
	
end function


function test_copy_used_cells_to_aggregate_sheet()
	'
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	' Copy used cells from sheet(0) to aggregate-sheet
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	'	
	' Define source cell range
	'''''''''''''''''''''''''''''''''''''	
	'
	o_cursor = ThisComponent.Sheets(0).createCursor()
	o_cursor.gotoEndOfUsedArea(false)
	i_row_max = o_cursor.getRangeAddress().EndRow
	i_column_max = o_cursor.getRangeAddress().EndColumn
	cells_source=ThisComponent.sheets(0).getCellRangeByPosition(0,0,i_column_max, i_row_max)
	
	
	' Define destination cell location
	'''''''''''''''''''''''''''''''''''''''''''''''''''
	'
	sheet_dest = ThisComponent.Sheets.getByName("Aggregate")
	o_cursor = sheet_dest.createCursor()
	o_cursor.gotoEndOfUsedArea(false)
	i_first_available_row = o_cursor.getRangeAddress().EndRow+1
	msgBox "First available row: " & i_first_available_row
	
	cells_dest=sheet_dest.getCellByPosition(0, i_first_available_row)


	' Copy!
	'''''''''''''''''''''''''''''''''''''''''''''''''''
	'
	ThisComponent.sheets(0).copyRange _
	( _
		cells_dest.CellAddress, _
		cells_source.RangeAddress _		
	)

	
end function

Zie ook

Bronnen

Algemeen

Code

Copy & paste cell ranges

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=Tabbladen_samenvoegen_(LibreOffice_Basic)&oldid=19892"