Question

我要用有自定义限制的 apache-poi 运行此自定义公式

=OR(IF(ISERROR(FIND(".",A1)),LEN(A1)>0,LEN(MID(A1,FIND(".",A1)+1,25))<3))

代码在下面...

公共阶级 PoiWrite ExcelFile {

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)>0,LEN(MID(A1,FIND( . ,A1)+1,25))<3))");
            DVConstraint dvConstraint = DVConstraint.createNumericConstraint(ValidationType.DECIMAL, DVConstraint.OperatorType.BETWEEN, "1.00", "1000000000000.00");
            dvConstraint.createCustomFormulaConstraint("OR(IF(ISERROR(FIND( . ,A1)),LEN(A1)>0,LEN(MID(A1,FIND( . ,A1)+1,25))<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 < 4; i++) {
            HSSFRow row1 = worksheet.createRow(i);
            for (int j = 0; j < 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 && i != 0) {
                    count++;
                    formula = "C" + count + "*D" + (count);
                    cellA1.setCellFormula(formula);
                时 时
                if (j == 3 && 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();
    时 时

时 时

时时

Answer 1

使用此方法 org.apache.poi.hssf.usermodel.HSSFCell.setCellValue(String) 。您的单元格应该是型号 org.apache.poi.s.usermodel.Cell.CELL_TYPE_FORMULA 。

Answer 2

http://poi.apache.org/apidocs/org/apache/poi/hsf/usermodel/HSSFFFFFormulaEvaluator.html" rel="nofollow"\code>org.apache.poi.hsf.usermodel.HSSFFFormulaEvaluator

< 强度 > 示例 :

HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator( currentWorkbook );  

HSSFCell cellA1 = currentRow.createCell( 0, Cell.CELL_TYPE_NUMERIC );  
cellA1.setCellValue( 6 );  

HSSFCell cellB1 = currentRow.createCell( 1, Cell.CELL_TYPE_FORMULA );  
cellB1.setCellFormula( "power( A1, 2 )" );  

// returns 0, because formula is not evaluated yet
// prints: cellB1.getNumericCellValue() = 0.0
System.out.println( "cellB1.getNumericCellValue() = " + cellB1.getNumericCellValue() );

HSSFCell cellC1 = currentRow.createCell( 2, Cell.CELL_TYPE_NUMERIC );  
// the base cell retains formula and returns evaluated cell value  
CellValue evaluatedCellValue = formulaEvaluator.evaluate( cellB1 );  
cellC1.setCellValue( evaluatedCellValue.getNumberValue() );  

// if you want to remove formula and write its evaluated value use the following.  
// removes formula and overwrites the cell with resulting value.  
formulaEvaluator.evaluateInCell( cellB1 );

如欲了解更多详情,请阅读上述文件。

这样你就可以评估和进一步处理你想要的公式。

友情链接