Enable drop down in xls once sheet is protected

Question

I need to enable the drop-down (filters) in a protected XLS sheet. I have written below code and also attached the XLS which gets generated from it. When you open the excel you will see the drop down filters however it will not be enabled for selection.

Constraints I have are: I need to keep the protection of the sheet on and I need this feature in XLS format file only (not XLSX).

Thanks for your help,

Shashank

String excelFileName = "C:\\Users\\Admin\\Desktop\\GN_Files\\Test.xls";//name of excel file
        String sheetName = "Sheet1";//name of sheet
        HSSFWorkbook wb = new HSSFWorkbook();

        HSSFSheet sheet = wb.createSheet(sheetName) ;
        //sheet.protectSheet("");
        //iterating r number of rows
        CellStyle style=wb.createCellStyle();
        style.setLocked(false);
        sheet.setAutoFilter(CellRangeAddress.valueOf("A1:C3"));
        for (int r=0;r < 3; r++ )
        {
              HSSFRow row = sheet.createRow(r);

              //iterating c number of columns

              for (int c=0;c < 3; c++ )
              {
                    if(r==1){
                    HSSFCell cell = row.createCell(c);
                    cell.setCellValue(1);
                    //cell.setCellStyle(style);
                    }
                    if(r==2){
                          HSSFCell cell = row.createCell(c);
                          cell.setCellValue(2);
                          //cell.setCellStyle(style);
                          }
                    if(r==0){
                          HSSFCell cell = row.createCell(c);
                          cell.setCellValue(0);
                          cell.setCellStyle(style);
                          }
              }

        }

        sheet.protectSheet("");
        FileOutputStream fileOut = new FileOutputStream(excelFileName);
        //write this workbook to an Outputstream.
        wb.write(fileOut);
        fileOut.flush();
        fileOut.close();
        wb.close();
        System.out.println("done-----");

Answer 1

According to OpenOffice BIFF8 documentation is the SHEETPROTECTION a BIFF record in the Sheet Substream. So we need inserting that record there.

Unfortunatelly does apache poi not supporting this. So we can only doing this ourselfs. I following example I get the InternalSheet and the records in it using reflection. Then I provide a new class SheetProtectionRecord which is created according to OpenOffice BIFF8 documentation. The bytes 19 and 20 of the byte[] data are the ones which are the Option flags.

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.hssf.record.RecordBase;
import org.apache.poi.hssf.record.StandardRecord;
import org.apache.poi.hssf.model.InternalSheet;
import org.apache.poi.util.LittleEndianOutput;

import java.lang.reflect.Field;

import java.util.List;

public class CreateExcelHSSFProtectedSheet {

 public static void main(String[] args) throws Exception {

  HSSFWorkbook workbook = new HSSFWorkbook();
  HSSFSheet sheet = workbook.createSheet();

  sheet.setAutoFilter(CellRangeAddress.valueOf("A1:C3"));
  HSSFRow row = sheet.createRow(0);
  for (int c = 0; c < 3; c++) {
   row.createCell(c).setCellValue("Col " + (c+1));
  }

  for (int r = 1; r < 4; r++) {
   row = sheet.createRow(r);
   for (int c = 0; c < 3; c++) {
    row.createCell(c).setCellValue(r * (c+1)); 
   }
  }

  sheet.protectSheet("");
  Field _sheet = HSSFSheet.class.getDeclaredField("_sheet");
  _sheet.setAccessible(true); 
  InternalSheet internalsheet = (InternalSheet)_sheet.get(sheet); 

  Field _records = InternalSheet.class.getDeclaredField("_records");
  _records.setAccessible(true);
  @SuppressWarnings("unchecked") 
  List<RecordBase> records = (List<RecordBase>)_records.get(internalsheet);

  SheetProtectionRecord sheetprotection = new SheetProtectionRecord();
  sheetprotection.lockAutoFilter(false);
  sheetprotection.lockInsertRows(false);
  sheetprotection.lockInsertHyperlinks(false);

  records.add(records.size() - 1, sheetprotection); 

/*  
  for (RecordBase r : internalsheet.getRecords()) {
   System.out.println(r);
  }
*/

  FileOutputStream out = new FileOutputStream("CreateExcelHSSFProtectedSheet.xls");
  workbook.write(out);
  out.close();
  workbook.close();

 }

 static class SheetProtectionRecord extends StandardRecord {

  //see https://www.openoffice.org/sc/excelfileformat.pdf#%5B%7B%22num%22%3A635%2C%22gen%22%3A0%7D%2C%7B%22name%22%3A%22XYZ%22%7D%2C85.6%2C771.1%2C0%5D

  byte[] data = new byte[]{(byte)0x67, 0x08, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x02, 0x00, 0x01,  (byte)0xFF, (byte)0xFF, (byte)0xFF, (byte)0xFF, 0x00, (byte)0x44, 0x00, 0x00};

  public int getDataSize() { 
   return 23; 
  }

  public short getSid() {
   return (short)0x0867;
  }

  void lockAutoFilter(boolean lock) {
   if(lock) data[20] &= 0xEF;
   else data[20] |= 0x10;
  } 

  void lockSelectLockedCells(boolean lock) {
   if(lock) data[20] &= 0xFB;
   else data[20] |= 0x04;
  }

  void lockSelectUnLockedCells(boolean lock) {
   if(lock) data[20] &= 0xBF;
   else data[20] |= 0x40;
  }

  void lockInsertRows(boolean lock) {
   if(lock) data[19] &= 0xBF;
   else data[19] |= 0x40;
  }

  void lockInsertHyperlinks(boolean lock) {
   if(lock) data[19] &= 0x7F;
   else data[19] |= 0x80;
  }
  //further methods ....

  public void serialize(LittleEndianOutput out) {
   out.write(data);
  }
 }

}

---

Using current apache poi 5.0.0 there are multiple abstract methods which needs override in class SheetProtectionRecord.

...
import org.apache.poi.hssf.record.HSSFRecordTypes;
...
import java.util.Map;
import java.util.function.Supplier;

 static class SheetProtectionRecord extends StandardRecord {
 ...

  @Override
  public SheetProtectionRecord copy() {
   return null; // not supported
  }

  @Override
  public HSSFRecordTypes getGenericRecordType() {
   return null; // not supported
  }
 
  @Override
  public Map<String, Supplier<?>> getGenericProperties() {
   return null; // not supported
  }
 }