Flexsheet does not copy/paste formulas correctly (and also inconsistently)

Flexsheet does not copy/paste formulas correctly (and also inconsistently)

Posted by: elaas on 21 February 2019, 3:21 pm EST

• 

  elaas

  • Post Options:
  • Link
    Copy

  Posted 21 February 2019, 3:21 pm EST

  Hello Support,

  I am having issues getting formulas to copy and paste correctly in AngularJS version of FlexSheet. This is aggravated by they fact that I have a very complex grid, of which I cannot share the implementation. I am trying to recreate the behavior in an example, and I am unable to do that as well, though my example code does not behave correctly either. I would like to detail the multiple points of my investigation across a demo created by you, a demo created by me, and the application that I writing. Hopefully you will be able to untangle this mess and help me get the behavior that I want:

  Your Demo: https://demos.wijmo.com/5/angular/FlexSheetExplorer/FlexSheetExplorer/#/
  
  Behavior (desired result): copying a formula and pasting it in another cell adjust cell references, exactly as Microsoft Excel behaves
  
  Angular Version: AngularJS v1.3.15
  
  Wijmo Version: 5.20183.568 (latest)

  My Application:
  
  Behavior: When I copy a cell with a formula, it copies the VALUE, not the formula at all.
  
  Angular Version: The application is developed against version 1.7.2. Due to it’s many dependencies, it is difficult to switch this version for testing, but if it comes to it, I can invest the time to try this.
  
  Wijmo Version: 5.20181.462, but I have also tried this against a CDN of the latest (5.20183.568) - Same behavior.

  My Demo: https://plnkr.co/edit/UDYkuNfTL1k6ntIeLDkB?p=preview
  
  Behavior: When I copy a cell with a formula, it copies EXACT formula, and does not adjust the cell references
  
  Wijmo Version: 5.20183.568 (latest).
  
  Angular Version: I have tried both 1.3.15 and 1.7.7 (both exhibit the same behavior)

  To reiterate, the desired behavior is to have FlexSheet work exactly like Excel, copying the formula, and pasting the relative formula. Your demo works this way, I have not been able to reproduce this. There seems to be absolutely nothing special in your demo, and yet creating a simple flexsheet causes different (though still not the desired) behavior. My Demo copies the formula exactly, whereas the actual application copies the value.

  I cannot share the code implentation in my application, but may be able so share small peices as needed. Can we first try to get my demo to copy relative formulas? Also if you have insight on why my copy/paste in my application is copying values instead (or what things I can check) that would be useful.

  Thank you,

  Eric

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 22 February 2019, 3:33 am EST

  Hi Eric,

  We are able to reproduce the issue at our end. Hence we have forwarded the case to the concerned team for further investigation of the issue with internal tracking ID 368009. We will let you know as we get an update on this.

  Thanks for reporting.

• 

  elaas

  • Post Options:
  • Link
    Copy

  Posted 22 February 2019, 11:24 am EST

  Hi Sharad,

  Thank you for responding and for your investigation. There are multiple symptoms here related to the same issue, and your response did not detail exactly what part you are trying to address.

  I assume that you are investigating why my demo does not paste relative values, but your demo does (please advise if otherwise). If we solve this issue that would be great, but my application is copying values not formulas. There’s a lot going on in the logic of my application’s flexsheet, but nothing I see stands out as something that would convert formulas into values. These symptoms seems like they could easily be separate issues, given that the simple demo behaves differently than the application. If you have insight as to common things to check why this might be happening that would be most helpful.

  And while I have you, I have a request to copy cell references between 2 different FlexSheets. Is this possible? If so is this built-in or would I need to write some code to make this happen?

  Thanks again for your help!

  Eric

