File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Locking Excel 2003 sheets using Apache POI

 
Ashwini Sawant
Greenhorn
Posts: 5
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Pie
Posts: 42966
73
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 5
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Pie
Posts: 42966
73
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 5
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Pie
Posts: 42966
73
  • 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 5
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Pie
Posts: 42966
73
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 5
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic