Bug Report T673232
Visible to All Users

GridControl - A calculated value based on a formula isn't pasted from Excel

created 6 years ago

Hi

I want to copy cells from Excel that contain formula e and be able to paste them into a GridControl.

I don't expect to see the formulae in the GridControl . I want to see the Excel cell Display value in the GridControl after pasting. That is, what the formulae evaluate to.

So, for example, if  the A1 cell in Excel has '=B1' as it's formula and cell B1 has the value "DevExpress". Now I copy cell A1 to the clipboard and paste it into a cell in a GridControl. Nothing shows up. But what I want to show up is "DevExpress".

Is it possible to make GridControl deal with such a scenario?

Many thanks in advance for any help.

Comments (3)

    Hi,

    To help I attach a sample project showing the problem.

    1. Create an Excel worksheet as described above.
    2. Run the attached project
    3. Go to the Excel worksheet and copy cell B1 i.e. the string 'DevExpress'
    4. Click on any row header of the Grid and paste in (CTRL + V)
    5. You will notice the  breakpoints for BOTH theGridControl_PastingFromClipboard

    and

    TableView_ClipboardRowPasting

    and

    TableView_ClipboardRowCellValuePasting

    ALL get hit and the cell correctly receives the pasted value.

    1. Now, return to the Workbook and Copy cell A1, i.e. the cell containing the Formula.
    2. Click on a different row header from step 3 and paste again.
    3. You will notice that the ONLY the breakpoint for

    GridControl_PastingFromClipboard

    is being hit and NO value is pasted.

    Now, in the docs HERE it says we can use the events

    ClipboardRowPasting

    and

    ClipboardRowCellValuePasting

    to modify pasted data.

    In the absence of a existing setting / property that copes with Formulae, these would be an ideal place to query and amend the pasted data but as you have just noticed, NEITHER of these  events are being rasied? It would seem the GridControl is making a decision about what's on the clipboard and cancelling the paste before I can get a chance to examine and amend the clipboard data for myself?

    I can't find any GridControl property that would help, or, as I have explained above, got a workaround for the problem.

    Please, this is a real deal breaker for me and my users. They need the ability to paste into the GridControl from Excel formula cells, but I'm stuck on how this can be achieved?

    I am praying someone at DevExpress can help!

    Many thanks for any help

    Kind Regards,
    Pete.

    Andrew Ser (DevExpress Support) 6 years ago

      Hello Pete,
      Thank you for bringing this to our attention. I reproduced the issue using your steps and passed it to our R&D team for further research. We will come back to this ticket once we make progress. Please stay tuned.

        Hi Andrew

        Thanks for your reply.

        I look forward to hearing from you.

        Pete.

        Answers approved by DevExpress Support

        created 6 years ago (modified 6 years ago)

        We have fixed the issue described in this ticket and will include the fix in our next maintenance update. To apply this solution before the official update, request a hotfix by clicking the corresponding link for product versions you require.

        Note: Hotfixes may be unavailable for beta versions and updates that are about to be released.

        Additional information:

        Our Excel clipboard engine doesn't evaluate formulas to calculate cell values. Modifying our current Excel parser so that it can read the result of calculated values will dramatically affect performance. Thus, we provide an alternative approach to support your scenario with this fix.
        Internally, we use different clipboard engines and the Excel one is used first. In the hotfix, we added a way to change the priority for clipboard engines. You can use the CSV one first by executing the following line in the beginning of your app:

        C#
        ClipboardSourceFactory<ColumnWrapper, RowBaseWrapper>.SourcePriority = new ClipboardSourceType[] { ClipboardSourceType.Text, ClipboardSourceType.Excel, ClipboardSourceType.XMLSpreadsheet, ClipboardSourceType.UnicodeText, ClipboardSourceType.Csv };

        This way, the calculated values will be copied as is.
        Changing the priority out of the box at our controls' level may lead to unpredictable consequences in a general case. However, it should work correctly in the scenario you demonstrated. We tested it and found no problems.

        [UPDATED: Code snippet,  Oct-9, 2018]

          Show previous comments (13)
          Andrew Ser (DevExpress Support) 6 years ago

            Hi,
            We've updated our code base and now it is possible to use the SourcePriority property for the ClipboardSourceFactory class without specifying generic types. Both approaches are equal.

              Hi Andrew,

              This is good news, so thanks.

              However,

              1. Is there meant to be a newer link to the updated code base? How do I know the link above still doesn't point to the previous version?

              2) Can you tell me what the version of new code base is, so I know I've got the right version.

              1. Does the non-generic parameter version of the code INCLUDE or EXCLUDE the changes made in the Ticket HERE that deals with the CSV problem above?

              Overall this is a bit confusing, so some clarity on where to get the most up to date version would be appreciated.

              Basically, is there one version I can now download which hopefully fixes all issues in this ticket and in T679611 and what is its version number please?

              Thanks.

              Andrew Ser (DevExpress Support) 6 years ago

                Hi Pete,
                I've removed the old link and requested a new hotfix once again. It will be published automatically once it is ready.
                Our builds are accumulative, which means that a build includes all fixes made before it. The hotfix build that will be published here will contain the fix we made in the scope of Grid - Inserting clipboard data copied from Excel clears values in a grid row under the target row.
                I cannot tell the precise version number in advance because it depends on the time when the hotfix build is published. So, wait until the current hotfix is prepared and install it to fix all the issues we were discussing here and in related threads.

                >> Does the non-generic parameter version of the code INCLUDE or EXCLUDE the changes made in the Ticket  HERE that deals with the CSV problem above?
                The issue in that ticket is resolved for the CSV parser in general. It doesn't matter which version of the ClipboardSourceFactory class you are using.

                Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

                Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.