how to make cell as read-only in excel using apachi-POI
13,288
Solution 1
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 :)
Solution 2
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.
Author by
Ahmed Abd El Atti
Updated on June 15, 2022Comments
-
Ahmed Abd El Atti almost 2 years
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.