[Serializable()]
//Create a subclass inheriting BaseFilterItem class.
public class CustomFilter : FarPoint.Win.Spread.BaseFilterItem
{
FarPoint.Win.Spread.SheetView sv = null;
public CustomFilter()
{
}
public CustomFilter(FarPoint.Win.Spread.SheetView sheetView)
{
this.sv = sheetView;
}
//---------------------------------------------------------
//Return names to be displayed in the drop-down list.
//---------------------------------------------------------
public override string DisplayName
{
get { return "Extract any values that are greater than 10 and less than 50."; }
}
//-----------------------------------------------------------------------------------------
//Set sheets to filters.
//-----------------------------------------------------------------------------------------
public override FarPoint.Win.Spread.SheetView SheetView
{
set { sv = value; }
}
//-----------------------------------------------------------------------------------------
//Evaluate specified values by conditions.
//-----------------------------------------------------------------------------------------
public bool IsFilteredIn(int value)
{
if (!(value == null) & (value >= 10) & (value <= 50))
{
//Return True only when the following conditions are satisfied.
//(1)Values are entered.
//(2)Values are not lower than 10.
//(3)Values are not greater than 50.
return true;
}
return false;
}
//-----------------------------------------------------------------------------------------
//Display names returned by DisplayName property.
//-----------------------------------------------------------------------------------------
public override bool ShowInDropDown(int columnIndex, int[] filteredInRowList)
{
return true;
}
//-----------------------------------------------------------------------------------------
//Execute filtering in the column specified in a sheet.
//-----------------------------------------------------------------------------------------
public override int[] Filter(int columnIndex)
{
System.Collections.ArrayList list = new System.Collections.ArrayList();
int[] returnList = null;
int row = 0;
if ((sv == null))
{
return returnList;
}
for (row = 0; row <= sv.RowCount - 1; row++)
{
if (!(sv.GetValue(row, columnIndex) == null))
{
int value = (int)sv.GetValue(row, columnIndex);
if (IsFilteredIn(value))
{
//Add row indexes that meet conditions sequentially.
list.Add(row);
}
}
}
//When there are any rows that meet conditions, copy them to arrays.
if (list.Count > 0)
{
returnList = new int[list.Count];
list.CopyTo(returnList);
list.Clear();
}
//Return all row indexes that meet conditions.
return returnList;
}
//-----------------------------------------------------------------------------------------
//When any data that has to be serialized is included in a custom filter class, override it.
//-----------------------------------------------------------------------------------------
public override bool Serialize(System.Xml.XmlTextWriter w)
{
w.WriteStartElement("CustomFilter");
base.Serialize(w);
w.WriteEndElement();
return true;
}
public override bool Deserialize(System.Xml.XmlNodeReader r)
{
if (r.NodeType == System.Xml.XmlNodeType.Element)
{
if (r.Name.Equals("CustomFilter"))
{
base.Deserialize(r);
}
}
return true;
}
}
private void Form1_Load(object sender, EventArgs e)
{
//Display only custom filters created in Column1.
FarPoint.Win.Spread.FilterColumnDefinition fcd = new FarPoint.Win.Spread.FilterColumnDefinition(0, FarPoint.Win.Spread.FilterListBehavior.Custom);
FarPoint.Win.Spread.HideRowFilter hf = new FarPoint.Win.Spread.HideRowFilter(fpSpread1.ActiveSheet);
hf.AddColumn(fcd);
fpSpread1.ActiveSheet.RowFilter = hf;
//Add the custom filter created for Column1.
CustomFilter cfi = new CustomFilter(fpSpread1.ActiveSheet);
FarPoint.Win.Spread.FilterColumnDefinition ccd = fpSpread1.ActiveSheet.RowFilter.GetFilterColumnDefinition(0);
ccd.Filters.Add(cfi);
fpSpread1.ActiveSheet.DefaultStyle.CellType = new FarPoint.Win.Spread.CellType.NumberCellType();
fpSpread1.ActiveSheet.SetValue(0, 0, 10);
fpSpread1.ActiveSheet.SetValue(1, 0, 100);
fpSpread1.ActiveSheet.SetValue(2, 0, 50);
fpSpread1.ActiveSheet.SetValue(3, 0, 40);
fpSpread1.ActiveSheet.SetValue(4, 0, 80);
fpSpread1.ActiveSheet.SetValue(5, 0, 1);
fpSpread1.ActiveSheet.SetValue(6, 0, 65);
fpSpread1.ActiveSheet.SetValue(7, 0, 20);
fpSpread1.ActiveSheet.SetValue(8, 0, 30);
fpSpread1.ActiveSheet.SetValue(9, 0, 35);
}
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Display only custom filters created in Column1.
Dim fcd As New FarPoint.Win.Spread.FilterColumnDefinition(0, FarPoint.Win.Spread.FilterListBehavior.Custom)
Dim hf As New FarPoint.Win.Spread.HideRowFilter(FpSpread1.ActiveSheet)
hf.AddColumn(fcd)
FpSpread1.ActiveSheet.RowFilter = hf
'Add the custom filter created for Column1.
Dim cfi As New CustomFilter(FpSpread1.ActiveSheet)
Dim ccd As FarPoint.Win.Spread.FilterColumnDefinition = FpSpread1.ActiveSheet.RowFilter.GetFilterColumnDefinition(0)
ccd.Filters.Add(cfi)
FpSpread1.ActiveSheet.DefaultStyle.CellType = New FarPoint.Win.Spread.CellType.NumberCellType
FpSpread1.ActiveSheet.SetValue(0, 0, 10)
FpSpread1.ActiveSheet.SetValue(1, 0, 100)
FpSpread1.ActiveSheet.SetValue(2, 0, 50)
FpSpread1.ActiveSheet.SetValue(3, 0, 40)
FpSpread1.ActiveSheet.SetValue(4, 0, 80)
FpSpread1.ActiveSheet.SetValue(5, 0, 1)
FpSpread1.ActiveSheet.SetValue(6, 0, 65)
FpSpread1.ActiveSheet.SetValue(7, 0, 20)
FpSpread1.ActiveSheet.SetValue(8, 0, 30)
FpSpread1.ActiveSheet.SetValue(9, 0, 35)
End Sub
'Custom filter class
<Serializable()> Public Class CustomFilter
'Create a sub-class inheriting BaseFilterItem class.
Inherits FarPoint.Win.Spread.BaseFilterItem
Dim sv As FarPoint.Win.Spread.SheetView = Nothing
Public Sub New()
End Sub
Public Sub New(ByVal sheetView As FarPoint.Win.Spread.SheetView)
Me.sv = sheetView
End Sub
'---------------------------------------------------------
'Return names to be displayed in the drop-down list.
'---------------------------------------------------------
Public Overrides ReadOnly Property DisplayName() As String
Get
Return "Extract any values that are greater than 10 and less than 50."
End Get
End Property
'-----------------------------------------------------------------------------------------
'Set sheets to filters.
'-----------------------------------------------------------------------------------------
Public Overrides WriteOnly Property SheetView() As FarPoint.Win.Spread.SheetView
Set(ByVal Value As FarPoint.Win.Spread.SheetView)
sv = Value
End Set
End Property
'-----------------------------------------------------------------------------------------
'Evaluate specified values by particular conditions.
'-----------------------------------------------------------------------------------------
Public Function IsFilteredIn(ByVal value As Integer) As Boolean
If Not (value = Nothing) And (value >= 10) And (value <= 50) Then
'Return True only when the following conditions are satisfied.
'(1)Values are entered.
'(2)Values are not lower than 10.
'(3)Values are not greater than 50.
Return True
End If
Return False
End Function
'-----------------------------------------------------------------------------------------
'Display names returned by DisplayName property.
'-----------------------------------------------------------------------------------------
Public Overrides Function ShowInDropDown(ByVal columnIndex As Integer, ByVal filteredInRowList() As Integer) As Boolean
Return True
End Function
'-----------------------------------------------------------------------------------------
'Execute filtering in the column specified in a sheet.
'-----------------------------------------------------------------------------------------
Public Overrides Function Filter(ByVal columnIndex As Integer) As Integer()
Dim list As New ArrayList
Dim returnList As Integer() = Nothing
Dim row As Integer
If (sv Is Nothing) Then
Return returnList
End If
For row = 0 To sv.RowCount - 1
If Not (sv.GetValue(row, columnIndex) = Nothing) Then
Dim value As Integer = sv.GetValue(row, columnIndex)
If IsFilteredIn(value) Then
'Add row indexes that meet conditions sequentially.
list.Add(row)
End If
End If
Next row
'When there are any rows that meet conditions, copy them to arrays.
If list.Count > 0 Then
returnList = New Integer(list.Count - 1) {}
list.CopyTo(returnList)
list.Clear()
End If
'Return all row indexes that meet conditions.
Return returnList
End Function
'-----------------------------------------------------------------------------------------
'When any data that has to be serialized is included in a custom filter class, override it.
'-----------------------------------------------------------------------------------------
Public Overrides Function Serialize(ByVal w As System.Xml.XmlTextWriter) As Boolean
w.WriteStartElement("CustomFilter")
MyBase.Serialize(w)
w.WriteEndElement()
Return True
End Function
Public Overrides Function Deserialize(ByVal r As System.Xml.XmlNodeReader) As Boolean
If r.NodeType = System.Xml.XmlNodeType.Element Then
If r.Name.Equals("CustomFilter") Then
MyBase.Deserialize(r)
End If
End If
Return True
End Function
End Class