Example usage for org.apache.poi.hssf.record DVRecord getFormula1

Introduction

In this page you can find the example usage for org.apache.poi.hssf.record DVRecord getFormula1.

Prototype

public Ptg[] getFormula1() 

Source Link

Usage

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 {//  w w  w.ja v  a 2 s.  c o 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() {
        /**//from   ww  w  .j a v  a  2  s .  co  m
         * /** 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;
}

---