aspose file tools*
The moose likes Other Open Source Projects and the fly likes Locking Excel 2003 sheets using Apache POI Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "Locking Excel 2003 sheets using Apache POI" Watch "Locking Excel 2003 sheets using Apache POI" New topic
Author

Locking Excel 2003 sheets using Apache POI

Ashwini Sawant
Greenhorn

Joined: Feb 15, 2011
Posts: 5
Hi,

I want to know what APIs I can use to lock work sheets on a Excel 2003 spreadsheet. I am able to do this for a 2007 spreadsheet using APIs provided for XSSFWorkbook and XSSFSheet. But I cannot create these objects for a 2003 spreadsheet. I am using POI 3.6 "poi-3.6-20091214.jar".

Any help appreciated.

Thanks,
Ashwini
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42927
    
  68
I'd think that the corresponding HSSF classes also have that functionality; have you looked through the javadocs of the "hssf" packages?
Ashwini Sawant
Greenhorn

Joined: Feb 15, 2011
Posts: 5
Ulf Dittmer wrote:I'd think that the corresponding HSSF classes also have that functionality; have you looked through the javadocs of the "hssf" packages?


- Yes I did and did not see any methods available. Also tried to just add the code like XSSF but that did not compile.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42927
    
  68
Also tried to just add the code like XSSF but that did not compile.

Well, we don't know what you tried and what the errors were, so we can't help with that.

Which classes/methods did you successfully use for Excel 2007 files?
Ashwini Sawant
Greenhorn

Joined: Feb 15, 2011
Posts: 5
This is what I used for a 2007 spreadsheet:

XSSFWorkbook workbook = new XSSFWorkbook("C:/ExcelTestingProj/test/testfile.xlsx");
XSSFSheet sheet = workbook.getSheet("Sheet2");
File file = new File("C:/ExcelTestingProj/test/testfile-updated.xlsx");
FileOutputStream fos = new FileOutputStream(file);

sheet.lockDeleteColumns();
sheet.lockDeleteRows();
sheet.lockFormatCells();
sheet.lockFormatColumns();
sheet.lockFormatRows();
sheet.lockInsertColumns();
sheet.lockInsertRows();
sheet.enableLocking();

workbook.lockStructure();
workbook.write(fos);

But this code does not work (fails at statement "XSSFWorkbook workbook = new XSSFWorkbook("C:/ExcelTestingProj/test/testfile.xlsx");
") if I pass a ".xls" file as parameter.

For a 2003 spreadsheet I used:

File file = new File("C:/ExcelTestingProj/test/testfile.xls");
InputStream is = new FileInputStream(file);
HSSFWorkbook workbook = new HSSFWorkbook(is);
HSSFSheet sheet = workbook.getSheet("Sheet2");

but then methods "sheet.lockDeleteColumns()" through "workbook.lockStructure()" are not available for the HSSF objects (as opposed to the XSSF objects used for 2007 spreadsheet above).

And I did not see any methods that would do similar tasks for HSSF obejcts.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42927
    
  68
But this code does not work if I pass a ".xls" file as parameter.

That was to be expected, since the XSSF classes only handle XLSX files, not XLS.

but then methods "sheet.lockDeleteColumns()" through "workbook.lockStructure()" are not available for the HSSF objects (as opposed to the XSSF objects used for 2007 spreadsheet above).

XLS files do not offer the same degree of locking that XLSX does, but you can use HSSFSheet.protectSheet to lock entire worksheets. Even better, use org.apache.poi.ss.usermodel.Sheet.protectSheet (you should use the ss package classes whenever possible since they work for both kinds of files).
Ashwini Sawant
Greenhorn

Joined: Feb 15, 2011
Posts: 5
That helped!!

The ss package Sheet object did not have protectSheet method. But I used HSSF object. Since in my case I am okay with locking the entire sheet, it works.

Thanks for your help.
Ashwini
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42927
    
  68
The ss package Sheet object did not have protectSheet method.

It most certainly does: http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Sheet.html#protectSheet(java.lang.String)
Ashwini Sawant
Greenhorn

Joined: Feb 15, 2011
Posts: 5
Ulf Dittmer wrote:
The ss package Sheet object did not have protectSheet method.

It most certainly does: http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Sheet.html#protectSheet(java.lang.String)


Hmm..I see that in the API, but when I actually use it in code, it errored! Maybe I did something wrong. Thanks anyway.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Locking Excel 2003 sheets using Apache POI