Example usage for org.apache.poi.ss.usermodel Cell getCellFormula

Introduction

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

Prototype

String getCellFormula();

Source Link

Document

Return a formula for the cell, for example, SUM(C4:E4)

Usage

From source file:com.dataart.spreadsheetanalytics.engine.DependencyExtractors.java

License:Apache License

/** For given cell address gives a list of this cell's dependencies. */
static List<IA1Address> resolveCellDependencies(IA1Address cellAddress, Sheet sheet,
        FormulaParsingWorkbook workbook) {
    Row row = sheet.getRow(cellAddress.row());
    if (row == null) {
        return emptyList();
    }/* w w  w.  j av a2 s.  com*/
    Cell cell = row.getCell(cellAddress.column());
    if (cell == null) {
        return emptyList();
    }

    if (CELL_TYPE_FORMULA != cell.getCellType()) {
        return singletonList(cellAddress);
    }

    List<IA1Address> dependencies = new LinkedList<>();

    for (Ptg ptg : parse(cell.getCellFormula(), workbook, CELL, 0)) { /* TODO: only one sheet is supported */

        if (ptg instanceof RefPtg) {
            RefPtg ref = (RefPtg) ptg;

            dependencies.addAll(
                    resolveCellDependencies(fromRowColumn(ref.getRow(), ref.getColumn()), sheet, workbook));

        } else if (ptg instanceof AreaPtg) {
            AreaPtg area = (AreaPtg) ptg;

            for (int r = area.getFirstRow(); r <= area.getLastRow(); r++) {
                for (int c = area.getFirstColumn(); c <= area.getLastColumn(); c++) {
                    dependencies.addAll(resolveCellDependencies(fromRowColumn(r, c), sheet, workbook));
                }
            }
        }

        dependencies.add(cellAddress);
    }

    return dependencies;
}

---

From source file:com.dataart.spreadsheetanalytics.engine.graph.PoiDependencyGraphBuilder.java

License:Apache License

public static IExecutionGraph buildDependencyGraph(IDataModel dataModel, IA1Address cell) {
    if (dataModel == null) {
        throw new CalculationEngineException("DataModel and PoiModel are required to build dependency graph");
    }/* w ww.ja  v  a 2 s  . c  om*/

    PoiDependencyGraphBuilder db = new PoiDependencyGraphBuilder(dataModel);

    Sheet s = db.poiBook.getSheetAt(0); //TODO: works for only one sheet workbooks
    if (s == null) {
        return null;
    }
    Row r = s.getRow(cell.row());
    if (r == null) {
        return null;
    }
    Cell c = r.getCell(cell.column());
    if (c == null) {
        return null;
    }

    ExecutionGraphVertex v = ExecutionGraph
            .createVertex(A1Address.fromRowColumn(c.getRowIndex(), c.getColumnIndex()).address());
    db.state.addVertex(v);

    if (CELL_TYPE_FORMULA == c.getCellType()) {
        db.collect(v, c.getCellFormula());
    }

    return db.state;
}

---

From source file:com.dataart.spreadsheetanalytics.engine.graph.PoiDependencyGraphBuilder.java

License:Apache License

protected void collect(ExecutionGraphVertex parent, String formula) {
    Ptg[] ptgs = FormulaParser.parse(formula, this.poiFormulaBook, FormulaType.CELL,
            0 /*TODO: Sheet number = 0*/);

    Deque<ExecutionGraphVertex> ptgBag = new ArrayDeque<>();
    for (Ptg ptg : ptgs) {
        String name = ptgToString(ptg);

        ExecutionGraphVertex v = ExecutionGraph.createVertex(name);
        this.state.addVertex(v);

        if (ptg instanceof OperandPtg) { //operand (cell)
            ptgBag.push(v);// w  w  w . ja va 2s. com

            A1Address address = A1Address.fromA1Address(name);
            Cell c = this.poiBook.getSheetAt(0).getRow(address.row()).getCell(address.column());

            //recursive call to formula cells
            if (CELL_TYPE_FORMULA == c.getCellType()) {
                this.collect(v, c.getCellFormula());
            }

        } else if (ptg instanceof OperationPtg) { //operator (function)
            for (ExecutionGraphVertex s : ptgBag) {
                this.state.addEdge(s, v);
            }
            ptgBag.clear();
            ptgBag.push(v);
        }
    }

    this.state.addEdge(ptgBag.poll(), parent);
}

---

From source file:com.dataart.spreadsheetanalytics.engine.PoiWorkbookConverters.java

License:Apache License

private PoiProxySheet makeSheet(Workbook wb, FormulaParsingWorkbook ewb) {
    Sheet wbSheet = wb.getSheetAt(0);/* w w  w  . ja v a  2s . co  m*/
    PoiProxySheet sheet = new PoiProxySheet(wbSheet.getSheetName());

    for (int r = 0; r <= wbSheet.getLastRowNum(); r++) {
        Row wbSheetRow = wbSheet.getRow(r);
        if (wbSheetRow == null) {
            continue;
        }

        for (int c = 0; c <= wbSheetRow.getLastCellNum(); c++) {
            Cell wbSheetRowCell = wbSheetRow.getCell(c);
            if (wbSheetRowCell == null) {
                continue;
            }

            final Ptg[] tokens = CELL_TYPE_FORMULA == wbSheetRowCell.getCellType()
                    ? FormulaParser.parse(wbSheetRowCell.getCellFormula(), ewb, FormulaType.CELL, 0)
                    : null;
            sheet.setCell(PoiProxyCell.makeCell(sheet, wbSheetRowCell, tokens));
        }
    }

    return sheet;
}

---

