PowerTools SPREAD for Windows Forms 10.0J
SPREADデータソース

SPREAD内のセル範囲を、データソースとしてDataGridコントロールなどのコントロールに連結できます。 次の図は、この処理に関わるオブジェクトを表しています。

相関図

各オブジェクトを表すクラスは以下のとおりです。

クラス 説明
SpreadDataView SPREADデータビュー
SpreadDataRowView SPREADデータビューのデータ行
ISpreadDataViewDataFormatter データモデルとSPREADデータビュー間の、データの転送処理の定義(インタフェース)
ISpreadDataViewMapper SPREADデータビューと、その基のシートとの、座標のマッピング処理の定義(インタフェース)
DefaultSpreadDataViewDataFormatter データモデルとSPREADデータビュー間の、データの転送処理のデフォルト実装
DefaultSpreadDataViewMapper SPREADデータビューと、その基のシートとの、座標のマッピング処理のデフォルト実装

カスタムフォーマッタの作成

ISpreadDataViewDataFormatterインタフェースを実装することで、独自のフォーマッタクラスを作成できます。

設定方法

ISpreadDataViewDataFormatterインタフェースを実装するクラスを作成し、対象のSPREADデータビューの列を割り当てます。

サンプルコード

次のサンプルコードは、SPREADのセル範囲をDataGridViewコントロールに連結します。SPREADのセル値に独自の書式を適用してDataGridViewに表示するよう、カスタムフォーマッターを作成し、設定します。

C#
コードのコピー
//カスタムフォーマッタクラス
public class MySpreadDataViewDataFormatter : ISpreadDataViewDataFormatter
{
    private SpreadDataColumn column;
    private SheetView sheetView;
    public SheetView SheetView
    {
        get { return sheetView; }
        set { sheetView = value; }
    }
    public MySpreadDataViewDataFormatter(SpreadDataColumn ownerColumn, SheetView sheetView)
    {
        if (ownerColumn == null)
        {
            throw new ArgumentNullException("ownerColumn");
        }
        column = ownerColumn;
        this.SheetView = sheetView;
    }
    public object GetCellValue(FarPoint.Win.Spread.Cell cell)
    {
        object ret = null;
        try
        {
            ret = this.SheetView.GetValue(cell.Row.Index, cell.Column.Index);
            ret += ": Customized format";
        }
        catch
        {
            ret = " No value";
        }
        return ret;
    }
    public void SetCellValue(FarPoint.Win.Spread.Cell cell, object value)
    {
        this.SheetView.SetValue(cell.Row.Index, cell.Column.Index, value + ": Customized format");
    }
}
//フォームのコードビハインド
private void Form1_Load(object sender, EventArgs e)
{
    //データソースの作成
    DataTable DT = new DataTable();
    DataSet DSt = new DataSet();
    DT = DSt.Tables.Add("SampleTable");
    DT.Columns.AddRange(new DataColumn[] {
        new DataColumn("ID", System.Type.GetType("System.Int32")),
        new DataColumn("Text", System.Type.GetType("System.String")),
        new DataColumn("Check", System.Type.GetType("System.Boolean"))
    });
    DT.Rows.Add(new object[] { 10, "Web", true });
    DT.Rows.Add(new object[] { 20, "Win", false });
    fpSpread1.DataSource = DSt;
    //SPREADセル範囲をDataGridViewに連結
    SpreadDataView spreadDataView = default(SpreadDataView);
    spreadDataView = new SpreadDataView(fpSpread1, "Sheet1");
    FarPoint.Win.Spread.Data.DefaultSpreadDataViewMapper mapper = new FarPoint.Win.Spread.Data.DefaultSpreadDataViewMapper();
    Type type = default(Type);
    int c = 0;
    int r = 0;
    object value = null;
    for (c = 0; c <= fpSpread1_Sheet1.ColumnCount - 1; c++)
    {
        type = null;
        for (r = 0; r <= fpSpread1.ActiveSheet.RowCount - 1; r++)
        {
            value = fpSpread1.ActiveSheet.GetValue(r, c);
            if (type == null & (value != null))
            {
                type = value.GetType();
            }
            else if ((type != null) && (value != null) && !type.Equals(value.GetType()))
            {
                type = typeof(string);
                break;
            }
        }
        if (type == null) type = typeof(string);
        SpreadDataColumn scol = default(SpreadDataColumn);
        scol = new SpreadDataColumn(fpSpread1.ActiveSheet.GetColumnLabel(0, c), type);
        spreadDataView.Columns.Add(scol);
    }
    mapper.CellRange = new FarPoint.Win.Spread.Model.CellRange(0, 0, 1, fpSpread1_Sheet1.ColumnCount);
    spreadDataView.Mapper = mapper;
    MySpreadDataViewDataFormatter testFormatter = new MySpreadDataViewDataFormatter(spreadDataView.Columns[2], fpSpread1.ActiveSheet);
    spreadDataView.Columns[1].Formatter = testFormatter;
    dataGridView1.DataSource = spreadDataView;
}
Visual Basic
コードのコピー
'カスタムフォーマッタクラス
Public Class MySpreadDataViewDataFormatter
    Implements ISpreadDataViewDataFormatter
    Private column As SpreadDataColumn
    Private m_sheetView As SheetView
    Public Property SheetView() As SheetView
        Get
            Return m_sheetView
        End Get
        Set
            m_sheetView = Value
        End Set
    End Property
    Public Sub New(ownerColumn As SpreadDataColumn, sheetView As SheetView)
        If ownerColumn Is Nothing Then
            Throw New ArgumentNullException("ownerColumn")
        End If
        column = ownerColumn
        Me.SheetView = sheetView
    End Sub
    Public Function GetCellValue(cell As FarPoint.Win.Spread.Cell) As Object Implements ISpreadDataViewDataFormatter.GetCellValue
        Dim ret As Object = Nothing
        Try
            ret = Me.SheetView.GetValue(cell.Row.Index, cell.Column.Index)
            ret += ": Customized format"
        Catch
            ret = " No value"
        End Try
        Return ret
    End Function
    Public Sub SetCellValue(cell As FarPoint.Win.Spread.Cell, value As Object) Implements ISpreadDataViewDataFormatter.SetCellValue
        Me.SheetView.SetValue(cell.Row.Index, cell.Column.Index, value + ": Customized format")
    End Sub
