Cut & Paste from Excel

[Administrator]

When I copy several cells containing eight 9’s “99999999” from Excel and attempt to paste them into one cell setup as a currency cell, CellValueType = System.Single and CurrencyNumberDigits = 6 on the grid I get this error: “An unhandled exception of type System.Exception occurred in system.windows.forms.dll Additional information: Invalid value has been set for the currency.” However if I only copy four rows of eight 9’s and one row of four 9’s totaling 28 9’s in all then the cell paste works fine by truncating the remaining data and I get no errors. I’m attaching an example so we are on the same page here. So what is the question at hand? Is the data exceeding the limits of a “CurrencyCell” set to “System.Single” cell or is this a copy paste issue with Currency Cells or by design, if by design what event cna I use to trap this so it doesn’t crash my program with the above error message? Please note I’m using an, Essential Grid version 2.0.5.1, GridControl with Visual Basic.Net not a DataBoundGrid with C code. Please note the difference when sending me samples. Thank you, JT CutPastFromExcel_8333.zip

[Administrator]

I think the reason that it works when you have some cells with four 9''s is because the error is likely being triggered when the value is being work with for the active cell.

But the basic problem is that a number with 8 significant digits is not a valid System.Single value, and this is triggerring the error message. (ms-help://MS.VSCC/MS.MSDNVS/cpref/html/frlrfSystemSingleClassTopic.htm)

If you want to handle this problem yourself, then you would subscribe to the PasteCellText event, and test the value there, setting e.Cancel = true if you do not want to paste it. This will continue the rest of the paste. If you want to stop all pasting when you get this error condition, also set e.Abort = true. The value about to be pasted is in e.Text.

[Philip Bishop]

Clay,

I was working on this with JT and would like to go in to some more detail on the problem. Take his sample and go to excel and enter nine 9''s in 4 cells in the same row consecutively. Then swipe 3 of the cells and copy them to the first cell in the sample grid. This will paste six 9''s(which is the max for this cell) in this cell. Now if u go back to excel and swipe all for cells and do the same paste in the sample grid this is where it blows up with the error message as he stated in the first post. So here is the question. If you have 3 cells of nine 9''s you have 27 digits total and it copy and pastes fine and truncates it down to the 6 the cell is allowed. If you have 4 cells of nine 9''s then you have a total of 36 digits, which blows up the grid. So what is happening between 27 digits and 36 digits? Whats the difference? Why doesnt it just continue to truncate that one field to the six digits its allowed which was set up in the collection? Hope this helps you see the problem?

Thanks

Phil

[Administrator]

I suspect the difference between 27 digits and 36 digits is the 27 digits in valid for a Decimal type, but 36 digits is not.

A Currency control uses a Decimal type internally to manage the value. so, 27 digits is valid in the control, but 36 digits is not.

[Philip Bishop]

Ok so that makes sense, but it presents a problem with your way of checking it in the pastecelltext event. if activatecurrentcellbehavior is set to selectall and the cell is in active edit mode or if you have the activatecurrentcellbehavior set to doubleclickoncell but ur in the cell in active edit mode u dont go to the pastecelltext event before it blows up. i set a break point in my code in the 2 scenarios i just listed and it never goes to it before it blows up with the error listed in the first post???

Phil

[Administrator]

The reason that PasteCellText is not being hit in this case is that the CurrencyControl in the cell has a context menu with a hotkey for paste. So, the form is sending the ctl+v directly to the currency control, by passing the grid (and its events) entirely.

One way to avoid this is to zap the default context menu with code like this in formload.

Dim c As Control = Me.grdInitCredInv.CellRenderers("Currency").Control
c.ContextMenu = New ContextMenu

[Philip Bishop]

Ok but if i do that then i lose my right click menu to copy and paste. What does the context menu have to do with the pastecelltext event and if i want a context menu how do i go about that without doing what u suggested. Also this only seems to happen in ACTIVE EDIT mode. If ur activatecurrentcellbehavior is doubleclickoncell and ur not in active edit mode on the single cell then it goes to the pastecelltext event fine like you would expect? It only seems to happen as i said whent he cell is being actively edited. This doesnt seem right to me as when u just have the doubleclick on cell on and arent in edit mode it goes there. Is this working right???????

[Administrator]

>>Is this working right???????

I think the key processing is being handled properly.

As far as the exception not being handled, I am not sure about that, and I will forward the problem to the Tools engineer who manages the control to see what should be done if someone tries to paste 36 9''s in a currency control.

Regarding the key processing, when any windows forms control is active, the form will forward any context menu hot key directly to the control. The control''s parent, the grid in this case, has no chance to do anything about it. The control cannot paste 36 digits, so you get an exception.

If you want to handle this now, you will have to do something about the hot key. The solution of zapping the menu was the simplest thing to do.

If you want to keep the menu, then you can try to just remove the hot key. With the hot key gone, the key stroke goes through the normal key processing with gives the grid a chance to handle it, and the grid handling the paste is what allows PasteCellText to be hit.

Dim c As Control = Me.grdInitCredInv.CellRenderers("Currency").Control
c.ContextMenu.MenuItems(4).Shortcut = Shortcut.None

Another solution to this problem is to have your own ContextMenu associated with the cell control. In that case, you could keep the hot key but in your handler for the menu item, you can catch the exception.

[Philip Bishop]

Ok so one last question. Is there any way I can just get the regular context pop up menu that a standard text box uses instead of urs? Like if you right click on a standard text box u dont get the hotkey or shortcut ctrl-v or c or x or whatever even listed. that only shows up on ur context menu. is there a way i can point at this other windows one without making my own, or am i just going to have to loop through and turn them all off with the code you gave me to do menuitem(4 paste). We just didnt like how it looked then with just the ctrl-v missing but the others were there

Phil

[Administrator]

There is no alternative contextmenu for the CurrencyControl provided in our class library.

You can try this, but you should check to make sure all the menu handlers work as expected.

Dim c As Control = Me.grdInitCredInv.CellRenderers("Currency").Control
c.ContextMenu = (New TextBox).ContextMenu

[Philip Bishop]

Is there a way to implent this on a currencytextbox. the code u showed me works great for the grid currency cell but when i try to copy from the grid to the currencytext box it doesnt appear to do anything. what i''m trying to do is to use sendkeys with the ^C or ^ V for copy and paste and in my menu and that works great in the grid and for text boxes but when i try that in a currency text box it doesnt work. pressing ctrl c and v does work however. it doesnt work when it has to go to the menu for some reason. i can get u a sample if u need it

[Administrator]

This not a grid question so I will forward it to the tools team so they can try to respond.

Instead of trying to use sendkeys to do this, you could just call the appropriate method on the control, eg. this.currencyTextBox1.Paste(); for ^V.

[Philip Bishop]

the reason i wasnt using that was...we are trying to have one menu that handles all the control on the form...like if u have a grid and a currency text box, we want one menu item for copy and paste that works for both. if i use the textbox.paste method well then that doesnt work for the grid..and if u use the grid method it doesnt work for the textbox...so we found out that ctrl-c and v works in the menu for the grid and for regular text boxes so we were trying to make it work with ur currency text box. otherwise u have lots of code and have to determine which control is active or has focus to decide which copy and paste statement to use. i sent it on to the tools forum and will see what they have to say. maybe i am making this to hard and there is an easier way

[Philip Bishop]

Upon further testing I noticed in a grid i have that has currency cells and originaltextbox cells that the SendKeys.Send("^C") command doesnt work on a originaltextbox cell either. It works find if u have the send keys in the menu for a currency cell. Is this related to the currencytextbox tool issue i''m having and is it a bug with the originaltextbox also???? Again I wrote above in my last post why we are trying to use the sendkeys method

[Administrator]

In the originaltextbox, the ^C seems to work for me, but the ^V fails if the cell is actively being edited when you call SendKeys. (It seems to work when the cell is not actively being edited). Debugging into the code shows that in this case this call, TextBox.SelectedText = text; fails. Not sure why as it works when you actually type ^V but it does fail with the SendKeys. If you replace this call with some equivalent code, things seem to work using SendKeys ^V. To make this change, you can derive the cell control and add your own override for Paste to handle this problem. If you are interested in doing this I can post the code I got to work. I will forward this issue to the Grid Architect to see if what he thinks about changing this in our code base.

I understand why you want to try to use SendKeys to do this, but you might have more control if you get a reference to the focused control and call the appropriate method on each control instead of calling SendKeys.

[Philip Bishop]

Yes can i take a look at your code

[Administrator]

The reason I was seeing a problem is that I was sending the keys from a button handler. Before sending them, I was setting teh focus to teh grid. When I set the focus to the cell control before sending them, the SendKeys started working OK. So I then put them in a Context menu like you are trying to use them, and they seemed to work OK from there (no focus problems using a context menu) for me. Here is a a little sample. The blue column is the OriginaltextBox, and you can right-click to copy and paste (using SendKeys in the handlers). OrignalTextBoxAndSendKeys_4858.zip

[Philip Bishop]

Clay, I may have confused u somewhere in my last posts. The original problem in this article was because of the context menu which you helped me solve. What i''m encountering now is with an actual main menu up by the title bar, not a context menu. I''ve attached a sample. The first column is an originaltextbox. it only allows 1 character to be typed and through a key press event it must be an x. if u use the edit menu which had the send keys in it, it does nothing. If i use the button on the right that does the gridname.model.cutpaste.copy/paste functions it also does nothing. I''m wondering if it has something to do with the key press event. Thats where I am a little confused. Does this make sense to you because i think what i wrote above was pretty confusing. originaltextbox_6298.zip

[Philip Bishop]

One last thing. After discussing this with others we''ve decided to take the approace you said about using each controls copy and paste method. Thats why I put buttons on the form to show u that I couldnt make it work in the menu with send keys or in the buttons with the control correct copy method.

[Administrator]

The SendKeys is not working because your CurrentCellKeyPress is eating them. Changing the if to this seemed to make things work for me in your sample. If does not handle any key if the ctl key is down.

 If (((Keys.Control And Control.ModifierKeys) <> 0) Or Char.IsLetter(e.KeyChar) And (Asc(e.KeyChar) = 120 Or Asc(e.KeyChar) = 88)) Or (AscW(e.KeyChar) = Keys.Back) Then

As far as the button''s go, it is the embedded textbox control that handles copying and pasting a single cell. Not the grid. So maybe try code like

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim tb As TextBox = Me.grdAGCY_RR.CurrentCell.Renderer.Control
        tb.Focus()
        tb.Copy()
        ''grdAGCY_RR.Model.CutPaste.Copy()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim tb As TextBox = Me.grdAGCY_RR.CurrentCell.Renderer.Control
        tb.Focus()
        tb.Paste()
        '' grdAGCY_RR.Model.CutPaste.Paste()
    End Sub

[Philip Bishop]

Ok so that works and now i''m trying to put this on forms that have activatecurrentcellbehavoir set to doubleclickoncell. u guys have some open issues with this option and the copy and paste doesnt work on doubleclickoncell unless ur actively editing the cell. it works fine then. is this another bug???

[Administrator]

Here is code that worked for me for any value of ActivateCurrentCellBehavior.

 Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click

        Dim editing As Boolean = False
        If Not Me.grdAGCY_RR.CurrentCell.IsEditing _
        And Me.grdAGCY_RR.Selections.Ranges.ActiveRange.Width = 1 _
        And Me.grdAGCY_RR.Selections.Ranges.ActiveRange.Height = 1 Then
            Me.grdAGCY_RR.CurrentCell.BeginEdit()
            Dim tb As TextBox = Me.grdAGCY_RR.CurrentCell.Renderer.Control
            tb.SelectAll()
             editing = True
        End If
        SendKeys.Send("^C")
        If editing Then
            Me.grdAGCY_RR.CurrentCell.EndEdit()
        End If

    End Sub

    Private Sub MenuItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem3.Click

        Dim editing As Boolean = False
        If Not Me.grdAGCY_RR.CurrentCell.IsEditing _
        And Me.grdAGCY_RR.Selections.Ranges.ActiveRange.Width = 1 _
        And Me.grdAGCY_RR.Selections.Ranges.ActiveRange.Height = 1 Then
             Me.grdAGCY_RR.CurrentCell.BeginEdit()
            Dim tb As TextBox = Me.grdAGCY_RR.CurrentCell.Renderer.Control
            tb.SelectAll()
            editing = True
        End If
        SendKeys.Send("^V")
        If editing Then
            Me.grdAGCY_RR.CurrentCell.EndEdit()
        End If

    End Sub

[Philip Bishop]

Ok so i''m going to try ur code out this morning and i see what ur doing and it looks like it should work. My only question is, is this related to the my direct trac issuse 10859. It seems to work without ur code on doubleclickoncell if u select more then one cell to copy and more then one to paste into. if u select 3 and copy and then select 3 and paste it works fine. its only when its one

[Administrator]

I do not think it is the same problem, but until things are fixed, I do not know for sure.

The problem in direct trac is related to ActivateOnDoubleClick not working for CurrencyCells. This problem seems to be the paste is not working when you have a single value to paste into a OriginalTextBox that is not actively editing.