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

List of usage examples for org.apache.poi.ss.usermodel DataValidation setShowErrorBox

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. DataValidation
  6. setShowErrorBox

Introduction

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

Prototype

public abstract void setShowErrorBox(boolean show);

Source Link

Document

Sets the behaviour when an invalid value is entered

Usage

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

/**
 * It helps to update cell and format the excell based on the formatting defined in ExcelCell.{@link ExcelFormat}
 * //from   www  .ja  v a2s .c  om
 * @param cell
 * @param excell
 * @param style
 * @param font
 */
private static void updateCell(Cell cell, ExcelCell excell, Map<IndexedColors, CellStyle> s_cellStyle,
        Workbook workbook, Font font, Font invisibleFont) {
    if (excell != null) {

        // [1] format cell
        formatCell(workbook, cell, excell, s_cellStyle, font, invisibleFont);

        // [2] set enum
        if (!excell.isConsiderEnum()) {
            if (StringUtils.isNotBlank(excell.getDisplayText())) {
                cell.setCellValue(excell.getDisplayText());
            }
            if (!excell.isMultiSelect() && excell.isNumberValidation()) {
                addNumberValidation(cell);
            }
        } else {
            String[] list = (String[]) excell.getRestriction().getEnumValues()
                    .toArray(new String[excell.getRestriction().getEnumValues().size()]);

            SXSSFSheet sheet = (SXSSFSheet) cell.getSheet();

            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(list);
            CellRangeAddressList regions = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(),
                    cell.getColumnIndex(), cell.getColumnIndex());
            DataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint,
                    regions);
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.createErrorBox("Not Applicable", "Can't change the value");
            dataValidation.setShowErrorBox(true);

            try {
                if (isValidEnumList(list)) {
                    sheet.addValidationData(dataValidation);
                } else {
                    Sheet hidden = null;
                    String hiddenName = "hidden" + getHiddenIndex(excell.getReferenceText());
                    Workbook wBook = cell.getSheet().getWorkbook();
                    if (cell.getSheet().getWorkbook().getSheet(hiddenName) != null) {
                        hidden = wBook.getSheet(hiddenName);
                    } else {
                        hidden = wBook.createSheet(hiddenName);

                        for (int i = 0, length = list.length; i < length; i++) {
                            String name = list[i];
                            Row row = hidden.createRow(i);
                            Cell cell1 = row.createCell(0);
                            cell1.setCellValue(name);
                        }
                        Name namedCell = hidden.getWorkbook().getName(hiddenName);
                        namedCell = namedCell != null ? namedCell : hidden.getWorkbook().createName();
                        namedCell.setNameName(hiddenName);
                        namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + list.length);
                    }

                    dvConstraint = (XSSFDataValidationConstraint) dvHelper
                            .createFormulaListConstraint(hiddenName);
                    dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
                    dataValidation.createErrorBox("Not Applicable", "Can't change the value");
                    dataValidation.setShowErrorBox(true);

                    cell.getSheet().addValidationData(dataValidation);
                    wBook.setSheetHidden(wBook.getSheetIndex(hidden), true);

                }

            } catch (Exception e) {
                String msg = "Excel creation failed while building cell: " + excell.getDisplayText();
                throw new IllegalStateException(msg, e);
            }

            // cell.setCellValue(excelConfig.getDropDownMsg());
        }

    }

}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

private static void addDataValidation(int rowNum, SXSSFSheet sheet) {

    String[] displayNameList = new String[] { "" };
    DataValidationHelper dvHelper = sheet.getDataValidationHelper();
    DataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
            .createExplicitListConstraint(displayNameList);
    CellRangeAddressList regions = new CellRangeAddressList(rowNum, rowNum, 0, 1000);
    DataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
    dataValidation.setSuppressDropDownArrow(false);
    dataValidation.createErrorBox("Not Applicable", "Can't change the value");
    dataValidation.setShowErrorBox(true);
    sheet.addValidationData(dataValidation);
}

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   w  w  w. j  a  va  2s  .co m*/
    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:edu.casetools.rcase.extensions.excel.control.Exporter.java

License:Open Source License

private DataValidation createExcel2007CellStyle(Sheet sheet, String[] possibleValues,
        CellRangeAddressList addressList) {
    DataValidation validation;
    DataValidationConstraint constraint;
    DataValidationHelper validationHelper;
    validationHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);

    constraint = validationHelper.createExplicitListConstraint(possibleValues);
    validation = validationHelper.createValidation(constraint, addressList);
    validation.setEmptyCellAllowed(false);
    validation.setShowErrorBox(true);
    validation.setErrorStyle(0);// w  w w  .ja  va2 s.c  o  m
    return validation;
}

From source file:org.cgiar.ccafs.marlo.action.center.capdev.ParticipantsAction.java

License:Open Source License

public DataValidation setDataValidator(String dataValidationName, DataValidationHelper validationHelper,
        CellRangeAddressList addressList, DataValidationConstraint constraint) {

    DataValidation dataValidation = null;

    constraint = validationHelper.createFormulaListConstraint(dataValidationName);
    dataValidation = validationHelper.createValidation(constraint, addressList);
    dataValidation.setSuppressDropDownArrow(true);
    if (dataValidation instanceof XSSFDataValidation) {
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
    } else {/*from w w w  .j  a  v a2s  . com*/
        dataValidation.setSuppressDropDownArrow(false);
    }

    return dataValidation;
}

From source file:org.cgiar.ccafs.marlo.action.center.capdev.test.java

