ReoGrid Forum

Fast and powerful .NET Spreadsheet Component

You are not logged in.

Announcement

This forum has been archived and no longer accepts new user registrations. Please report your questions, problems, and feedback to the issue page of ReoGrid on GitHub. Thank you for your cooperation.

https://github.com/unvell/ReoGrid/issues

#1 2015-12-30 03:54:44

asparatu
Member
Registered: 2014-08-20
Posts: 196

AfterPaste and BeforePaste Events FlowChart

Hello Jing,
   Can you tell me how the AfterPaste and BeforePaste Events works with Paste Event and where in the cell edit it is at. I have also notice that AfterPaste Event does not fire everytime is the reason for that or is that a bug?

thank you,
Shane

Last edited by asparatu (2015-12-30 03:56:45)

Offline

#2 2015-12-30 05:31:00

Jingwood
Moderator
From: jing at reogrid.net
Registered: 2014-06-03
Posts: 615

Re: AfterPaste and BeforePaste Events FlowChart

When Ctrl+V pressed inside worksheet or Paste method invoked, the BeforePaste will be raised. After the paste operation done, the AfterPaste will be raise.

1. Ctrl+V or Paste method invoked

2. Invoke BeforePaste event

3. If BeforePaste.IsCancelled is True, operation abort.

4. If BeforePaste.IsCancelled is False, do paste.

5. Invoke AfterPaste event

What situation of AfterPaste not be fired? What kind of data in the Clipboard?

Last edited by Jingwood (2015-12-30 05:32:04)

Offline

#3 2015-12-30 07:07:25

asparatu
Member
Registered: 2014-08-20
Posts: 196

Re: AfterPaste and BeforePaste Events FlowChart

Hello Jing,
     All the text information is text or numbers. I have found some text when pasted it will raise the afterpaste event.

if just paste this into the cells it does raise the afterpaste event.
                            'checks the target value is selected
                            If sheet.Cells((e.Cell.Row - 1), e.Cell.Column).DisplayText = "Target" Then
                                'Checks the target length of the cell
                                .TargetValue = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                            Else
                                .TargetValue = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                            End If

afterpaste event

 Private Sub rgInputDataSheet_AfterPaste(sender As Object, e As RangeEventArgs) Handles rgInputDataSheet.AfterPaste
        Dim sheet As Worksheet = DirectCast(sender, Worksheet)

        'checks the values of the better if are correct
        For j As Integer = 1 To e.Range.Cols
            Select Case sheet.Cells(3, j).DisplayText
                Case "High", "high", "Low", "low", "Unknown", "unknown"
                    'sheet.Cells(4, j).IsReadOnly = True
                Case "Target", "target"
                    'sheet.Cells(4, j).IsReadOnly = False
                Case Else
                    sheet.SetCellData(sheet.Cells(3, j).Position.ToAddress, Nothing)
            End Select
        Next

        'checks to see if the numeric value are for double values
        For j As Integer = 1 To e.Range.Cols
            For i As Integer = 4 To sheet.RowCount - 1
                If Not IsNumeric(sheet.Cells(i, j).Data) Then
                    sheet.SetCellData(sheet.Cells(i, j).Position.ToAddress, Nothing)
                End If
            Next
        Next
    End Sub

beforepaste event

Private Sub rgInputDataSheet_BeforePaste(sender As Object, e As BeforeRangeOperationEventArgs) Handles rgInputDataSheet.BeforePaste
        Dim sheet As Worksheet = DirectCast(sender, Worksheet)
        Dim book As ReoGridControl = rgInputDataBook

        Dim data(,) As Object
        Dim range As ReoGridRange

        'WorkSheet Settings
        'Checks to see if the paste is numeric or alpha
        With sheet
            data = RGUtility.ParseTabbedString(Clipboard.GetText)

            range = New ReoGridRange(.SelectionRange.Row, .SelectionRange.Col, data.GetLength(0), data.GetLength(1))

            For i As Integer = 0 To data.GetLength(0) - 1
                For j As Integer = 0 To data.GetLength(1) - 1
                    If Not IsNumeric(data(i, j)) Then
                        If Not (i = 0 And j <= 20) And Not (i = 1 And j <= 20) And Not (i = 2 And j <= 20) Then
                            e.IsCancelled = True
                            data(i, j) = Nothing
                        End If
                    End If
                Next
            Next
        End With

        'WorkBook Settings
        With book
            .DoAction(New SetRangeDataAction(range, data))
        End With
    End Sub

