Example usage for org.apache.poi.ss.usermodel FormulaEvaluator evaluate

List of usage examples for org.apache.poi.ss.usermodel FormulaEvaluator evaluate

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. FormulaEvaluator
  6. evaluate

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel FormulaEvaluator evaluate.

Prototype

CellValue evaluate(Cell cell);

Source Link

Document

If cell contains a formula, the formula is evaluated and returned, else the CellValue simply copies the appropriate cell value from the cell and also its cell type.

Usage

From source file:com.celtris.exparse.parser.ExcelReader.java

License:Apache License

public List<SheetData<T>> readExcel(String absolutePath, Class<T> excelModelClass, boolean headerExtraction)
        throws IOException, InstantiationException, IllegalAccessException {

    FileInputStream file = new FileInputStream(new File(absolutePath));

    // Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    DataFormatter objDefaultFormat = new DataFormatter();
    FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator(workbook);

    Iterator<Sheet> sheetIterator = workbook.iterator();
    List<SheetData<T>> sheetDataList = new ArrayList<SheetData<T>>(workbook.getNumberOfSheets());
    int sheetCount = 0;
    while (sheetIterator.hasNext()) {
        sheetCount++;//from  w w w  . ja  v a  2 s .  c  o m

        ExcelParser<T> excelParser = new ExcelParser<T>(headerExtraction, excelModelClass);
        Sheet sheet = sheetIterator.next();
        Iterator<Row> rowIterator = sheet.iterator();

        int rowCount = 0;

        // Evaluating header
        if (headerExtraction) {
            if (rowIterator.hasNext()) {

                rowCount++;

                Field[] fields = excelModelClass.getFields();
                List<String> heaaderStr = new ArrayList<String>(fields.length);

                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    String cellStrValue = cell.getStringCellValue();

                    heaaderStr.add(cellStrValue);
                }
                excelParser.processFieldAccordingToHeader(heaaderStr, sheet.getSheetName());
            }
        }

        while (rowIterator.hasNext()) {
            rowCount++;
            Row row = rowIterator.next();
            // For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            List<String> rowStr = new ArrayList<String>(excelParser.parameterCount());
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                String cellStrValue = "";
                switch (cell.getCellTypeEnum()) {
                case STRING:
                    cellStrValue = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        objFormulaEvaluator.evaluate(cell);
                        cellStrValue = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);
                    } else {

                        cellStrValue = Double.toString(cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    cellStrValue = Boolean.toString(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    cellStrValue = cell.getStringCellValue();
                    break;
                case BLANK:

                default:
                    break;
                }
                rowStr.add(cellStrValue);
            }

            excelParser.processRow(rowStr, rowCount, sheet.getSheetName());
        }

        SheetData<T> sheetData = new SheetData<T>(excelParser.getParsedObject(), sheet.getSheetName(),
                sheetCount);
        sheetDataList.add(sheetData);
    }

    file.close();
    workbook.close();
    return sheetDataList;
}

From source file:com.company.et.service.XlsService.java

