如何使用apache poi使excel单元格只读 [英] How to make excel cells readonly using apache poi

问题描述

在我的情况下，我需要保护单个单元格，为了实现这一点，最初我使用了 HSSFCellStyle 和 setLocked(true) 并使用 Sheet.protectSheet 保护工作表(密码").这也将保护非空单元格，所以我使用 DataValidation 和单个选项，它按预期工作，但它允许删除没有 validation.Below 的单元格内容是我的示例代码.预先感谢您的帮助.

In my case I need to protect single cell, to achieve this initially I used HSSFCellStyle and setLocked(true) and protect the sheet using Sheet.protectSheet("password"). This will protect non empty cell also so I am using DataValidation with single option, It is working as expected but it allows to delete the cell content without validation.Below is my sample code.Thanks in advance for your help.

 String errorBoxTitle = "Warning";
                    String errorBoxMessage = "Invalid Data";
                    String [] valueArr = {"cellValue"};

                    CellRangeAddressList cellValueAddress = new CellRangeAddressList(row.getRowNum(), row.getRowNum(), cell.getColumnIndex(), cell.getColumnIndex());

                    DVConstraint cellValueConstraint = DVConstraint.createExplicitListConstraint(valueArr);
                    DataValidation cellValueValidation = new HSSFDataValidation(cellValueAddress , cellValueConstraint );
                    cellValueValidation .setSuppressDropDownArrow(true);
                    cellValueValidation .createErrorBox(errorBoxTitle, errorBoxMessage);
                    cellValueValidation .setEmptyCellAllowed(false);
                    sheet.addValidationData(cellValueValidation );

推荐答案

可以锁定或不锁定单元格.如果它被锁定，那么它就不能被改变，也不能被删除.如果一个单元格没有被锁定，那么它当然也可以被删除.因此，由于需要在未锁定的单元格中使用数据验证，因此数据验证不是防止删除的选项.

A cell either can be locked or not locked. If it is locked, then it cannot be changed and also not be deleted. If a cell is not locked, then of course it also can be deleted. So since data validation needs to be used in not locked cells, data validation is not an option to protect against deleting.

如果目标是在工作表受保护时只锁定一些单元格但大多数单元格不应锁定，那么唯一的方法是创建一个设置了 setLocked(false) 的单元格样式并将该单元格样式应用于所有不应锁定的单元格.这是因为 Excel 中的默认设置是在工作表受保护时锁定单元格.

If the goal is to have only some cells locked when the sheet is protected but most of the cells shall be not locked, then the only way is creating a cell style having setLocked(false) set and applying that cell style to all cells which shall be not locked. That is because it is the default in Excel that cells are locked when the sheet is protected.

如果整列中的新单元格不应该被锁定，那么这个notLocked单元格样式可以设置为默认的列样式.

If new cells in whole columns shall be not locked, then this notLocked cell style can be set as the default column style.

在下面的示例中，只有标题单元格 A1:C1 和列中大于 C 的所有单元格被锁定.A2:C4 中的单元格未锁定，因为 notLocked 单元格样式应用于该单元格.此外，A:C 列中大于 4 的行的空单元格不会被锁定，因为 notLocked 单元格样式被应用为默认列样式对于 A:C 列.

In the following example only the header cells A1:C1 and all cells in columns greater than C are locked. The cells in A2:C4 are not locked because the notLocked cell style is applied to that cells. Also the empty cells in columns A:C for rows greater than 4 are not locked because the notLocked cell style is applied as the default column style for columns A:C.

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

public class CreateExcelDefaultColumnStyleNotLocked {

 public static void main(String[] args) throws Exception {

  //Workbook workbook = new XSSFWorkbook(); String filePath = "./CreateExcelDefaultColumnStyleNotLocked.xlsx";
  Workbook workbook = new HSSFWorkbook(); String filePath = "./CreateExcelDefaultColumnStyleNotLocked.xls";
  CellStyle notLocked = workbook.createCellStyle();
  notLocked.setLocked(false);

  Sheet sheet = workbook.createSheet();

  Row row = sheet.createRow(0);
  Cell cell = null;
  for (int c = 0; c < 3; c++) {
   cell = row.createCell(c);
   cell.setCellValue("Col " + (c+1));
  }

  for (int r = 1; r < 4; r++) {
   row = sheet.createRow(r);
   for (int c = 0; c < 3; c++) {
    cell = row.createCell(c);
    cell.setCellValue(r * (c+1));
    cell.setCellStyle(notLocked); 
   }
  }

  sheet.setDefaultColumnStyle(0, notLocked);
  sheet.setDefaultColumnStyle(1, notLocked);
  sheet.setDefaultColumnStyle(2, notLocked);
  sheet.protectSheet("");

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close();
  workbook.close();

 }
}

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