Tabbladen samenvoegen (LibreOffice Basic): verschil tussen versies

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen
Regel 202: 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! De code verdient geen schoonheidsprijs, maar 't werkt wel:
  
−
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.
+
* Gebruik functie ... om sheet ''Aggregate'' aan te maken
 +
* Gebruik functie ... om tabblad-naam in te voegen als kolom op alle sheets
 +
* Gebruik functie ... om alle tabbladen samen te voegen op ''Aggregate''.
  
 
<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; is error-
 +
' of
 +
 
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 in new column on all sheets
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 +
' * 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 245: Regel 260:
 
' Insert sheet name on each row
 
' Insert sheet name on each row
 
'''''''''''''''''''''''''''''''''''''
 
'''''''''''''''''''''''''''''''''''''
−
+
'
 
for i = 0 to i_rows-1
 
for i = 0 to i_rows-1
−
 
 
ThisComponent.sheets(sheet_counter).getCellByPosition(0,i).String=s_sheetname
 
ThisComponent.sheets(sheet_counter).getCellByPosition(0,i).String=s_sheetname
−
 
 
next
 
next
 
 
Regel 257: Regel 270:
  
  
−
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
−
' Insert sheet 'Aggregate' (if not exists)
 
−
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
−
'
 
 
function insert_sheet()
 
function insert_sheet()
−
 
+
'
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
' Insert sheet 'Aggregate' (if not exists)
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 
dim doc as object
 
dim doc as object
 
dim sheet as object
 
dim sheet as object
Regel 281: Regel 294:
 
end if
 
end if
  
 +
end function
 +
 +
function copy_paste_test()
 +
'
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
' 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 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 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 copy_used_cells_to_aggregate_sheet()
 +
'
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
' Copy used cells 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
 +
 +
function copy_used_cells_all_sheets_to_aggregate_sheet()
 +
'
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
' Copy used cells of all sheets to aggregate-sheet
 +
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 +
'
 +
' It is assumed that 'aggregate' is the last sheet
 +
 +
' Loop through all sheets
 +
'''''''''''''''''''''''''''''''''''''
 +
'
 +
for sheet_counter = 0 to ThisComponent.sheets.count-2
 +
 +
' 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)
 +
 +
 +
' 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(sheet_counter).copyRange _
 +
( _
 +
cells_dest.CellAddress, _
 +
cells_source.RangeAddress _
 +
)
 +
 +
next
 +
 
end function
 
end function
 
</pre>
 
</pre>

Versie van 3 nov 2019 10:17

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! De code verdient geen schoonheidsprijs, maar 't werkt wel:

  • Gebruik functie ... om sheet Aggregate aan te maken
  • Gebruik functie ... om tabblad-naam in te voegen als kolom op alle sheets
  • Gebruik functie ... om alle tabbladen samen te voegen op Aggregate.
'
' 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; is error-
' of 

function insert_sheet_name_on_each_row_on_all_sheets()
	'
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	' Insert sheet name on each row in new column on all sheets
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	'
	' * 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
	
		' 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	
	
	next

end function


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
		'
		MsgBox "Sheet 'Aggregate' already exists"
		
	else
	
		sheet = doc.createInstance("com.sun.star.sheet.Spreadsheet")
		doc.sheets.insertByName("Aggregate",sheet)	
	
	end if

end function

function copy_paste_test()
	'
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	' 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 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 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 copy_used_cells_to_aggregate_sheet()
	'
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	' Copy used cells 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

function copy_used_cells_all_sheets_to_aggregate_sheet()
	'
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	' Copy used cells of all sheets to aggregate-sheet
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	'
	' It is assumed that 'aggregate' is the last sheet
	
	' Loop through all sheets
	'''''''''''''''''''''''''''''''''''''
	'
	for sheet_counter = 0 to ThisComponent.sheets.count-2
			
		' 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)
		
		
		' 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(sheet_counter).copyRange _
		( _
			cells_dest.CellAddress, _
			cells_source.RangeAddress _		
		)

	next
	
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=19882"