how to lock cell while creating an excel (xls) file using a java library

Question

how we can create an excel file (xls) using any java library in a way so that cells are not selectable, i.e.: the user should not be able able to select or copy any of the data.

I know we can achieve this if we need an xlsx file by using apache-poi XSSF library i.e.: XSSFSheet.lockSelectLockedCells(boolean enabled) but not sure how to do it using HSSFSheet since I need to create only xls file

Answer 1

Apache poi does not support HSSFSheet lock settings for protection until now. It only supports sheet protection using default lock settings.

In 2017 I had provided a solution already. See Enable drop down in xls once sheet is protected.

Using current apache poi versions it needs override multiple abstract methods now. I have updated my answer with some not implemented methods only to make it work further.

New insights have shown that that SheetProtectionRecord actually is a Shared Features Header Record having the SharedFeatureType ISFPROTECTION. The rgbHdrData is EnhancedProtection data.

So I have extended my SheetProtectionRecord and will provide a method SheetProtectionRecord getOraddSheetProtectionRecord(HSSFSheet hssfSheet) which is able to get a SheetProtectionRecord from a HSSFSheet or add a new if necessary. Using that we then can set all the lock settings fr sheet protection.

Complete example:

import java.io.*;

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

import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.FeatHdrRecord;
import org.apache.poi.hssf.record.RecordBase;
import org.apache.poi.hssf.model.InternalSheet;

import java.lang.reflect.Field;

import java.util.List;

public class ExcelLockCells {
  
 static SheetProtectionRecord getOraddSheetProtectionRecord(HSSFSheet hssfSheet) throws Exception {  
  InternalSheet internalsheet = hssfSheet.getSheet(); 

  Field _records = InternalSheet.class.getDeclaredField("_records");
  _records.setAccessible(true);
  @SuppressWarnings("unchecked") 
  List<RecordBase> records = (List<RecordBase>)_records.get(internalsheet);
  
  byte[] data = null;
  
  //try to find sheet protection record which is a shared features record having type SHAREDFEATURES_ISFPROTECTION - 2
  //if found, get data and remove
  boolean sheetProtectionRecordFound = false;
  for (int i = records.size()-1; i >=0; i--) {
   RecordBase recordBase = records.get(i);
   if (recordBase instanceof FeatHdrRecord) {
    FeatHdrRecord record = (FeatHdrRecord)recordBase; 
    int recordSize = record.getRecordSize(); //includes additional the sid and reclength (4 bytes)
    byte[] recContent = record.serialize();
    data = new byte[recordSize-4];
    for (int j = 4; j < recContent.length; j++) data[j-4] = recContent[j];
    if (data[12] == 0x02 && data[13] == 0x00) { //SHAREDFEATURES_ISFPROTECTION - 2
     sheetProtectionRecordFound = true;
     //System.out.println(record);
     records.remove(record);
    }
   }       
  }

  SheetProtectionRecord sheetProtectionRecord = null; 
  if (sheetProtectionRecordFound && data != null) {
   sheetProtectionRecord = new SheetProtectionRecord(data);
  } else {
   sheetProtectionRecord = new SheetProtectionRecord();     
  }
  records.add(records.size() - 1, sheetProtectionRecord); 
  
  //System.out.println(sheetProtectionRecord);
  
  return sheetProtectionRecord;
 }

 public static void main(String[] args) throws Exception {
  //Workbook workbook = WorkbookFactory.create(new FileInputStream("./ExcelTemplate.xlsx")); String filePath = "./ExcelLockCells.xlsx";
  Workbook workbook = WorkbookFactory.create(new FileInputStream("./ExcelTemplate.xls")); String filePath = "./ExcelLockCells.xls";

  Sheet sheet = workbook.getSheetAt(0);

  //set lock select locked cells
  if (sheet instanceof XSSFSheet) {
   XSSFSheet xssfSheet= (XSSFSheet)sheet;   
   xssfSheet.lockSelectLockedCells(true);   
  } else if (sheet instanceof HSSFSheet) {
   HSSFSheet hssfSheet= (HSSFSheet)sheet; 
   SheetProtectionRecord sheetProtectionRecord = getOraddSheetProtectionRecord(hssfSheet);
   sheetProtectionRecord.lockSelectLockedCells(true);
  }
  
  //protect sheet
  sheet.protectSheet("");

  FileOutputStream fileOut = new FileOutputStream(filePath);
  workbook.write(fileOut);
  fileOut.close();
  workbook.close();
 }
}

Used SheetProtectionRecord.java:

import org.apache.poi.hssf.record.StandardRecord;
import org.apache.poi.hssf.record.HSSFRecordTypes;
import org.apache.poi.util.LittleEndianOutput;
import org.apache.poi.util.GenericRecordUtil;

