Hello,
We´ve come across an inconsistency in the behavior when deleting rows. Our scenario:
We have a spreadsheet which the user needs to fill. So we want the user to be able to insert, edit and delete data, but we wanted to protect the first row (column headers) from editing and we also want to protect the unused columns to prevent the user from adding columns that we are unable to read. So we decided to protect the worksheet, unprotect all known columns and then protect the first row.
It all works fine now, except when the user wants to delete a (data-) row. When protecting the worksheet, we set the value of allowDeletingRows to true.
There are two possibilities to delete a row:
I created a minimal sample application to test the problem. 5228.SpreadsheetMinimalPrototype.zip
Once the sample application opened, there are two rows. The header of the columns and below the first row of sample-data. The headers are protected and the data rows are editable. Please try the steps above for the data-row.
Which way is the intended way to work for this scenario?
best regards,
Tomas
Hello Tomas,
Thank you for sending the modified sample. I have been looking into it and my suggestion is to add a check for the Copy, Cut and any other command intended to be applied on the allowed region as well. The reason for the message to appear when pasting the entire row is again, that more cells are copied than there is unprotected space for them to be pasted on.
Please, keep in mind that this solution is a workaround and may not work in all scenarios. Additionally, I noticed that the only way to add data to the allowed table region is by manually inserting a row just under an existing data row. Of course, since most of the cells are protected, this may be the required behavior, however, some scenarios would require specific handling. For example, while the current solution would work for copying and pasting an entire row, a logic for copying and pasting a single or a few cells may have to be additionally introduced.
Please, let me know if I may be of any further assistance.
Sincerely,Bozhidara PachilovaAssociate Software Developer
Hello Bozhidara Pachilova,
Thank you for your suggestion, our described delete-row-scenario works fine with your solution.
However we now tried to apply your solution also to the paste-event and it does not work, although the cellrange gets set just fine. Is there something else we need to keep in mind?
Steps to reproduce in the example 7318.SpreadsheetMinimalPrototype.zip:
Result console output as to your logging:
Before modifying default ActiveSelection cell range:
A2:XFD2
Set selected rows: First 2, Last: 2
After modifying default ActiveSelection cell range:
A2:C2
Row pasted!
But there is still the error message that it is trying to change a protected cell.
The adapted sample is attached.
Thanks in advance,
Thank you for the provided sample.
I have been looking into it and after investigation, I determined that this behavior is due to the worksheet’s ActiveSelection’s cell range, which is different in the two scenarios you described.
When deleting a row by right-clicking on the row-index, the ActiveSelection’s CellRange is the entire row, including cells which are protected – i.e. “A2:XFD2”. Therefore attempting to delete the row is interpreted as attempting to delete protected cells.
On the other hand, when deleting a row by selecting any of the editable cells, the ActiveSelection’s CellRange is only that cell, however, the entire row deletion is indeed properly handled by the XamSpreadsheet.
My suggestion in this case is to handle the spreadsheet’s CommandExecuting event, check for the “DeleteRows” command and manually set the proper CellRange, based on the current selection. Since the editable region is known as per your description, the startColumn and endColumn indexes are hardcoded for the purposes of the example:
private void spreadSheet_CommandExecuting(object sender, SpreadsheetCommandExecutingEventArgs e) { if(e.Command == SpreadsheetCommandType.DeleteRows) { int startRow = 0; int endRow = 0; int startColumn = 0; int endColumn = 2; XamSpreadsheet spreadsheet = sender as XamSpreadsheet; int cellRangesCount = spreadsheet.ActiveSelection.CellRanges.Count; startRow = spreadsheet.ActiveSelection.CellRanges[0].FirstRow; endRow = spreadsheet.ActiveSelection.CellRanges[cellRangesCount - 1].LastRow; //... spreadsheet.ActiveSelection.ClearCellRanges(); spreadsheet.ActiveSelection.ReplaceActiveCellRange( new SpreadsheetCellRange(startRow, startColumn, endRow, endColumn), spreadsheet.ActiveCell ); //... } }
I have modified the sample provided by you to use the approach from above. You can find it attached below. Please, test it on your side and let me know how it behaves.
Sincerely,
Bozhidara Pachilova
Associate Software Developer
4426.SpreadsheetSampleModified.zip