apache poi with custom constraint
<p>i want to run this custom formula using apache-poi with custom constraints </p>

<pre><code>=OR(IF(ISERROR(FIND(".",A1)),LEN(A1)&gt;0,LEN(MID(A1,FIND(".",A1)+1,25))&lt;3))
</code></pre>

<p>so any idea how to do this ? The code is below...</p>

<p>public class PoiWriteExcelFile {</p>

<pre><code>public static void main(String[] args) {
    try {
        FileOutputStream fileOut = new FileOutputStream("c:/poi-test.xls");
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet worksheet = workbook.createSheet("POI Worksheet");
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFDataFormat df = workbook.createDataFormat();
        CellRangeAddressList addressList = new CellRangeAddressList(0, 4, 3, 3);
        DataValidationHelper helper = worksheet.getDataValidationHelper();
//            helper.create
            helper.createFormulaListConstraint("OR(IF(ISERROR(FIND('.',A1)),LEN(A1)&gt;0,LEN(MID(A1,FIND('.',A1)+1,25))&lt;3))");
            DVConstraint dvConstraint = DVConstraint.createNumericConstraint(ValidationType.DECIMAL, DVConstraint.OperatorType.BETWEEN, "1.00", "1000000000000.00");
            dvConstraint.createCustomFormulaConstraint("OR(IF(ISERROR(FIND('.',A1)),LEN(A1)&gt;0,LEN(MID(A1,FIND('.',A1)+1,25))&lt;3))");
            DataValidation dataValidation = new HSSFDataValidation(addressList,
                    dvConstraint);
            dataValidation.setSuppressDropDownArrow(false);
            worksheet.addValidationData(dataValidation);
            style.setLocked(true);
            //style.setDataFormat(workbook.createDataFormat().getFormat("0.00"));
            style.setDataFormat(df.getFormat("0.000"));
            // index from 0,0... cell A1 is cell(0,0)
        List val = new ArrayList();
        val.add("srNo");
        val.add("name");
        val.add("qty");
        val.add("price");
        val.add("total");
        val.add(1);
        val.add("a");
        val.add(100);
        val.add(0);
        val.add(0);
        val.add(2);
        val.add("b");
        val.add(1000);
        val.add(0);
        val.add(0);
        val.add(3);
        val.add("c");
        val.add(10000);
        val.add(0);
        val.add(0);
        int k = 0;
        int count = 1;
        int countt = 1;
        String formula = "";
        for (int i = 0; i &lt; 4; i++) {
            HSSFRow row1 = worksheet.createRow(i);
            for (int j = 0; j &lt; 5; j++) {
                HSSFCell cellA1 = row1.createCell(j);
                try {
                    //cellA1.setCellValue((Integer) val.get(j + k));
                    cellA1.setCellStyle(style);
                } catch (Exception e) {

                    cellA1.setCellStyle(style);
                }
                if (j == 4 &amp;&amp; i != 0) {
                    count++;
                    formula = "C" + count + "*D" + (count);
                    cellA1.setCellFormula(formula);
                }
                if (j == 3 &amp;&amp; i != 0) {
                    countt++;
                    formula = "ROUNDUP(D"+countt+",3)";
                    cellA1.setCellStyle(style);
                    cellA1.setCellFormula(formula);
                }
            }
            k = k + 5;
        }
        //worksheet.protectSheet("jogi");
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}
</code></pre>

<p>}</p>
