Accessing a cell's value from within DefaultSheetDataModel.GetValue

Posted by: mdcarr on 8 September 2017, 2:14 pm EST

  • Posted 8 September 2017, 2:14 pm EST

    <span style="font-size:14pt;">First thank you so very much for your time and expertise.</span>

    <span style="font-size:14pt;">On my spreadsheet I have
    implemented a custom data model by creating a new class and inheriting from
    DefaultSheetDataModel.<span>  </span>I then override
    the function GetValue which gets data from my database.<span>  </span>When I run my application, the spreadsheet is
    “initially” populated and everything appears to work just fine.<span>  </span><o:p></o:p></span>

    <span style="font-size:14pt;">However, after changing a
    single cell’s value to something else, I redisplay the spreadsheet.<span>  </span>The GetValue method is implicitly called and
    the data that is retrieved is from the database thus overwriting the change I
    just made to the cell.<span>  </span><o:p></o:p></span>

    <span style="font-size:14pt;">My first guess at a new algorithm
    is: <span> </span>the first time in the GetValue
    method should retrieve data from the database and every subsequent time, it
    should retrieve data from the spreadsheet.<span> 
    </span>After implementing this algorithm the GetValue method now is trying to
    access the cell’s value, which in turn implicitly calls the GetValue method and
    I immediately run into an infinite loop!?<o:p></o:p></span>

    <span style="font-size:14pt;">What is the proper design to
    solve this kind of problem?<span>  </span>If this is
    the proper design, how do I get the GetValue method to return a value from a
    cell without going into some recursive call?</span>

     <span style="font-size:14pt;">Again, thank you for your time.</span>


    <span style="font-size:14pt;"><o:p> </o:p></span>

  • Replied 8 September 2017, 2:14 pm EST


    Below is an example using a custom data model and overriding GetValue and SetValue Methods, please have a look and let me know if that helps,

    Public Class RowDataModel
        Inherits FarPoint.Win.Spread.Model.DefaultSheetDataModel
        Private ReadOnly m_DataModel As DataTable
        'Empty!! It contains just the structure of the rows
        Private ReadOnly m_Data As List(Of DataRow)
        'This list contains the real data
        Public Sub New(ByVal dataModel As DataTable, ByVal data As List(Of DataRow))
            m_DataModel = dataModel
            m_Data = data
            MyBase.RowCount = m_Data.Count
            MyBase.ColumnCount = m_DataModel.Columns.Count
        End Sub

        Public Overloads Overrides Function GetValue(ByVal row As Integer, ByVal column As Integer) As Object
            If row < 0 OrElse row >= RowCount OrElse column < 0 OrElse column >= ColumnCount OrElse m_Data(row)(column) Is Nothing Then
                Return String.Empty
            End If
            Return m_Data(row)(column)
        End Function

        Public Overloads Overrides Sub SetValue(ByVal row As Integer, ByVal column As Integer, ByVal value As Object)
            If row < 0 OrElse row >= RowCount OrElse column < 0 OrElse column >= ColumnCount Then
                Exit Sub
            End If
            m_Data(row)(column) = value
        End Sub
        Public Overrides Sub SetExpression(ByVal row As Integer, ByVal column As Integer, ByVal value As FarPoint.CalcEngine.Expression)
            MyBase.SetExpression(row, column, value)
            ' you can give the expression here
        End Sub
    End Class

    Could you please provide us some more information on how are you trying to access the value of a cell using GetValue?


  • Replied 8 September 2017, 2:14 pm EST

    First, thank you for replying so quickly.

    Not knowing the proper method to access a cell's data, I simply passed in a reference to the spreadsheet to the DefaultSheetDataModel class.  Then from within MyDataModel I just issued the command:

    Dim oCell As Cell = ss.ActiveSheet.Cells.Item(row, column)

    Dim sCellValue As String = oCell.Text

    I suspect you are about to tell me I shouldn't do it this way.  What is the proper way to access the cell's data from within the DataModel class?

    I am confused by the code you supplied.  Since I previously had set the spreadsheet's DataSource, are you telling me that I also need to peel out the DataTable and DataRow from the DataSet and set those values in the DataModel? 

    So I tried it and here is what I did (This code requires Framework 3.5:  System.Data.DataSetExtensions:

    Dim myDataSet As DataSet = Execute Stored Proc to return a DataSet

    ss.ActiveSheet.DataSource = myDataSet

    Dim myDataTable As DataTable = myDataSet.Tables(0)
    Dim myDataRows As List(Of DataRow) = myDataTable.AsEnumerable.ToList
    moActionsDataModel = New ActionsDataModel(myDataTable, myDataRows)

    Then from within the GetValue method I tried 

    Dim sCellValue As String = m_Data(row)(column).ToString

    This didn't work for me.  It returned values but not the values in the cell.

    Do you happen to have a complete example that initially fills in the spreadsheet with database data, then allows the user to change that data and then saves the data back to the database?  Even if you don't have a complete example, do you have snippets?

    Again, thank you so very much for helping me.

  • Replied 8 September 2017, 2:14 pm EST


    With the datamodel I used the following code for GetValue method, and it works fine, dataModel.GetValue(Row index, Column index);.With custom data mmodel this always depends how the GetValue method is overrriden.In order to have more information on it please have a look at the earlier discussed thread.




Need extra support?

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

Learn More

Forum Channels