Example usage for org.apache.poi.ss.usermodel DataValidation getRegions

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel DataValidation getRegions.

Prototype

public abstract CellRangeAddressList getRegions();

Source Link

Usage

From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java

License:Apache License

private boolean checkIfIsAppendedDataValidationNeccessary(DataValidation originalDv, int lastRowIndex) {
    CellRangeAddressList originalAl = originalDv.getRegions();
    int originalLastDataRow = 0;
    for (int i = 0; i < originalAl.countRanges(); i++) {
        CellRangeAddress cra = originalAl.getCellRangeAddress(i);
        if (cra.getLastRow() > originalLastDataRow) {
            originalLastDataRow = cra.getLastRow();
        }/*from   ww w .ja va2  s . co m*/
    }
    return (originalLastDataRow < lastRowIndex);
}

From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java

License:Apache License

private void createNewAppendingDataValidationAsCopy(Sheet sheet, DataValidation originalDv, int lastRowIndex) {
    CellRangeAddressList originalAl = originalDv.getRegions();
    CellRangeAddressList appendingAddressList = createNewAppendingCellRangeAddressList(originalAl,
            lastRowIndex);//from  ww  w.j a  va2 s .c  om
    DataValidationHelper dvHelper = sheet.getDataValidationHelper();
    DataValidation newValidation = dvHelper.createValidation(originalDv.getValidationConstraint(),
            appendingAddressList);
    newValidation.setSuppressDropDownArrow(originalDv.getSuppressDropDownArrow());
    newValidation.setShowErrorBox(originalDv.getShowErrorBox());
    newValidation.setShowPromptBox(originalDv.getShowPromptBox());
    newValidation.setEmptyCellAllowed(originalDv.getEmptyCellAllowed());
    newValidation.setErrorStyle(originalDv.getErrorStyle());
    String promptBoxText = originalDv.getPromptBoxText();
    String promptBoxTitle = originalDv.getPromptBoxTitle();
    String errorBoxText = originalDv.getErrorBoxText();
    String errorBoxTitle = originalDv.getErrorBoxTitle();
    if (promptBoxTitle != null && promptBoxText != null) {
        newValidation.createPromptBox(promptBoxTitle, promptBoxText);
    }
    if (errorBoxTitle != null && errorBoxText != null) {
        newValidation.createErrorBox(errorBoxTitle, errorBoxText);
    }
    sheet.addValidationData(newValidation);
}

From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java

License:Apache License

public void createDataValidationsForAppendedRows() {
    List<? extends DataValidation> dvs = sheet.getDataValidations();
    if (dvs != null) {
        if (debug) {
            debug("Original list of DataValidations:");
            int i = 0;
            for (DataValidation dv : dvs) {
                debug("#" + i + " Adress range: "
                        + dv.getRegions().getCellRangeAddresses()[0].formatAsString());
                debug("#" + i + "   Constraint: "
                        + printArray(dv.getValidationConstraint().getExplicitListValues()));
                i++;/*from   w w w .  ja v a2  s. co  m*/
            }
        }
        info("Create new extended DataValidations (last written row: " + (currentRow.getRowNum() + 1)
                + "), number of validations: " + dvs.size());
        for (DataValidation dv : dvs) {
            if (checkIfIsAppendedDataValidationNeccessary(dv, currentRow.getRowNum())) {
                createNewAppendingDataValidationAsCopy(sheet, dv, currentRow.getRowNum());
            }
        }
        if (debug) {
            debug("New appended list of DataValidations:");
            dvs = sheet.getDataValidations();
            int i = 0;
            for (DataValidation dv : dvs) {
                debug("#" + i + " Adress range: "
                        + dv.getRegions().getCellRangeAddresses()[0].formatAsString());
                debug("#" + i + "   Constraint: "
                        + printArray(dv.getValidationConstraint().getExplicitListValues()));
                i++;
            }
        }
    }
}

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