public static void generateFile(List<TreeItem<Task>> root, List<ArrayList<ArrayList<Double>>> waitingParts,
        ObservableList<Professor> professors) throws FileNotFoundException, IOException {
    FileOutputStream out = new FileOutputStream(filename);
    Workbook wb = new HSSFWorkbook();

    for (int k = 0; k < professors.size(); k++) {

        Sheet s = wb.createSheet(professors.get(k).getFio());

        Row rowFirst = s.createRow(0);//from   w  ww  . j  av a2s.  c  o  m

        createConstStringCells(wb, s, "??", rowFirst, 0, 0, 1, 0, 0);
        createConstStringCells(wb, s, "?? ", rowFirst, 1, 0, 0, 1, 3);
        createConstStringCells(wb, s, "? ", rowFirst, 4, 0, 0, 4, 6);
        createConstStringCells(wb, s, "?? ", rowFirst, 7, 0, 0, 7, 9);
        createConstStringCells(wb, s, "?? ", rowFirst, 10, 0, 0, 10, 12);
        createConstStringCells(wb, s, "?? ", rowFirst, 13, 0, 0, 13, 15);

        Row rowSecond = s.createRow(1);
        for (int i = 0, j = 1; i < 5; i++, j += 3) {
            createConstStringCells(wb, s, "", rowSecond, j, 0, 0, 0, 0);
            createConstStringCells(wb, s, "", rowSecond, j + 1, 0, 0, 0, 0);
            createConstStringCells(wb, s, "", rowSecond, j + 2, 0, 0, 0, 0);
        }

        for (int i = 1; i < 16; i++) {
            Row row = s.createRow(i + 1);
            for (int j = 0; j < root.get(k).getChildren().size(); j++) {
                createCellOfDouble(wb, s, row, j * 3 + 2,
                        root.get(k).getChildren().get(j).getValue().getCapacities().get(i));
                createCellOfDouble(wb, s, row, j * 3 + 1, waitingParts.get(k).get(j).get(i));
                createReserveCell(wb, s, row, (j + 1) * 3);

            }
            createMonthCell(wb, s, row, DoubleCapacities.getDoubleCapacitiesByIndex(i).toString());
            createFullTasksActualCell(wb, s, row, root.get(k).getChildren().size() * 3 + 1);
            createFullTasksCell(wb, s, row, root.get(k).getChildren().size() * 3 + 2);
            createFullTasksReserveCell(wb, s, row, (root.get(k).getChildren().size() + 1) * 3);

        }
    }
    FormulaEvaluator formulaEval = wb.getCreationHelper().createFormulaEvaluator();
    //all year report
    Sheet s = wb.createSheet("?");
    Row rowFirst = s.createRow(0);
    Row rowSecond = s.createRow(1);
    createConstStringCells(wb, s, "/", rowFirst, 0, 0, 1, 0, 0);
    createConstStringCells(wb, s, "", rowFirst, 1, 0, 1, 1, 1);
    createConstStringCells(wb, s, "?", rowFirst, 2, 0, 1, 2, 2);
    createConstStringCells(wb, s, ". .", rowFirst, 3, 0, 1, 3, 3);
    for (int i = 1; i < 16; i++) {
        createConstStringCells(wb, s, DoubleCapacities.getDoubleCapacitiesByIndex(i).toString(), rowFirst,
                (i * 3) + 1, 0, 0, (i * 3) + 1, (i * 3) + 3);
        createConstStringCells(wb, s, "", rowSecond, (i * 3) + 1, 1, 1, (i * 3) + 1, (i * 3) + 1);
        createConstStringCells(wb, s, "", rowSecond, (i * 3) + 2, 1, 1, (i * 3) + 2, (i * 3) + 2);
        createConstStringCells(wb, s, "", rowSecond, (i * 3) + 3, 1, 1, (i * 3) + 3, (i * 3) + 3);
    }
    for (int i = 0; i < professors.size(); i++) {
        Row row = s.createRow(i + 2);
        createConstStringCells(wb, s, Integer.toString(i + 1), row, 0, i + 2, i + 2, 0, 0);
        createConstStringCells(wb, s, professors.get(i).getFio(), row, 1, i + 2, i + 2, 1, 1);
        createConstStringCells(wb, s, professors.get(i).getRate().toString(), row, 3, i + 2, i + 2, 3, 3);
        for (int j = 1; j < 16; j++) {
            createConstStringCells(wb, s,
                    formulaEval.evaluate(wb.getSheet(professors.get(i).getFio()).getRow(j + 1).getCell(13))
                            .formatAsString(),
                    row, (j * 3) + 1, i + 2, i + 2, (j * 3) + 1, (j * 3) + 1);
            createConstStringCells(wb, s,
                    formulaEval.evaluate(wb.getSheet(professors.get(i).getFio()).getRow(j + 1).getCell(14))
                            .formatAsString(),
                    row, (j * 3) + 2, i + 2, i + 2, (j * 3) + 2, (j * 3) + 2);
            createConstStringCells(wb, s,
                    formulaEval.evaluate(wb.getSheet(professors.get(i).getFio()).getRow(j + 1).getCell(15))
                            .formatAsString(),
                    row, (j * 3) + 3, i + 2, i + 2, (j * 3) + 3, (j * 3) + 3);
        }
    }
    wb.write(out);

    out.close();

}

From source file:com.jmc.jfxxlsdiff.util.POIXlsUtil.java

