How to make perticular column readonly in excel using apache POI

Question

I am generating an excel file using apache poi and i want to make last two columns read only in that excel file. So i have tried the locked feature of poi but it is making all cells readonly , where i want to the last two readonly .Here i am posting what i have done so far

   HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        sheet.protectSheet("password");
        HSSFRow row = null;

        HSSFCell cell = null;

        /* cell style for locking */
        CellStyle lockedCellStyle = wb.createCellStyle();
        lockedCellStyle.setLocked(true);
        /* cell style for editable cells */
        CellStyle unlockedCellStyle = wb.createCellStyle();
        unlockedCellStyle.setLocked(false);


cell = row.createCell(columnNo);
                if (null != taskDiaryItem.getDueDate()) {
                    SimpleDateFormat formater = new java.text.SimpleDateFormat(InboxItemValue.INBOX_DATE_FORMAT.value());
                    cell.setCellValue(new HSSFRichTextString(formater.format(taskDiaryItem.getDueDate())));
                } else {
                    cell.setCellValue(new HSSFRichTextString(" "));
                }
                columnNo = columnNo + 1;
                /*** End Change : CR#20 CQ#fklnp00272463 ***/

                cell = row.createCell(columnNo);
                if (null != taskDiaryItem.getTaskPriorityDescription()) {
                    cell.setCellValue(new HSSFRichTextString(taskDiaryItem.getTaskPriorityDescription()));
                } else {
                    cell.setCellValue(new HSSFRichTextString(" "));
                }
                columnNo = columnNo + 1;

                cell = row.createCell(columnNo);
                if (null == taskDiaryItem.getContractNumber() || (taskDiaryItem.getContractNumber().isEmpty())) {
                    cell.setCellValue(new HSSFRichTextString(taskDiaryItem.getContractNumber()));
                } else {
                    cell.setCellValue(new HSSFRichTextString(" "));
                }
                columnNo = columnNo + 1;

                cell = row.createCell(columnNo);
                if (null != taskDiaryItem.getTaskStatusDescription()) {
                    cell.setCellValue(new HSSFRichTextString(taskDiaryItem.getTaskStatusDescription()));
                } else {
                    cell.setCellValue(new HSSFRichTextString(" "));
                }
                columnNo = columnNo + 1;

                if (!fromContext.equals(InboxItemValue.USER_INBOX.value())
                        && !fromContext.equals(InboxItemValue.TEAM_MEMBER.value())) {

                    cell = row.createCell(columnNo);
                    if (null != taskDiaryItem.getAssignedToName()) {
                        cell.setCellValue(new HSSFRichTextString(taskDiaryItem.getAssignedToName()));
                        cell.setCellStyle(unlockedCellStyle);
                    } else {
                        cell.setCellValue(new HSSFRichTextString(" "));
                    }
                    columnNo = columnNo + 1;
                }

                cell = row.createCell(columnNo);
                if (null != taskDiaryItem.getAssignedBy()) {
                    cell.setCellValue(new HSSFRichTextString(taskDiaryItem.getAssignedBy()));
                    cell.setCellStyle(lockedCellStyle);
                } else {
                    cell.setCellValue(new HSSFRichTextString(" "));
                }
            } else if(InboxItemValue.PST_INBOX.value().equals(fromContext)) {
                cell = tasksForPSTAndPlacement(taskDiaryItem, cell, row, columnNo, fromContext);
            } else if(InboxItemValue.PLACEMENT_INBOX.value().equals(fromContext)) {
                cell = tasksForPSTAndPlacement(taskDiaryItem, cell, row, columnNo, fromContext);
            }
            j++;
        }
        response.setHeader("Content-Disposition", "attachment; filename=Inbox.xls");
        response.setHeader("Content-type", "application/vnd.ms-excel");
        ServletOutputStream fileOut = response.getOutputStream();
        wb.write(fileOut);
        fileOut.close();

Answer 1

You might have to try the opposite to make it working.

Protect the whole sheet and call setLocked(false) for the cells which should be editable.

I have tried this sample code here if you run this you will find that cell havinf TEST is editable where as cell having value TEST2 is read only, you can use this code to build your logic.

String file = "C:\\poitest.xlsx";
        FileOutputStream outputStream = new FileOutputStream(file);
        Workbook wb = new XSSFWorkbook();

        CellStyle unlockedCellStyle = wb.createCellStyle();
        unlockedCellStyle.setLocked(false);

        Sheet sheet = wb.createSheet();
        sheet.protectSheet("password");
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("TEST");
        cell.setCellStyle(unlockedCellStyle);

        Cell cell2 = row.createCell(1);
        cell2.setCellValue("TEST2");


        wb.write(outputStream);
        outputStream.close();