how to make cell as read-only in excel using apachi-POI

Question

i have a drop down list contains some options, and for example two cells. what i need is regarding the selected option turn one of the cells to editable and the other to read-only and vise-versa.

FileOutputStream fos;
try {
    fos = new FileOutputStream("D:\\POIXls.xls");
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("new Sheet");
    DataValidationHelper dvHelper = sheet.getDataValidationHelper();
    DataValidationConstraint dvConstraint = 
                  dvHelper.createExplicitListConstraint(new String[] { "cell 1 edit","cell 2 edit"});
   CellRangeAddressList addressList = new CellRangeAddressList(0, 2, 0, 0);
   DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);

   if (validation instanceof XSSFDataValidation) {
       validation.setSuppressDropDownArrow(true);
       validation.setShowErrorBox(true);
   } else {
       validation.setSuppressDropDownArrow(false);
   }

   sheet.addValidationData(validation);
   workbook.write(fos);
   fos.flush();
   fos.close();
}catch(Exception e){//catch code}

i need to know how to make that xls file make these cells editable/read-only according to the user's selection. VB code may be helpful also.

This link may be helpful to you. http://stackoverflow.com/questions/8502552/make-column-as-read-only-using-apache-poi — Konza, Nov 06 '12 at 11:32
thank you @Konza for your reply, but what i need is how to make the excel file it self lock/unlock the cell while user is selecting the option. to prevent him from entering data in "cell 2" while "cell 1 edit" is selected. — Ahmed Abd El Atti, Nov 06 '12 at 11:37

score 7 · Answer 1 · answered Nov 06 '12 at 11:37

7

Get the cell you want and set your cell style

CellStyle unlockedCellStyle = wb.createCellStyle();
unlockedCellStyle.setLocked(true); //true or false based on the cell.
cell.setCellStyle(unlockedCellStyle);

Hope it helps.

answered Nov 06 '12 at 11:37

Sajan Chandran

11,287
3
29
38

thank you for your help, but what i need is how to make the excel file it self lock/unlock the cell while user is selecting the option. to prevent him from entering data in "cell 2" while "cell 1 edit" is selected. – Ahmed Abd El Atti Nov 06 '12 at 11:40
sounds like what you need is some VisualBasic magic embedded in the Excel you're generating. – Ivan Klaric Nov 06 '12 at 16:37
@IvanKlaric now i think that i wont be able to accomplish that without VB or macro. so any suggestions or help with code will be very helpful for me. – Ahmed Abd El Atti Nov 07 '12 at 11:42
Unfortunatelly, I don't have any experience with generating xls files with embedded VB from POI. I wouldn't be suprised it's not supported :-( – Ivan Klaric Nov 07 '12 at 15:08

score -1 · Accepted Answer · answered Nov 07 '12 at 13:27

OK i think i have found what i was looking for. using the following VBA code:

Private Sub Worksheet_Change(ByVal Target As Range){
    If Range(ActiveCell.Address).Validation.Parent = "33" Then
        ActiveSheet.Unprotect
        Range("$B$" & ActiveCell.Row).Locked = True
        Range("$C$" & ActiveCell.Row).Locked = False
        ActiveSheet.Protect
    ElseIf Range(ActiveCell.Address).Validation.Parent = "23" Then
        ActiveSheet.Unprotect
        MsgBox ActiveCell.Address
        Range("$C$" & ActiveCell.Row).Locked = True
        Range("$B$" & ActiveCell.Row).Locked = False
        ActiveSheet.Protect
    Else
        ActiveSheet.Unprotect
        Range("$C$" & ActiveCell.Row).Locked = True
        Range("$B$" & ActiveCell.Row).Locked = True
        ActiveSheet.Protect
    End If
End Sub

thanks to every one tried to help :)

I unvoted because I was looking for a solution in Java POI - but correct me if I'm wrong but it seems this is not possible in Java. Then may I ask you to edit your answer just to let me remove my down vote ;) — NicoESIEA, Dec 27 '22 at 15:57

score -1 · Answer 3 · edited Apr 09 '18 at 10:26

//To make specific cells ReadOnly when using NPOI:
//Make the whole sheet as protected first and then unlock the desired cells.

//Creating a workbook. workbook is a variable name
HSSFWorkbook workbook = new HSSFWorkbook();

//adding a sheet. sheet1 is a variable name 
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("sheet1");

//Creating column styling. storeCellStyle is a variable name
ICellStyle storeCellStyle = workbook.CreateCellStyle(); 

//Locking the whole sheet

sheet.ProtectSheet("password"); 

//giving islocked as false,this property will be used to make the cells editable while rest of the cells will remain read only
storeCellStyle.IsLocked=false; 

//Now applying the style while creating the cells in the sheet
ICell headerSheet21 = headerRowSheet2.CreateCell(0); //headerSheet21 is variable

headerSheet21.SetCellValue("Employee_Id"); //cell value

headerSheet21.CellStyle = storeCellStyle;

This will make this cell as editable while rest of the on which this property is not applied will remain locked or read only. On editing the password promtp will occur in excel. User can use password="password" or whatever is set to unlock them.

Screenshot

how to make cell as read-only in excel using apachi-POI

3 Answers3

Linked