Prevent cellSelection for protected/locked cells + scrolling issue

[Remy]

Dear Syncfusion Team,

I am trying to do a couple of things :

1. Manage selection for protected cells

I would like to find a way to prevent a user from being able to select a locked cell in a spreadsheet. Right now, the user can select a cell even though it's locked (either by single-clicking the cell in question or navigating to it via the keyboard). If he double-clicks the cell to edit its content, he is then presented with a message saying that the cell is locked. Is there a way to prevent the user from even selecting the cell in the first place?

If the cell A2 is locked and that the currently selected cell is A1 (assuming A1 is an unlocked cell), I don't want the user to be able to use the keyboard's right arrow to navigate to A2. Instead, it should jump to the cell A3 (assuming it's an unlocked cell). The same goes for using the mouse. If the user clicks on A2, it should do nothing and keep A1 selected.

If the selected cell is the last unprotected cell of the row, using the keyboard to navigate further on the row should be prohibited. Instead, if the last unprotected cell of a row is selected, clicking the right key on the keyboard should select the first unprotected cell of the next row containing an unprotected cell.

If the selected cell is on the last row containing an unprotected cell on the spreadsheet, pressing the right or bottom keys on the keyboard should select the first unprotected cell found at the top of the spreadsheet.

Here is an example of the desired behaviour (simulated in MS Excel) :

2. Fix a scrolling issue

I am experiencing an issue where when scrolling is disabled, I can still manually scroll using the keyboard arrows. If I go past the visible section of the spreadsheet using the keyboard, the page then scrolls down. The problem is that when I use the keyboard to scroll back up, the page keeps scrolling down. Here is a preview :

3. showSheetTabs + [showHeaders] breaks the spreadsheet

In the latest version of the spreadsheet component, a combination of [showSheetTabs] and [showHeaders] parameters values triggers different errors. With different combinations, different errors occur. Somehow the [showSheetTabs] parameter breaks the lines of code involving changing the row height programatically and the [showHeaders] parameters seems to break the cell editing functionality (either by double-clicking the cell or starting to type on the keyboard). Is this a known issue? Here is a description of the different combinations :

[showHeaders] = true     +     [showSheetTabs] = true     =      Behaviour is OK, but not the styling desired (I want to hide these)

[showHeaders] = true     +     [showSheetTabs] = false     =      Error when trying to edit cell content

[showHeaders] = false     +     [showSheetTabs] = true     =      Error on line(s) using .setRowHeight() and .setColumnWidth() on spreadsheet created() event

[showHeaders] = false     +     [showSheetTabs] = false     =      Both errors described above are triggered

Here is a stackblitz I created to reproduce all of these issues :

https://stackblitz.com/edit/angular-jhtxqp?file=app.component.ts

Any help would be greatly appreciated.

Thank you

Remy

[Sangeetha Priya Murugan]

Hi Remy, 

 

Thank you for contacting Syncfusion support. 

 

1. Manage selection for protected cells 

 

We have checked your reported requirement and it can be achievable in our spreadsheet by using the beforeSelect event as like as below. 

 

  beforeSelect(args: BeforeSelectEventArgs) { 

    var range = getRangeIndexes(args.range); // pass the address 

    var sheet = this.spreadsheetObj.getActiveSheet(); 

    var cell = getCell(range[0], range[1], sheet); 

    if (sheet.isProtected) args.cancel = true; // to prevent the selection for locked cell 

    if (cell && cell.isLocked == false) args.cancel = false; // to select the unlocked cells 

  } 

 

  <ejs-spreadsheet #spreadsheet   

(beforeSelect)="beforeSelect($event)"> 

  </ejs-spreadsheet> 

 

2. Fix a scrolling issue 

 

We have checked your reported issue and we would like to let you know that you have enabled the virtualization in spreadsheet. While navigating, the cells are created automatically and it is not an issue. So, we would suggest you to set the isFinite as true as like as below. 

 

    scrollSettings = { isFinite: true }; //  to set the finite scrolling in spreadsheet 

 

  <ejs-spreadsheet #spreadsheet  

    [scrollSettings]="scrollSettings"> 

  </ejs-spreadsheet> 

 

For your convenience, we have modified your sample based on our above suggestions. Please find the link below. 

 

Sample Link: https://stackblitz.com/edit/angular-jhtxqp-tsvp2b?file=app.component.html 

 

3. showSheetTabs + [showHeaders] breaks the spreadsheet 

 

We have checked your reported issue, and we can reproduce your reported issue in our end. We will include the fix for this issue in our upcoming patch release, which will be scheduled on 12th January 2021, until then we appreciate your patience. You can track the status of this improvement by using below link.  

Feedback link: https://www.syncfusion.com/feedback/21154/spreadsheet-showsheettabs-property-with-false-throws-an-error-while-editing  

Could you please check the above details and get back to us, if you need any further assistance on this. 

 

Regards, 

Sangeetha M 

[Remy]

Hi Sangeetha,

Thank you very much for your support. Your reply was of great support. However I still have two questions :

1. Cummulative count of cells selected outside the unlocked section

When arriving at a boundary of an unprotected section of the spreadsheet, the selection no longer expands to the locked cells as it was previously doing when navigating with the keyboard navigation keys, which is the expected behaviour (thanks to your reply!!). However, if we keep the supplied stackblitz as the example, If the cell A6 is selected and that I navigate upwards using the "up" key on the keyboard, I expect that no matter how many times I click the "up" key, once I press the "down" key, it would navigate to the cell A7. However, instead it keeps a count of the "up" key presses until I reach a boundary of the spreadsheet without explicitly showing the cells as being selected. So if I am on cell A6 and I press 3 times on the "up" key, I have to press 4 times on the "down" key to get to cell A7. Following the same logic, I expect that if the cell D6 is selected, if I press on the "up" key once, it jumps to cell D3 directly and that pressing that same "up" key once more jumps to the cell D1. Instead, at the moment, I have to press 3 times on the "up" key to get from D6 to D3, and 2 more times to get to D1. Is there a way around this?

2. Still able to select and edit/erase locked cells' content

Using the mouse, with the supplied code I am still able to select portions of the spreadsheet that contain locked cells. I wouldn't want the user to be able to do so for the UX. Furthermore, pressing the "delete" key on the keyboard erases the content of the selection even if it contains locked cells. Is this a bug? Is there a way to bypass both these behaviours? Here is an animation demonstrating the issue :

Sample Link : https://stackblitz.com/edit/angular-jhtxqp-tsvp2b?file=app.component.html

Thank you again for your support,

Remy

[Sangeetha Priya Murugan]

HI Remy, 

 

Thank you for your update. 

 

1. Cummulative count of cells selected outside the unlocked section 

 

We have checked your reported requirement and we would like to let you know that currently we don’t have support in spreadsheet. However, we have considered this as a feature  and you can track the status of this feature using below link from our feedback portal,  

 

Feedback Portal Link: https://www.syncfusion.com/feedback/21308/need-to-skip-the-selection-for-locked-cells  

 

2. Still able to select and edit/erase locked cells' content 

 

We have checked your reported issue and we confirmed this as defect and the fix will be available in immediate patch release after our Essential Studio Volume 4 SP1 release, we appreciate your patience until then. You can track the status of this defect using below link from our feedback portal,  

 

Feedback Portal Link: https://www.syncfusion.com/feedback/21279/need-to-fix-the-locked-cell-value-getting-erased-while-using-delete-key  

 

Could you please check the above details and get back to us, if you need any further assistance 

 

Regards, 

Sangeetha M 

[Remy]

Dear Syncfusion Team,

The issue stated at #2 in your reply (deleting protected cells) seems to still be an issue, even with the latest version of the Syncfusion Spreadsheet component as of the time of this writing (18.4.41).

Even though it is stated in the release note that it was fixed, it doesn't seem like it :

Feedback Portal Link : https://www.syncfusion.com/feedback/21279/need-to-fix-the-locked-cell-value-getting-erased-while-using-delete-key

Here is a sample demonstrating the issue :

https://stackblitz.com/edit/angular-jhtxqp?file=app.component.ts

Thank you,

Remy

[Sangeetha Priya Murugan]

Hi Remy, 

 

Thank you for your update. 

 

We have already fixed your reported issue in our latest patch release (18.4.42). So, we would suggest you to use the latest version to resolve this issue in your end. 

 

https://www.npmjs.com/package/@syncfusion/ej2-spreadsheet  

 

Regards, 

Sangeetha M 

[Remy]

Hi Sangeetha,

I updated the spreadsheet component to the latest version (18.4.42 as of today) and the issue still occurs (in my personal project AND on Stackblitz). When selecting multiple cells (locked and/or unlocked) and pressing the Delete key, everything gets erased (even some of the borders...). This definitely shouldn't be happening.

Here is a Stackblitz example :

https://stackblitz.com/edit/angular-jhtxqp-nmet5b?file=app.component.ts

To avoid this, ideally we shouldn't be able to select locked cells at all (or at least offered the option programatically via the API), but I understand this is a feature you are working on. Is there a way around this in the meantime? Perhaps checking for the locked status for each cell in the selection and only clear the contents of the ones that are unlocked when pressing the Delete key? And what about the border? Why does it get erased?

Thank you,

Remy

[Sangeetha Priya Murugan]

Hi Remy, 

 

Thank you for your update. 

 

We have checked your reported issue and we would like to let you know that we have checked and fixed this issue in windows machine. And the functionality works properly in our end, so please share your machine configuration details. Based on that we will check and provide you a better solution quickly. 

 

Regards, 

Sangeetha M  

[Remy]

Hi Sangeetha,

The computer I am using is a 2017 Macbook Pro running macOS Catalina (10.15.4). Tested on Chrome 88.0.4324.96 and Safari Version 13.1 (15609.1.20.111.8) and confirm that the issue exists for both. However, I tried running it on a Windows PC and still face the same issue. Tried on a Dell Latitude 5289 running Windows 10 (Chrome 88.0.4324.146 (Official Build) (64-bit)). Here is a demonstration :

Here is the Stackblitz sample :

https://stackblitz.com/edit/angular-jhtxqp-nmet5b?file=app.component.ts

Thank you,

Remy

[Sangeetha Priya Murugan]

Hi Remy, 

 

Sorry for the inconvenience. 

 

We are able to replicate this issue in our based on the provided selection scenario (A6, A9, A11 and then scroll to top in right direction). And we have planned to include this fix in our February 17^th patch release.  We kindly appreciate your patience until then. 

 

Regards, 

Sangeetha M