Gespeichert von Erik Wegner am/um Do, 01.04.2010 - 22:27


In einer Datei gibt es ein Blatt mit einer Liste. Diese Liste soll durch den Aufruf Daten, Spezialfilter gefiltert werden. Das Filterkriterium soll dynamisch aus einer Zelle gelesen werden.


sub do_specialfilter
	'structure for address
	dim outputaddr as new com.sun.star.table.CellAddress
	'the source range of data to be filtered
	xfilter = thiscomponent.sheets(1).getcellrangebyname("A1:G32000") 
	'the filter rules are set up in this
	'we take the value each row is to be compared with from a special location
	qSheet = ThisComponent.Sheets(0)
	filterWert$ = qSheet.getCellRangeByName("A2").getValue()
	call clearCells 'Clear output cells
	outputaddr.sheet = 0  'set up output location as a cell address NOT a range
	outputaddr.column=0  ' "A4" - col 0
	outputaddr.row=3     ' "A4" - row 3
	' The filter specs are set up as an array (one element in this example) set the dimension accordingly
	dim aFilterFields(0)  as  new com.sun.star.sheet.TableFilterField
	' set the filter rules up (one element per column)
	aFilterFields(0).Field        = 3 ' field to be compared (starts at zero)
	'the following lines for numeric comparison
	'aFilterFields(0).IsNumeric    = true ' numeric comparison
	'aFilterFields(0).Operator     = com.sun.star.sheet.FilterOperator.LESS_EQUAL
	'aFilterFields(0).NumericValue =  filterWert ' value each row is to be compared with
	'these lines for string comparison
	aFilterFields(0).IsNumeric    = false ' character (string) comparison
	aFilterFields(0).Operator     = com.sun.star.sheet.FilterOperator.LESS_EQUAL
	aFilterFields(0).StringValue =  filterWert$ ' value each row is to be compared with
	' set the filter description up
	xFilterDesc.setFilterFields(aFilterFields()) 'set the filter rules by assigning the array of fields
	xfilterDesc.ContainsHeader=true  'there is a header line for the range to be filtered
	xfilterDesc.CopyOutputData=true    ' output to a separate location
	xfilterDesc.OutputPosition=outputaddr  ' this is the output location
	xFilter.filter(xFilterDesc)  'do the filter operation
end sub

sub clearCells
	rem ----------------------------------------------------------------------
	rem define variables
	dim document   as object
	dim dispatcher as object
	rem ----------------------------------------------------------------------
	rem get access to the document
	document   = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	rem ----------------------------------------------------------------------
	dim args1(0) as new com.sun.star.beans.PropertyValue
	args1(0).Name = "ToPoint"
	args1(0).Value = "Auswahl.$A$4:$A$32000" 'Auswahl is the sheet's name
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
	rem ----------------------------------------------------------------------
	rem dispatcher.executeDispatch(document, ".uno:Delete", "", 0, Array())
	rem ----------------------------------------------------------------------
	dim args3(0) as new com.sun.star.beans.PropertyValue
	args3(0).Name = "Flags"
	args3(0).Value = "R"
	dispatcher.executeDispatch(document, ".uno:DeleteCell", "", 0, args3())
end sub