apache poi - How to get datavalidation source for a cell in java using poi?

Question

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

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

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

I get value of cells using method:

cell.getStringCellValue()

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

Answer 1

This problem contains multiple different problems.

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.

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

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.

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.

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

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.