如何使用 poi 获取 java 中单元格的数据验证源？

I have defined a list of valuses my_list in one excel sheet as follow:

在另一个 Excel 工作表中，我将某些单元格引用到该列表，以便该列表在单元格中显示为下拉列表，如下所示：

使用 poi，我去抛出 Excel 工作表行/列并读取单元格的单元格。

我使用方法获取单元格的值：

cell.getStringCellValue()

我的问题是如何获取列表的名称my_list来自细胞？

这个问题包含多个不同的问题。

首先，我们需要获取工作表的数据验证，然后对于每个数据验证，获取数据验证适用的 Excel 单元格范围。如果该单元格位于该单元格范围之一中并且数据验证是列表约束，则执行进一步的处理。否则返回默认值。

如果我们有一个明确的列表，如“item1，item2，item3，...”，则返回该列表。

否则，如果我们有一个创建列表的公式，并且 Formula1 是对同一工作表中的范围的区域引用，则获取该单元格范围中的所有单元格并将它们的值放入数组中并返回。

否则，如果我们有一个创建列表的公式，并且 Formula1 是对 Excel 中已定义名称的引用，则获取该名称引用的 Excel 单元格区域。获取该单元格区域中的所有单元格并将它们的值放入数组中并返回。

完整示例。这ExcelWorkbook包含第一个工作表单元格中的数据验证D1.

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

import java.io.FileInputStream;

import java.util.List;

public class ExcelGetDataValidationList {

 static String[] getDataFromAreaReference(AreaReference areaReference, Sheet sheet) {
  DataFormatter dataFormatter = new DataFormatter();
  Workbook workbook = sheet.getWorkbook();
  CellReference[] cellReferences = areaReference.getAllReferencedCells(); // get all cells in that cell range
  String[] listValues = new String[cellReferences.length]; // and put their values in an array
  for (int i = 0 ; i < cellReferences.length; i++) {
   CellReference cellReference = cellReferences[i];
   if (cellReference.getSheetName() == null) {
    listValues[i] = dataFormatter.formatCellValue(
     sheet.getRow(cellReference.getRow()).getCell(cellReference.getCol())
    );
   } else {
    listValues[i] = dataFormatter.formatCellValue(
     workbook.getSheet(cellReference.getSheetName()).getRow(cellReference.getRow()).getCell(cellReference.getCol())
    );
   }
  }
  return listValues;
 }

 static String[] getDataValidationListValues(Sheet sheet, Cell cell) {
  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();
     if (constraint.getValidationType() == DataValidationConstraint.ValidationType.LIST) { // if it is a list constraint

      String[] explicitListValues = constraint.getExplicitListValues(); // if we have a explicit list like "item1, item2, item3, ..."
      if (explicitListValues != null) return explicitListValues; // then  return this 

      String formula1 = constraint.getFormula1(); // else if we have a formula creating the list
System.out.println(formula1);

      Workbook workbook = sheet.getWorkbook();
      AreaReference areaReference = null;

      try { // is formula1 a area reference?
       areaReference = new AreaReference(formula1, 
        (workbook instanceof XSSFWorkbook)?SpreadsheetVersion.EXCEL2007:SpreadsheetVersion.EXCEL97
       );
       String[] listValues = getDataFromAreaReference(areaReference, sheet); //get data from that area reference
       return listValues; // and return this
      } catch (Exception ex) {
        //ex.printStackTrace();
        // do nothing as creating AreaReference had failed
      }

      List<? extends Name> names = workbook.getNames(formula1); // is formula1 a reference to a defined name in Excel?
      for (Name name : names) {
       String refersToFormula = name.getRefersToFormula(); // get the Excel cell range the name refers to
       areaReference = new AreaReference(refersToFormula, 
        (workbook instanceof XSSFWorkbook)?SpreadsheetVersion.EXCEL2007:SpreadsheetVersion.EXCEL97
       );
       String[] listValues = getDataFromAreaReference(areaReference, sheet); //get data from that area reference
       return listValues; // and return this
      } 
     }  
    }
   } 
  }
  return new String[]{}; // per default return an empy array
 }

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

  Row row = sheet.getRow(0); if (row == null) row = sheet.createRow(0); // row 1
  Cell cell = row.getCell(3); if (cell == null) cell = row.createCell(3); // cell D1
  System.out.println(cell.getAddress() + ":" + cell);

  String[] dataValidationListValues = getDataValidationListValues(sheet, cell);

  for (String dataValidationListValue : dataValidationListValues) {
   System.out.println(dataValidationListValue);
  }

  workbook.close();
 }
}

注意：当前的 Excel 版本允许数据验证列表引用成为对另一个工作表的直接区域引用，而无需使用命名范围。但这没什么apache poi可以得到。Apache poi is on Excel 2007仅级别。