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

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代码也可能有帮助。

3 个解决方案

#1

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.

希望能帮助到你。

#2

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

感谢每一个试图帮助:)

#3

-1

//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中进行promtp。用户可以使用password =“password”或其他任何设置来解锁它们。

Screenshot

#1