如何使用 poi 在 java 中获取单元格的数据验证源? [英] How to get datavalidation source for a cell in java using poi?

问题描述

我在一个 Excel 表中定义了一个值列表 my_list 如下:

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

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

In another excel sheet, I reference for some cells to that list sothat this list is shown as dropdown in the cell as follows:

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

Using poi, I go throw excel sheet rows/columns and read cells for cell.

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

I get value of cells using method:

cell.getStringCellValue()

我的问题是如何从单元格中获取列表 my_list 的名称?

My question is how to get the name of the list my_list from the cell?

推荐答案

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

This problem contains multiple different problems.

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

First we need get sheet's data validations and then for each data validation get Excel cell ranges the data validation applies to. If the cell is in one of that cell ranges and if data validation is a list constraint then do further proceedings. Else return a default value.

如果我们有一个明确的列表，比如item1, item2, item3, ..."然后返回这个.

If we have a explicit list like "item1, item2, item3, ..." then return this.

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

Else if we have a formula creating the list and is formula1 a area reference to a range in same sheet, then get all cells in that cell range and put their values in an array and return this.

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

Else if we have a formula creating the list and is formula1 a reference to a defined name in Excel, then get the Excel cell range the name refers to. Get all cells in that cell range and put their values in an array and return this.

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

Complete Example. The ExcelWorkbook contains the data validation in first sheet cell 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 仅在 Excel 2007 级别.

Note: Current Excel versions allow data validation list reference to be a direct area reference to another sheet without using a named range. But this is nothing what apache poi can get. Apache poi is on Excel 2007 level only.

这篇关于如何使用 poi 在 java 中获取单元格的数据验证源?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！