import java.util.Map;
import java.util.function.Supplier;

public class SheetProtectionRecord extends StandardRecord {

 //see https://learn.microsoft.com/en-us/openspecs/office_file_formats/ms-xls/5748f633-4a5c-4b2c-9f45-2d21c06f753d
 //https://learn.microsoft.com/en-us/openspecs/office_file_formats/ms-xls/4dc13a80-f10a-46e6-b55d-1df4c90508e8
 //https://learn.microsoft.com/en-us/openspecs/office_file_formats/ms-xls/f6b5a32d-7562-4124-882f-badecdc512eb
 
 private byte[] data; 

 public SheetProtectionRecord() {
  super();
  this.data = new byte[]{
   (byte)0x67, 0x08, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, //frtHeader (12 bytes): An FrtHeader structure. The frtHeader.rt MUST be 0x0867.
   0x02, 0x00, //isf (2 bytes): A SharedFeatureType enumeration that specifies the type of Shared Feature. ISFPROTECTION
   0x01, //reserved (1 byte):  Reserved and MUST be 1.
   (byte)0xFF, (byte)0xFF, (byte)0xFF, (byte)0xFF, //cbHdrData (4 bytes): An unsigned integer that specifies whether rgbHdrData exists. rgbHdrData MUST exist.
   0x00, (byte)0x44, 0x00, 0x00 //rgbHdrData (variable) EnhancedProtection
  }; 
 }
 
 public SheetProtectionRecord(byte[] data) {
  super();
  this.data = data;
 }
 
 private SheetProtectionRecord(SheetProtectionRecord other) {
  super(other);
  this.data = other.data;
 }

 public int getDataSize() { 
  return 23; 
 }

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

 public void lockObjects(boolean lock) {
  if(lock) data[19] &= 0xFE;
  else data[19] |= 0x01;
 }
 
 public void lockScenarios(boolean lock) {
  if(lock) data[19] &= 0xFD;
  else data[19] |= 0x02;
 }
 
 public void lockFormatCells(boolean lock) {
  if(lock) data[19] &= 0xFB;
  else data[19] |= 0x04;
 }
 
 public void lockFormatColumns(boolean lock) {
  if(lock) data[19] &= 0xF7;
  else data[19] |= 0x08;
 }
 
 public void lockFormatRows(boolean lock) {
  if(lock) data[19] &= 0xEF;
  else data[19] |= 0x10;
 }
 
 public void lockInsertColumns(boolean lock) {
  if(lock) data[19] &= 0xDF;
  else data[19] |= 0x20;
 }
 
 public void lockInsertRows(boolean lock) {
  if(lock) data[19] &= 0xBF;
  else data[19] |= 0x40;
 }

 public void lockInsertHyperlinks(boolean lock) {
  if(lock) data[19] &= 0x7F;
  else data[19] |= 0x80;
 }
 
 public void lockDeleteColumns(boolean lock) {
  if(lock) data[20] &= 0xFE;
  else data[20] |= 0x01;
 }
 
 public void lockDeleteRows(boolean lock) {
  if(lock) data[20] &= 0xFD;
  else data[20] |= 0x02;
 }

 public void lockSelectLockedCells(boolean lock) {
  if(lock) data[20] &= 0xFB;
  else data[20] |= 0x04;
 }
 
 public void lockSort(boolean lock) {
  if(lock) data[20] &= 0xF7;
  else data[20] |= 0x08;
 }
 
 public void lockAutoFilter(boolean lock) {
  if(lock) data[20] &= 0xEF;
  else data[20] |= 0x10;
 } 

 public void lockPivotTables(boolean lock) {
  if(lock) data[20] &= 0xDF;
  else data[20] |= 0x20;
 } 

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

 public void serialize(LittleEndianOutput out) {
  out.write(data);
 }
 
 @Override
 public SheetProtectionRecord copy() {
  return new SheetProtectionRecord(this);
 }

 @Override
 public HSSFRecordTypes getGenericRecordType() {
  return HSSFRecordTypes.FEAT_HDR;
 }
  
 @Override
 public Map<String, Supplier<?>> getGenericProperties() {
  //return null; // not supported
  return GenericRecordUtil.getGenericProperties(
   "sid", () -> getSid(),
   "type", () -> "FeatHdrRecord.SHAREDFEATURES_ISFPROTECTION"
  );
 }
}

This all is tested and works using current apache poi 5.0.0. Previous versions will fail.

Answer 2

Someone made this question before, I think this is what you want:

Locking some Excel Cells/Rows with POI others editable