Offline

#4 2015-12-30 12:05:27

Jingwood
Moderator
From: jing at reogrid.net
Registered: 2014-06-03
Posts: 615

Re: AfterPaste and BeforePaste Events FlowChart

Directly paste inside cell when editing mode may not raise the event, in this case it's may necessary to use CellDataChanged event.

Offline

#5 2015-12-30 13:36:32

asparatu
Member
Registered: 2014-08-20
Posts: 196

Re: AfterPaste and BeforePaste Events FlowChart

Hello Jing,
     I do have celldatachanged event also.. sometime afterpaste works with that other code and sometimes it does not..

here is some data that will work.. this raise the afterpaste event every time
Inpedance
z
target
2
193.1
202.7
193.3
190.5
197.1
193.5
81.5
91.9
79.5
90
80.4
84.9


celldatachanged event

 Private Sub rgInputDataSheet_CellDataChanged(sender As Object, e As CellEventArgs) Handles rgInputDataSheet.CellDataChanged
        Dim sheet As Worksheet = DirectCast(sender, Worksheet)

        'Checks to see if the load or creating new row
        If m_blnCreateOnLoadOrAddFlag Then Exit Sub

        Select Case cboTukeyTestSize.SelectedIndex
            Case 0
                With Valid(e.Cell.Column)
                    Select Case e.Cell.Row
                        Case 1
                            .Description = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 2
                            .Unit = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 3
                            'Checks the better if ComboBox
                            If sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length > 0 Then
                                Select Case sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText
                                    Case "High", "high", "Low", "low", "Unknown", "unknown"
                                        .BetterIf = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)

                                        'Sets cell is readonly true
                                        'sheet.Cells(e.Cell.Row, e.Cell.Column).IsReadOnly = True
                                    Case "Target", "target"
                                        'Sets cell is readonly false
                                        'sheet.Cells(e.Cell.Row, e.Cell.Column).IsReadOnly = False

                                        'Sets the true on flag
                                        .BetterIf = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                                End Select
                            Else
                                .BetterIf = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                            End If
                        Case 4
                            'checks the target value is selected
                            If sheet.Cells((e.Cell.Row - 1), e.Cell.Column).DisplayText = "Target" Then
                                'Checks the target length of the cell
                                .TargetValue = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                            Else
                                .TargetValue = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                            End If
                        Case 5
                            .G1 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 6
                            .G2 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 7
                            .G3 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 8
                            .G4 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 9
                            .G5 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 10
                            .G6 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 11
                            .B1 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 12
                            .B2 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 13
                            .B3 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 14
                            .B4 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 15
                            .B5 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 16
                            .B6 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                    End Select

                    'checks to see if the column data is completely filled in
                    If (.Description = True And .Unit = True And .BetterIf = True And .G1 = True And .G2 = True _
                       And .G3 = True And .G4 = True And .G5 = True And .G6 = True And .B1 = True And .B2 = True And .B3 = True _
                                           And .B4 = True And .B5 = True And .B6 = True) Then

                        sheet.Cells(0, e.Cell.Column).Style.BackColor = ColorTranslator.FromWin32(RGB(119, 147, 60))
                        .Valid = True

                        'Validated the target value
                        If sheet.Cells(3, e.Cell.Column).DisplayText = "Target" Or sheet.Cells(3, e.Cell.Column).DisplayText = "target" Then
                            If .TargetValue = True AndAlso sheet.Cells(4, e.Cell.Column).DisplayText.Length > 0 Then
                                sheet.Cells(0, e.Cell.Column).Style.BackColor = ColorTranslator.FromWin32(RGB(119, 147, 60))
                            Else
                                'sets the backcolor to red if not all values are not completely entered
                                sheet.Cells(0, e.Cell.Column).Style.BackColor = ColorTranslator.FromWin32(RGB(192, 80, 77))

                                MessageBox.Show(String.Format("Please Enter a Target Value for {0}",
                                                                            sheet.Cells(0, e.Cell.Column).DisplayText), "Target Value Error")
                                .Valid = False
                            End If
                        End If
                    Else
                        'sets the backcolor to red if not all values are not completely entered
                        sheet.Cells(0, e.Cell.Column).Style.BackColor = ColorTranslator.FromWin32(RGB(192, 80, 77))

                        'Sets the valid data flag to false
                        .Valid = False
                    End If

                    'Sets if the button is enabled or disabled
                    tbbNext.Enabled = ValidationCheck()
                End With
            Case 1
                With Valid(e.Cell.Column)
                    Select Case e.Cell.Row
                        Case 1
                            .Description = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 2
                            .Unit = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 3
                            'Checks the better if ComboBox
                            If sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length > 0 Then
                                Select Case sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText
                                    Case "High", "high", "Low", "low", "Target", "target", "Unknown", "unknown"
                                        .BetterIf = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                                End Select
                            Else
                                 .BetterIf = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                            End If
                        Case 4
                            'checks the target value is selected
                            If sheet.Cells((e.Cell.Row - 1), e.Cell.Column).DisplayText = "Target" Then
                                'Checks the target length of the cell
                                .TargetValue = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                            Else
                                .TargetValue = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                            End If
                        Case 5
                            .G1 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 6
                            .G2 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 7
                            .G3 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 8
                            .G4 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 9
                            .G5 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 10
                            .G6 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 11
                            .G7 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 12
                            .B1 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 13
                            .B2 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 14
                            .B3 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 15
                            .B4 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 16
                            .B5 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 17
                            .B6 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 18
                            .B7 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                    End Select

                    'checks to see if the column data is completely filled in
                    If (.Description = True And .Unit = True And .BetterIf = True And .G1 = True And .G2 = True _
                       And .G3 = True And .G4 = True And .G5 = True And .G6 = True And .G7 = True And .B1 = True And .B2 = True And .B3 = True _
                                           And .B4 = True And .B5 = True And .B6 = True And .B7 = True) Then

                        sheet.Cells(0, e.Cell.Column).Style.BackColor = ColorTranslator.FromWin32(RGB(119, 147, 60))
                        .Valid = True

                        'Validated the target value
                        If sheet.Cells(3, e.Cell.Column).DisplayText = "Target" Then
                            If .TargetValue = True Then
                                sheet.Cells(0, e.Cell.Column).Style.BackColor = ColorTranslator.FromWin32(RGB(119, 147, 60))
                            Else
                                'sets the backcolor to red if not all values are not completely entered
                                sheet.Cells(0, e.Cell.Column).Style.BackColor = ColorTranslator.FromWin32(RGB(192, 80, 77))

                                MessageBox.Show(String.Format("Please Enter a Target Value for {0}",
                                                                            sheet.Cells(0, e.Cell.Column).DisplayText), "Target Value Error")
                                .Valid = False
                            End If
                        End If
                    Else
                        'sets the backcolor to red if not all values are not completely entered
                        sheet.Cells(0, e.Cell.Column).Style.BackColor = ColorTranslator.FromWin32(RGB(192, 80, 77))

                        'Sets the valid data flag to false
                        .Valid = False
                    End If

                    'Sets if the button is enabled or disabled
                    tbbNext.Enabled = ValidationCheck()
                End With
            Case 2
                With Valid(e.Cell.Column)
                    Select Case e.Cell.Row
                        Case 1
                            .Description = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 2
                            .Unit = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 3
                            'Checks the better if ComboBox
                            If sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length > 0 Then
                                Select Case sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText
                                    Case "High", "high", "Low", "low", "Target", "target", "Unknown", "unknown"
                                        .BetterIf = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                                End Select
                            Else
                                .BetterIf = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                            End If
                        Case 4
                            'checks the target value is selected
                            If sheet.Cells((e.Cell.Row - 1), e.Cell.Column).DisplayText = "Target" Then
                                'Checks the target length of the cell
                                .TargetValue = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                            Else
                                .TargetValue = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                            End If
                        Case 5
                            .G1 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 6
                            .G2 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 7
                            .G3 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 8
                            .G4 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 9
                            .G5 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 10
                            .G6 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 11
                            .G7 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 12
                            .G8 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 13
                            .B1 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 14
                            .B2 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 15
                            .B3 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 16
                            .B4 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 17
                            .B5 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 18
                            .B6 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 19
                            .B7 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                        Case 20
                            .B8 = LenghtCheck(sheet.Cells(e.Cell.Row, e.Cell.Column).DisplayText.Length)
                    End Select

                    'checks to see if the column data is completely filled in
                    If (.Description = True And .Unit = True And .BetterIf = True And .G1 = True And .G2 = True _
                       And .G3 = True And .G4 = True And .G5 = True And .G6 = True And .G7 = True And .G8 = True And _
                       .B1 = True And .B2 = True And .B3 = True And .B4 = True And .B5 = True And .B6 = True _
                       And .B7 = True And .B8 = True) Then

                        sheet.Cells(0, e.Cell.Column).Style.BackColor = ColorTranslator.FromWin32(RGB(119, 147, 60))
                        .Valid = True

                        'Validated the target value
                        If sheet.Cells(3, e.Cell.Column).DisplayText = "Target" Then
                            If .TargetValue = True Then
                                sheet.Cells(0, e.Cell.Column).Style.BackColor = ColorTranslator.FromWin32(RGB(119, 147, 60))
                            Else
                                'sets the backcolor to red if not all values are not completely entered
                                sheet.Cells(0, e.Cell.Column).Style.BackColor = ColorTranslator.FromWin32(RGB(192, 80, 77))

                                MessageBox.Show(String.Format("Please Enter a Target Value for {0}",
                                                                            sheet.Cells(0, e.Cell.Column).DisplayText), "Target Value Error")
                                .Valid = False
                            End If
                        End If
                    Else
                        'sets the backcolor to red if not all values are not completely entered
                        sheet.Cells(0, e.Cell.Column).Style.BackColor = ColorTranslator.FromWin32(RGB(192, 80, 77))

                        'Sets the valid data flag to false
                        .Valid = False
                    End If

                    'Sets if the button is enabled or disabled
                    tbbNext.Enabled = ValidationCheck()
                End With
        End Select
    End Sub

