ReoGrid Forum

asparatu

Member

Registered: 2014-08-20

Posts: 196

Possible improvement to use with readonly function.

Hello Jing,
    I was thinking of something that might be improvement. I see when you have readonly cells you can not take and change anything in the cells, but you can still select the cells. What i was thinking was to make, so if make them readonly you can not select the cell anymore, infect disabling them. The reason why i was thinking this was because i want to use Reogrid just as output display of information. If you have worksheet that has some editable cells then it would only take and go in those cell and not readonly/disabled cells

just like the readonly have so it based on a range so you can decide which ones you want to disable and have enabled.

So have so you can make readonly still but also option make them so you can disable them too.

Shane

Jingwood

Moderator

From: jing at reogrid.net

Registered: 2014-06-03

Posts: 615

Re: Possible improvement to use with readonly function.

Hi, I can imagine the case that you described. And I think it really should be improvement, let me think about a solution for this.

Idea 1: now we have IsReadOnly property, maybe need another one: IsSelectable. But hard to set this properties for all cells one by one.

Idea 2: add BeforeSelectionRangeChange event, it currently does not exist. If there is a chance to abort the operation of selection range changing, we'll be able to put selection only on the cells that we want to edit.

Idea 3: add worksheet setting to disable entire worksheet range selection operation by mouse, only control the selection by keyboard with the code below.

VB.NET:

' in constructor
AddHandler sheet.BeforeCellKeyDown, Sub(s, e)
    e.IsCancelled = True

    If (e.KeyCode Or Keys.Enter) = Keys.Enter Then
      currentIndex = currentIndex + 1
      If currentIndex >= editableRanges.Count Then currentIndex = 0

      sheet.SelectionRange = editableRanges(currentIndex)
    End If
  End Sub

' class field
Dim editableRanges() = New Object() {New ReoGridRange("A1"), _
  New ReoGridRange("B1"), _
  New ReoGridRange("B2"), _
  New ReoGridRange("A3"), _
  New ReoGridRange("C3")}

Dim currentIndex As Integer = 0

This code makes the selection be only moved between the cells we defined. A1->B1->B2->A3->C3->A1 and loop...

Maybe this code + idea 2 is best.

Last edited by Jingwood (2015-01-22 01:46:03)

asparatu

Member

Registered: 2014-08-20

Posts: 196

Re: Possible improvement to use with readonly function.

Hello Jing,
      I like the suggestion you said with the code and idea 2. For idea 1 could you just use the code for IsReadOnly and modify it work with IsSelectable? Or does not work the same way? That is put the flag to readon? I do not think it can be done in a range but I can see it would be hard to do cell by cell. Or you can use idea 1 and idea 2 together and the code be the bases of the idea 2 and have parma of selectable cells that can be loop through.

Idea 3, I can see problems because the user will want to select the cells with the mouse.

Shane

Jingwood

Moderator

From: jing at reogrid.net

Registered: 2014-06-03

Posts: 615

Re: Possible improvement to use with readonly function.

OK, I'd like to finish this and release the next version within two days. thanks for your suggestions.

Edit: 0.8.8.4 Release day delayed to Jan 26 2015.

Last edited by Jingwood (2015-01-24 17:44:52)

asparatu

Member

Registered: 2014-08-20

Posts: 196

Re: Possible improvement to use with readonly function.

Ok.. Thank you for hearing what i have to suggest.

Last edited by asparatu (2015-01-25 03:42:01)

Jingwood

Moderator

From: jing at reogrid.net

Registered: 2014-06-03

Posts: 615

Re: Possible improvement to use with readonly function.

Sorry for I need more time. (about one day, now writing some demo and new documentation)

Jingwood

Moderator

From: jing at reogrid.net

Registered: 2014-06-03

Posts: 615

Re: Possible improvement to use with readonly function.

The 0.8.8.4 has been released: http://reogrid.net/download

There is a new example 'Customize Selection' in demo project written in C#. Below is a brief of the VB.NET version.

1. Firstly to define a list of valid ranges that we want set to be editable

Class member field:

Dim validRanges As New List(Of String) From {
	"A1:A2", "A5:A8", "G2:G3", "A11:A15", "D11:D15", "A18:G18",
	"A21:G35", "A38:D42", "G36:G41", "F46:G46", "A49:G49"}

This code makes the background of valid ranges filled by yellow:

For Each addr In Me.validRanges

  Me.worksheet.SetRangeStyle(addr, New ReoGridStyleObject With
  { 
    .Flag = PlainStyleFlag.BackColor,
    .BackColor = Color.LightYellow
  })

Next

2. In initialization method, attach the BeforeSelectionRangeChange event

This code makes the selection only be allowed inside valid ranges from the list defined above

