Bug 51483 - XSSF locking of specific features not working
Summary: XSSF locking of specific features not working
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.8-dev
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-07-06 13:52 UTC by Alex Panayotopoulos
Modified: 2014-09-04 22:55 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Panayotopoulos 2011-07-06 13:52:00 UTC
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.
Comment 1 Alex Panayotopoulos 2011-07-07 07:55:14 UTC
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!
Comment 2 Da_Groovy 2014-01-05 21:48:10 UTC
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();
Comment 3 Michael Ramirez 2014-07-23 20:45:54 UTC
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");
Comment 4 JMason 2014-08-29 16:26:53 UTC
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.
Comment 5 JMason 2014-08-29 16:27:40 UTC
(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
Comment 6 Andreas Beeker 2014-09-04 22:55:06 UTC
Patched this with r1622577
Also added some password routines