Last edited by asparatu (2015-12-31 01:07:00)

Offline

#6 2015-12-31 04:50:43

Jingwood
Moderator
From: jing at reogrid.net
Registered: 2014-06-03
Posts: 615

Re: AfterPaste and BeforePaste Events FlowChart

Do you know that what kind of data used where AfterPaste event won't be invoked?

Last edited by Jingwood (2015-12-31 04:51:48)

Offline

#7 2016-01-01 08:34:44

asparatu
Member
Registered: 2014-08-20
Posts: 196

Re: AfterPaste and BeforePaste Events FlowChart

Hello Jing,
   I will check and tell you.. i think it is data with a period in it. I have notice everything i have copied into that cell had a period at the begging of the the text.. but i will test and tell you.
Shane

Offline

#8 2016-01-02 04:01:57

asparatu
Member
Registered: 2014-08-20
Posts: 196

Re: AfterPaste and BeforePaste Events FlowChart

Hello Jing,
    I found out why it is not raising the afterpaste event.. When you have ' char in from of the string it will strip it and copy the text into the cell.. Beforepaste event worked right but never raised the afterpaste event. I have notice in excel it keeps in the cell but removes from the displaytext.

Shane

Offline

#9 2016-01-03 13:49:28

Jingwood
Moderator
From: jing at reogrid.net
Registered: 2014-06-03
Posts: 615

