Bug 29350 - Editable Ranges in Protected Sheets
Summary: Editable Ranges in Protected Sheets
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.17-FINAL
Hardware: Other All
: P3 enhancement with 16 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2004-06-02 20:59 UTC by Alex Rojkov
Modified: 2023-11-23 11:39 UTC (History)
1 user (show)

2 Excel files + their Biffviewer dump (35.47 KB, application/octet-stream)
2011-05-11 19:54 UTC, steph

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Rojkov 2004-06-02 20:59:43 UTC
Currently when creating a protected Sheet it's impossible to allow edits on
ranges of cells.
Comment 1 rachid.harradi 2011-03-17 04:59:23 UTC
(In reply to comment #0)
> Currently when creating a protected Sheet it's impossible to allow edits on
> ranges of cells.

did you have solution for this problem? i have the same
Comment 2 Nick Burch 2011-03-17 06:16:40 UTC
Any chance you could produce three closely related files with excel? They'd be:
* Two sheets, with a small number of rows+cells, both sheets un-protected
* Then protect one of the sheets
* Then allow editing of a couple of cells on the protected sheet

Please upload these files to the bug. Then, please try to use BiffViewer (org.apache.poi.hssf.dev.BiffViewer) to spot what Excel did differently between the files. What records were added to allow edits to the range of cells? Was the protection done differently overall?

(Until we know what the file format needs to look like to support this, the feature can't be added)
Comment 3 steph 2011-05-11 19:54:57 UTC
Created attachment 26985 [details]
2 Excel files + their Biffviewer dump

2 excel files identical except that one has a protected sheet including an unprotected range (on the other one everything is unprotected).
The range is named "protectedrange" and exists within the two files.


The biffviewer dump (--out) of those two files.

I hope I got what you were expecting to develop this feature. If not tell me.
Comment 4 Ricardo Mano 2017-10-31 12:03:33 UTC
Is there any update on this subject? Is there any known workaround?
We're trying to have a protected sheet (removing the possibility to remove columns/lines) but with the possibility to edit fields in a given range.
Comment 5 Ricardo Mano 2017-10-31 13:48:10 UTC
Found a workaround using CellStyles

CellStyle cellStyle = wb.createCellStyle();

Cell cell = sheet.createRow(0).createCell(0);


I Hope this helps someone else.
Comment 6 Greg Woolsey 2017-10-31 22:44:11 UTC
I think this example should be the accepted solution, and this issue could be closed as WONTFIX or perhaps INVALID or WORKSFORME.

The resulting XSSF workbook at least is structured the way Excel does it, with the unlock overrides stored at the cell style level.  This applies to row and column cell styles as well.

This could be added as another example, perhaps, as it's not particularly intuitive.