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

问题描述

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

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

解决方案

好的，我认为我已经找到了自己想要的东西. 使用以下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

感谢每一个试图帮助的人:)

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.

解决方案

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屋！