Not able to make a row non editable in .xlsx file without protectSheet() method

I have written a java code where in i am creating a Excel file of type.xlsx and in that file in sheet one i am creating 3 rows with some data as well as formula... now the requirement is to make this rows non editable to user without protecting the whole sheet.

Request to let me know if there is any other way to do this.

Below is the code snippet for the same.

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class CmWriteXLSXTemplateFile {
public static void main(String[] args) {
XSSFWorkbook wb = new XSSFWorkbook();
try {
OutputStream fileOut = new FileOutputStream("D:\\Personal\\Practise\\test.xlsx");
XSSFSheet sheet1 = wb.createSheet("Main_Tab");
XSSFSheet sheet2 = wb.createSheet("Defination");
//sheet1.enableLocking();
sheet1.protectSheet("0");
ArrayList<String> tt = new ArrayList<String>();
for(int kk=0;kk< 42;kk++){
tt.add("C"+kk);
}
// Finding number of Sheets present in Workbook
int numberOfSheets = wb.getNumberOfSheets();
System.out.println("Total Number of Sheets: "
+ numberOfSheets);
XSSFRow row = sheet1.createRow(0);
Cell cell = row.createCell(0);
sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
XSSFFont fontBoldItalic = wb.createFont();
fontBoldItalic.setBold(true);
fontBoldItalic.setItalic(true);
//fontBoldItalic.setColor(IndexedColors..index);
XSSFRichTextString cellValue = new XSSFRichTextString();
cellValue.append("Fixed Columns", fontBoldItalic);
cell.setCellValue(cellValue);
cell.setCellStyle(cellStyle);
CellStyle lock = wb.createCellStyle();
lock.setLocked(false);
Cell cell1 = row.createCell(8);
sheet1.addMergedRegion(new CellRangeAddress(0, 0, 8, 28));
CellStyle cellStyle1 = wb.createCellStyle();
cellStyle1.setFillBackgroundColor(IndexedColors.BROWN.index);
//cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle1.setAlignment(CellStyle.ALIGN_CENTER);
//cellStyle1.setLocked(true);
//Font ft = wb.createFont();
// ft.setColor(HSSFColor.WHITE.index);
XSSFFont fontBoldItalic1 = wb.createFont();
fontBoldItalic1.setBold(true);
fontBoldItalic1.setItalic(true);
//fontBoldItalic.setColor(IndexedColors..index);
XSSFRichTextString cellValue1 = new XSSFRichTextString();
cellValue1.append("Dynamics Columns", fontBoldItalic);
cell1.setCellValue(cellValue1);
cell1.setCellStyle(cellStyle1);
//row.setRowStyle(lock);
Row row12 = sheet1.createRow(1);
Cell cell12 = row12.createCell(0);
sheet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
CellStyle cellStyle12 = wb.createCellStyle();
cellStyle12.setFillBackgroundColor(IndexedColors.BROWN.index);
//cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle12.setAlignment(CellStyle.ALIGN_CENTER);
//cellStyle12.setLocked(true);
//Font ft = wb.createFont();
// ft.setColor(HSSFColor.WHITE.index);
XSSFFont fontBoldItalic12 = wb.createFont();
fontBoldItalic12.setBold(true);
fontBoldItalic12.setItalic(true);
//fontBoldItalic.setColor(IndexedColors..index);
XSSFRichTextString cellValue12 = new XSSFRichTextString();
cellValue12.append("Name", fontBoldItalic);
cell12.setCellValue(cellValue12);
cell12.setCellStyle(cellStyle12);
Cell cell13 = row12.createCell(2);
XSSFDataValidation dv = null;
XSSFDataValidationConstraint dvc = null;
//DataValidationHelper dvh = null;
XSSFDataValidationHelper dvh = null;
CellRangeAddressList addList = new CellRangeAddressList(1, 1, 2, 4);
sheet1.addMergedRegion(new CellRangeAddress(1, 1, 2, 4));
dvh = new XSSFDataValidationHelper((XSSFSheet) sheet1);
dvc =(XSSFDataValidationConstraint)                        dvh.createFormulaListConstraint("Defination!$A$2:$A$" + 40);
dv = (XSSFDataValidation) dvh.createValidation(dvc, addList);
dv.setSuppressDropDownArrow(true);
sheet1.addValidationData(dv);
Cell cell14 = row12.createCell(5);
//sheet1.addMergedRegion(new CellRangeAddress(1, 1, 2, 4));
CellStyle cellStyle14 = wb.createCellStyle();
cellStyle14.setFillBackgroundColor(IndexedColors.BROWN.index);
//cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle14.setAlignment(CellStyle.ALIGN_CENTER);
//cellStyle14.setLocked(false);
//Font ft = wb.createFont();
// ft.setColor(HSSFColor.WHITE.index);
XSSFFont fontBoldItalic14 = wb.createFont();
fontBoldItalic14.setBold(true);
fontBoldItalic14.setItalic(true);
//fontBoldItalic.setColor(IndexedColors..index);
XSSFRichTextString cellValue14 = new XSSFRichTextString();
cellValue14.append("ID", fontBoldItalic);
cell14.setCellValue(cellValue14);
cell14.setCellStyle(cellStyle14);
Cell cell15 = row12.createCell(6);
//sheet1.addMergedRegion(new CellRangeAddress(1, 1, 2, 4));
CellStyle cellStyle15 = wb.createCellStyle();
cellStyle15.setFillBackgroundColor(IndexedColors.PLUM.index);
//cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle15.setAlignment(CellStyle.ALIGN_CENTER);
//Font ft = wb.createFont();
// ft.setColor(HSSFColor.WHITE.index);
XSSFFont fontBoldItalic15 = wb.createFont();
fontBoldItalic15.setBold(true);
fontBoldItalic15.setItalic(true);
//fontBoldItalic.setColor(IndexedColors..index);
XSSFRichTextString cellValue15 = new XSSFRichTextString();
cellValue15.append(" ", fontBoldItalic);
cell15.setCellValue(cellValue15);
cell15.setCellStyle(cellStyle15);
CellStyle cellStyle16 = wb.createCellStyle();
cellStyle16.setFillBackgroundColor(IndexedColors.CORAL.index);
cellStyle16.setAlignment(CellStyle.ALIGN_CENTER);
XSSFRichTextString cellValue16 = new XSSFRichTextString();
cellValue16.append(" ", fontBoldItalic);
for(int i =7;i < 27; i++){
Cell cell16 = row12.createCell(i);
cell16.setCellValue(cellValue16);
cell16.setCellStyle(cellStyle16);
}
//row12.setRowStyle(lock);
Row row13 = sheet1.createRow(2);
CellStyle cellStyle17 = wb.createCellStyle();
cellStyle17.setFillBackgroundColor(IndexedColors.LIME.index);
cellStyle17.setAlignment(CellStyle.ALIGN_CENTER);
XSSFRichTextString cellValue17 = null;
CellStyle cellStyle18 = wb.createCellStyle();
cellStyle18.setFillBackgroundColor(IndexedColors.CORAL.index);
cellStyle18.setAlignment(CellStyle.ALIGN_CENTER);
XSSFRichTextString cellValue18 = null;
int k = 1;
for(int j=0;j<27;j++){
Cell cell17 = row13.createCell(j);
if(j < 7){
cellValue17 = new XSSFRichTextString();
if(j ==0){
cellValue17.append("Class", fontBoldItalic);
cell17.setCellValue(cellValue17);
cell17.setCellStyle(cellStyle17);
} else if (j ==1) {
cellValue17.append("Student_ID", fontBoldItalic);
cell17.setCellValue(cellValue17);
cell17.setCellStyle(cellStyle17);
} else if (j ==2) {
cellValue17.append("Birth_Date", fontBoldItalic);
cell17.setCellValue(cellValue17);
cell17.setCellStyle(cellStyle17);
} else if (j ==3) {
cellValue17.append("Section", fontBoldItalic);
cell17.setCellValue(cellValue17);
cell17.setCellStyle(cellStyle17);
} else if (j ==4) {
cellValue17.append("Strength", fontBoldItalic);
cell17.setCellValue(cellValue17);
cell17.setCellStyle(cellStyle17);
} else if (j ==5) {
cellValue17.append("Subject", fontBoldItalic);
cell17.setCellValue(cellValue17);
cell17.setCellStyle(cellStyle17);
} else if (j ==6) {
cellValue17.append("Marks", fontBoldItalic);
cell17.setCellValue(cellValue17);
cell17.setCellStyle(cellStyle17);
} 
} else {
cellValue18 = new XSSFRichTextString();
cellValue18.append("Field_"+k, fontBoldItalic);
cell17.setCellValue(cellValue18);
cell17.setCellStyle(cellStyle18);
k++;
}
}
//row13.setRowStyle(lock);
// sheet1.getRow(3).getRowStyle().setLocked(true);
Row row2 = sheet2.createRow(0);
Cell cell2 = row2.createCell(0);
CellStyle cellStyle2 = wb.createCellStyle();
//cellStyle15.setFillBackgroundColor(IndexedColors.PLUM.index);
//cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle2.setAlignment(CellStyle.ALIGN_CENTER);
//Font ft = wb.createFont();
// ft.setColor(HSSFColor.WHITE.index);
//fontBoldItalic.setColor(IndexedColors..index);
XSSFRichTextString cellValue2 = new XSSFRichTextString();
cellValue2.append("Sr_No", fontBoldItalic);
cell2.setCellValue(cellValue2);
cell2.setCellStyle(cellStyle2);
Row row3  = null;
CellStyle cellStyle3 = null;
XSSFRichTextString cellValue3 = null;
int[] nonEditableRows = {0, 1, 2}; 
for (int row55 : nonEditableRows) {
Row currentRow = sheet1.getRow(row55);
if (currentRow != null) {
for (Cell cell44 : currentRow) {
cell44.setCellStyle((lock));
}
}
}
wb.write(fileOut);
OutputStream bos = new ByteArrayOutputStream();
wb.write(bos);
//Channel
//fileOut.flush();
}  catch (FileNotFoundException e){
e.printStackTrace();
} catch (IOException e){`your text`
e.printStackTrace();
}
}

requirement is to make certain rows non editable to user without protecting the whole sheet.

Excel is only able protecting sheets. It is not able protecting single cells only. Cells may have a cell style which excludes them from protection. But per default all cells have cell style wich includes protection when sheet is protected. So if the requirement is ton protect only some cells, the need is to set a default cell style which excludes protection.

The principle for XSSF is shown in this Q/A already: https://stackoverflow.com/questions/45086875/excel-headers-uneditable-poi/45125170#45125170

Set a CellStyle having setLocked false as the default style for all columns. This is possible by setting a org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol element having min col 1 and max col 16384 having set that style.

Then take out some rows from using that style by setting a row style having setLocked true.

Using current Apache POI versions new created cells also need that style as default row style not gets applied automatically.

If it is XSSFSheet then some things can be set allowed when sheet is protected. See methods XSSFSheet.lock... -> XSSFSheet.lockAutoFilter and below.

If the user shall be able to resize column width, set XSSFSheet xssfSheet ...-> xssfSheet.lockFormatColumns(false); for example.

Following complete example shows this using current Apache POI version 5.2.3.

It provides a method setDefaultCellStyleForAllColumns(Sheet sheet, CellStyle style) to set a default style for all columns.

It also provides a method setCellValue(Cell cell, Object valueObject, DataFormat dataFormat) which is able to set cell value from any kind of object. It additionally uses org.apache.poi.ss.util.CellUtil to set date or date time style when dates are set.

Some annotations to your code:

First: It create an unnecessary amout of different objects. There is no need to create each cell as its own object until the garbage collector destrois it.

Second: It creates too much different cell styles and fonts. Excel has limits for cell styles and fonts per workbook. Those fast get reached when coding as you do. Cell styles and fonts are stored on workbook level and get shared by cells in sheets. So crreate them on workbook level as needed and only use them while creating sheets and set data into cells. Whenever single style properties are neede, do using org.apache.poi.ss.util.CellUtil to do so. This ist able to add properties to cell styles without creating new cell styles over and over again.

I hope, following code will shed some light and that you can get some inspiration from it.

<!-- language: java -->

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.CellUtil;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.GregorianCalendar;
public class CreateExcelSheetProtectOnlyHeaderRowsAndFormulas {
static void setDefaultCellStyleForAllColumns(Sheet sheet, CellStyle style) {
//set the CellStyle as the default style for all columns
if (sheet instanceof XSSFSheet) { // for XSSF
org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol cTCol = 
((XSSFSheet)sheet).getCTWorksheet().getColsArray(0).addNewCol();
cTCol.setMin(1);
cTCol.setMax(16384);
cTCol.setWidth(12.7109375); // default width for XLSX
cTCol.setStyle(style.getIndex());
} // else if (sheet instanceof HSSFSheet) { 
//ToDo for HSSF  
// }   
}
static Object[][] headers = new Object[][] {
new Object[] {"Header 1", "Header 2", "Header 3", "Header 4", "Header 5", "Header 6"},
new Object[] {"Subheader 1", "Subheader 2", "Subheader 3", "Subheader 4", "Subheader 5", "Subheader 6"}
};
static Object[][] data = new Object[][] {
new Object[] {"Text 1", LocalDate.of(2023, 2, 12), (short)123, .25, "C3*D3", true},
new Object[] {"Text 2", LocalDateTime.of(2023, 1, 23, 9, 45, 0), 123.45, .5, "C4*D4", false},
new Object[] {"Text 3", LocalDate.of(2023, 5, 2), 12345, 2, "C5*D5", true},
new Object[] {"Test", new GregorianCalendar(1964, 11, 21), null, 2, "C6*D6", null},
};
static void setCellValue(Cell cell, Object valueObject, DataFormat dataFormat) {
if (valueObject instanceof String) {
cell.setCellValue((String)valueObject);
} else if (valueObject instanceof RichTextString) {
cell.setCellValue((RichTextString)valueObject);
} else if (valueObject instanceof Number) {
cell.setCellValue(((Number)valueObject).doubleValue());
} else if (valueObject instanceof Boolean) {
cell.setCellValue((Boolean)valueObject);  
} else if (valueObject instanceof java.util.Calendar) {
cell.setCellValue((java.util.Calendar )valueObject);
//use CellUtil to set the CellStyleProperty data format to date
CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, dataFormat.getFormat("yyyy-MM-dd"));   
} else if (valueObject instanceof java.util.Date) {
cell.setCellValue((java.util.Date)valueObject);
//use CellUtil to set the CellStyleProperty data format to date
CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, dataFormat.getFormat("yyyy-MM-dd"));
} else if (valueObject instanceof java.time.LocalDate) {
cell.setCellValue((java.time.LocalDate)valueObject);
//use CellUtil to set the CellStyleProperty data format to date
CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, dataFormat.getFormat("yyyy-MM-dd"));
} else if (valueObject instanceof java.time.LocalDateTime) {
cell.setCellValue((java.time.LocalDateTime)valueObject);
//use CellUtil to set the CellStyleProperty data format to date time
CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, dataFormat.getFormat("yyyy-MM-dd hh:mm:ss"));
} else {
cell.setCellValue(String.valueOf(valueObject)); 
}
}
public static void main(String[] args) throws Exception {
Workbook workbook = new XSSFWorkbook();
//prepare workbook on workbook level start...
//create needed fonts
Font fontBoldBlue = workbook.createFont();
fontBoldBlue.setBold(true);
fontBoldBlue.setColor(IndexedColors.BLUE.getIndex());  
Font fontBoldItalic = workbook.createFont();
fontBoldItalic.setBold(true);
fontBoldItalic.setItalic(true);  
//create a CellStyle having setLocked false
CellStyle cellstyleNotLocked = workbook.createCellStyle();
cellstyleNotLocked.setLocked(false);
//create a CellStyle having setLocked true
CellStyle cellstyleLocked = workbook.createCellStyle();
cellstyleLocked.setLocked(true);
//create a CellStyle for header rows
CellStyle cellstyleHeaders = workbook.createCellStyle();
cellstyleHeaders.setLocked(true); // header cells are locked
cellstyleHeaders.setFont(fontBoldBlue);
cellstyleHeaders.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cellstyleHeaders.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//create a CellStyle for subheader rows
CellStyle cellstyleSubheaders = workbook.createCellStyle();
cellstyleSubheaders.setLocked(true); // header cells are locked
cellstyleSubheaders.setFont(fontBoldItalic);
cellstyleSubheaders.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cellstyleSubheaders.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//create data format
DataFormat dataFormat = workbook.createDataFormat();
//...prepare workbook on workbook level end
//create sheets and set data start...
Sheet sheet = workbook.createSheet("Sheet1");
//set the CellStyle having setLocked false as the default style for all columns
setDefaultCellStyleForAllColumns(sheet, cellstyleNotLocked);
Row row;
Cell cell;
int headerRow1 = 0;
int dataRow1 = headerRow1 + headers.length;
//set header rows
int r = headerRow1;
for (Object[] headerRow : headers) {
row = sheet.createRow(r);
//set the default style for that row
if (r==0) { // first row is main headers
row.setRowStyle(cellstyleHeaders);
} else { // other rows are subheaders
row.setRowStyle(cellstyleSubheaders);
}
int c = 0;
for (Object valueObject : headerRow) {
cell = row.createCell(c);
setCellValue(cell, valueObject, dataFormat);
if (r==0) {
cell.setCellStyle(cellstyleHeaders);
} else {
cell.setCellStyle(cellstyleSubheaders);
}
c++;
}
r++;   
}
//set data rows
r = dataRow1;
for (Object[] dataRow : data) {
row = sheet.createRow(r);
int c = 0;
for (Object valueObject : dataRow) {
cell = row.createCell(c);
if (c == 4) { // 5th cell is formula
cell.setCellFormula(String.valueOf(valueObject));
cell.setCellStyle(cellstyleLocked); // formulas are locked
} else {
setCellValue(cell, valueObject, dataFormat);
}
c++;
}
r++;   
}
//autosize column widths
for (int c = 0; c < 5; c++) {
sheet.autoSizeColumn(c);  
}
//allow some things for protected sheet if XSSFSheet
if (sheet instanceof XSSFSheet) {
XSSFSheet xssfSheet = (XSSFSheet)sheet;
xssfSheet.lockFormatColumns(false);
xssfSheet.lockFormatRows(false);
}
//protect sheet
sheet.protectSheet("password");  
//...create sheets and set data end
//save workbook to file
FileOutputStream out = new FileOutputStream("./CreateExcelSheetProtectOnlyHeaderRowsAndFormulas.xlsx");
workbook.write(out);
out.close();
workbook.close();
}
}