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

List of usage examples for org.apache.poi.ss.usermodel Cell getCellFormula

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. 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:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

private static Object _readCell(Cell cell) {
    if (cell == null) {
        return null;
    }/*from  w w w  . j a va 2s  . c  om*/
    int cellType = cell.getCellType();
    Object value = null;
    switch (cellType) {
    case Cell.CELL_TYPE_BLANK:
        value = null;
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        boolean bool = cell.getBooleanCellValue();
        value = bool;
        break;
    case Cell.CELL_TYPE_ERROR:
        // cell.getErrorCellValue();
        ExcelReadException e = new ExcelReadException("Cell type error");
        e.setRowIndex(cell.getRowIndex());
        e.setColIndex(cell.getColumnIndex());
        e.setCode(ExcelReadException.CODE_OF_CELL_ERROR);
        throw e;
    case Cell.CELL_TYPE_FORMULA:
        value = cell.getCellFormula();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        Object inputValue = null;//
        double doubleVal = cell.getNumericCellValue();
        if (DateUtil.isCellDateFormatted(cell)) {
            inputValue = DateUtil.getJavaDate(doubleVal);
        } else {
            long longVal = Math.round(cell.getNumericCellValue());
            if (Double.parseDouble(longVal + ".0") == doubleVal) {
                inputValue = longVal;
            } else {
                inputValue = doubleVal;
            }
        }
        value = inputValue;
        break;
    case Cell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        break;
    default:
        throw new RuntimeException("unsupport cell type " + cellType);
    }
    return value;
}

From source file:org.joeffice.spreadsheet.cell.CellUtils.java

License:Apache License

public static void copyCell(Cell oldCell, Cell newCell) {
    newCell.setCellStyle(oldCell.getCellStyle());

    if (newCell.getCellComment() != null) {
        newCell.setCellComment(oldCell.getCellComment());
    }//from  www .  j  a v a 2s  .co  m

    if (oldCell.getHyperlink() != null) {
        newCell.setHyperlink(oldCell.getHyperlink());
    }

    newCell.setCellType(oldCell.getCellType());

    // Set the cell data value
    switch (oldCell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        newCell.setCellValue(oldCell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        newCell.setCellErrorValue(oldCell.getErrorCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        newCell.setCellFormula(oldCell.getCellFormula());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        newCell.setCellValue(oldCell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getRichStringCellValue());
        break;
    }
}

From source file:org.kopsox.spreadsheet.util.POIUtil.java

License:Open Source License

/**
 * Gets a Value from the given Cell/*from ww  w  .java 2 s  .  c  o m*/
 * 
 * @param cell
 * @return Value
 */
@SuppressWarnings("boxing")
public static final Value getValueFormCell(Cell cell) {

    //NUMERIC
    if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
        //Numeric can be a Date or a Double

        return getValueFromNumeric(cell, null);

    } //STRING
    else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {

        return new StringValue(cell.getRichStringCellValue().getString());
    } //FORMEL
    else if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) {
        String formula = cell.getCellFormula();

        return getValueFromFormula(formula, cell);
    } //BOOLEAN
    else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {

        return new BooleanValue(cell.getBooleanCellValue());
    } //BLANK
    else if (Cell.CELL_TYPE_BLANK == cell.getCellType()) {

        return new BlankValue();
    } //ERROR
    else if (Cell.CELL_TYPE_ERROR == cell.getCellType()) {
        //TODO
        //NOT SUPPORTED AT THE MOMENT
    }

    return new BlankValue();
}

From source file:org.laukvik.excel.ExcelTableModel.java

License:Apache License

public String getString(Cell cell) {
    if (cell == null) {
        return null;
    }/*from   w w  w .j  a  v  a 2  s .co  m*/
    try {
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            Date date = cell.getDateCellValue();
            return dateFormat.format(date);
        }
    } catch (Exception e) {
    }
    try {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            return null;
        case Cell.CELL_TYPE_BOOLEAN:
            return cell.getBooleanCellValue() + "";
        case Cell.CELL_TYPE_ERROR:
            return null;
        case Cell.CELL_TYPE_FORMULA:
            return cell.getCellFormula();
        case Cell.CELL_TYPE_NUMERIC:
            Double d = cell.getNumericCellValue();
            return d.intValue() + "";
        case Cell.CELL_TYPE_STRING:
            return cell.getStringCellValue();

        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}