Re: AfterPaste and BeforePaste Events FlowChart

I have tested text including ['] char that AfterPaste event invoked well, is there any other conditions?

Offline

#10 2016-01-18 01:33:11

asparatu
Member
Registered: 2014-08-20
Posts: 196

Re: AfterPaste and BeforePaste Events FlowChart

Hello Jing,
   Would it matter if the cell is set as combobox? also.. if set the cell to readonly will the combobox still work right so you can type anything into it? also, sorry for the late reply..

Shane

Offline

#11 2016-01-23 02:28:45

Jingwood
Moderator
From: jing at reogrid.net
Registered: 2014-06-03
Posts: 615

Re: AfterPaste and BeforePaste Events FlowChart

By my test both BeforePaste and AfterPaste work with drop-down list cell. Drop-down list cell working with read-only cell is an issue, I'll consider to disable the drop-down list when it inside a read-only cell from next version. Currently using a custom drop-down list cell and overriding the PushDown method will get same result.

sheet["D3"] = new MyDropdownListCell("aaa", "bbb");
sheet.Cells["D3"].IsReadOnly = true;

...

class MyDropdownListCell : DropdownListCell
{
  public MyDropdownListCell(params object[] items) : base(items) { }

  public override void PushDown()
  {
    if (!this.Cell.IsReadOnly)
    {
      base.PushDown();
    }
  }
}

Offline

Board footer

Powered by FluxBB