• 

  elaas

  • Post Options:
  • Link
    Copy

  Posted 24 February 2019, 3:01 pm EST

  Hi Sharad,

  I have an update for you on my investigations.

  Please refer to my new demo: https://plnkr.co/edit/5dDt7EGG2Re86Jq9Xd2i?p=preview

  1. After placing an additional plain-jane (no binding) AngularJS grid in my application, I was surprised to see that the formulas copied correctly! However, this same markup in an isolated HTML page (like the previous demo) exhibits the previous issue’s behavior (copying exact formulas). This is truly strange. Any update?

  2. I then slowly re-implemented the features in my application’s FlexSheet grid until the symptoms I experienced (copying values from formulas) resurfaced. I found that this was due to a “fix” I had implemented based upon another forum post for a different problem.

     see: https://www.grapecity.com/en/forums/wijmo/remove-row-with-header-tex

     While this (IMO) is not a good answer, it demonstrates the problem. I would like to remove the first row completely (just like the OP in the forum) but retain the column letters (unlike the OP) for the users’ reference when creating formulas. The reason why I say it’s not a good answer is that the proposed solution is to simply hide the first row on the updatedView handler. This means the user’s sheet will start at row 2 instead of row 1. IMO, this is not part of my data, and it should not be there (unless I want it. config setting?). Setting headersVisibility = ‘Row’; will hide the actual column header with column letters, which I want to keep, and continues to display the first row (which I do not want).

     Based upon this example, I had tried to remove the first row in the updatedView handler. While the visible aspects of this approach were successful, it is problematic because (somehow) now the grid copy/pastes values instead of formulas. After commenting the code to remove the first row, formulas copied correctly, however, we still have the first row, showing the property names (or in my application’s case, column index numbers)

     I see two issues here for you to investigate:
     
     1. How can I remove the first row, such that the “column headers” row inside the sheet is gone, but the row numbers start at 1?
     
     2. Why does removing the first row in updatedView cause formula to copy/paste as values?

     A fix for either of these would get me where I need to go.  The first one seems like it should be achievable without using the updatedView handler.  Using the handler seems hacky to me, but what do I know? :)  The second one is curious and I would think you'd want answers for this as the developer of these components, but strictly speaking, I just need it to work.  So if your fix is to use updatedView, but it's performant and we can resolve the side affects, that's fine.
     

  3. While developing the above demo to illustrate our approach to implementing grids (which I use on all FlexGrids; this is my first FlexSheet), I discovered another issue that will be a problem ongoing in our use of FlexSheet. In this example, I use a factory to generate “smart” objects from plain JavaScript data-only objects. This allows us to encapsulate complex logic, abstracting it from the Wijmo grid and AngularJS Controller. This works flawlessly in Flexgrid and I have used it extensively. Flexgrid will bind to properties that are applied using Object.defineProperies() or using a Proxy, using the . Flexsheet will not. In FlexSheet, there seems to be no way to specify columns in the markup. I have developed a Pure JS (see PURE JS grid) version that accomplishes what I want to do in the latest example (Is this the preferred way to do this?), but I would prefer to accomplish this with AngularJS markup. Is this possible?

  4. Lastly, these FlexSheet implementations developed in the example were developed locally, then copy/pasted into Plunker. In my local environment, creating a formula in the grid throws the following error (displayed inside the cell):

  Error: SyntaxError: Invalid regular expression: /[　-〿぀-ゟ゠-ヿ＀-ﾟ一-龯㐀-䶿]/: Range out of order in character class
  

  However, this works fine in Plunker. The code is exactly the same (copy/paste). Can you help me understand why this may be happening? This is happening on all FlexSheets in this example, triggering when I make a formula in a cell.

  So to recap:
  
  1. Why does Flexsheet copy exact formulas instead of relative formulas in some cases? (the previous issue you are now investigating)
  
  2. I need a way to remove the first row auto-generated by FlexSheet that will allow copy/paste of relative formulas (row numbers should start at 1).
  
  3. Why does FlexGrid seem to support , but FlexSheet does not? Is this doable?
  
  4. Why does Plunkr code work, but locally, the exact same code gives a regex error when a formula is supplied in the cell?
  
  5. Bonus: Why does removing a row in the updateView handler cause formulas to copy/paste as values?

  Thank you for your efforts,

  Eric

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 25 February 2019, 3:36 am EST

  Hi Eric,

  Thank you for taking the time to investigate the issue and providing a detailed explanation of your observations. That was really helpful.

  ---

  After placing an additional plain-jane (no binding) AngularJS grid in my application, I was surprised to see that the formulas copied correctly!

  We are unable to replicate the issue. During our test adding grid had no effect on the formulas copying behaviour. They continued to paste incorrectly with/without the presence of pure-js grid. Also, we couldn’t observe this behaviour in the plunk you shared.

  >>1. Why does Flexsheet copy exact formulas instead of relative formulas in some cases? (the previous issue you are now investigating)
  
  The issue is still with the dev team(Id: 368009) and it seems to an issue specific to the builds distributed through the CDN. In our test, builds distributed via npm seems to be working fine.

  2. I need a way to remove the first row auto-generated by FlexSheet that will allow copy/paste of relative formulas (row numbers should start at 1).
  
  As of the now, removing the HeaderRow using the flexsheet.row.removeAt(0) method is the recommended approach. However, we could do so in the initialized and loadedRows event handlers instead of the updatedView handler.

  3. Why does FlexGrid seem to support , but FlexSheet does not? Is this doable?
  
  This is by design, wj-flex-grid-column are supported only inside the wj-flex-grid. Curretly, bound sheets allow to define the columns layout only by passing the flexgrid instance to addBoundSheet()(Api ref: https://demos.wijmo.com/5/Angular/WijmoHelp/WijmoHelp/topic/wijmo.grid.sheet.FlexSheet.Class.html#addBoundSheet) method or by passing the FlexGrid instance to the Sheet’s contructor as in your sample.
  
  We have also added an enhancement request to allow this via markup. Internal tracking Id for the issue is: 368225.

  4. Why does Plunkr code work, but locally, the exact same code gives a regex error when a formula is supplied in the cell?
  
  This seems to an issue specific to the 462 build. Updating to the latest build fixes the issue. Please update to the latest stable build(5.20183.568).

  5. Bonus: Why does removing a row in the updateView handler cause formulas to copy/paste as values?
  
  This is really strange behaviour which needs further investigation. Hence we have escalated it to the concerned team for investigation. Internal tracking id for the case is: 368224.

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 25 February 2019, 4:19 am EST

  I have a request to copy cell references between 2 different FlexSheets. Is this possible? If so is this built-in or would I need to write some code to make this happen?
  
  FlexSheet currently does not support copy/pasting formulas between multiple FlexSheet. Hence we have added an enhancement request to support this with internal tracking id: 368236

• 

  elaas

  • Post Options:
  • Link
    Copy

  Posted 25 February 2019, 3:19 pm EST

  Hi Sharad,

  Thanks for responding to my many questions. In regards to your comments:

  To be more clear, this appears to be an environment specific issue. An isolated demo does exhibit the symptom (copy exact formula), but my application does not. I discovered that my application does not suffer from this by adding a simple test FlexSheet to my application, but its not the addition of the test grid that “fixed” the problem. In my application’s FlexSheet (not the test grid I put into it), I am removing the first row, which causes the formula to copy as a value, not a formula at all, so it was not obvious to me at first that this issue (copy exact formula) was or was not present in my application because it exhibited different symptoms. In the test FlexSheet, I am not removing the first row, and discovered that it did copy relative formulas properly. Sorry for the confusion.

  This behavior seems consistent with why my application did not experience the “copy exact formula” issue on a simple FlexSheet. We do not use CDNs in our application, so I think we will not have this problem in our application. Still it would be good to fix and I appreciate your team’s investigation here.

  That’s fine. It’s good to get confirmation that this is the preferred approach. Here are my findings on the other event handlers that you suggested:

  initialized - same behavior (formula copies as value).

  loadedRows - no effect. First row present, and relative formulas copy as expected.

  I can work with that. Thank you for the confirmation (and the enhancement request). The good thing is, in all of this I have learned how to use bound columns in a FlexSheet. This is the major need, and it seems that it’s possible. The markup would be nice, but not strictly necessary.

  Thank you for the insight. I will note that the Plunker example is using 462 and does not have this problem.

  I have tried to update to the latest version via npm. However, I do not see the angular interop library (i.e. wijmo.angular.min.js). All files seem to refer to Angular2. How can I get the AngularJS tags included?

  Indeed it is. Please keep me posted, and thank you for investigating.

  Thanks for the enhancement request. To be fair, always copying across all FlexSheets may be undesireable in some cases, so if this is to be implemented, it should probaly be configurable. Please add this to the enhancement request.

  Recap:
  
  We have identified many issues, and thank you for your efforts to address them. It seems the only outstanding show-stoppers for me are the following:

  1. The ability to remove the first row, have the row numbers start at 1, and still be able to copy relative formulas.
  2. The RegExp error. Once I upgrade to latest version of Wijmo for AngularJS, this may be fixed, but I can’t find the interop library in the npm download.

  Thanks!

  Eric

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 26 February 2019, 2:32 am EST

  Hi Eric,

  Regarding:

  Here are my findings on the other event handlers that you suggested:
  
  initialized - same behavior (formula copies as value).
  
  loadedRows - no effect. First row present, and relative formulas copy as expected.
  
  We need to use both initialized and loadedRows together, initialized event to remove the HeaderRow for the first time and then loadedRows to remove it after we apply sort/filter on the flexsheet.
  
  Also regarding “initialized - same behavior (formula copies as value).”, we are sorry for the inconvenience, the issue is already escalated to the dev team.

  However, I do not see the angular interop library (i.e. wijmo.angular.min.js). All files seem to refer to Angular2. How can I get the AngularJS tags included?
  
  We are sorry but angular builds are not distributed via npm. You may get the latest build from our website: https://www.grapecity.com/en/wijmo
  
  You may also refer to the following blog post, if you need any referance for setting up wijmo project with webpack+angularjs: https://www.grapecity.com/en/forums/wijmo/angularjs--webpack-setup

  To be fair, always copying across all FlexSheets may be undesireable in some cases, so if this is to be implemented, it should probaly be configurable.
  
  Thanks Eric for your input. We also think that it would make much sense if it is configurable So we have already mentioned in the enhancement.

• 

  elaas

  • Post Options:
  • Link
    Copy

  Posted 4 March 2019, 11:05 am EST

  Hi Sharad,

  Thank you for your continued assistance. I was able to test the resolution of the Regex error on a CDN, and I’m working to have our private mirror updated with the latest Wijmo libraries, so I’m going to assume this problem will be fully resolved (although curiously, this still does not explain why the Plunker using version 462 does work, but a local html file does not).

  I think the only show-stopper right now is the ability remove the first row. I can do so, but as we’ve discussed, this breaks the copy/paste of formulas. I know the Wijmo team is investigating this.

  Please let me know when there are updates.

  Thanks,

  Eric

• 

  elaas

  • Post Options:
  • Link
    Copy

  Posted 11 March 2019, 9:39 am EST

  Hi Sharad,

  Is there an update on this?

  Thank you,

  Eric

• 

  abhishek.dutta

  

  • Post Options:
  • Link
    Copy

  Posted 12 March 2019, 9:41 am EST

  Hi Eric,

  Dev Team has approved the enhancement requests 368225 and 368236 we plan to implement in the V2 Major release.

  I have requested the Dev Team to prioritize the case and they are looking into it at priority. Will update you on this once we have some information from the Dev Team

• 

  elaas

  • Post Options:
  • Link
    Copy

  Posted 4 April 2019, 10:10 am EST

  Hello Abhishek,

  My customers are having to choose between copy/paste formulas and a having the correct row # references. Due to the confusion of having the grid start at row 2 I have opted for option 2. However, not being able to copy/paste formulas properly removes a large part of the value with FlexSheet. They are “power users” of Excel and really need this working.

  It’s been some weeks since your last post. Is there an update on this?

  Thank you for looking into this issue,

  Eric

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 10 April 2019, 9:03 am EST

  Hi Eric,

  Sorry for the late response. The issue #368224(unable to copy/paste on removing header row) is fixed in the latest rc build. You may confirm the same from the following sample which uses the nightly build:
  
  https://stackblitz.com/edit/js-kyjlvl?file=index.js
  
  Fix will be released with the next stable build.

  Regards