Spreadsheet partialy read-only - disable insert/delete actions

Hi everybody,

I’m tryig to make some parts of spreadsheet read-only while others editable. Almost completed this task using
custom CellValueHandler and rolling back changes to the cells.

Still insert/delete(Ctrl+V, DELETE) actions are not handled by CellValueHandler. Is there any way to intercept this actions and make the spreadsheet ignore them?

Also, is there any better way to make cells read-only?

Should be possible with something like

sheet.protectSheet(password); // locks everything
CellStyle unlockedCellStyle = workbook.createCellStyle();
unlockedCellStyle.setLocked(false);
cell.setCellStyle(unlockedCellStyle); // unlocks some specific cell(s)

I have tryed different ways and actually I don’t see protectSheet affect the restrictions in any way. Both with your code and with such:

CellStyle style = cell.getCellStyle(); style.setLocked(true); cell.setCellStyle(style); it’s still possible to insert/delete and edit locked cells. I forgot to mention that I’m loading the spreadsheet from xlsx file. May be that’s the reason locks don’t work?

Should work all the same with xlsx files, but now that you mention it I can reproduce the problem with copy-paste to unused cells. Created a
ticket
about it. Can’t reproduce the insert/delete though.

In my case locks doesn’t affect anything: insert, delete, edit are possible on locked cells.
I think it would be great to add spreadsheet.setCellValueManager() method or set CellValueManager in constructor, so it would be possible to handle such actions providing custom CellValueManager implementation.

Currently it seems to be imposible to replace standard handling of delete or copy/paste with custom one.

Hmm, interesting. Have you tried calling refreshAllCellValues() after setting the value, in case it somehow didn’t register otherwise?

Yes, I have tried it before and after the lock and protectSpreadsheet()

Is this problem with one particular file, or does it affect any and all sheets (both new ones and Excel-made imports)? If you can figure out how we could reproduce the issue, and are certain that the problem isn’t in your code (e.g. the spreadsheet or the setting gets overridden before anything is displayed, or that bit of code never ends up visited at all) a new ticket wouldn’t be amiss.

Ok, as far as my project is rather complex, I’d like to post here snippets which you can reproduce.
Firstly, as for protectedSpreadsheet, here is very simple snippet:

formLayout = new VerticalLayout(); formLayout.setImmediate(true); formLayout.setDefaultComponentAlignment(Alignment.TOP_LEFT); Spreadsheet spreadsheet = new Spreadsheet(); spreadsheet.getActiveSheet().protectSheet("ddd"); spreadsheet.refreshAllCellValues(); formLayout.addComponent(spreadsheet); formLayout.setSizeFull(); setCompositionRoot(formLayout); Testing on last Firefox version, Ubuntu - all actions are enabled - edit, delete, ctrl-c,ctrl-v.

I’ve found out that this call works fine:

spreadsheet.setActiveSheetProtected("ddd");

instead of

spreadsheet.getActiveSheet().protectSheet("ddd");

Thanks for the help!

Looks like I used the wrong method name up there in my bit of code from memory, sorry! That setActiveSheetProtected is indeed the correct choice, the getActiveSheet().protectSheet(…) only sets the protection on the POI level, it doesn’t update the Vaadin-specific state attribute for the Spreadsheet, and without that the client-side never receives the info. Glad to hear you got it working despite my slightly incorrect info, though :slight_smile:

Hi Anna, But if we have to make the tab which displays name of the sheet readonly then how can we do that