Spezialfilter

Beschreibung

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.

Quellcode

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
	xfilterDesc=xFilter.createFilterDescriptor(true)
	
	'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