| Summary: | XSSF locking of specific features not working | ||
|---|---|---|---|
| Product: | POI | Reporter: | Alex Panayotopoulos <Alex.Panayotopoulos> |
| Component: | XSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | saismriti |
| Priority: | P2 | ||
| Version: | 3.8-dev | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | All | ||
N.B. I have discovered a work-around, and that is to follow the call to s.enableLocking() with a call to s.getCTWorksheet().getSheetProtection(); this yields a CTSheetProtection object. CTSheetProtection is not documented in the official JavaDoc API, but has methods setSelectLockedCells(boolean); setSelectUnlockedCells(boolean); setFormatCells(boolean); setFormatColumns(boolean); setFormatRows(boolean); setInsertColumns(boolean); setInsertRows(boolean); setInsertHyperlinks(boolean); setDeleteColumns(boolean); setDeleteRows(boolean); setSort(boolean); setAutoFilter(boolean); setPivotTables(boolean); setObjects(boolean); setScenarios(boolean); All of which take "true" to enable locking of this aspect, and "false" to disable locking. Hope this comes in handy for anybody who stumbles across this issue! I compared 2 files at xml level. It seems the "lock state" is activated by default for all possible settings:
* sheet.lockDeleteColumns();
* sheet.lockDeleteRows();
* sheet.lockFormatCells();
* sheet.lockFormatColumns();
* sheet.lockFormatRows();
* sheet.lockInsertColumns();
* sheet.lockInsertHyperlinks();
* sheet.lockInsertRows();
* sheet.lockObjects();
* sheet.lockPivotTables();
* sheet.lockScenarios();
* sheet.lockSelectLockedCells();
* sheet.lockSelectUnlockedCells();
* sheet.lockSort();
That means calling "enableLocking()" automatically enables nearly all locks. Setting one of the locks above is quite useless because it already is locked by default. What we need here is an "unlock" option. In the following example nearly everything is locked. The only options allowed are selecting unlocked fields and inserting new rows.
POI export file:
<sheetProtection
sheet="true"
objects="true"
scenarios="true"
deleteColumns="true"
deleteRows="true"
formatColumns="true"
formatRows="true"
insertColumns="true"
insertHyperlinks="true"
pivotTables="true"
selectLockedCells="true"
sort="true"
password=""
/>
Excel export file with same settings:
<sheetProtection
sheet="1"
objects="1"
scenarios="1"
insertRows="0"
selectLockedCells="1"
password=""
/>
Using "true"/"false" instead of "1"/"0" doesn't matter here, means the same and seems to be interpreted both.
In POI export "insertRows" lock has not been set and was therefore assumed to be unlocked but it wasn't, inserting rows wasn't possible.
Manually changing the file by adding insertRows="false" makes the clue. It worked as assumed.
Maybe there are 3 possibilities:
1. developer can set "lock" only (same as current)
--> internally all other settings must be set to "unlock" (false or 0)
2. replace sheet methods "lock..." with "unlock..." because all locks are activated by default, the opposite must be defined
3. developer can set "unlock" for each setting as well as "lock"
--> would be a more intuitive handling as it would lead to something like this in code:
sheet.lockDeleteColumns();
sheet.lockDeleteRows();
sheet.lockFormatCells();
sheet.lockFormatColumns();
sheet.lockFormatRows();
sheet.lockInsertColumns();
sheet.lockInsertHyperlinks();
sheet.unlockInsertRows();
sheet.lockObjects();
sheet.lockPivotTables();
sheet.lockScenarios();
sheet.lockSelectLockedCells();
sheet.unlockSelectUnlockedCells();
sheet.lockSort();
Try this. It worked for me:
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet s = wb.createSheet("Locking");
s.protectSheet("a password");
s.getCTWorkSheet().getSheetProtection().setDeleteColumns(false);
save(wb, "locking.xlsx");
To add an additional wrinkle, it appears that the lockStructure(), lockRevision, and lockWindows methods at the workbook level do not take password parameters although passwords are supported for this functionality within Excel itself. So sheet level password protection is ok as Michael R states, but workbook level password protection is still unsupported as far as I can tell (unless someone smarter than I am has a trick to share). In bug 56076 document protection with password support has been added to XWPF as of 3.11 beta. I'm hoping for workbook level password protection, specifically for lockStructure, in XSSF as well. (In reply to JMason from comment #4) > To add an additional wrinkle, it appears that the lockStructure(), > lockRevision, and lockWindows methods at the workbook level do not take > password parameters although passwords are supported for this functionality > within Excel itself. > > So sheet level password protection is ok as Michael R states, but workbook > level password protection is still unsupported as far as I can tell (unless > someone smarter than I am has a trick to share). > > In bug 56076 document protection with password support has been added to > XWPF as of 3.11 beta. I'm hoping for workbook level password protection, > specifically for lockStructure, in XSSF as well. I should probably state I'm in 3.9 Final |
The XSSFSheet class has methods to lock only specific features on that sheet, such as column or row deletion. However, these don't appear to work in isolation -- either *everything* is locked, or nothing is. If I execute: XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet s = wb.createSheet("Locking"); s.lockDeleteColumns(); s.enableLocking(); wb.lockStructure(); save(wb, "locking.xlsx"); Then I would expect a worksheet that allows all changes except column deletion. However, the result is a completely locked-down worksheet, similar to what I would get with a "protectSheet()" call. If I comment out the "s.enableLocking()" line, then *all* changes are permitted.