End Class
'フォームのコードビハインド
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'データソースの作成
    Dim DT As New DataTable()
    Dim DSt As New DataSet()
    DT = DSt.Tables.Add("SampleTable")
    DT.Columns.AddRange(New DataColumn() {
                            New DataColumn("ID", System.Type.[GetType]("System.Int32")),
                            New DataColumn("Text", System.Type.[GetType]("System.String")),
                            New DataColumn("Check", System.Type.[GetType]("System.Boolean"))
                        })
    DT.Rows.Add(New Object() {10, "Web", True})
    DT.Rows.Add(New Object() {20, "Win", False})
    FpSpread1.DataSource = DSt
    'SPREADセル範囲をDataGridViewに連結
    Dim spreadDataView As FarPoint.Win.Spread.Data.SpreadDataView = Nothing
    spreadDataView = New FarPoint.Win.Spread.Data.SpreadDataView(FpSpread1, "Sheet1")
    Dim mapper As New FarPoint.Win.Spread.Data.DefaultSpreadDataViewMapper()
    Dim type As Type = Nothing
    Dim c As Integer = 0
    Dim r As Integer = 0
    Dim value As Object = Nothing
    For c = 0 To FpSpread1_Sheet1.ColumnCount - 1
        type = Nothing
        For r = 0 To FpSpread1.ActiveSheet.RowCount - 1
            value = FpSpread1.ActiveSheet.GetValue(r, c)
            If type Is Nothing And (value IsNot Nothing) Then
                type = value.[GetType]()
            ElseIf (type IsNot Nothing) AndAlso (value IsNot Nothing) AndAlso Not type.Equals(value.[GetType]()) Then
                type = GetType(String)
                Exit For
            End If
        Next
        If type Is Nothing Then
            type = GetType(String)
        End If
        Dim scol As FarPoint.Win.Spread.Data.SpreadDataColumn = Nothing
        scol = New FarPoint.Win.Spread.Data.SpreadDataColumn(FpSpread1.ActiveSheet.GetColumnLabel(0, c), type)
        spreadDataView.Columns.Add(scol)
    Next
    mapper.CellRange = New FarPoint.Win.Spread.Model.CellRange(0, 0, 1, FpSpread1_Sheet1.ColumnCount)
    spreadDataView.Mapper = mapper
    Dim testFormatter As New MySpreadDataViewDataFormatter(spreadDataView.Columns(2), FpSpread1.ActiveSheet)
    spreadDataView.Columns(1).Formatter = testFormatter
    DataGridView1.DataSource = spreadDataView
End Sub
関連トピック

 

 


© 2004-2017, GrapeCity inc. All rights reserved.