vba 如何使用apachi-POI在excel中将单元格设为只读

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

提问by Ahmed Abd El Atti

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.

我需要知道如何根据用户的选择使该 xls 文件使这些单元格可编辑/只读。VB 代码也可能有帮助。

采纳答案by Ahmed Abd El Atti

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

好的，我想我已经找到了我要找的东西。使用以下 VBA 代码：

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 :)

感谢每一位试图提供帮助的人:)

回答by Sajan Chandran

Get the cellyou want and set your cell style

得到cell你想要的并设置你的单元格样式

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

Hope it helps.

希望能帮助到你。

回答by Bhawishya

//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.

这将使此单元格成为可编辑的，而未应用此属性的其余单元格将保持锁定或只读状态。编辑密码提示将出现在 excel 中。用户可以使用password="password"或任何设置来解锁它们。

Screenshot

截屏