如何使用apachi-POI在excel中将单元格设为只读 [英] how to make cell as read-only in excel using apachi-POI

问题描述

我有一个包含一些选项的下拉列表，例如两个单元格.我需要的是关于所选选项将一个单元格变为可编辑，另一个变为只读和反之亦然.

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}

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

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.

推荐答案

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

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

这篇关于如何使用apachi-POI在excel中将单元格设为只读的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！