From source file:org.netxilia.impexp.impl.ExcelImportService.java

License:Open Source License

private ICellCommand copyCell(Cell poiCell, CellReference cellReference, HSSFPalette palette,
        NetxiliaStyleResolver styleResolver) throws FormulaParsingException {

    CellStyle poiStyle = poiCell.getCellStyle();
    Styles styles = PoiUtils.poiStyle2Netxilia(poiStyle,
            poiCell.getSheet().getWorkbook().getFontAt(poiStyle.getFontIndex()), palette, styleResolver);

    IGenericValue value = null;/*from w w w  .jav  a  2  s .  c o  m*/
    Formula formula = null;

    // log.info("CELL TYPE:" + cellReference + " type:" + poiCell.getCellType() + " "
    // + (poiCell.getCellType() == Cell.CELL_TYPE_FORMULA ? poiCell.getCellFormula() : "no"));
    switch (poiCell.getCellType()) {
    // TODO translate errors

    case Cell.CELL_TYPE_STRING:
        value = new StringValue(poiCell.getStringCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(poiCell)) {
            DateTime dt = new DateTime(poiCell.getDateCellValue());
            // TODO decide whether is date or time
            if (dt.isBefore(EXCEL_START)) {
                value = new DateValue(dt.toLocalTime());
            } else if (dt.getMillisOfDay() == 0) {
                value = new DateValue(dt.toLocalDate());
            } else {
                value = new DateValue(dt.toLocalDateTime());
            }
        } else {
            value = new NumberValue(poiCell.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        value = new BooleanValue(poiCell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        if (poiCell.getCellFormula() != null) {
            formula = formulaParser.parseFormula(new Formula("=" + poiCell.getCellFormula()));
        }
        break;
    }

    if ((styles == null || styles.getItems().isEmpty()) && formula == null
            && (value == null || value.equals(GenericValueUtils.EMTPY_STRING))) {
        return null;
    }
    return CellCommands.cell(new AreaReference(cellReference), value, formula, styles);
}

From source file:org.nuclos.server.common.ooxml.ExcelReader.java

License:Open Source License

private static Object getCellValue(Cell cell, int cellType) {
    switch (cellType) {
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        } else {/*from w  w w. j  a v  a2  s  .  c  om*/
            return cell.getNumericCellValue();
        }
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case Cell.CELL_TYPE_ERROR:
        return FormulaError.forInt(cell.getErrorCellValue()).getString();
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    default:
        throw new IllegalArgumentException("Unknown POI cell type " + cellType);
    }
}

From source file:org.openconcerto.erp.importer.DataImporter.java

License:Open Source License

public ArrayTableModel createModelFromXLS(File xlsFile, int sheetNumber) throws IOException {
    final InputStream inputStream = new FileInputStream(xlsFile);
    final POIFSFileSystem fileSystem = new POIFSFileSystem(new BufferedInputStream(inputStream));
    final HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
    final HSSFSheet sheet = workBook.getSheetAt(sheetNumber);
    Iterator<Row> rowsIterator = sheet.rowIterator();
    int columnCount = 0;
    int rowCount = 0;
    while (rowsIterator.hasNext()) {
        Row row = rowsIterator.next();//w w w. j a va 2  s .  co  m
        int i = row.getPhysicalNumberOfCells();
        if (i > columnCount) {
            columnCount = i;
        }
        rowCount++;
    }
    // Extract data
    rowsIterator = sheet.rowIterator();
    int start = 0;
    if (skipFirstLine) {
        start = 1;
        rowsIterator.next();
    }
    final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount - start);
    FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();

    while (rowsIterator.hasNext()) {
        final Row row = rowsIterator.next();
        final List<Object> rowData = new ArrayList<Object>();
        for (int i = 0; i < columnCount; i++) {
            final Cell cell = row.getCell(i);

            if (cell == null) {
                rowData.add("");
            } else {
                CellValue cellValue = evaluator.evaluate(cell);
                if (cellValue == null) {
                    rowData.add("");
                } else {
                    switch (cellValue.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        rowData.add(Boolean.valueOf(cellValue.getBooleanValue()));
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        rowData.add(Double.valueOf(cellValue.getNumberValue()));
                        break;
                    case Cell.CELL_TYPE_STRING:
                        rowData.add(cellValue.getStringValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        rowData.add(cell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        rowData.add("");
                        break;
                    default:
                        rowData.add(cellValue.getStringValue());
                        break;

                    }
                }
            }
        }

        rows.add(rowData);

    }
    inputStream.close();
    return new ArrayTableModel(rows);

}

From source file:org.openpythia.utilities.SSUtilities.java

License:Apache License

public static Row copyRow(Sheet sheet, Row sourceRow, int destination) {
    Row newRow = sheet.createRow(destination);
    // get the last row from the headings
    int lastCol = sheet.getRow(0).getLastCellNum();
    for (int currentCol = 0; currentCol <= lastCol; currentCol++) {
        Cell newCell = newRow.createCell(currentCol);

        // if there is a cell in the template, copy its content and style
        Cell currentCell = sourceRow.getCell(currentCol);
        if (currentCell != null) {
            newCell.setCellStyle(currentCell.getCellStyle());
            newCell.setCellComment(currentCell.getCellComment());
            switch (currentCell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(currentCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(currentCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                String dummy = currentCell.getCellFormula();
                dummy = dummy.replace("Row", String.valueOf(destination + 1));
                newCell.setCellFormula(dummy);
                newCell.setCellFormula(//www.jav a2s .  c  o m
                        currentCell.getCellFormula().replace("Row", String.valueOf(destination + 1)));
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(currentCell.getBooleanCellValue());
                break;
            default:
            }
        }
    }

    // if the row contains merged regions, copy them to the new row
    int numberMergedRegions = sheet.getNumMergedRegions();
    for (int i = 0; i < numberMergedRegions; i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);

        if (mergedRegion.getFirstRow() == sourceRow.getRowNum()
                && mergedRegion.getLastRow() == sourceRow.getRowNum()) {
            // this region is within the row - so copy it
            sheet.addMergedRegion(new CellRangeAddress(destination, destination, mergedRegion.getFirstColumn(),
                    mergedRegion.getLastColumn()));
        }
    }

    return newRow;
}

From source file:org.openstreetmap.josm.plugins.opendata.core.io.tabular.XlsReader.java

License:GNU General Public License

@Override
protected String[] readLine(ProgressMonitor progressMonitor) throws IOException {
    if (sheet != null) {
        Row row = sheet.getRow(rowIndex++);
        if (row != null) {
            List<String> result = new ArrayList<>();
            for (Cell cell : row) {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    result.add(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        result.add(cell.getDateCellValue().toString());
                    } else {
                        result.add(Double.toString(cell.getNumericCellValue()));
                    }/*  w  ww  .jav  a  2 s  . c  o  m*/
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    result.add(Boolean.toString(cell.getBooleanCellValue()));
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    result.add(cell.getCellFormula());
                    break;
                default:
                    result.add("");
                }
            }
            return result.toArray(new String[0]);
        }
    }
    return null;
}

From source file:org.opentestsystem.delivery.testreg.upload.ExcelUtils.java

License:Open Source License

private Object[] getCellValues(final Row row, final MissingCellPolicy policy) {
    final List<Object> excelColumnList = new ArrayList<Object>();
    final DataFormatter dataFormat = new DataFormatter();
    final int lastCellNo = row.getLastCellNum();
    for (int cellNum = 0; cellNum < lastCellNo; cellNum++) {
        final Cell cell = row.getCell(cellNum, policy);

        if (cell != null) {
            switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                excelColumnList.add(cell.getStringCellValue());
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                excelColumnList.add(cell.getStringCellValue());
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                excelColumnList.add(dataFormat.formatCellValue(cell));
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                excelColumnList.add("");
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                excelColumnList.add(cell.getCellFormula());
                break;
            case XSSFCell.CELL_TYPE_ERROR:
                excelColumnList.add(cell.getErrorCellValue());
                break;
            default:
                excelColumnList.add(cell.toString());
            }//ww  w .j  av a 2  s .  c o m
        }

    }
    return excelColumnList.toArray(new Object[excelColumnList.size()]);
}