CODEWITHSUNDEEP
javaexcelapache-poi
Ayyoub Miftah
Ayyoub Miftah

Reputation: 13

lock rename of sheet name POI java

lock rename of sheet name POI java

how to protect sheet name to not let users change it

XSSFSheet sheet = ((XSSFSheet)s);
//to lock my sheet name
    sheet.lockmysheetName();

I want to protect just sheet name.

Upvotes: 0

Views: 743

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

Microsoft Excel does not provide lock the sheet name on sheet level. There is a possibility to protect a workbook. That protects the structure of the workbook. This includes the lock of the sheet names but also the lock of the sheets order and the lock inserting new sheets.

This is what XSSFWorkbook.lockStructure sets.

HSSFWorkbook has nothing comparable up to now. But using InternalWorkbook and WorkbookRecordList and knowledge about the binary record stream in a binary *.xls workbook one can achieve the same.

Complete example:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.ProtectRecord;
import org.apache.poi.hssf.model.InternalWorkbook;
import org.apache.poi.hssf.model.WorkbookRecordList;

public class CreateExcelLockStructure {
    
 static void lockStructure(HSSFWorkbook hssfWorkbook) {
  InternalWorkbook internalWorkbook = hssfWorkbook.getInternalWorkbook();
  WorkbookRecordList workbookRecordList = internalWorkbook.getWorkbookRecordList();
  int protpos = workbookRecordList.getProtpos();
  Record record = workbookRecordList.get(protpos);
  if (record instanceof ProtectRecord) {
   ProtectRecord protectRecord = (ProtectRecord)record;
   protectRecord.setProtect(true);
  } else {
   ProtectRecord protectRecord = new ProtectRecord(true);
   protpos = workbookRecordList.size() - 1;
   workbookRecordList.add(protpos, protectRecord);
   workbookRecordList.setProtpos(protpos);
  }
 }

 public static void main(String[] args) throws Exception {
     
  Workbook workbook = new XSSFWorkbook(); String filePath = "./CreateExcelLockStructure.xlsx";
  //Workbook workbook = new HSSFWorkbook(); String filePath = "./CreateExcelLockStructure.xls";

  Sheet sheet = workbook.createSheet("SheetName1");
  sheet = workbook.createSheet("SheetName2");

  if (workbook instanceof XSSFWorkbook) {
   XSSFWorkbook xssfWorkbook = (XSSFWorkbook)workbook;   
   xssfWorkbook.lockStructure();
  } else if (workbook instanceof HSSFWorkbook) {
   HSSFWorkbook hssfWorkbook = (HSSFWorkbook)workbook;
   lockStructure(hssfWorkbook);
  }

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close();
  workbook.close();

 }

}

Upvotes: 1

Related Questions