AddHandler Me.worksheet.BeforeSelectionRangeChange, Sub(s, ee)

  ee.IsCancelled = Not validRanges.Any(Function(vr As String)
    Dim range = New ReoGridRange(vr)
    Return range.Contains(ee.SelectionStart) AndAlso range.Contains(ee.SelectionEnd)
    End Function)

End Sub

3. Control the selection by keyboard, add this code in initialize method:

AddHandler Me.worksheet.BeforeCellKeyDown, Sub(ss, ee) _
  ee.IsCancelled = Not validRanges.Any(Function(vr As String) New ReoGridRange(vr).Contains(ee.CellPosition))

Please check 'Customize Selection' in demo project for the details of this example.

Last edited by Jingwood (2015-01-27 14:48:37)

asparatu

Member

Registered: 2014-08-20

Posts: 196

Re: Possible improvement to use with readonly function.

Hello Jing,
   Sorry for the late reply..I just read what you posted.. and i will take and add that to my project and tell you how it goes.. Thank you..

Shane

asparatu

Member

Registered: 2014-08-20

Posts: 196

Re: Possible improvement to use with readonly function.

Hello Jing,
    I was trying to use your code and index sample in your demo and i got to work kind of. When i press the tab key, it goes to the next cell but when press it again it stays on that same column and goes to the next row. when should go to the cell before..

To make the editable cells

   For i As Integer = 3 To 32
            validRanges.Add(String.Format("B{0}:B{0}", i))
            validRanges.Add(String.Format("C{0}:C{0}", i))
        Next

does the tab and enter movements

    AddHandler rgDataGatheringControl.BeforeCellKeyDown, Sub(s, e)
                           If (e.KeyCode And Keys.Enter) = Keys.Enter Then
                           Dim index As Integer = Me.validRanges.FindIndex(Function(vr) New ReoGridRange(vr).Contains(e.CellPosition))

                          index += 2
                          If index >= Me.validRanges.Count Then
                              index = 0
                           End If

                          Me.rgDataGatheringControl.SelectionRange = New ReoGridRange(validRanges(index))
                         nonNumberEntered = True

                         ElseIf (e.KeyCode And Keys.Tab) = Keys.Tab Then
                          Dim index As Integer = Me.validRanges.FindIndex(Function(vr) New ReoGridRange(vr).Contains(e.CellPosition))

                         index += 1
                         If index >= Me.validRanges.Count Then
                        index = 0
                        End If
                         Me.rgDataGatheringControl.SelectionRange = New ReoGridRange(validRanges(index))
                        nonNumberEntered = True
                        End If
                 End Sub

Shane

Last edited by asparatu (2015-02-05 12:20:33)

Jingwood

Moderator

From: jing at reogrid.net

Registered: 2014-06-03

Posts: 615

Re: Possible improvement to use with readonly function.

Sorry I've tested your code but I think I haven't fully understand. What you want to do?

asparatu

Member

Registered: 2014-08-20

Posts: 196

Re: Possible improvement to use with readonly function.

Hello Jing,
   What i was do is make work like the SelectionForwardDirection with the get the last cell in the row and making it go down and right. here i will have see what im talking about.

Where the selection box starts

when tab is pressed

when tab is press again..

is support to back the cell before but does not..i keep going down the last column.

Shane

Jingwood

Moderator

From: jing at reogrid.net

Registered: 2014-06-03

Posts: 615

Re: Possible improvement to use with readonly function.

Please try this, the selection moved to next cell when pressing Enter and Tab, move to next row if it reached end. Move to previous cell and previous row when pressing Shift+Enter/Tab.
Cells or ranges outside the yellow region cannot be selected.

Imports unvell.ReoGrid