@SuppressWarnings("rawtypes")
private static void writeDataValidations(Sheet sheet, ExcelWriteSheetProcessor sheetProcessor) {
    int templateRowStartIndex = sheetProcessor.getTemplateStartRowIndex();
    int templateRowEndIndex = sheetProcessor.getTemplateEndRowIndex();
    int step = templateRowEndIndex - templateRowStartIndex + 1;
    int rowStartIndex = sheetProcessor.getStartRowIndex();

    Set<Integer> configColIndexSet = new HashSet<Integer>();
    for (Entry<String, Map<Integer, ExcelWriteFieldMappingAttribute>> fieldIndexMapping : sheetProcessor
            .getFieldMapping().export().entrySet()) {
        if (fieldIndexMapping == null || fieldIndexMapping.getValue() == null) {
            continue;
        }//from   w  w w  .j a  v a2s. c om
        for (Entry<Integer, ExcelWriteFieldMappingAttribute> indexProcessorMapping : fieldIndexMapping
                .getValue().entrySet()) {
            if (indexProcessorMapping == null || indexProcessorMapping.getKey() == null) {
                continue;
            }
            configColIndexSet.add(indexProcessorMapping.getKey());
        }
    }

    List<? extends DataValidation> dataValidations = sheet.getDataValidations();
    if (dataValidations != null) {
        for (DataValidation dataValidation : dataValidations) {
            if (dataValidation == null) {
                continue;
            }
            CellRangeAddressList cellRangeAddressList = dataValidation.getRegions();
            if (cellRangeAddressList == null) {
                continue;
            }

            CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.getCellRangeAddresses();
            if (cellRangeAddresses == null || cellRangeAddresses.length == 0) {
                continue;
            }

            CellRangeAddressList newCellRangeAddressList = new CellRangeAddressList();
            boolean validationContains = false;
            for (CellRangeAddress cellRangeAddress : cellRangeAddresses) {
                if (cellRangeAddress == null) {
                    continue;
                }
                if (templateRowEndIndex < cellRangeAddress.getFirstRow()
                        || templateRowStartIndex > cellRangeAddress.getLastRow()) {// specify row
                    continue;
                }
                for (Integer configColIndex : configColIndexSet) {
                    if (configColIndex < cellRangeAddress.getFirstColumn()
                            || configColIndex > cellRangeAddress.getLastColumn()) {// specify column
                        continue;
                    }
                    if (templateRowStartIndex == templateRowEndIndex) {
                        newCellRangeAddressList.addCellRangeAddress(rowStartIndex, configColIndex,
                                sheet.getLastRowNum(), configColIndex);
                        validationContains = true;
                    } else {
                        int start = cellRangeAddress.getFirstRow() > templateRowStartIndex
                                ? cellRangeAddress.getFirstRow()
                                : templateRowStartIndex;
                        int end = cellRangeAddress.getLastRow() < templateRowEndIndex
                                ? cellRangeAddress.getLastRow()
                                : templateRowEndIndex;
                        long lastRow = sheet.getLastRowNum();
                        if (lastRow > end) {
                            long count = (lastRow - templateRowEndIndex) / step;
                            int i = templateRowEndIndex;
                            for (; i < count; i++) {
                                newCellRangeAddressList.addCellRangeAddress(start + i * step, configColIndex,
                                        end + i * step, configColIndex);
                                validationContains = true;
                            }
                            long _start = start + i * step;
                            if (_start <= lastRow) {
                                long _end = end + i * step;
                                _end = _end < lastRow ? _end : lastRow;
                                newCellRangeAddressList.addCellRangeAddress((int) _start, configColIndex,
                                        (int) _end, configColIndex);
                                validationContains = true;
                            }
                        }
                    }
                }
            }
            if (validationContains) {
                DataValidation newDataValidation = sheet.getDataValidationHelper()
                        .createValidation(dataValidation.getValidationConstraint(), newCellRangeAddressList);
                sheet.addValidationData(newDataValidation);
            }
        }
    }
}