Example usage for org.apache.poi.xssf.streaming SXSSFSheet addValidationData

Introduction

In this page you can find the example usage for org.apache.poi.xssf.streaming SXSSFSheet addValidationData.

Prototype

@Override
public void addValidationData(DataValidation dataValidation) 

Source Link

Document

Creates a data validation object

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  w w  w . j a va2s .com*/
 * @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);
}

---