DefaultSheetDataModel vs. BaseSheetDataModel

Posted by: zenrhapsody on 8 September 2017, 12:44 pm EST

  • Posted 8 September 2017, 12:44 pm EST

    Do you have a little more description of the differences between DefaultSheetDataModel vs. BaseSheetDataModel?


    From the reading I've done (and looking through the metadata in Visual Studio), BaseSheetDataModel is enough for what I need.  However, when I use BaseSheetDataModel, I cannot type new values in the cells.  Only DefaultSheetDataModel supports this.


     


    Why is this happening?  What is the benefit of the 'thinner' objects such as BaseSheetDataModel or even the simpler ISheetDataModel interface?


     

  • Replied 8 September 2017, 12:44 pm EST

    Hello,


    I do not think so except for full control over what is implemented and what is not, but I have not found any real benifit to peformance in how the DataModel works.

  • Replied 8 September 2017, 12:44 pm EST

    Thanks for the reply Scott.  That works now.


    Is there any real tangible benefit (performance and/or memory usage) to deriving from BaseSheetDataModel instead of DefaultSheetDataModel ?


     

  • Replied 8 September 2017, 12:44 pm EST

    Hello,


    There are many Interfaces implemented in the DefaultSheetDataModel that are not implemented in the BaseSheetDataModel. That is why it is a larger DataModel. You would need to implement the interfaces yuou need for your DataModel if you were to use the BaseSheetDataModel.


    However, the reason you can not edit in the BaseSheetDataModel is because the IsEditable method in the class always returns False in this DataModel. You would need to override this method and return True for the cells you want to edit.

  • Replied 8 September 2017, 12:44 pm EST

    More questions:


    IF I allow the user to move columns around in a spreadsheet where I've customized the dataModel, how do I know (in my custom data model) that what was once column 10 is now column 5 and that the old columns 5-9 are now columns 6-10 ?


    Keeping the datamodel and axisModel in sync how?


     


     


     

  • Replied 8 September 2017, 12:44 pm EST

    Hello,


    This is the design I got from our source. When the Spread is not bound, we store the data in an internal 'SparseMatrix'. Then, as columns are moved, we move the data in the internal data storage (our SparseMatrix). The DefaultSheetDataModel really has no connection with the SheetView it is parented, so using the GetModelRowFromViewRow method would not work. This is used when you have information about an index on the SheetView that you need to map to a model coordinate for calling methods in the model.

  • Replied 8 September 2017, 12:44 pm EST

    Hello,


    What needs to happen to get this to work is for you to implement the Move method in the IRangeSupport interface and move the columns in the DataStore. The way I did this was by keeping track of where each of the column indices are and querying values from the correct new location.

     
        class ComputedDataModel : DefaultSheetDataModel, IRangeSupport 
    {
    bool mTranspose;
    int[] ColumnIndices;

    public ComputedDataModel(bool transpose)
    : base(DataStore.ROWS, DataStore.ROWS) // keep getting System.IndexOutOfRangeException for any small values like (10,10); not sure why
    {
    mTranspose = transpose;

    }

    public override int RowCount
    {
    get
    {
    if (mTranspose) return DataStore.COLS;
    else return DataStore.ROWS;
    }
    }

    public override int ColumnCount
    {
    get
    {
    if (!mTranspose)
    {
    ColumnIndices = new int[DataStore.COLS];
    for (int i = 0; i < DataStore.COLS; i++)
    ColumnIndicesIdea [I] = i;
    return DataStore.COLS;
    }
    else
    {
    ColumnIndices = new int[DataStore.ROWS];
    for (int i = 0; i < DataStore.ROWS; i++)
    ColumnIndicesIdea [I] = i;
    return DataStore.ROWS;
    }
    }
    }
    public override object GetValue(int row, int column)
    {
    int col = ColumnIndices[column];
    if (mTranspose)
    return DataStore.data[col][row];
    else
    return
    DataStore.data[row][col];
    }


    public override void SetValue(int row, int column, object value)
    {
    int col = ColumnIndices[column];
    int v = Convert.ToInt32(value);
    if (mTranspose)
    DataStore.data[col][row] = v;
    else
    DataStore.data[row][col] = v;
    }

    void IRangeSupport.Move(int fromRow, int fromColumn, int toRow, int toColumn, int rowCount, int columnCount)
    {
    if (fromRow == -1 && toRow == -1)
    {
    if (fromColumn < toColumn)
    {
    for (int i = fromColumn+1; i < toColumn; i++)
    ColumnIndices[i-1] = i;
    ColumnIndices[toColumn-1] = fromColumn;
    }
    if (fromColumn > toColumn)
    {
    for (int i = fromColumn - 2; i >= toColumn; i--)
    ColumnIndices[i+1] = i;
    ColumnIndices[toColumn] = fromColumn-1;
    }
    }
    }
    }
     
  • Replied 8 September 2017, 12:44 pm EST

    Thanks Scott.   I see how that works.


    But now I'm in a quandry.  Since DefaultSheetDataModel already implements IRangeSupport (and you have GetModelRowFromViewRow() method in SheetView) , I had thought that your DefaultSheetDataModel implementation would work for me.  If I had to support IRangeSupport myself, I had thought I should inherit from BaseSheetDataModel instead.. 


    What else will I have to override or be concerned with in DefaultSheetDataModel if I have custom data storage?   Why won't GetModelRowFromViewRow() work? 


    Is this a 'bug' in Spread, or just the way it is designed?


    Our requirements are still in flux, but I think I want to keep most of Spread's basic functionality with the exception of the raw data storage.  I have potential for large data structures which I really don't want to have to duplicate in the spreadsheet, essentially doubling my memory requirement.  So I think DefaultSheetDataModel is where I should start, but I want to keep you from allocating any data storage space.


    I hate to nitpick because your documentation is AWESOME, especially when compared to other .NET control packages, but there is just not enough docs on customizing the data models.


     

  • Replied 8 September 2017, 12:44 pm EST

    Sorry, but this is not working for me. 


    Here is a snippet of the code I have:


    public override object GetValue(int row, int column)


    {


    int col = mSV.GetModelColumnFromViewColumn(column);  // mSV = sheetView


    ...


    }


    There is similar code in overriden setValue() method.


    After I drag / drop a column, all calls to GetModelColumnFromViewColumn still return col == column


     

  • Replied 8 September 2017, 12:44 pm EST

    Hello,


    Could you post a small zipped project reproducing this issue for us to debug the code that is correct to use in your case?

  • Replied 8 September 2017, 12:44 pm EST

    project attached....


     


    Basic customized data model works fine as shown:


    Run project.


    Click 'Load Standard' to load a standard fpSpread component with 250 rows by 10 columns. Cells contain Row+Column


    Click 'Load Custom' to load 2 custom fpSpread components using a ComputedDataModel class for data.  One form is 250 rows x 10 columns.  Other form is 10 rows x 250 columns and is the tranpose of the first form.  Changing cell A5 in 'Form Custom' changes cell E1 in 'Form Custom Tranposed Data' (on cell redraw or clicking the 'Refresh Custom Sheets' on FormStart.


    The data sharing works as desired.


     



    However, I would like users to be able to reorder the columns.  That works fine with "Form Standard" (no custom data model).  It doesn't work on either "Form Custom" or "Form Custom Transposed Data" forms.


     


    Thanks for looking at this.


     


     


    2007/08/SpreadCustomDataModel.zip
  • Replied 8 September 2017, 12:44 pm EST

    Hello,


    You should be able to use the method GetModelRowFromViewRow method to determine the original model location of a column that has been moved to a different location. If you programatically move the column in the model, then all models should sync automatically.

  • Replied 8 September 2017, 12:44 pm EST

    Hello,


    It sounds like since you want full control over how the data is in the spreadsheet, then deriving from the BaseClassDataModel would be the best implementation. However, if it is just for memory usagae, I would conside looking at the DefaultSheetDataModel. The internal SparseMatrix is not being used in your case since you are overriding the GetValue and SetValue methods.There would be a little overhead for some things like calculation support. But this may offset the work that you may have to do to implement the functionality you want in the BaseSheetDataModel.


    It is hard to determine in your case which model is better to derive from. Both model implementations would have its own pros and cons. However, if all you are doing is getting and setting values in the data model, then I would think deriving from the BaseSheetDataModel would be the best model.

  • Replied 8 September 2017, 12:44 pm EST

    Thank you so much for  your time on this issue.  You have been very helpful.


    Now that I have a better understanding of how the DataModels work, I think one more question will do it (let's hope!)


    I want to completely remove usage of your internal 'SparseMatrix'.  My usage will be similar to your old ActiveX product's virtualMode, but without a database attached. 


    So, in your opinion, should I just go ahead and inherit from ISheetDataModel (and whatever other interfaces I should support like IRangeSupport)?   Or, if I inherit from BaseSheetDataModel and override GetValue, SetValue, and IRangeSupport, will the SparseMatrix be essentially empty?  I'm a little leery of DefaultSheetDataModel since it implements IRangeSupport but only for the internal SparseMatrix....


    Thanks again for your time!


     

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels