Excelライクフィルタの条件をコードで指定する

ExcelライクフィルタではUIからのフィルタリングを行う方法に加えて、コーディングで条件を指定する方法をサポートしています。フィルタリングはテキストの値だけではなく背景色やテキスト色などで行え、FilterColumnDefinitionCollectionクラスにフィルタ列定義を追加しAutoFilterColumnメソッドを呼び出すことで実現できます。

【実行例】実行前

【実行例】背景色の赤でフィルタリング

【実行例】テキスト色の青でフィルタリング

C#
Visual Basic

private void Form1_Load(object sender, System.EventArgs e)
 {
  // 自動フィルタリングを有効
  fpSpread1.ActiveSheet.Columns[0].AllowAutoFilter = true;
  // Excelライクフィルタを有効
  fpSpread1.ActiveSheet.AutoFilterMode = FarPoint.Win.Spread.AutoFilterMode.EnhancedContextMenu;
  fpSpread1.ActiveSheet.Cells[0, 0].Value = "North";
  fpSpread1.ActiveSheet.Cells[1, 0].Value = "South";
  fpSpread1.ActiveSheet.Cells[2, 0].Value = "East";
  fpSpread1.ActiveSheet.Cells[3, 0].Value = "South";
  fpSpread1.ActiveSheet.Cells[4, 0].Value = "North";
  fpSpread1.ActiveSheet.Cells[5, 0].Value = "North";
  fpSpread1.ActiveSheet.Cells[6, 0].Value = "West";
  fpSpread1.ActiveSheet.Columns[0].Width = 80;
  // 背景色を変更
  fpSpread1.ActiveSheet.Cells[1, 0].BackColor = Color.Red;
  fpSpread1.ActiveSheet.Cells[3, 0].BackColor = Color.Red;
  fpSpread1.ActiveSheet.Cells[5, 0].BackColor = Color.Red;
  // フォント色を変更
  fpSpread1.ActiveSheet.Cells[2, 0].ForeColor = Color.Blue;
  fpSpread1.ActiveSheet.Cells[4, 0].ForeColor = Color.Blue;
  fpSpread1.ActiveSheet.Cells[6, 0].ForeColor = Color.Blue;
}
private void button1_Click(object sender, EventArgs e)
{
  // フィルタの解除
  fpSpread1.ActiveSheet.RowFilter.ResetFilter();
  // 背景色の赤でフィルタリング
  FarPoint.Win.Spread.ColorFilterItem colorfilter = new FarPoint.Win.Spread.ColorFilterItem(false, Color.Red);
  FarPoint.Win.Spread.FilterColumnDefinition fd = new FarPoint.Win.Spread.FilterColumnDefinition(0, FarPoint.Win.Spread.FilterListBehavior.Custom);
  fd.Filters.Add(colorfilter);
  fpSpread1.ActiveSheet.RowFilter.ColumnDefinitions.Add(fd);
  fpSpread1.ActiveSheet.AutoFilterColumn(0, colorfilter.DisplayName, 0);
  fpSpread1.ActiveSheet.AutoFilterMode = FarPoint.Win.Spread.AutoFilterMode.EnhancedContextMenu;
}
private void button2_Click(object sender, EventArgs e)
{
  // フィルタの解除
  fpSpread1.ActiveSheet.RowFilter.ResetFilter();
  //1列目をテキスト色の青でフィルタリング
  FarPoint.Win.Spread.ColorFilterItem colorfilter = new FarPoint.Win.Spread.ColorFilterItem(true, Color.Blue);
  FarPoint.Win.Spread.FilterColumnDefinition fd = new FarPoint.Win.Spread.FilterColumnDefinition(0, FarPoint.Win.Spread.FilterListBehavior.Custom);
  fd.Filters.Add(colorfilter);
  fpSpread1.ActiveSheet.RowFilter.ColumnDefinitions.Add(fd);
  fpSpread1.ActiveSheet.AutoFilterColumn(0, colorfilter.DisplayName, 0);
  fpSpread1.ActiveSheet.AutoFilterMode = FarPoint.Win.Spread.AutoFilterMode.EnhancedContextMenu;
}

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
  ' 自動フィルタリングを有効
  FpSpread1.ActiveSheet.Columns(0).AllowAutoFilter = True
  ' Excelライクフィルタを有効
  FpSpread1.ActiveSheet.AutoFilterMode = FarPoint.Win.Spread.AutoFilterMode.EnhancedContextMenu
  FpSpread1.ActiveSheet.Cells(0, 0).Value = "North"
  FpSpread1.ActiveSheet.Cells(1, 0).Value = "South"
  FpSpread1.ActiveSheet.Cells(2, 0).Value = "East"
  FpSpread1.ActiveSheet.Cells(3, 0).Value = "South"
  FpSpread1.ActiveSheet.Cells(4, 0).Value = "North"
  FpSpread1.ActiveSheet.Cells(5, 0).Value = "North"
  FpSpread1.ActiveSheet.Cells(6, 0).Value = "West"
  FpSpread1.ActiveSheet.Columns(0).Width = 80
  ' 背景色を変更
  FpSpread1.ActiveSheet.Cells(1, 0).BackColor = Color.Red
  FpSpread1.ActiveSheet.Cells(3, 0).BackColor = Color.Red
  FpSpread1.ActiveSheet.Cells(5, 0).BackColor = Color.Red
  ' フォント色を変更
  FpSpread1.ActiveSheet.Cells(2, 0).ForeColor = Color.Blue
  FpSpread1.ActiveSheet.Cells(4, 0).ForeColor = Color.Blue
  FpSpread1.ActiveSheet.Cells(6, 0).ForeColor = Color.Blue
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  ' フィルタの解除
  FpSpread1.ActiveSheet.RowFilter.ResetFilter()
  ' 1列目を背景色の赤でフィルタリング
  Dim colorfilter As New FarPoint.Win.Spread.ColorFilterItem(False, Color.Red)
  Dim fd As New FarPoint.Win.Spread.FilterColumnDefinition(0, FarPoint.Win.Spread.FilterListBehavior.Custom)
  fd.Filters.Add(colorfilter)
  FpSpread1.ActiveSheet.RowFilter.ColumnDefinitions.Add(fd)
  FpSpread1.ActiveSheet.AutoFilterColumn(0, colorfilter.DisplayName, 0)
  FpSpread1.ActiveSheet.AutoFilterMode = FarPoint.Win.Spread.AutoFilterMode.EnhancedContextMenu
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
  ' フィルタの解除
  FpSpread1.ActiveSheet.RowFilter.ResetFilter()
  ' 1列目をテキスト色の青でフィルタリング
  Dim colorfilter As New FarPoint.Win.Spread.ColorFilterItem(True, Color.Blue)
  Dim fd As New FarPoint.Win.Spread.FilterColumnDefinition(0, FarPoint.Win.Spread.FilterListBehavior.Custom)
  fd.Filters.Add(colorfilter)
  FpSpread1.ActiveSheet.RowFilter.ColumnDefinitions.Add(fd)
  FpSpread1.ActiveSheet.AutoFilterColumn(0, colorfilter.DisplayName, 0)
  FpSpread1.ActiveSheet.AutoFilterMode = FarPoint.Win.Spread.AutoFilterMode.EnhancedContextMenu
End Sub