private static Object getFormulaValue(Cell cell) {
    Object cv = null;/*from  www .j a v  a2s  .c om*/

    FormulaEvaluator fe = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
    CellValue v = fe.evaluate(cell);

    switch (v.getCellType()) {
    case Cell.CELL_TYPE_BLANK: {
        break;
    }
    case Cell.CELL_TYPE_BOOLEAN: {
        cv = v.getBooleanValue();
        break;
    }
    case Cell.CELL_TYPE_ERROR: {
        cv = v.getErrorValue();
        break;
    }
    //case Cell.CELL_TYPE_FORMULA: {
    //   cv = cell.getCellFormula();
    //   break;
    //}
    case Cell.CELL_TYPE_NUMERIC: {
        double d = v.getNumberValue();

        if (DateUtil.isCellDateFormatted(cell)) {
            Calendar cal = Calendar.getInstance();
            cal.setTime(DateUtil.getJavaDate(d));
            cv = cal.getTime();
        } else {
            cv = d;
        }
        break;
    }
    case Cell.CELL_TYPE_STRING: {
        cv = v.getStringValue();
        break;
    }
    default: {
        logger.log(Level.WARNING, "Unexpected formula cell type = {0}", v.getCellType());
        break;
    }
    }

    return cv;
}

From source file:com.larasolution.serverlts.FileUploadHandler.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    //     tablename=request.getParameter(tablename)
    //process only if its multipart content
    FileOutputStream fos = new FileOutputStream("C:\\uploads\\data.csv");
    String list = "";
    List<List> allData = new ArrayList<List>();

    List<String> parameters = new ArrayList<String>();
    if (ServletFileUpload.isMultipartContent(request)) {

        try {/*from  w ww .  j  a va2s  .c o m*/

            StringBuilder data = new StringBuilder();
            List<FileItem> multiparts = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);
            System.out.println(multiparts);
            for (FileItem item : multiparts) {
                if (item.isFormField()) {
                    parameters.add(item.getFieldName());
                    System.out.println(parameters);
                }
                if (!item.isFormField()) {
                    String name = new File(item.getName()).getName();

                    item.write(new File(UPLOAD_DIRECTORY + File.separator + name));
                    //System.out.println(File.separator);
                    // Get the workbook object for XLSX file
                    XSSFWorkbook wBook = new XSSFWorkbook(
                            new FileInputStream(UPLOAD_DIRECTORY + File.separator + name));

                    XSSFSheet zz = wBook.getSheetAt(0);
                    FormulaEvaluator formulaEval = wBook.getCreationHelper().createFormulaEvaluator();

                    Row row;
                    Cell cell;

                    // Iterate through each rows from first sheet
                    Iterator<Row> rowIterator = zz.iterator();
                    while (rowIterator.hasNext()) {
                        row = rowIterator.next();

                        // For each row, iterate through each columns
                        Iterator<Cell> cellIterator = row.cellIterator();

                        while (cellIterator.hasNext()) {

                            cell = cellIterator.next();

                            switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_BOOLEAN:
                                data.append(cell.getBooleanCellValue()).append(",");
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    data.append(
                                            com.larasolution.modle.getDate.getDate5(cell.getDateCellValue()))
                                            .append(",");
                                } else {
                                    data.append(cell.getNumericCellValue()).append(",");
                                }

                                break;
                            case Cell.CELL_TYPE_STRING:
                                data.append(cell.getStringCellValue()).append(",");
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                data.append("" + ",");
                                break;
                            case Cell.CELL_TYPE_FORMULA:
                                Double value = Double.parseDouble(formulaEval.evaluate(cell).formatAsString());

                                data.append(String.format("%.2f", value)).append(",");
                                break;
                            default:
                                data.append(cell).append("");

                            }

                        }
                        data.append("\r\n");
                        //String k = data.substring(0, data.length() - 3);
                        //ls.add(k);

                        // data.setLength(0);
                    }

                    fos.write(data.toString().getBytes());
                    fos.close();

                    //
                }
            }

            savetosql();
            request.setAttribute("message", "successfully uploaded ");
        } catch (Exception ex) {
            request.setAttribute("message", "File Upload Failed due to " + ex);
        }

    } else {
        request.setAttribute("message", "Sorry this Servlet only handles file upload request");
    }

    request.setAttribute("arrayfile", allData);
    request.setAttribute("names", parameters);
    RequestDispatcher disp = getServletContext().getRequestDispatcher("/FileUploadResult.jsp");
    disp.forward(request, response);

    // System.out.println(allData.size());
    // response.sendRedirect("send.jsp?arrayfile=" + list + "");
    //request.getRequestDispatcher("/send.jsp?arrayfile='"+ls+"'").forward(request, response);
}

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

