| Spread Windows Forms 9.0 Product Documentation > Developer's Guide > Understanding the Underlying Models > Finding More Details on the Sheet Models > Understanding the Data Model |
The data model includes the contents of the cells, including the value or the formula in a cell, and the cell notes or cell tags. This includes the Value properties for cells in the data area of the spreadsheet, the database properties for data-bound spreadsheets, and anything having to do with the contents in the cells.
You are likely to customize the data model when working with Spread. The data model implements more interfaces, and more optional functionality through it, than any of the other models. Also, if you want to implement the equivalent to the unbound virtual model feature of the ActiveX Spread control, for example, you will need to customize the data model.
The following topics provide more information about the data model:
For more details, refer to the BaseSheetDataModel class, the DefaultSheetDataModel class, and the ISheetDataModel interface.
The data model is an object that supplies the cell values being displayed in the sheet. In most cases, you can simply use the default data model that is created when the sheet is created.
The default data model, DefaultSheetDataModel, creates objects to store notes, formulas, tags, and values, and those objects are designed to balance memory usage versus speed based on how big the model is and how sparse the data in the model is. If you are not using notes, formulas, and tags, then the component does not use much memory because the data is fairly sparse. In fact, those objects do not allocate any memory for data until it is actually needed; therefore, as long as there are no notes, formulas, or tags set in the model, memory usage remains low.
The default data model can be used in unbound mode or bound mode. In unbound mode, the data model acts similarly to a two-dimensional array of cell values. In bound mode, the data model wraps the supplied data source and if needed can supply additional settings not available from the data source, for example, cell formulas and unbound rows or columns.
The SetModelDataColumn is different from AddColumn in that you can specify which data field you want bound to which column in the data model.
If you add columns to the model, then they are added to the sheet. The row and column in the GetValue and SetValue methods of the data model have the same indexes as that of the columns in the sheet as long as the sheet is not sorted. If the sheet's rows or columns are sorted, then the view coordinates must be mapped to the model coordinates with these SheetView.GetModelRowFromViewRow and SheetView.GetModelColumnFromViewColumn methods.
The SheetView.GetValue and SheetView.SetValue methods always get and set the data in the data model. Calling these methods is the same as calling SheetView.Models.Data.GetValue and SheetView.Models.Data.SetValue. The Cell.Value property returns the value of the cell in the editor control if the cell is currently in edit mode in a SpreadView containing the SheetView. That value is not updated to the data model until the cell leaves edit mode; however, you can manually update the value in the data model using code:
SheetView.SetValue(row, column, SheetView.Cells(row, column).Value)
When the data model implements IDataSourceSupport and it is bound to a data source, the bound parts of the data model get and set data directly from the data source. Some columns in a bound data model can be unbound if columns are added to the data model with AddColumns after it is bound (IDataSourceSupport.IsColumnBound returns False for those model column indexes), and the values in those unbound columns are stored in the data model rather than the data source.
If the data model also implements IUnboundRowSupport, then some rows in the data model can also be unbound, and those values are also stored in the data model rather than the data source. Such rows can be made into bound rows by calling IUnboundRowSupport.AddRowToDataSource, and if the autoFill parameter is specified as True, then the data in the bound columns in that unbound row will be added to the data source in a new record or element, assuming that the data source permits it (you will get an exception if it does not), and the unbound row becomes a bound row.
The default data model class, DefaultSheetDataModel, implements all of these interfaces, plus many others related to calculation, hierarchy, and serialization.
To see the difference between the default data model and the objects on the sheet, review the following code snippets. These code snippets bind the sheet to a data source called MyData.
FpSpread1.Sheets(0).DataSource = MyData.Tables(0)
and
Dim model As FarPoint.Win.Spread.Model.DefaultSheetDataModel = New FarPoint.Win.Spread.Model.DefaultSheetDataModel(MyData, strTable)
FpSpread1.Sheets(0).Models.Data = model
In the first code snippet, the existing data model is used and resized to the data source; in the second snippet, the data model is replaced with a new one and the old one discarded. The outcome is the same in both examples, but the first example results in the old data model getting garbage collected. Generally you might not want to replace the data model unless you are creating your own data model class. There is generally no need to replace the data model with another DefaultSheetDataModel since there is already one there to use.
If you derive from DefaultSheetDataModel and use that implementation of GetValue and SetValue to store the data, then it will use the Spread implementation of sparse arrays and matrices to balance the memory usage with the access speed. This implementation is designed to make it very fast to create a very large model (that is, 2 billion rows by 2 billion columns) and keep it reasonably fast to get and set values into it, until the number of values gets very large (in which case you will start to run out of memory anyway).
In cases where the model is very large and/or sparse (that is, more than two-thirds empty), access speed is slower (a binary search is required) and memory usage is lower. In cases where the model is not very large (less than 32K rows and/or columns) and not sparse (more than one-third full), then the access speed is faster (no binary search required) and memory usage is higher.
You can run some simple tests by creating a test project with Spread on a form, and setting the ColumnCount and RowCount for the sheet to very large numbers, and you should not see any delay at all because the memory allocated is based on the actual number of data items. If you start to fill the sheet with lots and lots of data, then you will notice delays after a while, especially when memory gets low and the system starts using the page file to swap virtual memory (it will take a very large quantity of data for that to happen though).