Excelフィルタリング

SPREADでは、従来の単一データに絞り込むフィルタリングの他、Excel2010スタイルのフィルタリングが可能です。 フィルタしたい文字の検索、列の一意の値を含むチェックボックス、条件フィルタが利用でき、複数項目の絞り込みが簡単に実現できます。
 連番
氏名
性別
生年月日
年齢
11000長岡 文音1980/07/0932
21001住田 晶子1992/10/2020
31002西沢 有希1961/04/1551
41003石渡 実緒1954/05/2458
51004大林 芳雄1981/06/2931
61005花井 理津子1984/10/1628
71006永田 浩司1968/01/2244
81007古山 春菜1976/03/1536
91008水島 幸真1959/04/0653
101009浜田 雅典1962/03/3050
   

ソースコード

別ウィンドウで表示
using System;
using System.Web.UI.WebControls;

public partial class filtering_enhancedfilter : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            return;
        }

        // SPREADの設定
        InitSpread(FpSpread1);

        // シート設定
        InitSpreadStyles(FpSpread1.Sheets[0]);
    }

    private void InitSpread(FarPoint.Web.Spread.FpSpread spread)
    {
        //データ連結
        System.Data.DataSet ds = new System.Data.DataSet();
        ds.ReadXml(MapPath("../App_Data/dataexcelfilter.xml"));
        spread.DataSource = ds;

        spread.CssClass = "spreadStyle";
        spread.UseClipboard = false;
    }

    private void InitSpreadStyles(FarPoint.Web.Spread.SheetView sheet)
    {
        // フォントサイズの設定
        sheet.DefaultStyle.Font.Size = FontUnit.Parse("80%");
        sheet.ColumnHeader.DefaultStyle.Font.Size = FontUnit.Parse("80%");
        sheet.RowHeader.DefaultStyle.Font.Size = FontUnit.Parse("80%");
        sheet.SheetCorner.DefaultStyle.Font.Size = FontUnit.Parse("80%");

        // 列幅の設定
        sheet.Columns[0].Width = 50;
        sheet.Columns[1].Width = 120;
        sheet.Columns[2].Width = 80;
        sheet.Columns[3].Width = 100;
        sheet.Columns[4].Width = 80;

        // 縦方向の揃え位置を中央に設定
        sheet.DefaultStyle.VerticalAlign = VerticalAlign.Middle;

        // 背景色の設定
        for (int i = 0; i < sheet.RowCount; i++)
        {
            if (i % 5 == 0)
            {
                sheet.Rows[i].BackColor = System.Drawing.Color.Azure;
            }
            else if (i % 5 == 1)
            {
                sheet.Rows[i].BackColor = System.Drawing.Color.Beige;
            }
            else if (i % 5 == 2)
            {
                sheet.Rows[i].BackColor = System.Drawing.Color.LavenderBlush;
            }
            else if (i % 5 == 3)
            {
                sheet.Rows[i].BackColor = System.Drawing.Color.Silver;
            }
            else if (i % 5 == 4)
            {
                sheet.Rows[i].BackColor = System.Drawing.Color.PaleVioletRed;
            }

        }

        // フィルタリングの設定
        sheet.AutoFilterMode = FarPoint.Web.Spread.AutoFilterMode.Enhanced; 
        FarPoint.Web.Spread.IRowFilter rowFilter = new FarPoint.Web.Spread.HideRowFilter(sheet);

        FarPoint.Web.Spread.FilterColumnDefinition fd1 = new FarPoint.Web.Spread.FilterColumnDefinition(1, FarPoint.Web.Spread.FilterListBehavior.Default);
        FarPoint.Web.Spread.FilterColumnDefinition fd2 = new FarPoint.Web.Spread.FilterColumnDefinition(2, FarPoint.Web.Spread.FilterListBehavior.Default);
        FarPoint.Web.Spread.FilterColumnDefinition fd3 = new FarPoint.Web.Spread.FilterColumnDefinition(3, FarPoint.Web.Spread.FilterListBehavior.Default);
        FarPoint.Web.Spread.FilterColumnDefinition fd4 = new FarPoint.Web.Spread.FilterColumnDefinition(4, FarPoint.Web.Spread.FilterListBehavior.Default);
        rowFilter.ColumnDefinitions.Add(fd1);
        rowFilter.ColumnDefinitions.Add(fd2);
        rowFilter.ColumnDefinitions.Add(fd3);
        rowFilter.ColumnDefinitions.Add(fd4);

        sheet.RowFilter = rowFilter;
    }
}