private DataFrame readData(Sheet sheet, int startRow, int startCol, int nrows, int ncols, boolean header,
        ReadStrategy readStrategy, DataType[] colTypes, boolean forceConversion, String dateTimeFormat,
        boolean takeCached, int[] subset) {

    DataFrame data = new DataFrame();
    int[] colset;

    // Formula evaluator - only if we don't want to take cached values
    FormulaEvaluator evaluator = null;
    if (!takeCached) {
        evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.clearAllCachedResultValues();
    }// w ww  .  jav a2  s.c om

    if (subset == null) {
        colset = new int[ncols];
        for (int i = 0; i < ncols; i++) {
            colset[i] = i;
        }
    } else {
        colset = subset;
    }

    ColumnBuilder cb;
    switch (readStrategy) {
    case DEFAULT:
        cb = new DefaultColumnBuilder(nrows, forceConversion, evaluator, onErrorCell, missingValue,
                dateTimeFormat);
        break;
    case FAST:
        cb = new FastColumnBuilder(nrows, forceConversion, evaluator, onErrorCell, dateTimeFormat);
        break;
    default:
        throw new IllegalArgumentException("Unknown read strategy!");
    }

    // Loop over columns
    for (int col : colset) {
        int colIndex = startCol + col;
        // Determine column header
        String columnHeader = null;
        if (header) {
            Cell cell = getCell(sheet, startRow, colIndex, false);
            // Check if there actually is a cell ...
            if (cell != null) {
                if (!takeCached) {
                    CellValue cv = evaluator.evaluate(cell);
                    if (cv != null)
                        columnHeader = cv.getStringValue();
                } else {
                    columnHeader = cell.getStringCellValue();
                }
            }
        }
        // If it was specified that there is a header but an empty(/non-existing)
        // cell or cell value is found, then use a default column name
        if (columnHeader == null)
            columnHeader = "Col" + (col + 1);

        // Prepare column builder for new set of rows
        cb.clear();

        // Loop over rows
        Row r;
        for (int row = header ? 1 : 0; row < nrows; row++) {
            int rowIndex = startRow + row;

            // Cell cell = getCell(sheet, rowIndex, colIndex, false);
            Cell cell = ((r = sheet.getRow(rowIndex)) == null) ? null : r.getCell(colIndex);
            cb.addCell(cell);
        }

        DataType columnType = ((colTypes != null) && (colTypes.length > 0)) ? colTypes[col % colTypes.length]
                : cb.determineColumnType();
        switch (columnType) {
        case Boolean:
            data.addColumn(columnHeader, cb.buildBooleanColumn());
            break;
        case DateTime:
            data.addColumn(columnHeader, cb.buildDateTimeColumn());
            break;
        case Numeric:
            data.addColumn(columnHeader, cb.buildNumericColumn());
            break;
        case String:
            data.addColumn(columnHeader, cb.buildStringColumn());
            break;
        default:
            throw new IllegalArgumentException("Unknown data type detected!");

        }
        // ArrayList columnValues = cb.build(columnType);
        // data.addColumn(columnHeader, columnType, columnValues);
        // Copy warnings
        for (String w : cb.retrieveWarnings())
            this.addWarning(w);
    }

    return data;
}

From source file:com.phucdk.emailsender.utils.ExcelUtils.java

