XSSFSheet Apache POI - is it possible to lock everything but allowing update and insert rows?

Question

I try to lock the whole sheet but some columns should be unlocked (I want to add values in some columns). I want to copy some row, add new row and paste values from copied row. Is it possible?

 public ByteArrayResource getQuestionnaireTemplate(List<QuestionnaireTemplateDto> questionnaireTemplateInitialData) throws IOException {
        XSSFWorkbook workbook = excelExportService.createExcelWorkBook();

        String frameworkName = questionnaireTemplateInitialData.stream().map(QuestionnaireTemplateDto::getFramework).findFirst().orElse("Framework Name");
        XSSFSheet sheet = workbook.createSheet(frameworkName);
//        sheet.lockInsertColumns(true);
//        sheet.lockInsertRows(false);
        sheet.enableLocking();
//        sheet.lo
//        CellStyle unlockedCellStyle = workbook.createCellStyle();
//        unlockedCellStyle.setLocked(false);

        CTSheetProtection sheetProtection = sheet.getCTWorksheet().getSheetProtection();
        sheetProtection.setSelectLockedCells(false);
        sheetProtection.setSelectUnlockedCells(false);
        sheetProtection.setFormatCells(false);
        sheetProtection.setFormatColumns(false);
        sheetProtection.setFormatRows(false);
        sheetProtection.setInsertColumns(false);
        sheetProtection.setInsertRows(false);
        sheetProtection.setInsertHyperlinks(false);
        sheetProtection.setDeleteColumns(false);
        sheetProtection.setDeleteRows(false);
        sheetProtection.setSort(false);
        sheetProtection.setAutoFilter(false);
        sheetProtection.setPivotTables(false);
        sheetProtection.setObjects(false);
        sheetProtection.setScenarios(false);

Amd then for some row I can set some cell as editable (it works):

  private void addFieldRow(XSSFSheet sheet, XSSFCellStyle fieldRowStyle, QuestionnaireTemplateDto questionnaireTemplateDto) {
        XSSFRow row = excelExportService.createRow(sheet,
                sheet.getLastRowNum() + 1,
                Arrays.asList(questionnaireTemplateDto.getFrameworkFieldId().toString(), questionnaireTemplateDto.getFramework(), questionnaireTemplateDto.getFieldName(), questionnaireTemplateDto.getYear().toString()),
                fieldRowStyle);

        CellStyle unlockedStyle = sheet.getWorkbook().createCellStyle();
        unlockedStyle.setLocked(false);
        XSSFCell cell = row.createCell(4);
        cell.setCellStyle(unlockedStyle);
    }

My generated sheet works perfectly except for one small detail - I can't insert new row and copy some other row to it...

I think I tried all solutions from Stack Overflow...

Answer 1

You should use XSSFSheet methods instead of low level CTSheetProtection methods for specifying sheet protection. In current apache poi 4.1.2 there are all possibilities also useable using XSSFSheet.lock...- methods.

But what you want is not completely possible. Sheet protection mainly protects cells from changing. So if you allow deleting rows and the row contains protected cells, then deleting the row contradicts the cell protection. The same is for copying a row into another. If that other row contains protected cells, then copying also contradicts the cell protection.

Following complete example creates a workbook having one sheet where all cells are protected except columns A, C and E. The sheet protection allows formatting rows, inserting rows and deleting rows. But deleting rows contradicts the cell protection of all cells in that row except those in columns A, C and E. So deleting rows is allowed but not possible.

So the resulting sheet allows only changing cells in columns A, C and E, formatting rows (row height) and inserting rows.

import java.io.FileOutputStream;

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

public class CreateExcelXSSFProtectedSheet {

 public static void main(String[] args) throws Exception {

  Workbook workbook = new XSSFWorkbook();
  CellStyle notLocked = workbook.createCellStyle();
  notLocked.setLocked(false);

  Sheet sheet = workbook.createSheet();

  sheet.setDefaultColumnStyle(0, notLocked); // column A is not locked
  sheet.setDefaultColumnStyle(2, notLocked); // column C is not locked
  sheet.setDefaultColumnStyle(4, notLocked); // column E is not locked

  ((XSSFSheet)sheet).lockFormatRows(false); // formatting rows is allowed
  ((XSSFSheet)sheet).lockInsertRows(false); // inserting rows is allowed
  ((XSSFSheet)sheet).lockDeleteRows(false); // deleting rows is allowed but may contradict cell protection

  sheet.protectSheet("");

  FileOutputStream out = new FileOutputStream("CreateExcelXSSFProtectedSheet.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();

 }

}