License:Open Source License

public void createFile() throws FileNotFoundException {
    try {/*from   w  w  w .  j  a v a2  s  .co m*/
        DataValidation dataValidationCountries = null;
        DataValidation dataValidationInstitutions = null;
        DataValidation dataValidationCountryOfInstitutions = null;
        DataValidationConstraint constraintCountries = null;
        DataValidationConstraint constraintInstitutions = null;
        DataValidationConstraint constraintCountryOfInstitutions = null;
        DataValidationHelper validationHelper = null;

        final String path = new File(".").getCanonicalPath();
        final String filePath = "C:\\Users\\logonzalez\\Downloads\\participants-template.xlsm";
        final File file = new File(filePath);
        final FileInputStream fileInput = new FileInputStream(file);
        final XSSFWorkbook wb = new XSSFWorkbook(fileInput);

        final Sheet sheet1 = wb.getSheetAt(0);
        final XSSFSheet sheet2 = wb.getSheet("countries");
        final XSSFSheet sheet3 = wb.getSheet("institutions");

        final String reference = null;
        final String dataValidationCountryName = "countriesLis";
        final String dataValidationInstitutionName = "institutionsList";

        final String[] countries = { "1- Colombia", "2- Brazil", "3- Espenia", "4- Argentina", "5- Aruba",
                "6- Egipto", "7- Panama", "8- Ecuador" };
        final String[] institutions = { "CH- U.chile", "BZ- U.coritiba", "PN- U.panama", "AR- U.de.Palermo",
                "AF- U.delNilo", "EC- U.de.Quito", };

        for (int i = 0; i < countries.length; i++) {
            final Row fila = sheet2.createRow(i);
            final Cell celda = fila.createCell(0);
            final Cell celdaformula = fila.createCell(1);
            // final String formula = "SUM(C1,D1)";
            celda.setCellValue(countries[i]);
            // celdaformula.setCellFormula(formula);
        }

        // sheet2.protectSheet("marlo-ciat");
        // // 3. create named range for an area using AreaReference
        // final Name namedCountry = wb.createName();
        // namedCountry.setNameName(dataValidationCountryName);
        // reference = "countries!$A$1:$A$" + countries.length; // area reference
        // namedCountry.setRefersToFormula(reference);

        for (int i = 0; i < institutions.length; i++) {
            final Row fila = sheet3.createRow(i);
            final Cell celda = fila.createCell(0);
            celda.setCellValue(institutions[i]);

        }

        // final Name namedInstitution = wb.createName();
        // namedInstitution.setNameName(dataValidationInstitutionName);
        // reference = "institutions!$A$1:$A$" + institutions.length; // area reference
        // namedInstitution.setRefersToFormula(reference);
        //
        // sheet3.protectSheet("marlo-ciat");

        validationHelper = sheet1.getDataValidationHelper();
        final CellRangeAddressList addressListCountry = new CellRangeAddressList(11, 1000, 4, 4);
        constraintCountries = validationHelper.createFormulaListConstraint(dataValidationCountryName);
        dataValidationCountries = validationHelper.createValidation(constraintCountries, addressListCountry);
        dataValidationCountries.setSuppressDropDownArrow(true);
        if (dataValidationCountries instanceof XSSFDataValidation) {
            dataValidationCountries.setSuppressDropDownArrow(true);
            dataValidationCountries.setShowErrorBox(true);
        } else {
            dataValidationCountries.setSuppressDropDownArrow(false);
        }

        final CellRangeAddressList addressListInstitution = new CellRangeAddressList(11, 1000, 6, 6);
        constraintInstitutions = validationHelper.createFormulaListConstraint(dataValidationInstitutionName);
        dataValidationInstitutions = validationHelper.createValidation(constraintInstitutions,
                addressListInstitution);
        dataValidationInstitutions.setSuppressDropDownArrow(true);
        if (dataValidationInstitutions instanceof XSSFDataValidation) {
            dataValidationInstitutions.setSuppressDropDownArrow(true);
            dataValidationInstitutions.setShowErrorBox(true);
        } else {
            dataValidationInstitutions.setSuppressDropDownArrow(false);
        }

        final CellRangeAddressList addressListCountryOfInstitution = new CellRangeAddressList(11, 1000, 7, 7);
        constraintCountryOfInstitutions = validationHelper
                .createFormulaListConstraint(dataValidationCountryName);
        dataValidationCountryOfInstitutions = validationHelper.createValidation(constraintCountryOfInstitutions,
                addressListCountryOfInstitution);
        dataValidationCountryOfInstitutions.setSuppressDropDownArrow(true);
        if (dataValidationCountryOfInstitutions instanceof XSSFDataValidation) {
            dataValidationCountryOfInstitutions.setSuppressDropDownArrow(true);
            dataValidationCountryOfInstitutions.setShowErrorBox(true);
        } else {
            dataValidationCountryOfInstitutions.setSuppressDropDownArrow(false);
        }

        sheet1.addValidationData(dataValidationCountries);
        sheet1.addValidationData(dataValidationInstitutions);
        sheet1.addValidationData(dataValidationCountryOfInstitutions);

        FileOutputStream fileOut;

        fileOut = new FileOutputStream("C:\\Users\\logonzalez\\Downloads\\vineet.xlsm");
        wb.write(fileOut);
        fileOut.close();
        wb.close();

    } catch (EncryptedDocumentException | IOException e1) {
        e1.printStackTrace();
    }
}