工作表受保护后，在xls中启用下拉菜单 [英] Enable drop down in xls once sheet is protected

问题描述

我需要在受保护的XLS工作表中启用下拉菜单(过滤器).我写了下面的代码，并附加了从中生成的XLS.当您打开excel时，您会看到下拉过滤器，但是不会启用选择功能.

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.

我的约束是: 我需要保持工作表的保护，并且只需要XLS格式的文件(而不是XLSX)中的此功能.

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).

感谢您的帮助，

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-----");

推荐答案

根据

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

很遗憾，apache poi不支持此功能.所以我们只能自己做.在以下示例中，我得到了 InternalSheet 并使用反射记录其中的记录.然后，我提供了一个新类SheetProtectionRecord，它是根据OpenOffice BIFF8文档创建的. byte[] data的字节19和20是Option flags的字节.

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);
  }
 }

}

这篇关于工作表受保护后，在xls中启用下拉菜单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！