Partial Class filtering_enhancedfilter
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If IsPostBack Then
            Return
        End If

        ' SPREADの設定
        InitSpread(FpSpread1)

        ' シート設定
        InitSpreadStyles(FpSpread1.Sheets(0))
    End Sub

    Private Sub InitSpread(ByVal spread As FarPoint.Web.Spread.FpSpread)
        'データ連結
        Dim ds As New System.Data.DataSet()
        ds.ReadXml(MapPath("../App_Data/dataexcelfilter.xml"))
        spread.DataSource = ds

        spread.CssClass = "spreadStyle"
        spread.UseClipboard = False
    End Sub

    Private Sub InitSpreadStyles(ByVal sheet As FarPoint.Web.Spread.SheetView)
        ' フォントサイズの設定
        sheet.DefaultStyle.Font.Size = FontUnit.Parse("80%")
        sheet.ColumnHeader.DefaultStyle.Font.Size = FontUnit.Parse("80%")
        sheet.RowHeader.DefaultStyle.Font.Size = FontUnit.Parse("80%")
        sheet.SheetCorner.DefaultStyle.Font.Size = FontUnit.Parse("80%")

        ' 列幅の設定
        sheet.Columns(0).Width = 50
        sheet.Columns(1).Width = 120
        sheet.Columns(2).Width = 80
        sheet.Columns(3).Width = 100
        sheet.Columns(4).Width = 80

        ' 縦方向の揃え位置を中央に設定
        sheet.DefaultStyle.VerticalAlign = VerticalAlign.Middle

        ' 背景色の設定
        For i As Integer = 0 To sheet.RowCount - 1
            If i Mod 5 = 0 Then
                sheet.Rows(i).BackColor = Drawing.Color.Azure
            ElseIf i Mod 5 = 1 Then
                sheet.Rows(i).BackColor = Drawing.Color.Beige
            ElseIf i Mod 5 = 2 Then
                sheet.Rows(i).BackColor = Drawing.Color.LavenderBlush
            ElseIf i Mod 5 = 3 Then
                sheet.Rows(i).BackColor = Drawing.Color.Silver
            ElseIf i Mod 5 = 4 Then
                sheet.Rows(i).BackColor = Drawing.Color.PaleVioletRed
            End If
        Next

        ' フィルタリングの設定
        sheet.AutoFilterMode = FarPoint.Web.Spread.AutoFilterMode.Enhanced
        Dim rowFilter As FarPoint.Web.Spread.IRowFilter = New FarPoint.Web.Spread.HideRowFilter(sheet)

        Dim fd1 As New FarPoint.Web.Spread.FilterColumnDefinition(1, FarPoint.Web.Spread.FilterListBehavior.Default)
        Dim fd2 As New FarPoint.Web.Spread.FilterColumnDefinition(2, FarPoint.Web.Spread.FilterListBehavior.Default)
        Dim fd3 As New FarPoint.Web.Spread.FilterColumnDefinition(3, FarPoint.Web.Spread.FilterListBehavior.Default)
        Dim fd4 As New FarPoint.Web.Spread.FilterColumnDefinition(4, FarPoint.Web.Spread.FilterListBehavior.Default)
        rowFilter.ColumnDefinitions.Add(fd1)
        rowFilter.ColumnDefinitions.Add(fd2)
        rowFilter.ColumnDefinitions.Add(fd3)
        rowFilter.ColumnDefinitions.Add(fd4)

        sheet.RowFilter = rowFilter
    End Sub
End Class
<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true"
  CodeFile="enhancedfilter.aspx.cs" Inherits="filtering_enhancedfilter" %>

<%@ Register Assembly="FarPoint.Web.SpreadJ" Namespace="FarPoint.Web.Spread" TagPrefix="FarPoint" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeaderPlaceHolder1" runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
    <FarPoint:FpSpread ID="FpSpread1" runat="server" BorderColor="#A0A0A0" BorderStyle="Solid"
        BorderWidth="1px">
        <CommandBar BackColor="#F6F6F6" ButtonFaceColor="Control" ButtonHighlightColor="ControlLightLight"
            ButtonShadowColor="ControlDark">
        </CommandBar>
        <Sheets>
            <FarPoint:SheetView SheetName="Sheet1">
            </FarPoint:SheetView>
        </Sheets>
    </FarPoint:FpSpread>
</asp:Content>