此问题包含多个不同的问题。
首先，我们需要获取工作表的数据验证，然后为每个数据验证获取数据验证所适用的Excel单元格范围。 如果该单元格位于该单元格范围之一中，并且数据验证是列表约束，则进行进一步处理。 否则返回默认值。
如果我们有一个明确的列表，例如“ item1，item2，item3，...”，则返回此列表。
否则，如果我们有一个公式来创建列表，并且公式1是对Excel中定义的名称的引用，则获取该名称所引用的Excel单元格范围。 获取该单元格范围内的所有单元格，并将其值放入数组中并返回此值。
完整的例子。 ExcelWorkbook在第一个工作表单元格D1包含数据Validation。
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[] getDataValidationListValues(Sheet sheet, Cell cell) {
DataFormatter dataFormatter = new DataFormatter();
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
Workbook workbook = sheet.getWorkbook();
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 areaReference = new AreaReference(refersToFormula,
(workbook instanceof XSSFWorkbook)?SpreadsheetVersion.EXCEL2007:SpreadsheetVersion.EXCEL97
);
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];
listValues[i] = dataFormatter.formatCellValue(
workbook.getSheet(cellReference.getSheetName()).getRow(cellReference.getRow()).getCell(cellReference.getCol())
);
}
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();
}
}