Public Class Form1
  Dim grid As New ReoGridControl
  Dim sheet As Worksheet

  Private Sub Form1_Load(sender As Object, _e As EventArgs) Handles MyBase.Load
     grid.Dock = DockStyle.Fill
     Me.Controls.Add(grid)

     sheet = grid.CurrentWorksheet

     sheet.SelectionMode = ReoGridSelectionMode.Cell
     sheet.DisableSettings(WorksheetSettings.Behavior_DragToMoveCells)
     sheet.SetRangeStyle(validRange, New ReoGridStyleObject With {
               .Flag = PlainStyleFlag.BackColor,
               .BackColor = Color.LightYellow
     })
     sheet.SelectionRange = New ReoGridRange(validRange.StartPos)

     AddHandler sheet.BeforeSelectionRangeChange, Sub(s, e)
             ' Disable to select any cells outside validRanges
              e.IsCancelled = Not validRange.Contains(e.SelectionStart) _
                  OrElse Not validRange.Contains(e.SelectionEnd)
         End Sub

    AddHandler sheet.BeforeCellKeyDown, Sub(s, e)
             Select Case (e.KeyCode)
                Case Keys.Tab
                   e.IsCancelled = MoveToNextCell()
                Case Keys.Enter
                   e.IsCancelled = MoveToNextCell()

                Case Keys.Shift Or Keys.Tab
                   e.IsCancelled = MoveToPreviousCell()
                Case Keys.Shift Or Keys.Enter
                   e.IsCancelled = MoveToPreviousCell()
             End Select
          End Sub
  End Sub

  Dim validRange As New ReoGridRange("B2:C10")

  Function MoveToNextCell() As Boolean
    If sheet.SelectionRange.Col >= Me.validRange.EndCol _
        AndAlso sheet.SelectionRange.Row < Me.validRange.EndRow Then
        sheet.SelectionRange = New ReoGridRange(sheet.SelectionRange.Row + 1, validRange.Col, 1, 1)
      Return True
    Else
      Return False
    End If
  End Function

  Function MoveToPreviousCell() As Boolean
    If sheet.SelectionRange.Col <= Me.validRange.Col _
        AndAlso sheet.SelectionRange.Row > Me.validRange.Row Then
        sheet.SelectionRange = New ReoGridRange(sheet.SelectionRange.Row - 1, validRange.EndCol, 1, 1)
      Return True
    Else
      Return False
    End If
  End Function

End Class

asparatu

Member

Registered: 2014-08-20

Posts: 196

Re: Possible improvement to use with readonly function.

Hello Jing,
   Thank you that help.. I modified your code a little. Here is the final code, can you check this and tell me if there is better way to do it or is ok. One think I would like to know is how to tell when i am at the last row in a give column or the top row in a give column, so can go to the next or previous column.

 AddHandler rgDataGatheringControl.BeforeCellKeyDown, Sub(s, e)
                                                                 If (e.KeyCode And Keys.Enter) = Keys.Enter Then
                                                                     If e.KeyCode = Keys.Enter Then
                                                                         e.IsCancelled = MoveToNextCell(Keys.Enter)
                                                                         nonNumberEntered = True
                                                                     Else
                                                                         e.IsCancelled = MoveToPreviousCell(Keys.Enter)
                                                                         nonNumberEntered = True
                                                                     End If
                                                                 ElseIf (e.KeyCode And Keys.Tab) = Keys.Tab Then
                                                                     If e.KeyCode = Keys.Tab Then
                                                                         e.IsCancelled = MoveToNextCell(Keys.Tab)
                                                                         nonNumberEntered = True
                                                                     Else
                                                                         e.IsCancelled = MoveToPreviousCell(Keys.Tab)
                                                                         nonNumberEntered = True
                                                                     End If
                                                                 End If
                                                             End Sub

 Function MoveToNextCell(key As System.Windows.Forms.Keys) As Boolean
        'Sets the default value for the method
        MoveToNextCell = False

        'Checks the keys being passed
        With rgDataGatheringControl
            If key = Keys.Enter Then
                Select Case .SelectionRange.Col
                    'Checks to see which current column number
                    Case 1
                        'Just check for end of row only not end of column because this goes down.
                        If .SelectionRange.Row < Me.validRange.EndRow Then
                            .SelectionRange = New ReoGridRange(.SelectionRange.Row + 1, validRange.Col, 1, 1)
                            MoveToNextCell = True
                        Else
                            'Moves to the top of the next column
                            .SelectionRange = New ReoGridRange("C3")
                            MoveToNextCell = True
                        End If
                    Case 2
                        'Only move down in second only
                        If .SelectionRange.Row < Me.validRange.EndRow Then
                            .SelectionRange = New ReoGridRange(.SelectionRange.Row + 1, validRange.Col + 1, 1, 1)
                            MoveToNextCell = True
                        End If
                End Select
            Else
                'Code for the Tab key press
                If .SelectionRange.Col >= Me.validRange.EndCol _
                                        AndAlso .SelectionRange.Row < Me.validRange.EndRow Then
                    .SelectionRange = New ReoGridRange(.SelectionRange.Row + 1, validRange.Col, 1, 1)
                    MoveToNextCell = True
                End If
            End If
        End With
    End Function

    Function MoveToPreviousCell(key As System.Windows.Forms.Keys) As Boolean
        'Sets the default value for the method
        MoveToPreviousCell = False

        'Checks the keys being passed
        With rgDataGatheringControl
            If key = Keys.Enter Then
                Select Case .SelectionRange.Col
                    Case 1
                        If .SelectionRange.Row > Me.validRange.Row Then
                            .SelectionRange = New ReoGridRange(.SelectionRange.Row - 1, validRange.Col, 1, 1)
                            MoveToPreviousCell = True
                        End If
                    Case 2
                        If .SelectionRange.Row > Me.validRange.Row Then
                            .SelectionRange = New ReoGridRange(.SelectionRange.Row - 1, validRange.Col + 1, 1, 1)
                            MoveToPreviousCell = True
                        Else
                            'Moves to the end of the previous column
                            .SelectionRange = New ReoGridRange("B32")
                            MoveToPreviousCell = True
                        End If
                End Select
            Else
                'Code for the tab key press
                If .SelectionRange.Col <= Me.validRange.Col _
                           AndAlso .SelectionRange.Row > Me.validRange.Row Then
                    .SelectionRange = New ReoGridRange(.SelectionRange.Row - 1, validRange.EndCol, 1, 1)
                    MoveToPreviousCell = True
                End If
            End If
        End With
    End Function

