Setting a row or cell as being edited

Posted by: sheritt on 8 September 2017, 1:49 pm EST

  • Posted 8 September 2017, 1:49 pm EST

     


    I notice there is no IsDirty in the .Net version of the spread so I thought I would put something in a cells tag to let me know if the row has been edited.


    Could you please provide some sample code in VB.Net that inserts info into a cells tag and another example that retrieves it?


    Thanks

  • Replied 8 September 2017, 1:49 pm EST

    Hi,


    The code snippet for setting & retreiving .Tag property of cell as follows:


    To set the .Tag:


         Dim acell As FarPoint.Win.Spread.Cell
         acell = FpSpread1.ActiveSheet.Cells(0, 0)
         acell.Tag = "MyTag"


    To retrieve the .Tag property


         Dim acell As FarPoint.Win.Spread.Cell
         acell = FpSpread1.ActiveSheet.Cells(0, 0)
         If (acell.Tag <> Nothing) Then
            MessageBox.Show(acell.Tag.ToString())
         End If


    Let me know if this resolves the problem.


    Regards,

  • Replied 8 September 2017, 1:49 pm EST

    Hello,

    I have a big table that needs to be validated cell by cell and row by row according to certain rules... like this:

                        for (int i = 0; i < sheetView.RowCount; i++)
                        {
                            for (int j = 0; j < sheetView.ColumnCount; j++)
                                ValidateCell(i,j);
                           ValidateRow(i);
                        }

    This takes quite a lot of time so I was trying to use the Tag property to mark the cells and rows that have already been checked, so that not all cells and rows need to be validated after every change...

                         for (int i = 0; i < sheetView.RowCount; i++)
                         {

                            for (int j = 0; j < sheetView.ColumnCount; j++)

                                if (sheetView.Cells[i,j].Tag == null)
                                {
                                    ValidateCell(i,j);
                                    sheetView.Cells[i,j].Tag = "Valid";
                                }
                            if (sheetView.Rows[i ].Tag == null)

                            {

                                ValidateRow(i);

                                sheetView.Row[i ].Tag = "Valid";

                            }

                        }

    However, these simple instructions are screwing the performance of my validation function... I don't really know what is going on in the background when I get or set this tags but the loop is taking much more time than before! Specially when the table is validated for the the first time after the data is loaded, because all cell tags are set to Null by default. After that it is not so bad... logically, but I can't afford spending so much time on the first validation.

    What makes this property so slow? What can I do to make it quicker? Is there a better way to do what I want?

    Thanks! Regards

     

  • Replied 8 September 2017, 1:49 pm EST

    Hello,

    I have attached a project that shows the described behaviour in a 100000 x 500 cells sheet.

    In my PC (Inter Core 2 Quad @ 3GHz, 3 GB RAM, Windows XP Pro) it takes less than 1 second to complete the Test 1 (WITHOUT tagging) and more than 30 seconds to complete the Test 2 (WITH tagging).

    Hope this helps.

    Regards


    2009/08/Test.zip
  • Replied 8 September 2017, 1:49 pm EST

    Sorry, I just realized that the project was wrong, so better take this one instead!

    In the previous project the Test 1 was not accessing the content of the cells at all, and of course it was much quicker than Test 2.

    Now both tests 1 and 2 are accessing the cells and Test 2 is also tagging them. However Test 1 is still much quicker.

     Regards

     


    2009/08/Test-2.zip
  • Replied 8 September 2017, 1:49 pm EST

    Juano,

    Do you only want to use CellTag to be notified in case of change in cell value? You may use the LeaveCell event of the spread control to perform the validation by comparing the cell values as follows:

    Private Sub FpSpread1_LeaveCell(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.LeaveCellEventArgs) Handles FpSpread1.LeaveCell
            If FpSpread1.Sheets(0).Cells(e.Row, e.Column).Value <> FpSpread1.Sheets(0).Cells(e.Row, e.Column).Value Then
                MsgBox("Value Mismatch")
            End If
    End Sub

    In addition to this you may also use the Changed event of Spread.Thanks

  • Replied 8 September 2017, 1:49 pm EST

    Hello Suresh,

    I know that. I'm actually using the Changed event to tag the cells that have been changed by the user... although this event is not triggered after several common actions like paste, drag&drop, ... so I also need to handle these actions individually.

    My validation runs in a background thread and I cannot change that. It is started every X time and it must check the values on all cells.

    However, since this process is pretty slow, I'm just looking for a way to tag also the cells that have already been validated, so that they don't need to be checked again in the next validation, unless the user has made any changes again to them.

    Have you executed my attached project? Why is the Tag property making the loop so slow??

    Regards,

    Joan

  • Replied 8 September 2017, 1:49 pm EST

    Joan.

    I ran the sample project provided by you and as you have been using large number of rows/columns, then you may experience some performance issue.After discussing the issue with Scott, it has been found that to improve the performance you should be applying the code to data model rather than the rows/columns directly, this also improves the performance significantly. Here is the revised code snippet:

    m_Spread.SuspendLayout();
    m_Spread.ActiveSheet.AutoUpdateNotes
    = false;
    m_Spread.ActiveSheet.AutoCalculation =
    false;
    FarPoint.Win.Spread.Model.DefaultSheetDataModel dm =
    (FarPoint.Win.Spread.Model.DefaultSheetDataModel)m_Spread.ActiveSheet.Models.Data;
    for
    (int i = 0; i < dm.RowCount; i++)
    {
    for (int j = 0; j <
    dm.ColumnCount; j++)
    if (dm.GetTag(i,j) == null)
    {
    ValidateCell(i,
    j);
    dm.SetTag(i, j, "Valid");
    //m_Spread_Sheet1.Cells[i, j].Tag =
    "Valid";
    }
    if (dm.GetTag(i,-1) ==
    null)
    {
    ValidateRow(i);
    dm.SetTag(i, -1,
    "Valid");
    //m_Spread_Sheet1.RowsIdea.Tag =
    "Valid";
    }
    }
    m_Spread.ActiveSheet.AutoUpdateNotes =
    true;
    m_Spread.ActiveSheet.AutoCalculation =
    true;
    m_Spread.ResumeLayout();

    Hope this helps. Thanks

     

  • Replied 8 September 2017, 1:49 pm EST

    Hello,

    I tried to set the tag for Cells using you code with 1000 rows and 500 columns and didn't encounter the slow speed.

            For i As Integer = 0 To FpSpread1.Sheets(0).RowCount - 1
                For j As Integer = 0 To FpSpread1.Sheets(0).ColumnCount - 1
                    If FpSpread1.Sheets(0).Cells(i, j).Tag = Nothing Then
                        FpSpread1.Sheets(0).Cells(i, j).Tag = "tagged"
                    End If
                Next
            Next

    Could you please post a small zipped project replicating the behavior described by you for us to debug. 

    After setting the tag for a cell, you may use GetCellFromTag(cellToStart, tagToFind) Or GetRowFromTag(rowToStart, tagToFind) to find out the tagged Row or Cell.



    Thanks,

     

Need extra support?

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

Learn More

Forum Channels