Tabbladen samenvoegen (LibreOffice Basic)

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

• LibreOffice Basic - Inleiding
• LibreOffice Basic - Objecten

Bronnen

Algemeen

• http://www.debugpoint.com/libreoffice-basic-macro-tutorial-index/ - Hoopvol
• https://wiki.documentfoundation.org/Development/How_to_debug - Geen Immediate Windows zoals in VBA

Code

• LibreOffice 3.4 Basic Programmer's Guide, p. 106 [1]
• http://openoffice3.web.fc2.com/OOoBasic_Calc.html#OOoCCA01a - Diverse voorbeelden. Ook goed voor je Japans
• http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XCellRangeMovement.html#copyRange - XCellRangeMovement API-calls
• https://www.prahladyeri.com/blog/2016/02/ten-libreoffice-macro-recipes.html

Copy & paste cell ranges

• https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XCellRangeData.html - getDataArray & setDataArray
• https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Data_Array
• https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Cell_Ranges
• http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/SheetCellRange.html
• https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=33756 - copyRange, voorbeeld-code
• https://forum.openoffice.org/en/forum/viewtopic.php?f=25&t=16413
• https://ask.libreoffice.org/en/question/129574/calc-copy-range-of-cells-from-all-sheets-into-new-sheet/ - Indrukwekkend
• https://ask.libreoffice.org/en/question/187542/macro-to-copy-current-sheet-and-insert-it-after-current-sheet/