Apache POI - How to protect sheet with options?

Question

I'm using the Apache POI to generate an Excel File (2007). What I want is to protect the sheet, but with some options enabled. By options I mean the check box list when you try to protect the sheet in the Excel application (under the label "Allow all users of this worksheet to:"). Specifically, I want to enable "Select locked/unlocked cells", "Format Column", "Sort", and "Allow Autofilter". Thank you very much! :D

i dont think beyond `sheet.getSettings()` set() methods, you can do anything. — TheWhiteRabbit, Feb 05 '13 at 06:26
sheet.getSettings() is from JExcel, not Apache POI, I think. — John Bautista, Feb 11 '13 at 18:48

score 18 · Accepted Answer · answered Apr 02 '13 at 08:20

In Apache POI 3.9 you can use XSSF Sheet protection by enabling lock functions. even you can leave behind few excel objects unlocked as in case below I left out excel object (i.e text box) unlocked and rest are locked.

 private static void lockAll(Sheet s, XSSFWorkbook workbookx){
    String password= "abcd";
    byte[] pwdBytes = null;
    try {
        pwdBytes  = Hex.decodeHex(password.toCharArray());
    } catch (DecoderException e) {
        e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
    }
    XSSFSheet sheet = ((XSSFSheet)s);
    removePivot(s,workbookx);
    sheet.lockDeleteColumns();
    sheet.lockDeleteRows();
    sheet.lockFormatCells();
    sheet.lockFormatColumns();
    sheet.lockFormatRows();
    sheet.lockInsertColumns();
    sheet.lockInsertRows();
    sheet.getCTWorksheet().getSheetProtection().setPassword(pwdBytes);
    for(byte pwdChar :pwdBytes){
        System.out.println(">>> Sheet protected with '" + pwdChar + "'");
    }
    sheet.enableLocking();

    workbookx.lockStructure();

}

Could you please explain from where `removePivot(s,workbookx);` comes? Also, does this answer change at all on poi-ooxml 3.17? — Kevin Meredith, May 05 '18 at 20:32
Also, using this code on v. 3.17, minus the `removePivot(s,workbookx);`, I was able to open the saved XLSX, but then un-click "Protect Sheet" without entering a password. Then, I could modify the sheet however I liked. — Kevin Meredith, May 05 '18 at 20:39
@KevinMeredith: To make this work, you've to call the protectSheet method on the WorkSheet object with a password. With this, the protection can no longer be removed. — Vikash Madhow, Mar 22 '21 at 13:09

score 6 · Answer 2 · answered Oct 01 '13 at 11:13

You might encounter that you can't select which features, it's either all or nothing. This is currently a known bug in Apache Poi. Source: https://issues.apache.org/bugzilla/show_bug.cgi?id=51483

You can fix this by using the following workaround:

  xssfSheet.enableLocking();
  CTSheetProtection sheetProtection = xssfSheet.getCTWorksheet().getSheetProtection();
  sheetProtection.setSelectLockedCells(true); 
  sheetProtection.setSelectUnlockedCells(false); 
  sheetProtection.setFormatCells(true); 
  sheetProtection.setFormatColumns(true); 
  sheetProtection.setFormatRows(true); 
  sheetProtection.setInsertColumns(true); 
  sheetProtection.setInsertRows(true); 
  sheetProtection.setInsertHyperlinks(true); 
  sheetProtection.setDeleteColumns(true); 
  sheetProtection.setDeleteRows(true); 
  sheetProtection.setSort(false); 
  sheetProtection.setAutoFilter(false); 
  sheetProtection.setPivotTables(true); 
  sheetProtection.setObjects(true); 
  sheetProtection.setScenarios(true);

Im using an XSSFSheet object.Is there a way to enable Clear all filters option in excel with my sheet being protected.? any suggestions ? — Ryan Wright, Nov 25 '14 at 12:12

score 0 · Answer 3 · answered Dec 27 '22 at 20:13

Thanks to other answers, specially from @Patrigan, below code snipet that worked for me, using Apache POI version 3.17.

sheet.enableLocking();
CTSheetProtection sheetProtection = sheet.getCTWorksheet().getSheetProtection();
sheetProtection.setSelectLockedCells(true); 
sheetProtection.setSelectUnlockedCells(false); 
sheetProtection.setFormatCells(true); 
sheetProtection.setFormatColumns(true); 
sheetProtection.setFormatRows(true); 
sheetProtection.setInsertColumns(true); 
sheetProtection.setInsertRows(true); 
sheetProtection.setInsertHyperlinks(true); 
sheetProtection.setDeleteColumns(true); 
sheetProtection.setDeleteRows(true); 
sheetProtection.setSort(false); 
sheetProtection.setAutoFilter(false); 
sheetProtection.setPivotTables(true); 
sheetProtection.setObjects(true); 
sheetProtection.setScenarios(true);
sheet.protectSheet(password);
  
workbook_car.lockStructure();

Apache POI - How to protect sheet with options?

3 Answers3

Linked