java中mypoiexception,java - 如何使用Poi获取Java中单元格的数据验证源？ - 堆栈内存溢出...

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

首先，我们需要获取工作表的数据验证，然后为每个数据验证获取数据验证所适用的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();

}

}