java - 一旦工作表受到保护，启用 xls 中的下拉菜单

Question

我需要在 protected XLS 工作表中启用下拉列表(过滤器)。我编写了下面的代码，并附上了从中生成的 XLS。当您打开 Excel 时，您将看到下拉过滤器，但不会启用它进行选择。

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

感谢您的帮助，

沙申克

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

根据OpenOffice BIFF8 documentation SHEETPROTECTION 是 Sheet Substream 中的 BIFF 记录。所以我们需要在那里插入该记录。

不幸的是apache poi不支持这个。所以我们只能自己做这件事。我按照示例得到 InternalSheet并使用反射来记录其中的内容。然后我提供一个新类 SheetProtectionRecord，它是根据 OpenOffice BIFF8 文档创建的。 byte[]数据的字节19和20是选项标志。

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

}

<小时/>

使用当前的apache poi 5.0.0，有多个抽象方法需要在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
  }
 }