java - 使用 Apache POI 从 Excel 单元格中提取数据验证

Question

我正在尝试使用 Apache POI 从 Excel 单元格中获取预先存在的数据验证信息。例如，如果单元格已经具有仅允许 0 到 100 之间的整数的数据验证约束，我希望能够从单元格中提取该信息。

关于Data Validation section of the Quick Guide ，这些示例似乎只涵盖向单元格添加验证，而不是检索它。我找到了DataValidationEvaluator对象似乎可以通过其 getValidationForCell 方法执行我正在寻找的操作。但是，我无法弄清楚如何正确实例化该对象的实例，因为它的构造函数需要一个 WorkbookEvaluatorProvider ，根据其 official documentation ，仅供内部 POI 使用。

对此的任何帮助或指导将不胜感激!也许你们中的某个人会知道一种更简单、更好的方法来获取此信息。这是一段代码，演示了我想要做的事情:

// The impossible (?) bit
WorkbookEvaluatorProvider wep = ...???...

// Easy through here
DataValidationEvaluator dve = new DataValidationEvaluator(wb, wep)
CellReference cRef = aRef.getFirstCell();
DataValidation dv = dve.getValidationForCell(cRef);

Answer 1

我们可以有一个方法从给定的Cell中获取数据验证约束。

首先，我们需要获取工作表的数据验证，然后对于每个数据验证，获取数据验证适用的 Excel 单元格范围。如果单元格位于该单元格范围之一，则返回该验证约束。

示例:

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

import java.io.FileInputStream;

import java.util.List;
import java.util.Arrays;

public class ExcelGetDataValidationConstraints {

 static DataValidationConstraint getDataValidationConstraint(Cell cell) {
  Sheet sheet = cell.getSheet(); 
  List<? extends DataValidation> dataValidations = sheet.getDataValidations(); // get sheet's data validations
  for (DataValidation dataValidation : dataValidations) {
   CellRangeAddressList addressList = dataValidation.getRegions(); // get Excel cell ranges the data validation applies to
   CellRangeAddress[] addresses = addressList.getCellRangeAddresses();
   for (CellRangeAddress address : addresses) {
    if (address.isInRange(cell)) { // if the cell is in that cell range
     DataValidationConstraint constraint = dataValidation.getValidationConstraint();
     return constraint; // return this
    }
   } 
  }
  return null; // per default return null
 }

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

  //String filePath = "ExcelWorkbook.xls";
  String filePath = "ExcelWorkbook.xlsx";

  Workbook workbook = WorkbookFactory.create(new FileInputStream(filePath));
  Sheet sheet = workbook.getSheetAt(0);

  for (Row row : sheet) {
   for (Cell cell : row) {
    DataValidationConstraint constraint = getDataValidationConstraint(cell);
    System.out.println(cell.getAddress());
    System.out.println(constraint);
    if (constraint != null) {
     System.out.println("DataValidationConstraint.ValidationType: " + constraint.getValidationType());
      //https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/DataValidationConstraint.ValidationType.html
     System.out.println("Formula1: " + constraint.getFormula1());
     System.out.println("DataValidationConstraint.OperatorType: " + constraint.getOperator()); 
      //https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/DataValidationConstraint.OperatorType.html
     System.out.println("Formula2: " + constraint.getFormula2());
     String[] listValues = constraint.getExplicitListValues();
     if (listValues != null) System.out.println("List values: " + Arrays.asList(listValues));
    }
    System.out.println();
   }
  }

  workbook.close();
 }
}

参见How to get datavalidation source for a cell in java using poi?用于处理不同类型的列表约束。

<小时/>

回答有关使用 WorkbookEvaluatorProvider 的问题:

WorkbookEvaluatorProvider 是一个由所有 FormulaElevator 实现的接口(interface)。因此，要获取 WorkbookEvaluatorProvider，我们需要创建一个 FormulaEvaluator 。这可以使用 CreationHelper.html#createFormulaEvaluator 来完成。 CreationHelper可以从Workbook中获取。

所以你所描述的可以使用方法来完成:

DataValidation getDataValidationFromDataValidationEvaluator (Cell cell) {
 Sheet sheet = cell.getSheet(); 
 Workbook workbook = sheet.getWorkbook();
 WorkbookEvaluatorProvider workbookEvaluatorProvider =
   (WorkbookEvaluatorProvider)workbook.getCreationHelper().createFormulaEvaluator();
 DataValidationEvaluator dataValidationEvaluator = new DataValidationEvaluator(workbook, workbookEvaluatorProvider);
 DataValidation dataValidation = dataValidationEvaluator.getValidationForCell(new CellReference(cell));
 return dataValidation;
}

完整示例:

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

import java.io.FileInputStream;

import java.util.List;
import java.util.Arrays;

public class ExcelGetDataValidationFromDataValidationEvaluator {

 static DataValidation getDataValidationFromDataValidationEvaluator (Cell cell) {
  Sheet sheet = cell.getSheet(); 
  Workbook workbook = sheet.getWorkbook();
  WorkbookEvaluatorProvider workbookEvaluatorProvider =
   (WorkbookEvaluatorProvider)workbook.getCreationHelper().createFormulaEvaluator();
  DataValidationEvaluator dataValidationEvaluator = new DataValidationEvaluator(workbook, workbookEvaluatorProvider);
  DataValidation dataValidation = dataValidationEvaluator.getValidationForCell(new CellReference(cell));
  return dataValidation;
 }

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

  //String filePath = "ExcelWorkbook.xls";
  String filePath = "ExcelWorkbook.xlsx";

  Workbook workbook = WorkbookFactory.create(new FileInputStream(filePath));
  Sheet sheet = workbook.getSheetAt(0);

  for (Row row : sheet) {
   for (Cell cell : row) {
    System.out.println(cell.getAddress());
    DataValidation dataValidation = getDataValidationFromDataValidationEvaluator(cell);
    if (dataValidation!=null) {
     DataValidationConstraint constraint = dataValidation.getValidationConstraint();
     System.out.println(dataValidation);
     System.out.println(constraint);
     if (constraint != null) {
      System.out.println("DataValidationConstraint.ValidationType: " + constraint.getValidationType());
       //https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/DataValidationConstraint.ValidationType.html
      System.out.println("Formula1: " + constraint.getFormula1());
      System.out.println("DataValidationConstraint.OperatorType: " + constraint.getOperator()); 
       //https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/DataValidationConstraint.OperatorType.html
      System.out.println("Formula2: " + constraint.getFormula2());
      String[] listValues = constraint.getExplicitListValues();
      if (listValues != null) System.out.println("List values: " + Arrays.asList(listValues));
     }
    }
    System.out.println();
   }
  }

  workbook.close();
 }
}

值得测试哪种方法性能更高。