Example usage for org.apache.poi.hssf.usermodel DVConstraint createFormulaListConstraint

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel DVConstraint createFormulaListConstraint.

Prototype

public static DVConstraint createFormulaListConstraint(String listFormula)

Source Link

Usage

From source file:com.dituiba.excel.DefaultValidateAdapter.java

License:Apache License

/**
 * ??//from   w ww.j  a  v  a 2s  .  c om
 * @param config
 * @param sheet
 * @param columnIndex
 * @param valueSet
 */
protected void createDicCodeSheet(DicValidateConfig config, Sheet sheet, int columnIndex,
        Set<String> valueSet) {
    Workbook workbook = sheet.getWorkbook();
    Sheet codeSheet = workbook.getSheet(DICCODE_SHEET_NAME);
    if (codeSheet == null) {
        log.debug("?Sheet?Sheet");
        codeSheet = workbook.createSheet(DICCODE_SHEET_NAME);
    }
    int codeIndex = config.columnName() - 'A';
    log.debug("codeIndex{}", codeIndex);
    if (codeSheet.getRow(0) == null || codeSheet.getRow(0).getCell(codeIndex) == null) {
        log.debug("????");
        int i = 0;
        for (String dic : valueSet) {
            Row row = codeSheet.getRow(i);
            if (row == null)
                row = codeSheet.createRow(i);
            Cell cell = row.createCell(codeIndex);
            cell.setCellValue(dic);
            i++;
        }
    } else {
        log.debug("????");
    }
    Name name = workbook.getName(config.columnName() + "");
    if (name == null || name.isDeleted()) {
        log.debug("?Name?Name");
        name = workbook.createName();
        name.setNameName(config.columnName() + "");
    }
    name.setRefersToFormula(DICCODE_SHEET_NAME + "!$" + config.columnName() + "$1:$" + config.columnName() + "$"
            + valueSet.size());
    DVConstraint constraint = DVConstraint.createFormulaListConstraint(name.getNameName());
    CellRangeAddressList addressList = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE,
            columnIndex, columnIndex);
    HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
    workbook.setSheetHidden(workbook.getSheetIndex(DICCODE_SHEET_NAME), Workbook.SHEET_STATE_VERY_HIDDEN);
    setValidationTip(validation, config);
    sheet.addValidationData(validation);
    log.debug("??");
}

From source file:org.sysmodb.PatchedPoi.java

License:BSD License

public List<HSSFDataValidation> getValidationData(final HSSFSheet sheet, final HSSFWorkbook workbook) {
    final List<HSSFDataValidation> dataValidation = new ArrayList<HSSFDataValidation>();
    try {/*ww w. j a  va  2s. co m*/
        DataValidityTable dvt = sheet.getDataValidityTable();
        dvt.visitContainedRecords(new RecordAggregate.RecordVisitor() {
            /**
             * /** Implementors may call non-mutating methods on Record r.
             * 
             * @param r must not be <code>null</code>
             *            
             */
            public void visitRecord(Record r) {
                if (r instanceof DVRecord) {
                    DVRecord dvRecord = (DVRecord) r;
                    CellRangeAddressList cellRangeAddressList = dvRecord.getCellRangeAddress();
                    int validationType = dvRecord.getDataType();
                    if (validationType == DVConstraint.ValidationType.LIST) {
                        Formula f1 = dvRecord.getFormula1();
                        String formula1 = getStringFromPtgTokens(f1.getTokens(), workbook);
                        DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint(formula1);
                        HSSFDataValidation validation = new HSSFDataValidation(cellRangeAddressList,
                                dvConstraint);
                        dataValidation.add(validation);
                    } else if (validationType == DVConstraint.ValidationType.INTEGER
                            || validationType == DVConstraint.ValidationType.DECIMAL
                            || validationType == DVConstraint.ValidationType.TEXT_LENGTH) {
                        Formula f1 = dvRecord.getFormula1();
                        Formula f2 = dvRecord.getFormula2();
                        String formula1 = getStringFromPtgTokens(f1.getTokens(), workbook);
                        String formula2 = getStringFromPtgTokens(f2.getTokens(), workbook);
                        if (!formula2.isEmpty()) {
                            int comparison = dvRecord.getConditionOperator();
                            DVConstraint dvConstraint = DVConstraint.createNumericConstraint(validationType,
                                    comparison, formula1, formula2);
                            HSSFDataValidation validation = new HSSFDataValidation(cellRangeAddressList,
                                    dvConstraint);
                            dataValidation.add(validation);
                        }
                    }
                }
            }
        });
    } catch (IllegalStateException e) {
        logger.warning("Unable to read data validations - " + e.getMessage());
    }

    return dataValidation;
}

From source file:uk.ac.manchester.cs.owl.semspreadsheets.model.hssf.impl.PatchedPoi.java

License:BSD License

public List<HSSFDataValidation> getValidationData(final HSSFSheet sheet, final HSSFWorkbook workbook) {
    final List<HSSFDataValidation> dataValidation = new ArrayList<HSSFDataValidation>();
    DataValidityTable dvt = sheet.getDataValidityTable();
    dvt.visitContainedRecords(new RecordAggregate.RecordVisitor() {
        /**/*  w ww  . j  a  v a 2  s.  c om*/
         * /** Implementors may call non-mutating methods on Record r.
         * 
         * @param r must not be <code>null</code>
         *            
         */
        public void visitRecord(Record r) {
            if (r instanceof DVRecord) {
                DVRecord dvRecord = (DVRecord) r;
                CellRangeAddressList cellRangeAddressList = dvRecord.getCellRangeAddress();
                int validationType = dvRecord.getDataType();
                if (validationType == DVConstraint.ValidationType.LIST) {
                    Formula f1 = dvRecord.getFormula1();
                    String formula1 = getStringFromPtgTokens(f1.getTokens(), workbook);
                    DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint(formula1);
                    HSSFDataValidation validation = new HSSFDataValidation(cellRangeAddressList, dvConstraint);
                    dataValidation.add(validation);
                } else if (validationType == DVConstraint.ValidationType.INTEGER
                        || validationType == DVConstraint.ValidationType.DECIMAL
                        || validationType == DVConstraint.ValidationType.TEXT_LENGTH) {
                    Formula f1 = dvRecord.getFormula1();
                    Formula f2 = dvRecord.getFormula2();
                    String formula1 = getStringFromPtgTokens(f1.getTokens(), workbook);
                    String formula2 = getStringFromPtgTokens(f2.getTokens(), workbook);
                    if (!formula2.isEmpty()) { //potentially caused by macros, which leads to a error when attempting to create the constraint
                        int comparison = dvRecord.getConditionOperator();
                        DVConstraint dvConstraint = DVConstraint.createNumericConstraint(validationType,
                                comparison, formula1, formula2);
                        HSSFDataValidation validation = new HSSFDataValidation(cellRangeAddressList,
                                dvConstraint);
                        dataValidation.add(validation);
                    }

                } else if (validationType == DVConstraint.ValidationType.FORMULA) {
                    Formula f1 = dvRecord.getFormula1();

                    String formula1 = getStringFromPtgTokens(f1.getTokens(), workbook);
                    //doesn't contain the full formula, including cell and comparison - but should contain all the information needed. If not, getStringFromPtgTokens
                    //needs updating to handle the extra token types
                    DVConstraint dvConstraint = DVConstraint.createCustomFormulaConstraint(formula1);

                    HSSFDataValidation validation = new HSSFDataValidation(cellRangeAddressList, dvConstraint);
                    dataValidation.add(validation);
                }

            }
        }
    });
    return dataValidation;
}