asparatu

Member

Registered: 2014-08-20

Posts: 196

Re: Possible improvement to use with readonly function.

Hello Jing,
   I did notice on thing when the code you did and the modified version i did.. When you press the tab or enter buttons it does not end edit. When you press it tab through the form tab order. How can check if you are editing the cell and end edit before it moves to the next cell?

I have tried this:

 AddHandler rgDataGatheringControl.BeforeCellKeyDown, Sub(s, e)
                   'checks to see if the current cell is being edit and cancels it
                    If rgDataGatheringControl.IsEditing Then rgDataGatheringControl.EndEdit(unvell.ReoGrid.EndEditReason.NormalFinish)
                                       
                         If (e.KeyCode And Keys.Enter) = Keys.Enter Then
                             If e.KeyCode = Keys.Enter Then
                                  e.IsCancelled = MoveToNextCell(Keys.Enter)
                                  nonNumberEntered = True
                             Else
                                 e.IsCancelled = MoveToPreviousCell(Keys.Enter)
                                 nonNumberEntered = True
                            End If
                      ElseIf (e.KeyCode And Keys.Tab) = Keys.Tab Then
                         If e.KeyCode = Keys.Tab Then
                               e.IsCancelled = MoveToNextCell(Keys.Tab)
                               nonNumberEntered = True
                           Else
                              e.IsCancelled = MoveToPreviousCell(Keys.Tab)
                               nonNumberEntered = True
                         End If
                       End If
                    End Sub

But this fires after the edit is done not while the cell is being edited. how can fire this while its editing the cell? do i can end the edit when tab or enter press and go to the next cell? can i check in CellEditTextChanging to see if though key are press and end the edit and call the movetonextcell or movetopreviouscell?

Shane

Jingwood

Moderator

From: jing at reogrid.net

Registered: 2014-06-03

Posts: 615

Re: Possible improvement to use with readonly function.

Thanks for this information, tab not handled by control in editing mode is a bug, it will be fixed in next version. Before that please use the following code to resolve this problem. (This is temporary method and should only be used until next version available)

VB.NET:

  Dim sheet = grid.CurrentWorksheet

  Dim textBox As TextBox = Nothing

  ' find built-in input textbox
  For Each c In grid.Controls
    If (TypeOf c Is TextBox) Then
      textBox = c
      Exit For
    End If
  Next

  ' force textbox to handle tab key
  If Not textBox Is Nothing Then
    AddHandler textBox.PreviewKeyDown, Sub(s, arg)
      If (arg.KeyData And Keys.Escape) = Keys.Escape Then
        sheet.EndEdit(EndEditReason.Cancel)
      ElseIf (arg.KeyData And Keys.Tab) = Keys.Tab Then
	arg.IsInputKey = True

        sheet.EndEdit(EndEditReason.NormalFinish)

	If (arg.KeyData And Keys.Shift) = Keys.Shift Then
	  sheet.MoveSelectionBackward()
	Else
	  sheet.MoveSelectionForward()
        End If
      End If
    End Sub
  End If

Jingwood

Moderator

From: jing at reogrid.net

Registered: 2014-06-03

Posts: 615

Re: Possible improvement to use with readonly function.

Finally modified code:

VB.NET:

Dim sheet = grid.CurrentWorksheet

Dim textBox As TextBox = Nothing

' find built-in input textbox
For Each c In grid.Controls
   If (TypeOf c Is TextBox) Then
     textBox = c
   Exit For
 End If
Next

' force textbox to handle tab key
If Not textBox Is Nothing Then
 AddHandler textBox.PreviewKeyDown, Sub(s, arg)
   If (arg.KeyData = Keys.Escape) Then
     sheet.EndEdit(ReoGridEndEditReason.Cancel)

   ElseIf (arg.KeyData = Keys.Tab) Then
     arg.IsInputKey = True

     sheet.EndEdit(ReoGridEndEditReason.NormalFinish)
     sheet.MoveSelectionForward()

   ElseIf (arg.KeyData = (Keys.Tab Or Keys.Shift)) Then
     arg.IsInputKey = True
     sheet.EndEdit(ReoGridEndEditReason.NormalFinish)
     sheet.MoveSelectionBackward()

   End If
 End Sub

End If