The data model includes the contents of the cells, which could be the value or the formula in a cell, or the cell notes or cell tags. This includes the unformatted data 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.

Overview

The data model is usually the model most users who create a custom model will want to replace. The data model implements more interfaces, and more optional functionality through them, than any of the other models.

Users who want to implement the equivalent to the unbound virtual model feature of the ActiveX Spread control must create a custom data model.

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 can be used in unbound or bound modes. 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.

The 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 sparsity of the data is high. In fact, those objects do not allocate any memory for data until it is actually needed. As long as there are no notes, formulas, or tags set into the model, memory usage remains low. For more information, see the Performance section below.

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 the SheetView.GetModelRowFromViewRow and SheetView.GetModelColumnFromViewColumn methods.

The SetModelDataColumn is different from AddColumn in that you can specify which data field you want bound to which column in the data model.

Setting Unformatted Data

The SheetView.GetValue and SheetView.SetValue methods always get and set the data in the data model (using 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 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, as shown in the following example.

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

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

Data Binding

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 the AddColumns method after it is bound, and the values in those unbound columns are stored in the data model rather than the data source.

If you add unbound columns using the AddColumns method, IDataSourceSupport.IsColumnBound returns false for those model column indexes.

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. 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). At that point, the unbound row becomes a bound row.

Performance

If you derive from DefaultSheetDataModel and use that implementation of GetValue and SetValue to store the data, then it will use the Spread component’s implementation of sparse arrays and matrices to balance the memory usage with the access speed. This feature 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 probably start to run out of memory).

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. In other words, putting very large amounts of data in the FpSpread component can result in a very large view state and significant delays as the view state is saved and loaded during the page life cycle. To reduce these delays, turn off the SaveViewState property and load the data each time the page loads from the Page_Load event.

You can run some simple tests by creating a test project with a 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 a lot 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 significant amount of data for that to happen).

Summary

The default data model class, DefaultSheetDataModel, implements all of the interfaces discussed in this topic, plus many others related to calculation, hierarchy, and serialization. For more detail, refer to the topics for the classes in the data model provided in the Assembly Reference.

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

FpSpread1.Sheets(0).DataSource = MyData.Tables(0)

and

FarPoint.Web.Spread.Model.DefaultSheetDataModel model = new FarPoint.Web.Spread.Model.DefaultSheetDataModel(MyData, strTable);
FpSpread1.Sheets[0].DataModel = model;

Dim model As FarPoint.Web.Spread.Model.DefaultSheetDataModel = New FarPoint.Web.Spread.Model.DefaultSheetDataModel(MyData, strTable)
FpSpread1.Sheets(0).DataModel = 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 for both code snippets, but the first example results in the old data model being 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.

Copyright © GrapeCity, inc. All rights reserved.