You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Alex Panayotopoulos <Al...@vianet.co.uk> on 2011/07/05 17:32:26 UTC

XSSF Locking

Hi all,

Can anybody get the XSSF sheet locking functionality working? I've been
testing it, but so far it looks like 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. How
do I disable *just* column deletion?

N.B. I did find one other thread on this list about this problem, but
the example code did not solve the problem. I also searched for open
issues in Bugzilla, but found none. Should I open a new bug?

Thanks,

Alex Panayotopoulos, Software Engineer
ViaTelemetry Limited

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

RE: XSSF Locking

Posted by Alex Panayotopoulos <Al...@vianet.co.uk>.
Hi Amol,

Thanks for that. I actually stumbled across the same methods myself just before you sent this reply!
I have created a Bugzilla bug for the problem at https://issues.apache.org/bugzilla/show_bug.cgi?id=51483

One quibble: the methods shouldn't pass 'false', as that would actually *unlock* the functionality -- confusingly, "setInsertColumns(false)" enables column insertion, and "setInsertColumns(true)" disables it. This makes a modicum more sense when you consider that these are methods on the CTSheetProtection object, but it's still very confusing!

What is required is one of the following (in reverse order of preference):

* Change enableLocking() so that it doesn't lock any of the optional functionality by default
* Add new methods unlockInsertColumns(), unlockInsertRows(), etc. to complement the lock...() methods
* Replace these methods entirely with setInsertColumnsLocked(boolean), setInsertRowsLocked(boolean), and so on

Thanks,
Alex

-----Original Message-----
From: Shinde, Amol [mailto:ashinde@emptoris.com] 
Sent: 07 July 2011 16:50
To: POI Users List
Subject: RE: XSSF Locking

Hi,
I have faced similar issue.

POI 3.7 defines some methods in XSSFSheet.java class like lockInsertColumns(), lockInsertRows() and few more. 
They should actually allow inserting columns/rows, if sheet is already protected/locked.
They actually work reverse of what is expected and pass true to underlying API of CTWorksheet.

public void lockInsertColumns() {
        createProtectionFieldIfNotPresent();
        worksheet.getSheetProtection().setInsertColumns(true);
    }

public void lockInsertRows() {
        createProtectionFieldIfNotPresent();
        worksheet.getSheetProtection().setInsertRows(true);
    }

My suggestion is, these methods in XSSFSheet.java should pass false to underlying API to work correctly.


Thanks,
Amol Shinde
(O):- 91 (20) 30239400  extn:- 531


-----Original Message-----
From: Alex Panayotopoulos [mailto:Alex.Panayotopoulos@vianet.co.uk] 
Sent: Tuesday, July 05, 2011 9:02 PM
To: user@poi.apache.org
Subject: XSSF Locking

Hi all,

Can anybody get the XSSF sheet locking functionality working? I've been
testing it, but so far it looks like 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. How
do I disable *just* column deletion?

N.B. I did find one other thread on this list about this problem, but
the example code did not solve the problem. I also searched for open
issues in Bugzilla, but found none. Should I open a new bug?

Thanks,

Alex Panayotopoulos, Software Engineer
ViaTelemetry Limited

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

RE: XSSF Locking

Posted by "Shinde, Amol" <as...@emptoris.com>.
Hi,
I have faced similar issue.

POI 3.7 defines some methods in XSSFSheet.java class like lockInsertColumns(), lockInsertRows() and few more. 
They should actually allow inserting columns/rows, if sheet is already protected/locked.
They actually work reverse of what is expected and pass true to underlying API of CTWorksheet.

public void lockInsertColumns() {
        createProtectionFieldIfNotPresent();
        worksheet.getSheetProtection().setInsertColumns(true);
    }

public void lockInsertRows() {
        createProtectionFieldIfNotPresent();
        worksheet.getSheetProtection().setInsertRows(true);
    }

My suggestion is, these methods in XSSFSheet.java should pass false to underlying API to work correctly.


Thanks,
Amol Shinde
(O):- 91 (20) 30239400  extn:- 531


-----Original Message-----
From: Alex Panayotopoulos [mailto:Alex.Panayotopoulos@vianet.co.uk] 
Sent: Tuesday, July 05, 2011 9:02 PM
To: user@poi.apache.org
Subject: XSSF Locking

Hi all,

Can anybody get the XSSF sheet locking functionality working? I've been
testing it, but so far it looks like 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. How
do I disable *just* column deletion?

N.B. I did find one other thread on this list about this problem, but
the example code did not solve the problem. I also searched for open
issues in Bugzilla, but found none. Should I open a new bug?

Thanks,

Alex Panayotopoulos, Software Engineer
ViaTelemetry Limited

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org