Java 类org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator 实例源码

public List<String> parsePlanungSheet(final HSSFWorkbook wb) throws ParserException {

        // ExcelService anlegen
        this.exlSv = new ExcelOutputService(wb);

        // Parameter pruefen
        if (wb == null) {
            throw new IllegalArgumentException("Workbook must not be null");
        }

        List<String> lines = new ArrayList<String>();

        // Betreffendes Sheet anhand des Indexes ermitteln (funkt. auch noch
        // wenn mal das Tabellenblatt umbenannt wird)
        HSSFSheet sheet = null;
        sheet = wb.getSheet(ExcelNodeService.CONST_SHEETNNAME_PLANUNG);
        if (sheet == null) {
            sheet = wb.createSheet(ExcelNodeService.CONST_SHEETNNAME_PLANUNG);
        }

        // alle Formeln berechnen
        @SuppressWarnings("deprecation")
        HSSFFormulaEvaluator formulaEval = new HSSFFormulaEvaluator(sheet, wb);
        int startRownNum = ExcelNodeService.CONST_PLANUNG_ROUW_UE;
        // Ue;
        startRownNum++;
        for (int rowNum = startRownNum; rowNum <= sheet.getLastRowNum(); rowNum++) {
            String line = this.parsePlanungLine(sheet, formulaEval, rowNum);
            if (line != null) {
                lines.add(line);
            }
        }

        return lines;
    }

public XlsDataSource(InputStream in) throws IOException {
  HSSFWorkbook workbook = new HSSFWorkbook(in);
  HSSFFormulaEvaluator evaluator = workbook.getCreationHelper()
      .createFormulaEvaluator();
  evaluator.setIgnoreMissingWorkbooks(true);
  init(workbook, evaluator);
}

public ExcelFormulaEvaluator (@Nonnull final Workbook aWB, @Nullable final IStabilityClassifier aStability)
{
  m_aEvaluator = aWB instanceof HSSFWorkbook ? new HSSFFormulaEvaluator ((HSSFWorkbook) aWB, aStability)
                                             : XSSFFormulaEvaluator.create ((XSSFWorkbook) aWB, aStability, null);
}

public JSONObject parseXLS(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException{
            JSONObject jobj=new JSONObject();
                    POIFSFileSystem fs      =
            new POIFSFileSystem(new FileInputStream(filename));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
            HSSFSheet sheet = wb.getSheetAt(sheetNo);

            int startRow=0;
            int maxRow=sheet.getLastRowNum();
            int maxCol=0;
            int noOfRowsDisplayforSample = 20;
            if(noOfRowsDisplayforSample > sheet.getLastRowNum()){
                noOfRowsDisplayforSample = sheet.getLastRowNum();
            }

            JSONArray jArr=new JSONArray();
            try {
                for(int i=0;i <= noOfRowsDisplayforSample;i++) {
                    HSSFRow row = sheet.getRow(i);
                    JSONObject obj=new JSONObject();
                    JSONObject jtemp1 = new JSONObject();
                    if(row==null){
                        continue;
                    }
                    if(i==0) {
                        maxCol=row.getLastCellNum();
                    }
                    for(int cellcount=0; cellcount<maxCol; cellcount++){
                        HSSFCell cell = row.getCell(cellcount);
                        CellReference cref = new CellReference(i, cellcount);
                        String colHeader=cref.getCellRefParts()[2];
                        String val=null;
                        if(cell!=null){
                            switch(cell.getCellType()){
                                case HSSFCell.CELL_TYPE_NUMERIC: 
                                     if(HSSFDateUtil.isCellDateFormatted(cell)){
                                        val=Double.toString(cell.getNumericCellValue());
                                        java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                                        DateFormat sdf = new SimpleDateFormat(df);
                                        val = sdf.format(date1);
                                     }else{
                                        val=dfmt.format(cell.getNumericCellValue());
                                     }
                                     break;
                                case HSSFCell.CELL_TYPE_STRING: val=cleanHTML(cell.getRichStringCellValue().getString()); break;
                            }
                        }

                        if(i==0){ // List of Headers (Consider first row as Headers)
                            if(val!=null){
                                jtemp1 = new JSONObject();
                                jtemp1.put("header", val==null?"":val);
                                jtemp1.put("index", cellcount);
                                jobj.append("Header", jtemp1);
                            }
                        }
                        obj.put(colHeader,val);
                    }
//                    if(obj.length()>0){ //Don't show blank row in preview grid[SK]
                        jArr.put(obj);
//                    }
                }
            } catch(Exception ex) {
               Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
            }
            jobj.put("startrow", startRow);
            jobj.put("maxrow", maxRow);
            jobj.put("maxcol", maxCol);
            jobj.put("index", sheetNo);
            jobj.put("data", jArr);
            jobj.put("filename", filename);

            jobj.put("msg", "XLS has been successfully uploaded");
            jobj.put("lsuccess", true);
            jobj.put("valid", true);
            return jobj;
    }

public JSONObject parseXLS1(String filename, int sheetNo,int startindex) throws FileNotFoundException, IOException, JSONException{
        JSONObject jobj=new JSONObject();
                POIFSFileSystem fs      =
        new POIFSFileSystem(new FileInputStream(filename));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
        HSSFSheet sheet = wb.getSheetAt(sheetNo);
        ArrayList<String> arr = new ArrayList<String>();
        int startRow=0;
        int maxRow=sheet.getLastRowNum();
        int maxCol=0;

        JSONArray jArr=new JSONArray();
        try {
            for(int i=startindex;i<=sheet.getLastRowNum();i++) {
                HSSFRow row = sheet.getRow(i);
                JSONObject obj=new JSONObject();
                JSONObject jtemp1 = new JSONObject();
                if(row==null){
                    continue;
                }
                if(i==startindex){
                    maxCol=row.getLastCellNum();
                }
                for(int j=0; j<maxCol; j++){
                    HSSFCell cell = row.getCell(j);
                    String val=null;
                    if(cell==null){
                           arr.add(val);
                           continue;
                    };
                    String colHeader=new CellReference(i, j).getCellRefParts()[2];
                    switch(cell.getCellType()){
                        case HSSFCell.CELL_TYPE_NUMERIC: 
                             if(HSSFDateUtil.isCellDateFormatted(cell)){
                                val=Double.toString(cell.getNumericCellValue());
                                java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                                DateFormat sdf = new SimpleDateFormat(df);
                                val = sdf.format(date1);
                             }else{
                                val=dfmt.format(cell.getNumericCellValue());
                             }
                             break;
                        case HSSFCell.CELL_TYPE_STRING: val=cleanHTML(cell.getRichStringCellValue().getString()); break;
                    }
                    if(i==startindex){ // List of Headers (consider startindex row as a headers)
                        if(val!=null){
                        jtemp1 = new JSONObject();
                        jtemp1.put("header", val);
                        jtemp1.put("index", j);
                        jobj.append("Header", jtemp1);
                        obj.put(colHeader,val);
                        }
                        arr.add(val);
                    } else {
                        if(arr.get(j)!=null)
                            obj.put(arr.get(j),val);
                    }

                }
                if(obj.length()>0){
                jArr.put(obj);
            }

            }
        } catch(Exception ex) {
           Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        jobj.put("startrow", startRow);
        jobj.put("maxrow", maxRow);
        jobj.put("maxcol", maxCol);
        jobj.put("index", sheetNo);
        jobj.put("data", jArr);
        jobj.put("filename", filename);

        jobj.put("msg", "XLS has been successfully uploaded");
        jobj.put("lsuccess", true);
        jobj.put("valid", true);
        return jobj;
}