From source file:com.dituiba.excel.ExcelUtility.java

License:Apache License

public static void copyCell(Cell srcCell, Cell distCell) {
    distCell.setCellStyle(srcCell.getCellStyle());
    if (srcCell.getCellComment() != null) {
        distCell.setCellComment(srcCell.getCellComment());
    }//w  ww .j av a2s . c  o  m
    int srcCellType = srcCell.getCellType();
    distCell.setCellType(srcCellType);

    if (srcCellType == Cell.CELL_TYPE_NUMERIC) {
        if (DateUtil.isCellDateFormatted(srcCell)) {
            distCell.setCellValue(srcCell.getDateCellValue());
        } else {
            distCell.setCellValue(srcCell.getNumericCellValue());
        }
    } else if (srcCellType == Cell.CELL_TYPE_STRING) {
        distCell.setCellValue(srcCell.getRichStringCellValue());
    } else if (srcCellType == Cell.CELL_TYPE_BLANK) {
        //nothing
    } else if (srcCellType == Cell.CELL_TYPE_BOOLEAN) {
        distCell.setCellValue(srcCell.getBooleanCellValue());
    } else if (srcCellType == Cell.CELL_TYPE_ERROR) {
        distCell.setCellErrorValue(srcCell.getErrorCellValue());
    } else if (srcCellType == Cell.CELL_TYPE_FORMULA) {
        distCell.setCellFormula(srcCell.getCellFormula());
    } else {
        //nothing
    }
}

---

From source file:com.fjn.helper.common.io.file.office.excel.ListExcelSheetReader.java

License:Apache License

private <T> T getBean(Class<T> clazz, Row row, List<String> fieldList) {
    Field[] fields = new Field[fieldList.size()];
    for (int i = 0; i < fields.length; i++) {
        Field field = null;/*  ww  w.j  av a 2s .  c  o m*/
        try {
            field = clazz.getDeclaredField(fieldList.get(i));
            // field.getAnnotation();
            fields[i] = field;
            field.setAccessible(true);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    T t = null;
    try {
        t = clazz.newInstance();
    } catch (Exception e) {
        e.printStackTrace();
    }

    for (int i = 0; i < fields.length; i++) {
        Cell cell = null;
        cell = row.getCell(i);
        if (cell != null) {
            int cellType = cell.getCellType();
            Object value = null;
            switch (cellType) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getNumericCellValue();
                    Class fieldClass = fields[i].getType();
                    if (fieldClass == Integer.class || fieldClass == int.class) {
                        value = ((Double) value).intValue();
                    } else if (fieldClass == Short.class || fieldClass == short.class) {
                        value = ((Double) value).shortValue();
                    } else if (fieldClass == Byte.class || fieldClass == byte.class) {
                        value = ((Double) value).byteValue();
                    } else if (fieldClass == Long.class || fieldClass == long.class) {
                        value = ((Double) value).longValue();
                    } else if (fieldClass == Float.class || fieldClass == float.class) {
                        value = ((Double) value).floatValue();
                    } else if (fieldClass == Double.class || fieldClass == double.class) {
                        value = (Double) value;
                    }
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_FORMULA:
                value = cell.getCellFormula();
                break;
            default:
                break;
            }

            try {
                fields[i].set(t, value);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    return t;
}

---

From source file:com.funtl.framework.smoke.core.commons.excel.ImportExcel.java

License:Apache License

/**
 * ??/*from   w  w  w. j a v a 2 s .co  m*/
 *
 * @param row    ?
 * @param column ???
 * @return ?
 */
@SuppressWarnings("deprecation")
public Object getCellValue(Row row, int column) {
    Object val = "";
    try {
        Cell cell = row.getCell(column);
        if (cell != null) {
            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                val = cell.getNumericCellValue();
            } else if (cell.getCellTypeEnum() == CellType.STRING) {
                val = cell.getStringCellValue();
            } else if (cell.getCellTypeEnum() == CellType.FORMULA) {
                val = cell.getCellFormula();
            } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                val = cell.getBooleanCellValue();
            } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                val = cell.getErrorCellValue();
            }
        }
    } catch (Exception e) {
        return val;
    }
    return val;
}

---

From source file:com.github.camaral.sheeco.exceptions.SpreadsheetViolation.java

License:Apache License

private static Object getCellValue(final Cell cell) {
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        }// w  ww  .j  av  a 2  s  .  com
        return cell.getNumericCellValue();
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_ERROR:
        return cell.getErrorCellValue();
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    default:
        throw new UnsupportedOperationException("CellType " + cell.getCellType() + " is invalid");
    }
}

---

From source file:com.github.cutstock.excel.ExcelWriterSupport.java

License:Apache License

protected String getCellValue(Cell cell) {
    if (cell != null) {
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_FORMULA:
            return "  " + cell.getCellFormula();
        case HSSFCell.CELL_TYPE_NUMERIC:
            return "" + cell.getNumericCellValue();
        case HSSFCell.CELL_TYPE_STRING:
            return "" + cell.getRichStringCellValue();
        default://from w  ww.ja v a2 s. c om
            return "";
        }
    }
    return "";
}

---

From source file:com.github.cutstock.utils.ProfileUtils.java

License:Apache License

public static Object getCellValue(Cell cell) {
    if (cell != null) {
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_FORMULA:
            return cell.getCellFormula();
        case HSSFCell.CELL_TYPE_NUMERIC:
            return cell.getNumericCellValue();
        case HSSFCell.CELL_TYPE_STRING:
            return "" + cell.getRichStringCellValue();
        default://  w w w.j av a  2 s . c  o m
            return "";// ?empty
        }
    }
    return "";
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• »