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
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.
Someone made this question before, I think this is what you want:
