Exemple #1
0
  public List<String> getCellConstraints(Cell cell) {
    for (DataValidation _validation : getValidations())
      for (CellRangeAddress _region : _validation.getRegions().getCellRangeAddresses())
        if (_region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
          if (_validation.getValidationConstraint().getExplicitListValues() != null)
            return Arrays.asList(_validation.getValidationConstraint().getExplicitListValues());
          else if (_validation.getValidationConstraint().getFormula1() != null) {
            String formula = _validation.getValidationConstraint().getFormula1().split("\"")[1];
            String[] _names = formula.split("!");
            String _sheetName = _names[0];
            String _arrName = _names[1];
            int sheetIndex = evalWorkbook.getSheetIndex(_sheetName);

            EvaluationName nm = evalWorkbook.getName(_arrName, sheetIndex);
            if (nm == null || !nm.isRange()) {
              throw new RuntimeException(
                  "Specified name '" + _arrName + "' is not a range as expected.");
            }

            OperationEvaluationContext ec =
                new OperationEvaluationContext(
                    new WorkbookEvaluator(evalWorkbook, null, null),
                    evalWorkbook,
                    defaultSheet,
                    cell.getRowIndex(),
                    cell.getColumnIndex(),
                    null);

            Ptg[] ptgs = nm.getNameDefinition();
            if (ptgs.length == 1 && ptgs[0] instanceof Area3DPtg) {
              ValueEval result = ec.getArea3DEval((Area3DPtg) ptgs[0]);

              if (result instanceof AreaEvalBase) {
                AreaEvalBase _area = (AreaEvalBase) result;
                ArrayList<String> resultStrings = new ArrayList<>();
                for (int i = _area.getFirstRow(); i <= _area.getLastRow(); i++) {
                  String value =
                      getStringValue(
                          new CellCoord(_area.getFirstSheetIndex(), _area.getFirstColumn(), i));
                  if (value != null && value.length() > 0) resultStrings.add(value);
                }

                return resultStrings;
              }

              return null;
            }
          }
        }

    return null;
  }