public static String getCellValueAsString(int row, int column, XSSFWorkbook myWorkBook) {
    XSSFSheet mySheet = myWorkBook.getSheetAt(1);
    Cell cell = getCell(row, column, mySheet);
    String strCellValue = "";
    FormulaEvaluator evaluator = myWorkBook.getCreationHelper().createFormulaEvaluator();
    if (cell != null) {
        CellValue cellValue = null;/*ww  w .  j av  a2s . c  om*/
        try {
            cellValue = evaluator.evaluate(cell);
        } catch (Exception ex) {
            log.error("Error when evaluate cell value", ex);
        }

        if (cellValue != null) {
            switch (cellValue.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                strCellValue = String.valueOf(cellValue.getNumberValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                strCellValue = String.valueOf(cellValue.getBooleanValue());
                break;
            case Cell.CELL_TYPE_STRING:
                strCellValue = String.valueOf(cellValue.getStringValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                //strCellValue = String.valueOf(cellValue.get());
                break;
            }
        }

    }
    return strCellValue;
}

From source file:com.phucdk.emailsender.utils.ExcelUtils.java

public static Object getCellValue(int row, int column, XSSFWorkbook myWorkBook) {
    XSSFSheet mySheet = myWorkBook.getSheetAt(1);
    Cell cell = getCell(row, column, mySheet);
    Object cellValueObject = "";
    FormulaEvaluator evaluator = myWorkBook.getCreationHelper().createFormulaEvaluator();
    if (cell != null) {
        CellValue cellValue = null;/*  ww w .  j  av  a2s  .c  o m*/
        try {
            cellValue = evaluator.evaluate(cell);
        } catch (Exception ex) {
            log.error("Error when evaluate cell value", ex);
        }

        if (cellValue != null) {
            switch (cellValue.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                cellValueObject = cellValue.getNumberValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cellValueObject = cellValue.getBooleanValue();
                break;
            case Cell.CELL_TYPE_STRING:
                cellValueObject = cellValue.getStringValue();
                break;
            case Cell.CELL_TYPE_FORMULA:
                //strCellValue = cellValue.getErrorValue();
                break;
            }
        }

    }
    return cellValueObject;
}

From source file:com.streamsets.pipeline.lib.parser.excel.Cells.java

License:Apache License

static Field parseCell(Cell cell, FormulaEvaluator evaluator) throws ExcelUnsupportedCellTypeException {
    CellType cellType = cell.getCellTypeEnum();
    // set the cellType of a formula cell to its cached formula result type in order to process it as its result type
    boolean isFormula = cell.getCellTypeEnum().equals(CellType.FORMULA);
    if (isFormula) {
        cellType = cell.getCachedFormulaResultTypeEnum();
    }/*from   w  w  w  .j av a2  s  . c o m*/

    switch (cellType) {
    case STRING:
        return Field.create(cell.getStringCellValue());
    case NUMERIC:
        Double rawValue = cell.getNumericCellValue(); // resolves formulas automatically and gets value without cell formatting
        String displayValue = isFormula ? evaluator.evaluate(cell).formatAsString()
                : dataFormatter.formatCellValue(cell);
        boolean numericallyEquivalent = false;
        try {
            numericallyEquivalent = Double.parseDouble(displayValue) == rawValue;
        } catch (NumberFormatException e) {
        }

        if (DateUtil.isCellDateFormatted(cell)) {
            // It's a date, not a number
            java.util.Date dt = cell.getDateCellValue();
            // if raw number is < 1 then it's a time component only, otherwise date.
            return rawValue < 1 ? Field.createTime(dt) : Field.createDate(dt);
        }

        // some machinations to handle integer values going in without decimal vs. with .0 for rawValue
        return Field
                .create(numericallyEquivalent ? new BigDecimal(displayValue) : BigDecimal.valueOf(rawValue));

    case BOOLEAN:
        return Field.create(cell.getBooleanCellValue());
    case BLANK:
        return Field.create("");
    default:
        throw new ExcelUnsupportedCellTypeException(cell, cellType);
    }
}

From source file:com.wantdo.stat.excel.poi_src.formula.UserDefinedFunctionExample.java

License:Apache License

public static void main(String[] args) {

    if (args.length != 2) {
        System.out.println("usage: UserDefinedFunctionExample fileName cellId");
        return;/*from w ww  .  j a va 2  s  .  c o m*/
    }

    System.out.println("fileName: " + args[0]);
    System.out.println("cell: " + args[1]);

    File workbookFile = new File(args[0]);

    try {
        FileInputStream fis = new FileInputStream(workbookFile);
        Workbook workbook = WorkbookFactory.create(fis);
        fis.close();

        String[] functionNames = { "calculatePayment" };
        FreeRefFunction[] functionImpls = { new CalculateMortgage() };

        UDFFinder udfToolpack = new DefaultUDFFinder(functionNames, functionImpls);

        // register the user-defined function in the workbook
        workbook.addToolPack(udfToolpack);

        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

        CellReference cr = new CellReference(args[1]);
        String sheetName = cr.getSheetName();
        Sheet sheet = workbook.getSheet(sheetName);
        int rowIdx = cr.getRow();
        int colIdx = cr.getCol();
        Row row = sheet.getRow(rowIdx);
        Cell cell = row.getCell(colIdx);

        CellValue value = evaluator.evaluate(cell);

        System.out.println("returns value: " + value);

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:csv.impl.ExcelReader.java

License:Open Source License

/**
 * Returns the evaluated cell content./*ww  w  . j  a  v  a2s.co m*/
 * This assumes the cell contains a formula.
 * @param cell cell to evaluate
 * @return cell value
 */
public Object evaluateCellValue(Cell cell) {
    FormulaEvaluator evaluator = getFormulaEvaluator();
    CellValue value = evaluator.evaluate(cell);
    switch (value.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return value.getStringValue();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return DateUtil.getJavaDate(value.getNumberValue());
        } else {
            return value.getNumberValue();
        }
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return value.getBooleanValue();
    case Cell.CELL_TYPE_ERROR:
        return value.getErrorValue();
    default:
        System.out.println("type=" + cell.getCellType());
    }
    return cell.getCellFormula();
}