Creating a Custom Sheet Model

In This Topic

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.

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.

C#	Copy Code
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));

Copy Code

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));

Visual Basic	Copy Code
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))

Visual Basic

Copy Code

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.

C#	Copy Code
//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));

Copy Code

//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));

Visual Basic	Copy Code
'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))

Visual Basic

Copy Code

'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))