The data model includes the contents of the cells, whether it is the value or the formula in a cell, or 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.

The data model is very likely the model which you would customize 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 FarPoint Spread control, for example, customizing the data model is the solution.

To understand the data model, we provide these topics:

• Data Model Object
• Setting and Adding to the Data Model
• Implemented Interfaces
• Balance of Speed and Performance

For more details, refer to the BaseSheetDataModel class, the DefaultSheetDataModel class, and the ISheetDataModel interface.

Return to the overview of Finding More Details on the Sheet Models.

Data Model Object

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 not much memory is used since the data is fairly sparse. In fact, those objects do not allocate any memory for data until it is actually needed, so as long as there are no notes, formulas, or tags set into 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 much like a two-dimensional array of cell values. In bound mode, the data model wraps the supplied DataSource and if needed can supply additional settings not available from the DataSource, for example, cell formulas, and unbound rows or columns.

Setting and Adding to the Data Model

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. (It 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. But you can manually update the value to the data model from your code:

 SheetView.SetValue(row, column, SheetView.Cells(row, column).Value)

Implemented Interfaces

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, look at these 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 either way, but the first way results in the old data model getting garbage collected. Generally you may 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.

Balance of Speed and Performance

If you derive from DefaultSheetDataModel and use that implementation of GetValue and SetValue to store the data, then it will use our implementation of sparse arrays and matrices to balance the memory usage with the access speed. It 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 then 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 lots and lots of data for that to happen though).