Spread Windows Forms 18 Product Documentation

Creating a Custom Sheet Model

You can use a sheet model as a template for a new custom model. For example, consider making a custom data model. Using a custom data model requires creating a class that implements ISheetDataModel, then setting an instance of the class into the SheetView.Models.Data property.

ISheetDataModel is the only interface required, assuming that you do not need any of the optional interfaces. For more information on the other interfaces, refer to Understanding the Optional Interfaces.

!type=note
Note: In BaseSheetDataModel, the Changed event is also implemented.

In a few cases, you might need to create your own custom data model for performance reasons. For example, suppose you want to display a large table of computed values (such as an addition or multiplication table) that consists of a million rows by ten columns. If you used the default sheet data model, you would need to compute and store all ten million values, which would be consume a lot of time and memory. Take a look at the following example.

Example

The following example uses formula to set the sum of row and column indices to their respective cells in a sheet with million rows and 10 columns.

Stopwatch sw = new Stopwatch();
sw.Start();//Begin timing
int ROWCOUNT = 1000000;
int COLCOUNT = 10;
fpSpread1.Sheets[0].RowCount = ROWCOUNT;
fpSpread1.Sheets[0].ColumnCount = COLCOUNT;
for (int r = 0; r < ROWCOUNT; r++)
{
    for (int c = 0; c < COLCOUNT; c++)
    {
        fpSpread1.Sheets[0].Cells[r, c].Value = r + c;
    }
}
sw.Stop();//Stop timing
Console.WriteLine(string.Format("Time elapsed:{0}", sw.Elapsed));

Dim sw As New Stopwatch()
sw.Start() 'Begin timing
Dim ROWCOUNT As Integer = 1000000
Dim COLCOUNT As Integer = 10
FpSpread1.Sheets(0).RowCount = ROWCOUNT
FpSpread1.Sheets(0).ColumnCount = COLCOUNT
For r As Integer = 0 To ROWCOUNT - 1
    For c As Integer = 0 To COLCOUNT - 1
        FpSpread1.Sheets(0).Cells(r, c).Value = r + c
    Next
Next
sw.Stop() 'Stop timing
Console.WriteLine(String.Format("Time elapsed:{0}", sw.Elapsed))

This example creates your own custom data model and sets cell values. As compare to the above method of setting a formula in a cell, it reduces the processing time significantly.

//Custom data model class
class ComputedDataModel : BaseSheetDataModel
{
    public override int RowCount
    {
        get { return 1000000; }
    }
    public override int ColumnCount
    {
        get { return 10; }
    }
    public override object GetValue(int row, int column)
    {
        return row + column;
    }
}
//Write the following in code-behind of a form
Stopwatch sw = new Stopwatch();
sw.Start();//Begin timing
//Set your own custom data model
fpSpread1.Sheets[0].Models.Data = new ComputedDataModel();
sw.Stop();//Stop timing
Console.WriteLine(string.Format("Time elapsed:{0}",sw.Elapsed));

'Custom data model class
Class ComputedDataModel
    Inherits BaseSheetDataModel
    Public Overrides Property RowCount As Integer
        Get
            Return 1000000
        End Get
        Set(value As Integer)
        End Set
    End Property
    Public Overrides Property ColumnCount As Integer
        Get
            Return 10
        End Get
        Set(value As Integer)
        End Set
    End Property
    Public Overrides Function GetValue(row As Integer, column As Integer) As Object
        Return row + column
    End Function
End Class
'Write the following in code-behind of a form
Dim sw As New Stopwatch()
sw.Start() 'Begin timing
'Set your own custom data model
FpSpread1.Sheets(0).Models.Data = New ComputedDataModel()
sw.Stop() 'Stop timing
Console.WriteLine(String.Format("Time elapsed:{0}", sw.Elapsed))

Creating a Custom Sheet Model

Example

See Also