一旦工作表受到保护，在 xls 中启用下拉菜单 [英] Enable drop down in xls once sheet is protected
本文介绍了一旦工作表受到保护，在 xls 中启用下拉菜单的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要在受保护的 XLS 工作表中启用下拉菜单(过滤器).我写了下面的代码，并附上了从中生成的 XLS.当您打开 excel 时，您会看到下拉过滤器，但它不会被启用以供选择.
我的限制是:我需要保持对工作表的保护，并且我只需要在 XLS 格式文件(不是 XLSX)中使用此功能.
感谢您的帮助，
Shashank
String excelFileName = "C:\\Users\\Admin\\Desktop\\GN_Files\\Test.xls";//excel文件名String sheetName = "Sheet1";//工作表名称HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet(sheetName) ;//sheet.protectSheet("");//迭代r行数CellStyle style=wb.createCellStyle();style.setLocked(false);sheet.setAutoFilter(CellRangeAddress.valueOf("A1:C3"));for (int r=0;r <3; r++ ){HSSFRow 行 = sheet.createRow(r);//迭代c列数for (int c=0;c <3; c++ ){如果(r==1){HSSFCell 单元格 = row.createCell(c);cell.setCellValue(1);//cell.setCellStyle(style);}如果(r==2){HSSFCell 单元格 = row.createCell(c);cell.setCellValue(2);//cell.setCellStyle(style);}如果(r==0){HSSFCell 单元格 = row.createCell(c);cell.setCellValue(0);cell.setCellStyle(风格);}}}sheet.protectSheet("");FileOutputStream fileOut = new FileOutputStream(excelFileName);//将此工作簿写入输出流.wb.write(fileOut);fileOut.flush();fileOut.close();wb.close();System.out.println("完成-----"); 解决方案
根据 OpenOffice BIFF8 文档 是 Sheet Substream 中的 SHEETPROTECTION BIFF 记录.所以我们需要在那里插入那个记录.
不幸的是 apache poi 不支持这个.所以我们只能自己做.我按照示例我得到 InternalSheet 和其中的记录使用反射.然后我提供了一个新的类 SheetProtectionRecord，它是根据 OpenOffice BIFF8 文档创建的.byte[] data 的字节 19 和 20 是 Option flags.
import java.io.FileOutputStream;导入 org.apache.poi.hssf.usermodel.*;导入 org.apache.poi.ss.util.CellRangeAddress；导入 org.apache.poi.hssf.record.RecordBase;导入 org.apache.poi.hssf.record.StandardRecord；导入 org.apache.poi.hssf.model.InternalSheet;导入 org.apache.poi.util.LittleEndianOutput；导入 java.lang.reflect.Field;导入 java.util.List;公共类 CreateExcelHSSFProtectedSheet {public static void main(String[] args) 抛出异常 {HSSFWorkbook 工作簿 = new HSSFWorkbook();HSSFSheet 工作表 = workbook.createSheet();sheet.setAutoFilter(CellRangeAddress.valueOf(A1:C3"));HSSFRow 行 = 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(未选中")列表<RecordBase>record = (List)_records.get(internalsheet);SheetProtectionRecord sheetprotection = new SheetProtectionRecord();sheetprotection.lockAutoFilter(false);sheetprotection.lockInsertRows(false);sheetprotection.lockInsertHyperlinks(false);record.add(records.size() - 1, sheetprotection);/*for (RecordBase r : internalsheet.getRecords()) {System.out.println(r);}*/FileOutputStream out = new FileOutputStream(CreateExcelHSSFProtectedSheet.xls");workbook.write(out);关闭()；workbook.close();}静态类 SheetProtectionRecord 扩展了 StandardRecord {//见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字节[]数据=新字节[]{(字节)0x67, 0x08, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x02, 0x01, (0x01, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00,)0xFF, (byte)0xFF, (byte)0xFF, 0x00, (byte)0x44, 0x00, 0x00};公共 int getDataSize() {返回 23；}公共短 getSid() {返回(短)0x0867；}无效锁自动过滤器(布尔锁){if(lock) data[20] &= 0xEF;否则数据[20] |= 0x10；}void lockSelectLockedCells(boolean lock) {if(lock) data[20] &= 0xFB;否则数据[20] |= 0x04；}void lockSelectUnLockedCells(boolean lock) {if(lock) data[20] &= 0xBF;否则数据[20] |= 0x40；}无效锁插入行(布尔锁){if(lock) data[19] &= 0xBF;否则数据[19] |= 0x40；}void lockInsertHyperlinks(boolean lock) {if(lock) data[19] &= 0x7F;否则数据[19] |= 0x80；}//其他方法....公共无效序列化(LittleEndianOutput 输出){输出(数据)；}}} 使用当前的 apache poi 5.0.0 有多个抽象方法需要在类 SheetProtectionRecord 中覆盖.
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-----");
解决方案
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
}
}
这篇关于一旦工作表受到保护，在 xls 中启用下拉菜单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
